Re: InnoDB problem.
What's the MySQL error log have to say? - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 3:39:55 PM Subject: Re: InnoDB problem. Yep, I do backup of /home/mysql/ib* files too :D What it occurs is that even with ibdata1, ib_logfile0 and ib_logfile1 in it's due place, MySQL (provided by xampp) shows me the following messages, when trying to open InnoDB tables: SHOW FULL FIELDS FROM `my_innodb_table` ; #1286 - Unknown table engine 'InnoDB' skin-innodb is commented but either way InnoDB engine are not shown when I execute show engines command. 2013/7/22 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: InnoDB problem.
Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
RE: InnoDB problem.
Did you change innodb_log_file_size? -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: InnoDB problem.
2013/7/23 Rick James rja...@yahoo-inc.com Did you change innodb_log_file_size? innodb_log_file_size error always appears in the logs...he only posted a few lines of his log...but I guess (or I want to believe) he's gone through the whole log before starting the thread :-) Manuel -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Manuel Aróstegui Systems Team tuenti.com
RE: InnoDB problem.
Either change it back, or delete the log files so that they will be built in the new size. (Backup the entire tree, just in case.) From: Manuel Arostegui [mailto:man...@tuenti.com] Sent: Tuesday, July 23, 2013 1:05 PM To: Rick James Cc: Johan De Meersman; Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. 2013/7/23 Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com Did you change innodb_log_file_size? innodb_log_file_size error always appears in the logs...he only posted a few lines of his log...but I guess (or I want to believe) he's gone through the whole log before starting the thread :-) Manuel -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.bemailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.commailto:luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Manuel Aróstegui Systems Team tuenti.comhttp://tuenti.com
Re: InnoDB problem.
- Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com Subject: InnoDB problem. Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Oops. You should always read the fine manual. You took file-level backups, yes? Did they include the ibdata1 and similar files? Those contain innodb's dictionary - and in default installs also all the actual tables. The database/* files only contain the .frm, for innodb. If I'm right, you haven't got a backup at all. I'm crossing my fingers that I'm wrong... -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB: Problem with innobackup
James,
page number 22357 is corrupt. InnoDB Hot Backup notices these checksum
errors, and refuses to do the backup. Sometimes it happens that an unused
(i.e., freed) page in an ibdata file becomes corrupt. Then it would be nice
to have some tool to reset the checksums on that page, so that mysqld or
ibbackup would not complain of the page.
I wrote now a little C program innodb_page_checksum_reset.c that can be used
for this :). The program code is below. It will reset page 22357 in ibdata1.
I can send a Linux binary, if you do not have a working C compiler.
To compile in Linux:
gcc -o reset innodb_page_checksum_reset.c
Regards,
Heikki
/* This program is for resetting the lsn and checksum fields of an InnoDB
page, so that ibbackup and mysqld will not complain of corruption. NOTE that
this program does NOT fix the corruption, though! Read the instructions
below
VERY carefully.
Copyright 2005 Innobase Oy. This program is released under the GNU GPL
license version 2. */
#include stdio.h
#include sys/types.h
#include sys/stat.h
#include fcntl.h
int
main(void)
{
off_t page_number;
int file;
off_t offs;
off_t ret_offset;
ssize_t ret;
char* file_name;
charbuf[8];
/* page_number is the number of the page in the ibdata file that you
want to reset. Note that if you have several ibdata files, you have
to
calculate the page number in the particular ibdata file, and NOT use
the global tablespace page number. InnoDB page size is 16 kB. */
file_name = ibdata1;
page_number = 22357;
offs = page_number * 16 * 1024;
memset(buf, '\0', 8);
file = open(file_name, O_RDWR);
if (file == -1) {
printf(Cannot open %s\n, file_name);
exit(1);
}
/* Reset FIL_PAGE_SPACE_OR_CHKSUM */
ret_offset = lseek(file, offs, SEEK_SET);
if (ret_offset 0) {
printf(Error in lseek 1\n);
exit(1);
}
ret = write(file, buf, (ssize_t)4);
if (ret != 4) {
printf(Error in write 1\n);
exit(1);
}
/* Read FIL_PAGE_LSN */
ret_offset = lseek(file, offs + 16, SEEK_SET);
if (ret_offset 0) {
printf(Error in lseek 2\n);
exit(1);
}
ret = read(file, buf, (ssize_t)8);
if (ret != 8) {
printf(Error in read\n);
exit(1);
}
/* Reset FIL_PAGE_END_LSN_OLD_CHKSUM */
ret_offset = lseek(file, offs + 16 * 1024 - 8, SEEK_SET);
if (ret_offset 0) {
printf(Error in lseek 3\n);
exit(1);
}
ret = write(file, buf, (ssize_t)8);
if (ret != 8) {
printf(Error in write 2\n);
exit(1);
}
close(file);
printf(lsn and checksum fields of page %lu in file %s reset\n,
(ulong)page_number, file_name);
return(0);
}
- Original Message -
From: James Green [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, February 18, 2005 5:53 PM
Subject: InnoDB: Problem with innobackup
Hi,
On running the hot backup tool we receive:
ibbackup: Re-reading page at offset 0 366297088 in
/var/lib/mysql/data/ibdata1
ibbackup: Re-reading page at offset 0 366297088 in
/var/lib/mysql/data/ibdata1
050218 15:18:01 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
eeaefd1a57557b35693200017183e16e45bf000[garbage
continues]
(.(.u:.%.1./.7u.E.e8.'%.e.c9]q...;InnoDB: End of page dump
050218 15:18:01 InnoDB: Page checksum 4004445466,
prior-to-4.0.14-form checksum 3154721000
InnoDB: stored checksum 4004445466, prior-to-4.0.14-form stored
checksum 2825075037
InnoDB: Page lsn 1 1904468334, low 4 bytes of lsn at page end 1904466222
InnoDB: Page number (if stored to page already) 22357,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 162
ibbackup: Error: page at offset 0 366297088 in
/var/lib/mysql/data/ibdata1 seems corrupt!
innobackup: Error: ibbackup child process has died at innobackup.pl line
332.
We have gone through (via a script) and every table in every database
(all by 'mysql' is InnoDB) returns 'OK' using 'check table'.
We did suffer a hardware failure which required a table to be dropped
and rebuilt, however that was resolved and everything appears to be
operating fine now. Except we want the hot backup to work and it
clearly doesn't.
Looking for options. We have mysqldumps but clearly restoration will
be very slow. The server is Debian Linux (stable) with MySQL-4.1.9
from the mysql.com binary tarball.
Help! Many thanks!
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Re: InnoDB: Problem with innobackup
James, We've had this issue twice (every 4 months) - running on 4.0.20 - due to an old kernel (we just upgraded the kernel after the last issue). Do you have a replicated (slave) database? We shut down the master and then the slave (a few minutes after the master to let all changes propigate), and then copy the data files from the slave to the master and restart. We have to rebuild the slave after, but the database is up and running at that point. If that doesn't work, what about using an older (valid) backup and your binary logs? You can turn the binary logs into the SQL statements and run them on the old backup to bring the database up to date... Also, be careful about checking the tables - if one is found to be corrupt, it is marked as unusable until it is fixed. There are also different levels of CHECK TABLE - are you using the appropriate one? David James Green wrote: Hi, On running the hot backup tool we receive: ibbackup: Re-reading page at offset 0 366297088 in /var/lib/mysql/data/ibdata1 ibbackup: Re-reading page at offset 0 366297088 in /var/lib/mysql/data/ibdata1 050218 15:18:01 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex eeaefd1a57557b35693200017183e16e45bf000[garbage continues] (.(.u:.%.1./.7u.E.e8.'%.e.c9]q...;InnoDB: End of page dump 050218 15:18:01 InnoDB: Page checksum 4004445466, prior-to-4.0.14-form checksum 3154721000 InnoDB: stored checksum 4004445466, prior-to-4.0.14-form stored checksum 2825075037 InnoDB: Page lsn 1 1904468334, low 4 bytes of lsn at page end 1904466222 InnoDB: Page number (if stored to page already) 22357, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 162 ibbackup: Error: page at offset 0 366297088 in /var/lib/mysql/data/ibdata1 seems corrupt! innobackup: Error: ibbackup child process has died at innobackup.pl line 332. We have gone through (via a script) and every table in every database (all by 'mysql' is InnoDB) returns 'OK' using 'check table'. We did suffer a hardware failure which required a table to be dropped and rebuilt, however that was resolved and everything appears to be operating fine now. Except we want the hot backup to work and it clearly doesn't. Looking for options. We have mysqldumps but clearly restoration will be very slow. The server is Debian Linux (stable) with MySQL-4.1.9 from the mysql.com binary tarball. Help! Many thanks! James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb problem
Hello. Use the max attribute. See: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html If you want to decrease the size of your tablespace, see: http://dev.mysql.com/doc/mysql/en/adding-and-removing.html [EMAIL PROTECTED] wrote: Hello, my name's Matteo, probably my question is basic but I'm new with mysql. I've an application that write some milion of row in mysql innodb table. Every day my application creates a new table, write data and drop table oldest than 15 days. After the drop table execution command the disk space on my linux server doen't shrink and the disk space grow winthout end. is there a way or configuration setting to resolve this problem? Many thanks if someone can help me!!! Best regards, Matteo -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb problem
Matteo, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 04, 2005 3:51 PM Subject: innodb problem Hello, my name's Matteo, probably my question is basic but I'm new with mysql. I've an application that write some milion of row in mysql innodb table. Every day my application creates a new table, write data and drop table oldest than 15 days. After the drop table execution command the disk space on my linux server doen't shrink and the disk space grow winthout end. is there a way or configuration setting to resolve this problem? an upgrade to 4.1.9, and reading http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html will help. When you DROP a table, the .ibd file will be deleted, and the disk space is released to the operating system. Many thanks if someone can help me!!! Best regards, Matteo Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problem, yet performance increased!?
In article [EMAIL PROTECTED], James Green [EMAIL PROTECTED] writes: Unfortunately when we repeated on the live server, whilst we got a 200% performance boost (estimate) again, we failed to notice that innodb is in DISABLED state, and yet alter table returned ok. It's one of the ugliest misfeatures of MySQL that it doesn't always complain loudly, but instead tries to silently do what it thinks is right. We therefore conclude that the alter table command failed siliently (really bad bug that), however we cannot explain the performance boost. Probably the ALTER TABLE got silently changed to OPTIMIZE TABLE. Clearly before shutting anything down or fixing things, we would like to know if these tables are safely still myisam. Yes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problem, yet performance increased!?
You can determine table type with SHOW CREATE TABLE table_name or SHOW TABLE STATUS LIKE 'table_name'; From the manual http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html, If a storage engine is specified that is not available, MySQL uses MyISAM instead. That applies to ALTER as well as to CREATE. Starting with 4.1.1, you get a warning when that happens. In earlier versions, it's silent. Also from the manual http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. Your performance increase is probably due to better organized data and rebuilt indexes resulting from the copy operation. Michael James Green wrote: Hi Recently we performed an 'alter table' on a dev box to change from myisam to innodb, and it resulted in considerable speed improvement. in the lab setting. Unfortunately when we repeated on the live server, whilst we got a 200% performance boost (estimate) again, we failed to notice that innodb is in DISABLED state, and yet alter table returned ok. Looking at the tables on the disk, the only innodb references we can find were last modified a few days ago, whereas the myisam versions have the current date. We therefore conclude that the alter table command failed siliently (really bad bug that), however we cannot explain the performance boost. Clearly before shutting anything down or fixing things, we would like to know if these tables are safely still myisam. We would also like to know where the performance boost has come from - one of the alter tables commands took roughly 45 minutes to finish - it did something, we just don't know what! Any help duly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: InnoDB Problem
On Tuesday 18 March 2003 02:26, Custódio de Matos Lima wrote: Im having some troubles when creating the foreign key constraints in a InnoDB database. The problem is, i can create the InnoDB table, but im having a little difficult to make the connections with other tables. The error that apears is like that: Erro Comando SQL : alter table tab3 add constraint foreign key (cod2) references tab2 (cod2) on update cascade on delete cascade; Mensagens do MySQL : Can't create table '.\ola\#sql-52c_9e.frm' (errno: 150) Check that columns are indexed, that columns have the same type. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb problem
Massimo, Tuesday, July 16, 2002, 1:02:15 PM, you wrote: MP I am a new user of mysql MP I installed 3.23.51 on my nt MP this is my my.ini files. MP [mysqld] MP innodb_data_home_dir = MP innodb_data_file_path = ibdata1:30M:autoextend MP default-table-type=innodb MP [WinMySQLAdmin] MP Server=C:/mysql/bin/mysqld-max-nt.exe MP When I use the winmysqladmin it report in HAVE_INNODB disables; MP When I create a table mysql create if always with myisam format. MP Which is my error ? Look at the manual: If you specify innodb_data_home_dir as an empty string, then you can give absolute paths to your data files in innodb_data_file_path. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: innodb problem (with JDBC/transactions)
Hi, We are currently facing the same problem (Deadlock found when trying to get lock; Try restarting transaction) in our production environnement. We are using InnoDB tables (mysqk 3.23.48-max) with Jboss 2.4.4 and JDBC driver mm.mysql-2.0.11-bin.jar / RedHat 7.1. Could you please tell me how to fix this ? Thanks, jb - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: innodb problem (with JDBC/transactions)
Erik, please send me the lock monitor output. Version 3.23.44 has a better monitor where it is easier to correlate who locked what. But the way to proceed with an older lock monitor is to run individual SQL statements and look what they lock. In a single user environment all locks should be due to the operations of the single user. At 12:56 AM 10/28/01 -0400, you wrote: I tried the lock monitor, and the info it produced seemed to be impossible to correlate with the actual data operations. On top of that, I run it as a service, so I have to uninstall the service just to do use the lock monitor, I think you can just go the the NT control panel, the Services icon, and manually disable the mysqld service. Then go to the bin directory and do ...mysqld-max --console When doing debugging it is always advisable to run mysqld from the MS-DOS prompt. Then you see what is happening. so hesitate to run it. If you think you can actually make sense out of its output, let me know, and I'll do it again and e-mail it. I have had similar problems using SQL Server and Microsoft's JDBC driver, but was able to get rid of them by setting the transaction isolation to TRANSACTION_READ_UNCOMMITTED. This didn't help with MySQL/JDBC, though. I tried all isolation levels to no avail. That suggests it produced deadlocks also on MS SQL Server. Setting to 'read uncommitted' reduces locking and prevents some deadlocks. But the consistency of transactions is then compromised. Here is the pattern... The JSP reads all the rows in the table. I can then successfully update individual rows. With each update it rereads all rows again. Even if I don't update any rows, it still deadlocks when I then try to create a new row. The only time it doesn't deadlock is when I restart everything, and then create before I read anything. Does all this happen in a single transaction within a single connection? Do you call COMMIT in between? Are there several concurrent users? What is an exact sequence of operations which leads to a deadlock? Does it do a SELECT ... FOR UPDATE? These questions would be answered by looking at the lock monitor output. Why do you read the rows several times? If you read the rows and use a locking read (...FOR UPDATE or ...LOCK IN SHARE MODE), then no one else can change them in the meantime until you call COMMIT. If you insert new rows, bear in mind InnoDB does next-key locking to prevent phantoms: reading all the rows with a locking read also prevents all inserts by others to the result set. This is different from Oracle which does not prevent phantoms. See the manual at http://www.innodb.com/ibman.html Keep in mind that all of this is managed via EJBs and the container, and I am using connection pooling. This means that this activity may occur over several connections. Are there concurrent operations? A deadlock should only happen if there are at least 2 users, or if the client is badly written and it divides the work of a single user to two connections. Considering that I lead three systems over the past two years using VB/COM/ADO and Oracle and SQL Server without a single deadlock, even though they all use a lot of transactions, this is not encouraging. I need to be able to deliver quickly, yet I'm stuck on my first EJB because I insist on using open source. PLEASE HELP! The solution is to analyze the locking behavior of your application. What it locks at what phase, and when does it call COMMIT to release the locks. JDBC Driver: mm.mysql-2.0.6.1.jar MySQL: mysql-max-3.23.42-win Thanks, Erik Regards, Heikki http://www.innodb.com -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 3:11 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: innodb problem (with JDBC/transactions) Erik, run the MySQL server mysqld from a command prompt and do with the mysql client: mysqlcreate table innodb_lock_monitor(a int) type = innodb; (assuming you run a recent version). Then mysqld will print lock information to the standard output and you see what is happening. Regards, Heikki http://www.innodb.com/ibman.html Has anyone encountered locking problems using JDBC with InnoDB tables and transactions? I am wondering if the MySQL JDBC was designed to handle transactions properly since InnoDB is a relatively recent addition to MySQL. The problem I am running into is the 100 locking error, as if the previous transaction hasn't committed. What complicates this is that I am using Bean Managed Persistance (BMP) with container managed transactions with JBoss as my EJB container. This means that I don't actually write the transaction code, so it's hard to tell exactly what's going on. A transaction is generally defined by the container as each method call that the client makes. I have the transaction option set to Required for all the bean's methods, and the 10 error occurs when the client makes a second call for the same
Re: innodb problem (with JDBC/transactions)
Erik, run the MySQL server mysqld from a command prompt and do with the mysql client: mysqlcreate table innodb_lock_monitor(a int) type = innodb; (assuming you run a recent version). Then mysqld will print lock information to the standard output and you see what is happening. Regards, Heikki http://www.innodb.com/ibman.html Has anyone encountered locking problems using JDBC with InnoDB tables and transactions? I am wondering if the MySQL JDBC was designed to handle transactions properly since InnoDB is a relatively recent addition to MySQL. The problem I am running into is the 100 locking error, as if the previous transaction hasn't committed. What complicates this is that I am using Bean Managed Persistance (BMP) with container managed transactions with JBoss as my EJB container. This means that I don't actually write the transaction code, so it's hard to tell exactly what's going on. A transaction is generally defined by the container as each method call that the client makes. I have the transaction option set to Required for all the bean's methods, and the 10 error occurs when the client makes a second call for the same table row. An example would be creating a row, then trying to make a second method call to recreate the same row (resubmitting the JSP page.) This should return a duplicate row error, but instead produces a locking error, which the client never sees (shows up on JBoss log.) This makes it seam as though the lock from the original transaction wasn't released, and perhaps the database did not even receive or process the COMMIT. Is anyone having similar problems, and hopefully found a solution? Thanks, Erik - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: innodb problem (with JDBC/transactions)
Has anyone encountered locking problems using JDBC with InnoDB tables and transactions? I am wondering if the MySQL JDBC was designed to handle transactions properly since InnoDB is a relatively recent addition to MySQL. The problem I am running into is the 100 locking error, as if the previous transaction hasn't committed. What complicates this is that I am using Bean Managed Persistance (BMP) with container managed transactions with JBoss as my EJB container. This means that I don't actually write the transaction code, so it's hard to tell exactly what's going on. A transaction is generally defined by the container as each method call that the client makes. I have the transaction option set to Required for all the bean's methods, and the 10 error occurs when the client makes a second call for the same table row. An example would be creating a row, then trying to make a second method call to recreate the same row (resubmitting the JSP page.) This should return a duplicate row error, but instead produces a locking error, which the client never sees (shows up on JBoss log.) This makes it seam as though the lock from the original transaction wasn't released, and perhaps the database did not even receive or process the COMMIT. Is anyone having similar problems, and hopefully found a solution? Thanks, Erik -Original Message- From: jean-philippe [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 10, 2001 4:04 PM To: [EMAIL PROTECTED] Subject: Re: innodb problem Nope, it's not the SHOW TABLE STATUS that return the error but the java program that is doing the insert. For the ouput of the innodb monitor i'll see it tomorrow when i'll be back to the office. Thanks Jean-Philippe - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 10, 2001 7:34 PM Subject: Re: innodb problem Hi! Thanks for your help, but starting the innodb_lock_monitor with mysql create table innodb_lock_monitor(a int) type = innodb; doesn't output anything to the standard output. It makes the MySQL server mysqld to output to the standard output, not the client. Start your server from a command prompt. I have pasted below a sample output. But here is the new message i get with innodb .43b. It occurs when i do a SHOW TABLE STATUS during an insert with JDBC : SQLException: General error: Deadlock found when trying to get lock; Try restarting transaction Does the command SHOW TABLE STATUS return that error? Or some other SQL statement? I tried SHOW TABLE STATUS while a test program was running, and it returned the table list. Note: i'm just beginning to test innodb tables to use in place of MyISAM, everything seems to work ok except with this SQL statement that generate a deadlock if run it during an INSERT / UPDATE --Jean-Philippe Vignolo http://www.phonevalley.com A bus station is where buses stop. A train station is where trains stop. On my desk there is a work station Regards, Heikki = 011010 20:17:31 INNODB MONITOR OUTPUT = -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 3295, signal count 3295 Mutex spin waits 69407, rounds 68164, OS waits 155 RW-shared spins 6708, OS waits 2862; RW-excl spins 2800, OS waits 247 TRANSACTIONS Purge done for all trx's with n:o 0 1100739, undo n:o 0 0 Total number of lock structs in row lock hash table 0 ---TRANSACTION 0 1028205, OS thread id 10251, not started, runs or sleeps MySQL thread id 5, query id 93669 localhost heikki ---TRANSACTION 0 1038196, OS thread id 9226, not started, runs or sleeps MySQL thread id 4, query id 163875 localhost heikki FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 271 OS file reads, 1247 OS file writes, 497 OS fsyncs 0.32 reads/s, 1.45 writes/s, 0.58 fsyncs/s - INSERT BUFFER - Ibuf for space 0: size 1, free list len 5, seg size 7, 0 inserts, 0 merged recs, 0 merges --- LOG --- Log sequence number 1 1223862705 Log flushed up to 1 1223861076 Last checkpoint at 1 1223861076 0 pending log writes, 0 pending chkp writes 341 log i/o's done, 0.40 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 47466284; in additional pool allocated 233056 Free list length 49 LRU list length 1664 Flush list length 9 Buffer pool size 1792 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 238, created 2317, written 4229 0.28 reads/s, 2.70 creates/s, 4.92 writes/s Buffer pool hit rate
RE: InnoDB problem
Jamie, really strange. Now I start to suspect the file system. InnoDB should create and write a file of size 2000 MB like you specified in my.cnf. InnoDB does the initial file write in 1 MB chunks using pwrite, calling fsync in between. When you restart MySQL, InnoDB checks if the data file is of the size specified in my.cnf. It does the check using lseek from the end of file. That should not affect the file size. If the file size has changed in that lseek, it really makes the file system suspect! You could try with an ordinary disk. Regards, Heikki At 10:32 AM 5/16/01 -0700, you wrote: I had the same parameters as the startup options page for windows since I only have 256 megs of ram. However I have 3 30 Gig Hard Drives pasted together with LVM and it has ReiserFS as a partition type. [mysqld] innodb_data_home_dir = / innodb_data_file_path = web/mysql/data/ibdata1:2000M set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /web/mysql/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = /web/mysql/iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 When started it created the file ibdata1 with these properties. -rw-rw1 mysqlmysql32505856 May 16 09:16 ibdata1 So I rebooted to see if it would start on boot. It didn't. Got this err again: 010516 09:21:00 mysqld started InnoDB: Error: data file /web/mysql/data/ibdata1 is of different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 010516 9:21:02 Can't init databases 010516 09:21:02 mysqld ended funny thing was that the file size had changed. Really odd. -rw-rw1 mysqlmysql513802240 May 16 09:18 ibdata1 Thanks, Jamie -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 4:46 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re:InnoDB problem Jamie, I do not think it is the Reiser file system. Could you show what parameters you have in my.cnf and what is the size of the file ibdata1 (when looked with ls -l)? Regards, Heikki http://www.innobase.fi I complied MySQL as a max binary. However when I gave all the variables for InnoDB in the my.cnf startup file it seems that InnoDB gets initialized for the first time only. With mysqld going fine, I try to connect using the client and the server with it on really seems sluggish. I put in the password and then it just seems to hang there. I'm not put in to the client prompt at all. If I reboot the server and safe_mysqld tries to bring the database back up, the error: InnoDB: Error: data file /web/mysql/data/ibdata1 is of different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 010515 19:39:10 Can't init databases 010515 19:39:10 mysqld ended crops up and as you see, the database dies. The database files are residing on a LVM ReiserFS. Could this be the problem? Is InnoDB not compatible with LVM or ReiserFS? If it helps the kernel build is 2.4.2. Until then I'm stuck using BDB. Thanks, Jamie Krasnoo [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: InnoDB problem
Unfortunately I don't have an ordinary disk on that box. I'm thinking that InnoDB is having a major problem with LVM not ReiserFS. I tried it on an e2fs portion of the server and it still had problems. Do you know of anyone successfully using InnoDB with just LVM? Jamie -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 11:24 AM To: Jamie Krasnoo; [EMAIL PROTECTED] Subject: RE: InnoDB problem Jamie, really strange. Now I start to suspect the file system. InnoDB should create and write a file of size 2000 MB like you specified in my.cnf. InnoDB does the initial file write in 1 MB chunks using pwrite, calling fsync in between. When you restart MySQL, InnoDB checks if the data file is of the size specified in my.cnf. It does the check using lseek from the end of file. That should not affect the file size. If the file size has changed in that lseek, it really makes the file system suspect! You could try with an ordinary disk. Regards, Heikki At 10:32 AM 5/16/01 -0700, you wrote: I had the same parameters as the startup options page for windows since I only have 256 megs of ram. However I have 3 30 Gig Hard Drives pasted together with LVM and it has ReiserFS as a partition type. [mysqld] innodb_data_home_dir = / innodb_data_file_path = web/mysql/data/ibdata1:2000M set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /web/mysql/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = /web/mysql/iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 When started it created the file ibdata1 with these properties. -rw-rw1 mysqlmysql32505856 May 16 09:16 ibdata1 So I rebooted to see if it would start on boot. It didn't. Got this err again: 010516 09:21:00 mysqld started InnoDB: Error: data file /web/mysql/data/ibdata1 is of different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 010516 9:21:02 Can't init databases 010516 09:21:02 mysqld ended funny thing was that the file size had changed. Really odd. -rw-rw1 mysqlmysql513802240 May 16 09:18 ibdata1 Thanks, Jamie -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 4:46 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re:InnoDB problem Jamie, I do not think it is the Reiser file system. Could you show what parameters you have in my.cnf and what is the size of the file ibdata1 (when looked with ls -l)? Regards, Heikki http://www.innobase.fi I complied MySQL as a max binary. However when I gave all the variables for InnoDB in the my.cnf startup file it seems that InnoDB gets initialized for the first time only. With mysqld going fine, I try to connect using the client and the server with it on really seems sluggish. I put in the password and then it just seems to hang there. I'm not put in to the client prompt at all. If I reboot the server and safe_mysqld tries to bring the database back up, the error: InnoDB: Error: data file /web/mysql/data/ibdata1 is of different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 010515 19:39:10 Can't init databases 010515 19:39:10 mysqld ended crops up and as you see, the database dies. The database files are residing on a LVM ReiserFS. Could this be the problem? Is InnoDB not compatible with LVM or ReiserFS? If it helps the kernel build is 2.4.2. Until then I'm stuck using BDB. Thanks, Jamie Krasnoo [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
