simple insert in empty table doesn't fail but doesn't insert

Bug #518487 reported by tormen

This bug report was converted into a question: question #100268: simple insert in empty table doesn't fail but doesn't insert.

6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Connector/Python
Invalid
Undecided
Unassigned

Bug Description

CLIENT:
python 3.1.1 + mysql connector 0.1.3
Linux pixel 2.6.31.3-nogo-pixel #1 SMP PREEMPT Mon Dec 7 01:14:18 EST 2009 i686 Intel(R) Core(TM)2 CPU U7600 @ 1.20GHz GenuineIntel GNU/Linux

SERVER:
Server version: 5.1.39-ndb-7.0.9-cluster-gpl-log MySQL Cluster Server (GPL)
Linux oneohone 2.6.18-6-amd64 #1 SMP Fri Dec 12 05:49:32 UTC 2008 x86_64 GNU/Linux

COMMAND:
MySQLCursor.execute( query = "INSERT INTO users (`username`) VALUES (%s) ON DUPLICATE KEY UPDATE `username`=VALUES(`username`)", args = ['SYSTEM'] )

Debug from within cursor.py:
cursor.MySQLCursor.execute(...): 1 BEGINNING
cursor.MySQLCursor.execute(...): 2 AFTER _reset_result() operation == 'INSERT INTO users (`username`) VALUES (%s) ON DUPLICATE KEY UPDATE `username`=VALUES(`username`)'
cursor.MySQLCursor.execute(...): 3a == params is not None
cursor.MySQLCursor.execute(...): 4 stmt == 'INSERT INTO users (`username`) VALUES ('SYSTEM') ON DUPLICATE KEY UPDATE `username`=VALUES(`username`)'
cursor.MySQLCursor.execute(...): 5a _have_result == 'False'
cursor.MySQLCursor.execute(...): 6 _executed == 'b"INSERT INTO users (`username`) VALUES ('SYSTEM') ON DUPLICATE KEY UPDATE `username`=VALUES(`username`)"' rowcount == '1'

As I mentioned in the summary: It is an empty table (below you will find the show create table).
After several trials I then copied and pasted the "_executed" string in the mysql client on the server and here is what I saw:
  + at the beginning the table was still empty!
  + then I tried the _execute string (in the mysql client) and selected and got an ID of 12 !!?? (.. and I did not use the table in any other way --> I guess the "insert trials" from python did increase the ID (without inserting anything))
 + then I ran the python program again 3 times
 + then I "DELETE"d (in the mysql client)
 + and ran the "INSERT" again (in the mysql client) and got an ID of 16

Why is this not working? Am I missing something?!
Any help very appreciated.
Please let me know if you need any further information.

mysql> use user;
Database changed
mysql> select * from users;
Empty set (0.00 sec)

mysql> INSERT INTO users (`username`) VALUES ('SYSTEM') ON DUPLICATE KEY UPDATE `username`=VALUES(`username`);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from users;
+----+----------+----------+----------+
| ID | username | password | creation |
+----+----------+----------+----------+
| 12 | SYSTEM | | NULL |
+----+----------+----------+----------+
1 row in set (0.00 sec)

mysql> delete from users where username = 'SYSTEM';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users (`username`) VALUES ('SYSTEM') ON DUPLICATE KEY UPDATE `username`=VALUES(`username`);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from users;
+----+----------+----------+----------+
| ID | username | password | creation |
+----+----------+----------+----------+
| 16 | SYSTEM | | NULL |
+----+----------+----------+----------+
1 row in set (0.00 sec)

mysql> show create table users;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` char(128) COLLATE utf8_unicode_ci NOT NULL,
  `password` char(128) COLLATE utf8_unicode_ci NOT NULL,
  `creation` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`)
) ENGINE=ndbcluster AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Changed in myconnpy:
status: New → Invalid
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Related questions

Remote bug watches

Bug watches keep track of this bug in other bug trackers.