Phpmyadmin dump, SET SQL_MODE

[datadriven]

Recently migrated to a new host, and noticed in the first dump file, top identifier section:

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;

What is this value? (layman’s terms, please)

The phpmyadmin versions between the hosts appear mainly the same.
(new ver 2.11.9.5, prior host ver 2.6.3 -pl1)

Only difference I see is that the new version says:
“Use hexadecimal for BLOB”

While the corresponding choice in the earlier phpmyadmin read:
“Use hexadecimal for binary fields”

And the later version allows a field entry for "Maximal length of created query (preset at 50000)What does this represent?

[r937]

@datadriven

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;

What is this value? (layman’s terms, please)

here ya go:

@da Manual

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. (New in MySQL 4.1.1) This mode can be useful if 0 has been stored in a table’s AUTO_INCREMENT column. (This is not a recommended practice, by the way.)

regarding your other question, i believe 50000 is the number of bytes that the length of the sql string cannot exceed

[datadriven]

thanks rudy,

first, ‘0’ values have been stored in some of my columns, on tables having auto increment.

when I restored to the new host from my dump file (old host having mysql 4.1.21) do you think there is a reason for concern or should I look for something on the new tables to confirm the transfer (even though both old and new databases match at 15.3 MB, might any slight change be revealed in the individual table kb’s? ) - asking because the restored dump did not have NO_AUTO_VALUE_ON_ZERO stated.

and that other question, 50000 as the number of bytes the length of the sql string cannot exceed
what is considered the sql string. Can that figure be part of the returned dump? (choice appears among the data selections among the phpmyadmin dump menu)

[r937]

@datadriven

might any slight change be revealed in the individual table kb’s?

no, because an integer takes 4 bytes regardles of whether it holds a number like 0 or a number like 1,937,937

as for the sql string, here are some examples –

SELECT foo FROM bar – 19 characters

SELECT COUNT(*) FROM bar – 24 characters

SELECT really_long_column_name FROM really_long_table_name – 59 characters

[longneck]

@datadriven

first, ‘0’ values have been stored in some of my columns, on tables having auto increment.

NO_AUTO_VALUE_ON_ZERO does not prevent 0 in an auto_increment column. it changes the behavior of inserting a 0 in to an auto_increment column. with that mode off, inserting a 0 causes auto_increment to generate a value. with that mode on, inserting a 0 results in a 0.

@datadriven

when I restored to the new host from my dump file (old host having mysql 4.1.21) do you think there is a reason for concern or should I look for something on the new tables to confirm the transfer (even though both old and new databases match at 15.3 MB, might any slight change be revealed in the individual table kb’s? ) - asking because the restored dump did not have NO_AUTO_VALUE_ON_ZERO stated.

different backup utilities and different settings and different server versions will result in non-identical backups. if you want identical backups, you have to match all three of those.

[datadriven]

Thank you longneck,

NO_AUTO_VALUE_ON_ZERO does not prevent 0 in an auto_increment column. it changes the behavior of inserting a 0 in to an auto_increment column. with that mode off, inserting a 0 causes auto_increment to generate a value. with that mode on, inserting a 0 results in a 0.

Please verify; is NO_AUTO_VALUE_ON_ZERO already considerd in off mode or on? How or where is this toggled?

If I want ‘0’'s entered in columns on autoincrement, such as those having ‘0’ by table default already, how is the new table kept this way?

[longneck]

NO_AUTO_VALUE_ON_ZERO is off by default. it is very rare to actually have it turned on at the server level, and is usually used only in SQL dumps.

NO_AUTO_VALUE_ON_ZERO only affects what happens during an insert. turning the mode off does not affect rows that are already in a table.

[datadriven]

thankyou longneck/rudy, much appreciate your help here.

[r937]

FYI, you should not use 0 in any auto_increment column

if you ~need~ a 0 value, something is wrong