importing simple mysql table

[mike2010]

I'm having an issue importing a very simple mysql table.. I usually never use PhpMyAdmin, but I am for this table...just to get it imported.


I'm getting the following error :

MySQL / Specified key was too long; max key length is 1000 bytes

when importing the following feeds.sql file via PhpMyAdmin

CREATE TABLE IF NOT EXISTS `feeds` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `title` varchar(512) character set utf8 NOT NULL,
 `content` text character set utf8 NOT NULL,
 `link` text character set utf8 NOT NULL,
 `item_date` varchar(100) NOT NULL,
 `sort_date` datetime NOT NULL,
 `date_added` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `link` (`link`(255),`item_date`),
 KEY `sort_date` (`sort_date`)
) ENGINE=MyISAM;

I'm surprised such a small, basic table would return such an error.

Any help in modifying the above sql file, to get it right...so it doesn't an error. or manually creating the table from scratch is appreciated.

I attempted to manually create the table, but there are fields there, that don't display in the above coding.

such as 'Column' , 'type' , 'collation', and a ton of others...since I don't know specifically what to enter in all those fields, i'm not even attempting it...and would prefer the import method, whenever I could get the above error to disappear.

The site has nothing on it so far, so we can trial and error.

[swa66]

Is a mysql question, not a php one.

But: you have a text column with a unique key on it ?
Why even try that ?

Your text column is utf-8 encoded, which means that worst case it's 3 bytes per position, and you specify in your keylength to use 255 positions + item_date which itself is 100 positions. And 355*3 is more than 1000 bytes ... so the error does make sense.

Keep the total under 333 positions .. and it'll work.

But you might want to reconsider the link as a text column.
Also does a "link" need to be encoded as UTF-8 ? Not knowing what the data is I can't say. But if it's a URL, it's going to be UTF-8 data IMHO - nor do I see that need for a unique constraint to be honest.
And a constraint to be unique on the first part of a URL also makes little sense to me.

[mike2010]

It's just a simple empty table for a news feed type thing.

could I get the right code just to get this uploaded? It shouldn't be this difficult.

that's all the coding I was provided from the coder to create the table within the database.

he probably threw it together quickly, not anticipating I was using PhpMyAdmin to upload the file. (this, the size error) I haven't communicated with him for 6 months though, so it would be probably be useless asking him now.

There is currently no table(s) inside the database. It's just an empty database currently.

[mike2010]

seriously...nobody knows?

or anybody recommend another forum where I can get assistance with this ?

[Habtom]

[Runs fine for me]

[swa66]

From the mysql manual my myisam:
[dev.mysql.com...]

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling.

My guess is that you've set the default to UTF-8 encoding on the database and that those where it works have it set to something like ISO-LATIN-1

But the fix lies in rethinking your constraint IMHO.

[mike2010]

since it's just an empty table name within a DB, how about creating it from scratch?

can't believe this is such a pain in the ass.

I just need to have these columns filled out to create the simple table -

[imagizer.imageshack.us...]

hopefully all the details that are needed, are in the initial post ?

table name - Feeds

column?
length / values ?

someone here with mysql experience would be awesome.

and swa66, I looked for that option in PhpMyAdmin settings to increase the byte limit..but couldn't find it. I also attempted to change the encoding to something else , but still the stupid 1000 byte limit error.

[swa66]

I looked for that option in PhpMyAdmin settings to increase the byte limit..

It an option when mysql is compiled from source code (it's quite unlikely you did that and get stuck at this).

phpmyadmin: I've never used it, nor am I likely to ever do so.

I just connect from the command line to my database.

You have done at some point the equivalent of a CREATE DATABASE command using your webbased tool.

There you can set the default for that database to be UTF-8. My guess is that you did that and the result is that index key have their maxium size reduced for default strings with unspecified enoding by a factor of 3.

Ref [dev.mysql.com...]

I'd first confirm that the database is dafaulting to UTF-8:

mysql> use testdb;

mysql> show variables like "character_set_database";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like "collation_database";
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

If you insist on not tackling the core problem and want the quick and dirty fix: I'd delete the database and recreate it not using UTF-8 if it is created as such now.

That is if you have the needed rights ...

[swa66]

Alternatively, if your database defaults to UTF-8 and your PHP code doesn;t expect the all the fields to be in UTF-8, and you have no control over the database itself, try giving the table being created a DEFAULT CHARSET=latin1 and see if that works.

[mike2010]

I have root control over everything, so that's not an issue.

how about creating the table from scratch with the image fields I provided ? (in that image link)

so many fields, just to make a simple empty table within a DB.