Textpattern CMS

#1 2006-11-28 16:10:48

joeymarchy

New Member

Registered: 2005-04-26

Posts: 3

Problem importing txp_lang table

I exported my TXP database from PHPMyAdmin using the export to file feature. When I try to import my database I consistently get an error when trying to import the txp_lang table.

Here is the table I try to import.
CREATE TABLE `txp_lang` (
`id` int(9) NOT NULL auto_increment,
`lang` varchar(16) default NULL,
`name` varchar(64) default NULL,
`event` varchar(64) default NULL,
`data` tinytext,
`lastmod` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `lang` (`lang`,`name`),
KEY `lang_2` (`lang`,`event`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 AUTO_INCREMENT=1007 ;

I can get the table to import if I remove/change some items in the SQL statement. And here is where my question lies. The problem seems to be three-fold. If I make the three changes below, the table imports without any errors:

1. Change ENGINE=MyISAM to TYPE=MyISAM
2. Delete DEFAULT CHARSET=latin1
3. Remove the commands: default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Resulting in the following SQL statement:

CREATE TABLE `txp_lang` (
`id` int( 9 ) NOT NULL AUTO_INCREMENT ,
`lang` varchar( 16 ) default NULL ,
`name` varchar( 64 ) default NULL ,
`event` varchar( 64 ) default NULL ,
`data` tinytext,
`lastmod` timestamp NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE KEY `lang` ( `lang` , `name` ) ,
KEY `lang_2` ( `lang` , `event` )
) DELAY_KEY_WRITE =1 AUTO_INCREMENT =1007;

Is there a way to change the ENGINE to TYPE when I export?
Is there a way to export without the DEFAULT CHARSET=latin1 in the SQL statement?
What can be done about the issue with “default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP”?

Is it a MySQL version issue? The version of MySQL I am using is: 3.23.58

#2 2006-11-28 16:25:38

joeymarchy

New Member

Registered: 2005-04-26

Posts: 3

Re: Problem importing txp_lang table

I found this on the MySQL site
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

1. With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

2. With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

So it seems like changing the line:
`lastmod` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

to

`lastmod` timestamp NOT NULL,

will no have any effect on what goes into the table.

#3 2006-11-28 17:42:45

Mary

Sock Enthusiast

Registered: 2004-06-27

Posts: 6,236

Re: Problem importing txp_lang table

…I consistently get an error…

What error(s)?

#4 2006-11-28 17:47:55

joeymarchy

New Member

Registered: 2005-04-26

Posts: 3

Re: Problem importing txp_lang table

Mary,

Here is the error I receive:

MySQL said: Documentation

1064. - You have an error in your SQL syntax near ‘CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY ‘ at line 7

CREATE TABLE `txp_lang` (
`id` int( 9 ) NOT NULL AUTO_INCREMENT ,
`lang` varchar( 16 ) default NULL ,
`name` varchar( 64 ) default NULL ,
`event` varchar( 64 ) default NULL ,
`data` tinytext,
`lastmod` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` ) ,
UNIQUE KEY `lang` ( `lang` , `name` ) ,
KEY `lang_2` ( `lang` , `event` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 DELAY_KEY_WRITE =1 AUTO_INCREMENT =1007

I also receive the notice about “an error in your SQL syntax” for the line that contains:
ENGINE = MYISAM DEFAULT CHARSET = latin1 DELAY_KEY_WRITE =1 AUTO_INCREMENT =1007

It never specifies any additional information other than “an error in your SQL syntax”. Which is not very helpful.

Last edited by joeymarchy (2006-11-28 17:49:44)

#5 2006-11-29 15:27:42

onthink

New Member

Registered: 2006-11-29

Posts: 2

Re: Problem importing txp_lang table

I can not find the error either, shame

#6 2006-11-29 16:27:48

ruud

Developer Emeritus

From: a galaxy far far away

Registered: 2006-06-04

Posts: 5,068

Website

Re: Problem importing txp_lang table

See MySQL changelog 4.1.2

Support for “default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP” was added in MySQL 4.1.2, so it will cause errors on older versions that do not support it.

Last edited by ruud (2006-11-29 16:28:21)

#7 2006-11-29 18:59:59

Mary

Sock Enthusiast

Registered: 2004-06-27

Posts: 6,236

Re: Problem importing txp_lang table

Is it a MySQL version issue?

Yep: you’ve exported your database in a MySQL version higher than the version you are trying to import into.