DBI/DBD::mysql: Detecting InnoDB?

Hello,

I'm working on a database package right now that will support
multiple backend drivers (currently just DBD::Pg and DBD::mysql, possibly
more in the future). With MySQL, you can only support transactions and
foreign keys if you have enabled the InnoDB database backend. This would
undoubtedly mean that you would want to use a different database schema
depending on whether InnoDB is available.

Is there a clean way to detect whether InnoDB support is available
on the MySQL server? The best I've been able to come up with so far, is
attempting to create a temporary table with the "TYPE=InnoDB" argument and
testing to see if it fails, but if there is just some flag I can query the
database with I would much rather use that.

Thanks in advance,
Tyler

Re: DBI/DBD::mysql: Detecting InnoDB?

Hi.

13Nov2005 @ 13:04 Tyler MacDonald thusly spake
> Hello,
>
> I'm working on a database package right now that will support
> multiple backend drivers (currently just DBD::Pg and DBD::mysql, possibly
> more in the future). With MySQL, you can only support transactions and
> foreign keys if you have enabled the InnoDB database backend. This would
> undoubtedly mean that you would want to use a different database schema
> depending on whether InnoDB is available.
>
> Is there a clean way to detect whether InnoDB support is available
> on the MySQL server? The best I've been able to come up with so far, is
> attempting to create a temporary table with the "TYPE=InnoDB" argument and

You could try the 'mysqladmin -u -p -V'
If your version of mysql is later than 3.23.6 it will support InnoDB.

Also the command from the mysql prompt.
mysql> 'show table status;'
shows table types.

hth.
Kind regards.
Luke

> testing to see if it fails, but if there is just some flag I can query the
> database with I would much rather use that.
>
> Thanks in advance,
> Tyler
>

--
.............._..
..| .| |.|/.|_ .
..|__.|_|.|\.|_ .
:61 421 276 282:

Re: DBI/DBD::mysql: Detecting InnoDB?

On 11/13/05 4:04 PM, Tyler MacDonald wrote:
> Is there a clean way to detect whether InnoDB support is available
> on the MySQL server? The best I've been able to come up with so far, is
> attempting to create a temporary table with the "TYPE=InnoDB" argument and
> testing to see if it fail

That won't work either. Some (all?) versions of MySQL will silently create
a MyISAM table instead. To find out what really happened, you have to try
to create an InnoDB table and then check the actual table type with a "SHOW
TABLE STATUS FROM ..." query afterwards. That's the only way I've found to
do it, anyway.

-John

Re: DBI/DBD::mysql: Detecting InnoDB?

luke@chipcity.com.au wrote:
> You could try the 'mysqladmin -u -p -V'
> If your version of mysql is later than 3.23.6 it will support InnoDB.

Unless the "skip-innodb" option is enabled in their conf file. Also,
this requires that the user not only have DBD::mysql installed, but have
mysqladmin installed as well.

> mysql> 'show table status;'

Yes, but only if you've already created an InnoDB table. :-/

I guess that leaves me with attempting to create a temporary table,
and if that fails, assuming it's turned off... Unless, is there any perl/DBI
way for me to interrogate the MySQL server to determine what version it is,
and whether or not "skip-innodb" has been turned on?

Thanks,
Tyler

Re: DBI/DBD::mysql: Detecting InnoDB?

On 11/13/2005 3:04 PM, Tyler MacDonald wrote:

> Is there a clean way to detect whether InnoDB support is available
> on the MySQL server? The best I've been able to come up with so far, is
> attempting to create a temporary table with the "TYPE=InnoDB" argument and
> testing to see if it fails, but if there is just some flag I can query the
> database with I would much rather use that.

For MySQL >= 4.1.2 you can use 'show engines':

http://dev.mysql.com/doc/refman/4.1/en/show-engines.html

Paul