Mysql 1286 Unknown table engine 'InnoDB''

Databases
1

Hi,
This morning I’ve had a bad wake up (may be monday morning :slight_smile: )
if I go to my a customer site I’ve got
Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine ‘InnoDB’’
the site has worked fine just for a year and I didn’t touch it so …

Can you help me please ?

2

PS.
I’ve just asked for it to my hosting but the folks
told me it’s not their fault.
I wanted to know your opinion before
forwardind my ticket.

3

The hosting put up a back-up and the site works.
Thanks the same.

4

You’re welcome :wink:
I’m glad you resolved your problem.
Any idea what was causing it?

5

Nope :frowning: hosting folks said it’s my fault
but I don’t modified anything in my script !
On the other hand they worked out the problem so … ^^
though I’d like to know what’s the problem me too :slight_smile:

6

:frowning: a table is missing
I don’t really know what the hell is going in my db :((
Any help please ?

I post my db schema you never know the missing table is
myprefix_user_destination


 CREATE TABLE IF NOT EXISTS myprefix_user (
    id INT unsigned NOT NULL AUTO_INCREMENT,
    is_juridical tinyint(1) NOT NULL COMMENT '0 natural person, 1 juridical',
    uname varchar(255) NOT NULL COMMENT 'company name or first and last name',
    email VARCHAR(255) NOT NULL,
    password char(40) NOT NULL,
    fiscal_code_vat varchar(16) NOT NULL,
    discrict varchar(255) DEFAULT NULL,
    municipality varchar(255) DEFAULT NULL,
    address varchar(255) DEFAULT NULL,
    zip varchar(10) DEFAULT NULL,
    phone varchar(255) DEFAULT NULL,
    mobile varchar(255) DEFAULT NULL,
    fax varchar(255) DEFAULT NULL,
    web varchar(255) DEFAULT NULL,
    discountPercent TINYINT NOT NULL DEFAULT 0,
    is_locked TINYINT( 1 ) NOT NULL,
    role ENUM('basic','complete','admin') NOT NULL DEFAULT 'basic',
    status ENUM('unconfirmed','confirmed','deleted') NOT NULL DEFAULT 'unconfirmed',
    registration_ip VARCHAR(15) NOT NULL,
    registration_datetime DATETIME NOT NULL,
    last_login_ip VARCHAR(15) DEFAULT NULL,
    last_login_datetime DATETIME DEFAULT NULL,
    ads tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Do you want to receive ads ? 0 No 1 Yes',
    uid CHAR(40) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY unique_myprefix_user_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS myprefix_user_destination (
    id INT unsigned NOT NULL AUTO_INCREMENT,
    user_id INT unsigned NOT NULL,
    title VARCHAR(255) NOT NULL,
    discrict VARCHAR(255) NOT NULL,
    municipality VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    zip VARCHAR(10) NOT NULL,
    note VARCHAR(1000) DEFAULT NULL COMMENT 'Note per la spedizione',
    PRIMARY KEY (id),
    UNIQUE KEY unique_myprefix_user_destination (user_id,discrict,municipality,address,zip),
    KEY key_myprefix_user_destination (user_id),
    CONSTRAINT fk_myprefix_user_destination FOREIGN KEY (user_id) REFERENCES myprefix_user (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS myprefix_category (
    id INT unsigned NOT NULL auto_increment,
    parent_id INT unsigned  DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    ico VARCHAR(255) DEFAULT NULL,
    published int(10) NOT NULL,
    highlighted enum('0','1') NOT NULL default '0' COMMENT '0 no, 1 yes',
    PRIMARY KEY  (id),
    UNIQUE KEY unique_myprefix_category_title (title),
    UNIQUE KEY unique_myprefix_category_slug (slug),
    CONSTRAINT fk_myprefix_category FOREIGN KEY (parent_id) REFERENCES myprefix_category (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS myprefix_vat (
    id TINYINT unsigned NOT NULL auto_increment,
    value TINYINT  NOT NULL,
    PRIMARY KEY  (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS myprefix_item (
    id INT unsigned NOT NULL auto_increment,
    category_id INT unsigned  DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    code VARCHAR(255) NOT NULL,
    price decimal(10,2) NOT NULL,
    ico VARCHAR(255) DEFAULT NULL,
    published int(10) NOT NULL,
    onstock enum('0','1') NOT NULL default '0' COMMENT '0 no, 1 yes',
    discountPercent TINYINT NOT NULL DEFAULT 0,
    vat_id TINYINT unsigned NOT NULL,
    packing_volume FLOAT DEFAULT NULL,
    PRIMARY KEY  (id),
    UNIQUE KEY unique_myprefix_item_title (title),
    UNIQUE KEY unique_myprefix_item_slug (slug),
    CONSTRAINT fk_myprefix_item_category_id FOREIGN KEY (category_id) REFERENCES myprefix_category (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_myprefix_item_vat_id FOREIGN KEY (vat_id) REFERENCES myprefix_vat (id) ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS myprefix_payment_method (
    id TINYINT unsigned NOT NULL auto_increment,
    title VARCHAR(50) NOT NULL,
    charge_percentage TINYINT unsigned DEFAULT 0,
    description TEXT DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS myprefix_shipment_method (
  id TINYINT unsigned NOT NULL AUTO_INCREMENT,
  title VARCHAR(50) NOT NULL,
  description text  NOT NULL,
  price decimal(7,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE IF NOT EXISTS myprefix_order (
    id INT unsigned NOT NULL AUTO_INCREMENT,
    user_id INT unsigned NOT NULL,
    user_destination_id INT unsigned NOT NULL,
    shipment_id TINYINT unsigned NOT NULL,
    payment_id TINYINT unsigned NOT NULL,
    date datetime NOT NULL,
    from_ip VARCHAR(15) NOT NULL,
    shipment_cost decimal(9,2) NOT NULL,
    vat_total decimal(9,2) NOT NULL,
    packing_volume_tot NOT NULL DEFAULT '0',
    total decimal(11,2) NOT NULL,
    status enum('0','1') NOT NULL DEFAULT '0' COMMENT '0 Open 1 Closed',
    PRIMARY KEY (id),
    CONSTRAINT fk_myprefix_order_user_id FOREIGN KEY (user_id) REFERENCES myprefix_user (id) ON UPDATE CASCADE,
    CONSTRAINT fk_myprefix_order_user_destination_id FOREIGN KEY (user_destination_id) REFERENCES myprefix_user_destination (id) ON UPDATE CASCADE,
    CONSTRAINT fk_myprefix_order_shipment_id FOREIGN KEY (shipment_id) REFERENCES myprefix_shipment_method (id) ON UPDATE CASCADE,
    CONSTRAINT fk_myprefix_order_payment_id FOREIGN KEY (payment_id) REFERENCES myprefix_payment_method (id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS myprefix_order_to_product (
  order_id INT unsigned NOT NULL,
  item_id INT unsigned NOT NULL,
  quantity TINYINT NOT NULL,
  price decimal(11,2) NOT NULL,
  total decimal(11,2) NOT NULL,
  PRIMARY KEY (order_id,item_id),
  CONSTRAINT fk_myprefix_otp_order_id FOREIGN KEY (order_id) REFERENCES myprefix_order (id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS myprefix_payment (
  id INT unsigned NOT NULL AUTO_INCREMENT,
  user_id INT unsigned NOT NULL,
  order_id INT unsigned NOT NULL,
  amount decimal(11,2) NOT NULL,
  date DATE NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_myprefix_payment_user_id FOREIGN KEY (user_id) REFERENCES myprefix_user (id) ON UPDATE CASCADE,
  CONSTRAINT fk_myprefix_payment_order_id FOREIGN KEY (order_id) REFERENCES myprefix_order (id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
7

All in all before changing my hosting I’d like to say.
Is it possible that a mistake made in the code or in the schema
drop a table ?
I don’t think so but …

8

You can drop a table in your code. But not by mistake. It must be a DROP TABLE query.

9

ok it’s not the case thanks
I missed it in the post :slight_smile:

10

Something’s fishy. Did they recently do a MySQL update across the server? That would be about the only thing I could think of that would suddenly cause MySQL to lose an engine type.

11

This is a good question to ask to the hosting :slight_smile: thanks