From: Ben Clewett
Date: May 23 2006 4:09pm
Subject: Re: InnoDB problems under 5.1.9
List-Archive: http://lists.mysql.com/mysql/198222
Message-Id: <447333BA.1020908@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks for the excellent reference, this gives me a lot to go on.

My server is in bits at the moment, I'll let you know when it's up again!

Ben

Dan Buettner wrote:
> Ben, looks like you've either got it disabled in my.cnf or with a 
> startup flag, or you've not set all the needed options for InnoDB.
> 
> See http://dev.mysql.com/doc/refman/5.0/en/mysqld-max.html, near the 
> bottom of the page it explains what DISABLED means and refers you to the 
> error log for messages.
> 
> Dan
> 
> Ben Clewett wrote:
>> Hi Dan,
>>
>> This is what I have.  What does this mean with regards to InnoDB?
>>
>> +------------+----------+----------------------------------------------------------------+--------------+-----+------------+ 
>>
>> | Engine     | Support  | Comment                  | Transactions | 
>> XA  | Savepoints |
>> +------------+----------+----------------------------------------------------------------+--------------+-----+------------+ 
>>
>> | CSV        | YES      | CSV storage engine                  | 
>> NO           | NO  | NO         |
>> | MEMORY     | YES      | Hash based, stored in memory, useful for 
>> temporary tables      | NO           | NO  | NO         |
>> | MRG_MYISAM | YES      | Collection of identical MyISAM tables 
>>                  | NO           | NO  | NO         |
>> | InnoDB     | DISABLED | Supports transactions, row-level locking, 
>> and foreign keys     | YES          | YES | YES        |
>> | BLACKHOLE  | YES      | /dev/null storage engine (anything you write 
>> to it disappears) | NO           | NO  | NO         |
>> | MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great 
>> performance         | NO           | NO  | NO         |
>> | BerkeleyDB | DISABLED | Supports transactions and page-level locking 
>>                  | YES          | NO  | YES        |
>> | ARCHIVE    | YES      | Archive storage engine                  | 
>> NO           | NO  | NO         |
>> +------------+----------+----------------------------------------------------------------+--------------+-----+------------+ 
>>
>>
>> Thanks,
>>
>>
>> Dan Buettner wrote:
>>> Ben, what does SHOW ENGINES show you?  It should list all known 
>>> storage engines and indicate whether your MySQL install supports it 
>>> or not.
>>>
>>> Here's mine (5.0.21) for comparison; I was able to create a test 
>>> table as InnoDB and the SHOW CREATE showed it as InnoDB:
>>>
>>> -> show engines;
>>> +------------+---------+----------------------------------------------------------------+ 
>>>
>>> | Engine     | Support | Comment                 |
>>> +------------+---------+----------------------------------------------------------------+ 
>>>
>>> | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great 
>>> performance         |
>>> | MEMORY     | YES     | Hash based, stored in memory, useful for 
>>> temporary tables      |
>>> | InnoDB     | YES     | Supports transactions, row-level locking, 
>>> and foreign keys     |
>>> | BerkeleyDB | NO      | Supports transactions and page-level locking 
>>>                 |
>>> | BLACKHOLE  | NO      | /dev/null storage engine (anything you write 
>>> to it disappears) |
>>> | EXAMPLE    | NO      | Example storage engine                 |
>>> | ARCHIVE    | YES     | Archive storage engine                 |
>>> | CSV        | NO      | CSV storage engine                 |
>>> | ndbcluster | NO      | Clustered, fault-tolerant, memory-based 
>>> tables                 |
>>> | FEDERATED  | NO      | Federated MySQL storage 
>>> engine                 |
>>> | MRG_MYISAM | YES     | Collection of identical MyISAM tables 
>>>                 |
>>> | ISAM       | NO      | Obsolete storage engine                 |
>>> +------------+---------+----------------------------------------------------------------+ 
>>>
>>> 12 rows in set (0.00 sec)
>>>
>>>
>>>
>>>
>>> Ben Clewett wrote:
>>>> Hi Gerald,
>>>>
>>>> I am sure I don't have this in my my.cfg.  I am using the supplied 
>>>> 'large table' my.cfg.  The *only* innodb option I have is the 
>>>> command line parameter to mysqld:
>>>>
>>>> --innodb
>>>>
>>>> If anybody has any other options about how to get innodb working in 
>>>> 5.1.9, I'd be very interested!
>>>>
>>>> Thanks for the advise,
>>>>
>>>> Ben
>>>>
>>>>
>>>> gerald_clark wrote:
>>>>> Ben Clewett wrote:
>>>>>
>>>>>> Dear MySQL,
>>>>>>
>>>>>> I've installed 5.1.9 from source on a SUSE 10 box.  But I can't 
>>>>>> get InnoDB tables respected.
>>>>>>
>>>>>> I have used the correct compilation flag (--with-innodb).
>>>>>> SHOW VARIABLES; lists all the usual innodb variables.
>>>>>> The innodb table space has been created in ~/var/ibdata1.
>>>>>>
>>>>>> But if I enter:
>>>>>>
>>>>>> CREATE TABLE a (
>>>>>>   a int NOT NULL PRIMARY KEY
>>>>>> ) ENGINE=InnoDB;
>>>>>>
>>>>>> SHOW CREATE TABLE a;
>>>>>>
>>>>>> CREATE TABLE `a` (
>>>>>>   `a` int(10) NOT NULL PRIMARY KEY
>>>>>> ) ENGINE=MyISAM
>>>>>>
>>>>>> As you can see, an InnoDB has become an MyISAM and will be stored 
>>>>>> in ~/var/test/a.*
>>>>>>
>>>>>> I am using the large table .cnf file.  Everything else is much as 
>>>>>> default.
>>>>>>
>>>>>> Can anybody help me?
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Ben
>>>>>
>>>>> make sure you don't have
>>>>> skip--innodb
>>>>> in your my.cnf file.
>>>>>
>>>>
>>>>
>>>
>>
>>
>