Innodb Table Handler

[Luc_40]

when i hit relationships/foreign keys, i always get the message “the selected table does not have InnoDB table handler, You cannot make relations for the table”. I am sure that it is an InnoDB table.

—

What am i doing wrong ?

[peterlaursen]

Please paste in the 'create statement for the table' from the SQLyog OBJECTS-tab. It should look like

Code:

CREATE TABLE `currentusers` (          
               `uid` varchar(255) NOT NULL,          
               `starttime` int(11) default NULL,    
               `lasttime` int(11) default NULL,      
               PRIMARY KEY  (`uid`)                  
             ) ENGINE=InnoDB DEFAULT CHARSET=latin1

It sounds very much as if it is NOT an INNOdb table! And I don't think there is much that you can do wrong here 😉

Please note that if INNOdb is not enabled with the server configuration than MySQL will 'silently' replace INNOdb with the default table type (that is probably MyISAM)

What is the MySQL server version?

[Luc_40]

strange, it keeps returning MyISAM, even when i do 'change table type to … InnoDB'

codes CREATE TABLE `codes` (

`ID` int(11) NOT NULL auto_increment,

`DOMEIN` tinytext collate latin1_general_ci NOT NULL,

`CODE` tinytext collate latin1_general_ci NOT NULL,

`OMS_N` tinytext collate latin1_general_ci,

`OMS_F` tinytext collate latin1_general_ci,

`OMS_E` tinytext collate latin1_general_ci,

`OMS_D` tinytext collate latin1_general_ci,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC

[peterlaursen]

It is not an INNODB tale but a MYISAM table – it says:

Code:

ENGINE=MyISAM

Note what I wrote:

Please note that if INNOdb is not enabled with the server configuration than MySQL will 'silently' replace INNOdb with the default table type (that is probably MyISAM). You won't get an error than but a warning

If MySQL is ver.5 there is an SQL_MODE available so that an invalid table type used in ALTER TABLE statements will raise an error instead.

[peterlaursen]

That SQL_MODE is 'NO_ENGINE_SUBSTITUTION'

read

http://www.webyog.com/faq/28_72_en.html

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

I think the easiest thing to do here is to get “MySQL Administrator” and check the “support for INNODB” on ther Server Control .. Configure Service -tab. If Server configuration is under your control. Some ISP's disable INNODB.

Of course the server must be restarted, for the change to take effect

From SQLyog .. tools .. show .. variables you can see the current value for system variable “@@have innodb” Or you can use this SQL

Code:

select @@have_innodb;

[peterlaursen]

BTW: what is the reason for using all those TINYTEXTs ?

A TINYTEXT is only 256 characteres according to

(http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html)

2^8 – 1 = 255.

so a CHAR or VARCHAR would probably be much better to use here (much more effective), where you will need INDEXES and CONSTRAINTS.

Read about string types:

http://dev.mysql.com/doc/refman/5.0/en/sto…quirements.html

[Luc_40]

Sorry people, i am indeed a newbie, and i was using sql server 4.1.13. Now that i have updated to 5 it works fine. Sorry, i feel ashame …

By the way : congratulations for the quick response, thanks

Concerning the tiny text : the maximum length needs no more than 10 (codes and domains)

[peterlaursen]

Quote:

the maximum length needs no more than 10 (codes and domains)

then I would recommend that you use a char(10) or varchar(10) for those fields.

BTW. InnoDB works fine with 4.1.x too. But it looks like a fresh server install solved the issue!

[Author]

Posts