Getting Error Code: 1146 in MySQL Workbench

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Getting Error Code: 1146 in MySQL Workbench

Post 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! :)
Attachments
Screen Shot 2011-10-31 at 10.24.34.png
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting Error Code: 1146 in MySQL Workbench

Post by Celauran »

That doesn't look good at all. Have you tried mysql from the command line? Specifically, check mysqldump.
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Re: Getting Error Code: 1146 in MySQL Workbench

Post 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?
Attachments
Screenshot from phpMyAdmin
Screenshot from phpMyAdmin
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Re: Getting Error Code: 1146 in MySQL Workbench

Post 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. :(
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Getting Error Code: 1146 in MySQL Workbench

Post by Weirdan »

There shouldn't be a comma before ENGINE keyword.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Getting Error Code: 1146 in MySQL Workbench

Post by Benjamin »

Also, what is ?
Post Reply