Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Hello Mogens, On 8/18/2018 2:32 PM, Mogens Melander wrote: Guys, I think I remember this from way back. You could ask for a lock, and get an OK if it is safe. Something like, if there is pending transactions, on your target tables, you would get a NO. But then again. I could be wrong, and Shawn is the authority on this. Your request for a lock would have waited until all existing readers or writers (depending on the type of lock you asked for) had finished using the tables you wanted to lock. By extension, that means that any transactions active against the tables you wanted to lock would have also needed to have committed or rolled back before your request would have been granted. Any new actions against the table would have been queued up behind your LOCK request. This has confused more than one DBA as they didn't realize that the LOCK was going to be such a tight bottleneck. These kinds of whole table locks live above the blocking/locking coordination of the individual storage engines or the transaction control code. They are managed in the "server layer" of our code. This separation of scope is one reason why blending transactional and non-transactional tables in the same data management process is generally frowned on. Either be all-transactional (InnoDB) or not. The behavior will be easier to predict allowing your developers to use either the transaction control commands (BEGIN/COMMIT/ROLLBACK/... ) or the LOCK commands with confidence. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. === original thread === On 2018-08-18 23:59, shawn l.green wrote: Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net wrote: Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks Normally, the list is more responsive than this. This is a pretty easy question and someone usually handles those before I need to step in as a backstop. The key why you cannot execute a LOCK TABLE command within a stored program is here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html ### LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. ### Stored programs execute under the scope of the transaction in which they are started. That determines which sets of rows are "visible" to the routine and sets boundaries on what may be committed or rolled back should the need arise. (a simple example) * your session: START TRANSACTION * your session: ...other data activity ... * your session (INSERT ... ) * causes an INSERT trigger to fire * which calls a stored procedure If that stored procedure or that trigger called a LOCK TABLE command, it would forcibly COMMIT the existing transaction you had been working within until that moment. Your half-completed work would have become fully committed even if a later step had needed you to issue a ROLLBACK command. Note, even if you are not in a multi-statement transaction that any stored programs called by or executed within the scope of your user command are part of that little mini (auto-committed) transaction. Does that help? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Guys, I think I remember this from way back. You could ask for a lock, and get an OK if it is safe. Something like, if there is pending transactions, on your target tables, you would get a NO. But then again. I could be wrong, and Shawn is the authority on this. On 2018-08-18 23:59, shawn l.green wrote: Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net wrote: Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks Normally, the list is more responsive than this. This is a pretty easy question and someone usually handles those before I need to step in as a backstop. The key why you cannot execute a LOCK TABLE command within a stored program is here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html ### LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. ### Stored programs execute under the scope of the transaction in which they are started. That determines which sets of rows are "visible" to the routine and sets boundaries on what may be committed or rolled back should the need arise. (a simple example) * your session: START TRANSACTION * your session: ...other data activity ... * your session (INSERT ... ) * causes an INSERT trigger to fire * which calls a stored procedure If that stored procedure or that trigger called a LOCK TABLE command, it would forcibly COMMIT the existing transaction you had been working within until that moment. Your half-completed work would have become fully committed even if a later step had needed you to issue a ROLLBACK command. Note, even if you are not in a multi-statement transaction that any stored programs called by or executed within the scope of your user command are part of that little mini (auto-committed) transaction. Does that help? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net wrote: Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks Normally, the list is more responsive than this. This is a pretty easy question and someone usually handles those before I need to step in as a backstop. The key why you cannot execute a LOCK TABLE command within a stored program is here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html ### LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. ### Stored programs execute under the scope of the transaction in which they are started. That determines which sets of rows are "visible" to the routine and sets boundaries on what may be committed or rolled back should the need arise. (a simple example) * your session: START TRANSACTION * your session: ...other data activity ... * your session (INSERT ... ) * causes an INSERT trigger to fire * which calls a stored procedure If that stored procedure or that trigger called a LOCK TABLE command, it would forcibly COMMIT the existing transaction you had been working within until that moment. Your half-completed work would have become fully committed even if a later step had needed you to issue a ROLLBACK command. Note, even if you are not in a multi-statement transaction that any stored programs called by or executed within the scope of your user command are part of that little mini (auto-committed) transaction. Does that help? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
what is the rationale for not allowing LOCK TABLES in a stored procedure
Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Mysqldump routines dump, problem with lock tables.
Hi All. I use: # rpm -qa | grep -i percona-server-server Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 My system: # uname -a;cat /etc/redhat-release Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP Wed Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Red Hat Enterprise Linux Server release 6.3 (Santiago) I have a backup script which at some point calls: mysqldump --default-character-set=utf8 --routines --no-data --no-create-info --skip-triggers -S /mysql/database.sock -u backup -pxxx database and I have error: mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using password: YES) when using LOCK TABLES So I thinke that mysqldump locks the table (--add-locks) by default. But for this user: mysql show grants for yyy@'zzz'; ++ | Grants for backup@localhost | ++ | GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz' IDENTIFIED BY PASSWORD ... | | ++ 2 rows in set (0.00 sec) So why is this error showing? When I add --single-transaction to mysqldump everything is ok. But I would like to have this table locked because: mysql SELECT ENGINE - FROM information_schema.TABLES - WHERE TABLE_SCHEMA = 'information_schema' - AND TABLE_NAME = 'routines'; ++ | ENGINE | ++ | MyISAM | ++ so information_schema.tables is myisam. So why do I get the error about LOCK TABLES? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Mysqldump routines dump, problem with lock tables.
Do not try to dump or reload information_schema. It is derived meta information, not real tables. -Original Message- From: Rafał Radecki [mailto:radecki.ra...@gmail.com] Sent: Monday, February 04, 2013 12:17 AM To: mysql@lists.mysql.com Subject: Mysqldump routines dump, problem with lock tables. Hi All. I use: # rpm -qa | grep -i percona-server-server Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 My system: # uname -a;cat /etc/redhat-release Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP Wed Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Red Hat Enterprise Linux Server release 6.3 (Santiago) I have a backup script which at some point calls: mysqldump --default-character-set=utf8 --routines --no-data --no- create-info --skip-triggers -S /mysql/database.sock -u backup -pxxx database and I have error: mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using password: YES) when using LOCK TABLES So I thinke that mysqldump locks the table (--add-locks) by default. But for this user: mysql show grants for yyy@'zzz'; +-- --- ---+ | Grants for backup@localhost | +-- --- ---+ | GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz' IDENTIFIED BY PASSWORD ... | | +-- --- ---+ 2 rows in set (0.00 sec) So why is this error showing? When I add --single-transaction to mysqldump everything is ok. But I would like to have this table locked because: mysql SELECT ENGINE - FROM information_schema.TABLES - WHERE TABLE_SCHEMA = 'information_schema' - AND TABLE_NAME = 'routines'; ++ | ENGINE | ++ | MyISAM | ++ so information_schema.tables is myisam. So why do I get the error about LOCK TABLES? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slower performance with LOCK TABLES
- Original Message - From: Hank hes...@gmail.com Just an update. Using the load index into cache statement for the 200 million row indexed source table, my correlated update statement ran in 1 hour, 45 minutes to update 144 million rows. A 50% increase in performance! Good to hear :-) Ignore leaves might be a nice trick in this situation, actually. I never thought of it, but the leaves contain the record pointers, which you don't need because you have a covering index. Nice thinking :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Thanks dear everyone for sharing your views! Let me try the workarounds and keep you posted. Best Rgs, Shafi AHMED _ From: Suresh Kuna [mailto:sureshkumar...@gmail.com] Sent: Saturday, September 24, 2011 8:56 PM To: Prabhat Kumar Cc: Dan Nelson; Shafi AHMED; mysql@lists.mysql.com Subject: Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Hello Shafi, Adding to Prabhat alternatives, you can use --force to the mysqldump command to ignore the errors and continue taking backup. Regarding the error, we need to check whether the table is present or not and the engine type specifically. Thanks Suresh Kuna On Sat, Sep 24, 2011 at 3:31 AM, Prabhat Kumar aim.prab...@gmail.com wrote: correct. mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened. Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used. or try with add --single_transaction to your mysqldump command On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Thanks Suresh Kuna MySQL DBA Get your world in your inbox! Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id! Log on to http://www.sify.com ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Technologies Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at ad...@sifycorp.com
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Hello Shafi, Adding to Prabhat alternatives, you can use --force to the mysqldump command to ignore the errors and continue taking backup. Regarding the error, we need to check whether the table is present or not and the engine type specifically. Thanks Suresh Kuna On Sat, Sep 24, 2011 at 3:31 AM, Prabhat Kumar aim.prab...@gmail.comwrote: correct. mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened. Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used. or try with add --single_transaction to your mysqldump command On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Thanks Suresh Kuna MySQL DBA
Re: Slower performance with LOCK TABLES
- Original Message - From: Hank hes...@gmail.com (please read my ORIGINAL post with all this information). Welcome on the Internet, where everyone will tell you everything you need to know, except for what you want to know :-) I am trying to find a logical or reasonable explanation WHY this would be the case, despite the fact that the documentation states otherwise (see: Right here: http://dev.mysql.com/doc/refman/5.5/en/lock-tables-restrictions.html) I believe you're misinterpreting that, as is the author from the blog you originally referenced. What it says, is If you are going to run many operations. You are updating many rows, but you are only doing ONE operation: a single update statement. While this explains why you're not seeing benefit, I have to admit that I'm at a loss, too, as to why you are experiencing an actual slowdown - the update statement will lock the tables, too, so it shouldn't really make any difference at all. But if seeing some SQL will make you happy, here is just one example: UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key; See, this is why people ask to see your queries. You never mentioned you were doing a join in the update :-) I'll ignore the join condition in the where clause, as it makes little difference in this case; but I do note that you're using a straight join. Is the optimizer really reading the tables in the wrong order, or is it just something you picked up off a blog without realising the impact? Source is indexed by key+seq (key is primary key, but seq is included as a covering index). Good practice, that should prevent source from being read from disk, if your index is fully in the cache. Do you have an index on dest.key, too? That might help performance as well if it fits in memory, too, because you'll only need disk access for flushing writes, then. This query takes about 3.5 hours when I don't use LOCK TABLES, and over 4 hours when I do use LOCK TABLES. Most peculiar. Is the difference in performance consistent in repeated executions? And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. That's good, as it will give you the worst-case scenario. However, since such an update is going to wreck your index cache anyway, you may just as well preload the appropriate indices into it beforehand, if the cache is sized big enough to hold them. That might give a minor performance boost, too, as the server won't have to go to disk every so often to fetch index blocks. See http://dev.mysql.com/doc/refman/5.0/en/index-preloading.html for that. | key_buffer_size | 402653184 | 400MB of key buffer, that's not bad at all. Do a /show table status/ in your database, and sum the index sizes. If your key buffer is larger than this (and why not scale it for growth a bit?) all your indices will fit, which will save on disk access for index lookups *and* for index-covered queries. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Folks I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? Best Rgs, Shafi AHMED
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Someone seems to have deleted that file, which contains the description of the corresponding table. Recreate the exact same table (EXACT, including keys, indices, datatypes, encoding, the lot) and copy that tables's .frm file to replace the lost one. Then pray to the elder gods and restart your mysqld to see if it works. - Original Message - From: Shafi AHMED shafi.ah...@sifycorp.com To: mysql@lists.mysql.com Sent: Friday, 23 September, 2011 1:42:26 PM Subject: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Folks I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? Best Rgs, Shafi AHMED -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Slower performance with LOCK TABLES
Hello Johan, Thanks for your comprehensive reply. I'll try to answer each of your questions below. -Hank But if seeing some SQL will make you happy, here is just one example: UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key; See, this is why people ask to see your queries. You never mentioned you were doing a join in the update :-) I'll ignore the join condition in the where clause, as it makes little difference in this case; but I do note that you're using a straight join. Is the optimizer really reading the tables in the wrong order, or is it just something you picked up off a blog without realising the impact? Yes, I'm using a straight join intentionally. I have 144 million unindexed rows in dest. I want Mysql to start with those rows sequentially, then join them to the matching record in source using its index (244 million rows). If I don't do that, mysql tries to use the indexed table first, causing a full table scans on dest . So with straight_join, it does it in proper order. During experimentation with different joins, a regular join would run for days. A straight join runs for 3-4 hours. Source is indexed by key+seq (key is primary key, but seq is included as a covering index). Good practice, that should prevent source from being read from disk, if your index is fully in the cache. With 244 million records in the source table, I'm not sure that would fit in the cache. Do you have an index on dest.key, too? That might help performance as well if it fits in memory, too, because you'll only need disk access for flushing writes, then. I do not have an index on dest.key, also intentionally, for two reasons. First, updating 144 million records in place is slow enough, but trying to update 144 million records AND the index on that field would absolutely kill the performance of the update. Once the update is complete, I re-create the index with a sort using myisamchk. Second, the starting value of dest.key for all 144 million records is 0 so an index on that field wouldn't really help, I think. This query takes about 3.5 hours when I don't use LOCK TABLES, and over 4 hours when I do use LOCK TABLES. Most peculiar. Is the difference in performance consistent in repeated executions? yes, I've done these tests about a dozen times now, and while not exactly scientific, the results are that LOCK TABLES always results in longer running times. Not just for this query, but other full table update/select/delete/insert queries. Not more than twice as long, but easily a 10% to 25% increase. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. That's good, as it will give you the worst-case scenario. However, since such an update is going to wreck your index cache anyway, you may just as well preload the appropriate indices into it beforehand, if the cache is sized big enough to hold them. That might give a minor performance boost, too, as the server won't have to go to disk every so often to fetch index blocks. See http://dev.mysql.com/doc/refman/5.0/en/index-preloading.html for that. I'll look into that, but the index is huge. Ok, I just preloaded the source index using LOAD INDEX INTO CACHE source IGNORE LEAVES;.. it took two minutes/15 seconds to pre-load the index. I then ran two small tests on smaller tables using the same update statement, and they both yielded a massive increase in update speed. I'll have to rebuild the large dest table again to try it on the biggest UPDATE, but wow, if this is any indication, this was a great suggestion. I'll report back on the results later today. Thank you! | key_buffer_size | 402653184 | 400MB of key buffer, that's not bad at all. Do a /show table status/ in your database, and sum the index sizes. If your key buffer is larger than this (and why not scale it for growth a bit?) all your indices will fit, which will save on disk access for index lookups *and* for index-covered queries. The index length for source is 5,889,037,312. Thanks again for your assistance. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slower performance with LOCK TABLES
Hello Johan, Just an update. Using the load index into cache statement for the 200 million row indexed source table, my correlated update statement ran in 1 hour, 45 minutes to update 144 million rows. A 50% increase in performance! Thank you very much, -Hank On Fri, Sep 23, 2011 at 6:56 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Hank hes...@gmail.com (please read my ORIGINAL post with all this information). Welcome on the Internet, where everyone will tell you everything you need to know, except for what you want to know :-) I am trying to find a logical or reasonable explanation WHY this would be the case, despite the fact that the documentation states otherwise (see: Right here: http://dev.mysql.com/doc/refman/5.5/en/lock-tables-restrictions.html) I believe you're misinterpreting that, as is the author from the blog you originally referenced. What it says, is If you are going to run many operations. You are updating many rows, but you are only doing ONE operation: a single update statement. While this explains why you're not seeing benefit, I have to admit that I'm at a loss, too, as to why you are experiencing an actual slowdown - the update statement will lock the tables, too, so it shouldn't really make any difference at all. But if seeing some SQL will make you happy, here is just one example: UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key; See, this is why people ask to see your queries. You never mentioned you were doing a join in the update :-) I'll ignore the join condition in the where clause, as it makes little difference in this case; but I do note that you're using a straight join. Is the optimizer really reading the tables in the wrong order, or is it just something you picked up off a blog without realising the impact? Source is indexed by key+seq (key is primary key, but seq is included as a covering index). Good practice, that should prevent source from being read from disk, if your index is fully in the cache. Do you have an index on dest.key, too? That might help performance as well if it fits in memory, too, because you'll only need disk access for flushing writes, then. This query takes about 3.5 hours when I don't use LOCK TABLES, and over 4 hours when I do use LOCK TABLES. Most peculiar. Is the difference in performance consistent in repeated executions? And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. That's good, as it will give you the worst-case scenario. However, since such an update is going to wreck your index cache anyway, you may just as well preload the appropriate indices into it beforehand, if the cache is sized big enough to hold them. That might give a minor performance boost, too, as the server won't have to go to disk every so often to fetch index blocks. See http://dev.mysql.com/doc/refman/5.0/en/index-preloading.html for that. | key_buffer_size | 402653184 | 400MB of key buffer, that's not bad at all. Do a /show table status/ in your database, and sum the index sizes. If your key buffer is larger than this (and why not scale it for growth a bit?) all your indices will fit, which will save on disk access for index lookups *and* for index-covered queries. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slower performance with LOCK TABLES
2011/09/23 12:56 +0200, Johan De Meersman What it says, is If you are going to run many operations. You are updating many rows, but you are only doing ONE operation: a single update statement. For what it's worth, the downloading HTML help claims this only for MyISAM tables, because between LOCK TABLES and UNLOCK TABLES there is no key-cache flushing. InnoDB is not mentioned. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
correct. mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened. Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used. or try with add --single_transaction to your mysqldump command On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Slower performance with LOCK TABLES
Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.netwrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
Hi, Why dont u use a stored proc to update rows ,where u commit for every 1k or 10k rows. This will be much faster than ur individual update stmt. regards anandkl On Thu, Sep 22, 2011 at 8:24 PM, Hank hes...@gmail.com wrote: That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
Actually, that would be orders of magnitude slower. I'm using MYISAM tables, so there's no commit. On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar anan...@gmail.com wrote: Hi, Why dont u use a stored proc to update rows ,where u commit for every 1k or 10k rows. This will be much faster than ur individual update stmt. regards anandkl On Thu, Sep 22, 2011 at 8:24 PM, Hank hes...@gmail.com wrote: That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
No, Use a cursor(select column_name to be used in where condition of update stmt), loop through it for each update. regards anandkl On Thu, Sep 22, 2011 at 11:36 PM, Hank hes...@gmail.com wrote: Actually, that would be orders of magnitude slower. I'm using MYISAM tables, so there's no commit. On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar anan...@gmail.com wrote: Hi, Why dont u use a stored proc to update rows ,where u commit for every 1k or 10k rows. This will be much faster than ur individual update stmt. regards anandkl On Thu, Sep 22, 2011 at 8:24 PM, Hank hes...@gmail.com wrote: That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
Sorry, but you do not understand my original issue or question. -Hank On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar anan...@gmail.com wrote: No, Use a cursor(select column_name to be used in where condition of update stmt), loop through it for each update. regards anandkl On Thu, Sep 22, 2011 at 11:36 PM, Hank hes...@gmail.com wrote: Actually, that would be orders of magnitude slower. I'm using MYISAM tables, so there's no commit. On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar anan...@gmail.com wrote: Hi, Why dont u use a stored proc to update rows ,where u commit for every 1k or 10k rows. This will be much faster than ur individual update stmt. regards anandkl On Thu, Sep 22, 2011 at 8:24 PM, Hank hes...@gmail.com wrote: That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
May be if u can let the audience know a sip-net of ur sql, some can help u On Thu, Sep 22, 2011 at 11:43 PM, Hank hes...@gmail.com wrote: Sorry, but you do not understand my original issue or question. -Hank On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar anan...@gmail.com wrote: No, Use a cursor(select column_name to be used in where condition of update stmt), loop through it for each update. regards anandkl On Thu, Sep 22, 2011 at 11:36 PM, Hank hes...@gmail.com wrote: Actually, that would be orders of magnitude slower. I'm using MYISAM tables, so there's no commit. On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar anan...@gmail.com wrote: Hi, Why dont u use a stored proc to update rows ,where u commit for every 1k or 10k rows. This will be much faster than ur individual update stmt. regards anandkl On Thu, Sep 22, 2011 at 8:24 PM, Hank hes...@gmail.com wrote: That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
Like I said, the problem is not just one particular SQL statement. It is several dozen statements operating on tables with several hundred million records. The problem is that I am finding that when I use LOCK TABLES, these queries run slower (please read my ORIGINAL post with all this information). I am trying to find a logical or reasonable explanation WHY this would be the case, despite the fact that the documentation states otherwise (see: Right here: http://dev.mysql.com/doc/refman/5.5/en/lock-tables-restrictions.html ) But if seeing some SQL will make you happy, here is just one example: UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key; for 140 million records in dest and 220 million records in source. Source is indexed by key+seq (key is primary key, but seq is included as a covering index). There is no index on dest.seq -- that index is built once the update is complete. This query takes about 3.5 hours when I don't use LOCK TABLES, and over 4 hours when I do use LOCK TABLES. -Hank On Thu, Sep 22, 2011 at 2:18 PM, Ananda Kumar anan...@gmail.com wrote: May be if u can let the audience know a sip-net of ur sql, some can help u On Thu, Sep 22, 2011 at 11:43 PM, Hank hes...@gmail.com wrote: Sorry, but you do not understand my original issue or question. -Hank On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar anan...@gmail.com wrote: No, Use a cursor(select column_name to be used in where condition of update stmt), loop through it for each update. regards anandkl On Thu, Sep 22, 2011 at 11:36 PM, Hank hes...@gmail.com wrote: Actually, that would be orders of magnitude slower. I'm using MYISAM tables, so there's no commit. On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar anan...@gmail.comwrote: Hi, Why dont u use a stored proc to update rows ,where u commit for every 1k or 10k rows. This will be much faster than ur individual update stmt. regards anandkl On Thu, Sep 22, 2011 at 8:24 PM, Hank hes...@gmail.com wrote: That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect
Re: Slower performance with LOCK TABLES
do u have index on dest,key On Fri, Sep 23, 2011 at 12:21 AM, Hank hes...@gmail.com wrote: Like I said, the problem is not just one particular SQL statement. It is several dozen statements operating on tables with several hundred million records. The problem is that I am finding that when I use LOCK TABLES, these queries run slower (please read my ORIGINAL post with all this information). I am trying to find a logical or reasonable explanation WHY this would be the case, despite the fact that the documentation states otherwise (see: Right here: http://dev.mysql.com/doc/refman/5.5/en/lock-tables-restrictions.html ) But if seeing some SQL will make you happy, here is just one example: UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key; for 140 million records in dest and 220 million records in source. Source is indexed by key+seq (key is primary key, but seq is included as a covering index). There is no index on dest.seq -- that index is built once the update is complete. This query takes about 3.5 hours when I don't use LOCK TABLES, and over 4 hours when I do use LOCK TABLES. -Hank On Thu, Sep 22, 2011 at 2:18 PM, Ananda Kumar anan...@gmail.com wrote: May be if u can let the audience know a sip-net of ur sql, some can help u On Thu, Sep 22, 2011 at 11:43 PM, Hank hes...@gmail.com wrote: Sorry, but you do not understand my original issue or question. -Hank On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar anan...@gmail.com wrote: No, Use a cursor(select column_name to be used in where condition of update stmt), loop through it for each update. regards anandkl On Thu, Sep 22, 2011 at 11:36 PM, Hank hes...@gmail.com wrote: Actually, that would be orders of magnitude slower. I'm using MYISAM tables, so there's no commit. On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar anan...@gmail.comwrote: Hi, Why dont u use a stored proc to update rows ,where u commit for every 1k or 10k rows. This will be much faster than ur individual update stmt. regards anandkl On Thu, Sep 22, 2011 at 8:24 PM, Hank hes...@gmail.com wrote: That is what I'm doing. I'm doing a correlated update on 200 million records. One UPDATE statement. Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm trying to figure out why, despite what the documentation says, using LOCK TABLES hinders performance for large update statements on MYISAM tables when it is supposed to increase performance on exactly the type of queries I am performing. If you can't help answer *that* question, please stop lecturing me on the reasons not to use LOCK TABLES. Thanks. -Hank On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis antonycur...@verizon.netwrote: Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates
Re: Slower performance with LOCK TABLES
On Thu, Sep 22, 2011 at 3:11 PM, Hassan Schroeder hassan.schroe...@gmail.com wrote: On Thu, Sep 22, 2011 at 11:51 AM, Hank hes...@gmail.com wrote: Like I said, the problem is not just one particular SQL statement. It is several dozen statements operating on tables with several hundred million records. The problem is that I am finding that when I use LOCK TABLES, these queries run slower (please read my ORIGINAL post with all this information). Wandering out my area of expertise here :-) but have you done any key cache tuning or are you running with the defaults? mysql show variables like 'key_%'; Also, what is the exact LOCK TABLE statement you're using? No, I haven't done any key cache tuning, as that's out of my area of expertise as well! I have 8GB of memory on this box, and I can go up to 12GB. Here are the variables: | key_buffer_size | 402653184 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | The lock statement is quite simple: LOCK TABLE dest d write, source s read; thanks.
Slower performance with LOCK TABLES
According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
I haven't bothered to look for the bug, but it seems to me to be quite reasonable default behaviour to lock the whole lot when you're dumping transactional tables - it ensures you dump all tables from the same consistent view. I would rather take this up with the ZRM people - it should just work. 3.3 came out last week, you may want to have a look at wether it's already been adressed there. Harald's solution is, as usual, technically superior; but he keeps having trouble understanding people not wanting to change their entire setup because their solution isn't optimal :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
On Mon, 06 Jun 2011 12:44 +0200, Johan De Meersman vegiv...@tuxera.be wrote: I haven't bothered to look for the bug, but it seems to me to be quite reasonable default behaviour to lock the whole lot when you're dumping transactional tables - it ensures you dump all tables from the same consistent view. thanks for the comment. I would rather take this up with the ZRM people - it should just work. this, http://bugs.mysql.com/bug.php?id=61414 suggests the same. so, i've already started that discussion as well, http://forums.zmanda.com/showthread.php?t=3703 Excluding 'performance_schema' appears to eliminate the error. And it seems does NOT cause a reliability-of-the-backup problem. 3.3 came out last week, you may want to have a look at wether it's already been adressed there. I believe that's an Amanda 3.3 release you're referring to. ZRM is still at 2.2, http://www.zmanda.com/download-zrm.php ZRM for MySQL, Version 2.2 is the Latest Stable Release and, i've MySQL-zrm-2.2.0-1.noarch installed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
- Original Message - From: ag...@airpost.net Excluding 'performance_schema' appears to eliminate the error. And it seems does NOT cause a reliability-of-the-backup problem. Hah, no, backing that up is utterly pointless. Never noticed it doing that. It's basically a virtual schema that contains realtime information about the database, intended to replace a lot of show tables parsing and similar mayhem with simple select statements. I believe that's an Amanda 3.3 release you're referring to. ZRM is still at 2.2, No, I do mean 3.3. Apparently the free downloadable version is quite a bit behind the commercial one. Maybe that's why I never noticed it backing up the performance schema, too :-) They're not really that expensive, either, it might well be worth it to grab licenses and support. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
On Mon, 06 Jun 2011 18:54 +0200, Johan De Meersman vegiv...@tuxera.be wrote: Excluding 'performance_schema' appears to eliminate the error. And it seems does NOT cause a reliability-of-the-backup problem. Hah, no, backing that up is utterly pointless. that's a useful/final confirmation. thx. No, I do mean 3.3. Apparently the free downloadable version is quite a bit behind the commercial one. Maybe that's why I never noticed it backing up the performance schema, too :-) i didn't catch that ZRM's commercial version was at 3.3! thx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
i use ZRM to do backups of my databases. with v5.1.x, this works fine, mysql-zrm-scheduler --now --backup-set manual --backup-level 0 to execute a manual backup. i recently upgraded from v5.1.x - v5.5.12, mysqladmin -V mysqladmin Ver 8.42 Distrib 5.5.12, for Linux on i686 now, at exec of that backup cmd, i see an ERROR @ console, ... manual:backup:INFO: PHASE START: Creating raw backup manual:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/socket-copy.pl --mysqlhotcopy=/usr/bin --host=localhost --port=3306 --socket=/var/cache/mysql/mysql.sock --quiet mysql /var/mysql-bkup/manual/20110605131003 /var/cache/tmp/bZvaQFwQY2 21 manual:backup:INFO: raw-databases=mysql manual:backup:INFO: PHASE END: Creating raw backup manual:backup:INFO: PHASE START: Creating logical backup manual:backup:WARNING: The database(s) drupal6 performance_schema will be backed up in logical mode since they contain tables that use a transactional engine. manual:backup:INFO: Command used for logical backup is /usr/bin/mysqldump --opt --extended-insert --create-options --default-character-set=utf8 --routines --host=localhost --port=3306 --socket=/var/cache/mysql/mysql.sock --databases drupal6 performance_schema /var/mysql-bkup/manual/20110605131003/backup.sql mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES -- manual:backup:ERROR: mysqldump did not succeed. Command used is /usr/bin/mysqldump --opt --extended-insert --create-options --default-character-set=utf8 --routines --host=localhost --port=3306 --socket=/var/cache/mysql/mysql.sock --databases drupal6 performance_schema /var/mysql-bkup/manual/20110605131003/backup.sqlmanual:backup:INFO: PHASE START: Cleanup manual:backup:INFO: backup-status=Backup failed ... reading up on the error at, http://bugs.mysql.com/bug.php?id=33762 http://bugs.mysql.com/bug.php?id=49633 it looks to do with mysqldump itself. i modified in /etc/my.cnf ... [mysqldump] quick quote-names max_allowed_packet = 8M + skip-lock-tables ... but that doesn't seem to make any difference. something's changed between 5.1.x 5.5.x. what do i need to modify to get past this error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
have you checked you permissions-table if all privileges are active for root and have you started ymsql_upgrade after all updates? Am 05.06.2011 22:20, schrieb ag...@airpost.net: i use ZRM to do backups of my databases. with v5.1.x, this works fine, mysql-zrm-scheduler --now --backup-set manual --backup-level 0 to execute a manual backup. i recently upgraded from v5.1.x - v5.5.12, mysqladmin -V mysqladmin Ver 8.42 Distrib 5.5.12, for Linux on i686 now, at exec of that backup cmd, i see an ERROR @ console, ... manual:backup:INFO: PHASE START: Creating raw backup manual:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/socket-copy.pl --mysqlhotcopy=/usr/bin --host=localhost --port=3306 --socket=/var/cache/mysql/mysql.sock --quiet mysql /var/mysql-bkup/manual/20110605131003 /var/cache/tmp/bZvaQFwQY2 21 manual:backup:INFO: raw-databases=mysql manual:backup:INFO: PHASE END: Creating raw backup manual:backup:INFO: PHASE START: Creating logical backup manual:backup:WARNING: The database(s) drupal6 performance_schema will be backed up in logical mode since they contain tables that use a transactional engine. manual:backup:INFO: Command used for logical backup is /usr/bin/mysqldump --opt --extended-insert --create-options --default-character-set=utf8 --routines --host=localhost --port=3306 --socket=/var/cache/mysql/mysql.sock --databases drupal6 performance_schema /var/mysql-bkup/manual/20110605131003/backup.sql mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES -- manual:backup:ERROR: mysqldump did not succeed. Command used is /usr/bin/mysqldump --opt --extended-insert --create-options --default-character-set=utf8 --routines --host=localhost --port=3306 --socket=/var/cache/mysql/mysql.sock --databases drupal6 performance_schema /var/mysql-bkup/manual/20110605131003/backup.sqlmanual:backup:INFO: PHASE START: Cleanup manual:backup:INFO: backup-status=Backup failed ... reading up on the error at, http://bugs.mysql.com/bug.php?id=33762 http://bugs.mysql.com/bug.php?id=49633 it looks to do with mysqldump itself. i modified in /etc/my.cnf ... [mysqldump] quick quote-names max_allowed_packet = 8M + skip-lock-tables ... but that doesn't seem to make any difference. something's changed between 5.1.x 5.5.x. what do i need to modify to get past this error? -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
hi, On Sun, 05 Jun 2011 22:24 +0200, Reindl Harald h.rei...@thelounge.net wrote: have you checked you permissions-table if all privileges are active for root i've got, mysql show grants for 'root'@'localhost'; ++ | Grants for root@localhost | ++ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*3...4' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | ++ and, mysql show grants for 'drupal_admin'@'localhost'; +--+ | Grants for drupal_admin@localhost | +--+ | GRANT USAGE ON *.* TO 'drupal_admin'@'localhost' IDENTIFIED BY PASSWORD '*D...D' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `drupal6`.* TO 'drupal_admin'@'localhost' | +--+ 17 rows in set (0.00 sec) are these sufficient? these permissions worked fine as far as i could tell for the v5.1.x install i had. and have you started ymsql_upgrade after all updates? yes, i'd already executed 'mysql_upgrade', following the instructions here: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html checking, mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck This installation of MySQL is already upgraded to 5.5.12, use --force if you still need to run mysql_upgrade -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
the grant statements does nobody interest maybe use phpmyadmin for a clearer display mysql select * from mysql.user where user='root' limit 1; +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | localhost | root | * | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y| Y | Y | Y | Y| Y | Y| Y | || | | 0 | 0 | 0 |0 || | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ 1 row in set (0.00 sec) Am 05.06.2011 23:05, schrieb ag...@airpost.net: hi, On Sun, 05 Jun 2011 22:24 +0200, Reindl Harald h.rei...@thelounge.net wrote: have you checked you permissions-table if all privileges are active for root i've got, mysql show grants for 'root'@'localhost'; ++ | Grants for root@localhost | ++ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*3...4' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | ++ signature.asc Description: OpenPGP digital signature
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
fwiw, others are seeing this. e.g., in addition to the two bugs i'd already referenced, http://www.directadmin.com/forum/showthread.php?p=202053 and one http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with-mysqldump-SELECT-LOCK-TABL-command claims a solution Add --skip-add-locks to your mysqldump command which, having added as i mentioned above, to the [mysqldump] section of /etc/my.cnf, does NOT make a difference for me. On Sun, 05 Jun 2011 23:19 +0200, Reindl Harald h.rei...@thelounge.net wrote: the grant statements does nobody interest mysql select * from mysql.user where user='root' limit 1; and, my result for your cmd, mysql select * from mysql.user where user='root' limit 1; +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | localhost | root | *3..4 | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y| Y| Y | Y | Y | Y| Y | Y | Y | || | | 0 | 0 | 0 | 0 || NULL | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ 1 row in set (0.06 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
hm - bad i would use a replication slave and stop him for consistent backups because dumb locks are not really a good solution independent if this works normally Am 05.06.2011 23:26, schrieb ag...@airpost.net: fwiw, others are seeing this. e.g., in addition to the two bugs i'd already referenced, http://www.directadmin.com/forum/showthread.php?p=202053 and one http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with-mysqldump-SELECT-LOCK-TABL-command claims a solution Add --skip-add-locks to your mysqldump command which, having added as i mentioned above, to the [mysqldump] section of /etc/my.cnf, does NOT make a difference for me. On Sun, 05 Jun 2011 23:19 +0200, Reindl Harald h.rei...@thelounge.net wrote: the grant statements does nobody interest mysql select * from mysql.user where user='root' limit 1; and, my result for your cmd, mysql select * from mysql.user where user='root' limit 1; +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | localhost | root | *3..4 | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y| Y| Y | Y | Y | Y| Y | Y | Y | || | | 0 | 0 | 0 | 0 || NULL | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ 1 row in set (0.06 sec) -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
BTW WHY is everybody ansering to the list AND the author of the last post? this reults in get every message twice :-( Am 05.06.2011 23:26, schrieb ag...@airpost.net: fwiw, others are seeing this. e.g., in addition to the two bugs i'd already referenced, http://www.directadmin.com/forum/showthread.php?p=202053 and one http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with-mysqldump-SELECT-LOCK-TABL-command claims a solution Add --skip-add-locks to your mysqldump command which, having added as i mentioned above, to the [mysqldump] section of /etc/my.cnf, does NOT make a difference for me. On Sun, 05 Jun 2011 23:19 +0200, Reindl Harald h.rei...@thelounge.net wrote: the grant statements does nobody interest mysql select * from mysql.user where user='root' limit 1; and, my result for your cmd, mysql select * from mysql.user where user='root' limit 1; +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ | localhost | root | *3..4 | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y| Y| Y | Y | Y | Y| Y | Y | Y | || | | 0 | 0 | 0 | 0 || NULL | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--++--++-+--+---+-+-+--++---+ 1 row in set (0.06 sec) -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
On Sun, 05 Jun 2011 23:30 +0200, Reindl Harald h.rei...@thelounge.net wrote: BTW WHY is everybody ansering to the list AND the author of the last post? this reults in get every message twice :-( Reply - sends to ONLY the From == h.rei...@thelounge.net Reply to all sends to BOTH the From == h.rei...@thelounge.net AND the list. I suppose if the list manager software, or your client were configured differently ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
On Sun, 05 Jun 2011 23:29 +0200, Reindl Harald h.rei...@thelounge.net wrote: i would use a replication slave and stop him for consistent backups because dumb locks are not really a good solution independent if this works normally unfortunately, i have no idea what that means. something's apparently broken with mysqldump -- enough so that lots of people are seeing and reporting this same error after the 5.1 - 5.5 upgrade. why would setting up a replication slave be necessary or a good solution to the problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
Am 05.06.2011 23:49, schrieb ag...@airpost.net: On Sun, 05 Jun 2011 23:29 +0200, Reindl Harald h.rei...@thelounge.net wrote: i would use a replication slave and stop him for consistent backups because dumb locks are not really a good solution independent if this works normally unfortunately, i have no idea what that means. something's apparently broken with mysqldump -- enough so that lots of people are seeing and reporting this same error after the 5.1 - 5.5 upgrade. why would setting up a replication slave be necessary or a good solution to the problem? because there is no lock on any production table? have fun using mysqldump with really hughe databases :-) a replication slave is synchron, you can stop the slave, copy the whole datadir and after starting the slave it will make all changes from the binary log signature.asc Description: OpenPGP digital signature
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
i still have no idea why this is necessary. there seems to be a but, problem, misconfiguration, etc. wouldn't it make some sense to try to FIX it, rather than setting up a completely different server? perhaps someone with an idea of the problem and its solution will be able to chime in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgraded from 5.1-5.5. now getting a mysqldump ERROR 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES. a bug or my config?
Am 05.06.2011 23:55, schrieb ag...@airpost.net: i still have no idea why this is necessary. take it or not it is a professional solution which works for databses with 20 GB every day here with rsync without interrupt/lock mysqld a second and it is much faster there seems to be a but, problem, misconfiguration, etc. wouldn't it make some sense to try to FIX it, rather than setting up a completely different server? it takes 5 minutes starting a replication salve on the same machine with its own socket/port perhaps someone with an idea of the problem and its solution will be able to chime in. wait until it is fixed or think about a better solution which will work in the future signature.asc Description: OpenPGP digital signature
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Appreciate any help on this. Any alternative of mysqldump is also fine with me to take backup while restricting root login access from localhost only. Thanks in advance. Regards, Tanmay On Wed, Oct 20, 2010 at 10:14 AM, Tanmay Pradhan tanma...@gmail.com wrote: Yes, r...@localhost entry is still present in user table. Only root@'%' is deleted. So it's not obvious to fail. Hi yu.zou, The r...@localhost entry already had all privileges, except this entry had empty password column. u...@localhost entry before GRANT ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | However, I still gave the following cmd. mysql GRANT select, lock tables ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password'; mysql flush privileges; u...@localhost entry after GRANT == ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Of course you know you did not delete r...@localhost, just root @ '%'
which generally should not matter to mysqldump.
What I suspect is the issue here is that the database you are trying
to dump contains procedures/methods that were defined by a user while
logged in as root@'%'. If you recreate those procs as your database
user, this should correct.
- michael dykman
On Tue, Oct 19, 2010 at 8:40 AM, Krishna Chandra Prajapati
prajapat...@gmail.com wrote:
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hellpo Krishna,
On 10/19/2010 8:40 AM, Krishna Chandra Prajapati wrote:
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Not true. The utility mysqldump is just a client like any other program
and can authenticate with the MySQL instance (the database daemon) as
any valid user.
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhantanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
To fix this, you need to reset the DEFINER for a TRIGGER defined within
the database so that it is defined as a valid user account.
http://dev.mysql.com/doc/refman/5.1/en/triggers.html
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
In order to issue the LOCK TABLES command, a user needs certain privileges:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Securing MySQL is fairly easy. Check out this guide in the manual for
details:
http://dev.mysql.com/doc/refman/5.1/en/security.html
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Yes, r...@localhost entry is still present in user table. Only root@'%' is deleted. So it's not obvious to fail. Hi yu.zou, The r...@localhost entry already had all privileges, except this entry had empty password column. u...@localhost entry before GRANT ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost | root| | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y | Y | Y | Y | Y| || | | 0 | 0 | 0 | 0 | However, I still gave the following cmd. mysql GRANT select, lock tables ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password'; mysql flush privileges; u...@localhost entry after GRANT == ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost
mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Lock Tables Question
I think you're missing the concept of a transaction in the database sense. The idea behind a transaction is that you can perform multiple steps and if you don't complete all steps, any changes are reversed. The reversal process is handled by the database. A good example is moving money from bank account A to B. You specify how much to move and that amount is debited from account A and credited to account B, 2 steps. If the first step happens, but not the second, then the first step needs to be reversed. Until the transaction is complete, anything querying the data needs to see bank account in it's state before any transaction started, a type of versioning. You seem to be trying implement all this manually, which you would need to do if you are using MyISAM based tables. But you may not need to use transactions at all if your data does not have real time dependencies. On 6/4/07, David T. Ashley [EMAIL PROTECTED] wrote: I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. For example: #1)LOCK TABLE x; #2)Make modifications to tables x, y, and z; #3)UNLOCK TABLES; Are there any race conditions in just using one table for this purpose? For example, SQL guarantees that a given SQL statement is atomic. But it is guaranteed that #2 will complete before #3 above? If every process uses the same rule, can anything unexpected happen? One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. Thanks.
Re: Lock Tables Question
On 6/5/07, Brent Baisley [EMAIL PROTECTED] wrote: I think you're missing the concept of a transaction in the database sense. The idea behind a transaction is that you can perform multiple steps and if you don't complete all steps, any changes are reversed. The reversal process is handled by the database. A good example is moving money from bank account A to B. You specify how much to move and that amount is debited from account A and credited to account B, 2 steps. If the first step happens, but not the second, then the first step needs to be reversed. Until the transaction is complete, anything querying the data needs to see bank account in it's state before any transaction started, a type of versioning. You seem to be trying implement all this manually, which you would need to do if you are using MyISAM based tables. But you may not need to use transactions at all if your data does not have real time dependencies. I knew somebody was going to say this. Here is the relevant prose from my original post. BEGIN One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. END There is no concept that I'm missing. I understand what a transaction is. But I just don't want to bothered. My application is simple enough that bogarting the database until all necessary modifications have been made and the tables are again consistent is good enough. Collisions are handled by serialization. Period. Somebody wins. Everyone else waits. Works for me.
Re: Lock Tables Question
David T. Ashley wrote:
There is no concept that I'm missing. I understand what a transaction is.
But I just don't want to bothered. My application is simple enough that
bogarting the database until all necessary modifications have been made and
the tables are again consistent is good enough.
Collisions are handled by serialization. Period. Somebody wins. Everyone
else waits. Works for me.
Then the simplest possible thing to do (besides using transactions, which IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name'). That should handle
your requirement to make locks 'database-local.'
In my experience, using LOCK TABLES becomes a spaghetti problem that begins to involve
more and more things until you are going through *serious* contortions. I would avoid
it at all costs.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/5/07, Baron Schwartz [EMAIL PROTECTED] wrote:
David T. Ashley wrote:
There is no concept that I'm missing. I understand what a transaction
is.
But I just don't want to bothered. My application is simple enough that
bogarting the database until all necessary modifications have been made
and
the tables are again consistent is good enough.
Collisions are handled by serialization. Period. Somebody
wins. Everyone
else waits. Works for me.
Then the simplest possible thing to do (besides using transactions, which
IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name'). That
should handle
your requirement to make locks 'database-local.'
In my experience, using LOCK TABLES becomes a spaghetti problem that
begins to involve
more and more things until you are going through *serious* contortions. I
would avoid
it at all costs.
My only concern with GET_LOCK() is that lock is server-global rather than
database-global. This makes attacks possible in a shared setting (some bad
person could disable your database code by going after your lock).
My solution is just to lock all tables in one statement.
The only question I have (and nobody has answered this) is how many tables I
can include in a single LOCK TABLE statement. I thinking anything up to a
few thousand shouldn't be a problem. What is the limit?
Thanks, Dave.
Re: Lock Tables Question
Hi David, On Jun 5, 2007, at 3:55 PM, David T. Ashley wrote: My only concern with GET_LOCK() is that lock is server-global rather than database-global. This makes attacks possible in a shared setting (some bad person could disable your database code by going after your lock). My solution is just to lock all tables in one statement. The only question I have (and nobody has answered this) is how many tables I can include in a single LOCK TABLE statement. I thinking anything up to a few thousand shouldn't be a problem. What is the limit? The size of a query is limited by the value of the max_allowed_packet system parameter. So if you want to add more tables to your LOCK TABLE statement, you should just be able to increase max_allowed_packet until it works. Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock Tables Question
I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. For example: #1)LOCK TABLE x; #2)Make modifications to tables x, y, and z; #3)UNLOCK TABLES; Are there any race conditions in just using one table for this purpose? For example, SQL guarantees that a given SQL statement is atomic. But it is guaranteed that #2 will complete before #3 above? If every process uses the same rule, can anything unexpected happen? One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. Thanks.
Re: Lock Tables Question
David T. Ashley wrote: I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. You could use a string lock for this. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/4/07, Gerald L. Clark [EMAIL PROTECTED] wrote: David T. Ashley wrote: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; You could use a string lock for this. Thanks for the suggestion. It looks logically correct. I'd like to stay away from a string lock if possible because other database users could interfere with it (it is server global, and not tied to the database). My original question is still of interest to me ... Thanks.
RE: Lock Tables Question
Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: David T. Ashley [mailto:[EMAIL PROTECTED] Sent: Monday, June 04, 2007 3:54 PM To: mysql@lists.mysql.com Subject: Re: Lock Tables Question On 6/4/07, Gerald L. Clark [EMAIL PROTECTED] wrote: David T. Ashley wrote: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; You could use a string lock for this. Thanks for the suggestion. It looks logically correct. I'd like to stay away from a string lock if possible because other database users could interfere with it (it is server global, and not tied to the database). My original question is still of interest to me ... Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/4/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Hi Jerry, I really appreciate the good advice. However, my original question is still unanswered. My original question is whether I can lock one table (as a gentleman's rule followed by all processes) to serialize access to the database consisting of many tables. LOCK TABLE x; Manipulate many tables, perhaps not even including table x; UNLOCK TABLES; My question is really whether MySQL might do some strange optimizations ... or somehow buffer the middle query so that it completes after the UNLOCK. Thanks, Dave.
Re: Lock Tables Question
David T. Ashley wrote: On 6/4/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Hi Jerry, I really appreciate the good advice. However, my original question is still unanswered. My original question is whether I can lock one table (as a gentleman's rule followed by all processes) to serialize access to the database consisting of many tables. LOCK TABLE x; Manipulate many tables, perhaps not even including table x; UNLOCK TABLES; My question is really whether MySQL might do some strange optimizations ... or somehow buffer the middle query so that it completes after the UNLOCK. Thanks, Dave. Once you issue a LOCK TABLES command, you may not access any tables not in the LOCK statement. You must lock *ALL* tables you will use, perform your updates, and then UNLOCK TABLES. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
Once you issue a LOCK TABLES command, you may not access any tables not in the LOCK statement. You must lock *ALL* tables you will use, perform your updates, and then UNLOCK TABLES. I didn't know that. I reviewed the documentation. Thanks. OK, then my only remaining question is how many tables I can lock in a single SQL statement. I'm guessing no practical limit (i.e. thousands). What is the maximum length of a MySQL statement, anyway? Thanks.
Re: LOCK TABLES
Hi
From the analysis of other sources,
The error may be due to:
1. MediaWiki was updated from an older version without updating the database.
so to update the database, you can use either the maintenance script
maintenance/update.php via the command line, or the web installer (rename
LocalSettings.php, then go to the wiki).
2. --opt is enabled by default with mysqldump, and part of what it does it
lock tables. So try the backup without lock tables, by adding
--skip-lock-tables.
Thanks
ViSolve DB Team.
- Original Message -
From: mdpeters [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 16, 2006 9:14 PM
Subject: LOCK TABLES
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES
mysqldump --user root --password=password horsewiki horsewiki.sql Dan Buettner wrote: Hmmm, sounds like something's pretty abnormal here. Any idea what may have been done here? I wonder if you could step around this with a call to mysqldump that doesn't explicitly lock tables ... what is the commad you're running again? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. It moves past archive and onto another table. I did this 6 times, each time moving the next one it complained about until I stopped and put them all back. Dan Buettner wrote: Try looking in the filesystem for the file(s) called archive in the database directory, and move them somewhere else (or if you know you don't need them, delete them). If it is/was a MyISAM table, or just an errant file, this should work. If it is/was InnoDB, this will be trickier ... Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: As you can see, it is clearly showing up but I cannot do anything with it. mysql show tables; +-+ | Tables_in_horsewiki | +-+ | archive | | categorylinks | | externallinks | | hitcounter | | horse_archive | | horse_categorylinks | | horse_externallinks | | horse_hitcounter| | horse_image | | horse_imagelinks| | horse_interwiki | | horse_ipblocks | | horse_job | | horse_logging | | horse_math | | horse_objectcache | | horse_oldimage | | horse_page | | horse_pagelinks | | horse_querycache| | horse_recentchanges | | horse_revision | | horse_searchindex | | horse_site_stats| | horse_templatelinks | | horse_text | | horse_trackbacks| | horse_transcache| | horse_user | | horse_user_groups | | horse_user_newtalk | | horse_validate | | horse_watchlist | | image | | imagelinks | | interwiki | | ipblocks| | job | | logging | | math| | objectcache | | oldimage| | page| | pagelinks | | querycache | | recentchanges | | revision| | searchindex | | site_stats | | templatelinks | | text| | trackbacks | | transcache | | user| | user_groups | | user_newtalk| | validate| | watchlist | +-+ 58 rows in set (0.00 sec) mysql SELECT * FROM archive LIMIT 1; ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist mysql drop table archive; ERROR 1051 (42S02): Unknown table 'archive' Dan Buettner wrote: Michael, is the 'archive' table present in your database? e.g., if you do a 'LIST TABLES', does it show up? What happens if you do a SELECT * FROM archive LIMIT 1 ? I'd hazard a guess that you may have a table definition file with no actual table data files, if you're on MyISAM tables. If you don't need the archive table, can you DROP it successfully? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I am having a devil of a time moving a database from one server to another. My database is one that is in production to support the mediawiki wiki application. This is a Solaris Sparc 10 system using the mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki. I execute this: # mysqldump --user root --password=password horsewiki horsewiki.sql and get this: mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when using LOCK TABLES I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the database to an SQL file. When I attempt to import it on the other system, I get this: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 494 STR: / SQL: -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8 / link rel=stylesheet type=text/css href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
Re: LOCK TABLES
I tried this first to no avail.
mysqldump --user root --password=password --skip-lock-tables horsewiki
horsewiki.sql
mysqldump: mysqldump: Couldn't execute 'show create table `archive`':
Table 'horsewiki.archive' doesn't exist (1146)
I'll try the update next.
Visolve DB Team wrote:
Hi
From the analysis of other sources,
The error may be due to:
1. MediaWiki was updated from an older version without updating the
database. so to update the database, you can use either the
maintenance script maintenance/update.php via the command line, or
the web installer (rename
LocalSettings.php, then go to the wiki).
2. --opt is enabled by default with mysqldump, and part of what it
does it lock tables. So try the backup without lock tables, by adding
*--skip-lock-tables.*
Thanks
ViSolve DB Team.
- Original Message -
From: mdpeters [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com
Sent: Monday, October 16, 2006 9:14 PM
Subject: LOCK TABLES
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is
horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist
when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the
problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database:
`horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
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: LOCK TABLES
I wonder if this is a permissions problem. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mdpeters [mailto:[EMAIL PROTECTED] Sent: Monday, October 16, 2006 9:19 PM To: Dan Buettner Cc: mysql@lists.mysql.com Subject: Re: LOCK TABLES I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. It moves past archive and onto another table. I did this 6 times, each time moving the next one it complained about until I stopped and put them all back. Dan Buettner wrote: Try looking in the filesystem for the file(s) called archive in the database directory, and move them somewhere else (or if you know you don't need them, delete them). If it is/was a MyISAM table, or just an errant file, this should work. If it is/was InnoDB, this will be trickier ... Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: As you can see, it is clearly showing up but I cannot do anything with it. mysql show tables; +-+ | Tables_in_horsewiki | +-+ | archive | | categorylinks | | externallinks | | hitcounter | | horse_archive | | horse_categorylinks | | horse_externallinks | | horse_hitcounter| | horse_image | | horse_imagelinks| | horse_interwiki | | horse_ipblocks | | horse_job | | horse_logging | | horse_math | | horse_objectcache | | horse_oldimage | | horse_page | | horse_pagelinks | | horse_querycache| | horse_recentchanges | | horse_revision | | horse_searchindex | | horse_site_stats| | horse_templatelinks | | horse_text | | horse_trackbacks| | horse_transcache| | horse_user | | horse_user_groups | | horse_user_newtalk | | horse_validate | | horse_watchlist | | image | | imagelinks | | interwiki | | ipblocks| | job | | logging | | math| | objectcache | | oldimage| | page| | pagelinks | | querycache | | recentchanges | | revision| | searchindex | | site_stats | | templatelinks | | text| | trackbacks | | transcache | | user| | user_groups | | user_newtalk| | validate| | watchlist | +-+ 58 rows in set (0.00 sec) mysql SELECT * FROM archive LIMIT 1; ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist mysql drop table archive; ERROR 1051 (42S02): Unknown table 'archive' Dan Buettner wrote: Michael, is the 'archive' table present in your database? e.g., if you do a 'LIST TABLES', does it show up? What happens if you do a SELECT * FROM archive LIMIT 1 ? I'd hazard a guess that you may have a table definition file with no actual table data files, if you're on MyISAM tables. If you don't need the archive table, can you DROP it successfully? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I am having a devil of a time moving a database from one server to another. My database is one that is in production to support the mediawiki wiki application. This is a Solaris Sparc 10 system using the mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki. I execute this: # mysqldump --user root --password=password horsewiki horsewiki.sql and get this: mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when using LOCK TABLES I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the database to an SQL file. When I attempt to import it on the other system, I get this: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 494 STR: / SQL: -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8
Re: LOCK TABLES
I execute using root permissions. I successfully upgraded mediawiki to the latest mediawiki-1.8.2 version for grins. I ran php -cli ./maintenance/update.php without trouble. Jerry Schwartz wrote: I wonder if this is a permissions problem. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mdpeters [mailto:[EMAIL PROTECTED] Sent: Monday, October 16, 2006 9:19 PM To: Dan Buettner Cc: mysql@lists.mysql.com Subject: Re: LOCK TABLES I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. It moves past archive and onto another table. I did this 6 times, each time moving the next one it complained about until I stopped and put them all back. Dan Buettner wrote: Try looking in the filesystem for the file(s) called archive in the database directory, and move them somewhere else (or if you know you don't need them, delete them). If it is/was a MyISAM table, or just an errant file, this should work. If it is/was InnoDB, this will be trickier ... Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: As you can see, it is clearly showing up but I cannot do anything with it. mysql show tables; +-+ | Tables_in_horsewiki | +-+ | archive | | categorylinks | | externallinks | | hitcounter | | horse_archive | | horse_categorylinks | | horse_externallinks | | horse_hitcounter| | horse_image | | horse_imagelinks| | horse_interwiki | | horse_ipblocks | | horse_job | | horse_logging | | horse_math | | horse_objectcache | | horse_oldimage | | horse_page | | horse_pagelinks | | horse_querycache| | horse_recentchanges | | horse_revision | | horse_searchindex | | horse_site_stats| | horse_templatelinks | | horse_text | | horse_trackbacks| | horse_transcache| | horse_user | | horse_user_groups | | horse_user_newtalk | | horse_validate | | horse_watchlist | | image | | imagelinks | | interwiki | | ipblocks| | job | | logging | | math| | objectcache | | oldimage| | page| | pagelinks | | querycache | | recentchanges | | revision| | searchindex | | site_stats | | templatelinks | | text| | trackbacks | | transcache | | user| | user_groups | | user_newtalk| | validate| | watchlist | +-+ 58 rows in set (0.00 sec) mysql SELECT * FROM archive LIMIT 1; ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist mysql drop table archive; ERROR 1051 (42S02): Unknown table 'archive' Dan Buettner wrote: Michael, is the 'archive' table present in your database? e.g., if you do a 'LIST TABLES', does it show up? What happens if you do a SELECT * FROM archive LIMIT 1 ? I'd hazard a guess that you may have a table definition file with no actual table data files, if you're on MyISAM tables. If you don't need the archive table, can you DROP it successfully? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I am having a devil of a time moving a database from one server to another. My database is one that is in production to support the mediawiki wiki application. This is a Solaris Sparc 10 system using the mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki. I execute this: # mysqldump --user root --password=password horsewiki horsewiki.sql and get this: mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when using LOCK TABLES I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the database to an SQL file. When I attempt to import it on the other system, I get this: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 494 STR: / SQL: -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon
LOCK TABLES
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES
Michael, is the 'archive' table present in your database? e.g., if
you do a 'LIST TABLES', does it show up? What happens if you do a
SELECT * FROM archive LIMIT 1 ?
I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.
If you don't need the archive table, can you DROP it successfully?
Dan
On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
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: LOCK TABLES
As you can see, it is clearly showing up but I cannot do anything with it.
mysql show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks |
| externallinks |
| hitcounter |
| horse_archive |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks |
| horse_job |
| horse_logging |
| horse_math |
| horse_objectcache |
| horse_oldimage |
| horse_page |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision |
| horse_searchindex |
| horse_site_stats|
| horse_templatelinks |
| horse_text |
| horse_trackbacks|
| horse_transcache|
| horse_user |
| horse_user_groups |
| horse_user_newtalk |
| horse_validate |
| horse_watchlist |
| image |
| imagelinks |
| interwiki |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks |
| querycache |
| recentchanges |
| revision|
| searchindex |
| site_stats |
| templatelinks |
| text|
| trackbacks |
| transcache |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist |
+-+
58 rows in set (0.00 sec)
mysql SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist
mysql drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'
Dan Buettner wrote:
Michael, is the 'archive' table present in your database? e.g., if
you do a 'LIST TABLES', does it show up? What happens if you do a
SELECT * FROM archive LIMIT 1 ?
I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.
If you don't need the archive table, can you DROP it successfully?
Dan
On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is
horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the
problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico
type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html;
charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database:
`horsewiki` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
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
Re: LOCK TABLES
Try looking in the filesystem for the file(s) called archive in the
database directory, and move them somewhere else (or if you know you
don't need them, delete them).
If it is/was a MyISAM table, or just an errant file, this should work.
If it is/was InnoDB, this will be trickier ...
Dan
On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:
As you can see, it is clearly showing up but I cannot do anything with it.
mysql show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks |
| externallinks |
| hitcounter |
| horse_archive |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks |
| horse_job |
| horse_logging |
| horse_math |
| horse_objectcache |
| horse_oldimage |
| horse_page |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision |
| horse_searchindex |
| horse_site_stats|
| horse_templatelinks |
| horse_text |
| horse_trackbacks|
| horse_transcache|
| horse_user |
| horse_user_groups |
| horse_user_newtalk |
| horse_validate |
| horse_watchlist |
| image |
| imagelinks |
| interwiki |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks |
| querycache |
| recentchanges |
| revision|
| searchindex |
| site_stats |
| templatelinks |
| text|
| trackbacks |
| transcache |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist |
+-+
58 rows in set (0.00 sec)
mysql SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist
mysql drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'
Dan Buettner wrote:
Michael, is the 'archive' table present in your database? e.g., if
you do a 'LIST TABLES', does it show up? What happens if you do a
SELECT * FROM archive LIMIT 1 ?
I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.
If you don't need the archive table, can you DROP it successfully?
Dan
On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is
horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the
problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico
type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html;
charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database:
`horsewiki` --
I have not found a good source
Re: LOCK TABLES
I tried mv archive.frm .archive.frm first. Then I ran mysqldump again.
It moves past archive and onto another table. I did this 6 times, each
time moving the next one it complained about until I stopped and put
them all back.
Dan Buettner wrote:
Try looking in the filesystem for the file(s) called archive in the
database directory, and move them somewhere else (or if you know you
don't need them, delete them).
If it is/was a MyISAM table, or just an errant file, this should work.
If it is/was InnoDB, this will be trickier ...
Dan
On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:
As you can see, it is clearly showing up but I cannot do anything
with it.
mysql show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks |
| externallinks |
| hitcounter |
| horse_archive |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks |
| horse_job |
| horse_logging |
| horse_math |
| horse_objectcache |
| horse_oldimage |
| horse_page |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision |
| horse_searchindex |
| horse_site_stats|
| horse_templatelinks |
| horse_text |
| horse_trackbacks|
| horse_transcache|
| horse_user |
| horse_user_groups |
| horse_user_newtalk |
| horse_validate |
| horse_watchlist |
| image |
| imagelinks |
| interwiki |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks |
| querycache |
| recentchanges |
| revision|
| searchindex |
| site_stats |
| templatelinks |
| text|
| trackbacks |
| transcache |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist |
+-+
58 rows in set (0.00 sec)
mysql SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist
mysql drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'
Dan Buettner wrote:
Michael, is the 'archive' table present in your database? e.g., if
you do a 'LIST TABLES', does it show up? What happens if you do a
SELECT * FROM archive LIMIT 1 ?
I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.
If you don't need the archive table, can you DROP it successfully?
Dan
On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:
I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system
using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is
horsewiki.
I execute this:
# mysqldump --user root --password=password horsewiki horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't
exist when
using LOCK TABLES
I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:
Error
There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the
problem
ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico
type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html;
charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721
media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not
allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com /
localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL
Re: Re: LOCK TABLES
Hmmm, sounds like something's pretty abnormal here. Any idea what may have been done here? I wonder if you could step around this with a call to mysqldump that doesn't explicitly lock tables ... what is the commad you're running again? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. It moves past archive and onto another table. I did this 6 times, each time moving the next one it complained about until I stopped and put them all back. Dan Buettner wrote: Try looking in the filesystem for the file(s) called archive in the database directory, and move them somewhere else (or if you know you don't need them, delete them). If it is/was a MyISAM table, or just an errant file, this should work. If it is/was InnoDB, this will be trickier ... Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: As you can see, it is clearly showing up but I cannot do anything with it. mysql show tables; +-+ | Tables_in_horsewiki | +-+ | archive | | categorylinks | | externallinks | | hitcounter | | horse_archive | | horse_categorylinks | | horse_externallinks | | horse_hitcounter| | horse_image | | horse_imagelinks| | horse_interwiki | | horse_ipblocks | | horse_job | | horse_logging | | horse_math | | horse_objectcache | | horse_oldimage | | horse_page | | horse_pagelinks | | horse_querycache| | horse_recentchanges | | horse_revision | | horse_searchindex | | horse_site_stats| | horse_templatelinks | | horse_text | | horse_trackbacks| | horse_transcache| | horse_user | | horse_user_groups | | horse_user_newtalk | | horse_validate | | horse_watchlist | | image | | imagelinks | | interwiki | | ipblocks| | job | | logging | | math| | objectcache | | oldimage| | page| | pagelinks | | querycache | | recentchanges | | revision| | searchindex | | site_stats | | templatelinks | | text| | trackbacks | | transcache | | user| | user_groups | | user_newtalk| | validate| | watchlist | +-+ 58 rows in set (0.00 sec) mysql SELECT * FROM archive LIMIT 1; ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist mysql drop table archive; ERROR 1051 (42S02): Unknown table 'archive' Dan Buettner wrote: Michael, is the 'archive' table present in your database? e.g., if you do a 'LIST TABLES', does it show up? What happens if you do a SELECT * FROM archive LIMIT 1 ? I'd hazard a guess that you may have a table definition file with no actual table data files, if you're on MyISAM tables. If you don't need the archive table, can you DROP it successfully? Dan On 10/16/06, mdpeters [EMAIL PROTECTED] wrote: I am having a devil of a time moving a database from one server to another. My database is one that is in production to support the mediawiki wiki application. This is a Solaris Sparc 10 system using the mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki. I execute this: # mysqldump --user root --password=password horsewiki horsewiki.sql and get this: mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when using LOCK TABLES I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the database to an SQL file. When I attempt to import it on the other system, I get this: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 494 STR: / SQL: -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8 / link rel=stylesheet type=text/css href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right / link rel=stylesheet type=text/css href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print
re[2]: (Windows) drop / create index and lock tables
Dan, I do need to prevent writes from occurring between the DROP and CREATE. The primary purpose of this is to prevent the CREATE from failing due to duplicate entries being found during key creation. As CREATE INDEX has no IGNORE option, I had thought I would have to do this a nasty way, but it turns out that even though CREATE INDEX is mapped to the equivalent ALTER TABLE statement, not all of the options available in ALTER TABLE are mapped. So, if I change my queries to the following: DROP INDEX `keyX` ON `foo`; ALTER IGNORE TABLE `foo` ADD UNIQUE KEY `keyX`(`column1`, `column2`); Then, if any rows are duplicated between the two, they will be silently dropped during the key creation. Thanks for your help! --Rob Rob, seems like you want to ensure that no writes occur in between the drop index and create index statements, yes? It's not pretty, but you could stop the mysql service and start it back up with --skip-networking, then access it from localhost to perform your changes. If you have processes updating from localhost this won't be effective, though. Another thought might be to create your new index first, then drop the old, as in: CREATE UNIQUE INDEX `keyXX` ON `foo` (`column1`,`column2`); DROP INDEX `keyX` ON `foo`; The index having a different name should only be a problem if you've used optimizer hints in your SQL - some people do, some don't. Someone else may have a better thought. Dan On 10/3/06, Rob Desbois [EMAIL PROTECTED] wrote: Hi all, Say I have the following MyISAM table (example-ified) in a Windows-hosted DB: CREATE TABLE foo ( column1 CHAR(1), column2 CHAR(1), UNIQUE KEY `keyX`(`column1`)); I have to perform an update of the key to extend it to both columns (it's an example, ignore the content of the key), and want to ensure data integrity while I recreate it. The following is what I thought I had to do: LOCK TABLES foo WRITE; DROP INDEX `keyX` ON `foo`; CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`); UNLOCK TABLES; After much head-scratching due to Error Code : 1100 Table 'foo' was not locked with LOCK TABLES, I discovered that CREATE / DROP INDEX statements are mapped to equivalent ALTER TABLE statements. This, due to the way ALTER TABLE statements 'work' on windows, renders this code unusable as the DROP INDEX statement unlocks the table. Before the CREATE INDEX statement will work I then have to run UNLOCK TABLES, which also makes sense (I thought it was unlocked??). So - I can't lock the table whiel I drop then recreate the index, so what's the best way to do this? TIA, --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- Original Message -- FROM: Dan Buettner [EMAIL PROTECTED] TO:Rob Desbois [EMAIL PROTECTED] DATE: Tue, 3 Oct 2006 11:03:58 -0500 SUBJECT: Re: (Windows) drop / create index and lock tables Rob, seems like you want to ensure that no writes occur in between the drop index and create index statements, yes? It's not pretty, but you could stop the mysql service and start it back up with --skip-networking, then access it from localhost to perform your changes. If you have processes updating from localhost this won't be effective, though. Another thought might be to create your new index first, then drop the old, as in: CREATE UNIQUE INDEX `keyXX` ON `foo` (`column1`,`column2`); DROP INDEX `keyX` ON `foo`; The index having a different name should only be a problem if you've used optimizer hints in your SQL - some people do, some don't. Someone else may have a better thought. Dan On 10/3/06, Rob Desbois [EMAIL PROTECTED] wrote: Hi all, Say I have the following MyISAM table (example-ified) in a Windows-hosted DB: CREATE TABLE foo ( column1 CHAR(1), column2 CHAR(1), UNIQUE KEY `keyX`(`column1`)); I have to perform an update of the key to extend it to both columns (it's an example, ignore the content of the key), and want to ensure data integrity while I recreate it. The following is what I thought I had to do: LOCK TABLES foo WRITE; DROP INDEX `keyX` ON `foo`; CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`); UNLOCK TABLES; After much head-scratching due to Error Code : 1100 Table 'foo' was not locked with LOCK TABLES, I discovered that CREATE
(Windows) drop / create index and lock tables
Hi all, Say I have the following MyISAM table (example-ified) in a Windows-hosted DB: CREATE TABLE foo ( column1 CHAR(1), column2 CHAR(1), UNIQUE KEY `keyX`(`column1`)); I have to perform an update of the key to extend it to both columns (it's an example, ignore the content of the key), and want to ensure data integrity while I recreate it. The following is what I thought I had to do: LOCK TABLES foo WRITE; DROP INDEX `keyX` ON `foo`; CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`); UNLOCK TABLES; After much head-scratching due to Error Code : 1100 Table 'foo' was not locked with LOCK TABLES, I discovered that CREATE / DROP INDEX statements are mapped to equivalent ALTER TABLE statements. This, due to the way ALTER TABLE statements 'work' on windows, renders this code unusable as the DROP INDEX statement unlocks the table. Before the CREATE INDEX statement will work I then have to run UNLOCK TABLES, which also makes sense (I thought it was unlocked??). So - I can't lock the table whiel I drop then recreate the index, so what's the best way to do this? TIA, --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Windows) drop / create index and lock tables
Rob, seems like you want to ensure that no writes occur in between the drop index and create index statements, yes? It's not pretty, but you could stop the mysql service and start it back up with --skip-networking, then access it from localhost to perform your changes. If you have processes updating from localhost this won't be effective, though. Another thought might be to create your new index first, then drop the old, as in: CREATE UNIQUE INDEX `keyXX` ON `foo` (`column1`,`column2`); DROP INDEX `keyX` ON `foo`; The index having a different name should only be a problem if you've used optimizer hints in your SQL - some people do, some don't. Someone else may have a better thought. Dan On 10/3/06, Rob Desbois [EMAIL PROTECTED] wrote: Hi all, Say I have the following MyISAM table (example-ified) in a Windows-hosted DB: CREATE TABLE foo ( column1 CHAR(1), column2 CHAR(1), UNIQUE KEY `keyX`(`column1`)); I have to perform an update of the key to extend it to both columns (it's an example, ignore the content of the key), and want to ensure data integrity while I recreate it. The following is what I thought I had to do: LOCK TABLES foo WRITE; DROP INDEX `keyX` ON `foo`; CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`); UNLOCK TABLES; After much head-scratching due to Error Code : 1100 Table 'foo' was not locked with LOCK TABLES, I discovered that CREATE / DROP INDEX statements are mapped to equivalent ALTER TABLE statements. This, due to the way ALTER TABLE statements 'work' on windows, renders this code unusable as the DROP INDEX statement unlocks the table. Before the CREATE INDEX statement will work I then have to run UNLOCK TABLES, which also makes sense (I thought it was unlocked??). So - I can't lock the table whiel I drop then recreate the index, so what's the best way to do this? TIA, --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- 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: MySQL lock tables - bug or not?
Thanks for your reply. I repeated your test with the same results on 4.1.21 (database in question is on 4.1). I'll give the ISP another kick and see what they have to say. So there's no other reason why an ISP might not want to grant LOCK TABLES in a shared hosting environment? Thanks, James I am not aware of any such bug related to the LOCK TABLES privilege. Like you I could not find a mention in our bugs database, for any version. It is easy to demonstrate that this is not the case. If permissions are properly set up, LOCK TABLES can be restricted to a database just like every other priv (makes sense, of course!). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL lock tables - bug or not?
I'm using MySQL as the db for Drupal (PHP based CMS), on shared hosting. There are repeated errors because the db user does not have permission for LOCK TABLES, which Drupal uses. The ISP says that they don't grant this permission because ... MySQL has a bug which allows users with GrantTables* the ability to view the Database names of all other databases on the server. Whilst the users can not see any other data, knowing the names of tables can facilitate attacks. (* = I assume they meant 'Lock Tables') However I can't find any mention of this in the bugs db, nor is it listed in the manual as a side effect of granting 'lock tables' permissions. Does anyone know if it is a bug or not? Does anyone know whether LOCK TABLES really is a security risk in a shared server / multi-user environment? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL lock tables - bug or not?
I am not aware of any such bug related to the LOCK TABLES privilege. Like you I could not find a mention in our bugs database, for any version. It is easy to demonstrate that this is not the case. If permissions are properly set up, LOCK TABLES can be restricted to a database just like every other priv (makes sense, of course!). On 5.0.20: mysql grant select, insert, update, delete, lock tables on dl.* to 'bar'@'localhost' identified by 'bar'; mysql show grants for 'bar'@'localhost'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'bar'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' | | GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON `dl`.* TO 'bar'@'localhost' | ++ 2 rows in set (0.00 sec) mysql show databases; ++ | Database | ++ | information_schema | | dl | ++ 2 rows in set (0.00 sec) -- Eric Braswell Web Manager MySQL AB Cupertino, USA James Harvard wrote: I'm using MySQL as the db for Drupal (PHP based CMS), on shared hosting. There are repeated errors because the db user does not have permission for LOCK TABLES, which Drupal uses. The ISP says that they don't grant this permission because ... MySQL has a bug which allows users with GrantTables* the ability to view the Database names of all other databases on the server. Whilst the users can not see any other data, knowing the names of tables can facilitate attacks. (* = I assume they meant 'Lock Tables') However I can't find any mention of this in the bugs db, nor is it listed in the manual as a side effect of granting 'lock tables' permissions. Does anyone know if it is a bug or not? Does anyone know whether LOCK TABLES really is a security risk in a shared server / multi-user environment? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Options --lock-tables, --lock-all-tables do not work in mysql-4.1.8
Hello. Not enough information to make a conclusion. This could be a bug, say: http://bugs.mysql.com/bug.php?id=12410 Slawomir Orlowski (CYMPAK) wrote: hello, I have mysql-4.1.8 compiled form source working on RH7.2. I have tried to backup my database with mysqldump. mysqldump --opt database_name database_name.sql mysqldump --lock-tables database_name database_name.sql mysqldump --skip-opt --lock-tables database_name database_name.sql mysqldump --lock-all-tables database_name database_name.sql Unfortunately options --lock-tables, --lock-all-tables do not work. Only --add-locks works (alone or as part of --opt) but it can cause some inconsistencies because it locks tables individually. How to lock all tables at once? I would appreciate any help. Best regards Slawomir Orlowski -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Options --lock-tables, --lock-all-tables do not work in mysql-4.1.8
hello, I have mysql-4.1.8 compiled form source working on RH7.2. I have tried to backup my database with mysqldump. mysqldump --opt database_name database_name.sql mysqldump --lock-tables database_name database_name.sql mysqldump --skip-opt --lock-tables database_name database_name.sql mysqldump --lock-all-tables database_name database_name.sql Unfortunately options --lock-tables, --lock-all-tables do not work. Only --add-locks works (alone or as part of --opt) but it can cause some inconsistencies because it locks tables individually. How to lock all tables at once? I would appreciate any help. Best regards Slawomir Orlowski -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Options --lock-tables, --lock-all-tables do not work in mysql-4.1.8
Slawomir Orlowski (CYMPAK) wrote: hello, I have mysql-4.1.8 compiled form source working on RH7.2. I have tried to backup my database with mysqldump. mysqldump --opt database_name database_name.sql mysqldump --lock-tables database_name database_name.sql mysqldump --skip-opt --lock-tables database_name database_name.sql mysqldump --lock-all-tables database_name database_name.sql Unfortunately options --lock-tables, --lock-all-tables do not work. Only --add-locks works (alone or as part of --opt) but it can cause some inconsistencies because it locks tables individually. How to lock all tables at once? I would appreciate any help. Best regards Slawomir Orlowski http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html When you say --lock-all-tables does not work, what do you mean? Is there a specific error that you are running into? According to the documentation, this option will Lock all tables across all databases. However, there is a note that may be of interest to you, since you are using 4.1.8 --first-slave, -x Deprecated, renamed to --lock-all-tables in MySQL 4.1.8. Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock tables and sql cache
Hello.
I think it is a weird behavior. I've reported a bug:
http://bugs.mysql.com/bug.php?id=9511
Bob O'Neill [EMAIL PROTECTED] wrote:
If I try to read table 'b' after locking table 'a', I expect to get
the error message Table 'b' was not locked with LOCK TABLES.
However, if my query that accesses table b is stored in the query
cache, I don't get the error. This causes a problem in the following
scenario:
User 1:
LOCK TABLES a
SELECT SQL_CACHE COUNT(*) FROM b
(assume it was already cached)
User 2:
INSERT b VALUES('value');
SELECT SQL_CACHE COUNT(*) FROM b
(the SELECT puts the query back into the cache)
User 1:
SELECT SQL_CACHE COUNT(*) FROM b
(now he gets a different result)
UNLOCK TABLES
User 1 thinks that everything he's doing is safe inside of an
emulated transaction. But the data in table b has changed between
the LOCK and the UNLOCK, and User 1 isn't notified that he is doing
anything wrong.
I think an appropriate fix would be to force User 1 to lock table b
even though the results of that query are stored in the query cache.
Is this possible?
Thanks,
-Bob
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lock tables and sql cache
If I try to read table 'b' after locking table 'a', I expect to get
the error message Table 'b' was not locked with LOCK TABLES.
However, if my query that accesses table b is stored in the query
cache, I don't get the error. This causes a problem in the following
scenario:
User 1:
LOCK TABLES a
SELECT SQL_CACHE COUNT(*) FROM b
(assume it was already cached)
User 2:
INSERT b VALUES('value');
SELECT SQL_CACHE COUNT(*) FROM b
(the SELECT puts the query back into the cache)
User 1:
SELECT SQL_CACHE COUNT(*) FROM b
(now he gets a different result)
UNLOCK TABLES
User 1 thinks that everything he's doing is safe inside of an
emulated transaction. But the data in table b has changed between
the LOCK and the UNLOCK, and User 1 isn't notified that he is doing
anything wrong.
I think an appropriate fix would be to force User 1 to lock table b
even though the results of that query are stored in the query cache.
Is this possible?
Thanks,
-Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Errcode: 24) when using LOCK TABLES doing backup
Hello, We have an issue related to backup a specific db mounted on MySql 3.23.58 and RedHat 8 OS. The server is running Brightor Arcserve Backup agents for MySQL and cannot backup the entire database. Our Computer Associates support say that this error is originated by MySQL, but only occur when the agent try to access a specific db. Other databases in MySQL don´t report errors and the CA agent can do normal backup. Following the issue and the screen reported when launching mysqldump command. This method is used for Brighstor backup agent. Can you help me ? mysqldump -F --opt -S socket -P port -u username --password=password dbname -- MySQL dump 8.23 -- -- Host: localhost Database: - -- Server version 3.23.58-log /usr/local/mysql/bin/mysqldump: Got error: 1105: File '/usr/local/mysql/var/atmail/Abook_vx.MYD' not found (Errcode: 24) when using LOCK TABLES But the message not is always with the table Abook_vx.MYD. Sometimes appear in Abook_vx.MYD, other in data files .MYD and other in .frm files. Our database has 7000 tables. Could be this the problem ? The backup support say that the problem resides in the amount of tables open at the same time. Thanks in advance, Diego.-
lock tables
Hi, I'm a bit confused by the lock mechanism under mysql. When user A does an update on table 1, the table is automatically locked by mysql?that means at the same time user B won't be able to modify the same row? Or do I have to specify the lock for each query? And what about temporary tables? If anybody has a simple explanation or a link on a doc thanks, Melanie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock tables
You only need to lock whene you are going to run a query that contains a series of actions and they all have to happen at the same time. As for single queries, they are already atomic, so you don't need to put and locks around them. On Mon, 11 Oct 2004 11:14:36 +0100, Melanie Courtot [EMAIL PROTECTED] wrote: Hi, I'm a bit confused by the lock mechanism under mysql. When user A does an update on table 1, the table is automatically locked by mysql?that means at the same time user B won't be able to modify the same row? Or do I have to specify the lock for each query? And what about temporary tables? If anybody has a simple explanation or a link on a doc thanks, Melanie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOCK TABLES permission and 4.0.15a
Hello, My ISP is using the old version 4.0.15a and have no early plans to upgrade it. I'm trying to issue LOCK/UNLOCK TABLES commands at my ISP's MySQL, and I'm getting an access denied error. I asked them to give me the lock tables permission, but they answered this permission option isn't available on versions prior to 4.0.2. They also told me they would have to give me a high elevated previledge to issue these commands. Is it true ? A plain user with full database (but no grant all) access cannot lock a table ? Could someone please point me a solution ?? Thank you very much, Fernando Monteiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES permission and 4.0.15a
Version 4.0.15 comes after version 4.0.2 (15 2), so the version is not a problem here. From the manual: As of MySQL 4.0.2, to use LOCK TABLES you must have the global LOCK TABLES privilege and a SELECT privilege for the involved tables. http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html The LOCK TABLES privilege may seem elevated as it is global, but you can only lock tables for which you have the SELECT privilege. Michael Fernando Monteiro wrote: Hello, My ISP is using the old version 4.0.15a and have no early plans to upgrade it. I'm trying to issue LOCK/UNLOCK TABLES commands at my ISP's MySQL, and I'm getting an access denied error. I asked them to give me the lock tables permission, but they answered this permission option isn't available on versions prior to 4.0.2. They also told me they would have to give me a high elevated previledge to issue these commands. Is it true ? A plain user with full database (but no grant all) access cannot lock a table ? Could someone please point me a solution ?? Thank you very much, Fernando Monteiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES permission and 4.0.15a
Fernando Monteiro wrote: Hello, Michael, Version 4.0.15 comes after version 4.0.2 (15 2), so the version is not a problem here. (...) I asked them to give me the lock tables permission, but they answered this permission option isn't available on versions prior to 4.0.2. They also told me they would have to give me a high elevated privilege to issue these commands. It was a typo, sorry... The right version should be 4.0.20. Thank you for you explanation about the lock table permission !! Regards, Fernando Monteiro You're welcome. If you look at the quote I included from the manual, the LOCK TABLES privilege has been available since version 4.0.2, so they are wrong if they told you 4.0.20. The next sentence in the manual after the one I quoted is In MySQL 3.23, you must have SELECT, INSERT, DELETE, and UPDATE privileges for the tables. In other words, the LOCK TABLES global privilege was added in 4.0.2 to further restrict who could lock tables. So, again, the version of mysql is not a problem -- the ability to lock tables has been in mysql for a long time. In fact, prior to 4.0.2 you would most likely have been able to lock tables already, as I imagine you do have SELECT, INSERT, DELETE, and UPDATE privileges on your own tables. They can give you the ability to lock tables in 4.0.15, if they choose to. If they are your tables, I'm not sure why they are reluctant to do so. The only reason I can think of would be if they have already given you SELECT permission on tables which are not yours, which they don't want you to lock. Now that you are armed with the truth, perhaps you can get them to tell you their real reasons... Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VB .NET MYSQL - LOCK TABLES
Hello MySql List, I have create connection with VB .NET MySql, and now i must use the LOCK TABLES statment. I want to know how i can use this sintax .. i must open connection - begin the LOCK TABLES - begin the select statment, and then UNLOCK TABLES .. I think so that is not correctly .. where i can see the example for this problem ? Excusme for my english .. i don's speak and write very well!! Mirco
RE: VB .NET MYSQL - LOCK TABLES
You will need to reuse your database connection, do not open a new connection with each call or the lock will not be there. LOCK TABLE table1 READ; SELECT * FROM table1; UNLOCK TABLES; -Original Message- From: Mirco Santori To: [EMAIL PROTECTED] Sent: 6/3/04 2:17 PM Subject: VB .NET MYSQL - LOCK TABLES Hello MySql List, I have create connection with VB .NET MySql, and now i must use the LOCK TABLES statment. I want to know how i can use this sintax .. i must open connection - begin the LOCK TABLES - begin the select statment, and then UNLOCK TABLES .. I think so that is not correctly .. where i can see the example for this problem ? Excusme for my english .. i don's speak and write very well!! Mirco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VB .NET MYSQL - LOCK TABLES
Put of all three lines of your sql in one string variable with the semi-colons and execute it as one command. Works for me using the ByteFX libraries. Larry - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Mirco Santori ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 03, 2004 1:25 PM Subject: RE: VB .NET MYSQL - LOCK TABLES You will need to reuse your database connection, do not open a new connection with each call or the lock will not be there. LOCK TABLE table1 READ; SELECT * FROM table1; UNLOCK TABLES; -Original Message- From: Mirco Santori To: [EMAIL PROTECTED] Sent: 6/3/04 2:17 PM Subject: VB .NET MYSQL - LOCK TABLES Hello MySql List, I have create connection with VB .NET MySql, and now i must use the LOCK TABLES statment. I want to know how i can use this sintax .. i must open connection - begin the LOCK TABLES - begin the select statment, and then UNLOCK TABLES .. I think so that is not correctly .. where i can see the example for this problem ? Excusme for my english .. i don's speak and write very well!! Mirco -- 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]
Blocking Selects with LOCK TABLES
According to the docs (http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html) : If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can read from or write to the table. Other threads are blocked. So, I've got two threads going (T1, T2). T1 issues LOCK TABLES transtest WRITE; But when I go to T2, I can still issue: SELECT * FROM transtest; and retrieve all the data. I CANNOT update, so I know the command is at least partially working. As I understand it, I'm supposed to see a message from T2 that says something about This table has been locked with the LOCK TABLES command. It is an InnoDB table, if that matters. Thanks, Lou
Re: Blocking Selects with LOCK TABLES
Lou Olsten [EMAIL PROTECTED] wrote: According to the docs (http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html) : If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can read from or write to the table. Other threads are blocked. So, I've got two threads going (T1, T2). T1 issues LOCK TABLES transtest WRITE; But when I go to T2, I can still issue: SELECT * FROM transtest; and retrieve all the data. I CANNOT update, so I know the command is at least partially working. As I understand it, I'm supposed to see a message from T2 that says something about This table has been locked with the LOCK TABLES command. It is an InnoDB table, if that matters. Looks like it's a query cache issue. In this case you get result from the cache. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blocking Selects with LOCK TABLES
At 12:40 -0400 on 05/10/2004, Lou Olsten wrote about Blocking Selects with LOCK TABLES: x-charset iso-8859-1According to the docs (http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html) : If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can read from or write to the table. Other threads are blocked. So, I've got two threads going (T1, T2). T1 issues LOCK TABLES transtest WRITE; But when I go to T2, I can still issue: SELECT * FROM transtest; and retrieve all the data. I CANNOT update, so I know the command is at least partially working. As I understand it, I'm supposed to see a message from T2 that says something about This table has been locked with the LOCK TABLES command. Did T2 issue a LOCK TABLES transtest READ; or did you just try doing the read without trying to get a READ Lock? If the latter, then that is your problem. Once you start locking a table, EVERYONE needs to get locks on that table. It is an InnoDB table, if that matters. Thanks, Lou /x-charset -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blocking Selects with LOCK TABLES
Looks like it's a query cache issue. In this case you get result from the cache. That was it. THANKS - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 10, 2004 1:20 PM Subject: Re: Blocking Selects with LOCK TABLES Lou Olsten [EMAIL PROTECTED] wrote: According to the docs (http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html) : If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can read from or write to the table. Other threads are blocked. So, I've got two threads going (T1, T2). T1 issues LOCK TABLES transtest WRITE; But when I go to T2, I can still issue: SELECT * FROM transtest; and retrieve all the data. I CANNOT update, so I know the command is at least partially working. As I understand it, I'm supposed to see a message from T2 that says something about This table has been locked with the LOCK TABLES command. It is an InnoDB table, if that matters. Looks like it's a query cache issue. In this case you get result from the cache. -- 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 -- 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]
LOCK TABLES and SHOW TABLE STATUS FROM - deadlock? (3.23.58)
Hi,
I'm trying to understand LOCKing mechanism, but it is not enough described in the
manual, so I have to test it myself.
To see what happens when I use tables I haven't locked, I created a PHP script like
this:
$sql = USE fakturace; if(!$res =
mysql_query($sql,$spoj)){ decho(' A '.$sError = sql_error(mysql_error(),$sql)); }
$sql = LOCK TABLES $gt_firmy WRITE; if(!$res = mysql_query($sql,$spoj)){ decho('
A '.$sError = sql_error(mysql_error(),$sql)); }
$sql = SELECT * FROM $gt_firmy AS f;if(!$res = mysql_query($sql,$spoj)){
decho(' B '.$sError = sql_error(mysql_error(),$sql)); }
$sql = INSERT INTO test SET id = NULL; if(!$res = mysql_query($sql,$spoj)){
decho(' C '.$sError = sql_error(mysql_error(),$sql)); }
$sql = 'UNLOCK TABLES'; if(!$res = mysql_query($sql,$spoj)){
decho(' Z '.$sError = sql_error(mysql_error(),$sql)); }
The result is expected:
B MySQL error: Table 'f' was not locked with LOCK TABLES
SQL: SELECT * FROM firmy AS f
C MySQL error: Table 'test' was not locked with LOCK TABLES
SQL: INSERT INTO test SET id = NULL
The output doesn't change, until PhpMyAdmin sends
SHOW TABLE STATUS FROM fakturace
This query hangs on the server and no other query to database fakturace can be done.
Is that ok?
That's not all. When I did SHOW PROCESSLIST, I got this:
++---+---+---+-+--+++
| Id | User | Host | db| Command | Time | State | Info
|
++---+---+---+-+--+++
| 1 | fakturace | localhost | fakturace | Sleep | 716 || NULL
|
| 2 | fakturace | localhost | fakturace | Sleep | 606 || NULL
|
| 5 | fakturace | localhost | fakturace | Sleep | 549 || NULL
|
| 7 | fakturace | localhost | fakturace | Sleep | 472 || NULL
|
| 9 | root | localhost | zona3d| Query | 410 | Locked | SHOW TABLE STATUS
FROM `fakturace` |
| 10 | root | localhost | NULL | Query | 47 | Locked | SHOW TABLE STATUS
FROM fakturace |
| 11 | fakturace | localhost | fakturace | Query | 30 | Locked | SELECT * FROM
firmy AS f |
| 12 | root | localhost | NULL | Query | 0| NULL | show processlist
|
++---+---+---+-+--+++
8 rows in set (0.00 sec)
So I killed that threads what locked the table:kill 1; kill 2; kill 5; kill 7;
++---+---+---+-+--+++
| Id | User | Host | db| Command | Time | State | Info
|
++---+---+---+-+--+++
| 1 | fakturace | localhost | fakturace | Killed | 1036 || NULL
|
| 2 | fakturace | localhost | fakturace | Killed | 926 || NULL
|
| 5 | fakturace | localhost | fakturace | Killed | 869 || NULL
|
| 7 | fakturace | localhost | fakturace | Killed | 792 || NULL
|
| 9 | root | localhost | zona3d| Query | 730 | Locked | SHOW TABLE STATUS
FROM `fakturace` |
| 10 | root | localhost | NULL | Query | 367 | Locked | SHOW TABLE STATUS
FROM fakturace |
| 11 | fakturace | localhost | fakturace | Query | 350 | Locked | SELECT * FROM
firmy AS f |
| 12 | root | localhost | NULL | Query | 0| NULL | show processlist
|
++---+---+---+-+--+++
But the queries SHOW TABLE STATUS are still hanging and after a while whole computers
gets stunned, nothingchanges in processlist and the processor runs at 100% (P4 1.5
GHz). While writing this mail, thread id # 11 changed to Reopen tables, then Waiting
for tables, then NULL.
I don't understand it at alll as I am not familiar fwith locking, but i think this
behavior is bad. Should I report this as a bug? I am using 3.23.58 and I DON'T want to
install newer, because 3.x is running on servers I use for hosting.
Thanks, Ondra
