MySQL ON DUPLICATE KEY UPDATE double insert on Xampp PHP7.0

Question

Problem:

insert on duplicate key update inserts two rows into table from PHP only and Xampp/Windows only

Test code:

$connection = new \PDO('mysql:dbname=XXXXXXXXX;host=XXXXXXXX', 'XXXXXXXX', 'XXXXXXXX');
$query = 'insert into `test` (test) VALUES (1) on duplicate key update test=1';
$connection->exec($query);

Mysql schema:

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Server:

xampp-win32-7.0.18-0-VC14

Additional information:

Tested on multiple MySQL servers, from the same web server
When testing on a Linux server, also with PHP7, the problem was fixed
So the problem seems to be with the php mysql extension
I'm still diagnosing but hoping this issue can be resolved for documentation

EDIT:

As mentioned below, it seems this question is easily mis-interpretted. I am aware that a key is required for the condition to insert/update against one row. This is not the intent and it is not the question. Given the case that a key is not used, the IODKU statement should insert a row every time it is run. It is inserting 2 rows after running 1 time

@Forbs It doesn't matter since `on duplicate key update` shouldn't _insert_ multiple rows. As mentioned, the problem resolved when testing from another web server with the same structure. — iautomation, Nov 18 '17 at 03:20
Sure it would, if it isn't a Key, it doesn't violate the duplicate rule so the `on duplicate key` doesn't fire and you get 2 rows. A `key` is not a `field`, they have to be predefined — Forbs, Nov 18 '17 at 03:29
@Forbs This isn't referring to multiple statements. Just one. I've posted the structure above. You can see for yourself on a Linux server — iautomation, Nov 18 '17 at 03:48
As I said, there is no 'duplicate key' `test` is not a key...`id` is. And since it is an autoincrement, it will do 2, 3 ,100... You might want to brush up on how keys work.. You saying it working differently a linux server makes me suspicious about it. — Forbs, Nov 18 '17 at 06:31
@Forbs If you don't understand the post, don't comment. An `insert` statement creates one row, nothing to do with logic afterward. If that statement is run again, it will create two rows, yes that's absolutely true. That is not what this post is about. — iautomation, Nov 18 '17 at 07:38
Finally you explain it properly...a single run of a query causes 2 rows t o be inserted.. Well then something is wrong...majorly And the answer is do not use `INSERT .. ON DUPLICATE KEY UPDATE` with an AutoIncrement field...strange things happen. https://stackoverflow.com/questions/7087869/mysql-insert-on-duplicate-update-adds-one-to-the-autoincrement — Forbs, Nov 18 '17 at 23:48
How is your PHP is run, I mean is it just a page you hit? Also can you clarify which cases show the problem and which don't, eg what does "from PHP only" mean? — Don't Panic, Nov 21 '17 at 15:38
So a single simple INSERT will generate only one row? And what if you run that query on the same server without PHP? — Paul Spiegel, Nov 21 '17 at 17:10
@Don'tPanic an other-wise blank php page with the above example. the problem only happens from this server(xampp on windows) with both PDO and MySQLi. However running the from phpMyAdmin only inserts 1 row as it should. — iautomation, Nov 22 '17 at 17:37
@PaulSpiegel From phpMyAdmin it only inserts 1 row as it should. I'm guessing phpMyAdmin runs command-line. This could be relavant. — iautomation, Nov 22 '17 at 17:40

score 1 · Accepted Answer · answered Nov 21 '17 at 21:43

Perhaps you can test something by changing the code to the following:

$connection = new \PDO('mysql:dbname=XXXXXXXXX;host=XXXXXXXX', 'XXXXXXXX', 'XXXXXXXX');
$query = 'insert into `test` (test) VALUES (1) on duplicate key update test=1';
$connection->exec($query);

$query = 'insert into `test` (test) VALUES (2) on duplicate key update test=2';
$connection->exec($query);

Now the result can be one of the following:

1. 1 2

2. 1 1 2 2

3. 1 2 1 2

In the first case, everything is alright.

If the second case is inserted, then there is a bug in your system. Either the PDO driver or the MySQL server, but i'm quite sure it is some weird glitch in your installation. A fresh installation or a different machine would most likely not experience this problem.

If the third thing happens, then your PHP script is run twice. This could be a problem with the PHP installation itself, or it could be that for some reason there actually is a second call to the script, either a redirect or other setting.

Anyway, this is not something we can solve. The code you have is working fine. You can try a fresh install on a separate machine. Since I understand you use the same PHP server for all your test and different mysql servers, my bet would be that a fresh PHP server would solve this.

But a fresh install of both on another machine is always the best way to ensure it's actually a bug. In that case you can post a bug report with the system administrator or any of the tools involved.

You solved it Hugo. I had not stopped Apache & MySQL when uninstalling a previous Xampp installation. I'm guessing that was the cause :D — iautomation, Nov 22 '17 at 17:47

score 0 · Answer 2 · answered Nov 18 '17 at 16:59

0

To use IODKU, you must have a UNIQUE key on one (or a combination) of the columns being set. For the query provided, you would need this in the table definition:

UNIQUE(test)

On the other hand, perhaps you did not really need IODKU for what you are trying to do. What is the intent?

answered Nov 18 '17 at 16:59

Rick James

135,179
13
127
222

Thanks for the response. It seems this question is easily mis-interpretted. **I am aware that a key is required for the condition to insert/update against one row. This is not the intent and it is not the question**. Given the case that _a key is not used_, the IODKU statement should insert a row every time it is run. *It is inserting 2 rows after running 1 time* – iautomation Nov 18 '17 at 23:17
Right. Well this isn't an answer to the question/bug. – iautomation Nov 19 '17 at 14:11
I'll say again, the bug I'm referring to is that *after running 1 time* it is *inserting 2 rows*. In what world does an single _insert_ statement with a single set of fields create 2 rows ever? Please answer accordingly. – iautomation Nov 19 '17 at 14:23

MySQL ON DUPLICATE KEY UPDATE double insert on Xampp PHP7.0

2 Answers2