Re: stunningly slow query
On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote: To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Subject: Re: stunningly slow query [EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM: snip Can you post your show create table tbl_name statement for these tables that involve slow queries? | old_crumb |CREATE TABLE `old_crumb` ( `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', `actual_Time` datetime NOT NULL default '-00-00 00:00:00', snip `last_Modified` datetime default NULL, PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), KEY `old_crumb_ix_reported_Time` (`reported_Time`), KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 COMMENT='List of breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' | I'm no DB expert Chris but do you really need to create a primary key index over 4 columns? What about something simple and possibly faster like adding a seperate ID primary key column to the table like: | old_crumb |CREATE TABLE `old_crumb` ( `ID` int unsigned not null auto_increment `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', snip PRIMARY KEY (`ID`), snip An unsigned int will take an extra 4 bytes of storage space per row, and will give you an index range of 0 - 4294967295. If that is not enough range, an unsigned bigint will take an extra 8 bytes of storage space, and will give you an index range of 0 - 18446744073709551615. Although this will increase the amount of storage space required in the .MYD file, it may also decrease the amount of space required in the .MYI index file, as you would not be needing to store multi-column indexes. Keith, Your method won't guarantee that there are no rows where the combination of the values in those four columns fails to repeat in any other row. To do that would require an EXTRA four-column unique index of type UNIQUE. Your proposal would actually make the situation worse as now there would be two indexes to maintain to achieve the same effect as the previous single PK. Thankyou for your expert reply Shawn. Is it not possible to mark each of those those column values as UNIQUE without them becoming a part of the index as well? Or is this a contradiction in terms? Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Keith, Your method won't guarantee that there are no rows where the combination of the values in those four columns fails to repeat in any other row. To do that would require an EXTRA four-column unique index of type UNIQUE. Your proposal would actually make the situation worse as now there would be two indexes to maintain to achieve the same effect as the previous single PK. Thankyou for your expert reply Shawn. Is it not possible to mark each of those those column values as UNIQUE without them becoming a part of the index as well? Or is this a contradiction in terms? In theory it would be possible to add a uniqueness constraint to a column without an index, but this would cause a full table scan on every INSERT. AFAIK therefore MySQL automatically puts an index on this column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 28 from table handler
Quoting Jacques Brignon ([EMAIL PROTECTED]): 1030 : Got error 28 from table handler % perror 28 OS error code 28: No space left on device Any idea of what might cause that, and what should be done to correct this situation? Aparently the disk that stores your MySQL datadirectory is full. Either you or your hoster should clean it up ;) With regards, Sander. -- | Junk is something you've kept for years and throw away three weeks | before you need it. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On 3/29/2006 2:10 PM, Jorrit Kronjee wrote: It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Mark, We've been testing it over the weekend and it seems that the bugs we ran into before completely disappeared. Thanks for your support! Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trans.: Re: error 28 from table handler
Oops forgot to include the list - Message transféré de Jacques Brignon [EMAIL PROTECTED] - Date : Mon, 03 Apr 2006 10:07:58 +0200 De : Jacques Brignon [EMAIL PROTECTED] Adresse de retour :Jacques Brignon [EMAIL PROTECTED] Sujet : Re: error 28 from table handler À : Jos Elkink [EMAIL PROTECTED] Well I tried, to the extent I was able to, as long as I do not have full access to the server! I am not running it, I have access to some admin capabilities through DirectAdmin web interface ... which was not running. In the mean time I talked to the server admin and he unocked the problem, a temp was getting full. Not sure which temp file or folder. In order to avoid this to happen agiain, the question I then have is to understand if my application (PHP using MySQL) can be the cause of that? Selon Jos Elkink [EMAIL PROTECTED]: Hi Jacques, I presume you already checked whether your harddisk is full ? Jos On 4/2/06, Jacques Brignon [EMAIL PROTECTED] wrote: Hi, I am getting the following error when executing a simple SELECT query which used to work: 1030 : Got error 28 from table handler I did not found that in the manual. When googleing it it seems it may be related to disk space ... The server hosting my application is running MySQL 4.0.23. My db uses MyISAM tables Any idea of what might cause that, and what should be done to correct this situation? -- Jacques -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jos Elkink Postgraduate Student [EMAIL PROTECTED] Department of Political Science http://jaeweb.cantr.netTrinity College Dublin -- Jacques Brignon - Fin du message transféré - -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 28 from table handler
Thanks, Table corruption is exactly what I fear as a result of this problem. As long as things semm to work again now after the hister cleaned some temp files, how do I check my db for possible table corruptions? Selon prathima rao [EMAIL PROTECTED]: if u have shut down the system then the table would have been corrupted - Original Message - From: Jacques Brignon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 03, 2006 1:58 AM Subject: error 28 from table handler Hi, I am getting the following error when executing a simple SELECT query which used to work: 1030 : Got error 28 from table handler I did not found that in the manual. When googleing it it seems it may be related to disk space ... The server hosting my application is running MySQL 4.0.23. My db uses MyISAM tables Any idea of what might cause that, and what should be done to correct this situation? -- Jacques -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.4/299 - Release Date: 3/31/2006 -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Jorrit Kronjee wrote: On 3/29/2006 2:10 PM, Jorrit Kronjee wrote Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Mark, We've been testing it over the weekend and it seems that the bugs we ran into before completely disappeared. Thanks for your support! Jorrit Hi Jorrit, Great news - that bug was one that we tried for a long time to replicate (as can be seen from the bug report), eventually I managed to repeat it a few months ago, and we got the fix turned around fairly quickly - so it's a fairly obvious one every time I see it now ;) Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Way of declaring variables?
Got the snippet from the mysql website: select @last := last_insert_id() i av the hunch that is to assign the variable 'last' to the last_insert_id(). Im i right? is placing @ before a word mysql's way of declaring variables? what's the work of the colon preceeding the equal sign? -- http://ngBot.com | http://wap.ngBot.com Nigeria's #1 website directory.
Unsigned
What does it mean declaring a table unsigned? An example is a s below: id smallint unsigned not null auto_increment
Re: Unsigned
Hi Yemi, It means the variable contains only positive values - otherwise one bit would be used to denote whether it's positive or negative. From the documentation: SMALLINT[(M)] [UNSIGNED] [ZEROFILL] A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. Jos On 4/3/06, Yemi Obembe [EMAIL PROTECTED] wrote: What does it mean declaring a table unsigned? An example is a s below: id smallint unsigned not null auto_increment -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Way of declaring variables?
First of all... yes... in MySQL variables are declared and used with @ Now for the : ... there is no particular role... they're there because otherwise the SQL parser will be confused... *SELECT column1, @neededValue=column2 FROM table_name LIMIT 1* *SELECT column1, @neededValue:=column2 FROM table_name LIMIT 1* In the first SQL the parser would test whether @neededValue is equal tot the value in column2 instead of assigning to @neededValue the value from column2 This confusion can happen only in SELECT statements... so you are required to add : only in SELECT statements. In SET statements you don't need that... you can write: *SET @last = last_insert_id()* Or with values from outside MySQL: *SET @iNeedThis = 'someText';* Then use both in an insert statement: *INSERT INTO table (lid, txt) VALUES (@last, @iNeedThis);* Hope this cleared up things ! -- Gabriel PREDA Senior Web Developer On 4/3/06, Yemi Obembe [EMAIL PROTECTED] wrote: Got the snippet from the mysql website: select @last := last_insert_id() i av the hunch that is to assign the variable 'last' to the last_insert_id(). Im i right? is placing @ before a word mysql's way of declaring variables? what's the work of the colon preceeding the equal sign?
on select statements
what is the difference between this: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a; and this: 'SELECT * FROM tbl LIMIT 1 And then this: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; and this: 'SELECT * FROM tbl LIMIT 1,5
Syntax Question Constraint, Index
I ran into some syntax over the weekend, that I am trying to make sense of. Here is the create table statements. Drop table if exists events Drop table if exists locations Create table events ( uid BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255), start_date DATE, duration INTEGER, location_id BIGINT, primary key (uid) ); Create table locations ( uid BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255), address VARCHAR(255), primary key (uid) ) Alter table events add index (location_id), add Constraint FKB307E11920EBB9E5 foreign key (location_id) references locations(uid) // Here is my conclusion, and I was hoping someone may back this up. Events has a primary key of UID that is auto_incremeneted. Locations has a primary key of UID that is also incremented. The constraint and index are where I have questions. What is the index and constraint doing? I can't seem to get my mind around what that alter statement is trying to accomplish. Thanks,
Re: Is port forwarded connection taken as local?
Bing Du wrote: Hello everyone, What I'm after is trying to figure out a way to centrally and remotely managing (e.g. on server1) our MySQL servers (server2 is an example) on different machines. Right now, these MySQL servers are all set up to only accept logons from localhost. My questions: 1. server1 has to be able to connect to server2 directly via SSH, right? 2. On server2, does 'grant ...to [EMAIL PROTECTED] identified by ...' have to be done? I've been looking through the relevent threads regarding how to make port forwarding for MySQL work. I saw one thread mentioned that port forwarded MySQL connection was taken as a local connection. But my own testing cannot agree with that. I'm confused. Here is what I did: On server1: server1% ssh -2 -l myusername -N -L 3307:server2:3306 server2 server1% mysql -P 3307 (in another term window) ERROR 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES) You need to grant permissions to 'myusername'@'localhost' as indicated in the line above. Yes, I understand the error message. What confused me was if I connected as 'myusername' directly on server2, there was not any problem at all as what was shown in the following in my original post. So in another word, 'myusername'@'localhost' was denied access if connecting via port forwarding, but it's allowed access if connecting from localhost. Hence my question, if port forwarded connections are really taken as local connections??? But on server2, the following command works fine. server2% mysql -h localhost -u myusername -p I'd appreciate if anybody would shed some light. Bing Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax Question Constraint, Index
Create table events ( uid BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255), start_date DATE, duration INTEGER, location_id BIGINT, primary key (uid) ); Create table locations ( uid BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255), address VARCHAR(255), primary key (uid) ) Alter table events add index (location_id), add Constraint FKB307E11920EBB9E5 foreign key (location_id) references locations(uid) // Here is my conclusion, and I was hoping someone may back this up. Events has a primary key of UID that is auto_incremeneted. Locations has a primary key of UID that is also incremented. The constraint and index are where I have questions. What is the index and constraint doing? I can't seem to get my mind around what that alter statement is trying to accomplish. Well, the index part is adding, guess what, an index for column location_id, and the constraint part is adding a referential constraint. That is, values in column events.location_id need to exist in table locations.uid. The referential constraint is only enforced for InnoDB tables. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Undelete rows with .MYD-File?
Hi! Today i've dropped a MySQL-table and realized seconds later that i've dropped the wrong one.. I've saved the .MYD-File and tried to recover it with a tool (MySQLRecovery 1.5), but it just recovered the structure and not the data (about 620 rows). I've searched now all the day and not found anything. Maybe someone knows how to bring the data back or has got any hint for me? Binary log is not enabled unfortunately... http://www.swissmade.com/mysql/shop_item.MYD http://www.swissmade.com/mysql/shop_item.MYI http://www.swissmade.com/mysql/shop_item.frm Thanks a lot! Nico -- Nico Schefer [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
If those three files were backed up some where before you dropped the table all you need to do is to copy them back into the data dir, and things should be ok again. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006, Nico Schefer wrote: To: mysql@lists.mysql.com From: Nico Schefer [EMAIL PROTECTED] Subject: Undelete rows with .MYD-File? Hi! Today i've dropped a MySQL-table and realized seconds later that i've dropped the wrong one.. I've saved the .MYD-File and tried to recover it with a tool (MySQLRecovery 1.5), but it just recovered the structure and not the data (about 620 rows). I've searched now all the day and not found anything. Maybe someone knows how to bring the data back or has got any hint for me? Binary log is not enabled unfortunately... http: //www.swissmade.com/mysql/shop_item.MYD http: //www.swissmade.com/mysql/shop_item.MYI http: //www.swissmade.com/mysql/shop_item.frm Thanks a lot! Nico -- Nico Schefer [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to quit a (CLI) script ?
Hi everyone, I use many scripts that are started from the CLI (MySQL 5.0.15). And if errors occur, I want to quit a script. I looked at: http://dev.mysql.com/doc/refman/5.0/en/show-errors.html and found Show Count(*) Errors; and for Select @@error_count; But how to quit a CLI script, if @@error_count 0 ? Any ideas are most welcome. TIA, Cor
Re: Undelete rows with .MYD-File?
I have downloaded the table files and this is what I get: mysql use swissmade; Database changed mysql show tables; +-+ | Tables_in_swissmade | +-+ | shop_item | +-+ 1 row in set (0.00 sec) mysql select * from shop_item \G Empty set (0.00 sec) Do you have any copies of the table files that you have not run the recovery program on? Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006, Nico Schefer wrote: To: mysql@lists.mysql.com From: Nico Schefer [EMAIL PROTECTED] Subject: Undelete rows with .MYD-File? Hi! Today i've dropped a MySQL-table and realized seconds later that i've dropped the wrong one.. I've saved the .MYD-File and tried to recover it with a tool (MySQLRecovery 1.5), but it just recovered the structure and not the data (about 620 rows). I've searched now all the day and not found anything. Maybe someone knows how to bring the data back or has got any hint for me? Binary log is not enabled unfortunately... http: //www.swissmade.com/mysql/shop_item.MYD http: //www.swissmade.com/mysql/shop_item.MYI http: //www.swissmade.com/mysql/shop_item.frm Thanks a lot! Nico -- Nico Schefer [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
IIRC there may be a hidden field in each table row that mysql uses to mark that row as deleted. I'm not sure if you can use some mysql utility program such as myisamchk to undelete the rows. This may be possible. Regards Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote: To: mysql@lists.mysql.com From: [EMAIL PROTECTED] Subject: Re: Undelete rows with .MYD-File? I have downloaded the table files and this is what I get: mysql use swissmade; Database changed mysql show tables; +-+ | Tables_in_swissmade | +-+ | shop_item | +-+ 1 row in set (0.00 sec) mysql select * from shop_item \G Empty set (0.00 sec) Do you have any copies of the table files that you have not run the recovery program on? Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006, Nico Schefer wrote: To: mysql@lists.mysql.com From: Nico Schefer [EMAIL PROTECTED] Subject: Undelete rows with .MYD-File? Hi! Today i've dropped a MySQL-table and realized seconds later that i've dropped the wrong one.. I've saved the .MYD-File and tried to recover it with a tool (MySQLRecovery 1.5), but it just recovered the structure and not the data (about 620 rows). I've searched now all the day and not found anything. Maybe someone knows how to bring the data back or has got any hint for me? Binary log is not enabled unfortunately... http: //www.swissmade.com/mysql/shop_item.MYD http: //www.swissmade.com/mysql/shop_item.MYI http: //www.swissmade.com/mysql/shop_item.frm Thanks a lot! Nico -- Nico Schefer [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to quit a (CLI) script ?
I am not sure if you can do this from the Mysql CLI, but a better way of doing this would be to use an external script like PHP, PERL, BASH, etc as they have a better way of error checking againest Mysql. Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/3/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi everyone, I use many scripts that are started from the CLI (MySQL 5.0.15). And if errors occur, I want to quit a script. I looked at: http://dev.mysql.com/doc/refman/5.0/en/show-errors.html and found Show Count(*) Errors; and for Select @@error_count; But how to quit a CLI script, if @@error_count 0 ? Any ideas are most welcome. TIA, Cor
Re: Strange thing
So what you are doing here is sending some sql statements from the dump to mysql CLI, so all that in going into the pipe is just plain text output from the dump, it does pass in any auth parameters, so for your script to run just add the required user, pwd etc as arguments to the mysql cli utility, and it will happily run. like this below #mysqldump ... | mysql -uuser -ppassword Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/1/06, Anton Statutov [EMAIL PROTECTED] wrote: On FreeBSD 5.3. MySQL 4.1.15 unix pipeline like mysqldump ... | mysql ... causes an auth error in the second command, even different databases are used. What does it mean? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is port forwarded connection taken as local?
in port forwarding , all you are saying is !!ok, if server1 gets any connection on port 3307 then forward that connection to port 3306 on server2, so essentially the connection on server2 for Mysql is coming from the client on server1 , so the user you are using to connect to server2 from server1 has to have permissions to connect to server2 from server1 ( Note: the user might have permission to connect to server2 from server2 itself, but not from server1). So please add that permissions appropriately on server2 as you told earlier On server2 GRANT ... TO 'someuser''@'server1' identified by 'password' That would solve it ... You also asked if port forwarded connections are really taken as local connections??? in this case a connection on server1 was port forwarded to server2 , so server2 does not see this connection as being local... Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/3/06, Bing Du [EMAIL PROTECTED] wrote: Bing Du wrote: Hello everyone, What I'm after is trying to figure out a way to centrally and remotely managing (e.g. on server1) our MySQL servers (server2 is an example) on different machines. Right now, these MySQL servers are all set up to only accept logons from localhost. My questions: 1. server1 has to be able to connect to server2 directly via SSH, right? 2. On server2, does 'grant ...to [EMAIL PROTECTED] identified by ...' have to be done? I've been looking through the relevent threads regarding how to make port forwarding for MySQL work. I saw one thread mentioned that port forwarded MySQL connection was taken as a local connection. But my own testing cannot agree with that. I'm confused. Here is what I did: On server1: server1% ssh -2 -l myusername -N -L 3307:server2:3306 server2 server1% mysql -P 3307 (in another term window) ERROR 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES) You need to grant permissions to 'myusername'@'localhost' as indicated in the line above. Yes, I understand the error message. What confused me was if I connected as 'myusername' directly on server2, there was not any problem at all as what was shown in the following in my original post. So in another word, 'myusername'@'localhost' was denied access if connecting via port forwarding, but it's allowed access if connecting from localhost. Hence my question, if port forwarded connections are really taken as local connections??? But on server2, the following command works fine. server2% mysql -h localhost -u myusername -p I'd appreciate if anybody would shed some light. Bing Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: on select statements
There is no logical difference. There's a tiny bit of overhead in preparing and executing the statements, but if you have a query where the limit is variable, a prepared statement might be better than coding within an application, because then when ANY user or application connects it can use that prepared statement. -Sheeri On 4/3/06, Yemi Obembe [EMAIL PROTECTED] wrote: what is the difference between this: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a; and this: 'SELECT * FROM tbl LIMIT 1 And then this: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; and this: 'SELECT * FROM tbl LIMIT 1,5 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limited trafic in 5.0?
You might want to change those. I'm guessing your wait_timeout is set to the default of a day. Is the application letting go of connections? If you do a SHOW PROCESSLIST on the server, is there really only one connection, or are there many? -Sheeri On 4/3/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: mysql SHOW VARIABLES LIKE max%connect%; +--+---+ | Variable_name| Value | +--+---+ | max_connect_errors | 10| | max_connections | 100 | | max_user_connections | 0 | +--+---+ 3 rows in set (0.06 sec) mysql Citando sheeri kritzer [EMAIL PROTECTED]: It's possible that you have max_connections set to something very low, and/or the application is not letting go of MySQL connections. What does the following show you? mysql SHOW VARIABLES LIKE max%connect%; -Sheeri On 4/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have a program that uses MySql. When using a MySql 5.0 server, the MySql Administrator shows in the health section that the server never passes a perfectly stable limit of 9.1~9.2 KB/s of trafic and never shows more that 1 query (per second). Looks like some kind of configuration limitation!? This makes my application absurdly slow. My mysql 5.0 server was installed on windows with all default settings. Any hints at what could be wrong and how to change it? __ O email preferido dos portugueses agora com 2 000 MB de espaço e acesso gratuito à Internet http://www.portugalmail.pt/2000mb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Email gratuito com 2 000 MB Espaço para guardar 1 milhão de mensagens http://www.portugalmail.pt/2000mb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
karsites:/var/swissmade # ./myisamchk -dvv shop_item.MYI MyISAM file: shop_item.MYI Record format: Packed Character set: latin1_swedish_ci (8) File-version:1 Creation time: 2006-03-31 13:59:48 Status: open,changed Auto increment key: 1 Last value: 673 Data records: 0 Deleted blocks: 675 Datafile parts: 675 Deleted data: 33760 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 Datafile length: 33760 Keyfile length: 8192 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 295 It looks like someone has packed this table with myisampack, which means it's read only. Did you use myisampack on the table before it got dropped Nico? Also, did mysql die when the table was in use? I have tries to run myisamchk -r shop_item.MYI but this sets the *.MYD file to zero. I don't know if it is possible to recover data from a packed table. AFAIK the packing process is one way, and you may need the original non-compressed table to get your data. Regards Keith On Mon, 3 Apr 2006, Nico Schefer wrote: To: [EMAIL PROTECTED] From: Nico Schefer [EMAIL PROTECTED] Subject: Re: Undelete rows with .MYD-File? Hi Keith, thanks for your proposal with myisamchk. If i'm using the myisamchk, it finds the deleted rows, but i have not found a way to restore them and i can't find nothing in the manual.. Checking MyISAM file: shop_item.MYI Data records: 0 Deleted blocks: 675 myisamchk.exe: warning: 1 client is using or hasn't closed the table properly - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check record links MyISAM-table 'shop_item.MYI' is usable but should be fixed Does anybody know how to restore the data this way? Thanks a lot, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Sum with union, tricky question perhaps not for you
The solution is to redesign your tables. You need to split into separate columns the values you want to maintain. You do not want to keep the flat file design you are currently trying to use. CREATE TABLE calendar ( objectid, year, dayofyear, ... other fields... ) CREATE TABLE price ( objectid, year, dayofyear, price ) Having a separate column for each day of the year may make sense to a person but as you have discovered, it is extremely difficult to use for any kind of ad-hoc querying. A more normalized data structure will be almost as efficient in space usage but 1000s of times more efficient for querying. There is no simple way to write a query that spans years with the table structures you currently have. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi thanks for the quick response! Well i think you are right about this. I will probably have to redesign the database. What do you think of using DATE instead of year,dayofyear or is there problems i could get into then. It would be great to be able to use select from price where date=somedate AND date = tosomedate /Henrik _ Hitta rätt på nätet med MSN Search http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index and multiple fields
Markus Fischer wrote: Basically, this means if I've a table like this id1 id2 id3 id4 id5 and I've two different select statements: select * from ... where id1 = .. and id2 = .. and the other being select * from ... where id3 = .. and id4 = .. I would create two indexes, one for id1/id2 and the other for id3/id4 , right? yep. remember that mysql can only use one index per table, and that column order is significant in a multi column index. so if your index was (id1, id2) then a query with where id1 = x and id2 = y would use the index, as would id1 = x. but plain old where id2 = x would not. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restoring database previous mysql version
Dear Sir, I have 8 mb total SQL database, i'm currently using mysql version: 4.1.11-debian. Our database uses latin5 character set and collate: latin5_turkish_ci And now... how can i restore this database without problem; previous mysql version? previous mysql version is: 4.0.25-standart Note: I have SSH Access. This is very important for me, thank you. _ Hava durumunu bizden ögrenin ve evden öyle çikin! http://www.msn.com.tr/havadurumu/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restoring database previous mysql version
if I have to put in one line, then just taka a copy (zip, gzip, tar or whatever) of your old mysql data directory, like /usr/local/mysql/data, and then restore it into your new data directory under 4.1.x, and this should be fine assuming you have taken care of other things, also please refer to the doc on mysql for the upgrade http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html http://dev.mysql.com/doc/refman/4.1/en/upgrading-to-arch.html Kishore Jalleda http://kjalleda.googlepages.com On 4/3/06, murat . [EMAIL PROTECTED] wrote: Dear Sir, I have 8 mb total SQL database, i'm currently using mysql version: 4.1.11-debian. Our database uses latin5 character set and collate: latin5_turkish_ci And now... how can i restore this database without problem; previous mysql version? previous mysql version is: 4.0.25-standart Note: I have SSH Access. This is very important for me, thank you. _ Hava durumunu bizden ögrenin ve evden öyle çikin! http://www.msn.com.tr/havadurumu/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
As my server does not get alot of traffic I tend to shutdown mysql, and do an OS copy of the complete /var/lib/mysql directory to another partition on another drive. Then restart mysql again. This may not be a feasable option on a busy server. Obviously each person has their way of doing backups. I do need to study the manual myself on all available backup options. Regards Keith On Mon, 3 Apr 2006, Nico Schefer wrote: To: [EMAIL PROTECTED] From: Nico Schefer [EMAIL PROTECTED] Subject: Re: Undelete rows with .MYD-File? Hi Keith It looks like someone has packed this table with myisampack, which means it's read only. Did you use myisampack on the table before it got dropped Nico? Thanks a lot for looking at the problem. As far as i know the table has not been compressed, and i've not used myisampack. I'v tried to run myisamchk -e -r as well, but is has trunctated my MYD-File as well. I think i have to live with it, i begun to recunstruct the data by hand wich took me the whole day now and i'm not finished by far.. but well, i'll certainly do some backups now ;-) Thanks and greetings, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: differences between varchar and text fields
On 2006-01-20, at 01:13, Tucker Cunningham wrote:
So, my question is: what are the pros and cons of using varchar
vs. text/longtext? Right now, longtext seems to be the best option
(it provides most flexibility in data that can be stored, at only a
2-byte-per-row storage premium) There must be some drawback, what
am I missing? Thanks for your help
Using VARCHAR instead of LONGTEXT for no reason you misinform the
optimizer giving it less data to reason out how to go about to perform
faster calculations and give you results You (or your program) - that
is data - expect. Think of it as reverse EXPLAIN.
--
Seks, seksić, seksolatki... uri: news:pl.soc.seks.moderowana ,
http://www.okcupid.com/tests/take?testid=7062295162911411482 ,
If you could only see what love has made of me, I'd... { (.A.)' }
no longer be in *your* mind the mind the difficult kind...{ A.A }
smime.p7s
Description: S/MIME cryptographic signature
Re: Undelete rows with .MYD-File?
Thankyou for your reply Dilipkumar. Much appreciated. Regards Keith On Tue, 4 Apr 2006, Dilipkumar wrote: To: [EMAIL PROTECTED] From: Dilipkumar [EMAIL PROTECTED] Subject: Re: Undelete rows with .MYD-File? Hi, If it is a busry server you cannot copy MYD FRM file * MYI files to another data ditrectory. Try to take a dump or try using snapshot your datadirectory. If it is a less MB database you can user as: In mysql prompt. flush tables with read locks dont exit you mysql terminal Open an another terminal tar -cvzf you mysql old datadirectory and then after doing so give in 1st prompt flush tables. Then Untar your tar files to your new data-direcotry. This might help you out. [EMAIL PROTECTED] wrote: As my server does not get alot of traffic I tend to shutdown mysql, and do an OS copy of the complete /var/lib/mysql directory to another partition on another drive. Then restart mysql again. This may not be a feasable option on a busy server. Obviously each person has their way of doing backups. I do need to study the manual myself on all available backup options. Regards Keith On Mon, 3 Apr 2006, Nico Schefer wrote: To: [EMAIL PROTECTED] From: Nico Schefer [EMAIL PROTECTED] Subject: Re: Undelete rows with .MYD-File? Hi Keith It looks like someone has packed this table with myisampack, which means it's read only. Did you use myisampack on the table before it got dropped Nico? Thanks a lot for looking at the problem. As far as i know the table has not been compressed, and i've not used myisampack. I'v tried to run myisamchk -e -r as well, but is has trunctated my MYD-File as well. I think i have to live with it, i begun to recunstruct the data by hand wich took me the whole day now and i'm not finished by far.. but well, i'll certainly do some backups now ;-) Thanks and greetings, Nico -- Thanks Regards, Dilipkumar DBA Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
// confusing syntax error when importing //
--apologies if you receive this email from another account--
hi,
i exported a *.sql using phpMyAdmin on a system running:
mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i486) using
readline 5.1
i'm trying to import it from this localhost to a remote host
running:
mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i486) using
readline 5.1
the 13MB file fails during import with this error:
//-
Error
SQL query:
-- phpMyAdmin SQL Dump
-- version 2.8.0.2-Debian-3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 03, 2006 at 07:09 PM
-- Server version: 5.0.19
-- PHP Version: 4.4.2-1+b1
--
-- Database: `nuke`
--
--
--
-- Table structure for table `cur`
--
CREATE TABLE `cur` (
`cur_id` int( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`cur_namespace` tinyint( 2 ) unsigned NOT NULL default '0',
`cur_title` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`cur_text` mediumtext NOT NULL ,
`cur_comment` tinyblob NOT NULL ,
`cur_user` int( 5 ) unsigned NOT NULL default '0',
`cur_user_text` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_restrictions` tinyblob NOT NULL ,
`cur_counter` bigint( 20 ) unsigned NOT NULL default '0',
`cur_is_redirect` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_minor_edit` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_is_new` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_random` double unsigned NOT NULL default '0',
`cur_touched` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`inverse_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE
latin1_bin NOT NULL default '',
UNIQUE KEY `cur_id` ( `cur_id` ) ,
KEY `cur_namespace` ( `cur_namespace` ) ,
KEY `cur_title` ( `cur_title` ( 20 ) ) ,
KEY `cur_timestamp` ( `cur_timestamp` ) ,
KEY `cur_random` ( `cur_random` ) ,
KEY `name_title_timestamp` ( `cur_namespace` , `cur_title` ,
`inverse_timestamp` ) ,
KEY `user_timestamp` ( `cur_user` , `inverse_timestamp` ) ,
KEY `usertext_timestamp` ( `cur_user_text` , `inverse_timestamp` ) ,
KEY `namespace_redirect_timestamp` ( `cur_namespace` , `cur_is_redirect`
, `cur_timestamp` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 PACK_KEYS =1 AUTO_INCREMENT
=1028;
MySQL said: Documentation
#1064 - 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 'collate
+latin1_bin NOT NULL default '',
`cur_text` mediumtext
//-
as i know very little about mysql generally, i'm having a hard time
deriving what the right syntax might be here.
out of interest the same *.sql imports on the local system just fine
using mysql on the commandline ('mysql -u user -p database
file.sql'),
but on the remote machine fails with the same error as seen above.
if someone could point me in the right direction i'd be very grateful.
cheers,
julian
--
julian oliver
[EMAIL PROTECTED]
--
http://www.fastmail.fm - Does exactly what it says on the tin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
better way of doing 1800 sequential updates?
This table holds latest data from an app: mysql select * from ultimas_respuestas_snmp limit 10; +++---+-+---++--+ | id | info_oficina | columna_donde_guardar | info_interfaz | valorSNMP | nombre_dns | hora | +++---+-+---++--+ | 0 | Sucursal Canas | USO_CPU_1min || error | canas | 18:49:53 | | 1 | Sucursal Canas | RAM_LIBRE || error | canas | 18:49:54 | | 2 | Sucursal Canas | ESTADO_ADMIN_1| TDM 195-2883 ICE| 1 | canas | 18:49:55 | | 3 | Sucursal Canas | ESTADO_ADMIN_2| RDSI 669-9010 ICE | error | canas | 18:49:56 | | 4 | Sucursal Canas | ESTADO_ADMIN_3| RDSI_doble 669-9010 ICE | error | canas | 18:49:57 | | 5 | Sucursal Canas | ESTADO_PROTOCOLO_1| TDM 195-2883 ICE| error | canas | 18:49:58 | | 6 | Sucursal Canas | ESTADO_PROTOCOLO_2| RDSI 669-9010 ICE | error | canas | 18:49:59 | | 7 | Sucursal Canas | ESTADO_PROTOCOLO_3| RDSI_doble 669-9010 ICE | 5 | canas | 18:50:00 | | 8 | Sucursal Canas | BW_ENTRADA_1 | TDM 195-2883 ICE| error | canas | 18:50:01 | | 9 | Sucursal Canas | BW_ENTRADA_2 | RDSI 669-9010 ICE | error | canas | 18:50:02 | +++---+-+---++--+ 10 rows in set (0.00 sec) without the limit 10 1780 rows in set (0.03 sec) the create table: ultimas_respuestas_snmp CREATE TABLE `ultimas_respuestas_snmp` ( `id` int(4) NOT NULL default '0', `info_oficina` varchar(35) default NULL, `columna_donde_guardar` varchar(30) default NULL, `info_interfaz` varchar(30) default NULL, `valorSNMP` varchar(12) default NULL, `nombre_dns` varchar(20) default NULL, `hora` varchar(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I cannot use indexes because my dbexpress driver doesn't support it (long story short, I'll change it in the next version). Now, I have to make a lot of sequential updates, like UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:21 WHERE id =0; UPDATE ultimas_respuestas_SNMP SET valorSNMP =10, hora =18:47:22 WHERE id =1; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =2; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =3; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =4; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =5; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =6; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =7; UPDATE ultimas_respuestas_SNMP SET valorSNMP =7000, hora =18:47:24 WHERE id =8; . . . UPDATE ultimas_respuestas_SNMP SET valorSNMP =0, hora =18:48:38 WHERE id =1778; UPDATE ultimas_respuestas_SNMP SET valorSNMP =, hora =18:48:38 WHERE id =1779; This makes my server CPU load top 100% for about 1:20 s. First question: is update the best command for this? I've seen replace that might work too; has anyone played around with something like this before? Second: is there a better way of formulating the update command, for this sequence?? Perhaps one that takes advantage of the sequential inserts? TIA, all comments welcome. I am a newbie by the way, trying to optimize my first MySQL related program. Ariel
Need for distinct sum
Hello,
I need to be able to sum over distinct values but I can't seem to do it
unless I use sub-selects (which I want to avoid doing).
To see what I mean, I've constructed a toy DB:
DROP TABLE IF EXISTS spell;
CREATE TABLE spell (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
power FLOAT,
time_casted DATETIME
);
DROP TABLE IF EXISTS wizard;
CREATE TABLE wizard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
age INT UNSIGNED,
name VARCHAR(255)
);
DROP TABLE IF EXISTS spellcast;
CREATE TABLE spellcast (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type ENUM ('fire', 'air', 'water', 'earth'),
spell_id INT UNSIGNED,
wizard_id INT UNSIGNED
);
INSERT INTO wizard (name, age) VALUES
('alan', 20),
('bill', 23),
('chris', 21);
INSERT INTO spell (power, time_casted) VALUES
(400, '2006-02-02 12:00'),
(432, '2006-02-04 12:00'),
(123, '2006-02-03 12:00'),
(543, '2006-02-08 12:00'),
(320, '2006-02-01 12:00'),
(102, '2006-02-12 12:00'),
(732, '2006-02-14 12:00'),
(948, '2006-02-18 12:00'),
(932, '2006-02-21 12:00'),
(842, '2006-02-26 12:00');
INSERT INTO spellcast (type, spell_id, wizard_id) VALUES
('fire', 1, 1),
('air', 1, 1),
('water', 1, 1),
('earth', 2, 1),
('water', 2, 1),
('fire', 3, 1),
('water', 3, 1),
('water', 4, 1),
('fire', 4, 1),
('air', 5, 1),
('fire', 6, 1),
('water', 7, 1),
('water', 1, 2),
('fire', 1, 2),
('air', 2, 2),
('earth', 3, 2),
('water', 3, 2),
('earth', 4, 2),
('fire', 4, 2),
('air', 4, 2),
('water', 1, 3),
('earth', 1, 3),
('air', 1, 3),
('water', 5, 3),
('fire', 5, 3),
('earth', 5, 3),
('water', 6, 3),
('air', 7, 3);
A spell is an individual spell that's been cast. A spellcast is the
action of casting the spell by a particular wizard (or a group of
wizards). When casting a spell, a wizard can contribute various essenses
(fire, earth, air, water). So for example, Alan cast a spell (id=1) and
contributed three essences (fire, air water) - this means that there
are 3 spellcast rows for this contribution to this spell.
Let's say I want to find the total power of all the spells cast by each
wizard that involve fire air. At first I thought the following might work:
SELECT wizard.name,
SUM(spell.power)
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
GROUP BY wizard.id;
But this is wrong. The above query will count some spells more than
once, so the resulting sum is greater than it should be.
The only way I can think of doing this correctly is to use sub-queries:
SELECT DISTINCT wizard.name,
sub.s
FROM
(SELECT SUM(inner_sub.power) AS s,
inner_sub.wiz_id
FROM
(SELECT DISTINCT
spell.id,
spell.power,
wizard.id AS wiz_id
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
) AS inner_sub
GROUP BY inner_sub.wiz_id
) AS sub,
wizard,
spellcast
WHERE wizard.id = sub.wiz_id AND
spellcast.wizard_id = wizard.id AND
spellcast.type IN ('fire', 'air');
This works but I was wondering whether there was a simpler way to do it.
All my queries are generated dynamically, and I want to avoid generating
complex subqueries.
Anyone know of a way to do the above a lot more simply? I can change the
DB schema if needs be.
Thanks,
Yasir
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE/lock problems?
I'm seeing some very odd locking behaviour on 4.1.13: mysql show full processlist; ++--++--+-+--+++ | Id | User | Host | db | Command | Time | State | Info | ++--++--+-+--+++ | 30 | len | dick.landsonar.com:36746 | NULL | Sleep |1 || NULL | | 33 | len | dick.landsonar.com:3233| us | Sleep | 7423 || NULL | | 35 | len | yertle.landsonar.com:64667 | us | Sleep | 38 || NULL | | 36 | len | yertle.landsonar.com:64666 | us | Query | 30 | init | UPDATE crumb SET link_ID = 127624294, dir_Travel = 'T', last_Modified = SYSDATE() WHERE link_ID IS NULL AND customer_ID = 2 AND source_ID = 2 AND vehicle_ID = 43920 AND actual_Time = '2005-03-11 01:19:40' | | 37 | len | yertle.landsonar.com:64665 | us | Query | 30 | Locked | UPDATE crumb SET link_ID = 125170474, dir_Travel = 'F', last_Modified = SYSDATE() WHERE link_ID IS NULL AND customer_ID = 2 AND source_ID = 2 AND vehicle_ID = 161878 AND actual_Time = '2005-08-14 15:26:17' | | 38 | len | yertle.landsonar.com:64664 | us | Query | 30 | Locked | UPDATE crumb SET link_ID = 125154498, dir_Travel = 'T', last_Modified = SYSDATE() WHERE link_ID IS NULL AND customer_ID = 2 AND source_ID = 2 AND vehicle_ID = 208475 AND actual_Time = '2006-02-28 20:46:15' | There is no other activity in the system. The db server is dead idle - no CPU activity, no disk activity. I'm suspicious that there may be some sort of bug in the lock manager that we are tickling; the deadlock breaks eventually, but sometimes it takes 10 minutes or more. Thoughts? Thanks, chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
