backups created with DBA module have some bogus default values

great suggestion to look at the table in the DB. it's definitely wrong...

here's the output of the sessions table on my 4.7 test site, which is using MySQL 5.0.16:

mysql> describe sessions;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| uid       | int(10) unsigned | NO   | MUL |         |       |
| sid       | varchar(32)      | NO   | PRI |         |       |
| hostname  | varchar(128)     | NO   |     |         |       |
| timestamp | int(11)          | NO   | MUL | 0       |       |
| cache     | int(11)          | NO   |     | 0       |       |
| session   | longtext         | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

here's the output from my live 4.6 site's sessions table (also MySQL 5.0.16):

mysql> describe sessions;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| uid       | int(10) unsigned | NO   | MUL |         |       |
| sid       | varchar(32)      | NO   | PRI |         |       |
| hostname  | varchar(128)     | NO   |     |         |       |
| timestamp | int(11)          | NO   | MUL | 0       |       |
| session   | longtext         | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

looking at both on another test site (on my laptop) running MySQL 4.1.18, the schema agrees exactly with what you previously posted. so, somehow, the default for uid is getting lost in MySQL 5.0, but still works in 4.0. weird! any suggestions on where to debug from here?

it still seems like a bug in the dba module that it's writing out a default value of an empty string for a field without a default (especially if the field is an unsigned int). but something more fundamental also seems broken here... i'm not really sure where to look next. let me know if i can be of any further assisstance tracking this down, if you want me to test anything in MySQL 5, etc.

thanks!
-derek

In any case, the real bug is in the dba module, as it's detecting a default when there isn't one

i finally had a chance to look at this closely, and it seems the "real" bug might be in MySQL itself. :( here's what my debugging found...

basically, all the DESCRIBE output from above is still true. however, when we're in dba_backup_table() and we're trying to create the right DB schema, MySQL is returning misleading things that are confusing our code. :( in particular, i added a bunch of debugging watchdog messages when we're dealing with $row['Default'] and it's not pretty. :(

for the integer uid field, the row we get back from db_fetch_row() from our DESCRIBE query is as follows:
array ( 'Field' => 'uid', 'Type' => 'int(10) unsigned', 'Null' => 'NO', 'Key' => 'MUL', 'Default' => '', 'Extra' => '', )

since it's an int field, MySQL is treating the default as 0 (easily verifiable if you insert a row into the table without specifying a uid), but it's returning the default as an empty string. :( this of course causes havoc for our code that's trying to handle real empty strings as defaults for string fields.

a few interesting (but ultimately, not going to solve our problems) things to read:
http://sql-info.de/mysql/gotchas.html#1_1
http://bugs.mysql.com/bug.php?id=21618

the only thing i can think of to handle this problem is to use logic where if Default is an empty string, and NULL isn't YES, then we should check the Type and if it's any of the possible numeric types, we leave off specifying an empty string as the default. seems like a hack, but i can't see any way to get around it if MySQL is going to return '' for the default in this case, not 0 (which is what it's actually using) or NULL (if you didn't specify in your schema). :(

the attached patch seems to work, but it makes me feel dirty. ;) i decided not to just append a default '0' in this case (which is what MySQL is really doing), and instead just left the case completely blank with a big comment about why. alternatively, we could combine this else if clause with the final one, but that made the line so long it was harder to read and i don't think putting them together buys us anything in terms of speed or clarity.

let me know what you think. i'd like to apply this to all 3 branches (4.6, 4.7 and HEAD) if you agree this isn't a completely insane workaround for buggy MySQL behavior.

thanks,
-derek

sweet, thanks!
-derek

p.s. today is my 1-year anniversary from when i created my drupal.org account. ;)