problem in after moving to high php/msql

[terrymechan]

I could use copy/data structure command in sqlyog to create a copy table OK when running under php 4.2.3 and mysql 3.32.39

MY ISp has upgraded the server to php5.01 , and mysql 4.0.20

The copy/data structure command now does not work for of my tables as I get an error message

error 1070 specified key was too long maximum key length is 500

I notice that under sqlyog facility of “manage indexes” on php 4* , the full textbox is checked (as it should be) but when I imported the tables to the new server php5/mysql 4,0.20 the check box has been automatically unchecked

why can't I use the copy under the older php/mysql but not the new server?

TJM

[peterlaursen]

Do you have an index/key that is (so) very long ??

There should be few problems with upgrading from Mysql 3.x to 4.0.x (it's worse with 4.1.x).

I have been looking for some server setting relatind to your problem but can't find it.

Can't find a setting in php.ini either …

I don't believe it's an sqlyog issue.

Did you try copying the sql from the history pane and execute it from for instance “My Sql Query Browser”.

Probably you'll get the same error message.

So my best guess is that the ISP haven't finished “trimming” their server parmetres yet. Did you contact then ?

[Shadow]

I agree with Peterlaursen, this error does not seem to have anything to to with SQLyog. MySql forums suggest to decrease field lengths in the given index. As far as I'm concerned, this limit has been imposed to prevent the creation large index entries that would slow down selects and would consume extra storage capacity.

[terrymechan]

thanks for the information

I have reduced the length of some of my field in a test table so that the full text index is below the 500 allowed and now I can copy

my isp owner is not so familiar with trimmings.

Any idea on where he would need to go to increase the maximum mysql key length allowable

I'd like to take it up to 650 to keep the integrity of my user inputs the same

rgds

tjm

[Shadow]

Thre is no way to modify this limit – apart from modifying the source code of MySql. See this feature request. If you use utf-8 encoding, then you may consider choosing another one, as utf-8 uses 3 bytes to store a character.

[terrymechan]

According to the docs, the limit should be 1000 not 500.

If 500 has been set by default, then apparently it can be re-compiled

here is an extract from the manual on http://help.scibit.com/mysql/manual_Table_types.html

---

The maximum key length is 500 bytes by default, and 1000 bytes as of MySQL 4.1.2. This can be changed by recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

---

MY ISP provider doesn't know how to do this. Can you offer any help pse

[Shadow]

No. I'm good at administering/tuning MySql, but recompilation is a different issue. I believe, it requires more than just changing one variable from 500 to 1000, MySql would have done it otherwise… If I were the ISP, then I would write to MySql and ask for their help, or I would ask for some help on a MySql forum.

[terrymechan]

thanks

I'll take this away now as it is not a sqlyog problem

regds

terry mechan

http://www.morgle.com

[Shadow]

I would be grateful if you notified me of the results! Just in case I run into the same error once again…

Besides, 500 bytes value corresponds to specs as your ISP has a 4.0 and not 4.1.2 or higher.

[Author]

Posts