Page 1 of 1

Getting Error Code: 1146 in MySQL Workbench

Posted: Mon Oct 31, 2011 5:25 am
by mikeashfield
This is driving me CRAZY! I was using MAMP PRO on my iMac to serve a small project for a few friends, and the PRO side of it ran out, and it costs £40 to upgrade, which I am not paying for. So I decided to host it with 000webhost.com but trying to get the data and schema from MySQL is proving to be problematic.

I've attached a screenshot of this error if anybody can help me that would be great! :)

Re: Getting Error Code: 1146 in MySQL Workbench

Posted: Mon Oct 31, 2011 3:05 pm
by Celauran
That doesn't look good at all. Have you tried mysql from the command line? Specifically, check mysqldump.

Re: Getting Error Code: 1146 in MySQL Workbench

Posted: Mon Oct 31, 2011 3:32 pm
by mikeashfield
The plot thickens, phpMyAdmin shows the DB as having 4 tables in the side bar, but no tables when i click it. What the hell has happened?

Re: Getting Error Code: 1146 in MySQL Workbench

Posted: Mon Oct 31, 2011 3:50 pm
by mikeashfield
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. :(

Re: Getting Error Code: 1146 in MySQL Workbench

Posted: Mon Oct 31, 2011 4:16 pm
by Weirdan
There shouldn't be a comma before ENGINE keyword.

Re: Getting Error Code: 1146 in MySQL Workbench

Posted: Mon Oct 31, 2011 4:35 pm
by Benjamin
Also, what is ?