deadlock - further information
Hello.
We have a web application for our intranet, which uses MySQL for db
management. The application has a way of logging query errors that are
generated in the system.
We get a mysql error recorded as - Deadlock found when trying to get lock;
try restarting transaction when one of the files tries to create a
temporary table. The SQL query for it is as below -
CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
{... conditions for selection ...}
I looked through the MySQL lists, but I could not get info as to why a
deadlock is created in this scenario. I also do not know how to replicate
the scenario, as this error is not logged always. Is there a particular case
when this happens?
I looked through
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
SELECT ... FROM is a consistent read, reading a snapshot of the database
and setting no locks ... So why is a lock being taken in the first place?
I am at a loss. Can someone shed light on this, or point me to some
resource?
Environment -
OS : RHEL 3
DB : MySQL 4.1.11 using INNoDB.
Regards,
Rithish.
Re: deadlock - further information
Rithish,
- Original Message -
From: Rithish Saralaya [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, March 20, 2006 10:02 AM
Subject: deadlock - further information
--=_NextPart_000_0059_01C64C23.16088020
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Hello.
We have a web application for our intranet, which uses MySQL for db
management. The application has a way of logging query errors that are
generated in the system.
We get a mysql error recorded as - Deadlock found when trying to get
lock;
try restarting transaction when one of the files tries to create a
temporary table. The SQL query for it is as below -
CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
{... conditions for selection ...}
I looked through the MySQL lists, but I could not get info as to why a
deadlock is created in this scenario. I also do not know how to replicate
the scenario, as this error is not logged always. Is there a particular
case
when this happens?
I looked through
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
SELECT ... FROM is a consistent read, reading a snapshot of the database
and setting no locks ... So why is a lock being taken in the first
place?
I am at a loss. Can someone shed light on this, or point me to some
resource?
in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to
make InnoDB to use a consistent read in the SELECT tables in CREATE ...
SELECT. Read the caveats about the my.cnf option, though.
This is explained at:
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
I noticed that this behavior is broken in 5.0. I filed the bug report
http://bugs.mysql.com/bug.php?id=18350 about this. Thank you for bringing
this up.
Environment -
OS : RHEL 3
DB : MySQL 4.1.11 using INNoDB.
Regards,
Rithish.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy regex replace?
2006/3/19, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED]: On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Yeah, but sometimes beoing able to do such things on the mysql command line would be very helpful ! (Instead of having a script for such simple things which would be like having a jack hammer for a nail.. ) -- Pooly Webzine Rock : http://www.w-fenec.org/
RE: Easy regex replace?
If %20 are the actual characters in the varchar column you shuld be able to do UPDATE table SETcolumn_name =REPLACE(column_name,'%20',' '); You might have to use REPLACE(column_name,'\%20',' '); to force MySQL to treat % as an actual value instead of a wild card. -Original Message- From: Pooly [mailto:[EMAIL PROTECTED] Sent: Monday, March 20, 2006 5:11 AM To: MySQL General Subject: Re: Easy regex replace? 2006/3/19, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED]: On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Yeah, but sometimes beoing able to do such things on the mysql command line would be very helpful ! (Instead of having a script for such simple things which would be like having a jack hammer for a nail.. ) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 20 Mar 2006, Martijn Tonies wrote: To: mysql@lists.mysql.com From: Martijn Tonies [EMAIL PROTECTED] Subject: Re: Discussion: the efficiency in using foreign keys Hi, This is a fundamental concept in RDBMS: the use of foreign keys in database design. I'd just like to poll the community here, on whether it is a best practice, or practically essential to 'link' related tables by use of foreign keys. For myself, I usually do all the validity checking when adding a new record that references a record id from another table. I understand that this may not be efficient because it becomes 2 database calls (and db calls are expensive in high-load environments). What are the advantages/ disadvantages in using foreign keys? In MySQL, this means one cannot use MyISAM. Do you place a lot of triggers as well? When it comes to referential constraints, the answer is simple: ALWAYS put them on the database. Anyway who answers differently either never had to recover a database that was trashed by the lack of integrity constraints or has no ide what he's talking about. Most probably, this statement will get me tons of e-mail again ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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]
Problems with UTF and MySQL
I have problem with UTF-8 character set. I use MySQL 5 and Tomcat 5.5.9.I use NetBeans for programinnig in Java. I made one database and is in UTF8,also all my JSP pages are set to UTF8 encoding,and also all HTML are set to UTF8. When i put ,#273;,#269;,#263;, character date in database directly from MySQL Query tool and and display that data in jsp pages i see all character ok, but when i use jsp pages to insert #269;,#273;,,#263; character and want to display them i got some strange characters. Also i put my IE browser to UNICODE UTF8 encoding. How can i see correct ,#263;,#273;,,#269; in my browser correct? Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with UTF and MySQL
One must issue immediately after connection: SET NAMES 'utf8' Also look at: SHOW VARIABLES LIKE 'collation_%'; SHOW VARIABLES LIKE 'character_set_%'; Server must know what you are assking for... and they ALL have to talk the same language !!! -- Gabriel PREDA Senior Web Developer
multiple DB copies with periodic synchronization
Hi We have a mySQL database which is being used by a restaurant ordering system, in which many of the tables are being used to store menu item information, pricing etc. The restaurant started off as a single outlet, but is now about to open some more new premises. We would like to have ordering systems in the new premises which can use the information from the database on the original machines. For operational reasons, we want to synchronise the tables that hold all the menu information once a day, and then use the local copies throughout the day. Synchronisation will be over a VPN. We would prefer to do it this way, so that it the VPN goes down (e.g. WAN or phone line is out), the restaurant can still operate using the last synchronised copy of the menu. (as opposed to simply sending the SQL commands over the VPN). There will be one master machine will be where the restaurant managers make changes to their menus, which will get picked up in the morning by the remote machines. The master will also be used to store transactions from all the other branches (where it can be backed up). We'd like to synchronise the menu information in the morning, before the restaurant opens. Then after they close, the transaction tables for the days sales to be synchronised back to the master machine. What is the usual approach in this sort of case? Do we: 1) write our own perl or php script to run the sql commands we need to synchronise? There's not a lot of tables. I don't know much about MySQL commands for synchronisation, or even if there are any. 2) use a third party synchronisation tool ? I looked at SQLyog, but we don't need a GUI. 3) is there another way? - for example a built-in mechanism in mysql to allow duplication with regular synchronisation. Sorry if this is a dumb question - but I'm sure this type of scenario must be fairly common, for example when implementing redundant or distributed databases, so I would be very interested in hearing about people experiences and opinions. Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple DB copies with periodic synchronization
Chris Cowen [EMAIL PROTECTED] wrote on 03/20/2006 10:34:48 AM: Hi We have a mySQL database which is being used by a restaurant ordering system, in which many of the tables are being used to store menu item information, pricing etc. The restaurant started off as a single outlet, but is now about to open some more new premises. We would like to have ordering systems in the new premises which can use the information from the database on the original machines. For operational reasons, we want to synchronise the tables that hold all the menu information once a day, and then use the local copies throughout the day. Synchronisation will be over a VPN. We would prefer to do it this way, so that it the VPN goes down (e.g. WAN or phone line is out), the restaurant can still operate using the last synchronised copy of the menu. (as opposed to simply sending the SQL commands over the VPN). There will be one master machine will be where the restaurant managers make changes to their menus, which will get picked up in the morning by the remote machines. The master will also be used to store transactions from all the other branches (where it can be backed up). We'd like to synchronise the menu information in the morning, before the restaurant opens. Then after they close, the transaction tables for the days sales to be synchronised back to the master machine. What is the usual approach in this sort of case? Do we: 1) write our own perl or php script to run the sql commands we need to synchronise? There's not a lot of tables. I don't know much about MySQL commands for synchronisation, or even if there are any. 2) use a third party synchronisation tool ? I looked at SQLyog, but we don't need a GUI. 3) is there another way? - for example a built-in mechanism in mysql to allow duplication with regular synchronisation. Sorry if this is a dumb question - but I'm sure this type of scenario must be fairly common, for example when implementing redundant or distributed databases, so I would be very interested in hearing about people experiences and opinions. Thanks Chris For your master-to-copy synchronization, MySQL already has the facilities for this. Check the section of the manual for replication http://dev.mysql.com/doc/refman/4.1/en/replication.html For the situation where you store transactions in each satellite restaraunt and at the end of the night you want to re-synch with the master database back at HQ, that's something you will need to script. The reason is, MySQL replication is all one-way. Each replication source (the master) can auto-synch with one or more destinations (slaves) but each slave can only listen to one master at a time. That means that you cannot setup a database at HQ to listen to your multiple satellite sites using the built in facilities. However, you can cascade several servers and you can set up replication to move in a circular pattern. Each of these designs have positives and negatives and you should really understand replication a little better before making a decision. More details are in the reading. Several varieties of questions similar to yours have also been discussed on this list. You should check the archives, too, for more information: http://lists.mysql.com/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine
update statements problem
I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks!
Re: update statements problem
Hi, I think your problem is that OR and AND do not have the same precedence, AND binds tighter. So what you need is probably: (products_model=5217-01 OR products_model=5217-01) AND products_um=CS and the same for PK and EA. The way you have you'll get an update as soon as products_model=5217-01 /Johan cybermalandro cybermalandro wrote: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 2006-03-17 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update statements problem
cybermalandro cybermalandro [EMAIL PROTECTED] wrote on 03/20/2006 11:00:51 AM: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! It has to do with the expression you are using to pick which row to update WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS This is parsed as WHERE products_model=5217-01 OR (products_model=521701 AND products_um=CS) But what I think you wanted to say was WHERE (products_model=5217-01 OR products_model=521701) AND products_um=CS Add the parentheses around your OR terms and you should only be changing what you wanted to change. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: multiple DB copies with periodic synchronization
Chris, Interesting problem. We just went through a similar scenario. Our setup is: 1. A central server that has all data for everyone. 2. Remote databases (could be on either a single workstation or a server.) Requirements: 1. All administration (price changes, etc.) is done (web interface) on the central server. 2. Each remote site must be able to process transactions even if communications with central site are lost. 3. Only the data of interest to each remote site is replicated in that site's database. How we implemented: 1. A separate Java application runs on each remote computer that has a copy of a database. Periodically (timer), this application looks for data that has changed since it last checked. It knows the data that has changed because, for those tables that we want replicated, the primary key and table ID are put into a special table using triggers. 2. The changed remote data is sent to the central server (we have a Java application listening on a specific port) where it is stored and the primary key is returned to the remote (that way we always know the serial of the other side.) 3. When the remote has sent all the data it has accumulated, it asks the central server for any changed data (uses same process to determine what constitutes changed data) and that data is sent to the remote. In our case, we may have many remotes that are interested in the same or different data (several organizations may be running on the same central server.) Of course, there are the usual processes to make certain that data gets from one side to the other and that, once the data gets to the other side, it never comes over again (unless it is changed again.) Just our way of doing this (after three false starts.) Thanks, Carl - Original Message - From: Chris Cowen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 20, 2006 10:34 AM Subject: multiple DB copies with periodic synchronization Hi We have a mySQL database which is being used by a restaurant ordering system, in which many of the tables are being used to store menu item information, pricing etc. The restaurant started off as a single outlet, but is now about to open some more new premises. We would like to have ordering systems in the new premises which can use the information from the database on the original machines. For operational reasons, we want to synchronise the tables that hold all the menu information once a day, and then use the local copies throughout the day. Synchronisation will be over a VPN. We would prefer to do it this way, so that it the VPN goes down (e.g. WAN or phone line is out), the restaurant can still operate using the last synchronised copy of the menu. (as opposed to simply sending the SQL commands over the VPN). There will be one master machine will be where the restaurant managers make changes to their menus, which will get picked up in the morning by the remote machines. The master will also be used to store transactions from all the other branches (where it can be backed up). We'd like to synchronise the menu information in the morning, before the restaurant opens. Then after they close, the transaction tables for the days sales to be synchronised back to the master machine. What is the usual approach in this sort of case? Do we: 1) write our own perl or php script to run the sql commands we need to synchronise? There's not a lot of tables. I don't know much about MySQL commands for synchronisation, or even if there are any. 2) use a third party synchronisation tool ? I looked at SQLyog, but we don't need a GUI. 3) is there another way? - for example a built-in mechanism in mysql to allow duplication with regular synchronisation. Sorry if this is a dumb question - but I'm sure this type of scenario must be fairly common, for example when implementing redundant or distributed databases, so I would be very interested in hearing about people experiences and opinions. Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 3/17/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 3/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update statements problem
Hey guys! thanks a bunch that really fixed my problem. Thanks for your prompt response. cybm On 3/20/06, Johan Höök [EMAIL PROTECTED] wrote: Hi, I think your problem is that OR and AND do not have the same precedence, AND binds tighter. So what you need is probably: (products_model=5217-01 OR products_model=5217-01) AND products_um=CS and the same for PK and EA. The way you have you'll get an update as soon as products_model=5217-01 /Johan cybermalandro cybermalandro wrote: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 2006-03-17
MySQL malloc error on Solaris
Hello, We are running MySQL 4.0.13 on Solaris 8 UltrSPARC with 2048 MB of RAM. This machine has plenty of swap space and has worked fine for over a year now. Out of the blue, it stopped working yesterday and after looking through the error logs, following is what I found: - Error Message - key_buffer_size=16777216 read_buffer_size=131072 Fatal signal 11 while backtracing 060319 16:19:46 mysqld restarted Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line InnoDB: Fatal error: cannot allocate 48 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 3879876 bytes. Operating system errno: 11 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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=16777216 read_buffer_size=131072 060319 16:19:47 mysqld ended - End of Error Message - Would increasing shared memory max, make a difference? Thanks _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
another update error
Ok now I am running this statement UPDATE products INNER JOIN products_ums ON products.products_id = products_ums.products_id SET products_ums.products_ums_price=552 WHERE ( products.products_model=2420-01 OR products.products_model=242001) AND products_ums.products_ums_um=EA; and I am getting a 1064 error I am lost at this point. Your help would be appreciate it. Thanks, Kuai
beginning confusions
I'm just starting with mysql. I've got it going but am running into difficulties working through 2.9.2 of the user's manual. Specifically I'm having problems with where commands on pp 115ff are run from. (I'm running Fedora Core 4 and Ver 8.41 Distrib 4.1.11, for redhat-linux-gnu on i386). I figured out that mysqladmin runs from my [EMAIL PROTECTED] prompt, without the bin/. I also got mysql show to run from there. But then I tried 'mysqlshow mysql' and it wouldn't run. Nor would it run when I logged on as (system) root. (It's terribly confusing to know when 'root' refers to MySQL root and when to the system root.) I get this result: [EMAIL PROTECTED] ~]$ mysqlshow +---+ | Databases | +---+ | test | +---+ [EMAIL PROTECTED] ~]$ mysqlshow mysql mysqlshow: Access denied for user ''@'localhost' to database 'mysql' [EMAIL PROTECTED] ~]$ su - Password: [EMAIL PROTECTED] ~]# mysqlshow mysql mysqlshow: Access denied for user 'root'@'localhost' (using password: NO) What am I doing wrong? EB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errors 1005 and 1025 - but not foreign keys
Ah, it was an orphaned table due to a bad shutdown. Thanks. David On Mar 18, 2006, at 12:50 AM, Heikki Tuuri wrote: David, - Original Message - From: David Felio [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 11:31 PM Subject: Errors 1005 and 1025 - but not foreign keys I got an error 1025 trying to rename an InnoDB table. When I go to look in the database now, that table isn't there even though there is a .ibd file in the mysql data directory with the target name. If I try to create a table with the target name (as an InnoDB table), I get error 1005. I can create it as MyISAM, however. If I try to then convert that MyISAM table to InnoDB, I get the 1025 error. I tried removing the .ibd file from the mysql data dir and that did not help. In googling the error, it seems all solutions revolve around foreign keys, but there are no foreign keys in this table nor are there any foreign keys referencing this table. what is the MySQL version? Please post the error messages verbatim. If mysqld prints something to the .err log, please also post the printout. After the failing operation, run SHOW INNODB STATUS\G and post the latest foreign key error explanation in it if any. The issue may be an orphaned table in ibdata1 which does not have an .frm file: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting- datadict.html Or you may have FOREIGN KEY constraints that you are not aware of. Deleting an .ibd file manually from the database directory never helps because the InnoDB internal data dictionary is in ibdata files. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repairing packed MyISAM tables with no index file (.MYI)
Hello, I have been using myisampack to pack old MyISAM tables to archive huge amounts of data. To save more space I decided to get rid of index (.MYI) files based on the assumption that I can reconstruct those indexes whenever I needed. I've rebuild indexes on plain MyISAM tables with no problem. I always use : repair table TABLENAME USE_FRM; from MySQL console to rebuild index files from scratch. When I try the same on packed MyISAM tables MySQL fails. First it gives me bunch of same type of errors : | test.z_976287758_978107517 | repair | info | Found block that points outside data file at 382300672 | Then when it is finishes complaining about blocks outside data file, it actually deletes actual data file (.MYD) : -rw-r- 1 0 Mar 20 21:58 z_976287758_978107517.MYD -rw-rw 1 1.0K Mar 20 21:59 z_976287758_978107517.MYI -rw-r- 1 8.7K Mar 20 20:15 z_976287758_978107517.frm Typically I would expect USE_FRM to not to touch actual data, but just rebuild index file. When I try the same with myisamchk console utility, it does the same. I use 5.0.18 on RHEL4 and RHEL3. Tested it only on 5.x so far, doesn't work it with 4.x since utils seems like different. Any idea on what is going on? Did I hit to a bug? Thanks. Kayra Otaner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RIGHT JOIN better than INNER JOIN?
I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: beginning confusions
On Mon, 2006-03-20 at 16:53 -0500, Eric Beversluis wrote: I'm just starting with mysql. I've got it going but am running into difficulties working through 2.9.2 of the user's manual. Specifically I'm having problems with where commands on pp 115ff are run from. (I'm running Fedora Core 4 and Ver 8.41 Distrib 4.1.11, for redhat-linux-gnu on i386). I figured out that mysqladmin runs from my [EMAIL PROTECTED] prompt, without the bin/. I also got mysql show to run from there. But then I tried 'mysqlshow mysql' and it wouldn't run. Nor would it run when I logged on as (system) root. (It's terribly confusing to know when 'root' refers to MySQL root and when to the system root.) I get this result: [EMAIL PROTECTED] ~]$ mysqlshow +---+ | Databases | +---+ | test | +---+ [EMAIL PROTECTED] ~]$ mysqlshow mysql mysqlshow: Access denied for user ''@'localhost' to database 'mysql' [EMAIL PROTECTED] ~]$ su - Password: [EMAIL PROTECTED] ~]# mysqlshow mysql mysqlshow: Access denied for user 'root'@'localhost' (using password: NO) What am I doing wrong? EB More Confusions: When I try to follow the manual and enter 'bin/mysqld_safe --user=mysql ' I get this: [EMAIL PROTECTED] ~]$ cd /usr [EMAIL PROTECTED] usr]$ bin/mysqld_safe --user=mysql [1] 10340 [EMAIL PROTECTED] usr]$ cat: /var/run/mysqld/mysqld.pid: Permission denied rm: cannot remove `/var/run/mysqld/mysqld.pid': Permission denied Fatal error: Can't remove the pid file: /var/run/mysqld/mysqld.pid bin/mysqld_safe: line 284: /var/log/mysqld.log: Permission denied Please remove it manually and start bin/mysqld_safe again mysqld daemon not started This command will start the server, but only from system root: 'service mysqld start'. Isn't there a way to start mysql other than as root? Thanks for any help you can give me. EB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
I've always been a believer in avoiding sql procedures, for the main reason that I want to be as database-independent as possible. I know it is less efficient, but being able to switch between MySQL, Postgre, and the new freebies from IBM, Oracle, and Microsoft is a strong advantage from the business perspective (of total cost to the customer, and customer preference). Of course, this is a discussion point. I'd love to hear from the community on their experiences. [EMAIL PROTECTED] wrote: I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
~Mysql cluster info~
Hi, Iam new to clustering in mysql. I went through the reference manual 5.0 and found that the RAM memory requirements for implementing a cluster is almost twice the size of the database. My problem is i have a database which is 55GB. So does it mean that i need to have 110 GB RAM memory ? Can anyone let me know whether it is possible for me to configure a cluster for such a huge database. If yes, how am i suppose to proceed ( regarding memory requirements ). Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with MySQL Migration Toolkit
I have downloaded sucesfully the MySQL Migration Toolkit tool. After start the error with Java appears. I have Java installed on my PC because I work with Eclipse 3.1.1 + PHPEclipse Plugin. The JDK installed is 1.5. So if any can help me ... Regards, -- ReynierPM 4to. Ing. Informática Linux User: #310201 El programador superhéroe aprende de compartir sus conocimientos. Es el referente de sus compañeros. Todo el mundo va a preguntarle y él, secretamente, lo fomenta porque es así como adquiere su legendaria sabiduría: escuchando ayudando a los demás... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
The only relational databases I've ever used to any significant extent are MySQL and DB2. I've used DB2 for a lot longer than MySQL and on most of the platforms on which it runs over various versions. As far as I'm concerned, the answer to your questions, at least as far as DB2 goes, is: it depends. It depends on a host of factors. In no particular order, these factors include: - which version of DB2 you are using - what hardware you are running on - how you write your SQL - whether the data is properly clustered - whether the tables and indexes have been reorganized in a timely fashion - etc. etc. You simply can't make a categorical statement that a right join will perform better than an inner join - or vice versa - in every case in DB2. All versions of DB2 use a cost-based optimizer that makes great efforts to give the optimum access path (and therefore optimum performance) for each query. A lot of very smart people have worked on the design of that optimizer over the years - I've met some of them - but, as good as the DB2 optimizer is, it can still make inappropriate decisions. This happens when you don't do routine maintenance like reorganizing tables and the RUNSTATS utility but the way you write (or mis-write) your SQL can also affect your access path and therefore your performance. This unpredictability may sound like a bad thing but it is often a very good thing since the optimizer has many tricks and shortcuts. It will often rewrite a poorly-written query to improve its performance. You may be able to find more categorical answers for the other major databases, like Oracle, since they tend to use different optimizer designs. The only way to be really sure though is to do a proper benchmark for all the platforms and configurations that interest you. -- Rhino - Original Message - From: Robert DiFalco [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 20, 2006 7:11 PM Subject: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL malloc error on Solaris
Ubaidul, - Original Message - From: Ubaidul Khan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 20, 2006 8:31 PM Subject: MySQL malloc error on Solaris Hello, We are running MySQL 4.0.13 on Solaris 8 UltrSPARC with 2048 MB of RAM. This machine has plenty of swap space and has worked fine for over a year now. Out of the blue, it stopped working yesterday and after looking through the error logs, following is what I found: - Error Message - key_buffer_size=16777216 read_buffer_size=131072 Fatal signal 11 while backtracing 060319 16:19:46 mysqld restarted Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line InnoDB: Fatal error: cannot allocate 48 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 3879876 bytes. Operating system errno: 11 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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=16777216 read_buffer_size=131072 060319 16:19:47 mysqld ended - End of Error Message - Would increasing shared memory max, make a difference? what is your my.cnf like? InnoDB has only allocated 4 MB of memory, so InnoDB can hardly be the culprit. Do you run a query that uses huge amounts of memory? Thanks Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to shutdown mysql from Java
Hi, my application starts mysqld (via Runtime.getRuntime().exec) and I would like to stop it also from Java (because of technical reasons I cannot use mysqladmin). What is the best way stop shutdown mysqld? 1. FLUSH TABLES 2. Process.destroy() 3. kill PID auf Unix What do you think? Zsolt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
