Bug with ON DUPLICATE KEY UPDATE and bulk insert in MySQL-4.1

Hi,

How-to-repeat :

CREATE TABLE `searchjoinhardwarefr8` (
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`pseudo` char(35) character set latin1 NOT NULL default '',
`numreponse` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`date`,`numreponse`,`topic`),
UNIQUE KEY `pseudo` (`pseudo`,`date`,`numreponse`,`topic`),
UNIQUE KEY `numreponse` (`numreponse`),
KEY `topic` (`topic`)
) TYPE=MyISAM ROW_FORMAT=FIXED;

INSERT INTO searchjoinhardwarefr8 VALUES (0, '0000-00-00 00:00:00', '',
2788),
(0, '0000-00-00 00:00:00', '', 2789),
(0, '0000-00-00 00:00:00', '', 2815);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM searchjoinhardwarefr8;
+-------+---------------------+--------+------------+
| topic | date | pseudo | numreponse |
+-------+---------------------+--------+------------+
| 0 | 0000-00-00 00:00:00 | | 2788 |
| 0 | 0000-00-00 00:00:00 | | 2789 |
| 0 | 0000-00-00 00:00:00 | | 2815 |
+-------+---------------------+--------+------------+
3 rows in set (0.00 sec)

INSERT INTO searchjoinhardwarefr8 VALUES (0, '0000-00-00 00:00:00', '',
2788),
(0, '0000-00-00 00:00:00', '', 2789),
(0, '0000-00-00 00:00:00', '1', 2816) ON DUPLICATE KEY UPDATE topic=topic+1;
Query OK, 5 rows affected (0.00 sec)
Records: 3 Duplicates: 2 Warnings: 0

mysql> SELECT * FROM searchjoinhardwarefr8;
+-------+---------------------+--------+------------+
| topic | date | pseudo | numreponse |
+-------+---------------------+--------+------------+
| 0 | 0000-00-00 00:00:00 | 1 | 2788 |
| 0 | 0000-00-00 00:00:00 | 1 | 2789 |
| 0 | 0000-00-00 00:00:00 | 1 | 2815 |
| 0 | 0000-00-00 00:00:00 | 1 | 2816 |
+-------+---------------------+--------+------------+
4 rows in set (0.00 sec)

=> topic has not been incremented, and moreover pseudo has been updated to 1
for all the rows ??

UPDATE : I'm unable to reproduce this bug now with the same testcase ?! I've
just checked again my first the result into mysql-monitor, and there is no
error...
I don't understand exactly what's going on. Could it be possible the query
cache be implicated in this problem ?

Regards,
Jocelyn


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13203@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with ON DUPLICATE KEY UPDATE and bulk insert in MySQL-4.1

Hi!

On Dec 05, Jocelyn Fournier wrote:
> Hi,
>
> How-to-repeat :
>
> CREATE TABLE `searchjoinhardwarefr8` (
> `topic` mediumint(8) unsigned NOT NULL default '0',
> `date` datetime NOT NULL default '0000-00-00 00:00:00',
> `pseudo` char(35) character set latin1 NOT NULL default '',
> `numreponse` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`date`,`numreponse`,`topic`),
> UNIQUE KEY `pseudo` (`pseudo`,`date`,`numreponse`,`topic`),
> UNIQUE KEY `numreponse` (`numreponse`),
> KEY `topic` (`topic`)
> ) TYPE=MyISAM ROW_FORMAT=FIXED;
>
> INSERT INTO searchjoinhardwarefr8 VALUES (0, '0000-00-00 00:00:00', '',
> 2788),
> (0, '0000-00-00 00:00:00', '', 2789),
> (0, '0000-00-00 00:00:00', '', 2815);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> SELECT * FROM searchjoinhardwarefr8;
> +-------+---------------------+--------+------------+
> | topic | date | pseudo | numreponse |
> +-------+---------------------+--------+------------+
> | 0 | 0000-00-00 00:00:00 | | 2788 |
> | 0 | 0000-00-00 00:00:00 | | 2789 |
> | 0 | 0000-00-00 00:00:00 | | 2815 |
> +-------+---------------------+--------+------------+
> 3 rows in set (0.00 sec)
>
> INSERT INTO searchjoinhardwarefr8 VALUES (0, '0000-00-00 00:00:00', '',
> 2788),
> (0, '0000-00-00 00:00:00', '', 2789),
> (0, '0000-00-00 00:00:00', '1', 2816) ON DUPLICATE KEY UPDATE topic=topic+1;
> Query OK, 5 rows affected (0.00 sec)
> Records: 3 Duplicates: 2 Warnings: 0
>
> mysql> SELECT * FROM searchjoinhardwarefr8;
> +-------+---------------------+--------+------------+
> | topic | date | pseudo | numreponse |
> +-------+---------------------+--------+------------+
> | 0 | 0000-00-00 00:00:00 | 1 | 2788 |
> | 0 | 0000-00-00 00:00:00 | 1 | 2789 |
> | 0 | 0000-00-00 00:00:00 | 1 | 2815 |
> | 0 | 0000-00-00 00:00:00 | 1 | 2816 |
> +-------+---------------------+--------+------------+
> 4 rows in set (0.00 sec)
>
> => topic has not been incremented, and moreover pseudo has been updated to 1
> for all the rows ??
>
> UPDATE : I'm unable to reproduce this bug now with the same testcase ?! I've
> just checked again my first the result into mysql-monitor, and there is no
> error...
> I don't understand exactly what's going on. Could it be possible the query
> cache be implicated in this problem ?

Hardly, it only handles exact matches, when a query is completely
identical to some previous one.

And I just tried your testcase on a restarted server - query cache was
empty, and this test case works, no problems :-/

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13218@lists.mysql.com
To unsubscribe, e-mail