Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 0.8.6

Request #13544 Default Timestamp Problems
Submitted: 2008-03-31 11:53 UTC
From: tacki Assigned:
Status: Open Package: MDB2_Schema (version 0.8.2)
PHP Version: 5.2.5 OS: Fedora Core 8
Roadmaps: (Not assigned)    
Subscription  


 [2008-03-31 11:53 UTC] tacki (Markus Schlegel)
Description: ------------ I don't know if this is a Problem of MDB2_Schema or MDB2 itself. I'd like to dump a Database from Postgre 8.2.7 to a XML and then import it into a MySQL5-Server. MDB2-version is 2.4.1. I run into Problems while exporting Tables with Timestamp-values: 1. Problem - A Timestamp-Field must have a default Value. That's ok, i used '1970-01-01 00:00:00' for this field and exported the DB. 2. Problem - I tried to import (with updateDatabase()) and got this failure: _doQuery: [Error message: Could not execute statement] [Last executed query: ALTER TABLE timers CHANGE id id VARCHAR(64) DEFAULT ' ' NOT NULL, CHANGE completiontime completiontime DATETIME DEFAULT '\'1970-01-01 00:00:00\'::timestamp without time zone' NOT NULL] [Native code: 1067] [Native message: Invalid default value for 'completiontime'] Looks like MySQL doesn't understand the Postgre-Timestamp Handling, or this Value is not correctly handled by the MDB2-Postgre-Driver. So if i use a defaultvalue, i'll run into Problems during the Import, and if i won't use a defaultvalue, i'll run into Problems during Export. I changed the Validate.php to allow empty-values (which are then set to default '1970-01-01 00:00:00' by MDB2_Schema), but if i set a non-empty value at Postgres, i'll still get an Error, because the MDB2_MySQL-Driver can't handle it. Test script: --------------- 1. Postgre-Tabledata: CREATE TABLE timers ( id character varying(64) NOT NULL, completiontime timestamp without time zone NOT NULL DEFAULT '1970-01-01 00:00:00'::timestamp without time zone, CONSTRAINT timers_pkey_idx PRIMARY KEY (id) ) 2. Dump it to a XML with getDefinitionFromDatabase() and dumpDatabase() 3. Import the XML to a MySQL-Database with updateDatabase() Expected result: ---------------- No Error Message Actual result: -------------- _doQuery: [Error message: Could not execute statement] [Last executed query: ALTER TABLE timers CHANGE id id VARCHAR(64) DEFAULT ' ' NOT NULL, CHANGE completiontime completiontime DATETIME DEFAULT '\'1970-01-01 00:00:00\'::timestamp without time zone' NOT NULL] [Native code: 1067] [Native message: Invalid default value for 'completiontime']

Comments

 [2008-11-23 20:34 UTC] hschletz (Holger Schletz)
Looks like a symptom of bug #14510 in the pgsql driver which produces this: <default>&apos;1970-01-01 00:00:00&apos;</default> In that case it's not a bug in MDB2_Schema.