how to get the name of primary key ?
Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
Re: how to get the name of primary key ?
Hi, Use *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()* It will help you. Regards, Thiyaghu CK, MySQL DBA www.mafiree.com 2010/2/1 曹凯 tx...@hotmail.com Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
RE: how to get the name of primary key ?
Hi Thiyaghu, I have already got the last_insert_id. now I wanna know if there are any variables or constants to instead of game_log's primary key cos I don't know its name. Date: Mon, 1 Feb 2010 14:24:59 +0530 Subject: Re: how to get the name of primary key ? From: theyaho...@gmail.com To: tx...@hotmail.com CC: mysql@lists.mysql.com Hi, Use *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()* It will help you. Regards, Thiyaghu CK, MySQL DBA www.mafiree.com 2010/2/1 曹凯 tx...@hotmail.com Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1 _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
Re: how to get the name of primary key ?
On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY'; +-+ | COLUMN_NAME | +-+ | GameLogID | +-+ 1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper
Re: extend via table VS extend via database
If you have performance problems, and no slowlog, maybe it just isn't enabled in your config ? Your indexes may not have changed, but the size of your data has. A query that does a full tablescan will run acceptably on a few thousand records, but be damn slow on a few million - especially as soon as the size of the dataset to be scanned exceeds the system's filesystem cache. Find out what queries are going slow, and use explain to see what you can do about it. On Sun, Jan 31, 2010 at 3:46 PM, Cui Shijun rancp...@gmail.com wrote: I had checked slow log indexes, but no slow log exists, neither did the indexes change since the tables were created. Besides, The way I deleted old data is drop some unused table. No query were performed to those tables when I do that. I guess something really magic had happened when mysql dropped tables in that situation. :-P 2010/1/31 Johan De Meersman vegiv...@tuxera.be: the first things to look at are usually the slowlog and indexes :-) On 1/30/10, Cui Shijun rancp...@gmail.com wrote: Mainly performance issues maintain cost consideration. I've experienced a performance regression a few days ago. The box running mysql got a continuous suddenly-high user% cpu, util I delete some old data( 20G ), which caused by several data migrations. To avoid the above situation, I decided to split the table, so that I can move some of them to other boxes if necessary, to keep the amount of data per mysql in a suitable range. This is certainly not a way of solve a problem completely, but might be a way that works before I figure out what had happened to mysql. 2010/1/29 Johan De Meersman vegiv...@tuxera.be: On Fri, Jan 29, 2010 at 9:52 AM, Cui Shijun rancp...@gmail.com wrote: I'm using innodb engine. There are more and more rows in my table recently, and I've planned to extend the structure. Why do you want to split your table ? What problems are you trying to solve ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. but it seems that a lot of times they are just used simply to use them. I cannot find justification for making databases unnecessarily complex, using subqueries when a simple join is all you need, using views, functions, stored procedures in cases that don't require such features, etc. I agree that a lot of people requiring more powerful hard- and software for their application are simply forgetting that they were supposed to produce a working application and not the most normalized database with all the fancy views and other stuff. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Tom, I noticed the article didn't say how much money you'll save by not paying through the nose for Oracle per server licensing, the cost of upgrading your hardware to get some speed out of Oracle, or the cost of having to hire one or more Oracle administrators to manage and tweak the database. how much does an oracle programmer who can maintain your queries with more than 61 joins cost, in, say, usd/hr? Views :-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to get the name of primary key ?
hi Jesper, thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com From: jes...@noggin.com.au Subject: Re: how to get the name of primary key ? Date: Mon, 1 Feb 2010 20:26:36 +1100 To: tx...@hotmail.com On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID | +-+1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper _ Windows Live社区两周年，拿奖过新年！ http://events.livetome.cn/2010/2birthday
Re: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. I didn't say that you said that. You stated that Normalizing gets you -more- tables. It wasn't mentioned why you wanted to normalize the database in the first place. To me your statement looked like it said that normalizing a database would be a requirement for any database. This automatically would produce queries with 61+ joins in them. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. Normalizing has nothing to do with enterprise level, but joining complex views has. Don't ask yourself why you've created the views, just use them in a join. So normalize each database because you may want to create constraints in some situations? This is the behaviour which causes unnecessarily complex databases, queries and applications. If you ask yourself if normalizing a column in a table is useful and if you really need the constraint and if the view, stored procedure, function or whatever you use is really useful, chances are that the application is a lot simpler, faster and easier to maintain. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is table_open_cache a private cache of a session?
hi, I'm a little confused by the document(http://dev.mysql.com/doc/refman/5.1/en/table-cache.html), which only says: To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. Are sessions using private table caches? if I have M concurrent sessions and N tables, assume each session will access tables one by one, will mysql open M*N tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to get the name of primary key ?
u can also get information of table using use use urdbname mysqlshow create table game_log \G 2010/2/1 曹凯 tx...@hotmail.com hi Jesper, thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com From: jes...@noggin.com.au Subject: Re: how to get the name of primary key ? Date: Mon, 1 Feb 2010 20:26:36 +1100 To: tx...@hotmail.com On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID | +-+1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper _ Windows Live社区两周年，拿奖过新年！ http://events.livetome.cn/2010/2birthday -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Is table_open_cache a private cache of a session?
On Mon, Feb 1, 2010 at 2:21 PM, Cui Shijun rancp...@gmail.com wrote: To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. That literally means that every time a table gets referenced, it is opened anew. Even if you use the same table twice in a single select, it gets opened twice. Are sessions using private table caches? No, as far as I understand the internals there's a global cache, and as soon as a table is no longer in use, the object gets returned for subsequent use by another thread (or free()ing if there's not enough room for something else). if I have M concurrent sessions and N tables, assume each session will access tables one by one, will mysql open M*N tables? Yes, this is correct. This also implies that you need to make sure your system allows sufficient filehandles for the MySQL user/process. Table cache objects don't really use a lot of space, so feel free to set it to a large enough value. I've got hosts where it's set to 30.000, although it's quite rare that they ever get there - I've had one occurrence where it grew to 26.000 open tables, with up to 20 tables being opened per second. Graph it (Open_tables variable) and make sure you don't run into the limit. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Selecting Dates
Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Neither of these work. What am I missing? -ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Selecting Dates
Just trying to help. SELECT * FROM orders WHERE order_date BETWEEN '2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date = '2010-01-01' AND = '2010-01-30' ORDER BY order_date; From: ML mailingli...@mailnewsrss.com To: mysql@lists.mysql.com Sent: Mon, February 1, 2010 8:35:01 AM Subject: Selecting Dates Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Neither of these work. What am I missing? -ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=luckyx_cool_...@yahoo.com
Re: Selecting Dates
Hi What is the datatype of the column order_date. Give the full form of the date for the between condition. Thanks Suresh Kuna MySQL DBA --Original Message-- From: ML To: mysql@lists.mysql.com Subject: Selecting Dates Sent: Feb 1, 2010 7:05 AM Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Neither of these work. What am I missing? -ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
Re: Selecting Dates
Shouldn't it be: SELECT * FROM orders WHERE order_date = '2010-01-01' AND order_date = '2010-01-30' ORDER BY order_date; ? change the = and repeat the column_name. Datetime should be datetime or timestamp; On Mon, Feb 1, 2010 at 8:25 AM, sureshkumar...@gmail.com wrote: Hi What is the datatype of the column order_date. Give the full form of the date for the between condition. Thanks Suresh Kuna MySQL DBA --Original Message-- From: ML To: mysql@lists.mysql.com Subject: Selecting Dates Sent: Feb 1, 2010 7:05 AM Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Neither of these work. What am I missing? -ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante
Query Question
For the given table:
FIELD TYPE COLLATION NULL
KEY DEFAULT Extra PRIVILEGES COMMENT
-
-- -- --- -- ---
---
job_coop VARCHAR(6)latin1_swedish_ci
PRI SELECT,INSERT,UPDATE,REFERENCES
ftp_serverVARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
ftp_login VARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
ftp_password VARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
ftp_mode ENUM('Production','Test','Both') latin1_swedish_ci YES
(NULL) SELECT,INSERT,UPDATE,REFERENCES
ftp_passive ENUM('Normal','Passive') latin1_swedish_ci YES
(NULL) SELECT,INSERT,UPDATE,REFERENCES
url_serverVARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
url_port INT(11) NULL
0SELECT,INSERT,UPDATE,REFERENCES
I have situations where different rows have the same value in the
ftp_server column:
job_coop ftp_server ftp_loginftp_password
ftp_modeftp_passiveurl_serverurl_port
B1502715027dbs.nisc.lanmailroomca15027
Both Normal 15027dbs35000
B1512715027dbs.nisc.lanmailroomca15027
Both Normal 0
B1522715027dbs.nisc.lanmailroomca15027
Test Normal 0
I'd like to update the url_server and url_port fields in this example
for B15127 and B15227 to the values contained in B15027. There are other
examples as well. I would like a query that would update all instances
where the ftp_server values matched and where the url_server and
url_port have no assigned values and they would be updated from the
matching ftp_server that did have values in the url_server and url_port.
I imagine that this might require a join and perhaps a temporary table.
Please advise.
Dirk Bremer - Senior Systems Engineer - Utility - AMS
NISC Lake St. Louis MO - USA Central Time Zone
636-755-2652 fax 636-755-2502
dirk.bre...@nisc.coop www.nisc.coop
Re: 50 things to know before migrating from Oracle to MySQL
database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. I didn't say that you said that. You stated that Normalizing gets you -more- tables. It wasn't mentioned why you wanted to normalize the database in the first place. To me your statement looked like it said that normalizing a database would be a requirement for any database. Yes, that's a good thing, unless it's an OLAP database. It improves data consistency and avoids NULLs in storage, which is good. This automatically would produce queries with 61+ joins in them. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. Normalizing has nothing to do with enterprise level, but joining complex views has. Don't ask yourself why you've created the views, just use them in a join. So normalize each database because you may want to create constraints in some situations? Constraints are a good thing. This is the behaviour which causes unnecessarily complex databases, queries and applications. Unless you don't value your data very much, I consider normalizing, database constraints etc a pro, not a con. If you ask yourself if normalizing a column in a table is useful and if you really need the constraint and if the view, stored procedure, function or whatever you use is really useful, chances are that the application is a lot simpler, faster and easier to maintain. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante -- - 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: 50 things to know before migrating from Oracle to MySQL
Oracle will sell it if they can convince the customer. Any one who has had the pleasure of using Oracle Application Server can attest to that. -- - 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: Selecting Dates
Jim, Shouldn't it be: SELECT * FROM orders WHERE order_date = '2010-01-01' AND order_date = '2010-01-30' ORDER BY order_date; ? change the = and repeat the column_name. Datetime should be datetime or timestamp; Spot on. Thank you for the clarification, obviously a syntax mistake on my part. -ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
First things first: You *are* on InnoDB, which has row-level locking instead of table-level ? On Mon, Feb 1, 2010 at 4:08 PM, Michael Dykman mdyk...@gmail.com wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante -- - 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=vegiv...@tuxera.be -- 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: Is table_open_cache a private cache of a session?
hi, This post( http://bugs.mysql.com/bug.php?id=48929 ) shows that If the number of opened file grows too big, mysql will get error. I'm also confused by the difference relationship between open table and open file descriptor by the table cache. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( 2010/2/1 Johan De Meersman vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 2:21 PM, Cui Shijun rancp...@gmail.com wrote: To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. That literally means that every time a table gets referenced, it is opened anew. Even if you use the same table twice in a single select, it gets opened twice. Are sessions using private table caches? No, as far as I understand the internals there's a global cache, and as soon as a table is no longer in use, the object gets returned for subsequent use by another thread (or free()ing if there's not enough room for something else). if I have M concurrent sessions and N tables, assume each session will access tables one by one, will mysql open M*N tables? Yes, this is correct. This also implies that you need to make sure your system allows sufficient filehandles for the MySQL user/process. Table cache objects don't really use a lot of space, so feel free to set it to a large enough value. I've got hosts where it's set to 30.000, although it's quite rare that they ever get there - I've had one occurrence where it grew to 26.000 open tables, with up to 20 tables being opened per second. Graph it (Open_tables variable) and make sure you don't run into the limit. -- 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: WAMP vs LAMP
Jerry Schwartz wrote: From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] Sent: Thursday, January 28, 2010 2:03 PM To: Jerry Schwartz Subject: Re: WAMP vs LAMP [JS] The file paths were all the same, actually, and the address for MySQL is just “localhost”. [WB]Consider to use MySQL on Unix like environment because the socket file. This way you will get more performance then use TCP/IP on MS Windows. [JS] That’s an interesting suggestion. Windows has socket files, but I’ve never looked at them. In fact, I don’t even know if MySQL can us a socket file and TCP/IP at the same time. We’re going to have more ODBC traffic than web traffic, I expect. On Windows they aren't called sockets, they are named pipes and yes you can be listening to both internal and external connections at the same time. --enabled-named-pipe http://dev.mysql.com/doc/refman/4.1/en/server-options.html#option_mysqld_enable-named-pipe When connecting locally, you have both options at your disposal http://dev.mysql.com/doc/refman/4.1/en/connecting.html There is even another option for Windows, --shared-memory http://dev.mysql.com/doc/refman/4.1/en/server-options.html#option_mysqld_shared-memory This works similar to a named pipe and if you want to enable both (pipes and shares) at the same time, you need to provide different names. The TCP/IP ports are independent and also available. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, 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: WAMP vs LAMP
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 From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] Sent: Friday, January 29, 2010 5:49 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: WAMP vs LAMP Hi JS, I never see socket file on MS Windows...are you sure about it? But, the other question is yes, if you make a connection with the MySQL Server (mysqld) using -h localhost, you will connect with the server using a socket file (linux only), but, if you make using -h 127.0.0.1, TCP/IP will be use. See this: http://dev.mysql.com/doc/refman/5.0/en/connecting.html#option_general_protocol --protocol Value Connection Protocol Allowable Operating Systems TCP TCP/IP connection to local or remote server All SOCKET Unix socket file connection to local server Unix only PIPE Named-pipe connection to local or remote server Windows only MEMORY Shared-memory connection to local server Windows only Source: http://dev.mysql.com/doc/refman/5.0/en/connecting.html -- Wagner Bianchi [JS] Perhaps I misspoke, but the basic principle is the same. On *nix, a socket file is an inode that is used as a handle for processes to “find” the appropriate memory-based data. There’s no data in the file itself. On Windows, IPC via file mapping is pretty close to the same thing. It is one of many IPC mechanisms that Microsoft has implemented over the years. It isn’t mentioned in the table above, but perhaps they included it under the heading of “memory”. Windows also supports the DCE standard. 2010/1/29 Jerry Schwartz jschwa...@the-infoshop.com From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] Sent: Thursday, January 28, 2010 2:03 PM To: Jerry Schwartz Subject: Re: WAMP vs LAMP [JS] The file paths were all the same, actually, and the address for MySQL is just “localhost”. [WB]Consider to use MySQL on Unix like environment because the socket file. This way you will get more performance then use TCP/IP on MS Windows. [JS] That’s an interesting suggestion. Windows has socket files, but I’ve never looked at them. In fact, I don’t even know if MySQL can us a socket file and TCP/IP at the same time. We’re going to have more ODBC traffic than web traffic, I expect. 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 Best regards. -- Wagner Bianchi 2010/1/28 Jerry Schwartz jschwa...@the-infoshop.com From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Wednesday, January 27, 2010 12:18 PM To: Jerry Schwartz Cc: shawn.gr...@sun.com; Daevid Vincent; Dan Nelson; mysql@lists.mysql.com Subject: Re: Event feature already working in Server 5.1.37 On Wed, Jan 27, 2010 at 5:52 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: [JS] I second this. Instead of using a LAMP development environment, I went with WAMP -- even though our production environment was LAMP. Generally a bad idea - you keep running into annoying minor differences between the systems. File paths, for example :-) [JS] The file paths were all the same, actually, and the address for MySQL is just “localhost”. I’ve only run into one incompatibility, and that one bit me yesterday: On Windows, the PHP rand() function has a native range of 1 – 32767. I replaced that with a call to mt_rand(), and all’s right with the world. (Why are we using random numbers? It would take a psychiatric evaluation of my predecessor to determine that.) It was a lot easier than setting up LAMP in a virtual machine. I'll set up up in under an hour, if you want :-) [JS] I’m sure you could. I actually did, before deciding that it wasn’t worth it what with the port forwarding and all. When we shut down our LAMP site for cost reasons, I moved it to a WAMP environment that I bought off the Wait. You shut down machines for cost reasons, and then go buy new ones ? [JS] The one we shut down was externally hosted, and had customer-accessible information on it. When management decided to consolidate our customer-accessible sites in Japan, there was no reason to have our administrative stuff hosted externally. shelf for $800. For that money I got 8GB of RAM, four cores, and a RAID controller. Another $90 for a second drive, and I've got mirroring going. Granted, it's a low-traffic site used for internal administration; but I think this box could handle a lot more traffic than it does. It seems to be loafing all of the time. Oh, probably. Webserving isn't all that hard of a job, if the site is reasonably well-designed. If you're implying that the LAMP setup you had earlier didn't perform quite as well, though, I'll go out on a leg and say that it probably wasn't
Re: Is table_open_cache a private cache of a session?
On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com wrote: I'm also confused by the difference relationship between open table and open file descriptor by the table cache. open table is a MySQL concept. Open file descriptor is an OS concept. A single table (MyISAM) consists of three files: the .frm (description), the .MYD (data) and the .MYI (indices). Thus, a single open table can correspond to multiple open files. Additionally, temp tables, sortfiles and whatnot also consume file descriptors. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table and *there is a cache entry that* is currently not used* - multiple entries can exist for the same table. * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table The thread will get a new cache object that opens that table, yes. Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( I just skimmed over it, but the bug seem related specifically to InnoDB, and to a highly specific file descriptor number being equal to some form of hardcoded limit - maybe different OSes or linux distro's have different values for said limit, or maybe it only occurs under specific conditions. -- 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: WAMP vs LAMP
Thanks for the clarification. As I understand it, the syntax on the client end is to use . as the host name. I was using localhost on the LAMP configuration, and never bothered to change it. With 7 or so web-based users, there isn't much rush. 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 -Original Message- From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] Sent: Monday, February 01, 2010 11:07 AM To: Jerry Schwartz Cc: 'Wagner Bianchi'; mysql@lists.mysql.com Subject: Re: WAMP vs LAMP Jerry Schwartz wrote: From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] Sent: Thursday, January 28, 2010 2:03 PM To: Jerry Schwartz Subject: Re: WAMP vs LAMP [JS] The file paths were all the same, actually, and the address for MySQL is just localhost. [WB]Consider to use MySQL on Unix like environment because the socket file. This way you will get more performance then use TCP/IP on MS Windows. [JS] That's an interesting suggestion. Windows has socket files, but I've never looked at them. In fact, I don't even know if MySQL can us a socket file and TCP/IP at the same time. We're going to have more ODBC traffic than web traffic, I expect. On Windows they aren't called sockets, they are named pipes and yes you can be listening to both internal and external connections at the same time. --enabled-named-pipe http://dev.mysql.com/doc/refman/4.1/en/server- options.html#option_mysqld_enable-named-pipe When connecting locally, you have both options at your disposal http://dev.mysql.com/doc/refman/4.1/en/connecting.html There is even another option for Windows, --shared-memory http://dev.mysql.com/doc/refman/4.1/en/server- options.html#option_mysqld_shared-memory This works similar to a named pipe and if you want to enable both (pipes and shares) at the same time, you need to provide different names. The TCP/IP ports are independent and also available. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.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: Is table_open_cache a private cache of a session?
Got your idea. Thank you very much. Now I know how table cache works :-) For the bug, yes, it's related to the value of FD_SETSIZE, which is limited to 1024 at my RedHat box. Maybe I should update it to a suitable value. 2010/2/2 Johan De Meersman vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com wrote: I'm also confused by the difference relationship between open table and open file descriptor by the table cache. open table is a MySQL concept. Open file descriptor is an OS concept. A single table (MyISAM) consists of three files: the .frm (description), the .MYD (data) and the .MYI (indices). Thus, a single open table can correspond to multiple open files. Additionally, temp tables, sortfiles and whatnot also consume file descriptors. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table and *there is a cache entry that* is currently not used* - multiple entries can exist for the same table. * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table The thread will get a new cache object that opens that table, yes. Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( I just skimmed over it, but the bug seem related specifically to InnoDB, and to a highly specific file descriptor number being equal to some form of hardcoded limit - maybe different OSes or linux distro's have different values for said limit, or maybe it only occurs under specific conditions. -- 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: Is table_open_cache a private cache of a session?
Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com Got your idea. Thank you very much. Now I know how table cache works :-) For the bug, yes, it's related to the value of FD_SETSIZE, which is limited to 1024 at my RedHat box. Maybe I should update it to a suitable value. 2010/2/2 Johan De Meersman vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com wrote: I'm also confused by the difference relationship between open table and open file descriptor by the table cache. open table is a MySQL concept. Open file descriptor is an OS concept. A single table (MyISAM) consists of three files: the .frm (description), the .MYD (data) and the .MYI (indices). Thus, a single open table can correspond to multiple open files. Additionally, temp tables, sortfiles and whatnot also consume file descriptors. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table and *there is a cache entry that* is currently not used* - multiple entries can exist for the same table. * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table The thread will get a new cache object that opens that table, yes. Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( I just skimmed over it, but the bug seem related specifically to InnoDB, and to a highly specific file descriptor number being equal to some form of hardcoded limit - maybe different OSes or linux distro's have different values for said limit, or maybe it only occurs under specific conditions. -- 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=wagnerbianch...@gmail.com
Re: Is table_open_cache a private cache of a session?
Sorry, *open_files_limit... 2010/2/1 Wagner Bianchi wagnerbianch...@gmail.com Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com Got your idea. Thank you very much. Now I know how table cache works :-) For the bug, yes, it's related to the value of FD_SETSIZE, which is limited to 1024 at my RedHat box. Maybe I should update it to a suitable value. 2010/2/2 Johan De Meersman vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com wrote: I'm also confused by the difference relationship between open table and open file descriptor by the table cache. open table is a MySQL concept. Open file descriptor is an OS concept. A single table (MyISAM) consists of three files: the .frm (description), the .MYD (data) and the .MYI (indices). Thus, a single open table can correspond to multiple open files. Additionally, temp tables, sortfiles and whatnot also consume file descriptors. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table and *there is a cache entry that* is currently not used* - multiple entries can exist for the same table. * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table The thread will get a new cache object that opens that table, yes. Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( I just skimmed over it, but the bug seem related specifically to InnoDB, and to a highly specific file descriptor number being equal to some form of hardcoded limit - maybe different OSes or linux distro's have different values for said limit, or maybe it only occurs under specific conditions. -- 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=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Johan De Meersman wrote: First things first: You *are* on InnoDB, which has row-level locking instead of table-level ? Yes, both cli_lock and queue tables are InnoDB. The server is running MySQL 5.1.36. I find it strange that I would have so many of these deadlocks throughout a day when these queries run from 3 processes every 20 seconds. What's the chance that 2 scripts should be executing these queries simultaneously, and even if the probability exists, why is it causing this deadlock error each time? If I break the query into 2 parts ... like SELECT FOR UPDATE followed by the INSERT/UPDATE, would that help fix the errors? What is this error exactly, anyhow? Where is the deadlock ... is it on the select or the insert? -- Dante On Mon, Feb 1, 2010 at 4:08 PM, Michael Dykman mdyk...@gmail.com mailto:mdyk...@gmail.com wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com mailto:da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante -- -- D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is table_open_cache a private cache of a session?
FD_SETSIZE is defined at /usr/include/linux/posix_types.h and /usr/include/bits/typesizes.h ... Set open_files_limit bigger is a way to let mysql use more file descriptors( and thus has the chance to reach the FD_SETSIZE limit ) Also notice in the bug description, innodb_open_files was used, together with innodb_file_per_table. I guess this does not affect the bug, which occurs at sql/mysqld.cc. :-P 2010/2/2 Wagner Bianchi wagnerbianch...@gmail.com: Sorry, *open_files_limit... 2010/2/1 Wagner Bianchi wagnerbianch...@gmail.com Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com Got your idea. Thank you very much. Now I know how table cache works :-) For the bug, yes, it's related to the value of FD_SETSIZE, which is limited to 1024 at my RedHat box. Maybe I should update it to a suitable value. 2010/2/2 Johan De Meersman vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com wrote: I'm also confused by the difference relationship between open table and open file descriptor by the table cache. open table is a MySQL concept. Open file descriptor is an OS concept. A single table (MyISAM) consists of three files: the .frm (description), the .MYD (data) and the .MYI (indices). Thus, a single open table can correspond to multiple open files. Additionally, temp tables, sortfiles and whatnot also consume file descriptors. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table and *there is a cache entry that* is currently not used* - multiple entries can exist for the same table. * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table The thread will get a new cache object that opens that table, yes. Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( I just skimmed over it, but the bug seem related specifically to InnoDB, and to a highly specific file descriptor number being equal to some form of hardcoded limit - maybe different OSes or linux distro's have different values for said limit, or maybe it only occurs under specific conditions. -- 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=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SELECT FOR UPDATE is supposed to lock those rows selected.. an operation in another connection attempting to read or modify those rows gets an error on the lock if it is still in place. That is that SELECT FOR UPDATE is supposed to do. If that is not the behaviour you want, then why are you using the lock? - michael dykman On Mon, Feb 1, 2010 at 12:29 PM, D. Dante Lorenso da...@lorenso.com wrote: Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- - 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: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Try to run SHOW ENGINE INNODB STATUS; Near the top there will be some information on the latest deadlock. That might help you to understand what is deadlocking. Sometimes changing the query or changing the indexes can remove the condition that causes the deadlock. I don't know whether you have triggers on any of your tables? If so that's one place to watch for as well as the deadlock will show up as it is on the original query even if it is a trigger causing it. Jesper On 02/02/2010, at 6:06 AM, Michael Dykman wrote: The SELECT FOR UPDATE is supposed to lock those rows selected.. an operation in another connection attempting to read or modify those rows gets an error on the lock if it is still in place. That is that SELECT FOR UPDATE is supposed to do. If that is not the behaviour you want, then why are you using the lock? - michael dykman On Mon, Feb 1, 2010 at 12:29 PM, D. Dante Lorenso da...@lorenso.com wrote: Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- - 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=jes...@noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
May I suggest this link? I found it useful. I haven't looked but there may be more recent posts with additional information. http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/ Donna From: Jesper Wisborg Krogh jes...@noggin.com.au To: mysql@lists.mysql.com Date: 02/01/2010 03:43 PM Subject: Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Try to run SHOW ENGINE INNODB STATUS; Near the top there will be some information on the latest deadlock. That might help you to understand what is deadlocking. Sometimes changing the query or changing the indexes can remove the condition that causes the deadlock. I don't know whether you have triggers on any of your tables? If so that's one place to watch for as well as the deadlock will show up as it is on the original query even if it is a trigger causing it. Jesper On 02/02/2010, at 6:06 AM, Michael Dykman wrote: The SELECT FOR UPDATE is supposed to lock those rows selected.. an operation in another connection attempting to read or modify those rows gets an error on the lock if it is still in place. That is that SELECT FOR UPDATE is supposed to do. If that is not the behaviour you want, then why are you using the lock? - michael dykman On Mon, Feb 1, 2010 at 12:29 PM, D. Dante Lorenso da...@lorenso.com wrote: Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- - 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=jes...@noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdevaudre...@sironahealth.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
I NEED HELP ON 'SOURCE(\.) MYSQL
Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
How did you type the source command? Did you remember not to quote the file name? E.g. mysql SOURCE schema.sql is the correct way. Jesper On Tue, 2 Feb 2010 10:38:08 kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
On 02/01/2010 06:38 PM, kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi mysql looks in the current directory for the source file. That the directory where you ran the mysql client from. The quickest fix is to give the complete path to the file, e.g. source /home/me/mysourcefile.sql (You say yoiu changed your current directory to mysql but didn't give us the complete path. There are multiple mysql directories in most configurations.) --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
Error 2 is No such file or directory So, probably the file is not in the right path or you need to explicitly provide the path to the file. Carlos On 2/1/2010 5:38 PM, kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
what everyone else said... also, is there a longer version of the error? or is it literally error 2 or error 22? Colin On February 1, 2010 06:38:08 pm kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi -- Your love life will be happy and harmonious. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
Give the ful path of file name with in '' and try it out Thanks Suresh Kuna MySQL DBA --Original Message-- From: Colin Streicher To: mysql@lists.mysql.com Cc: kebede teferi ReplyTo: co...@obviouslymalicious.com Subject: Re: I NEED HELP ON 'SOURCE(\.) MYSQL Sent: Feb 2, 2010 7:33 AM what everyone else said... also, is there a longer version of the error? or is it literally error 2 or error 22? Colin On February 1, 2010 06:38:08 pm kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi -- Your love life will be happy and harmonious. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
MY PROBLEM WITH THE SOURCE(\.) IS SOLVED
I thank you all who responded to solve my problem in using the source command to execute a mysql script saved on a notepad. Thank you, for your tips, I've solved the problem. When I was using the \.create.sql at the prompt I wasn't giving the full path where 'create.sql' is residing. Once I did, that thanks to you all, I saw my command line streaming with data and finally the result was in my database. Thanks. Kebede
hi help to take backup-mysql-windows-xp
hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me
Re: hi help to take backup-mysql-windows-xp
Please paste the error and don't give the spave between -p and password. On Tue, Feb 2, 2010 at 12:05 PM, muralikrishna g muralikrishn...@gmail.comwrote: hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me -- Thanks Suresh Kuna MySQL DBA
Re: hi help to take backup-mysql-windows-xp
hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me mysql mysqldump -u root -p dbadmin murali backupfile.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump -u root -p dbadmin murali backupfile.sql' at line 1 mysql mysqldump -uroot -pdbadmin murali backupfile.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump -uroot -pdbadmin murali backupfile.sql' at line 1 mysql On 2/2/10, Suresh Kuna sureshkumar...@gmail.com wrote: Please paste the error and don't give the spave between -p and password. On Tue, Feb 2, 2010 at 12:05 PM, muralikrishna g muralikrishn...@gmail.com wrote: hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me -- Thanks Suresh Kuna MySQL DBA
Re: hi help to take backup-mysql-windows-xp
Hi Murali, You have to execute it outside mysql prompt i.e command prompt. Thanks Suresh Kuna MySQL DBA Sent from BlackBerry® on Airtel -Original Message- From: muralikrishna g muralikrishn...@gmail.com Date: Tue, 2 Feb 2010 12:17:24 To: Suresh Kunasureshkumar...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: hi help to take backup-mysql-windows-xp hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me mysql mysqldump -u root -p dbadmin murali backupfile.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump -u root -p dbadmin murali backupfile.sql' at line 1 mysql mysqldump -uroot -pdbadmin murali backupfile.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump -uroot -pdbadmin murali backupfile.sql' at line 1 mysql On 2/2/10, Suresh Kuna sureshkumar...@gmail.com wrote: Please paste the error and don't give the spave between -p and password. On Tue, Feb 2, 2010 at 12:05 PM, muralikrishna g muralikrishn...@gmail.com wrote: hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me -- Thanks Suresh Kuna MySQL DBA
RE: hi help to take backup-mysql-windows-xp
Hey guys Try using this option from the command prompt via run Mysqldump.exe -u root -p --all-database backup.txt This should work. Thanks, Dilipkumar Database Engineering phone: +91-44-6637 3167 mailto: dilipkumar.par...@eds.com Pager mail:9884430...@nma.vodafone.in -Original Message- From: sureshkumar...@gmail.com [mailto:sureshkumar...@gmail.com] Sent: Tuesday, February 02, 2010 12:22 PM To: muralikrishna g Cc: mysql@lists.mysql.com Subject: Re: hi help to take backup-mysql-windows-xp Hi Murali, You have to execute it outside mysql prompt i.e command prompt. Thanks Suresh Kuna MySQL DBA Sent from BlackBerry® on Airtel -Original Message- From: muralikrishna g muralikrishn...@gmail.com Date: Tue, 2 Feb 2010 12:17:24 To: Suresh Kunasureshkumar...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: hi help to take backup-mysql-windows-xp hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me mysql mysqldump -u root -p dbadmin murali backupfile.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump -u root -p dbadmin murali backupfile.sql' at line 1 mysql mysqldump -uroot -pdbadmin murali backupfile.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump -uroot -pdbadmin murali backupfile.sql' at line 1 mysql On 2/2/10, Suresh Kuna sureshkumar...@gmail.com wrote: Please paste the error and don't give the spave between -p and password. On Tue, Feb 2, 2010 at 12:05 PM, muralikrishna g muralikrishn...@gmail.com wrote: hi to all.. i was in need to take backup of database.. i am using sql server version:5.0.27-coomunity-nt i have tried by using mysqldump -u name -p password database_name backup.sql; but i am getting error.. i am using windows xp system.. please help me -- Thanks Suresh Kuna MySQL DBA
