Description:
Defining a unique key constraint on a tinyint(4) column appears to see all values above 127 for that column as being duplicates.
PHP 5.2.17
How to repeat:
PHP code is:
<html>
<head>
<title>Demo of mysql duplicate key issue</title>
</head>
<body>
<?php
$Host = "localhost";
$User = "dbusername";
$Password = "dbpassword";
$DBName = "test_database";
$Link = mysql_connect($Host,$User,$Password);
printf("<p>MySQL server version: %s</p>\n", mysql_get_server_info());
print "<p>Current PHP version: " . phpversion() . "</p>\n";
$Query = "DROP TABLE IF EXISTS `demo`";
$Result = mysql_db_query ($DBName, $Query, $Link);
$Query = "CREATE TABLE `demo` ( `primaryKey` int(11) NOT NULL auto_increment, `ID` tinyint(4) NOT NULL, PRIMARY KEY (`primaryKey`), UNIQUE KEY `unique_key` (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
$Result = mysql_db_query ($DBName, $Query, $Link);
for ( $i = 120; $i <= 140; $i++)
{
$Query = "INSERT INTO `demo` SET `ID` = $i";
$Result = mysql_db_query ($DBName, $Query, $Link);
if (mysql_errno($Link) == 0)
{
print "<p>Table insert OK for \$i = $i</p>\n";
}
else
{
$error_text = mysql_error($Link);
$error_no = mysql_errno($Link);
print "<p>Error inserting into table at \$i = $i. Error number is $error_no, error message is '$error_text'</p>\n";
}
}
mysql_close($Link);
?>
</body>
</html>
Output from above is:
MySQL server version: 5.0.77-log
Current PHP version: 5.2.17
Table insert OK for $i = 120
Table insert OK for $i = 121
Table insert OK for $i = 122
Table insert OK for $i = 123
Table insert OK for $i = 124
Table insert OK for $i = 125
Table insert OK for $i = 126
Table insert OK for $i = 127
Error inserting into table at $i = 128. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 129. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 130. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 131. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 132. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 133. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 134. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 135. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 136. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 137. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 138. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 139. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Error inserting into table at $i = 140. Error number is 1062, error message is 'Duplicate entry '127' for key 2'
Suggested fix:
Ensure that unique key constraint recognises tinyint(4) correctly.
Description: Defining a unique key constraint on a tinyint(4) column appears to see all values above 127 for that column as being duplicates. PHP 5.2.17 How to repeat: PHP code is: <html> <head> <title>Demo of mysql duplicate key issue</title> </head> <body> <?php $Host = "localhost"; $User = "dbusername"; $Password = "dbpassword"; $DBName = "test_database"; $Link = mysql_connect($Host,$User,$Password); printf("<p>MySQL server version: %s</p>\n", mysql_get_server_info()); print "<p>Current PHP version: " . phpversion() . "</p>\n"; $Query = "DROP TABLE IF EXISTS `demo`"; $Result = mysql_db_query ($DBName, $Query, $Link); $Query = "CREATE TABLE `demo` ( `primaryKey` int(11) NOT NULL auto_increment, `ID` tinyint(4) NOT NULL, PRIMARY KEY (`primaryKey`), UNIQUE KEY `unique_key` (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"; $Result = mysql_db_query ($DBName, $Query, $Link); for ( $i = 120; $i <= 140; $i++) { $Query = "INSERT INTO `demo` SET `ID` = $i"; $Result = mysql_db_query ($DBName, $Query, $Link); if (mysql_errno($Link) == 0) { print "<p>Table insert OK for \$i = $i</p>\n"; } else { $error_text = mysql_error($Link); $error_no = mysql_errno($Link); print "<p>Error inserting into table at \$i = $i. Error number is $error_no, error message is '$error_text'</p>\n"; } } mysql_close($Link); ?> </body> </html> Output from above is: MySQL server version: 5.0.77-log Current PHP version: 5.2.17 Table insert OK for $i = 120 Table insert OK for $i = 121 Table insert OK for $i = 122 Table insert OK for $i = 123 Table insert OK for $i = 124 Table insert OK for $i = 125 Table insert OK for $i = 126 Table insert OK for $i = 127 Error inserting into table at $i = 128. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 129. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 130. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 131. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 132. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 133. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 134. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 135. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 136. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 137. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 138. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 139. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Error inserting into table at $i = 140. Error number is 1062, error message is 'Duplicate entry '127' for key 2' Suggested fix: Ensure that unique key constraint recognises tinyint(4) correctly.