#2945 Copying table with bit field with default

3.5.8
works-for-me
nobody
1
2013-07-27
2009-11-14
Don Hughes
No

Error copying a table with a bit field with a default value. Copy works if I remove the default. Notice that the 'b' for the bit field is jambed right next to the DEFAULT keyword with no space.

CREATE TABLE `Test`.`test` (
`Date` date NOT NULL DEFAULT '2009-01-01',
`Start` time NOT NULL DEFAULT '00:00:00',
`Stop` time NOT NULL DEFAULT '00:00:00',
`Vehicle` varchar( 8 ) NOT NULL DEFAULT ' ',
`Job` varchar( 8 ) NOT NULL DEFAULT ' ',
`ChangeMask` bit( 8 ) NOT NULL DEFAULTb '0',
`LastSeen` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
PRIMARY KEY ( `Date` , `Start` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULTb '0',
`LastSeen` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
P' at line 6

Discussion

  • Marc Delisle
    Marc Delisle
    2009-11-15

    • assigned_to: nobody --> lem9
     
  • Marc Delisle
    Marc Delisle
    2009-11-15

    • summary: SQL error when exporting table with bit field with default --> Copying table with bit field with default
     
  • Marc Delisle
    Marc Delisle
    2009-11-15

    Fixed in subversion, thanks for reporting.

     
  • Marc Delisle
    Marc Delisle
    2009-11-15

    • priority: 5 --> 1
    • summary: Copying table with bit field with default --> (ok 3.2.4) Copying table with bit field with default
     
  • Marc Delisle
    Marc Delisle
    2009-12-02

    • status: open --> closed
     
  • The bug is back (3.5.8 & 4.0.0-rc1) :

    Create this database :

    CREATE DATABASE test;
    USE test;
    CREATE TABLE test (test bit( 6 ) DEFAULT '0');

    And try to copy this database to another and you will get this :

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'defaultb '110000' ) ENGINE = MyISAM DEFAULT CHARSET = latin1' at line 1

    Because phpmyadmin try to do this :

    CREATE TABLE test2.test (

    test BIT( 6 ) defaultb '110000'
    ) ENGINE = MYISAM DEFAULT CHARSET = latin1

    Note the defaultb.

    Thank you and have nice day !
    Julien

     
  • Marc Delisle
    Marc Delisle
    2013-04-09

    • summary: (ok 3.2.4) Copying table with bit field with default --> Copying table with bit field with default
    • status: closed --> open
    • milestone: --> 3.5.8
     
  • Marc Delisle
    Marc Delisle
    2013-04-09

    • assigned_to: Marc Delisle --> nobody
     
  • Marc Delisle
    Marc Delisle
    2013-04-09

    Julien,
    I cannot reproduce this bug with 3.5.8 nor with 4.0.0-rc1.

    I am using MySQL 5.6.4, PHP 5.4.13 with the mysqli extension and mysqlnd. Please describe your environment.

     
  • Hi,

    Sorry for the noise, feel free to close the ticket again :
    We have something like 300 PHPMyAdmin and I don't remember with which one I had the problem :)
    The only thing I know, is that it's was with an old MySQL (4.x or 5.0.x).

    Thank you

     
  • Marc Delisle
    Marc Delisle
    2013-07-27

    • status: open --> works-for-me