MySQL: #1293 - Incorrect table definition

Question

Designed Database and Table in Local Server Using MySQL 5.6.17 and Export into *.sql file.

When try to Import *.sql file into Live Server(MySQL 5.1.36) got below Error:

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Understood the issue through this link

Is there any way to import Local Server's *.sql file to Live Server without Updating MySQL Version?

TABLE:

            CREATE TABLE 'currency' (
          'id' int(11) NOT NULL AUTO_INCREMENT,
          'currency_name' varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT
         NULL,
          'country' varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
          'currency' varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
          'created_by' int(11) NOT NULL,
          'created_on' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          'status' int(1) NOT NULL DEFAULT '1',
          'modified_by' int(11) DEFAULT NULL,
          'modified_on' timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY ('id')) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

NOTE:- Windows Server Running WAMP(Local Server) and QNAP(Live Server).

score 1 · Accepted Answer · answered May 13 '15 at 12:40

There is not a direct way to do it, since this is not a valid table definition for any version prior to MySQL Server 5.6.5.

You could edit the dump file by hand or with a tool like sed or perl to modify the offending lines, or you could change the table definition on the source server... but then your application, which presumably expects this behavior, isn't going to work properly.

You could also modify the table definition to make it valid for 5.1, with only one automatic timestamp, and use triggers to get the rest of the desired behavior.

The best course, of course, is one of these:

update the 5.1 server to 5.5 and then to 5.6, or
when developing for a 5.1 server, always use 5.1 in the development environment, so you don't get into conditions like this, relying on newer features that aren't compatible with older deployments... remembering, though, that there are some pretty significant improvements that happened in version 5.6.

MySQL: #1293 - Incorrect table definition

1 Answers1