MariaDB CREATE TABLE SQL Error: 1170

Question

I know this question has been asked many times before, however I'm really struggling applying those answers to this problem.

I'm migrating a database from Postgres to MariaDB using MySQL Workbench.

Here is the table I'm having trouble with

CREATE TABLE IF NOT EXISTS `postgres`.`oldimage` (
  `oi_name` LONGTEXT NOT NULL,
  `oi_archive_name` LONGTEXT NOT NULL,
  `oi_size` INT NOT NULL,
  `oi_width` INT NOT NULL,
  `oi_height` INT NOT NULL,
  `oi_bits` SMALLINT NULL,
  `oi_description` LONGTEXT NULL,
  `oi_user` INT NULL,
  `oi_user_text` LONGTEXT NOT NULL,
  `oi_timestamp` DATETIME NULL,
  `oi_metadata` LONGBLOB NOT NULL,
  `oi_media_type` LONGTEXT NULL,
  `oi_major_mime` LONGTEXT NULL,
  `oi_minor_mime` LONGTEXT NULL,
  `oi_deleted` SMALLINT NOT NULL DEFAULT 0,
  `oi_sha1` LONGTEXT NOT NULL DEFAULT '',
  INDEX `oi_name_archive_name` (`oi_name`(255) ASC, `oi_archive_name`(255) ASC),
  INDEX `oi_name_timestamp` (`oi_name`(255) ASC, `oi_timestamp` ASC),
  INDEX `oi_sha1` (`oi_sha1`(255) ASC),
  CONSTRAINT `oldimage_oi_name_fkey_cascaded`
    FOREIGN KEY (`oi_name`)
    REFERENCES `postgres`.`image` (`img_name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `oldimage_oi_user_fkey`
    FOREIGN KEY (`oi_user`)
    REFERENCES `postgres`.`mwuser` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION)

The error I'm getting is

BLOB/TEXT column 'oi_name' used in key specification without a key length
SQL Error: 1170

I've tried making oi_name the PRIMARY KEY, as well I've tried changing it to VARCHAR(255), no luck with either.
Any help is greatly appreciated.

This might be of help: https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length — kchason, Nov 20 '17 at 18:30
Yes if I make oi_name VARCHAR(255) I get this error; SQL Error: 1005 "Foreign key constraint is incorrectly formed" — Huckleberry Finn, Nov 20 '17 at 19:21
Move away from `LONGTEXT` (etc) to rational values of nn in `VARCHAR(nn)`. This problem, and others, will go away. Index prefixing has many problems; hopefully you can get rid of that, too. — Rick James, Nov 20 '17 at 22:17

score 2 · Accepted Answer · answered Nov 20 '17 at 18:37

2

Your issue is at

...
CONSTRAINT `oldimage_oi_name_fkey_cascaded`
FOREIGN KEY (`oi_name`)
REFERENCES `postgres`.`image` (`img_name`)
...

From Create foreign Key

Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

answered Nov 20 '17 at 18:37

Jacques Amar

1,803
1
10
12

Aha! I believe I got it. After making `oi_name VARCHAR(255)` I also made the column `img_name VARCHAR(255)`. Thanks for pointing me in the right direction. – Huckleberry Finn Nov 20 '17 at 19:27

MariaDB CREATE TABLE SQL Error: 1170

1 Answers1