I've got backups (SQL statement exports) from phpMyAdmin that I'm trying to use now to re-build the database. Here's what it printed for me:
Code: Select all
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `ARRIVA_DUTIES` ;
CREATE SCHEMA IF NOT EXISTS `ARRIVA_DUTIES` ;
USE `arriva_duties` ;
USE `ARRIVA_DUTIES` ;
CREATE TABLE IF NOT EXISTS `ARRIVA_DUTIES`.`tbl_duties` (
`duty_id` INT(5) NOT NULL AUTO_INCREMENT ,
`duty_number` VARCHAR(11) NOT NULL ,
`duty_type` VARCHAR(16) NOT NULL ,
`duty_start` TIME NOT NULL ,
`duty_finish` TIME NOT NULL ,
`duty_paytime` VARCHAR(5) NOT NULL ,
PRIMARY KEY (`duty_id`) ,
UNIQUE INDEX `duty_number` (`duty_number` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 144
DEFAULT CHARACTER SET = latin1 ;
CREATE TABLE IF NOT EXISTS `ARRIVA_DUTIES`.`tbl_problems` (
`problem_id` INT(11) NOT NULL AUTO_INCREMENT ,
`problem_type` VARCHAR(4) NULL DEFAULT NULL ,
`search_value` VARCHAR(15) NULL DEFAULT NULL ,
`reporter_name` VARCHAR(50) NULL DEFAULT NULL ,
`report_desc` VARCHAR(300) NULL DEFAULT NULL ,
`auto_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`reporter_contact` VARCHAR(100) NULL DEFAULT NULL ,
`problem_status` TEXT NULL DEFAULT NULL ,
PRIMARY KEY (`problem_id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `ARRIVA_DUTIES`.`tbl_reporter_requests` (
`request_id` INT(11) NOT NULL AUTO_INCREMENT ,
`request_timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
`request_ip` VARCHAR(15) NULL DEFAULT NULL ,
PRIMARY KEY (`request_id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 242
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `ARRIVA_DUTIES`.`tbl_requests` (
`timestamp_id` INT(11) NOT NULL AUTO_INCREMENT ,
`auto_timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
`request_ip` VARCHAR(15) NULL DEFAULT NULL ,
`request_type` TEXT NULL DEFAULT NULL ,
`request_value` VARCHAR(30) NULL DEFAULT NULL ,
`request_referer` VARCHAR(60) NULL DEFAULT NULL ,
PRIMARY KEY (`timestamp_id`) ,
UNIQUE INDEX `auto_timestamp` (`auto_timestamp` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 555
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `ARRIVA_DUTIES`.`tbl_weeks` (
 `week_id` INT(5) NOT NULL AUTO_INCREMENT ,
`week_number` INT(5) NOT NULL ,
`week_dutyday1` INT(5) NULL DEFAULT NULL ,
`week_dutyday2` INT(5) NULL DEFAULT NULL ,
`week_dutyday3` INT(5) NULL DEFAULT NULL ,
`week_dutyday4` INT(5) NULL DEFAULT NULL ,
`week_dutyday5` INT(5) NULL DEFAULT NULL ,
`week_dutyday6` INT(5) NULL DEFAULT NULL ,
`week_dutyday7` INT(5) NULL DEFAULT NULL ,
PRIMARY KEY (`week_id`)) ,
ENGINE = InnoDB
AUTO_INCREMENT = 103
DEFAULT CHARACTER SET = latin1 ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
But it now doesn't like the statements it produced.
Here's the error:
Code: Select all
SQL query:
CREATE TABLE IF NOT EXISTS `ARRIVA_DUTIES`.`tbl_weeks` (
 `week_id` INT( 5 ) NOT NULL AUTO_INCREMENT ,
`week_number` INT( 5 ) NOT NULL ,
`week_dutyday1` INT( 5 ) NULL DEFAULT NULL ,
`week_dutyday2` INT( 5 ) NULL DEFAULT NULL ,
`week_dutyday3` INT( 5 ) NULL DEFAULT NULL ,
`week_dutyday4` INT( 5 ) NULL DEFAULT NULL ,
`week_dutyday5` INT( 5 ) NULL DEFAULT NULL ,
`week_dutyday6` INT( 5 ) NULL DEFAULT NULL ,
`week_dutyday7` INT( 5 ) NULL DEFAULT NULL ,
PRIMARY KEY ( `week_id` )
),
ENGINE = INNODB AUTO_INCREMENT =103 DEFAULT CHARACTER SET = latin1;
MySQL said:
#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 '`week_id` INT(5) NOT NULL AUTO_INCREMENT ,
`week_number` INT(5) NOT NULL ,
' at line 2
Could anybody shed some light on where I'm going wrong? It's created 4 of the tables just fine.
