Re: innodb file won't shrink
Walt, - Original Message - From: walt [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:56 PM Subject: Re: innodb file won't shrink Heikki Tuuri wrote: On September 15th, 2003 you will be able to put every InnoDB table into its own file. That should alleviate this kind of problem. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ That is great news! Will a single table be able to span several datafiles? sorry, no. The TODO list is already overloaded. Thanks! walt Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file won't shrink
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sorry HeikkiCan you give me a few steps to obtain that...? Thanks a lot. Alvaro Avello. Heikki Tuuri wrote: |Alvaro, | |- Original Message - |From: Alvaro Avello [EMAIL PROTECTED] |To: Heikki Tuuri [EMAIL PROTECTED] |Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] |Sent: Tuesday, July 15, 2003 1:13 AM |Subject: Re: innodb file won't shrink | | |-BEGIN PGP SIGNED MESSAGE- |Hash: SHA1 | |There 's any chance that in the future every InnoDB table files from a |determinated database can be placed in the same directory of the mysql ( |MyISAM ) database ? . My point is that if you want to take a binary |backup of all databases in the mysql directory and you want to restore |just one of the databases , you could just copy the directory to the |correct place and thats all you have to do to restore a single |database.. I guess I'm getting tired to wait for the dump to |re-create all the indexes and stuff like that. | | |it will not be straightforward. The undo logs used to purge old versions of |rows and roll back uncommitted transaction will not be placed to those table |files. But if you let the database to be silent and run purge to completion, |then you will get clean tables you can restore later individually to the |database. | |Thanks In advance | |Saludos / Regards , | |Alvaro Avello. | | |Regards, | |Heikki | |walt wrote: | ||Heikki Tuuri wrote: || || ||On September 15th, 2003 you will be able to put every InnoDB table |into its ||own file. That should alleviate this kind of problem. || || ||Best regards, || ||Heikki Tuuri ||Innobase Oy ||http://www.innodb.com ||Transactions, foreign keys, and a hot backup tool for MySQL ||Order MySQL technical support from https://order.mysql.com/ || || ||That is great news! Will a single table be able to span several ||datafiles? || ||Thanks! ||walt || |-BEGIN PGP SIGNATURE- |Version: GnuPG v1.2.1 (GNU/Linux) |Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org | |iD8DBQE/EysJR9NZaw5tbc0RAnMHAJ4/ZxbE5sRwAjW8cDAcXOr6cbsiowCeJU14 |y/QT2dFY16n6L/OcJ0vCHyw= |=PCGI |-END PGP SIGNATURE- | | | | | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org iD8DBQE/FGZMR9NZaw5tbc0RAn6lAJ0filp6siUs+TBk7N2CP8Il6mgHvwCfV20j YBIWR33O86CMczdhleqvZKs= =iQu1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file won't shrink
Alvaro, - Original Message - From: Alvaro Avello [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 11:38 PM Subject: Re: innodb file won't shrink -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sorry HeikkiCan you give me a few steps to obtain that...? sorry, I was talking about the September file per table storage model. Thanks a lot. Alvaro Avello. Regards, Heikki Heikki Tuuri wrote: |Alvaro, | |- Original Message - |From: Alvaro Avello [EMAIL PROTECTED] |To: Heikki Tuuri [EMAIL PROTECTED] |Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] |Sent: Tuesday, July 15, 2003 1:13 AM |Subject: Re: innodb file won't shrink | | |-BEGIN PGP SIGNED MESSAGE- |Hash: SHA1 | |There 's any chance that in the future every InnoDB table files from a |determinated database can be placed in the same directory of the mysql ( |MyISAM ) database ? . My point is that if you want to take a binary |backup of all databases in the mysql directory and you want to restore |just one of the databases , you could just copy the directory to the |correct place and thats all you have to do to restore a single |database.. I guess I'm getting tired to wait for the dump to |re-create all the indexes and stuff like that. | | |it will not be straightforward. The undo logs used to purge old versions of |rows and roll back uncommitted transaction will not be placed to those table |files. But if you let the database to be silent and run purge to completion, |then you will get clean tables you can restore later individually to the |database. | |Thanks In advance | |Saludos / Regards , | |Alvaro Avello. | | |Regards, | |Heikki | |walt wrote: | ||Heikki Tuuri wrote: || || ||On September 15th, 2003 you will be able to put every InnoDB table |into its ||own file. That should alleviate this kind of problem. || || ||Best regards, || ||Heikki Tuuri ||Innobase Oy ||http://www.innodb.com ||Transactions, foreign keys, and a hot backup tool for MySQL ||Order MySQL technical support from https://order.mysql.com/ || || ||That is great news! Will a single table be able to span several ||datafiles? || ||Thanks! ||walt || |-BEGIN PGP SIGNATURE- |Version: GnuPG v1.2.1 (GNU/Linux) |Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org | |iD8DBQE/EysJR9NZaw5tbc0RAnMHAJ4/ZxbE5sRwAjW8cDAcXOr6cbsiowCeJU14 |y/QT2dFY16n6L/OcJ0vCHyw= |=PCGI |-END PGP SIGNATURE- | | | | | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org iD8DBQE/FGZMR9NZaw5tbc0RAn6lAJ0filp6siUs+TBk7N2CP8Il6mgHvwCfV20j YBIWR33O86CMczdhleqvZKs= =iQu1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file won't shrink
Heikki Tuuri wrote: On September 15th, 2003 you will be able to put every InnoDB table into its own file. That should alleviate this kind of problem. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ That is great news! Will a single table be able to span several datafiles? Thanks! walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance issues
Nicholas, - Original Message - From: Nicholas Elliott [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, July 11, 2003 6:04 PM Subject: InnoDB Performance issues --=_NextPart_000_003B_01C3479C.77A1AB60 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hey all, ... create table basic_daily_grid( date DATE NOT NULL PRIMARY KEY, variable1 MEDIUMBLOB, variable2 MEDIUMBLOB ... variable9 MEDIUMBLOB ); ... mysql select date from basic_daily_grid_innodb; ... 317 rows in set (0.00 sec) mysql explain select date, count(*) from basic_daily_grid_innodb group = by date; +-+---+---+-+-+--= +--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+--= +--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | = Using index | +-+---+---+-+-+--= +--+-+ mysql explain select date, count(*) from basic_daily_grid_innodb group = by date; ... 317 rows in set (2 min 54.95 sec) I assume this is due to versioning or some other transactional feature. = Or, is this a bug, or am I doing something wrong? I don't quite see why = grouping items that are all unique should be that much slower than not = grouping. I need InnoDB for the unlimited table size, but I don't = (Really) need transactions, commit/rollback, or checkpoints. it is a performance bug. I an ORDER BY MySQL may use more columns than are mentioned in the SELECT query, and InnoDB retrieves the whole row. If there is a big BLOB in the row, it can take quite a while. I may fix this to 4.1.1, but first I have to ask the MySQL developer if handler::extra(KEYREAD) is aware that in a clustered index all columns are in the index record. Workaround: put BLOBs to a separate table and use a surrogate key (= auto-inc column) to join it to a smaller table where the other columns are. Any suggestions on solving this last hurdle? Its entirely likely I'll = need to group by year and average the results, or something similar - = and at 3 minutes a pop, thats a little high for my liking. Perhaps I'm = expecting too much? Thanks, Nick Elliott Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file won't shrink
Steve, - Original Message - From: Steve [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, July 10, 2003 11:24 PM Subject: Re: innodb file won't shrink I found the following thread: http://groups.google.com/groups?threadm=9dpadc%2412ag%241%40FreeBSD.csie.NCTU.edu.tw That was from 2001 and talks about the potential for INNODB tools... does anyone know if any work has been made on those? I just can't believe that there's no real way for me to make this file smaller when the space is no longer needed/used... the suggested - dump the database and then recreate it just won't since I have a 34+GB db and no more space on my drive? Anyone? Please! sorry, it is not possible to shrink the InnoDB tablespace without recreating it. You may try gzip or some other tool to compress the table dumps if they would not otherwise fit on your disk. On September 15th, 2003 you will be able to put every InnoDB table into its own file. That should alleviate this kind of problem. -Steve Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDb and fragmentation
Sorry, - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 12:39 AM Subject: Re: InnoDb and fragmentation Mike, ... So how do I defrag the InnoDb file space so I can get it back up to speed? The simplest method is ALTER TABLE ... TYPE=MYISAM; ALTER TABLE ... TYPE=INNODB; I forgot to mention that the above method removes your FOREIGN KEY constraints in the table! If you have them, best to use mysqldump, and remember to put that SET FOREIGN_KEY_CHECKS=0 at the start of the dump files if you do not import the tables in the right order. ... Mike Best regards, Heikki Tuuri Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance issues
In the last episode (Jul 11), Nicholas Elliott said: I've been experimenting with the best way to store a large (~100GB) of data for retrieval. Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the last ten years. This can work out at around 4MB a variable a day - but not all variables are always present (could be null). [...] Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of 4.2GB - because a BLOB is a variable length field. MyISAM doesn't have a hard 4gb table size... It may default to a 4gb limit if you forgot to give a hint as to the final table size when you created it, though. Try running ALTER TABLE mytable AVG_ROW_LENGTH=3600 MAX_ROWS=3600 ( 36MB average row length since you have 9 4mb blobs, and 10 years worth of records. ) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance issues
That did it -- show table status lists the upper limit as approx 1TB now =]. I'm still curious about the InnoDB issues, but now at least I can avoid it and work with the original plan! Thanks, Nick Elliott - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Nicholas Elliott [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 11, 2003 11:29 AM Subject: Re: InnoDB Performance issues In the last episode (Jul 11), Nicholas Elliott said: I've been experimenting with the best way to store a large (~100GB) of data for retrieval. Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the last ten years. This can work out at around 4MB a variable a day - but not all variables are always present (could be null). [...] Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of 4.2GB - because a BLOB is a variable length field. MyISAM doesn't have a hard 4gb table size... It may default to a 4gb limit if you forgot to give a hint as to the final table size when you created it, though. Try running ALTER TABLE mytable AVG_ROW_LENGTH=3600 MAX_ROWS=3600 ( 36MB average row length since you have 9 4mb blobs, and 10 years worth of records. ) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance issues
Hi Nicholas, How about storing the BLOBS outside of the DB and refering to them ? Best regards Nils Valentin Tokyo/Japan 2003 7 12 00:06Nicholas Elliott : Hey all, I've been experimenting with the best way to store a large (~100GB) of data for retrieval. Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the last ten years. This can work out at around 4MB a variable a day - but not all variables are always present (could be null). (If you don't care about the details, I'll summarize at the end of this email). Inserting and retrieving from a MyISAM table seemed to be approaching impossible. (Not totally surprising.) I originally had a table along the lines of: create table basic_daily_report( date DATE NOT NULL, location_id MEDIUMINT UNSIGNED NOT NULL, variable1 float, variable2 float variable9 float, primary key (date, location_id) ); (Just a summary of the actual table) With this I had a maxiumum table size of around 100GB - just barely enough to do it. I expected I would end up segmenting by year, or something similar, as ugly as that is. I tested InnoDB as an alternative to this, but we'll get to that in a second. Basically, inserting a day's worth of data would take ages, and pretty much require an analyze table for a couple hours every morning. Selecting was getting to be pretty slow, as well. Eventually, I hit on the idea of including one row per day: create table basic_daily_grid( date DATE NOT NULL PRIMARY KEY, variable1 MEDIUMBLOB, variable2 MEDIUMBLOB ... variable9 MEDIUMBLOB ); And wrote a UDF such that you pass it the variable and a location, and it'll return the exact value. This works well because every day has a constant number of locations in a grid format, so it's simply an array lookup. So, select grid_point(location_id, variable1) from basic_daily_grid where date=20030101 would return the right value for locationid. It turns out this is almost (95%) as fast as the first version in selecting, but it has the added bonus of inserts now only take ~5 seconds per day! Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of 4.2GB - because a BLOB is a variable length field. - I mention all the above in case someone has an alternative solution I'm looking over. Possible solutions I've found are a) use InnoDB instead, b) work with the source to create a new field type BLOBARRAY of a constant width instead of dynamic, c) work with the source to somehow overcome the 4.2GB limit on a dynamic table. c) Seems unlikely - if the actual developers can't do it, I probably can't b) Seems possible, I assume no one saw a need for a constant width column of 4MB, so hopefully its not too difficult a) Was my first try. Inserting takes about twice as long as myisam... sure, I can deal with that. Selecting a specific date is in the same ballpark as well, so little problem there. What I'm having severe performance issues on are querys that group, or do a count(*). For example: mysql explain select date from basic_daily_grid_innodb; +-+---+---+-+-+ --+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+ --+--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using | index | +-+---+---+-+-+ --+--+-+ mysql select date from basic_daily_grid_innodb; ... 317 rows in set (0.00 sec) mysql explain select date, count(*) from basic_daily_grid_innodb group by date; +-+---+---+-+-+ --+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+ --+--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using | index | +-+---+---+-+-+ --+--+-+ mysql explain select date, count(*) from basic_daily_grid_innodb group by date; ... 317 rows in set (2 min 54.95 sec) I assume this is due to versioning or some other transactional feature. Or, is this a bug, or am I doing something wrong? I don't quite see why grouping items that are all unique should be that much slower than not grouping. I need InnoDB for the unlimited table size, but I don't (Really) need transactions, commit/rollback, or checkpoints. Any suggestions on solving this last hurdle? Its entirely likely I'll need to group by year and average the results, or something similar - and at 3 minutes a pop, thats a little high for my liking. Perhaps I'm
Re: Innodb table full
The size is already set to 2000M, and I may be wrong, but the autoextend feature is not support in mysql version earlier that 4. Nils Valentin wrote: Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003 7 8 22:45mixo : How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table full
Hi Mixo, How about adding a second innodb file and set the first one to a fixed size ? ...If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of `ibdata1', round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of `ibdata1' explicitly in innodb_data_file_path. After that you can add another datafile: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend... (taken from the manual http://www.mysql.com/doc/en/InnoDB_start.html ) Best regards Nils Valentin Tokyo/Japan 2003 7 9 15:19mixo : The size is already set to 2000M, and I may be wrong, but the autoextend feature is not support in mysql version earlier that 4. Nils Valentin wrote: Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003 7 8 22:45mixo : How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13 in a file operation
Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13 in a file operation
Hello Egor, You give me hope but... I attempted to set up permissions but it appears that I need to start up MySQL in order to do that. That brings me right back to my original problem of not being able to connect. I am thinking of reinstalling MySQL to see if I missed something in the original install. Any more suggestions please, I am at a loss. Mark D. On Wednesday, July 9, 2003, at 09:26 AM, Egor Egorov wrote: Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB: Operating system error number 13 in a file operation
If you got the error message during creation of the innoDB files (e.g. during install) you HAVE TO DELETE all files and restart the installation. There is no way around this! However, if the file got corrupted after installation (e.g. you already used it for days) you can repair it (maybe someone changed the file permissions and/or ownership). You can change filepermissions on unix (linux) with chmod (type 'man chmod') on the command line, and change ownership with chown (man chown). You can delete files with 'rm' or 'unlink' (again, see the man pages for help) Cheers /rudy -Original Message- From: Mark Depenbrock [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:35 To: Egor Egorov Cc: [EMAIL PROTECTED] Subject: Re: InnoDB: Operating system error number 13 in a file operation Hello Egor, You give me hope but... I attempted to set up permissions but it appears that I need to start up MySQL in order to do that. That brings me right back to my original problem of not being able to connect. I am thinking of reinstalling MySQL to see if I missed something in the original install. Any more suggestions please, I am at a loss. Mark D. On Wednesday, July 9, 2003, at 09:26 AM, Egor Egorov wrote: Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13 in a file operation
Rudy, Egor, Victoria, Thank you for keeping an eye on me. I ended up doing a reinstall of MySQL and have successfully connected. I feel I have broken through a wall only to find an infinite number of walls yet to break through. Such is the adventure I have chosen. Be at peace, Mark Depenbrock On Wednesday, July 9, 2003, at 11:13 AM, Rudy Metzger wrote: If you got the error message during creation of the innoDB files (e.g. during install) you HAVE TO DELETE all files and restart the installation. There is no way around this! However, if the file got corrupted after installation (e.g. you already used it for days) you can repair it (maybe someone changed the file permissions and/or ownership). You can change filepermissions on unix (linux) with chmod (type 'man chmod') on the command line, and change ownership with chown (man chown). You can delete files with 'rm' or 'unlink' (again, see the man pages for help) Cheers /rudy -Original Message- From: Mark Depenbrock [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:35 To: Egor Egorov Cc: [EMAIL PROTECTED] Subject: Re: InnoDB: Operating system error number 13 in a file operation Hello Egor, You give me hope but... I attempted to set up permissions but it appears that I need to start up MySQL in order to do that. That brings me right back to my original problem of not being able to connect. I am thinking of reinstalling MySQL to see if I missed something in the original install. Any more suggestions please, I am at a loss. Mark D. On Wednesday, July 9, 2003, at 09:26 AM, Egor Egorov wrote: Mark Depenbrock [EMAIL PROTECTED] wrote: Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? No, you should set up permissions on the directory and file. Error 13 means Permission denied. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Hello Nick, You and I are just about on the same page here. However, I just turned the page you are now baffling over. Let me ask, Did you removed the old Macintosh HD/Library/Receipts/mysql-standard-4.0.13.pkg file before you did the new install. If I am not mistaken, this is what cinched it for me. God bless, be at peace, Mark D On Wednesday, July 9, 2003, at 02:00 PM, Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. mysql doesn't have permissions for this file. It is probably owned by root, or its directory is. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Yep, that was it, along with what Mark said. On Wednesday, Jul 9, 2003, at 15:10 US/Central, gerald_clark wrote: Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. mysql doesn't have permissions for this file. It is probably owned by root, or its directory is. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table full
At 8:19 +0200 7/9/03, mixo wrote: The size is already set to 2000M, and I may be wrong, but the autoextend feature is not support in mysql version earlier that 4. 3.23.50, actually. Nils Valentin wrote: Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003îN 7åé 8ì âójì 22:45ÅAmixo ÇÇÒÇÕèëÇ´ÇÐǵÇ: How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table full
Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003 7 8 22:45mixo : How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb transaction
Benny, - Original Message - From: Bernhard Schmidt [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 07, 2003 6:33 PM Subject: innodb transaction --=_NextPart_000_0144_01C344AD.CB5B11C0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable hi all i started working with the innodb databasel. after reading the manual i = am not sure how i shall use transctions. say i have the following case: SELECT read some row UPDATE depending on the select COMMIT to make the command sequence safe, the select has to lock the table. it = would be nice to have something like: START TRANSACTION SELECT read some row UPDATE depending on the select COMMIT any row accessed between START TRANSACTION and COMMIT automatically = locks the rows. but the manual does not exactly describe the behavior of = the commands START TRANSACTION, BEGIN etc. It only denotes that it = servers for ad-hoc transactions. i know that i can use the LOCK IN SHARE = MODE with the SELECT command, but is this the only way? SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; does the trick. Note that in InnoDB everything happens inside a transaction. If you have AUTOCOMMIT=1, then every SQL statement constitutes its own transaction (unless you enclose several statements inside BEGIN ... COMMIT). http://www.innodb.com/ibman.html#InnoDB_transaction_model A detailed description of each isolation level in InnoDB: READ UNCOMMITTED This is also called 'dirty read': non-locking SELECTs are performed so that we do not look at a possible earlier version of a record; thus they are not 'consistent' reads under this isolation level; otherwise this level works like READ COMMITTED. READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, NOT the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. But still in range type UPDATE and DELETE InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary since 'phantom rows' have to be blocked for MySQL replication and recovery to work. Consistent reads behave like in Oracle: each consistent read, even within the same transaction, sets and reads its own fresh snapshot. REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. Otherwise these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users. In consistent reads there is an important difference from the previous isolation level: in this level all consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECTs within the same transaction, these SELECTs are consistent also with respect to each other. SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE. best regards benny Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb transaction
hi many thanks for that quick and precise answer - a big smile. i am very impressed by this user group, especially regarding the speed and quality of the answers. regards benny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDb and fragmentation
Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 07, 2003 5:54 PM Subject: InnoDb and fragmentation Heikki, Do InnoDb tables suffer from internal table fragmentation. (Is there any way to measure it?) I only mention this because of a problem I have with another database system (not MySQL). Some of its tables are hopelessly fragmented (800,000 file fragments for one table alone! Did I set a new record here?g). Since InnoDb creates its own file space, it is immune from OS fragmentation which is great (provided I defrag the drive before allocating InnoDb space), but what about internal fragmentation. If I continuously add/delete small temporary Innodb tables/rows, then add more rows to a table, delete rows etc., the table rows are no longer going to be contiguous. since InnoDB first allocates 32 pages individually to an index, and after that complete 1 MB 'extents', there should practically never be inter-table fragmentation. I mean, other tables never affect how fragmented a table is. Inside a single index, the worst possible fragmentation is that every page is only 50 % full and the pages are completely scattered in those 1 MB extents. Then a table can reserve many more extents than required, and table scans are slow because pages are not contiguous. InnoDB always tries to allocate an adjacent page in a B-tree page split, but often that is not possible. So how do I defrag the InnoDb file space so I can get it back up to speed? The simplest method is ALTER TABLE ... TYPE=MYISAM; ALTER TABLE ... TYPE=INNODB; The only way I can think of is to unload all the databases and then reload then back in which can take a bit of time. Am I worried about nothing? Or should this type of maintenance be done on a regular basis? TIA My feeling is that no defragmentation is normally needed unless you tend to run out of disk space. I'm using Win2k with NTFS volumes. Mike Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB logfile question
Nils, - Original Message - From: Nils Valentin [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, July 02, 2003 12:04 PM Subject: InnoDB logfile question Hello Heikki other Mysql Fans ;-); Does anybody know which requests or data the below logfils actually keep ?? If I understood correct than they are all in binary format (not readable in a text editor. log.01 this is a BDB log I think. ib_arch_log_00 InnoDB archived log which is produced in log file creation. Not needed for anything, just a relic from the past. ib_logfile0 ib_logfile1 These are the InnoDB redo logs it uses in crash recovery. It writes circularly to these files. Unfortunately I was unable to to find sufficient info here http://www.innodb.com/ibman.html. Best regards -- --- Valentin Nils Regards, Heikki Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB logfile question
Hi Heikki, 2003 7 2 18:10Heikki Tuuri : Nils, - Original Message - From: Nils Valentin [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, July 02, 2003 12:04 PM Subject: InnoDB logfile question Hello Heikki other Mysql Fans ;-); Does anybody know which requests or data the below logfils actually keep ?? If I understood correct than they are all in binary format (not readable in a text editor. log.01 this is a BDB log I think. ib_arch_log_00 InnoDB archived log which is produced in log file creation. Not needed for anything, just a relic from the past. ib_logfile0 ib_logfile1 These are the InnoDB redo logs it uses in crash recovery. It writes circularly to these files. Do I assume correctly that it writes into this files a) all successful transactions (requests, status A and B - before ad after the request) b) nothing else ?? Best regards Nils Valentin Tokyo/Japan Unfortunately I was unable to to find sufficient info here http://www.innodb.com/ibman.html. Best regards -- --- Valentin Nils Regards, Heikki Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB logfile question
Nils, InnoDB writes to ib_logfiles all tablespace modifying operations (= mini-transactions), whether they belong to a successful or an unsuccessful transaction. In crash recovery we redo everything, then roll back based on undo logs inside the tablespace. The best reference is Gray and Reuter: Transaction Processing, published around 1992. Regards, Heikki - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 12:34 PM Subject: Re: InnoDB logfile question Hi Heikki, 2003 7 2 18:10Heikki Tuuri : Nils, - Original Message - From: Nils Valentin [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, July 02, 2003 12:04 PM Subject: InnoDB logfile question Hello Heikki other Mysql Fans ;-); Does anybody know which requests or data the below logfils actually keep ?? If I understood correct than they are all in binary format (not readable in a text editor. log.01 this is a BDB log I think. ib_arch_log_00 InnoDB archived log which is produced in log file creation. Not needed for anything, just a relic from the past. ib_logfile0 ib_logfile1 These are the InnoDB redo logs it uses in crash recovery. It writes circularly to these files. Do I assume correctly that it writes into this files a) all successful transactions (requests, status A and B - before ad after the request) b) nothing else ?? Best regards Nils Valentin Tokyo/Japan Unfortunately I was unable to to find sufficient info here http://www.innodb.com/ibman.html. Best regards -- --- Valentin Nils Regards, Heikki Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB logfile question
Uups must have forgotten to copy the list ;-) Thanks Heikki, Also I dont fully understand the resulting context yet, I appreciate the reply. Best regards Nils Valentin Tokyo/Japan 2003 7 3 00:07Heikki Tuuri : Nils, at the lower level all mini-transactions always succeed. They all have to be logged. Regards, Heikki - Alkuperinen viesti - Lhettj: Nils Valentin [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Lhetetty: Wednesday, July 02, 2003 1:39 PM Aihe: Re: InnoDB logfile question Hi Heikki, I trust your info. However, I am currently waiting for MySQL Transactions and Replication Handbook ISBN: 1861008384 http://www.amazon.com/exec/obidos/ASIN/1861008384/103-2494567-5987851 to be publised, which hopefully contains similar and newer information. Wouldn't it make sense not to log unsuccessful requests ? Isn't that useless overhead ? Best regards Nils Valentin Tokyo/Japan 2003 7 2 19:25Heikki Tuuri : Nils, InnoDB writes to ib_logfiles all tablespace modifying operations (= mini-transactions), whether they belong to a successful or an unsuccessful transaction. In crash recovery we redo everything, then roll back based on undo logs inside the tablespace. The best reference is Gray and Reuter: Transaction Processing, published around 1992. Regards, Heikki - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 12:34 PM Subject: Re: InnoDB logfile question Hi Heikki, 2003 7 2 18:10Heikki Tuuri : Nils, - Original Message - From: Nils Valentin [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, July 02, 2003 12:04 PM Subject: InnoDB logfile question Hello Heikki other Mysql Fans ;-); Does anybody know which requests or data the below logfils actually keep ?? If I understood correct than they are all in binary format (not readable in a text editor. log.01 this is a BDB log I think. ib_arch_log_00 InnoDB archived log which is produced in log file creation. Not needed for anything, just a relic from the past. ib_logfile0 ib_logfile1 These are the InnoDB redo logs it uses in crash recovery. It writes circularly to these files. Do I assume correctly that it writes into this files a) all successful transactions (requests, status A and B - before ad after the request) b) nothing else ?? Best regards Nils Valentin Tokyo/Japan Unfortunately I was unable to to find sufficient info here http://www.innodb.com/ibman.html. Best regards -- --- Valentin Nils Regards, Heikki Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB table recovery problem - not beeing able to start database server
Ivan, if you have already dumped all InnoDB tables, then there is no need to keep ibdata files and ib_logfiles. You can delete them and recreate them from scratch and reimport the tables. But in the printout which you pasted below you do not have innodb_force_recovery set to 4? When I test 4.0.14, I get a printout which tells the option is set to 4: C:\mysql-4.0.13\mysql-4.0.13\client_debugmysqld --console 030630 16:49:40 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 16 1350394126 InnoDB: Doing recovery: scanned up to log sequence number 16 1350394126 030630 16:49:41 InnoDB: Flushing modified pages from the buffer pool... 030630 16:49:41 InnoDB: Started InnoDB: !!! innodb_force_recovery is set to 4 !!! mysqld: ready for connections Note that innodb_force_recovery only helps you to dump your tables. I have intentionally blocked inserts and updates if innodb_force_recovery 0, because it is not a good idea to use a corrupt database. If the disk or the Windows OS lies to InnoDB about when it really has written a page to disk, you can easily get this kind of corruption at a power outage. The free page bitmap can be out-of-sync with other data structures in the tablespace. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html Subject: InnoDB table recovery problem - not beeing able to start database server From: Ivan Tomasic Date: Mon, 30 Jun 2003 12:10:08 +0200 Hi. Please help. During long delete operation from one InnoDB table there was PC shutdown due to power supply failure. After that I was unable to start database server so I have started it with set-variable = innodb force recovery=4 option and I have dropped all databases with InnoDB tables. Still I am unable to start database server normally. Following is the part of Err file: 030630 11:58:36 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 3895685648 InnoDB: Doing recovery: scanned up to log sequence number 0 3895685632 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1777810 row operations to undo InnoDB: Trx id counter is 0 22060800 InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 22060495, 1777810 rows to undo InnoDB: Progress in percents: 1InnoDB: Dump of the tablespace extent descriptor: len 40; hex 00950004aa aafeff; asc ...Ľ¬¬¬¬¬¬¬¬¬¬¬¬â- ...; InnoDB: Serious error! InnoDB is trying to free page 32306 InnoDB: though it is already marked as free in the tablespace! InnoDB: The tablespace free space info is corrupt. InnoDB: You may need to dump your InnoDB tables and recreate the whole InnoDB: database! InnoDB: If the InnoDB recovery crashes here, see section 6.1 InnoDB: of http://www.innodb.com/ibman.html about forcing recovery. 030630 11:58:37 InnoDB: Assertion failure in thread 580 in file D:\mysql-4.0.12 \innobase\fsp\fsp0fsp.c line 2689 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 030630 11:58:37 mysqld-max-nt: Got signal 11. Aborting! 030630 11:58:37 Aborting 030630 11:58:37 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030630 11:58:37 mysqld-max-nt: Shutdown Complete Please help B.Sc.E.E Ivan Tomasic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB file...
I.-A., - Original Message - From: I-A.Kotopoulos [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, June 25, 2003 10:05 AM Subject: InnoDB file... --=_NextPart_000_00B5_01C33B01.642AD2C0 Content-Type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: quoted-printable When should I use the autoextend option for the InnoDB file...Even after = emptying the tables the files keeps its size and for new insertions it = gets even bigger instead of using the already allocated space(which I = suppose should be available after emptyibg the tables). yes, it should free the space if you empty the table. The size of ibdata1 will stay the same, but other tables can use the freed space. Are you sure you do not have long-running transactions dangling? Use SHOW INNODB STATUS\G to print a list of transactions. The purge operation cannot remove delete-marked rows if there are old transactions which could still see them. thank you friends aik_b p.s. MySQL doesn't seem to do a good usage of the disk space and the = InnoDB file size and its density..anyway Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB foreign keys
If possible, what is the syntax? It's possible, but if you can't search, I'm not sure you can handle it. ;P http://www.innodb.com/ibman.html#InnoDB_foreign_keys Edward Dudlik Becoming Digital www.becomingdigital.com Did I help you? Want to show your thanks? www.amazon.com/o/registry/EGDXEBBWTYUU - Original Message - From: Mikael Engdahl [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 24 June, 2003 15:36 Subject: InnoDB foreign keys Hello, Is it possible to create a foreign key that referencestwo columns, if the referenced table uses more two colums for it's primary key? If possible, what is the syntax? Mikael Engdahl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB file size...
From the InnoDB manual: Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows can be physically removed only in a purge operation after they are no longer needed in transaction rollback or consistent read. Perhaps that helps. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: I-A.Kotopoulos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, 20 June, 2003 04:36 Subject: InnoDB file size... I am currently working on some experiments and have to deal with some millions of tuples. As you can imagine indexing is essentiall. However My InnoDB file has grown to over 3.6G and fragmentation is sometimes a big problem. Additionally the file keeps its size even after dropping the 'big' tables. So, any suggestions in order to optimize the space usage? thanx in advance aik_b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb error
John,
what Linux kernel version you are running? Did the first crash occur because
of the same assertion failure?
The InnoDB tablespace is probably corrupt and it asserts in purge or insert
buffer merge.
You can try starting with
innodb_force_recovery=4
in the [mysqld] section of your my.cnf. You can try to determine how
widespread the corruption is by running CHECK TABLE ... on your tables.
Then dump your tables and recreate the whole tablespace.
MySQL-4.0.14 will have the page checksum fixed. Then we will get more
information whether this kind of corruption originates in the file system.
It might also be an ordinary bug in InnoDB. It asserts in copying of records
to another page because a record does not fit though we have calculated it
should fit.
I have now added diagnostic code to 4.0.14 which will print hex dumps of the
pages if the assertion fails again.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
...
/* Copy records from the original page to the new page */
sup = page_get_supremum_rec(page);
while (sup != page_cur_get_rec(cur1)) {
ut_a(
page_cur_rec_insert(cur2, page_cur_get_rec(cur1), mtr));
page_cur_move_to_next(cur1);
page_cur_move_to_next(cur2);
}
...
- Original Message -
From: John Smith [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Thursday, June 19, 2003 12:21 AM
Subject: Innodb error
I'm getting this error in my log:
--
030618 15:08:24 mysqld started
030618 15:08:24 InnoDB: Started
030618 15:08:24 /usr/sbin/mysqld: Can't create/write
to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
/usr/sbin/mysqld: ready for connections.
Version: '4.0.12' socket: '/var/lib/mysql/mysql.sock'
port: 3306
030618 15:08:26 InnoDB: Assertion failure in thread
28680 in file page0page.c line 450
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to
[EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also
possible that this binary
or one of the libraries it was linked against is
corrupt, improperly built,
or misconfigured. This error can also be caused by
malfunctioning hardware.
We will try our best to scrape up some info that will
hopefully help diagnose
the problem, but since we have already crashed,
something is definitely wrong
and this may fail.
key_buffer_size=67108864
read_buffer_size=1044480
030618 15:08:26 mysqld ended
--
Prior to this, I was doing 'mysqldump -A ...' and
mysql crashed. After rebooting the server I can get
mysql running again, but most queries show 'connection
lost', 'mysql gone away', etc. errors - but usually
the queries work after the client automatically
reconnects. 'mysqlcheck [-r] ...' dies with
'connection lost'.
Please help!
TIA,
John
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb error
John,
actually, you should first try
innodb_force_recovery=2
and run CHECK TABLEs.
Regards,
Heikki
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 1:25 AM
Subject: Re: Innodb error
John,
what Linux kernel version you are running? Did the first crash occur
because
of the same assertion failure?
The InnoDB tablespace is probably corrupt and it asserts in purge or
insert
buffer merge.
You can try starting with
innodb_force_recovery=4
in the [mysqld] section of your my.cnf. You can try to determine how
widespread the corruption is by running CHECK TABLE ... on your tables.
Then dump your tables and recreate the whole tablespace.
MySQL-4.0.14 will have the page checksum fixed. Then we will get more
information whether this kind of corruption originates in the file system.
It might also be an ordinary bug in InnoDB. It asserts in copying of
records
to another page because a record does not fit though we have calculated it
should fit.
I have now added diagnostic code to 4.0.14 which will print hex dumps of
the
pages if the assertion fails again.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
...
/* Copy records from the original page to the new page */
sup = page_get_supremum_rec(page);
while (sup != page_cur_get_rec(cur1)) {
ut_a(
page_cur_rec_insert(cur2, page_cur_get_rec(cur1), mtr));
page_cur_move_to_next(cur1);
page_cur_move_to_next(cur2);
}
...
- Original Message -
From: John Smith [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Thursday, June 19, 2003 12:21 AM
Subject: Innodb error
I'm getting this error in my log:
--
030618 15:08:24 mysqld started
030618 15:08:24 InnoDB: Started
030618 15:08:24 /usr/sbin/mysqld: Can't create/write
to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
/usr/sbin/mysqld: ready for connections.
Version: '4.0.12' socket: '/var/lib/mysql/mysql.sock'
port: 3306
030618 15:08:26 InnoDB: Assertion failure in thread
28680 in file page0page.c line 450
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to
[EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also
possible that this binary
or one of the libraries it was linked against is
corrupt, improperly built,
or misconfigured. This error can also be caused by
malfunctioning hardware.
We will try our best to scrape up some info that will
hopefully help diagnose
the problem, but since we have already crashed,
something is definitely wrong
and this may fail.
key_buffer_size=67108864
read_buffer_size=1044480
030618 15:08:26 mysqld ended
--
Prior to this, I was doing 'mysqldump -A ...' and
mysql crashed. After rebooting the server I can get
mysql running again, but most queries show 'connection
lost', 'mysql gone away', etc. errors - but usually
the queries work after the client automatically
reconnects. 'mysqlcheck [-r] ...' dies with
'connection lost'.
Please help!
TIA,
John
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB question(s)
Hi Edward, Thank you for the reply. I really appreciate the response,but I was thinking into a different direction. I was hoping that perhaps additionally to the normal backup procedure that there is a shortcut or a trick which would quickly allow you to fix that specific issue (f.e. recover the orginal dbname). Best regards Nils Valentin Tokyo/Japan 2003 6 10 14:55Becoming Digital : http://www.innodb.com/ibman.html#Backing_up See the section on Forcing Recovery. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 10 June, 2003 01:40 Subject: InnoDB question(s) O.K MySQL List This one is on me, a bit funny, a bit strange but with a serious background. You are allowed to laugh ;-). Assuming that somebody has the clever idea to rename the folder for a InnoDB database f.e. on the command line base (mv command for Linux). Now the mysqld server startsup and of course he will not be able to access any table , data etc. he will get something like below: mysql use sam_member_db Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'discountplan' Didn't find any fields in table 'members' Didn't find any fields in table 'rank' Didn't find any fields in table 'relations' Database changed Then he would try to access one of the tables f.e. like this: mysql describe members - ; ERROR 1016: Can't open file: 'members.InnoDB'. (errno: 1) 1) Is it possible to reconstruct which name the database originally had before the folder was renamed on the command line ? 2) Is it possible to access or release (delete) the captured data related to the not anymore useable database ? Of course I could step through the log files trying to find the correct CREATE DATABASE dbname statement, but having seen the innodb log file sizes I would appreciate another solution ;-) PS: Didn't happen with serious data, just when fooling around with mysql. -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB
I will let others give their opinions on which one is best, but it is important to point out that you are not making a one-or-the-other decision. One of the advantages of using MySQL is that you can choose the right table handler on a table-by-table basis. This means that you can have sales and inventory tables be innodb and take advantage of transactions, but keep your log tables in MyISAM for extra speed and fulltext searching (just an example). Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 12:32 PM To: [EMAIL PROTECTED] Subject: InnoDB Hi, I need your experience to help me which table type it's better. I am going to define my database with more than 100 tables and some tables have more than 200,000 records. I know that InnoDB has some advantages against MyISAM such as transactions or foreign keys. But I am not sure that these services cause slowing process or not. If you want to start a database project, which table type do you prefer to use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB question(s)
http://www.innodb.com/ibman.html#Backing_up See the section on Forcing Recovery. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 10 June, 2003 01:40 Subject: InnoDB question(s) O.K MySQL List This one is on me, a bit funny, a bit strange but with a serious background. You are allowed to laugh ;-). Assuming that somebody has the clever idea to rename the folder for a InnoDB database f.e. on the command line base (mv command for Linux). Now the mysqld server startsup and of course he will not be able to access any table , data etc. he will get something like below: mysql use sam_member_db Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'discountplan' Didn't find any fields in table 'members' Didn't find any fields in table 'rank' Didn't find any fields in table 'relations' Database changed Then he would try to access one of the tables f.e. like this: mysql describe members - ; ERROR 1016: Can't open file: 'members.InnoDB'. (errno: 1) 1) Is it possible to reconstruct which name the database originally had before the folder was renamed on the command line ? 2) Is it possible to access or release (delete) the captured data related to the not anymore useable database ? Of course I could step through the log files trying to find the correct CREATE DATABASE dbname statement, but having seen the innodb log file sizes I would appreciate another solution ;-) PS: Didn't happen with serious data, just when fooling around with mysql. -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB Transactions
http://www.innodb.com/ibman.html#InnoDB_transaction_model Scroll down a bit to Section 8.5 Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Miguel Perez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, 09 June, 2003 18:44 Subject: INNODB Transactions Hi : I would like to know what happen if I start a transaction using BEGIN command I do some insert or update statements, and just before executing a ROLLBACK or COMMIT command I get disconnected and I can't execute those commands. For how long does the table remain locked, or in other words how unlock that table with another client connection?. Best regards _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup - the best way.
Alternatively, use InnoDB Hot Backup: http://www.innodb.com/hotbackup.html This will let you take a real-time backup of your InnoDB tables without taking the server down. Chris Rafal Jank wrote: Dnia Wed, 28 May 2003 12:57:59 +0200 Jarek Jarzebowski [EMAIL PROTECTED] zezna/a co nastpuje: Hi, I have MySQL 4.0.12 working on Debian Linux box. DB server uses MYISAM and InnoDB tables. MySQL docs shows what is the best way to backup InnoDB tables but I don't want to shutdown db server every backup time. I suppose it is not a good idea to just copy database files and innodb log files to the safe place because of unwanted rollbacks danger. What if I use mysqldump to backup databases data, and then I copy innodb log files to the safe place? I suppose I should flush tables first. You can mysqldump InnoDB tables too. Copying InnoDB files (data or redologs) without shuting down mysql is a bad idea. You can also use mysql replication if you have spare server and do a cold backup from it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup - the best way.
On Wed, May 28, 2003 at 09:53:02AM -0700, Chris Tucker wrote: Alternatively, use InnoDB Hot Backup: http://www.innodb.com/hotbackup.html This will let you take a real-time backup of your InnoDB tables without taking the server down. Agreed. Aside from shutting down MySQL and backing it up with your favorite backup too, the hot backup tool is the best option. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,938,408 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup - the best way.
Dnia Wed, 28 May 2003 12:57:59 +0200 Jarek Jarzebowski [EMAIL PROTECTED] zezna/a co nastpuje: Hi, I have MySQL 4.0.12 working on Debian Linux box. DB server uses MYISAM and InnoDB tables. MySQL docs shows what is the best way to backup InnoDB tables but I don't want to shutdown db server every backup time. I suppose it is not a good idea to just copy database files and innodb log files to the safe place because of unwanted rollbacks danger. What if I use mysqldump to backup databases data, and then I copy innodb log files to the safe place? I suppose I should flush tables first. You can mysqldump InnoDB tables too. Copying InnoDB files (data or redologs) without shuting down mysql is a bad idea. You can also use mysql replication if you have spare server and do a cold backup from it. -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215770 _/ _/ _/ ==* http://szukaj.wp.pl *==-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB disk file grows, never shrinks
I should add that my test continually inserted and deleted records from a set of tables. At no point did any table have more than 101 records, but the test ran 348 times, so there were 348 * 100 rows INSERTed and DELETEd. -ms -- Original Message -- From: Michael S [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 3 Apr 2003 23:45:28 -0600 My InnoDB file is set to 60MB, and is not set to autoexpand. When running stress tests against my server today, I got a message that the table was full and the test stopped. Sure enough, the InnoDB file was about 62MB. My question: I assume that the InnoDB file is the transaction log. Is there a command that I should be running to truncate the transaction log periodically so it doesn't fill up? On MS SQL Server, I can run with TRUNCATE LOG ON CHECKPOINT if running in a test scenario. TIA, -ms -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb memory problem
Kaming, please run SHOW INNODB STATUS to monitor the amount of memory allocated by InnoDB. If you have very many tables, the dictionary cache can take quite a lot of memory. But it should not grow indefinitely. If the problem is memory fragmentation caused by the malloc() in FreeBSD, you can test setting innodb_additional_mem_pool_size so big that the InnoDB memory allocation does not spill out of it. In the pool InnoDB uses a buddy algorithm which is very resistant to memory fragmentation. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL . Subject: Innodb memory problem From: Kaming Date: Mon, 31 Mar 2003 17:48:46 +0800 Hi all, I have a problem about the innodb memory usage. The following is my config file for mysql and the version I am using is 4.0.12 in FreeBSD 4.7 stable. my.cnf [mysqld] port=3306 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable = thread_cache_size=700 set-variable = table_cache=1000 set-variable = key_buffer_size=16M set-variable = max_connect_errors=99 set-variable = max_connections=850 skip-name-resolve skip-locking memlock ### INNODB CONFIG ### innodb_data_home_dir = /var/lib/mysql/innodbdata innodb_data_file_path = datafile:4096M:autoextend set-variable = innodb_buffer_pool_size=24M set-variable = innodb_additional_mem_pool_size=8M innodb_log_group_home_dir = /var/lib/mysql/innodblog innodb_log_arch_dir = /var/lib/mysql/innodblog set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 # [mysql.server] user=mysql basedir=/usr/local/site/mysql [safe_mysqld] err-log=/var/lib/mysql/mysqld.log pid-file=/var/lib/mysql/mysqld.pid When I start the mysqld, the memory usage is about 85M. Then the mysqld will use more and more memory and doesn't release. It will use all the memory and the performance will be dropped when it starts to use swap space... Do anyone know how to calculate the memory mysqld use when it starts? And also any method can be used to limit the memory usage for innodb? I don't have memory problem when using MyISAM before... The memory usage of mysqld reachs certain level and will not continue to grow. Thanks a lot. Kaming. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB
Those are binary log files used in replication. If you are not running replication, remove the line bin-log from the config file. Tiele Declercq wrote: Hey guys, Ii could not find an answer on the innodb website, i'm using InnoDB now for some months and i'm very happy since the constant corruptions i had in MyISAM are gone now. Although i have been warned that InnoDB would take up more diskspace i did not suspect gigabytes. When first installed i've set up my DB file at 300MB, hold in mind that the database is quiet large... some tables hold over 2.000.000 records (for statistics). I soon realized this would have to be a bit bigger so i've set this to 2G's. Did this because i saw some binary files in my MySQL dir... myname-bin.001, 002, 003,. up to 30 for now. Sometimes 2 each day sometimes none for a few days... Biggest so far is 1G... there are 4 of those. Total size of those binary files is 7.5G's and i have NO idea what those files do. I think i've read the complete manual but could find anything about these files. It can't be log files coz that's ib_logfile0 1, each set at 150MB). My total disk space is 40Gb... so it's enough to hold it for now but i WILL run out of space in a couple of months at this rate. Can i safely remove these files ? I'm pulling backups of my database each day so if something goes wrong i can easily restore it. So the currect db file is 2GB big and i have over 1GB free... so it's NOT the ibdata file that takes up diskspace but the hostname-bin.xxx files are bugging me. Best Regards, Tiele Declercq --- Projectleider Start.be Moderator http://pcshop.start.be --- http://start.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB
Hello, This actually brings up a question I've been meaning to ask. from the InnoDB documentations To be able to recover your InnoDB database to the present from the binary backup described above, you have to run your MySQL database with the general logging and log archiving of MySQL switched on. Here by the general logging we mean the logging mechanism of the MySQL server which is independent of InnoDB logs. http://www.vanderouderaa.nl/MySQL/doc/en/Backing_up.html Are they referring to the bin logs here. and does that mean bin logs are absolutely needed for crash recovery of innoDBs. many thanks Murad gerald_clark wrote: Those are binary log files used in replication. If you are not running replication, remove the line bin-log from the config file. Tiele Declercq wrote: Hey guys, Ii could not find an answer on the innodb website, i'm using InnoDB now for some months and i'm very happy since the constant corruptions i had in MyISAM are gone now. Although i have been warned that InnoDB would take up more diskspace i did not suspect gigabytes. When first installed i've set up my DB file at 300MB, hold in mind that the database is quiet large... some tables hold over 2.000.000 records (for statistics). I soon realized this would have to be a bit bigger so i've set this to 2G's. Did this because i saw some binary files in my MySQL dir... myname-bin.001, 002, 003,. up to 30 for now. Sometimes 2 each day sometimes none for a few days... Biggest so far is 1G... there are 4 of those. Total size of those binary files is 7.5G's and i have NO idea what those files do. I think i've read the complete manual but could find anything about these files. It can't be log files coz that's ib_logfile0 1, each set at 150MB). My total disk space is 40Gb... so it's enough to hold it for now but i WILL run out of space in a couple of months at this rate. Can i safely remove these files ? I'm pulling backups of my database each day so if something goes wrong i can easily restore it. So the currect db file is 2GB big and i have over 1GB free... so it's NOT the ibdata file that takes up diskspace but the hostname-bin.xxx files are bugging me. Best Regards, Tiele Declercq --- Projectleider Start.be Moderator http://pcshop.start.be --- http://start.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB
Hey, thanks fopr the tip. Indeed the line was in there. Can i now erase those bin files that have been written before ? - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Tiele Declercq [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, March 31, 2003 6:39 PM Subject: Re: InnoDB Those are binary log files used in replication. If you are not running replication, remove the line bin-log from the config file. Tiele Declercq wrote: Hey guys, Ii could not find an answer on the innodb website, i'm using InnoDB now for some months and i'm very happy since the constant corruptions i had in MyISAM are gone now. Although i have been warned that InnoDB would take up more diskspace i did not suspect gigabytes. When first installed i've set up my DB file at 300MB, hold in mind that the database is quiet large... some tables hold over 2.000.000 records (for statistics). I soon realized this would have to be a bit bigger so i've set this to 2G's. Did this because i saw some binary files in my MySQL dir... myname-bin.001, 002, 003,. up to 30 for now. Sometimes 2 each day sometimes none for a few days... Biggest so far is 1G... there are 4 of those. Total size of those binary files is 7.5G's and i have NO idea what those files do. I think i've read the complete manual but could find anything about these files. It can't be log files coz that's ib_logfile0 1, each set at 150MB). My total disk space is 40Gb... so it's enough to hold it for now but i WILL run out of space in a couple of months at this rate. Can i safely remove these files ? I'm pulling backups of my database each day so if something goes wrong i can easily restore it. So the currect db file is 2GB big and i have over 1GB free... so it's NOT the ibdata file that takes up diskspace but the hostname-bin.xxx files are bugging me. Best Regards, Tiele Declercq --- Projectleider Start.be Moderator http://pcshop.start.be --- http://start.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB
I delete the old binlogs after doing my nightly mysqlhotcopy. Tiele Declercq wrote: Hey, thanks fopr the tip. Indeed the line was in there. Can i now erase those bin files that have been written before ? - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Tiele Declercq [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, March 31, 2003 6:39 PM Subject: Re: InnoDB Those are binary log files used in replication. If you are not running replication, remove the line bin-log from the config file. Tiele Declercq wrote: Hey guys, Ii could not find an answer on the innodb website, i'm using InnoDB now for some months and i'm very happy since the constant corruptions i had in MyISAM are gone now. Although i have been warned that InnoDB would take up more diskspace i did not suspect gigabytes. When first installed i've set up my DB file at 300MB, hold in mind that the database is quiet large... some tables hold over 2.000.000 records (for statistics). I soon realized this would have to be a bit bigger so i've set this to 2G's. Did this because i saw some binary files in my MySQL dir... myname-bin.001, 002, 003,. up to 30 for now. Sometimes 2 each day sometimes none for a few days... Biggest so far is 1G... there are 4 of those. Total size of those binary files is 7.5G's and i have NO idea what those files do. I think i've read the complete manual but could find anything about these files. It can't be log files coz that's ib_logfile0 1, each set at 150MB). My total disk space is 40Gb... so it's enough to hold it for now but i WILL run out of space in a couple of months at this rate. Can i safely remove these files ? I'm pulling backups of my database each day so if something goes wrong i can easily restore it. So the currect db file is 2GB big and i have over 1GB free... so it's NOT the ibdata file that takes up diskspace but the hostname-bin.xxx files are bugging me. Best Regards, Tiele Declercq --- Projectleider Start.be Moderator http://pcshop.start.be --- http://start.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb transactions and drop table
Christian, - Original Message - From: Christian Jaeger [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, March 27, 2003 1:42 PM Subject: Innodb transactions and drop table Hello It looks like 'drop table' implicitely does a 'commit', at least when issued by the mysql commandline utility with mysql 3.23.51. This happens even if it was a temporary heap table as typically used to emulate subselects. I think this should be documented. (Or better yet, not do a commit, at least for temporary tables?) http://www.innodb.com/ibman.html#InnoDB_transaction_model 8.5 When does MySQL implicitly commit or rollback a transaction? MySQL has the autocommit mode switched on in a session if you do not do SET AUTOCOMMIT=0. In the autocommit mode MySQL does a commit after each SQL statement, if that statement did not return an error. If an error is returned by an SQL statement, then the commit/rollback behavior depends on the error. See section 13 for details. The following SQL statements cause an implicit commit of the current transaction in MySQL: CREATE TABLE (if MySQL binlogging is used), ALTER TABLE, BEGIN, CREATE INDEX, DROP INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. If you you have the autocommit mode off and end a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction. Hmm... an implicit commit in DROP TABLE is necessary as MySQL writes the DROP TABLE to the binlog immediately. In that case it would break replication if possible INSERTs to that table were written later to the binlog than the DROP TABLE. Actually, there is a slight bug in replication now: inserts by OTHER users may still be written to the binlog AFTER the table is dropped. InnoDB should roll back any transaction by other users who have modified the dropped table, but have not committed yet! We must write inserts to a temporary table to the binlog because inserts to other tables may depend on them. We cannot just ignore temporary tables in binlogging. As a workaround I recommed dropping your temporary tables only AFTER you have performed the transaction. Christian. Best regards, Heikki Tuuri Innobase Oy --- MySQL Users Conference, San Jose, California, April 10-12 Register at http://www.mysql.com/events/uc2003/attendee.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb transactions and drop table
Christian, It looks like 'drop table' implicitely does a 'commit', at least when issued by the mysql commandline utility with mysql 3.23.51. This happens even if it was a temporary heap table as typically used to emulate subselects. I think this should be documented. (Or better yet, not do a commit, at least for temporary tables?) It _is_ documented, but it's hard to find. The MySQL reference manual is not up to date, regarding InnoDB, so you should have a look at the InnoDB reference manual: http://www.innodb.com/ibman.html#InnoDB_transaction_model Scroll down to 8.5 (When does MySQL implicitly commit or rollback a transaction?). And here are those crucial sentences: The following SQL statements cause an implicit commit of the current transaction in MySQL: CREATE TABLE (if MySQL binlogging is used), ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb transactions and drop table
At 13:22 +0100 3/27/03, Stefan Hinz wrote: Christian, It looks like 'drop table' implicitely does a 'commit', at least when issued by the mysql commandline utility with mysql 3.23.51. This happens even if it was a temporary heap table as typically used to emulate subselects. I think this should be documented. (Or better yet, not do a commit, at least for temporary tables?) It _is_ documented, but it's hard to find. The MySQL reference manual is not up to date, regarding InnoDB, so you should have a look at the InnoDB reference manual: http://www.innodb.com/ibman.html#InnoDB_transaction_model Scroll down to 8.5 (When does MySQL implicitly commit or rollback a transaction?). And here are those crucial sentences: The following SQL statements cause an implicit commit of the current transaction in MySQL: CREATE TABLE (if MySQL binlogging is used), ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. I'll second Stefan's remarks, and go further: If you're using InnoDB, the primary documentation for it is the InnoDB reference manual. Go to http://www.innodb.com/ibman.html and READ THE WHOLE THING. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- Paul DuBois http://www.kitebird.com/ sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Foreign Keys
# [EMAIL PROTECTED] / 2003-03-24 23:49:59 +0100:
this is what Mysql Reference Manual tells me about creating foreign
keys.
I understood how to use them, except for the restrict here ---\
|
[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...) |
REFERENCES table_name (index_col_name, ...) |
[ON DELETE {CASCADE | SET NULL | NO ACTION |
| RESTRICT}] --X
[ON UPDATE {CASCADE | SET NULL | NO ACTION |
| RESTRICT}] --/
WHat are they?
what's the use of them ?
They define business rules, or, the properties of the foreign key:
* RESTRICT permits deletion of parent entity instance only when
there are no matching child entity instances.
* CASCADE always permits deletion of parent entity occurrence, and
deletes any matching child entity instances (cascade the
deletion to children).
* SET NULL: deletion of parent entity occurrence is always permitted,
and children (if any) have their foreign keys set to NULL.
* NO ACTION does what it says: it doesn't enforce any referential
integrity, parent may be deleted whether it has children or not,
and the deletion is not reflected in the children's foreign key
values.
The above is a reworded description taken from Handbook of
Relational Database Design (Addison Wesley), I hope I didn't break
any copyright. :)
--
begin 666 nonexistent.vbs
FreeBSD 4.8-RC
11:30AM up 1 day, 2:50, 10 users, load averages: 0.00, 0.01, 0.00
end
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: InnoDB Foreign Key
On Wednesday 19 March 2003 15:19, Thorsten Schmidt wrote: how can I remove a foreign key in InnoDB? ALTER TABLE DROP (FOREIGN) KEY `key` isn't working (and also not specified in documentation)... : ( Currently you should recreate table to remove FOREIGN KEY CONSTRAINTS. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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 Foreign Key
Dear all, how can I remove a foreign key in InnoDB? ALTER TABLE DROP (FOREIGN) KEY `key` isn't working (and also not specified in documentation)... : ( It's not implemented yet. You can copy your data to another table without the foreign key constraint, drop original one and rename. sql, query -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - 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
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 Assertion Error
Philip, - Forwarded message from Philip Molter [EMAIL PROTECTED] - Date: Mon, 10 Mar 2003 08:30:22 -0600 From: Philip Molter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: InnoDB Assertion Error THE SITUATION: We're running MySQL 3.23.51. We have a table which has a primary-keyed field 'sid'. We have four tables what have foreign key references on that field. We deleted all rows from those four tables that had values in that foreign key index and then tried to delete the row in the main table. The main table delete failed with a parent row reference error. please tell what SHOW CREATE TABLE says for these tables. What SELECT statements prove that there are no child rows? Have you run CHECK TABLE on the tables? Note also that if you have a self-referencing row, you have to use SET FOREIGN_KEY_CHECKS=0; do the delete SET FOREIGN_KEY_CHECKS=1; to delete the row. InnoDB is not smart enough to notice that the DELETE will remove also the 'child' row. Fine, MySQL/InnoDB have gone through a few revisions and perhaps this bug is fixed. Certainly, running this old version isn't going to help us. THE PROBLEM: After downloading and compiling MySQL 3.23.55 (we use the compile flags from MySQL's web site, but we compiled our own), we switched our config over to the new compilation (same data, same config, etc. just a new binary) and started up. On a table scan (SHOW TABLE STATUS, `mysql` without -A, etc.), MySQL crashes with an assertion: The below assertion probably means that there is not a suitable index in a referenced table. I do not see how that could happen once you have succeeded to create the foreign key constraints. I tested the below with .51 and .56, but got no error. Please send me the whole schema which takes part in this. All referencing or referenced tables. mysqldump -d databasename dumps table defs. Do you remember what kind of ALTER TABLE did you use? Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL sql query 030307 21:08:02 mysqld restarted 030307 21:08:03 InnoDB: Started /usr/local/mysql-3.23.55/libexec/mysqld: ready for connections 030307 21:08:21 InnoDB: Assertion failure in thread 15 in file dict0load.c line 677 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=33550336 record_buffer=131072 sort_buffer=16777208 max_used_connections=3 max_connections=500 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4094456 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 030307 21:08:21 mysqld restarted In the MySQL client it looks like this: mysql use tx; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'task' Didn't find any fields in table 'taxbase' Didn't find any fields in table 'terms' Didn't find any fields in table 'ticket' Didn't find any fields in table 'ticket_master' Didn't find any fields in table 'ticket_perm_bits' Didn't find any fields in table 'traffic_service_history' Didn't find any fields in table 'uid' Database changed If it's reading the tables in order, it's having a problem with one of the following tables: CREATE TABLE sync_times ( system varchar(48) NOT NULL default '', host varchar(255) NOT NULL default '', tstamp datetime default NULL, PRIMARY KEY (system,host) ) TYPE=InnoDB; CREATE TABLE task ( task int(11) NOT NULL auto_increment, flags varchar(255) default NULL, depends int(11) default NULL, type varchar(40) default NULL, name varchar(100) default NULL, description text, department varchar(20) default NULL, owner varchar(20) default NULL, customer int(11) default NULL, contact int(11) default NULL, origin varchar(255) default NULL, status varchar(20) default NULL, assigned varchar(20) default NULL, entry_time datetime default NULL, due_time datetime default NULL, finish_time datetime default NULL, tstamp timestamp(14) NOT NULL, PRIMARY KEY (task), KEY _task_ (task), KEY _customer_ (customer), KEY _contact_ (contact), KEY _depends_ (depends), KEY _name_ (name), FOREIGN KEY (`depends`) REFERENCES `tx.task` (`task`), FOREIGN KEY (`customer`) REFERENCES `tx.customer` (`customer`), FOREIGN KEY
Re: Innodb
MySQL must be compiled with support for InnoDB, if yours wasn't, you wouldn't be able to use InnoDB tables. You can download a MySQL binary here: http://www.mysql.com/downloads/mysql-3.23.html According to the InnoDB quick start page (http://www.innodb.com/howtouse.html), you'll want MySQL-max: -- How to download a binary version of MySQL/InnoDB? For Unix, you should download one of the MySQL -Max binaries at this page. For Windows from this page. Use mysqld-max.exe on Win 95, 98, ME, and mysqld-max-nt.exe on Win NT and 2000. If you already have a recent version MySQL-3.23.4x installed in your computer, you can install the corresponding -Max version with the same version number 3.23.4x just by replacing the MySQL server executable mysqld by the corresponding executable from the -Max version. MySQL and MySQL -Max differ only in the server executable --- Hope this helps. Brian McCain Digital March Internet Marketing and Consulting - Original Message - From: Paul Taylor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 10, 2003 2:28 PM Subject: Innodb I have MySQL 3.23, running on Windows XP. I don't seem to be able to make my tables Innodb type. Is there something I should install, or some script I should configure. Looking on the MySQL site, I noticed there was a script I should run. I'm so inexperience with MySQL, I don't know what it meant by this. My ISP has MySQL 3.23, yet when I connect to their MySQL server, I am able to make Innodb tables, but not on my desktop. Paul Taylor [EMAIL PROTECTED] www.technocurve.co.uk The Complete Business Internet Solution... - 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
At 22:28 + 3/10/03, Paul Taylor wrote: I have MySQL 3.23, running on Windows XP. I don't seem to be able to make my tables Innodb type. Is there something I should install, or some script I should configure. Looking on the MySQL site, I noticed there was a script I should run. I'm so inexperience with MySQL, I don't know what it meant by this. My ISP has MySQL 3.23, yet when I connect to their MySQL server, I am able to make Innodb tables, but not on my desktop. Issue this query and see what the value of the have_innodb variable is: SHOW VARIABLES LIKE 'have%'; If it's NO, then your server doesn't have InnoDB and you need to use one that does support it. For a 3.23 distribution, you probably want to use a mysqld-max or mysqld-max-nt. Otherwise, you can update to MySQL 4, which includes InnoDB support by default. If the value is DISABLED, then your server supports InnoDB but was either started with the --skip-innodb option or (more likely) you don't have the necessary configuration directives in an option file that tell the server where to write the InnoDB tablespace. If your MySQL installation is at C:\mysql, try adding something like this to your C:\my.cnf file: [mysqld] innodb_data_file_path = ibdata1:10M 10M = 10 megabytes; vary as desired. The server then will create the ibdata1 tablespace file in your server's data directory. Paul Taylor [EMAIL PROTECTED] www.technocurve.co.uk The Complete Business Internet Solution... - 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 crash report; fatal failure on restarts too!
On Thu, Mar 06, 2003 at 04:24:10PM -0800, Ask Bjoern Hansen wrote: I am running RedHat 7.3, mostly using InnoDB's. I am using the MySQL-Max rpms (4.0.11) from mysql.com. Earlier today it crashed, and while trying to start up again it crashed again. Now I can't start the server with the InnoDB tables. I don't see any tools to fix it. What to do? I would like to get the server started or at least be able to access my data somehow! Have you tried copying the data and then doing this? http://www.innodb.com/ibman.html#Forcing_recovery Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 31 days, processed 973,605,145 queries (357/sec. avg) - 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 crash report; fatal failure on restarts too!
Ask, I think some other user encountered the error number 4 EINTR when his harddisk was broken. I do not know why in the later runs it did not print an error message. Possibly the error number was EEXIST, for which InnoDB omits the error printout and which is nonsensical if you are reading an open file. I modified now os0file.c so that it should print the OS error number before asserting. If the ib_logfiles are broken, then you have to use in my.cnf set-variable=innodb_force_recovery=6 and you may also need to delete the old ib_logfiles if they are really badly broken. Then dump your tables and recreate the whole tablespace. Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - Original Message - From: Ask Bjoern Hansen [EMAIL PROTECTED] To: Zak Greant [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Sent: Friday, March 07, 2003 3:12 AM Subject: Re: InnoDB crash report; fatal failure on restarts too! On Thu, 6 Mar 2003, Zak Greant wrote: A quick update. The harddrive is crashing, so it's entirely likely that the corruption came from failing hardware. I restored a backup and I'm just using that now. In a few days I will try copying the innodb files from the bad drive and see if InnoDB can get up with innodb_force_recovery enabled there. Thanks! - ask Hi Heikki, This looks like a nasty problem to me. Thought that you would want to see it quickly! Cheers! --zak On Thu, Mar 06, 2003 at 04:24:10PM -0800, Ask Bjoern Hansen wrote: I am running RedHat 7.3, mostly using InnoDB's. I am using the MySQL-Max rpms (4.0.11) from mysql.com. Earlier today it crashed, and while trying to start up again it crashed again. Now I can't start the server with the InnoDB tables. I don't see any tools to fix it. What to do? I would like to get the server started or at least be able to access my data somehow! Logs below. First it crashed: /usr/sbin/mysqld-max: ready for connections. Version: '4.0.11-gamma-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 030306 9:30:10 InnoDB: Error: Write to file ./ib_logfile0 failed at offset 0 17611264. InnoDB: 4608 bytes should have been written, only 1536 were written. InnoDB: Operating system error number 4. InnoDB: Look from section 13.2 at http://www.innodb.com/ibman.html InnoDB: what the error number means or use the perror program of MySQL. InnoDB: Check that your OS and file system support files of this size. InnoDB: Check also that the disk is not full or a disk quota exceeded. 030306 9:30:10 InnoDB: Assertion failure in thread 7176 in file fil0fil.c line 1211 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=131072 Number of processes running now: 1 Then restarting: 030306 09:30:14 mysqld restarted 030306 9:30:24 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 10 1182820980 030306 9:31:10 InnoDB: Assertion failure in thread 1024 in file os0file.c line 1067 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=131072 sort_buffer_size=65537 max_used_connections=0 max_connections=200 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 103936 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8464ba8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe308, stack_bottom
Re: InnoDB crash report; fatal failure on restarts too!
On Thu, 6 Mar 2003, Jeremy Zawodny wrote: I am running RedHat 7.3, mostly using InnoDB's. I am using the MySQL-Max rpms (4.0.11) from mysql.com. Earlier today it crashed, and while trying to start up again it crashed again. Now I can't start the server with the InnoDB tables. I don't see any tools to fix it. What to do? I would like to get the server started or at least be able to access my data somehow! Have you tried copying the data and then doing this? http://www.innodb.com/ibman.html#Forcing_recovery No, I will try that right away. Thank you. - ask -- ask bjoern hansen, http://www.askbjoernhansen.com/ !try; do(); - 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
query re: Innodb Table Locks
to whom it may concern, I need some help regarding transactions using innodb tables. I am running MySQL 3.23.51-Max as database server and developing a web front-end using ASP. Many of the tables used are of innodb format since it is imperative for me to use transactions in most of my scripts. In 95% of the cases the scripts work fine but at times this error pops up 'Lock Wait Timeout Try Restarting Transaction' This leaves certain tables locked for a quite a number of hours and the only possibilty to recover the table is to restart mysql. Now my software is still at the testing stage and so i'm the only user. But when going live, 15 people will be using it concurrently and restarting my sql each time to avoid the table lock will not be possible. How can the deadlock be avoided? I have set innodb_lock_wait as 60 in my.cnf file, is this enough? as for the transaction syntax i am using the following Begin; on error resume next sql statements If err then rollback; else commit; end if on error goto 0 am i doing something wrong? will set autocommit=0; be useful? or is there any way to recover from the table lock? i would really appreciate a reply Best Regards Erik DeBattista Systems Developer Webcraft Ltd. --CONTACT DETAILS--- www.webcraft.com.mt Email: [EMAIL PROTECTED] Tel: +356 21421540 Fax: +356 21419300 - 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 deadlock leads to server crash
Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de
Re: innodb deadlock leads to server crash
Hi! Ok, now I think I understand the problem. Since you use the -q option, the client mysql retrieves rows in the result set in chunks of some size. As InnoDB keeps the adaptive hash index latch S-locked till the end of the the big SELECT ... ORDER BY ... query, it will probably be S-latched when you start retrieving the rows. And then you cannot perform much further operations in the database because of the S-latch! As you pointed out, a workaround would be to remove the -q option, since then mysql would retrieve all rows in one chunk and release the S-latch. Hmm... the underlying problem is that InnoDB does not know when mysqld moves control to the client, and does not know to release the S-latch then. I have to check if I can see when mysql_use_result() is used to retrieve the result in smaller chunks. I could then release the S-latch always when the program control leaves InnoDB. Thank you, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:24 AM Subject: Re: innodb deadlock leads to server crash Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326
Re: innodb deadlock leads to server crash
Hi! I have now fixed the hang to MySQL-4.0.12. I let mysqld to release the adaptive hash S-latch when it passes the control to the client as it sends result rows of a SELECT to the client. Can you test the latest 4.0.12 bk tree? Best regards, Heikki sql query ... Subject: Re: innodb deadlock leads to server crash From: Heikki Tuuri Date: Mon, 3 Mar 2003 10:42:48 +0200 Hi! Ok, now I think I understand the problem. Since you use the -q option, the client mysql retrieves rows in the result set in chunks of some size. As InnoDB keeps the adaptive hash index latch S-locked till the end of the the big SELECT ... ORDER BY ... query, it will probably be S-latched when you start retrieving the rows. And then you cannot perform much further operations in the database because of the S-latch! As you pointed out, a workaround would be to remove the -q option, since then mysql would retrieve all rows in one chunk and release the S-latch. Hmm... the underlying problem is that InnoDB does not know when mysqld moves control to the client, and does not know to release the S-latch then. I have to check if I can see when mysql_use_result() is used to retrieve the result in smaller chunks. I could then release the S-latch always when the program control leaves InnoDB. Thank you, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:24 AM Subject: Re: innodb deadlock leads to server crash Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash
Re: innodb deadlock leads to server crash
Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.0) Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtt i' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so -rwxr-xr-x 1 root root 2194520 Feb 6 12:32
Re: InnoDB Crash [more info]
Michael T. Babcock wrote: I've got a nice MySQL crash that happened during the night last night and I can't seem to get it to come back online for me without --skip-innodb. Follow-up with mysql's bug report: MySQL support: none Synopsis:InnoDB Crashing on Startup Severity:critical Priority:medium Category:mysql Class:sw-bug Release:mysql-3.23.55 (yes) Environment: System: Linux web.fibrespeed.net 2.2.14-10.0 #21 Fri Apr 21 00:22:11 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O2 -march=pentium' CXX='gcc' CXXFLAGS='-O2 -march=pentium' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Apr 10 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101836 Jan 15 2001 /lib/libc-2.1.3.so -rw-r--r--1 root root 20273324 Jan 15 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 15 2001 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Jul 20 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--sysconfdir=/etc' '--prefix=/usr' '--localstatedir=/var/mysql' '--enable-assembler' '--with-berkeley-db' '--with-innodb' '--with-comment' '--enable-thread-safe-client' 'CC=gcc' 'CFLAGS=-O2 -march=pentium' 'CXXFLAGS=-O2 -march=pentium' 'CXX=gcc' 'LDFLAGS=-static' -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 Crash [more info]
Michael, your database is probably corrupt. The normal procedure in this case is to use the my.cnf option set-variable=innodb_force_recovery=4 and dump your tables and recreate the whole tablespace. But, please do not do that yet. I will send you a new version of /mysql/innobase/ibuf/ibuf0ibuf.c which will print a lot of diagnostic information. I want to see what it prints. A general note: you are using a very old Linux kernel 2.2.14. Some combinations of Linux + hardware seem to cause sporadic table corruption. One user was able to resolve the problem by upgrading to a stock kernel 2.4.20. Regards, Heikki Innobase Oy sql query - Original Message - From: Michael T. Babcock [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, February 21, 2003 9:23 PM Subject: Re: InnoDB Crash [more info] Michael T. Babcock wrote: I've got a nice MySQL crash that happened during the night last night and I can't seem to get it to come back online for me without --skip-innodb. Follow-up with mysql's bug report: MySQL support: none Synopsis:InnoDB Crashing on Startup Severity:critical Priority:medium Category:mysql Class:sw-bug Release:mysql-3.23.55 (yes) Environment: System: Linux web.fibrespeed.net 2.2.14-10.0 #21 Fri Apr 21 00:22:11 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O2 -march=pentium' CXX='gcc' CXXFLAGS='-O2 -march=pentium' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Apr 10 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101836 Jan 15 2001 /lib/libc-2.1.3.so -rw-r--r--1 root root 20273324 Jan 15 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 15 2001 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Jul 20 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--sysconfdir=/etc' '--prefix=/usr' '--localstatedir=/var/mysql' '--enable-assembler' '--with-berkeley-db' '--with-innodb' '--with-comment' '--enable-thread-safe-client' 'CC=gcc' 'CFLAGS=-O2 -march=pentium' 'CXXFLAGS=-O2 -march=pentium' 'CXX=gcc' 'LDFLAGS=-static' -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 Crash [more info]
Michael, how do I send you email? A 'Spamcop' seems to block email to you. Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Michael T. Babcock [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 21, 2003 10:33 PM Subject: Re: InnoDB Crash [more info] Michael, your database is probably corrupt. The normal procedure in this case is to use the my.cnf option set-variable=innodb_force_recovery=4 and dump your tables and recreate the whole tablespace. But, please do not do that yet. I will send you a new version of /mysql/innobase/ibuf/ibuf0ibuf.c which will print a lot of diagnostic information. I want to see what it prints. A general note: you are using a very old Linux kernel 2.2.14. Some combinations of Linux + hardware seem to cause sporadic table corruption. One user was able to resolve the problem by upgrading to a stock kernel 2.4.20. Regards, Heikki Innobase Oy sql query - Original Message - From: Michael T. Babcock [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, February 21, 2003 9:23 PM Subject: Re: InnoDB Crash [more info] Michael T. Babcock wrote: I've got a nice MySQL crash that happened during the night last night and I can't seem to get it to come back online for me without --skip-innodb. Follow-up with mysql's bug report: MySQL support: none Synopsis:InnoDB Crashing on Startup Severity:critical Priority:medium Category:mysql Class:sw-bug Release:mysql-3.23.55 (yes) Environment: System: Linux web.fibrespeed.net 2.2.14-10.0 #21 Fri Apr 21 00:22:11 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O2 -march=pentium' CXX='gcc' CXXFLAGS='-O2 -march=pentium' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Apr 10 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101836 Jan 15 2001 /lib/libc-2.1.3.so -rw-r--r--1 root root 20273324 Jan 15 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 15 2001 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Jul 20 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--sysconfdir=/etc' '--prefix=/usr' '--localstatedir=/var/mysql' '--enable-assembler' '--with-berkeley-db' '--with-innodb' '--with-comment' '--enable-thread-safe-client' 'CC=gcc' 'CFLAGS=-O2 -march=pentium' 'CXXFLAGS=-O2 -march=pentium' 'CXX=gcc' 'LDFLAGS=-static' -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 Crash [more info]
Michael,
since the 'Spamcop':
Recipient: [EMAIL PROTECTED]
Reason:Blocked - see http://spamcop.net/bl.shtml?194.251.242.203
has blocked email to you, I post the patch here. This patch makes InnoDB to
print a lot of diagnostic info if it notices the failure you have. It also
tries to recover from the failure by discarding possible insert buffer
records for that page.
Best regards,
Heikki
Innobase Oy
sql query
--- 1.11/innobase/ibuf/ibuf0ibuf.c Mon Jul 8 19:27:47 2002
+++ 1.12/innobase/ibuf/ibuf0ibuf.c Fri Feb 21 23:11:10 2003
-2483,7 +2483,9
ulint old_bits;
ulint new_bits;
dulint max_trx_id;
+ ibool corruption_noticed = FALSE;
mtr_t mtr;
+ char err_buf[500];
if (srv_force_recovery = SRV_FORCE_NO_IBUF_MERGE) {
-2535,7 +2537,38
block = buf_block_align(page);
rw_lock_x_lock_move_ownership((block-lock));
- ut_a(fil_page_get_type(page) == FIL_PAGE_INDEX);
+ if (fil_page_get_type(page) != FIL_PAGE_INDEX) {
+
+ corruption_noticed = TRUE;
+
+ ut_print_timestamp(stderr);
+
+ mtr_start(mtr);
+
+ fprintf(stderr,
+ InnoDB: Dump of the ibuf bitmap page:\n);
+
+ bitmap_page = ibuf_bitmap_get_map_page(space, page_no,
+ mtr);
+ buf_page_print(bitmap_page);
+
+ mtr_commit(mtr);
+
+ fprintf(stderr, \nInnoDB: Dump of the page:\n);
+
+ buf_page_print(page);
+
+ fprintf(stderr,
+InnoDB: Error: corruption in the tablespace. Bitmap shows insert\n
+InnoDB: buffer records to page n:o %lu though the page\n
+InnoDB: type is %lu, which is not an index page!\n
+InnoDB: We try to resolve the problem by skipping the insert buffer\n
+InnoDB: merge for this page. Please run CHECK TABLE on your tables\n
+InnoDB: to determine if they are corrupt after this.\n\n
+InnoDB: Please make a detailed bug report and send it to\n
+InnoDB: [EMAIL PROTECTED],
+ page_no, fil_page_get_type(page));
+ }
}
n_inserts = 0;
-2578,8 +2611,14
goto reset_bit;
}
+
+ if (corruption_noticed) {
+ rec_sprintf(err_buf, 450, ibuf_rec);
+
+ fprintf(stderr,
+InnoDB: Discarding record\n %s\n from the insert buffer!\n\n, err_buf);
-if (page) {
+} else if (page) {
/* Now we have at pcur a record which should be
inserted to the index page; NOTE that the call below
copies pointers to fields in ibuf_rec, and we must
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, February 21, 2003 10:35 PM
Subject: Re: InnoDB Crash [more info]
Michael,
how do I send you email? A 'Spamcop' seems to block email to you.
Regards,
Heikki
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: Michael T. Babcock [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, February 21, 2003 10:33 PM
Subject: Re: InnoDB Crash [more info]
Michael,
your database is probably corrupt.
The normal procedure in this case is to use the my.cnf option
set-variable=innodb_force_recovery=4
and dump your tables and recreate the whole tablespace.
But, please do not do that yet. I will send you a new version of
/mysql/innobase/ibuf/ibuf0ibuf.c
which will print a lot of diagnostic information. I want to see what it
prints.
A general note: you are using a very old Linux kernel 2.2.14. Some
combinations of Linux + hardware seem to cause sporadic table
corruption.
One user was able to resolve the problem by upgrading to a stock kernel
2.4.20.
Regards,
Heikki
Innobase Oy
sql query
- Original Message -
From: Michael T. Babcock [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Friday, February 21, 2003 9:23 PM
Subject: Re: InnoDB Crash [more info]
Michael T. Babcock wrote:
I've got a nice MySQL crash that happened during the night last night
and
I
can't seem to get it to come back online for me
without --skip-innodb.
Follow-up with mysql's bug report:
MySQL support: none
Synopsis:InnoDB Crashing on Startup
Severity:critical
Priority:medium
Category:mysql
Class:sw-bug
Release:mysql-3.23.55 (yes)
Environment:
System: Linux web.fibrespeed.net 2.2.14-10.0 #21 Fri Apr 21 00:22:11
EDT
2000 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from
/usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='-O2 -march=pentium' CXX='gcc'
CXXFLAGS='-O2 -march=pentium' LDFLAGS='-static'
LIBC:
lrwxrwxrwx1 root root 13 Apr 10 2000 /lib/libc.so.6
- libc-2.1.3.so
-rwxr-xr-x1 root root 4101836 Jan 15 2001
/lib/libc-2.1.3.so
-rw-r--r--1 root root 20273324 Jan 15 2001
/usr/lib/libc.a
-rw-r--r--1 root root 178 Jan 15 2001
/usr/lib/libc.so
lrwxrwxrwx1 root root 10 Jul 20 2001
/usr/lib/libc
re: InnoDB
On Wednesday 19 February 2003 13:21, Rusch (ext) Reiner wrote: I have one question about InnoDB-tables especial the filesize of all tables. In the past I took MyISAM but found out, some things in InnoDB make the system more stable. But I want to get more free space. In MyISAM the space grows automatically, but not in InnoDB by default. Take a look at autoextend option: http://www.mysql.com/doc/en/InnoDB_start.html In my /etc/my.cnf the line for the path and file size is not active like in the original file. But it works but with about 250-300MB max. size (don't know exactly). If I uncomment this line to expand my space, mysql (4.0.10) doesn't start. Don't know why. Check error logs. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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
Assuming your server supports innodb, you can use: Alter table table_name TYPE = InnoDB; if it failed, simply create a table with the new type, populate it with the data in your table (with the wrong type), drop the old table and rename the new table. --- [EMAIL PROTECTED] wrote: Are you running mysql or mysql-max? InnoDB is not supported in standard mysql. If you are running max then are there any error messages? Extranet [EMAIL PROTECTED] - 05/02/2003 17:17 To:mysql cc: Subject:innodb I have a small probleme with MySQL in 3.23.55. I do not manage to create my tables under the format InnoDB by specifying the type of creation at the end of the command. Thank you in advance - 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 This message and any attachments (the message) is intended solely for the addressees and is confidential. If you receive this message in error, please delete it and immediately notify the sender. Any use not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval. The internet can not guarantee the integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will) not therefore be liable for the message if modified. - Ce message et toutes les pieces jointes (ci-apres le message) sont etablis a l'intention exclusive de ses destinataires et sont confidentiels. Si vous recevez ce message par erreur, merci de le detruire et d'en avertir immediatement l'expediteur. Toute utilisation de ce message non conforme a sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. L'internet ne permettant pas d'assurer l'integrite de ce message, BNP PARIBAS (et ses filiales) decline(nt) toute responsabilite au titre de ce message, dans l'hypothese ou il aurait ete modifie. - 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 __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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 foreign keys bug
Scott Wong [EMAIL PROTECTED], Do you think the following links may help? * http://www.mysql.com/doc/en/example-Foreign_keys.html * http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html * http://www.mysql.com/doc/en/ALTER_TABLE.html * http://www.mysql.com/doc/en/Using_InnoDB_tables.html * http://www.mysql.com/doc/en/Indexes.html This was an automated response to your email 'InnoDB foreign keys bug'. Final search keyword used to query the manual was 'InnoDB foreign keys bug'. Feedbacks, suggestions and complaints about me should be directed to [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
Re: InnoDB foreign keys bug
Scott, I would like to declare this as a 'feature'. You should not define multiple foreign key constraints on the same foreign key/referenced key pair. I could, of course, add an error message if someone tries to do that. The algorithm in ON UPDATE CASCADE and ON DELETE CASCADE is this: InnoDB takes a single declared constraint, tries to update or delete child rows as instructed in that constraint, and checks that other constraints are satisfied. Also, I recommend not to define contradictory ON UPDATE actions. The following is an example of such: CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t2 (a) ON UPDATE CASCADE, FOREIGN KEY (a) REFERENCES t2 (b) ON UPDATE CASCADE) TYPE = InnoDB; Now if someone UPDATEs both a and b in t2, what should we do? Regards, Heikki Innobase Oy sql query . Subject: InnoDB foreign keys bug From: Scott Wong Date: Thu, 6 Feb 2003 15:25:12 -0800 Version: Mysql 4.0.10-gamma Description: Innodb fails to follow the foreign key rules after alter table. This bug can be done in several ways. How to repeat These variations gives same errors. 1st bug example: drop table if exists parent; drop table if exists child; CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id), FOREIGN KEY (parent id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; insert into parent set id = 1; insert into child set id=1, parent id=1; alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE ; update parent set id=2 where id=1; gives :ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails end 1st bug. Variations of this bug : drop table if exists parent; drop table if exists child; CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id), FOREIGN KEY (parent id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=INNODB; insert into parent set id = 1; insert into child set id=1, parent id=1; alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON UPDATE CASCADE ; update parent set id=2 where id=1; ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails delete from parent; The intesting thing is this : show create table child; FOREIGN KEY (`parent id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE, FOREIGN KEY (`parent id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE fix ? Thank you for your time Scott Wong Meiko America, INC - 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
Are you running mysql or mysql-max? InnoDB is not supported in standard mysql. If you are running max then are there any error messages? Extranet [EMAIL PROTECTED] - 05/02/2003 17:17 To:mysql cc: Subject:innodb I have a small probleme with MySQL in 3.23.55. I do not manage to create my tables under the format InnoDB by specifying the type of creation at the end of the command. Thank you in advance - 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 This message and any attachments (the message) is intended solely for the addressees and is confidential. If you receive this message in error, please delete it and immediately notify the sender. Any use not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval. The internet can not guarantee the integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will) not therefore be liable for the message if modified. - Ce message et toutes les pieces jointes (ci-apres le message) sont etablis a l'intention exclusive de ses destinataires et sont confidentiels. Si vous recevez ce message par erreur, merci de le detruire et d'en avertir immediatement l'expediteur. Toute utilisation de ce message non conforme a sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. L'internet ne permettant pas d'assurer l'integrite de ce message, BNP PARIBAS (et ses filiales) decline(nt) toute responsabilite au titre de ce message, dans l'hypothese ou il aurait ete modifie. - 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 Foreign Key Problems.
Scott, http://www.innodb.com/ibman.html#InnoDB_foreign_keys Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Corresponding ON UPDATE options are available starting from 4.0.8. Regards, Heikki Innobase Oy sql query Subject: Innodb Foreign Key Problems. From: Scott Wong Date: Wed, 5 Feb 2003 10:03:17 -0800 Hi. Simple parent/child table generates some weird output based on the order possible bug? Mysql 3.23.54 CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id), FOREIGN KEY (parent id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; show create table commands give this : FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) when it should be FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE insert into parent set id = 1; insert into child set id=1, parent id=1; delete from parent where id = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Now if the child was created like this : drop table child; CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id), FOREIGN KEY (parent id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=INNODB; show create table gives this : FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE Order matters! :) and another bug from same tables: (do this with the create table command above) alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON UPDATE CASCADE ON DELETE CASCADE alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE --reversed from above ON UPDATE CASCADE and you'll get some nice output from show create table: FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE, FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE, FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) Thank you for your time. Fix? Scott Wong, Meiko America, INC - 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 Problems
Davy, DO I have been trying to get InnoDb to run on my windows XP machine. DO (...) DO #innodb_data_home_dir = DO #innodb_log_group_home_dir = DO #innodb_data_file_path = /ibdata/ibdata1:10M:autoextend The remarks are in German, but I guess you will know what to do if you look at the following excerpt from my my.ini file on a Win2K machine: # InnoDB # Speicherort fuer Tablespaces (Vorgabe: DATADIR) innodb_data_home_dir=c:\mysql\innodb # Vorgabemaessig wird ab MySQL 4.0 # ein 64 MB grosser Tablespace namens ibdata1 # (per Vorgabe in DATADIR) angelegt. # Folgende Einstellungen ueberschreiben diesen Wert. innodb_data_file_path = ibdata1:100M;ibdata2:100M # Speicherort fuer Logdateien innodb_log_group_home_dir=c:\mysql\innodb innodb_log_arch_dir = c:\mysql\innodb # Logarchivierung anschalten (1) oder abschalten (0) innodb_log_archive=0 # Einstellungen fuer Logdateien # set-variable = innodb_mirrored_log_groups=1 # set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M # Puffer-Poolgroesse auf 50% bis 80% # des Arbeitsspeichers Ihres Computers setzen set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # Transaktionen immer sicher (1) oder nicht (0) innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - 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 Problems
i see that you don't specify any path for innodb_data_home_dir, innodb_log_group_home_dir and innodb_data_file_path ... write down the correct path and retry. regards. --- Davy Obdam [EMAIL PROTECTED] ha scritto: Hi People, I have been trying to get InnoDb to run on my windows XP machine. I have MySQL 3.23.55 installed and i am running the mysqld-max-nt server. I have added the following lines to my my.ini file: (note the # because i didnt get it to work, i dissabled it) [mysqld] . #innodb_data_home_dir = #innodb_log_group_home_dir = #innodb_data_file_path = /ibdata/ibdata1:10M:autoextend When i start my MySQL server with this config, i get and error and the server wont start. What am i doing wrong here?? Any help is appreciated, thanks for your time. Best regards, Davy Obdam mailto:[EMAIL PROTECTED] -- Davy Obdam - Obdam webdesign© mailto:[EMAIL PROTECTED] web: www.davyobdam.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 __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - 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: Limited creation of tables
In the last episode (Feb 04), Shomal Bafna said: The box is running a MySQL 3.23-47 Max rpm based version on a Mandrake 8.2 Linux with Duron processor and 256MB Ram. Thera are 10 InnoDb type tables created and several MyISAM types too (approx 30 more.). On adding the 11 table of InnoDb type the following error is displayed: ERROR 1005 at line 1: Can't create table './myDB/personalDetails.frm' (errno: 135) $ perror 135 Error code 135: Unknown error: 135 135 = No more room in record file Did you fill up your InnoDB tablespace? -- Dan Nelson [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
Re: Innodb: Limited creation of tables
Shomal, - Original Message - From: Shomal Bafna [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, February 04, 2003 7:46 PM Subject: Innodb: Limited creation of tables The box is running a MySQL 3.23-47 Max rpm based version on a Mandrake 8.2 Linux with Duron processor and 256MB Ram. Thera are 10 InnoDb type tables created and several MyISAM types too (approx 30 more.). On adding the 11 table of InnoDb type the following error is displayed: ERROR 1005 at line 1: Can't create table './myDB/personalDetails.frm' (errno: 135) heikki@hundin:~/mysql-max-3.23.47-pc-linux-gnu-i686/bin perror 135 Error code 135: Unknown error 135 135 = No more room in record file Please see http://www.innodb.com/ibman.html#InnoDB_start and http://www.innodb.com/ibman.html#Adding_and_removing about how to add another ibdata file. Need help. Shomal Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - 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: Limited creation of tables
On Tue, 4 Feb 2003, Shomal Bafna wrote: The box is running a MySQL 3.23-47 Max rpm based version on a Mandrake 8.2 Linux with Duron processor and 256MB Ram. Thera are 10 InnoDb type tables created and several MyISAM types too (approx 30 more.). On adding the 11 table of InnoDb type the following error is displayed: ERROR 1005 at line 1: Can't create table './myDB/personalDetails.frm' (errno: 135) Need help. Shomal Hello! It seems to be no more space in the ibdata file, check the my.cnf file, the definition for the ibdata must be set to autoextend. A problem with innodb tables is, all the databases and tables goes to the same tablespace (innodb file), and always grow up, it is necessary make dumps, of all the databases with innodb tables, creating new innodb files. Grover - 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: Limited creation of tables
It appears that the record has room out of room. 135 = No more room in record file -Original Message- From: Shomal Bafna [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 9:56 AM To: [EMAIL PROTECTED] Subject: Innodb: Limited creation of tables The box is running a MySQL 3.23-47 Max rpm based version on a Mandrake 8.2 Linux with Duron processor and 256MB Ram. Thera are 10 InnoDb type tables created and several MyISAM types too (approx 30 more.). On adding the 11 table of InnoDb type the following error is displayed: ERROR 1005 at line 1: Can't create table './myDB/personalDetails.frm' (errno: 135) Need help. Shomal - 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 table with auto-increment column doesn't create (err 1005)
Matthias, what have you done? Below you create table 'foo' but mysql answers that it cannot create 'stundenliste.frm'. mysql create table foo (id int auto_increment,unique key (id)) type=innodb; ERROR 1005: Can't create table './test_smurf/stundenliste.frm' (errno: 121) mysql create table stundenliste (id int auto_increment) type=innodb; ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key mysql create table stundenliste (id int auto_increment) type=myisam; *SUCCESS* I guess you have the table stundenliste in the internal data dictionary of InnoDB, but have somehow managed to delete the .frm file. Look to the file yourhostname.err in the datadir of MySQL. You should find there: heikki@hundin:~/mysql-standard-4.0.6-gamma-pc-linux-i686/bin mysqld 030130 14:15:17 InnoDB: Started mysqld: ready for connections 030130 14:15:48 InnoDB: Error: table test/stundenliste already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version = 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and moving the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. InnoDB: You can look further help from section 15.1 of InnoDB: http://www.innodb.com/ibman.html I tested deleting the .frm file manually and indeed I got: heikki@hundin:~/mysql-standard-4.0.6-gamma-pc-linux-i686/bin mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.6-gamma-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table stundenliste (id int auto_increment,unique key (id)) type=in nodb; ERROR 1005: Can't create table './test/stundenliste.frm' (errno: 121) mysql Regards, Heikki Innobase Oy sql query Subject: Innodb table with auto-increment column doesn't create (err 1005) From: Smurf Date: Thu, 30 Jan 2003 11:04:18 +0100 Description: Table not creatable How-To-Repeat: mysql create table foo (id int auto_increment,unique key (id)) type=innodb; ERROR 1005: Can't create table './test_smurf/stundenliste.frm' (errno: 121) mysql create table stundenliste (id int auto_increment) type=innodb; ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key mysql create table stundenliste (id int auto_increment) type=myisam; *SUCCESS* Fix: Unknown. Submitter-Id: [EMAIL PROTECTED] Originator: Matthias Urlichs Organization: noris network AG MySQL support: licence Synopsis: auto_increment innodb tables don't Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.6-gamma (Bitkeeper source) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.2/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-i nclude-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext - -enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.2 20030124 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='-O2 -fomit-frame-pointer -g ' CXX='g++' CXXFLAGS='-O2 -fomit-frame-pointer -g -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-01-30 01:26 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1102984 2003-01-21 23:15 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337952 2003-01-21 23:15 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-01-21 23:15 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--with-libwrap' '--enable-assembler' '--with-innodb' '--enable-static' '--enable-shared' '--enable-local-infile' '--with-raid' '--enable-thread-safe-client' '--without-readline' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--without-bench' '--with-client-ldflags=-lstdc++' '--with-extra-charsets=all' 'CC=gcc' 'CFLAGS=-O2 -fomit-frame-pointer -g ' 'CXXFLAGS=-O2
RE: InnoDB / MyISAM
-- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 29, 2003 11:29 AM To: Joe Stump Subject: Re: InnoDB / MyISAM Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I've been looking into switching to InnoDB. I have a few questions though. 1.) Can I do joins between InnoDB / MyISAM tables? 2.) Do FK restraints work if the FK referenced is in a MyISAM table? I'm trying to get the best of both worlds. I'd have a data table with FULLTEXT and then have join tables, etc. InnoDB so I can have transactions and FK restraints. I've looked through the archives and couldn't find any answers to these questions. BTW, I'm using 4.0-gamma right now. --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. - 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 is losing records, there are gaps in autoincrementsequence
Hi. - Original Message - InnoDB appears to be losing records. The version I am using is 3.32.53 in Windows 2000. Gaps are appearing in the autoincrement sequence when the application does not permit record deletions. Records disappear although they have been retrieved for printing earlier. We seem to be having the same problem: Even though there is not a single delete statement referencing the table in question from time to time records seem to disappear. I even let mysql do an update log and couldn't find a delete statement there either. Today I found the following article in the mailing list archives: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:40074 It explains how a REPLACE statement on a table with multiple unique keys might cause more than one row to be deleted. I am not sure if this is definite cause of the disappearing records in my application, but I am going to investigate this further. Regards, Robert. - 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 is losing records, there are gaps in autoincrement sequence
Robert, yes, if there are multiple UNIQUE keys in a table, then in the REPLACE implementation there is a loop which DELETEs all records which have conflicting UNIQUE key values. Since you have kept the binlog of your database, you can rerun the SQL statements and try to find where your rows magically disappear. I am not fond of the REPLACE statement. It is 'implicit programming', which can produce hard-to-find bugs in your application. Similar implicit bugs arise from the use of ON DELETE/UPDATE CASCADE and triggers. Regards, Heikki sql query Subject: Re: InnoDB is losing records, there are gaps in autoincrement sequence From: Robert Wunderer Date: 24 Jan 2003 12:53:04 +0100 Hi. - Original Message - InnoDB appears to be losing records. The version I am using is 3.32.53 in Windows 2000. Gaps are appearing in the autoincrement sequence when the application does not permit record deletions. Records disappear although they have been retrieved for printing earlier. We seem to be having the same problem: Even though there is not a single delete statement referencing the table in question from time to time records seem to disappear. I even let mysql do an update log and couldn't find a delete statement there either. Today I found the following article in the mailing list archives: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:40074 It explains how a REPLACE statement on a table with multiple unique keys might cause more than one row to be deleted. I am not sure if this is definite cause of the disappearing records in my application, but I am going to investigate this further. Regards, Robert. - 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 enable/disable keys
Carlos, - Original Message - From: Carlos Proal [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, January 19, 2003 7:13 PM Subject: Innodb enable/disable keys Hi, i have a simple question: The sql statement: alter table _table_name_ disable keys; has any action on INNODB tables ?, helps improving bulk inserts ?. no effect. If you have UNIQUE secondary indexes, you can try SET UNIQUE_CHECKS=0; to speed up disk-bound inserts. thanx Carlos Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - 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 Foreign Key Questions
Muhammed, - Original Message - From: Muhammed Syyid [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, January 16, 2003 3:06 PM Subject: Re: InnoDB Foreign Key Questions Second the same question :). What does the CONSTRAINT keyword regarding FOREIGN KEYS do? the constraint name is simply ignored. Karam Chand, as far as I know, the drop command hasn't been implemented yet. So the only way to drop a foreign key is to drop and re-create the table. DROP FOREIGN KEY will come in some 4.1.x version. Since the constraint name is not stored, and is not required by the FOREIGN KEY syntax, I think that the syntax could be ALTER TABLE frobboz DROP FOREIGN KEY (column1) REFERENCES abbaguu (column2); Muhammed Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query [EMAIL PROTECTED] (Karam Chand) wrote in message news:avva22$168e$[EMAIL PROTECTED]... Hello I have two tables of InnoDB type. CREATE TABLE `ledger` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, PRIMARY KEY (`id`), KEY `id` (`name`) ) TYPE=InnoDB; CREATE TABLE `voucher` ( `id` int(11) NOT NULL default '0', `vdate` date NOT NULL default '-00-00', `amount` decimal(10,0) default '100', `name` varchar(50) default 'Karam', PRIMARY KEY (`id`,`vdate`) ) TYPE=InnoDB; Now I add a Foreign Key reference to ledger.id for voucher id by using the following command - alter table voucher add constraint fk_key_1 foreign key (id) references ledger (id) now when i do a query - show table status like 'voucher' I am getting the following value in the comment field. InnoDB free: 23552 kB; (id) REFER rohit/ledger(id) It shows that foreign key has been made but i had specified it to be fk_key_1. Even if I make the Foreign Key without the keyword constraint the same thing happens ? So, what is the purpose of CONSTRAINT keyword. How can I give a name to a relationship. Also after reading the MySQL and InnoDB docs I am not able to guess how to drop a FOREIGN KEY ? How can I do that ? I am using mysql-mx-nt 3.23.54 running as a service in WinXP. Any help will be appreciated. Thanks in advance. Karam sql, query ( filteraide ) __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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 - 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 is losing records, there are gaps in autoincrement sequence
Hi! - Original Message - From: My Deja [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, January 16, 2003 12:39 PM Subject: InnoDB is losing records, there are gaps in autoincrement sequence InnoDB appears to be losing records. The version I am using is 3.32.53 in Windows 2000. Gaps are appearing in the autoincrement sequence when the application does not permit record deletions. Records disappear although they have been retrieved for printing earlier. I have not seen this kind of bug reported. Are you sure you do not roll back the insertions? Or delete the rows? The auto-inc sequence will not be continuous if you roll back insertions, or delete rows. Does anyone know how this problem can be fixed? I am checking the application code to see if records can be deleted, either deliberately or accidentally. Has anyone come across such a problem? (sql,query,database,odbc - spam block bypass) Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - 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 foreign keys problem
Heikki Tuuri wrote: In the future, MySQL might do the following: in CREATE TABLE abbaguu ( ... FOREIGN KEY (column1) REFERENCES frobboz (column2) ) TYPE=InnoDB; it could check if there is a suitable index in abbaguu. If not, it would create the index automatically. For what its worth, and I'm sure you've considered this, at least table creation is a semi-rare enough event in the life and activity of the average database that adding more overhead to the process wouldn't affect much. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 table, NOT NULL question
Harald Fuchs wrote: You _did_ enter some data, namely a string which just happens to be the empty string (which in turn is different from no data, i.e. NULL). How should MySQL know that you don't want empty strings? I think the user expects: INSERT INTO table (bar) VALUES (text); to behave the same as: INSERT INTO table (foo, bar) VALUES (NULL, text); which would throw an error (correctly). No comment ;-) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 table, NOT NULL question
Ahh, but I didn't enter data that is the point. MySQL definitely knows that I omitted a field, it just happens that MySQL automatically turns the field into an empty string. In the absence of CHECK constraints perhaps the following behavior could be implemented. I would like to see MySQL throw an error if: NOT NULL is specified No DEFAULT is specified The field is omitted from an INSERT Possibly a compatibility switch in the cnf file could turn on such a check. Thanks, Gabe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Harald Fuchs Sent: Wednesday, January 15, 2003 1:23 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB table, NOT NULL question In article [EMAIL PROTECTED], Adolfo Bello [EMAIL PROTECTED] writes: This is wierd. If you define a field as not null is because you want the user to enter some data, for example, First Name and Last Name. Yes, I know one should check it at the application level but, isn't MySQL being a little too flexible with this? You _did_ enter some data, namely a string which just happens to be the empty string (which in turn is different from no data, i.e. NULL). How should MySQL know that you don't want empty strings? Other databases offer check constraints or triggers for that; MySQL still doesn't. [Filter fodder: SQL query] - 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 table, NOT NULL question
On Monday 13 January 2003 23:38, Gabe Geisendorfer wrote:
Hello, I'm in the process of moving from Postgres to MySQL and I have a
question.
+How do you prevent a field from being left empty?
I have an InnoDB table that looks like the following.
CREATE TABLE `stuff` (
`stuff_id` int(11) NOT NULL auto_increment,
`somevalue1` varchar(35) NOT NULL,
`somevalue2` varchar(35) NOT NULL,
PRIMARY KEY (`stuff_id`)
) TYPE=InnoDB COMMENT='stuff table';
I run the following insert statement.
INSERT INTO stuff ( somevalue2 ) VALUES ('blah')
Now the field somevalue1 is empty. Doesn't this violate the NOT NULL
constraint on the field?
It's a known behaviour and is described in the MySQL manual:
http://www.mysql.com/doc/en/Bugs.html
You can change it if you compile MySQL server with -DDONT_USE_DEFAULT_FIELDS
option. But in this case you can't use default values at all.
--
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 table, NOT NULL question
Hello, I'm in the process of moving from Postgres to MySQL and I have a
question.
+How do you prevent a field from being left empty?
I have an InnoDB table that looks like the following.
CREATE TABLE `stuff` (
`stuff_id` int(11) NOT NULL auto_increment,
`somevalue1` varchar(35) NOT NULL,
`somevalue2` varchar(35) NOT NULL,
PRIMARY KEY (`stuff_id`)
) TYPE=InnoDB COMMENT='stuff table';
I run the following insert statement.
INSERT INTO stuff ( somevalue2 ) VALUES ('blah')
Now the field somevalue1 is empty. Doesn't this violate the NOT NULL
constraint on the field?
Well, by empty you probably mean , which is not null, is it?
- Csongor
-
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 table, NOT NULL question
Are there plans in future versions to have MySQL fail the below INSERT
statement?
It would be useful to at least receive a warning.
In my situation, I only intend on leaving fields blank by omitting them
if:
1. I have set a DEFAULT on the column.
2. Or the column accepts NULL's.
Thanks,
Gabe
-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 14, 2003 7:11 AM
To: [EMAIL PROTECTED]
Subject: re: InnoDB table, NOT NULL question
On Monday 13 January 2003 23:38, Gabe Geisendorfer wrote:
Hello, I'm in the process of moving from Postgres to MySQL and I have
a question.
+How do you prevent a field from being left empty?
I have an InnoDB table that looks like the following.
CREATE TABLE `stuff` (
`stuff_id` int(11) NOT NULL auto_increment,
`somevalue1` varchar(35) NOT NULL,
`somevalue2` varchar(35) NOT NULL,
PRIMARY KEY (`stuff_id`)
) TYPE=InnoDB COMMENT='stuff table';
I run the following insert statement.
INSERT INTO stuff ( somevalue2 ) VALUES ('blah')
Now the field somevalue1 is empty. Doesn't this violate the NOT NULL
constraint on the field?
It's a known behaviour and is described in the MySQL manual:
http://www.mysql.com/doc/en/Bugs.html
You can change it if you compile MySQL server with
-DDONT_USE_DEFAULT_FIELDS
option. But in this case you can't use default values at all.
--
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
-
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 table, NOT NULL question
I'm aware that NULL and are not the same thing.. I would like to
prevent the column from accepting values automatically ( with out the
presence of a DEFAULT).
-Original Message-
From: Csongor Fagyal [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 14, 2003 8:21 AM
To: [EMAIL PROTECTED]
Subject: Re: InnoDB table, NOT NULL question
Hello, I'm in the process of moving from Postgres to MySQL and I have
a question.
+How do you prevent a field from being left empty?
I have an InnoDB table that looks like the following.
CREATE TABLE `stuff` (
`stuff_id` int(11) NOT NULL auto_increment,
`somevalue1` varchar(35) NOT NULL,
`somevalue2` varchar(35) NOT NULL,
PRIMARY KEY (`stuff_id`)
) TYPE=InnoDB COMMENT='stuff table';
I run the following insert statement.
INSERT INTO stuff ( somevalue2 ) VALUES ('blah')
Now the field somevalue1 is empty. Doesn't this violate the NOT NULL
constraint on the field?
Well, by empty you probably mean , which is not null, is it?
- Csongor
-
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 table, NOT NULL question
I have an InnoDB table that looks like the following.
CREATE TABLE `stuff` (
`stuff_id` int(11) NOT NULL auto_increment,
`somevalue1` varchar(35) NOT NULL,
`somevalue2` varchar(35) NOT NULL,
PRIMARY KEY (`stuff_id`)
) TYPE=InnoDB COMMENT='stuff table';
I run the following insert statement.
INSERT INTO stuff ( somevalue2 ) VALUES ('blah')
Now the field somevalue1 is empty. Doesn't this violate the NOT NULL
constraint on the field?
Well, by empty you probably mean , which is not null, is it?
This is wierd. If you define a field as not null is because you want the
user to enter some data, for example, First Name and Last Name. Yes, I
know one should check it at the application level but, isn't MySQL being
a little too flexible with this?
__
/ \\ @ ____@ Adolfo Bello [EMAIL PROTECTED]
/ // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente
/ \\ // / \\/ // // / //cel: +58 416 609-6213
/___ / _/\__\\//__/ // fax: +58 212 952-6797
www.bisapi.com //pager: www.tun-tun.com (# 609-6213)
-
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 vs. MySQL performance Issue
If this is going to hurt someone please when u see mails from [EMAIL PROTECTED] do not scroll down to the bottom I won't. I've had enough of this, so I just set my mail program to erase any mails from you. I do the same for anyone who repeatedly promotes religious or political views on the mailing list, whether passively or actively. I subscribe to this list to obtain info on MySQL, not middle-eastern or any other politics. I can read the newspaper for that. Steve. - 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: [OT] Re: InnoDB vs. MySQL performance Issue
To MySQL AB: Your lists are being used to disseminate extremist muslim hate-speech. As such, this will reflect terribly negatively on your company if you let this go unchallenged much longer. This individual, if you can call him that, has repeatedly said inflammatory stuff about Israel in his signature. Clearly, no one in an official capacity at your company saw fit to address that. However, now he has equated the Jews and Christians living in [his] lands to pigs and monkeys. This kind of racism is reprehensible in the extreme. This scum is posting his screed from Egypt; I, on the other hand, live in a country which has a much richer tradition of freedom of speech, the United States. And, contrary to this morons assertions, you DO NOT have the right to say anything, at any time, anywhere. And, particularly not in a private forum such as this. There are no first amendment protections from non-governmental actors ie: in this case MySQLAB. Thus, if MySQLAB chooses to do nothing about it your company will be tacitly condoning his behavior. I recommend that at a bare minimum this guy should be removed from the list, for the sheer disruption that he has caused to the normally extremely helpful discourse which is usual in this list. Furthermore, I am a DBA in a Fortune 500 company, and I do have significant influence over the buying decisions of my company with respect to databases. You can be sure that any business I have will not go to you if you do nothing. Sincerely, Paul Magid, DBA -Original Message- From: Sameh Attia [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 1:54 AM To: [EMAIL PROTECTED] Subject: [OT] Re: InnoDB vs. MySQL performance Issue Sam Przyswa wrote: Septenber 11 2001, more than 3000 women, men, child, killed, Tel Aviv January 5 2003, 23 death and 100 injured by islamic terrorists, that's the islamic history (small part). Sam Apache-PHP-MySQL user. -- Albert Einstein, Karl Marx, Jesus Christ, and you Mohamed what have you done for the world ? First of all do not cc me directly. I do get a copy of ur shit by the list. If you think that Mohamed did not do something for the world which is not true. At least he did not leave us with some pigs and monkeys. Read your history if u forgot. If u were a muslim, I thank God that u were not, u would learn how to respect other prophets. We as muslims do repect Jesus, Moses, Ibrahim, and other prohpets and we are asked to believe in them because it is a fundamental believe in our religion. If u want to know more about out prophet Mohamed go and read http://www.islamonline.com/PAGE12.html Also you will find below detailed information about what the modern, democractec, and civilized gang of Zions does to muslims. Israeli Massacres http://www.ummah.net/unity/palestine/index.htm Sabra Shatila http://www.ummah.net/unity/sabra/main.html Deir Yassin http://www.deiryassin.org http://www.ariga.com/peacewatch/dy http://www.us-israel.org/jsource/History/deir_yassin.html Qana http://web.cyberia.net.lb/qana/ .and much more but the memory does not help. Sam go and get a life and may God forgive u. But u should believe that sooner or later there will be no more pigs and monkeys in our lands. -- Sameh Attia Senior System Engineer T.E. Data -- __ __ _ _ _/ /_/ /_(_)___ _ / ___/ __ `/ __/ __/ / __ `/ (__ ) /_/ / /_/ /_/ / /_/ / //\__,_/\__/\__/_/\__,_/ She who is in my mind and mouth, I love her with all my heart and blood We'll restore OUR Palestine - 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: [OT] Re: InnoDB vs. MySQL performance Issue
Hi Paul, MYSQL AB maybe have already done something to diffuse this situation but without to get involved this very-mega-ultra public MYSQL LIST. And i think also is a duty of every member of this list to make all to keep clean this list but without any implication of it. I feel frustration also, because on January 5 2003 two of my townsman(romanians) was killed without any fault by the palestinian terrorist. But we must be rational and must to make difference between our feelings and MYSQL company with all what it is. Maybe it's time to show that the pigs and monkey are able to forgive in comparison with him,witch is man and witch is not able to respect all the members of this list(by making propaganda). If we think that this LISTS belong to MYSQL AB then MYSQL AB is a victim of this disputation.And if MYSQL AB will be have a PUBLIC attitude this means MYSQL AB will be agree with this kind of disputation .Personally, i think THIS NOT A PURPOSE OF MYSQL AB. Anyhow,i think this list belong to all MYSQL users/software developers and in this situation MYSQL AB didn't have ANY FAULT. With all my consideration, Gelu Gogancea Software Developer - System Integrator __ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Paul Magid [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; 'Michael Widenius' [EMAIL PROTECTED] Sent: Thursday, January 09, 2003 7:46 AM Subject: RE: [OT] Re: InnoDB vs. MySQL performance Issue To MySQL AB: Your lists are being used to disseminate extremist muslim hate-speech. As such, this will reflect terribly negatively on your company if you let this go unchallenged much longer. This individual, if you can call him that, has repeatedly said inflammatory stuff about Israel in his signature. Clearly, no one in an official capacity at your company saw fit to address that. However, now he has equated the Jews and Christians living in [his] lands to pigs and monkeys. This kind of racism is reprehensible in the extreme. This scum is posting his screed from Egypt; I, on the other hand, live in a country which has a much richer tradition of freedom of speech, the United States. And, contrary to this morons assertions, you DO NOT have the right to say anything, at any time, anywhere. And, particularly not in a private forum such as this. There are no first amendment protections from non-governmental actors ie: in this case MySQLAB. Thus, if MySQLAB chooses to do nothing about it your company will be tacitly condoning his behavior. I recommend that at a bare minimum this guy should be removed from the list, for the sheer disruption that he has caused to the normally extremely helpful discourse which is usual in this list. Furthermore, I am a DBA in a Fortune 500 company, and I do have significant influence over the buying decisions of my company with respect to databases. You can be sure that any business I have will not go to you if you do nothing. Sincerely, Paul Magid, DBA -Original Message- From: Sameh Attia [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 1:54 AM To: [EMAIL PROTECTED] Subject: [OT] Re: InnoDB vs. MySQL performance Issue Sam Przyswa wrote: Septenber 11 2001, more than 3000 women, men, child, killed, Tel Aviv January 5 2003, 23 death and 100 injured by islamic terrorists, that's the islamic history (small part). Sam Apache-PHP-MySQL user. -- Albert Einstein, Karl Marx, Jesus Christ, and you Mohamed what have you done for the world ? First of all do not cc me directly. I do get a copy of ur shit by the list. If you think that Mohamed did not do something for the world which is not true. At least he did not leave us with some pigs and monkeys. Read your history if u forgot. If u were a muslim, I thank God that u were not, u would learn how to respect other prophets. We as muslims do repect Jesus, Moses, Ibrahim, and other prohpets and we are asked to believe in them because it is a fundamental believe in our religion. If u want to know more about out prophet Mohamed go and read http://www.islamonline.com/PAGE12.html Also you will find below detailed information about what the modern, democractec, and civilized gang of Zions does to muslims. Israeli Massacres http://www.ummah.net/unity/palestine/index.htm Sabra Shatila http://www.ummah.net/unity/sabra/main.html Deir Yassin http://www.deiryassin.org http://www.ariga.com/peacewatch/dy http://www.us-israel.org/jsource/History/deir_yassin.html Qana http://web.cyberia.net.lb/qana/ .and much more but the memory does not help. Sam go and get a life and may God forgive u. But u should believe that sooner or later there will be no more pigs and monkeys in our lands. -- Sameh Attia Senior
[OT] Re: InnoDB vs. MySQL performance Issue
Sam Przyswa wrote: Septenber 11 2001, more than 3000 women, men, child, killed, Tel Aviv January 5 2003, 23 death and 100 injured by islamic terrorists, that's the islamic history (small part). Sam Apache-PHP-MySQL user. -- Albert Einstein, Karl Marx, Jesus Christ, and you Mohamed what have you done for the world ? First of all do not cc me directly. I do get a copy of ur shit by the list. If you think that Mohamed did not do something for the world which is not true. At least he did not leave us with some pigs and monkeys. Read your history if u forgot. If u were a muslim, I thank God that u were not, u would learn how to respect other prophets. We as muslims do repect Jesus, Moses, Ibrahim, and other prohpets and we are asked to believe in them because it is a fundamental believe in our religion. If u want to know more about out prophet Mohamed go and read http://www.islamonline.com/PAGE12.html Also you will find below detailed information about what the modern, democractec, and civilized gang of Zions does to muslims. Israeli Massacres http://www.ummah.net/unity/palestine/index.htm Sabra Shatila http://www.ummah.net/unity/sabra/main.html Deir Yassin http://www.deiryassin.org http://www.ariga.com/peacewatch/dy http://www.us-israel.org/jsource/History/deir_yassin.html Qana http://web.cyberia.net.lb/qana/ .and much more but the memory does not help. Sam go and get a life and may God forgive u. But u should believe that sooner or later there will be no more pigs and monkeys in our lands. -- Sameh Attia Senior System Engineer T.E. Data -- __ __ _ _ _/ /_/ /_(_)___ _ / ___/ __ `/ __/ __/ / __ `/ (__ ) /_/ / /_/ /_/ / /_/ / //\__,_/\__/\__/_/\__,_/ She who is in my mind and mouth, I love her with all my heart and blood We'll restore OUR Palestine - 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
