Bug #45144 UPDATE IS NOT AVALIABLE IN ANOTHER CONNECTION
Submitted: 27 May 2009 18:37 Modified: 27 Jun 2009 20:00
Reporter: alexandre mancini Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S2 (Serious)
Version:5.1.31-COMMUNITY OS:Windows (WINDOWS SERVER 2003)
Assigned to: CPU Architecture:Any
Tags: UPDATE;REFRESH

[27 May 2009 18:37] alexandre mancini 
Description:
I AM USING A CLIENT APPLICATION TO INSERT/UPDATE A TABLE.

BUT, IF I OPEN ANOTHER CONNECTION, WITH THE FIRST ONE OPEN YET, WHEN I QUERY THE TABLE WITH A SELECT, THE INSERTED/UPDATED DATA IS NOT AVALIABLE.

DROP TABLE IF EXISTS `tbp8`;
CREATE TABLE `tbp8` (
  `p8cdus` char(2) NOT NULL DEFAULT '',
  `p8cdfu` char(3) NOT NULL DEFAULT '',
  `p8modl` char(6) NOT NULL DEFAULT '',
  `p8dten` date DEFAULT '0001-01-01',
  `p8hren` char(8) NOT NULL DEFAULT '',
  `p8hrsd` char(8) NOT NULL DEFAULT '',
  `p8indu` char(1) NOT NULL DEFAULT '',
  `sql_rowid` bigint(10) NOT NULL AUTO_INCREMENT,
  `sql_deleted` enum('F','T') NOT NULL,
  PRIMARY KEY (`sql_rowid`),
  KEY `IDX_tbP801` (`p8cdus`,`p8cdfu`,`p8modl`,`p8dten`,`p8indu`,`p8hren`,`sql_rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=latin1;

FIRST I MADE THIS INSERT...
INSERT INTO tbp8 VALUES
('01', '001', 'MTC211', '2009-05-27', '15:21:16', '', 'S', null, 'F');

BUT, WHEN I MADE THE FOLLOWING QUERY, I GOT A WRONG ANSWER!
mysql> select * from tbP8 ORDER BY sql_rowid DESC LIMIT 5;
Empty set (0.00 sec)

AFTER THAT, I MAD ANOTHER QUERY AN UPDATE:
UPDATE tbp8
SET p8indu = 'X',
    p8hrsd = '15:30:35'
WHERE p8cdus = '01' AND p8cdfu = '001' AND p8modl = 'MTC211'
  AND p8dten = '2009-05-27' AND p8indu = 'S' AND p8hren = '15:21:16'
  AND sql_rowid = '119';

BUT, WHEN I MADE THE FOLLOWING QUERY, I GOT A WRONG ANSWER!
mysql> select * from tbP8 ORDER BY sql_rowid DESC LIMIT 5;
+--------+--------+--------+------------+----------+--------+--------+-----------+-------------+
| p8cdus | p8cdfu | p8modl | p8dten     | p8hren   | p8hrsd | p8indu | sql_rowid | sql_deleted |
+--------+--------+--------+------------+----------+--------+--------+-----------+-------------+
| 01     | 001    | MTC211 | 2009-05-27 | 15:21:16 |        | S      |       119 | F           |
+--------+--------+--------+------------+----------+--------+--------+-----------+-------------+
1 row in set (0.00 sec)

IT WOULD BE... LIKE THIS... WHAT I GOT WHEN I FINISH THE APPLICATION
mysql> select * from tbP8 ORDER BY sql_rowid DESC LIMIT 5;
+--------+--------+--------+------------+----------+----------+--------+-----------+-------------+
| p8cdus | p8cdfu | p8modl | p8dten     | p8hren   | p8hrsd   | p8indu | sql_rowid | sql_deleted |
+--------+--------+--------+------------+----------+----------+--------+-----------+-------------+
| 01     | 001    | MTC211 | 2009-05-27 | 15:21:16 | 15:30:35 | X      |       119 | F           |
+--------+--------+--------+------------+----------+----------+--------+-----------+-------------+
1 row in set (0.00 sec)

ANOTHER INFORMATION... I HAVE A MYSQL SERVER INSTALLED IN A LINUX VERSION 5.0.45! AND IT WASN'T HAPPEN...

How to repeat:
DROP TABLE IF EXISTS `tbp8`;
CREATE TABLE `tbp8` (
  `p8cdus` char(2) NOT NULL DEFAULT '',
  `p8cdfu` char(3) NOT NULL DEFAULT '',
  `p8modl` char(6) NOT NULL DEFAULT '',
  `p8dten` date DEFAULT '0001-01-01',
  `p8hren` char(8) NOT NULL DEFAULT '',
  `p8hrsd` char(8) NOT NULL DEFAULT '',
  `p8indu` char(1) NOT NULL DEFAULT '',
  `sql_rowid` bigint(10) NOT NULL AUTO_INCREMENT,
  `sql_deleted` enum('F','T') NOT NULL,
  PRIMARY KEY (`sql_rowid`),
  KEY `IDX_tbP801` (`p8cdus`,`p8cdfu`,`p8modl`,`p8dten`,`p8indu`,`p8hren`,`sql_rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=latin1;

FIRST I MADE THIS INSERT...
INSERT INTO tbp8 VALUES
('01', '001', 'MTC211', '2009-05-27', '15:21:16', '', 'S', null, 'F');

BUT, WHEN I MADE THE FOLLOWING QUERY, I GOT A WRONG ANSWER!
mysql> select * from tbP8 ORDER BY sql_rowid DESC LIMIT 5;
Empty set (0.00 sec)

AFTER THAT, I MAD ANOTHER QUERY AN UPDATE:
UPDATE tbp8
SET p8indu = 'X',
    p8hrsd = '15:30:35'
WHERE p8cdus = '01' AND p8cdfu = '001' AND p8modl = 'MTC211'
  AND p8dten = '2009-05-27' AND p8indu = 'S' AND p8hren = '15:21:16'
  AND sql_rowid = '119';

BUT, WHEN I MADE THE FOLLOWING QUERY, I GOT A WRONG ANSWER!
mysql> select * from tbP8 ORDER BY sql_rowid DESC LIMIT 5;
+--------+--------+--------+------------+----------+--------+--------+-----------+-------------+
| p8cdus | p8cdfu | p8modl | p8dten     | p8hren   | p8hrsd | p8indu | sql_rowid | sql_deleted |
+--------+--------+--------+------------+----------+--------+--------+-----------+-------------+
| 01     | 001    | MTC211 | 2009-05-27 | 15:21:16 |        | S      |       119 | F           |
+--------+--------+--------+------------+----------+--------+--------+-----------+-------------+
1 row in set (0.00 sec)

IT WOULD BE... LIKE THIS... WHAT I GOT WHEN I FINISH THE APPLICATION
mysql> select * from tbP8 ORDER BY sql_rowid DESC LIMIT 5;
+--------+--------+--------+------------+----------+----------+--------+-----------+-------------+
| p8cdus | p8cdfu | p8modl | p8dten     | p8hren   | p8hrsd   | p8indu | sql_rowid | sql_deleted |
+--------+--------+--------+------------+----------+----------+--------+-----------+-------------+
| 01     | 001    | MTC211 | 2009-05-27 | 15:21:16 | 15:30:35 | X      |       119 | F           |
+--------+--------+--------+------------+----------+----------+--------+-----------+-------------+
1 row in set (0.00 sec)

ANOTHER INFORMATION... I HAVE A MYSQL SERVER INSTALLED IN A LINUX VERSION 5.0.45! AND IT WASN'T HAPPEN...
[27 May 2009 19:21] Sveta Smirnova 
Thank you for the report.

I assume you run "select * from tbP8 ORDER BY sql_rowid DESC LIMIT 5;" in another client than one you did INSERT in.

Please provide output of query SELECT @@autocommit;
[27 May 2009 19:43] alexandre mancini 
AFTER the INSERT:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

AFTER the UPDATE
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

AFTER I EXIT THE APPLICATION
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

AFTER THE QUERIES SELECT @@AUTOCOMMIT; I MADE A SELECT QUERY AND THE SAME ERROR PERSIST.
[27 May 2009 20:00] Sveta Smirnova 
Thank you for the feedback.

I can not repeat described behavior. Additionally there is a chance this problem fixed already as there were several query cache bugs fixed lately.

Please upgrade to current version 5.1.34, try with it and if problem still exists provide your configuration file.
[27 Jun 2009 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".