SQL error 1170 importing PW database

[MarcC]

I exported from an existing PW site (using SQL Buddy) and I'm having troubles importing the data to PHPMyAdmin on a separate server. I get a bunch of errors like the following. Does anyone have advice on fixing this? This comes from a working PW site, but for some reason it's not importing. Unfortunately I no longer have access to the original PW site, just this SQL file. Here's the error--any help would be appreciated.

Error

SQL query:

CREATE TABLE `field_content_images` (

`pages_id` INT( 10 ) UNSIGNED NOT NULL ,
`data` VARCHAR( 255 ) NOT NULL ,
`sort` INT( 10 ) UNSIGNED NOT NULL ,
`description` TEXT NOT NULL ,
PRIMARY KEY ( `pages_id` , `sort` ) ,
KEY `data` ( `data` ) ,
KEY `description` ( `description` )

) ENGINE = MYISAM DEFAULT CHARSET = utf8;
 

 

MySQL said: 

#1170 - BLOB/TEXT column 'description' used in key specification without a key length 

[nik]

Looks like the key "description" wasn't exported quite right as the table structure isn't what it is when created by ProcessWire. Try changing "KEY `description` ( `description` )" to "FULLTEXT KEY `description` ( `description` )", that seems to fix the issue for me at least. And the same fix for other file/image field tables at least.

I haven't got a clue why this has happened (SQL Buddy problem maybe?), but I hope changing the key definitions gets you around the problem!

[MarcC]

Thanks Nik--that seemed to work with description, but it complains about `data`:

Error

SQL query:

CREATE TABLE `field_body` (

`pages_id` INT( 10 ) UNSIGNED NOT NULL ,

`data` MEDIUMTEXT NOT NULL ,

PRIMARY KEY ( `pages_id` ) ,

KEY `data` ( `data` )

) ENGINE = MYISAM DEFAULT CHARSET = utf8;

MySQL said: 

#1170 - BLOB/TEXT column 'data' used in key specification without a key length

[ And then I changed to FULLTEXT... ]

Error

SQL query:

CREATE TABLE `field_display_specialty_footer` (

`pages_id` INT( 10 ) UNSIGNED NOT NULL ,

`data` INT( 11 ) NOT NULL ,

`sort` INT( 10 ) UNSIGNED NOT NULL ,

PRIMARY KEY ( `pages_id` , `sort` ) ,

FULLTEXT KEY `data` ( `data` , `pages_id` , `sort` )

) ENGINE = MYISAM DEFAULT CHARSET = utf8;

MySQL said: 

#1283 - Column 'data' cannot be part of FULLTEXT index

[MarcC]

I wonder if this is due to the collation being off. The old DB is latin1. :-/ (Edit: Seems the new one is too, so this isn't a problem)

[nik]

Do the change only if the error is #1170 and the mentioned column is of type TEXT/BLOB (may be with a prefix TINY/MEDIUM/LONG too). In the table "field_display_specialty_footer" column "data" is of type INT(11) and should cause no trouble ("KEY `data` ( `data` , `pages_id` , `sort` )" is correct).

So first see the error and only change what's actually needed.

[MarcC]

Yes, it's the same error. Edit: Oh, I see--you mean depending on the table. Missed that. Some data are different from other data Let's try it...

[nik]

What you did earlier for table "field_body" was correct - I was trying to say don't apply the same fix blindly to all keys named "data" as they're not all the same.

So, for table "field_body" change "KEY `data` ( `data` )" to "FULLTEXT KEY `data` ( `data` )" (as column "data" is of type MEDIUMTEXT in that table). But for table "field_display_specialty_footer" leave the key to what it was ("KEY `data` ( `data` , `pages_id` , `sort` )") as column "data" is not of type TEXT/BLOB there.

[MarcC]

Thanks, I re-read your reply and understood completely. After working through the problem table by table, I am now a big fan of FULLTEXT, and the DB imported perfectly. One website restored. I appreciate the help!

[nik]

Glad you were able to get it working!

The problem must have been there in the source database (can't see why it could have worked like that there though..) or then it got introduced during the export process. This answer in SO explains why MySQL didn't like the key definitions you had, but where they originally came from remains a mystery.