Menu ▾ ▴

#4089 (ok 4.0.7) Moving Columns will alter column definition

4.0.6
fixed
Madhura Jayaratne
moving (1) column (1) change (1) definition (1)
1
2013-09-23
2013-09-13
Frederic Steinfels
No

Using MySQL 5.6.13, PHPmyAdmin 4.0.6, InnoDB
This Version of MySQL more strict than older versions and does not allow inserting rows if one column is not defined in the query and has no default value in the table definition. Unfortunately, moving columns will erase the default value of VARCHAR columns, at least if that default value is '' (empty string). There is a big difference between empty string and nothing set at all, at least for MySQL 5.6.x.

Furthermore if you have a 'CURRENT_TIMESTAMP' default value in a 'DATETIME' column, phpmyadmin will report an error message (invalid sql statement) when moving around. Removing the default value, moving around and setting it back afterwards is a workaround.

Please review your whole column moving code. I assume the bugs are even more general than what I figured out.

Discussion

  • Madhura Jayaratne

    Madhura Jayaratne - 2013-09-13
    • assigned_to: Madhura Jayaratne
     

  • Madhura Jayaratne

    Madhura Jayaratne - 2013-09-13
    • summary: Moving Columns will alter column definition --> (ok 4.0.7) Moving Columns will alter column definition
    • status: open --> resolved
    • Priority: 5 --> 1
     

  • Marc Delisle

    Marc Delisle - 2013-09-23
    • Status: resolved --> fixed