simple mysql table creation in PhpMyAdmin

mike2010 · 01-20-2014, 10:54 PM

The database is already made..along with username and password.

But 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 this table started / imported.

Usually the software programs install these automatically, but not this one.

The table is called 'feeds'

I'm getting the following error :

Quote:

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

when importing the following feeds.sql file via PhpMyAdmin

Code:

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=InnoDB;

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 cause an error. or manually creating the table from scratch is appreciated.

I attempted to manually create the table, (without importing the above) but there are fields there, that don't display in the above coding. If ya think that route is easier, all the fields are here :

http://imagizer.imageshack.us/v2/800...0/841/eytv.jpg

whichever way can get the job done = appreciated.

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

astrogeek · 01-20-2014, 11:03 PM

It has nothing to do with PHPMyAdmin.

The unique key on link uses a key length of 255 characters plus a datetime, but link is also UTF8, which can require up to four bytes per character - 255 X 4 = 1020 bytes. MyISAM tables have a limit of 1000 bytes for keys... hence the error.

UPDATE *** I see that item_date is not a datetime but a varchar(100) and we don't know the default encoding. If it is also UTF8 then your key length is potentially 1020 + 400 = 1420 bytes.

mike2010 · 01-22-2014, 07:06 PM

trying to contact the developer / coder now. He made the project over 6 months ago...and I just finally got around to it now.. so it's possible he might completely ignore me.

any chance we could figure this out without him? or creating the table from scratch, with just the fields provided?

astrogeek · 01-22-2014, 10:16 PM

Quote:

Originally Posted by mike2010

trying to contact the developer / coder now. He made the project over 6 months ago...and I just finally got around to it now.. so it's possible he might completely ignore me.

any chance we could figure this out without him? or creating the table from scratch, with just the fields provided?

So I take it this is an untested bit of code, and we know nothing about how/where/why it is used, so I'll make MANY assumptions and guesses... use at your own risk...

First, guessing that item_date is actually a date, it is ridiculous to store it as a VARCHAR(100). But not knowing how it might be used in the code I would refrain from changing it to a date or datetime - so you might try to reduce it to something like VARCHAR(24) which should accomodate most basic date or datetime equivalent string formats. (If it isn't a date and actually needs 100 characters... revert).

Next, reduce the key size for the link component. If you have reduced item_date to 24 characters and assuming it to be UTF8 then 1000 - 24*4 = 906. Since we know link is UTF8, the max key size for that would then be 226, which is still a generous key for assorted web links!

All of that is going to take ~4K storage just for that key, plus the actual data, plus everything else in the table. If it is a very large data set or a very busy access site that may not be a good way to store it and access it. On the other hand, if it is not large or busy the key is probably extreme overkill... but then we know nothing about the usage.

So, to summarize, consider reducing the size of the item_date attribute, and definitely reduce the length of the link key, so that the total of both times 4 is less than 1000.

A final note: Since this is an untested application, be alert for additional problems...

mike2010 · 01-23-2014, 10:32 PM

Thx for assisting to help.

Oh trust me, I wouldn't be surprised at all if there are problems. He was one of the few that actually offered to take on the project. Since it was regarding adding on to 'simple pie'...which is basically a discontinued product. Some of the details of the project before the coder took it on -

Quote:

The script solely uses SimplePie. I'm using the latest version that runs on Linux. If you know nothing of this software, maybe u should download it / install it first before bidding....to familiarize yourself - http://simplepie.org/downloads/ (the big blue button)

My problems ?

As you can see at the bottom of my test page, it says "showing 1 - 10 out of 78" , the feeds only store the articles that it pulls in initially....nothing is ever accumulated.

this means it gets cut off at 78 total articles, and then the new articles overwrite the old articles. I WANT it to store till 1,000. (or whatever number I specify) into a database. I want mine : "showing 1 - 10 out of 1000" I need someone to help create something to support this. SimplePie by default doesn't need MySql....but i'm wondering if I want it to store till 1,000 will it need a database of some sort ?

customizing any of these options makes no difference :

// Set our paging values
$start = (isset($_GET['start']) && !empty($_GET['start'])) ? $_GET['start'] : 0; // Where do we start?
$length = (isset($_GET['length']) && !empty($_GET['length'])) ? $_GET['length'] : 10; // How many per page?
$max = $feed->get_item_quantity(); // Where do we end?

ok...so basically you're saying :

Code:

`item_date` varchar(100) NOT NULL,

>

Code:

`item_date` varchar(24) NOT NULL,

and

Code:

UNIQUE KEY `link` (`link`(255),`item_date`),

>

Code:

UNIQUE KEY `link` (`link`(200),`item_date`),

?

astrogeek · 01-23-2014, 10:53 PM

Quote:

Originally Posted by mike2010

ok...so basically you're saying :

Code:

`item_date` varchar(100) NOT NULL,

>

Code:

`item_date` varchar(24) NOT NULL,

and

Code:

UNIQUE KEY `link` (`link`(255),`item_date`),

>

Code:

UNIQUE KEY `link` (`link`(200),`item_date`),

?

Yes, those should allow you to create the table at least.

mike2010 · 01-27-2014, 08:22 PM

thx bud, that got it working.