VVC_VERSION_3.2 addition - Invalid default value for 'dt'

[syscon]

I've installed VVC_VERSION_3.2 and it is working OK but when I backup the database and try to restore it on another system I get an error:

 

ERROR 1067 (42000) at line 38009: Invalid default value for 'dt'

 

Line 38009 is: create table visual_verify_code (

 

The entire section:

drop table if exists visual_verify_code;
create table visual_verify_code (
oscsid varchar(32) not null ,
code varchar(6) not null ,
dt timestamp default 'CURRENT_TIMESTAMP' not null ,
PRIMARY KEY (oscsid)
);

 

The table gets dropped but is not created. What would be the problem?

Do we need to store those temporary codes in database?

[syscon]

I know why is it doing it but I don't know how to fix it :-/

 

Orignal instruciton from the contribution was to creat the table:

DROP TABLE IF EXISTS visual_verify_code;
CREATE TABLE visual_verify_code (
 oscsid varchar(32) NOT NULL,
 code varchar(6) NOT NULL,
 dt TIMESTAMP(12) NOT NULL DEFAULT NOW(),
 PRIMARY KEY (oscsid)
);

 

When I run the server for few days and back it up the instruction had changed to:

create table visual_verify_code (
 oscsid varchar(32) not null ,
 code varchar(6) not null ,
 dt timestamp default 'CURRENT_TIMESTAMP' not null ,
 PRIMARY KEY (oscsid)
);

 

The original instruction was:

dt TIMESTAMP(12) NOT NULL DEFAULT NOW(),

 

after backup, my server change it to:

dt timestamp default 'CURRENT_TIMESTAMP' not null ,

 

Why is it doing it? How to fix it?

[MrPhil]

Interesting. Does it actually say the string CURRENT_TIMESTAMP, or is it an integer value like 1235034744? I would have a chat with your host about the software they use to generate backups. According to the MySQL support forums, the constant CURRENT_TIMESTAMP (no quotes) is the correct format for a CREATE TABLE -- their backup software has a bug. For an INSERT statement, now() is the normal way of doing it, but I'm not sure that's valid for CREATE TABLE.

[syscon]

It seems to me the OSC backup.php icorreclty puts quotes around: 'CURRENT_TIMESTAMP'

When I remove the quotes I can restore it.

[syscon]

Interesting. Does it actually say the string CURRENT_TIMESTAMP, or is it an integer value like 1235034744? I would have a chat with your host about the software they use to generate backups. According to the MySQL support forums, the constant CURRENT_TIMESTAMP (no quotes) is the correct format for a CREATE TABLE -- their backup software has a bug. For an INSERT statement, now() is the normal way of doing it, but I'm not sure that's valid for CREATE TABLE.

 

To make a backup I'm using OSC backup.php located in amdin/ so it is the backup.php that generate backup.

[MrPhil]

OK, I will try to remember to look at the code tonight when I get home. This may be a special case where the backup.php needs to be told to leave CURRENT_TIMESTAMP alone and not put quotes around it.

[syscon]

Interesting. Does it actually say the string CURRENT_TIMESTAMP, or is it an integer value like 1235034744? I would have a chat with your host about the software they use to generate backups. According to the MySQL support forums, the constant CURRENT_TIMESTAMP (no quotes) is the correct format for a CREATE TABLE -- their backup software has a bug. For an INSERT statement, now() is the normal way of doing it, but I'm not sure that's valid for CREATE TABLE.

 

Yes, when I make a backup with backup.php via oscommerce this is what gets dump:

drop table if exists visual_verify_code;
create table visual_verify_code (
 oscsid varchar(32) not null ,
 code varchar(6) not null ,
 dt timestamp default 'CURRENT_TIMESTAMP' not null ,
 PRIMARY KEY (oscsid)
);
insert into visual_verify_code (oscsid, code, dt) values ('troldn0333qs7qn3sv13f5dpt3', 'ESp', '2012-12-25 14:49:24');
insert into visual_verify_code (oscsid, code, dt) values ('tebon75ir3k8uqdm0qc54qh065', 'EAY', '2012-12-23 20:11:51');

 

Instead of manually removing the qoutes, I think it might be better to use as root:

mysqldump --opt -ppassword catalog > catalog_dump.sql

[MrPhil]

All I have available to look at is osc 2.3.3, but it seems to have the same problem as 2.2, so hopefully the following fix will work.

 

Find in admin/backup.php the following line:

		 if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';

The problem is that it always puts single quotes around the default value.

 

Try changing that line to:

		 if (strlen($fields['Default']) > 0) {
			 if ($fields['Default'] == 'CURRENT_TIMESTAMP')
				 $schema .= ' default ' . $fields['Default'];
			 else
 	 $schema .= ' default \'' . $fields['Default'] . '\'';
 }

 

Of course, back up your backup.php file first, in case it doesn't work and you need to restore it. If it does work OK, there may be other special cases that need the same kind of treatment.

[syscon]

All I have available to look at is osc 2.3.3, but it seems to have the same problem as 2.2, so hopefully the following fix will work.

 

Find in admin/backup.php the following line:

		 if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';

The problem is that it always puts single quotes around the default value.

 

Try changing that line to:

		 if (strlen($fields['Default']) > 0) {
			 if ($fields['Default'] == 'CURRENT_TIMESTAMP')
				 $schema .= ' default ' . $fields['Default'];
			 else
 	 $schema .= ' default \'' . $fields['Default'] . '\'';
 }

 

Of course, back up your backup.php file first, in case it doesn't work and you need to restore it. If it does work OK, there may be other special cases that need the same kind of treatment.

 

SOLVED.

Thank you Phil, that seems to be working.

May I ask a question?

Why not use standard "mysqldump" as a backup, just curious?

 

....{@} * {@} * {@} Happy New Year!

{@} * {@} * {@} * {@} Wish you extra ordinary good luck!

{@} * {@} * {@}

\ \ \ 2013 / / /

[syscon]

I forgot to mention. Using "mysqldump" my backup file is 4Mb instead of 11Mb using osc backup.php

[MrPhil]

It's almost always better to use standard tools such as mysqldump rather than something cobbled together by application programmers. I don't know why people feel it is necessary to duplicate standard system utilities, which have been honed and polished over years. Even something like mysqldump should be runnable from within the application.