public inbox for gentoo-user@lists.gentoo.org
 help / color / mirror / Atom feed
* [gentoo-user] php CURRENT_TIMESTAMP vs NOW()
@ 2012-12-28  6:44 Joseph
  2012-12-28  7:00 ` Michael Orlitzky
  0 siblings, 1 reply; 7+ messages in thread
From: Joseph @ 2012-12-28  6:44 UTC (permalink / raw
  To: gentoo-user

I'm not a PHP programmer but I'll try to explain my problem.
I've create table in my php database:

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)
);

It worked OK, after few days I backup my database and try to restore it, but it keeps complaining on the "dt":
ERROR 1067 (42000) at line 38009: Invalid default value for 'dt'

so the database is dropped but never restored. The backup data base contain:

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)
);

so the difference is:

  dt TIMESTAMP(12) NOT NULL DEFAULT NOW(),
vs
  dt timestamp default 'CURRENT_TIMESTAMP' not null ,

If I change in backup database the line to: "dt TIMESTAMP(12) NOT NULL DEFAULT NOW(),"
I can restore it without problems.  

Why is it doing it?  
In "dt" table structure the "default" has option:
- None
- As Defined:
- NULL
- CURRENT_TIMESTAMP

If I change setting from "CURRENT_TIMESTAMP" to any of the above will it help restore it correctly? 

-- 
Joseph


^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: [gentoo-user] php CURRENT_TIMESTAMP vs NOW()
  2012-12-28  6:44 [gentoo-user] php CURRENT_TIMESTAMP vs NOW() Joseph
@ 2012-12-28  7:00 ` Michael Orlitzky
  2012-12-28 15:56   ` Joseph
  0 siblings, 1 reply; 7+ messages in thread
From: Michael Orlitzky @ 2012-12-28  7:00 UTC (permalink / raw
  To: gentoo-user

On 12/28/2012 01:44 AM, Joseph wrote:
> I'm not a PHP programmer but I'll try to explain my problem.
> I've create table in my php database:
> 
> 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)
> );
> 

Looks fine.


> It worked OK, after few days I backup my database and try to restore it, but it keeps complaining on the "dt":
> ERROR 1067 (42000) at line 38009: Invalid default value for 'dt'
> 
> so the database is dropped but never restored. The backup data base contain:
> 
> 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)
> );


CURRENT_TIMESTAMP shouldn't be quoted. How are you backing up the database?


^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: [gentoo-user] php CURRENT_TIMESTAMP vs NOW()
  2012-12-28  7:00 ` Michael Orlitzky
@ 2012-12-28 15:56   ` Joseph
  2012-12-28 16:06     ` Michael Orlitzky
  0 siblings, 1 reply; 7+ messages in thread
From: Joseph @ 2012-12-28 15:56 UTC (permalink / raw
  To: gentoo-user

On 12/28/12 02:00, Michael Orlitzky wrote:
>On 12/28/2012 01:44 AM, Joseph wrote:
>> I'm not a PHP programmer but I'll try to explain my problem.
>> I've create table in my php database:
>>
>> 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)
>> );
>>
>
>Looks fine.
>
>
>> It worked OK, after few days I backup my database and try to restore it, but it keeps complaining on the "dt":
>> ERROR 1067 (42000) at line 38009: Invalid default value for 'dt'
>>
>> so the database is dropped but never restored. The backup data base contain:
>>
>> 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)
>> );
>
>
>CURRENT_TIMESTAMP shouldn't be quoted. How are you backing up the database?

Your are correct, when I removed the quotes it worked.

I'm backing it up through the backup.php sript that came with osCommerce, I can post it but it is a long one.

-- 
Joseph


^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: [gentoo-user] php CURRENT_TIMESTAMP vs NOW()
  2012-12-28 15:56   ` Joseph
@ 2012-12-28 16:06     ` Michael Orlitzky
  2012-12-28 17:51       ` Joseph
  0 siblings, 1 reply; 7+ messages in thread
From: Michael Orlitzky @ 2012-12-28 16:06 UTC (permalink / raw
  To: gentoo-user

On 12/28/12 10:56, Joseph wrote:
> 
> Your are correct, when I removed the quotes it worked.
> 
> I'm backing it up through the backup.php sript that came with
> osCommerce, I can post it but it is a long one.
> 

I am... familiar... with osCommerce. You will be much better off doing a
mysqldump if you have access. You can run it either on the server or
remotely if the MySQL ports on the server are open.


^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: [gentoo-user] php CURRENT_TIMESTAMP vs NOW()
  2012-12-28 16:06     ` Michael Orlitzky
@ 2012-12-28 17:51       ` Joseph
  2012-12-28 18:00         ` Michael Orlitzky
  0 siblings, 1 reply; 7+ messages in thread
From: Joseph @ 2012-12-28 17:51 UTC (permalink / raw
  To: gentoo-user

On 12/28/12 11:06, Michael Orlitzky wrote:
>On 12/28/12 10:56, Joseph wrote:
>>
>> Your are correct, when I removed the quotes it worked.
>>
>> I'm backing it up through the backup.php sript that came with
>> osCommerce, I can post it but it is a long one.
>>
>
>I am... familiar... with osCommerce. You will be much better off doing a
>mysqldump if you have access. You can run it either on the server or
>remotely if the MySQL ports on the server are open.

Yes, I run osCommerce on my server.
Is the manuall command:

mysqldump --opt -ppassword catalog > catalog_backup.sql

-- 
Joseph


^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: [gentoo-user] php CURRENT_TIMESTAMP vs NOW()
  2012-12-28 17:51       ` Joseph
@ 2012-12-28 18:00         ` Michael Orlitzky
  2012-12-28 18:23           ` Joseph
  0 siblings, 1 reply; 7+ messages in thread
From: Michael Orlitzky @ 2012-12-28 18:00 UTC (permalink / raw
  To: gentoo-user

On 12/28/12 12:51, Joseph wrote:
> 
> Yes, I run osCommerce on my server.
> Is the manuall command:
> 
> mysqldump --opt -ppassword catalog > catalog_backup.sql
> 

I think --opt is on by default, but yes, that should do it. If you would
like to automate the backup (say, nightly), you can add the following to
~/.my.cnf [1]:

  [mysqldump]
  user     = <your mysql username>
  password = <your mysql password>

Then, when you run the `mysqldump` command, it will use that username
and password automatically (and not prompt you). That way you can make
the backups in a cron job.


[1] Warning: chmod 600 the ~/.my.cnf file if you create one.


^ permalink raw reply	[flat|nested] 7+ messages in thread

* Re: [gentoo-user] php CURRENT_TIMESTAMP vs NOW()
  2012-12-28 18:00         ` Michael Orlitzky
@ 2012-12-28 18:23           ` Joseph
  0 siblings, 0 replies; 7+ messages in thread
From: Joseph @ 2012-12-28 18:23 UTC (permalink / raw
  To: gentoo-user

On 12/28/12 13:00, Michael Orlitzky wrote:
>On 12/28/12 12:51, Joseph wrote:
>>
>> Yes, I run osCommerce on my server.
>> Is the manuall command:
>>
>> mysqldump --opt -ppassword catalog > catalog_backup.sql
>>
>
>I think --opt is on by default, but yes, that should do it. If you would
>like to automate the backup (say, nightly), you can add the following to
>~/.my.cnf [1]:
>
>  [mysqldump]
>  user     = <your mysql username>
>  password = <your mysql password>
>
>Then, when you run the `mysqldump` command, it will use that username
>and password automatically (and not prompt you). That way you can make
>the backups in a cron job.
>
>
>[1] Warning: chmod 600 the ~/.my.cnf file if you create one.

Thank you, that will help.
I'm stuck with oSCommerce 2.2rc2 as they don't want to put visa module in the new oSC ver. 3

....{@} * {@} * {@}         Happy New Year!
{@} * {@} * {@} * {@}       
     {@} * {@} * {@}       
     \ \ \ 2013 / / /

-- 
Joseph


^ permalink raw reply	[flat|nested] 7+ messages in thread

end of thread, other threads:[~2012-12-28 18:23 UTC | newest]

Thread overview: 7+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2012-12-28  6:44 [gentoo-user] php CURRENT_TIMESTAMP vs NOW() Joseph
2012-12-28  7:00 ` Michael Orlitzky
2012-12-28 15:56   ` Joseph
2012-12-28 16:06     ` Michael Orlitzky
2012-12-28 17:51       ` Joseph
2012-12-28 18:00         ` Michael Orlitzky
2012-12-28 18:23           ` Joseph

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox