| Bug #45470 | problem with ALTER TABLE from SIGNED to UNSIGNED integer | ||
|---|---|---|---|
| Submitted: | 12 Jun 2009 14:35 | Modified: | 15 Jun 2009 8:05 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.1.35 likely any | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[12 Jun 2009 14:41]
Peter Laursen
Please read like this A negative value in a SIGNED column will be made '0' when ...
[12 Jun 2009 15:34]
Peter Laursen
In my understanding also restoring a dump of the table will fail (if there is both a '0' and a '1' value for the column) This was originally reported to us by a customer using MySQL 5.0.45
[12 Jun 2009 16:53]
Valeriy Kravchuk
Sorry, but your id column is defined as auto_increment: `id` INT(11) NOT NULL AUTO_INCREMENT, So, when you insert 0 into this column after truncate, what value should you expect? I expect 1, because of auto_increment property.
[12 Jun 2009 16:57]
Valeriy Kravchuk
Sorry, but your id column is defined as auto_increment: `id` INT(11) NOT NULL AUTO_INCREMENT, So, when you insert 0 into this column after truncate, what value should you expect? I expect 1, because of auto_increment property.
[12 Jun 2009 17:03]
Peter Laursen
In my understanding also restoring a dump of the table will fail (if there is both a '0' and a '1' value for the column) This was originally reported to us by a customer using MySQL 5.0.45
[12 Jun 2009 17:12]
Peter Laursen
I know and that is the problem!
I explicitly specify '0' for the autoinc column and it becomes '1'.
"Insert into table (text) values ('a')" will insert (1,'a') if table has no id-values larger than '0' - so far no problem.
but
"Insert into table (id, text) values (0, 'a')" should insert (0,'a').
This may be as documented. But the current implementation creates a situation where data cannot be backed/restored and also not copied successfully with various tools! Will row-based replication properly handle the sequense of statements I described? Anyway an implementation where (SQL-based) backup/restore is not possible is buggy in my opinion!
[13 Jun 2009 13:36]
Valeriy Kravchuk
If you want to insert 0 as is into auto_increment column, we have a special SQL mode for this. Please, check http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_auto_value_on_zero.
[13 Jun 2009 13:55]
Peter Laursen
OK! But then every backup and copy routine will have to use that SQL-mode (either always or when a test for the condition finds the requirement). I think it should not be necessary to check for data before taking a backup or copying. Since this sql_mode is required and not default what problems may then arise from using it? I can insert (99,'a'), and '99' will not become '1', but not (0,'b') without specifying that mode. In my understanding it is an unnecessary complication. I do not understand the need for such mode. Anyway that may be a solution for us to use in backup/copy/synchronisation tools. I did not check, but does 'mysqldump' set this mode? No issues with replication either?
[13 Jun 2009 21:02]
Peter Laursen
Just want to clarify that I expect the server to handle auto-increment column automatically *when and only when* the INSERT (or UPDATE) statement did not specify a value for the column.
[14 Jun 2009 7:48]
Peter Laursen
.. or the ALTER statement should not result in '0' value for the autoincrement column (but rather return an error) unles that special sql_mode is set. But that is not even a full solution as sql_moded is SESSION based. One client may do that and another client doing backup etc. will not know!
[14 Jun 2009 9:30]
Valeriy Kravchuk
Please, do not base your reasoning on your user's claims or some assumptions. Just do mysqdump on the table after this ALTER and read the results and/or try to restore it. You will see how it works, what SQL mode is set etc. Just try.
I tried, and I see the following:
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.36-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `idtest`; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
mysql> CREATE TABLE `idtest` (
-> `id` INT(11) NOT NULL AUTO_INCREMENT,
-> `txt` VARCHAR(20) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> INSERT INTO `idtest` VALUES (-1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `idtest` VALUES (1, 'b');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> ALTER TABLE `idtest` CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
Query OK, 2 rows affected, 1 warning (0.15 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql>
mysql> SELECT * FROM `idtest`;
+----+------+
| id | txt |
+----+------+
| 0 | a |
| 1 | b |
+----+------+
2 rows in set (0.00 sec)
mysql> exit
Bye
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysqldump -uroot test idtest
-- MySQL dump 10.13 Distrib 5.1.36, for apple-darwin9.6.0 (i386)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.1.36-debug
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `idtest`
--
DROP TABLE IF EXISTS `idtest`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `idtest` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`txt` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `idtest`
--
LOCK TABLES `idtest` WRITE;
/*!40000 ALTER TABLE `idtest` DISABLE KEYS */;
INSERT INTO `idtest` VALUES (0,'a'),(1,'b');
/*!40000 ALTER TABLE `idtest` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2009-06-14 12:27:48
Note this line:
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
You can see that, as a result, there is no problem with dump and restore.
Also try to do the same ALTER with two rows in table having negative PK values. I'd say that both MySQL server and mysqldump are working as correct as possible in this case.
[14 Jun 2009 9:38]
Peter Laursen
OK .. there is no problem with mysqldump then. I still think the server behaviour is weird both when ALTERing a table as described and when explicitly INSERTing '0'. My report was reported as an issue with the server and not mysqldump (a program I never use). That was why I did not try it. But I think there is not more to say, and you can conclude this.
[14 Jun 2009 9:56]
Valeriy Kravchuk
Well, I can conclude that current server's behavior is intended, consistent, taken into account by tools like mysqldump, and documented. You ask to change it and do not treat value 0 for auto_increment column in any special way by default. This is a feature request (change in server's behavior) that, probably, will not be implemented any time soon (not in 5.x for sure). Personally I think that there is no need to change anything. Some other RDBMSes, for example, IBM Informix, treat 0 in the same way for columns with auto-generated sequential values. So, do you want me to mark this as "Not a bug" or you prefer reclassification as a feature request?
[14 Jun 2009 10:57]
Peter Laursen
If it is consistent with SQL standards and the behavior or other database servers it is *not a bug*. I reserve myself though the right to check in documentation if all aspects of the bahaviour are documented. In particular I will verify documentation of: * The behaviour with ALTER table SIGNED>>UNSIGNED and negative values (yes, there is a warning!) * The behaviour when explicitly inserting '0' to an autoinc field. * That a special SQL-mode is required for successfully backup/restory, copy etc. to succesfully handle all cases. (but I do not consider a functional workaround in 'mysldump' program important. Actually I find it irrelevant. The server must be judged by its own behavior on its own premsies. What specific clients do does not matter). Anyway thanks a lot for pointing my attention to that sql_mode option!
Description: This server behaviour casues problems with Data Synchronisation tools (SQLyog, Maatkit). I think it may cause problems with row-based replication too (but you know better). A SIGNED value will be made '0' when column is ALTERED to UNSIGNED. But when manually inserting '0' it will become '1' How to repeat: DROP TABLE IF EXISTS `idtest`; CREATE TABLE `idtest` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `txt` VARCHAR(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO `idtest` VALUES (-1,'a'); INSERT INTO `idtest` VALUES (1, 'b'); ALTER TABLE `idtest` CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; SELECT * FROM `idtest`; /* id txt ------ ------ 0 a 1 b */ TRUNCATE TABLE `idtest`; INSERT INTO `idtest` VALUES (0,'a'); SELECT * FROM `idtest`; /* id txt ------ ------ 1 a */ INSERT INTO `idtest` VALUES (1,'b'); -- Error Code : 1062 Duplicate entry '1' for key 'PRIMARY' Suggested fix: But why not allow insertion of '0'? The context is the 'drupal' application where the installer does things similar to the operations listed above.