editing a row and using 'insert as new row' uses primary key 0

[aschuch247]

Describe the bug

If editing a row with an autoincrement primary key and using the option 'insert as new row', the new row will get the primary key 0 assigned.

To Reproduce

Steps to reproduce the behaviour:

0. Have the database server using SQL mode NO_AUTO_VALUE_ON_ZERO (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_value_on_zero).
1. Create a table with an autoincrement primary key id and a string name.
2. Insert some rows to the table.
3. Click on one row and select edit.
4. The bottom dialogue by default has save selected. Select insert as new row.
5. The column id now is automatically set to 0.
6. Press go to edit the row.

Expected behaviour

My intention is to edit a row and insert it as a new row. This works if the automatically added 0 to the column id is manually removed. I expect to not have an automatically filled out 0 in the column id as soon as I select insert as new row.

Server configuration

• Database version: MySQL 5.6 with NO_AUTO_VALUE_ON_ZERO enabled
• phpMyAdmin version: 4.8.5

Additional context

The SQL mode NO_AUTO_VALUE_ON_ZERO needs to be enabled in order to not interpret the given primary key 0 as a literal zero instead of an instruction to create a new primary key.

[aschuch247]

If the primary key NULL is used, a new autoincrement value will be created regardless of NO_AUTO_VALUE_ON_ZERO set. So the fool-proof way is to always provide NULL when inserting a new primary key (or do not specify the field at all).

[aschuch247]

I can still reproduce the error.

• phpMyAdmin: 4.9.1
• browser: Firefox 69.0 (64-bit)
• operating system: KDE neon

[yashrajbothra]

Hey @aschuch247 can u please tell me how u changed the SQL_MODE

As I am unable to reproduce the issue

OR

You just dont want 0 in id when we click on Insert as new Row

[aschuch247]

I can still reproduce the error.

• phpMyAdmin: 4.9.4
• browser: Firefox 72.0.1 (64-bit)
• operating system: KDE neon

[aschuch247]

@yashrajbothra: See the documentation at https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_value_on_zero.

SET GLOBAL sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

And actually, I want the primary key to either not be present in the created SQL statement, so that MySQL will create a new primary key and thus a new row, or that the used primary key is NULL, so that MySQL will create a new primary key and thus a new row.

[yashrajbothra]

Thanks you @aschuch247 :)

I think NULL will be good solution as it creates New primary key for every new row even in 'NO_AUTO_VALUE_ON_ZERO' mode.

Hey @williamdes is it Okay if we replace 0 to NULL for evey Insert new Row??

[williamdes]

Hey @yashrajbothra I think it would be okay but I need some documentation proof from MySQl and MariaDB to approve such a change

Can you please do some research?

[williamdes]

"NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number."

Approved change for MySQL

[yashrajbothra]

For MariaDB : Docs

cc @williamdes

[williamdes]

Thank you!
I think a PR can be made and merged into next patch version

[yashrajbothra]

Okay then I will Open a PR soon 👍

[aschuch247]

@williamdes: Is there any chance this one-line fix can be backported to phpMyAdmin 4.9 and released with any further security fix (in case such a security fix is ever required)?