Backu/restore

[mreaves53]

When I export my database as a sql statement and then try to do a restore I am erorring out when ever I encounter a default value in a table.

I am using MySQL version 5.0 and SQLyog version 5.02.

Here is the exact error message:

Error occured at:2006-06-05 10:55:46

Line no.:962

Error Code: 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default 19' at line 2

Here is the object info on the table that the error occurs in while creating.

/*Column Information For – ttd.master_projects*/

---

Field Type Collation Null Key Default Extra Privileges Comment

---

---

---

---

---

---

---

---

---

no varchar(15) latin1_swedish_ci NO PRI select,insert,update,references

name varchar(100) latin1_swedish_ci YES (NULL) select,insert,update,references

description longtext latin1_swedish_ci YES (NULL) select,insert,update,references

psd date (NULL) YES 1900-01-01 select,insert,update,references

asd date (NULL) YES 1900-01-01 select,insert,update,references

pfd date (NULL) YES 1900-01-01 select,insert,update,references

afd date (NULL) YES 1900-01-01 select,insert,update,references

manager varchar(15) latin1_swedish_ci YES (NULL) select,insert,update,references

number_developers int(2) (NULL) YES 0 select,insert,update,references

pcost varchar(10) latin1_swedish_ci YES 0 select,insert,update,references

tcost varchar(10) latin1_swedish_ci YES (NULL) select,insert,update,references

printcost varchar(10) latin1_swedish_ci YES (NULL) select,insert,update,references

lcost varchar(10) latin1_swedish_ci YES (NULL) select,insert,update,references

ocost varchar(10) latin1_swedish_ci YES (NULL) select,insert,update,references

phours int(2) (NULL) YES 0 select,insert,update,references

ahours varchar(5) latin1_swedish_ci YES 0 select,insert,update,references

projstatus char(1) latin1_swedish_ci YES O select,insert,update,references

requestor varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references

tc varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references

owner char(1) latin1_swedish_ci YES (NULL) select,insert,update,references

development char(1) latin1_swedish_ci YES n select,insert,update,references

coordinators char(1) latin1_swedish_ci YES n select,insert,update,references

multimedia char(1) latin1_swedish_ci YES n select,insert,update,references

admin char(1) latin1_swedish_ci YES n select,insert,update,references

it char(1) latin1_swedish_ci YES n select,insert,update,references

maintance char(1) latin1_swedish_ci YES n select,insert,update,references

finance char(1) latin1_swedish_ci YES n select,insert,update,references

night char(1) latin1_swedish_ci YES n select,insert,update,references

prop_dt date (NULL) YES 1900-01-01 select,insert,update,references

denied_dt date (NULL) YES 1900-01-01 select,insert,update,references

hold_dt date (NULL) YES 1900-01-01 select,insert,update,references

resume_dt date (NULL) YES 1900-01-01 select,insert,update,references

changelog timestamp (NULL) YES CURRENT_TIMESTAMP select,insert,update,references

acost varchar(10) latin1_swedish_ci YES (NULL) select,insert,update,references

/*Index Information For – ttd.master_projects*/

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

master_projects 0 PRIMARY 1 no A 312 (NULL) (NULL) BTREE

/*DDL Information For – ttd.master_projects*/

---

Table Create Table

---

---

master_projects CREATE TABLE `master_projects` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`phours` int(2) default '0',

`ahours` varchar(5) default '0',

`projstatus` char(1) default 'O',

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`owner` char(1) default NULL,

`development` char(1) default 'n',

`coordinators` char(1) default 'n',

`multimedia` char(1) default 'n',

`admin` char(1) default 'n',

`it` char(1) default 'n',

`maintance` char(1) default 'n',

`finance` char(1) default 'n',

`night` char(1) default 'n',

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`resume_dt` date default '1900-01-01',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

`acost` varchar(10) default NULL,

PRIMARY KEY (

Any suggestions will be appreciated. I am concerned that my backups will not be restorabel.

Thanks

Michael

[peterlaursen]

hmmm…

It exports and imports OK here with SQLyog 5.02 and 5.13 as well.

Now I have only the structure – no data! But I understand that the error occurs when creating the structure?

What is your EXACT MySQL version? (I tested with 5.0.22). You should at least use 5.0.10 … two many bugs and incompatible changes before that!

It looks like an issue with the DATE-type. Can you test that? (remove all DATAs from the file as a test, and change the 'crate database' and the 'use' statement.

Also please answer: do you export using the 'ordinary' export tool or the back-up 'powertool' ?

But no matter what: This statement exports and imports (you truncated the crate statement a little, I think, but should not make a big difference!)!

CREATE TABLE `master_projects` (

`no` varchar(15) NOT NULL,

`name` varchar(100) DEFAULT NULL,

`description` longtext,

`psd` date DEFAULT '1900-01-01',

`asd` date DEFAULT '1900-01-01',

`pfd` date DEFAULT '1900-01-01',

`afd` date DEFAULT '1900-01-01',

`manager` varchar(15) DEFAULT NULL,

`number_developers` int(2) DEFAULT '0',

`pcost` varchar(10) DEFAULT '0',

`tcost` varchar(10) DEFAULT NULL,

`printcost` varchar(10) DEFAULT NULL,

`lcost` varchar(10) DEFAULT NULL,

`ocost` varchar(10) DEFAULT NULL,

`phours` int(2) DEFAULT '0',

`ahours` varchar(5) DEFAULT '0',

`projstatus` char(1) DEFAULT 'O',

`requestor` varchar(50) DEFAULT NULL,

`tc` varchar(50) DEFAULT NULL,

`owner` char(1) DEFAULT NULL,

`development` char(1) DEFAULT 'n',

`coordinators` char(1) DEFAULT 'n',

`multimedia` char(1) DEFAULT 'n',

`admin` char(1) DEFAULT 'n',

`it` char(1) DEFAULT 'n',

`maintance` char(1) DEFAULT 'n',

`finance` char(1) DEFAULT 'n',

`night` char(1) DEFAULT 'n',

`prop_dt` date DEFAULT '1900-01-01',

`denied_dt` date DEFAULT '1900-01-01',

`hold_dt` date DEFAULT '1900-01-01',

`resume_dt` date DEFAULT '1900-01-01',

`changelog` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

`acost` varchar(10) DEFAULT NULL,

PRIMARY KEY (`no`) ;

) ENGINE=MyISAM DEFAULT CHARSET=latin1

[peterlaursen]

I think I have something now!

search and replace ” ' ” with plain ” ' “.

What application wrote the backup? I do not think i was SQLyog that ever did ” ' ” (just tested both backup-tools of 5.02 and 5.13)

What sql_mode is the server running? (execute “SELECT @@global.sql_mode;” to find out)

[mreaves53]

I am using SQLyog 5.02 and when I check the mode I get: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Here is a copy of the sql.txt that Sqlyog created. I am using the export tool. The backup will not work because of the table type.

The error first starts in the master_projects table.

/*

SQLyog Enterprise – MySQL GUI v5.02

Host – 5.0.18-nt : Database – ttd

*********************************************************************

Server version : 5.0.18-nt

*/

/*Table structure for table `audit` */

CREATE TABLE `audit` (

`no` varchar(50) NOT NULL,

`trail` longtext,

`initial` timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `costs` */

CREATE TABLE `costs` (

`level` varchar(5) NOT NULL,

`rate` int(2) default NULL,

PRIMARY KEY (`level`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `costs_admin` */

CREATE TABLE `costs_admin` (

`level` varchar(5) default NULL,

`rate` varchar(5) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers` */

CREATE TABLE `developers` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NOT NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_admin` */

CREATE TABLE `developers_admin` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_cood` */

CREATE TABLE `developers_cood` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_copy` */

CREATE TABLE `developers_copy` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_finance` */

CREATE TABLE `developers_finance` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_it` */

CREATE TABLE `developers_it` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_maint` */

CREATE TABLE `developers_maint` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_mm` */

CREATE TABLE `developers_mm` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `developers_night` */

CREATE TABLE `developers_night` (

`no` varchar(15) default NULL,

`user` varchar(25) default NULL,

`hr` float default NULL,

`status` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `hma` */

CREATE TABLE `hma` (

`dept` varchar(50) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Table structure for table `lincoln` */

CREATE TABLE `lincoln` (

`dept` varchar(50) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

/*Table structure for table `managers` */

CREATE TABLE `managers` (

`manager` varchar(50) default NULL,

`email` varchar(50) default NULL,

`dept` varchar(50) default NULL,

`user` varchar(50) default NULL,

`password` varchar(15) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `master_projects` */

CREATE TABLE `master_projects` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`phours` int(2) default '0',

`ahours` varchar(5) default '0',

`projstatus` char(1) default 'O',

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`owner` char(1) default NULL,

`development` char(1) default 'n',

`coordinators` char(1) default 'n',

`multimedia` char(1) default 'n',

`admin` char(1) default 'n',

`it` char(1) default 'n',

`maintance` char(1) default 'n',

`finance` char(1) default 'n',

`night` char(1) default 'n',

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`resume_dt` date default '1900-01-01',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

`acost` varchar(10) default NULL,

PRIMARY KEY (;

/*Table structure for table `master_projects_copy` */

CREATE TABLE `master_projects_copy` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`phours` int(2) default '0',

`ahours` varchar(5) default '0',

`projstatus` char(1) default 'O',

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`owner` char(1) default NULL,

`development` char(1) default 'n',

`coordinators` char(1) default 'n',

`multimedia` char(1) default 'n',

`admin` char(1) default 'n',

`it` char(1) default 'n',

`maintance` char(1) default 'n',

`finance` char(1) default 'n',

`night` char(1) default 'n',

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`resume_dt` date default '1900-01-01',

`changelog` timestamp NULL default NULL,

`acost` varchar(10) default NULL,

PRIMARY KEY (;

/*Table structure for table `montgomery` */

CREATE TABLE `montgomery` (

`dept` varchar(50) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Table structure for table `printing` */

CREATE TABLE `printing` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`bw` varchar(10) default NULL,

`color` varchar(10) default NULL,

`bound` char(1) default NULL,

`outside` varchar(10) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `printing_admin` */

CREATE TABLE `printing_admin` (

`user` varchar(25) default NULL,

`no` varchar(50) default NULL,

`bw` decimal(8,2) default NULL,

`color` decimal(8,2) default NULL,

`bound` char(1) default NULL,

`outside` decimal(8,2) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `printing_cood` */

CREATE TABLE `printing_cood` (

`user` varchar(25) default NULL,

`no` varchar(50) default NULL,

`bw` decimal(8,2) default NULL,

`color` decimal(8,2) default NULL,

`bound` char(1) default NULL,

`outside` decimal(8,2) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NOT NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `printing_finance` */

CREATE TABLE `printing_finance` (

`user` varchar(25) default NULL,

`no` varchar(50) default NULL,

`bw` decimal(8,2) default NULL,

`color` decimal(8,2) default NULL,

`bound` char(1) default NULL,

`outside` decimal(8,2) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `printing_it` */

CREATE TABLE `printing_it` (

`user` varchar(25) default NULL,

`no` varchar(50) default NULL,

`bw` decimal(8,2) default NULL,

`color` decimal(8,2) default NULL,

`bound` char(1) default NULL,

`outside` decimal(8,2) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `printing_maint` */

CREATE TABLE `printing_maint` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`bw` varchar(10) default NULL,

`color` varchar(10) default NULL,

`bound` char(1) default NULL,

`outside` varchar(10) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `printing_mm` */

CREATE TABLE `printing_mm` (

`user` varchar(25) default NULL,

`no` varchar(50) default NULL,

`bw` decimal(8,2) default NULL,

`color` decimal(8,2) default NULL,

`bound` char(1) default NULL,

`outside` decimal(8,2) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `printing_night` */

CREATE TABLE `printing_night` (

`user` varchar(25) default NULL,

`no` varchar(50) default NULL,

`bw` decimal(8,2) default NULL,

`color` decimal(8,2) default NULL,

`bound` char(1) default NULL,

`outside` decimal(8,2) default NULL,

`dateworked` date default NULL,

`dateentered` date default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projectcosts` */

CREATE TABLE `projectcosts` (

`no` varchar(25) default NULL,

`materials` varchar(10) default NULL,

`multimedia` varchar(10) default NULL,

`vendor` varchar(10) default NULL,

`inter` varchar(10) default NULL,

`other` varchar(10) default NULL,

`tl` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projectcosts_admin` */

CREATE TABLE `projectcosts_admin` (

`no` varchar(25) default NULL,

`materials` varchar(10) default NULL,

`vendor` varchar(10) default NULL,

`other` varchar(10) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projectcosts_cood` */

CREATE TABLE `projectcosts_cood` (

`no` varchar(25) default NULL,

`materials` varchar(10) default NULL,

`vendor` varchar(10) default NULL,

`other` varchar(10) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projectcosts_finance` */

CREATE TABLE `projectcosts_finance` (

`no` varchar(25) default NULL,

`materials` varchar(10) default NULL,

`vendor` varchar(10) default NULL,

`other` varchar(10) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projectcosts_it` */

CREATE TABLE `projectcosts_it` (

`no` varchar(25) default NULL,

`materials` varchar(10) default '0.00',

`vendor` varchar(10) default '0.00',

`other` varchar(10) default '0.00',

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=;

/*Table structure for table `projectcosts_maint` */

CREATE TABLE `projectcosts_maint` (

`no` varchar(25) default NULL,

`materials` varchar(10) default NULL,

`vendor` varchar(10) default NULL,

`other` varchar(10) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projectcosts_mm` */

CREATE TABLE `projectcosts_mm` (

`no` varchar(25) default NULL,

`materials` varchar(10) default NULL,

`vendor` varchar(10) default NULL,

`other` varchar(10) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projectcosts_night` */

CREATE TABLE `projectcosts_night` (

`no` varchar(25) default NULL,

`materials` varchar(10) default NULL,

`vendor` varchar(10) default NULL,

`other` varchar(10) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours` */

CREATE TABLE `projecthours` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours_admin` */

CREATE TABLE `projecthours_admin` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours_cood` */

CREATE TABLE `projecthours_cood` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NOT NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours_finance` */

CREATE TABLE `projecthours_finance` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours_it` */

CREATE TABLE `projecthours_it` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours_maint` */

CREATE TABLE `projecthours_maint` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours_mm` */

CREATE TABLE `projecthours_mm` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projecthours_night` */

CREATE TABLE `projecthours_night` (

`user` varchar(25) default NULL,

`no` varchar(15) default NULL,

`hours` decimal(8,2) default NULL,

`type` varchar(15) default NULL,

`dateworked` date default NULL,

`dateentered` datetime default NULL,

`notes` longtext,

`projstatus` char(1) default NULL,

`tl` timestamp NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `projects` */

CREATE TABLE `projects` (

`no` varchar(15) NOT NULL,

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default '0',

`phours` varchar(10) default '0',

`ahours` varchar(10) default '0',

`projstatus` char(1) default 'O',

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE=MyISAM DEFAULT CHARS;

/*Table structure for table `projects_admin` */

CREATE TABLE `projects_admin` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default '0',

`phours` varchar(10) default '0',

`ahours` varchar(10) default '0.00',

`projstatus` char(1) default NULL,

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE=M;

/*Table structure for table `projects_cood` */

CREATE TABLE `projects_cood` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default '0',

`phours` varchar(10) default '0',

`ahours` varchar(10) default '0.00',

`projstatus` char(1) default NULL,

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE=M;

/*Table structure for table `projects_finance` */

CREATE TABLE `projects_finance` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default '0',

`phours` varchar(10) default '0',

`ahours` varchar(10) default '0.00',

`projstatus` char(1) default NULL,

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE=M;

/*Table structure for table `projects_it` */

CREATE TABLE `projects_it` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default NULL,

`phours` varchar(10) default '0',

`ahours` varchar(10) default NULL,

`projstatus` char(1) default NULL,

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE=MyISA;

/*Table structure for table `projects_maint` */

CREATE TABLE `projects_maint` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default '0',

`phours` varchar(10) default '0',

`ahours` varchar(10) default '0',

`projstatus` char(1) default 'O',

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE;

/*Table structure for table `projects_mm` */

CREATE TABLE `projects_mm` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default '0',

`phours` varchar(10) default '0',

`ahours` varchar(10) default '0',

`projstatus` char(1) default NULL,

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`resume_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE;

/*Table structure for table `projects_night` */

CREATE TABLE `projects_night` (

`no` varchar(15) NOT NULL default '',

`name` varchar(100) default NULL,

`description` longtext,

`psd` date default '1900-01-01',

`asd` date default '1900-01-01',

`pfd` date default '1900-01-01',

`afd` date default '1900-01-01',

`manager` varchar(15) default NULL,

`number_developers` int(2) default '0',

`pcost` varchar(10) default '0',

`tcost` varchar(10) default NULL,

`printcost` varchar(10) default NULL,

`lcost` varchar(10) default NULL,

`ocost` varchar(10) default NULL,

`acost` varchar(10) default '0',

`phours` varchar(10) default '0',

`ahours` varchar(10) default '0.00',

`projstatus` char(1) default NULL,

`requestor` varchar(50) default NULL,

`tc` varchar(50) default NULL,

`prop_dt` date default '1900-01-01',

`denied_dt` date default '1900-01-01',

`hold_dt` date default '1900-01-01',

`owner` char(1) default 'n',

`changelog` timestamp NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)

) ENGINE=M;

/*Table structure for table `tcs` */

CREATE TABLE `tcs` (

`name` varchar(50) default NULL,

`email` varbinary(50) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `test` */

DROP VIEW IF EXISTS `test`;

CREATE UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select `users_cood`.`user` AS `user`,`users_cood`.`password` AS `password`,`users_cood`.`level` AS `level`,`users_cood`.`name` AS `name`,`users_cood`.`censtatus` AS `censtatus` from `users_cood`;

/*Table structure for table `users` */

CREATE TABLE `users` (

`user` varchar(25) NOT NULL,

`password` varchar(15) default NULL,

`level` char(1) default NULL,

`name` varchar(50) default NULL,

`censtatus` char(1) default NULL,

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

/*Table structure for table `users_admin` */

CREATE TABLE `users_admin` (

`user` varchar(25) NOT NULL,

`password` varchar(15) default NULL,

`level` char(1) default NULL,

`name` varchar(50) default NULL,

`censtatus` char(1) default NULL,

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

/*Table structure for table `users_cood` */

CREATE TABLE `users_cood` (

`user` varchar(25) NOT NULL,

`password` varchar(15) default NULL,

`level` char(1) default '1',

`name` varchar(50) default NULL,

`censtatus` char(1) default 'a',

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYN;

/*Table structure for table `users_finance` */

CREATE TABLE `users_finance` (

`user` varchar(25) NOT NULL default '',

`password` varchar(15) default NULL,

`level` char(1) default NULL,

`name` varchar(50) default NULL,

`censtatus` char(1) default NULL,

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=lati;

/*Table structure for table `users_it` */

CREATE TABLE `users_it` (

`user` varchar(25) NOT NULL default '',

`password` varchar(15) default NULL,

`level` char(1) default NULL,

`name` varchar(50) default NULL,

`censtatus` char(1) default NULL,

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=lati;

/*Table structure for table `users_maint` */

CREATE TABLE `users_maint` (

`user` varchar(25) NOT NULL default '',

`password` varchar(15) default NULL,

`level` char(1) default NULL,

`name` varchar(50) default NULL,

`censtatus` char(1) default NULL,

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=lati;

/*Table structure for table `users_mm` */

CREATE TABLE `users_mm` (

`user` varchar(25) NOT NULL default '',

`password` varchar(15) default NULL,

`level` char(1) default '1',

`name` varchar(50) default NULL,

`censtatus` char(1) default 'a',

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=;

/*Table structure for table `users_night` */

CREATE TABLE `users_night` (

`user` varchar(25) NOT NULL default '',

`password` varchar(15) default NULL,

`level` char(1) default '1',

`name` varchar(50) default NULL,

`censtatus` char(1) default 'a',

PRIMARY KEY (`user`)

) ENGINE=MyISAM DEFAULT CHARSET=;

/*View structure for view `test` */

drop view if exists `test`;

drop table if exists `test`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select `users_cood`.`user` AS `user`,`users_cood`.`password` AS `password`,`users_cood`.`level` AS `level`,`users_cood`.`name` AS `name`,`users_cood`.`censtatus` AS `censtatus` from `users_cood`;

/* Procedure structure for procedure `cordtotal` */

drop procedure if exists `cordtotal`;

DELIMITER $$;

CREATE PROCEDURE `cordtotal`(IN a varchar(50))

BEGIN

SELECT @ocost := materials + multimedia + vendor + other from projectcosts_cood where no = a;

UPDATE projects_cood set ocost = @ocost;

SELECT @pcost := sum((bw*.015)+(color * .1)+outside) from printing_cood where no = a;

UPDATE projects_cood set printcost = @pcost where no = a;

SELECT @laborcost:=sum(projecthours_cood.hours*costs.rate) from projecthours_cood,users_cood,costs where no = a and projecthours_cood.user = users_cood.user and users_cood.level = costs.level;

UPDATE projects_cood set lcost = @laborcost where no = a;

SELECT @totcost:= printcost + ocost + lcost from projects_cood where no = a;

UPDATE projects_cood set tcost = @totcost where no = a;

END$$

DELIMITER ;$$

/* Procedure structure for procedure `projecttotal` */

drop procedure if exists `projecttotal`;

DELIMITER $$;

CREATE PROCEDURE `projecttotal`(In a varchar(50))

BEGIN

SELECT @ocost := materials + multimedia + vendor + other from projectcosts where no = a;

UPDATE projects set ocost = @ocost;

SELECT @pcost := sum((bw*.015)+(color * .1)+outside) from printing where no = a;

UPDATE projects set printcost = @pcost where no = a;

SELECT @laborcost:=sum(projecthours.hours*costs.rate) from projecthours,users,costs where no = a and projecthours.user = users.user and users.level = costs.level;

UPDATE projects set lcost = @laborcost where no = a;

SELECT @totcost:= printcost + ocost + lcost from projects where no = a;

UPDATE projects set tcost = @totcost where no = a;

END$$

DELIMITER ;$$

/* Procedure structure for procedure `totalcost` */

drop procedure if exists `totalcost`;

DELIMITER $$;

CREATE PROCEDURE `totalcost`(IN a varchar(15))

BEGIN

DECLARE grand decimal (8,2);

DECLARE oottotal decimal (8,2);

DECLARE labtotal decimal (8,2);

DECLARE printtotal decimal (8,2);

call projecttotal (a);

call cordtotal(a);

SELECT @cordtotal:= tcost, @pcost:= printcost, @labor:= lcost, @outcost:= ocost from projects_cood where no =a;

Select @total:=tcost, @ppcost:=printcost, @projectlabor:=lcost, @projout:=ocost from projects where no =a;

SET @grand = @cordtotal + @total;

SET @oototal = @outcost + @projout;

SET @labtotal = @labor + @projectlabor;

SET @printotal = @pcost + @ppcost;

UPDATE master_projects set tcost = @grand, printcost = @printotal, lcost = @labtotal, ocost = @oototal where no = a;

END$$

DELIMITER ;$$

Thanks,

[peterlaursen]

It is difficult to remember .. about the past in particular .. 🙂

But there was an issue with SJA and some early 5.0.x releases (I do no remember exactly which ones) that it did not 'normalize' the sql_mode. That could be the issue if server default sql_mode is a special one (like 'NO_BACKSLASH_ESCAPES'. About SQLyog and sql_modes: http://webyog.com/faq/28_72_en.html

I think that upgrading to 5.13 will solve that.

get it from here: http://www.webyog.com/sqlyog/upgrade.html

.. and try making a new backup with it.

It this one imports OK, then there is no reason to 'drill holes' in the past I think!

Please explain:

“The backup will not work because of the table type”

????

Should InnoDB be a problem ???

You just have to “set FOREIGN_KEYS_CHECK = 0” in the backup wizard as you most often will when working with InnoDB!

The problem is the escaped ' ( like ' ) in deaults. MySQL does not seem to like that.

I really do not understand either how it gets there!

I'll research a little!

I cannot make it generate

default 'blabla' no matter what I try! Also not changing the sql_mode!

Now .. escaping applies WITHIN strings but the string enclose character should not be escaped itself!

Any idea where that comes from ??

Anyway, please try 5.13!

Also this:

Quote:

PRIMARY KEY (;

.. and then it stops! This of couse will not load! I cannot generate that either!

[peterlaursen]

@Michael

Try this:

Code:

SHOW CREATE TABLE `master_projects`;

If you do not have a valid CREATE STATEMENT returned, there is something wrong with your MySQL installation.

[Author]

Posts