DBI with MySQL InnoDB tables troubles

Ouato has asked for the wisdom of the Perl Monks concerning the following question:

I have understood from an other post that there was no problem to use DBI with InnoDB tables, but I still have a problem :

I used to have MyISAM tables where everything was working fine, and I created a whole new set of InnoDB tables. When I call Inserts on these new tables, it does NOT insert my values, and DBI::execute is not returning any error, still the 'auto_increment'... increments (on table status).
Table is :
______________________________

CREATE TABLE `log` (
`id` int(11) NOT NULL auto_increment,
`timestamp` datetime NOT NULL default '0000-00-00 
00:00:00',
`alert` enum('y','n') NOT NULL default 'y',
`id_daemon` int(11) NOT NULL default '0',
`id_event_type` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `id_daemon` (`id_daemon`),
KEY `id_event_type` (`id_event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
[download]

___________________________________
Foreign keys :

ALTER TABLE `log`
ADD CONSTRAINT `log_ibfk_1` FOREIGN KEY (`id_daemon`) 
REFERENCES `daemons` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `log_ibfk_2` FOREIGN KEY (`id_event_type`) 
REFERENCES `event_types` (`id`) ON DELETE CASCADE;
[download]

___________________________________

Code :

$req = "INSERT INTO log
(`timestamp`,`alert`,`id_daemon`,`id_event_type`)
VALUES
(NOW(),'y','31','3')";
$query = $dbh->prepare($req);
$query->execute or sayAll("Query failed : $req".$query->errstr);
[download]

When I paste the Insert request into 'mysql' in command line, or by phpMyAdmin... it works... but not with DBI.

Can anybody bring me help ?

Comment on DBI with MySQL InnoDB tables troubles Select or Download Code

Replies are listed 'Best First'.
Re: DBI with MySQL InnoDB tables troubles by erix (Prior) on Jul 26, 2007 at 18:41 UTC
If there really are no errors, maybe you need to issue a commit, after the execute? `$dbh->commit`	[reply] [d/l]
Re: DBI with MySQL InnoDB tables troubles by TOD (Friar) on Jul 26, 2007 at 12:46 UTC
maybe fiddling about the `DBI->trace` will give you a hint. -------------------------------- masses are the opiate for religion.	[reply] [d/l]
Re: DBI with MySQL InnoDB tables troubles by Ouato (Novice) on Jul 26, 2007 at 13:12 UTC
Thanks it could have helped... but for me 'DESTROY' does only mean it does not work... and not why :( -> prepare for DBD::mysql::db (DBI::db=HASH(0xae4410)~0xaf6400 ' INSERT INTO log (`timestamp`,`alert`,`id_daemon`,`id_e +vent_type`) VALUES (NOW(),'y','31','3'); ') thr#505010 dbd_st_prepare calling count_params (counting params emulation) <- prepare= DBI::st=HASH(0xb04b00) at sermon line 809 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xaeae60)~INNER) thr#5 +05010 <- DESTROY= undef at sermon line 810 -> execute for DBD::mysql::st (DBI::st=HASH(0xb04b00)~0xaeadd0) th +r#505010 -> dbd_st_execute for 00ae9a20 [download]	[reply] [d/l]
Re^2: DBI with MySQL InnoDB tables troubles by Tobin Cataldo (Monk) on Jul 26, 2007 at 13:47 UTC
Sometimes when working with InnoDB, I need to quote strings and NOT quote integers when preparing an INSERT. And also you might try `prepare() or die();` [download] on your prepare statement. Also, are those backticks in your prepare statement? Try simple quotes.	[reply] [d/l]
Re^2: DBI with MySQL InnoDB tables troubles by ysth (Canon) on Jul 26, 2007 at 18:36 UTC
That doesn't look right: some statement handle is being destroyed after prepare returns and before execute is called. Could you show us the actual code you ran to get that trace? For comparison, when I do this: my $dbh = ...; $req = "INSERT INTO log (`timestamp`,`alert`,`id_daemon`,`id_event_type`) VALUES (NOW(),'y','31','3')"; DBI->trace(4); $query = $dbh->prepare($req); $query->execute(); DBI->trace(0); [download] I get this trace: DBI 1.53-ithread default trace level set to 0x0/4 (pid 716) -> prepare for DBD::mysql::db (DBI::db=HASH(0x7804d0)~0x8fc970 'IN +SERT INTO log (`timestamp`,`alert`,`id_daemon`,`id_event_type`) VALUES (NOW(),'y','31','3')') thr#603010 New DBI::st (for DBD::mysql::st, parent=DBI::db=HASH(0x8fc970), id +=) dbih_setup_handle(DBI::st=HASH(0x8fcbf0)=>DBI::st=HASH(0x60a480), +DBD::mysql::st, 8fcc00, Null!) dbih_make_com(DBI::db=HASH(0x8fc970), 8fd720, DBD::mysql::st, 440, + 0) thr#603010 dbd_st_prepare calling count_params (counting params emulation) <- prepare= DBI::st=HASH(0x8fcbf0) at - line 7 -> execute for DBD::mysql::st (DBI::st=HASH(0x8fcbf0)~0x60a480) th +r#603010 -> dbd_st_execute for 008fcd00 ---> parse_params with statement INSERT INTO log (`timestamp`,`alert`,`id_daemon`,`id_event_type`) VALUES (NOW(),'y','31','3') num params 0 mysql_st_internal_execute <- dbd_st_execute returning imp_sth->row_num 1 <- execute= 1 at - line 8 [download]	[reply] [d/l] [select]
Re^2: DBI with MySQL InnoDB tables troubles by Ouato (Novice) on Jul 26, 2007 at 15:47 UTC
Thank you but it didn't do the trick... `INSERT INTO log (timestamp,alert,id_daemon,id_event_type) VALUES (NOW(),'n',31,3);` [download] does not do anything. `prepare or die()` never go through die()... as execute.	[reply] [d/l] [select]
Re^3: DBI with MySQL InnoDB tables troubles by Tobin Cataldo (Monk) on Jul 26, 2007 at 16:53 UTC
What is the `sayAll()` subroutine you are calling? Can't you use `die()` or `warn()` until you figure out the issue?	[reply] [d/l] [select]
Re: DBI with MySQL InnoDB tables troubles by Ouato (Novice) on Jul 27, 2007 at 07:49 UTC
WHAT A ... This was "only" the commit... with `$dbh->commit` everything works fine... I turned on the Autocommit in the dbh creation and everything also worked. Strange that it worked with MyISAM tables without the commit, and not with InnoDB tables. Thank you everybody for your help.	[reply] [d/l]
Re^2: DBI with MySQL InnoDB tables troubles by andreas1234567 (Vicar) on Jul 27, 2007 at 14:06 UTC
Strange that it worked with MyISAM tables without the commit, and not with InnoDB tables. Isn't that because MyISAM tables are non-transactional (i.e they do not support commit, rollback), while InnoDB are transactional? See this (dev.mysql.com) article. -- Andreas	[reply]