Update Doesn't Update!
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql select sizes, colorsShadesNumbersShort from products where ID=0;
+---+--+
| sizes | colorsShadesNumbersShort |
+---+--+
| | |
+---+--+
1 row in set (0.00 sec)
Huh?
TIA,
Victor
Re: Update Doesn't Update!
On Fri, 11 Dec 2009 04:38:01 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Warnings: 1
do a SHOW WARNINGS immediately after you execute the stmt.
/ Carsten
mysql select sizes, colorsShadesNumbersShort from products where ID=0;
+---+--+
| sizes | colorsShadesNumbersShort |
+---+--+
| | |
+---+--+
1 row in set (0.00 sec)
Huh?
TIA,
Victor
!DSPAM:451,4b221339930275276717544!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V
Re: Update Doesn't Update!
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Are you serious??
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: Update Doesn't Update!
If ID column is primary key and auto increment as you said, it cant be equal
to zero.
You got a query which reads:
UPDATE columns WHERE false
There is no chance for any updates.
http://dev.mysql.com/doc/refman/5.0/en/update.html
Regards,
m
-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: 11 December 2009 10:06
Cc: mysql@lists.mysql.com
Subject: Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V
--
Szef przynudza? Zagraj sobie!
Sprawdz http://link.interia.pl/f24e4
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:43 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 04:38:01 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Warnings: 1
do a SHOW WARNINGS immediately after you execute the stmt.
mysql update products set sizes=('Small', 'Large') where SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
TIA,
V
Re: Update Doesn't Update!
On Fri, 11 Dec 2009 10:48:59 +0100, Jørn Dahl-Stamnes
sq...@dahl-stamnes.net wrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Then how did he manage to get a matched row?
Of course you can have ID=0.
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in the
first place...
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
Jørn Dahl-Stamnes wrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
You can, but not if it's an auto-increment field.
Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
first desc products
or try to
update products set sizes='Small' where
SKU='prodSKU1';
Best Regards!
Yang Wang
Tel.: 0769-21687397
Fax.: 0769-21685577
Email: yw...@lfm-agile.com.hk
- Original Message -
From: cars...@bitbybit.dk
To: Victor Subervi victorsube...@gmail.com
Cc: mysql@lists.mysql.com
Sent: Friday, December 11, 2009 6:13 PM
Subject: Re: Update Doesn't Update!
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in the
first place...
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=yw...@lfm-agile.com.hk
Re: Update Doesn't Update!
Quoting cars...@bitbybit.dk: Of course you can have ID=0. Definately agree mysql DESCRIBE test; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | autoinc | int(11) | NO | PRI | NULL| auto_increment | | value | varchar(10) | NO | | NULL|| +-+-+--+-+-++ 2 rows in set (0.00 sec) mysql SELECT * FROM test; +-++ | autoinc | value | +-++ | 0 | 1234567890 | +-++ 1 row in set (0.00 sec) mysql UPDATE test SET value='a' WHERE autoinc='0'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql SELECT * FROM test; +-+---+ | autoinc | value | +-+---+ | 0 | a | +-+---+ 1 row in set (0.00 sec) However, what I believe the problem is: mysql UPDATE test set value='12345678901' WHERE autoinc='0'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql SELECT * FROM test; +-++ | autoinc | value | +-++ | 0 | 1234567890 | +-++ 1 row in set (0.00 sec) the value of value is too long for the varchar(10) in the table. It thus generates the warning, and truncate the field. The poster's table needs to be updated therefor to accept longer variables in the sizes column. -- Regards, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in the
first place...
Sorry. It's an enum of which the elements I am trying to add into a row are
elements of the same enum; that is, a subset.
V
Re: Update Doesn't Update!
On Fri, 11 Dec 2009 05:28:41 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in
the
first place...
Sorry. It's an enum of which the elements I am trying to add into a row
are
elements of the same enum; that is, a subset.
You're using the wrong type. RTFM re. the difference between enums and
sets.
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
different 5.0 and 5.1 about long_query_time
Hi,All The version of 5.1 include microtime slow query patch? slow query log in version 5.1 __ # u...@host: root[root] @ localhost [] # Query_time: 0.94 Lock_time: 0.24 Rows_sent: 0 Rows_examined: 0 SET timestamp=1260514025; UPDATE gamesession SET session_expires = '1260515825', session_data = 'ljuser|s:13:chenqingji008;task|s:2:52;' WHERE session_id = '79bfdd9840bdf4cdb19d19ff90dc0643'; version 5.0.45 ___ r...@localhost:(none) 18:34:01show variables like '%long%'; +-+---+ | Variable_name | Value | +-+---+ | long_query_time | 10| +-+---+ 1 row in set (0.01 sec) r...@localhost:(none) 18:35:41select version(); +---+ | version() | +---+ | 5.0.45| +---+ 1 row in set (0.00 sec) _ version 5.1.29 ___ r...@localhost:(none) 18:36:56show variables like '%long%'; +-+--+ | Variable_name | Value| +-+--+ | long_query_time | 2.00 | +-+--+ 1 row in set (0.01 sec) r...@localhost:(none) 18:36:58select version(); +---+ | version() | +---+ | 5.1.29-rc-log | +---+ 1 row in set (0.00 sec) __ Best Regards! Yang Wang Email: yw...@lfm-agile.com.hk
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 5:33 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:28:41 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in
the
first place...
Sorry. It's an enum of which the elements I am trying to add into a row
are
elements of the same enum; that is, a subset.
You're using the wrong type. RTFM re. the difference between enums and
sets.
k. Thanks,
V
Re: different 5.0 and 5.1 about long_query_time
2009/12/11 Yang Wang yw...@lfm-agile.com.hk: Hi,All The version of 5.1 include microtime slow query patch? As of MySQL 5.1.21, the minimum value is 0, and a resolution of microseconds is supported when logging to a file. http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: different 5.0 and 5.1 about long_query_time
On Fri, 11 Dec 2009 18:43:06 +0800, Yang Wang yw...@lfm-agile.com.hk wrote: Hi,All The version of 5.1 include microtime slow query patch? It would have taken you less time to google mysql long_query_time and getting your answer, than it did to write your mail... / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
This only works for MyISAM :-) However, there's another solution where you don't need to shut down, and that works for any engine afaik: rename table oldschema.table to newschema.table; I agree that it's a silly thing to not have, but I can't say that I've encountered a whole lot of instances where I needed it, either. On Fri, Dec 11, 2009 at 7:04 AM, Ken D'Ambrosio k...@jots.org wrote: Uhhh... wow. Unless I'm very, very, very mistaken, I think you're missing something pretty obvious: I believe you can simply a) shut down the database b) mv the directory to a different directory name. *DONE* Your database now has a different name. Boy, that 30 seconds of hard labor was sure faster than waiting a week for SQL dumps. Granted, I can't swear that this is Officially Sanctioned And Approved(tm), but I've done it many times, myself (and, indeed, just verified it under 5.1 to be sure it still worked). Since you are talking such a significant volume of data, I would suggest either testing, or hearing from someone more knowledgeable than I, but I think this problem is substantially smaller than you've let yourself believe. -Ken On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote: How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=onst at us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os_det ai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viabilit y= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@jots.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 11:19 AM, Mark Goodge m...@good-stuff.co.uk wrote:
Jørn Dahl-Stamnes wrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
You can, but not if it's an auto-increment field.
Also, not *entirely* correct, although you have to jump through a few hoops:
it can occur if the field was changed to auto_increment *after* the 0 was
put in there.
Yes, I inherited a database like that once, and yes, it fucks up your day.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
On Fri, December 11, 2009 7:38 am, Johan De Meersman wrote: This only works for MyISAM :-) Good to know -- thanks! However, there's another solution where you don't need to shut down, and that works for any engine afaik: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? I agree that it's a silly thing to not have, but I can't say that I've encountered a whole lot of instances where I needed it, either. Agreed. Thanks much! -Ken On Fri, Dec 11, 2009 at 7:04 AM, Ken D'Ambrosio k...@jots.org wrote: Uhhh... wow. Unless I'm very, very, very mistaken, I think you're missing something pretty obvious: I believe you can simply a) shut down the database b) mv the directory to a different directory name. *DONE* Your database now has a different name. Boy, that 30 seconds of hard labor was sure faster than waiting a week for SQL dumps. Granted, I can't swear that this is Officially Sanctioned And Approved(tm), but I've done it many times, myself (and, indeed, just verified it under 5.1 to be sure it still worked). Since you are talking such a significant volume of data, I would suggest either testing, or hearing from someone more knowledgeable than I, but I think this problem is substantially smaller than you've let yourself believe. -Ken On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote: How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=on; st at us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os _det ai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viab ilit y= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@jots.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join between columns with unique keys randomly uses the keys, or uses a temporary table (which fails).
Hi all I have this query: SELECT n.ID, n.CatalogNumber, [...more...], d.ID, d.CatalogNumber, [...more...] FROM newdarwincoredata n INNER JOIN darwincoredata d ON n.CatalogNumber = d.CatalogNumber ORDER BY n.CatalogNumber; Both tables have exactly the same structure and indices: mysql SHOW CREATE TABLE darwincoredata; CREATE TABLE `darwincoredata` ( `ID` int(10) NOT NULL auto_increment, `CatalogNumber` varchar(20) NOT NULL, [...more...], PRIMARY KEY (`ID`), UNIQUE KEY `CatalogNumber` (`CatalogNumber`), UNIQUE KEY `GlobalUniqueIdentifier` (`GlobalUniqueIdentifier`), KEY `DateLastModified` (`DateLastModified`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 There are 336983 rows in darwincoredata, and 337304 in newdarwincoredata. The plan for the query varies between using the CatalogNumber index and using a temporary table (which fails, once it fills up the disk). I get either this: mysql EXPLAIN that query ++-+---+--+---+---+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+---+-+--++-+ | 1 | SIMPLE | d | ALL | CatalogNumber | NULL | NULL| NULL | 336238 | Using temporary; Using filesort | | 1 | SIMPLE | n | ref | CatalogNumber | CatalogNumber | 22 | GBIF_wrapper.d.CatalogNumber | 1 | | ++-+---+--+---+---+-+--++-+ Or this: ++-+---+---+---+---+-+--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--++---+ | 1 | SIMPLE | d | index | CatalogNumber | CatalogNumber | 22 | NULL | 326508 | | | 1 | SIMPLE | n | ref | CatalogNumber | CatalogNumber | 22 | GBIF_wrapper.d.CatalogNumber | 1 | | ++-+---+---+---+---+-+--++---+ Executing SHOW INDEX IN newdarwincoredata; SHOW INDEX IN darwincoredata; And then doing the EXPLAIN again sometimes changes the plan. I assume this is because the cardinalities are recalculated. I expect the CatalogNumber unique keys to always be used for this query. Even if I do: SELECT [...] FROM newdarwincoredata n FORCE INDEX (CatalogNumber) INNER JOIN darwincoredata d FORCE INDEX (CatalogNumber) ON n.CatalogNumber = d.CatalogNumber ORDER BY n.CatalogNumber The query still sometimes uses a temporary table. EXPLAIN SELECT STRAIGHT_JOIN [...] FROM newdarwincoredata n INNER JOIN darwincoredata d ON n.CatalogNumber = d.CatalogNumber ORDER BY n.CatalogNumber ++-+---+---+---+---+-+--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--++---+ | 1 | SIMPLE | n | index | CatalogNumber | CatalogNumber | 22 | NULL | 336588 | | | 1 | SIMPLE | d | ref | CatalogNumber | CatalogNumber | 22 | GBIF_wrapper.n.CatalogNumber | 1 | | ++-+---+---+---+---+-+--++---+ This seems to always use the index, but I don't want to rely on this without knowing why -- might it be because n has more rows than d? MySQL version is 5.0.22-log, x86_64, redhat-linux-gnu. Thanks for any insights Matt Blissett
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-)
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Stored Proc's
Silly question here... But can I have multiple definers for a stored proc, rather than allowing `us...@`%`? basically, I want to only allow 1 user name, but from only 2 or 3 IP's. My Googleing didn't turn up anything of use, so now, I am here asking :) Thanks in advance! Steve Staples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Stored Proc's
of course. you can have entries u...@domain1.me.com u...@xxx.foo.com the only caveat is to make sure the reverse lookup of your client hosts works as expected as those are the names that mysql will apply at authentication time. - mkichael dykman On Fri, Dec 11, 2009 at 9:36 AM, Steve Staples sstap...@mnsi.net wrote: Silly question here... But can I have multiple definers for a stored proc, rather than allowing `us...@`%`? basically, I want to only allow 1 user name, but from only 2 or 3 IP's. My Googleing didn't turn up anything of use, so now, I am here asking :) Thanks in advance! Steve Staples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
temporary merge table as table name alias
All, Is is possible to create temporary MERGE tables? This code gives an error: 8 8 -- clean slate DROP TABLE IF EXISTS test_abcdefgh; DROP TABLE IF EXISTS dante; -- create demo table CREATE TABLE test_abcdefgh ( `myvalue` INT(11) UNSIGNED NOT NULL, `modified` DATETIME DEFAULT NULL ) ENGINE=MYISAM DEFAULT CHARSET=utf8; -- sample data INSERT INTO test_abcdefgh (myvalue) VALUES (1), (3), (5); -- clone the table structure CREATE TEMPORARY TABLE dante LIKE test_abcdefgh; -- change the temporary table to a merge table ALTER TABLE dante ENGINE=MERGE UNION(test_abcdefgh); -- check tables SELECT * FROM test_abcdefgh; SELECT * FROM dante; 8 8 Error Code : 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist If you change the line to: CREATE TABLE dante LIKE test_abcdefgh; instead of: CREATE TEMPORARY TABLE dante LIKE test_abcdefgh; Then, the error goes away and the code works. Having a TEMPORARY table is very important for me, though, because I want the table name dante to not be visible to other connections. The whole point of this code is to enable me to create alias names for tables inside a stored procedure. Can someone tell me why the temporary merge table is failing? Dante
RE: Stored Proc's
They are actual IP's, not domain names. So it would be like `us...@`192.168.0.100` and `us...@`192.168.0.101`... so how would I go about putting that in the definer? Currently, i have this: DELIMITER $$ USE `tablename`$$ DROP PROCEDURE IF EXISTS `tablename`$$ CREATE definer=`us...@`192.168.0.100` PROCEDURE `tablename`(... etc etc etc... Sorry if this is sounding dumb, I've only just started using stored procs (and i love them), and i have only seen examples with 1 user, or wildcarding the domain. I would also like to know how to do it for any/all users (but that is not what I am needing now) Steven Staples -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: December 11, 2009 9:50 AM To: mysql@lists.mysql.com Subject: Re: Stored Proc's of course. you can have entries u...@domain1.me.com u...@xxx.foo.com the only caveat is to make sure the reverse lookup of your client hosts works as expected as those are the names that mysql will apply at authentication time. - mkichael dykman On Fri, Dec 11, 2009 at 9:36 AM, Steve Staples sstap...@mnsi.net wrote: Silly question here... But can I have multiple definers for a stored proc, rather than allowing `us...@`%`? basically, I want to only allow 1 user name, but from only 2 or 3 IP's. My Googleing didn't turn up anything of use, so now, I am here asking :) Thanks in advance! Steve Staples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.709 / Virus Database: 270.14.101/2555 - Release Date: 12/11/09 05:06:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Can you use that syntax if the databases are on different file systems? If you can, and the original table is big, the command would take a while as it moved data from one file system to another. On Fri, Dec 11, 2009 at 7:58 AM, Johan De Meersman vegiv...@tuxera.bewrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 8:43 AM, Johan De Meersman vegiv...@tuxera.bewrote:
On Fri, Dec 11, 2009 at 11:19 AM, Mark Goodge m...@good-stuff.co.uk
wrote:
Jørn Dahl-Stamnes wrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
You can, but not if it's an auto-increment field.
Also, not *entirely* correct, although you have to jump through a few
hoops:
it can occur if the field was changed to auto_increment *after* the 0 was
put in there.
Yes, I inherited a database like that once, and yes, it fucks up your day.
I'm lost. I set up this database originally with auto_increment and the
first value was 0. I thought that was always the case. Is there a problem
here?
V
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
According to MySQL docs, it should still work atomically. Granted, I have only used this particular trick when they are on the same filesystem. Copying across filesystems, I imagine it should still be atomic, but your system may be locked for awhile. Obviously, a dedicated RENAME DATABASE command would have the same limitations. - michael dykman On Fri, Dec 11, 2009 at 11:35 AM, Jim Lyons jlyons4...@gmail.com wrote: Can you use that syntax if the databases are on different file systems? If you can, and the original table is big, the command would take a while as it moved data from one file system to another. On Fri, Dec 11, 2009 at 7:58 AM, Johan De Meersman vegiv...@tuxera.bewrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Stored Proc's
Yeah, that's exactly right You can also do some partials like 'user'@'192.168.2.%' - michael dykman On Fri, Dec 11, 2009 at 11:33 AM, Steven Staples sstap...@mnsi.net wrote: They are actual IP's, not domain names. So it would be like `us...@`192.168.0.100` and `us...@`192.168.0.101`... so how would I go about putting that in the definer? Currently, i have this: DELIMITER $$ USE `tablename`$$ DROP PROCEDURE IF EXISTS `tablename`$$ CREATE definer=`us...@`192.168.0.100` PROCEDURE `tablename`(... etc etc etc... Sorry if this is sounding dumb, I've only just started using stored procs (and i love them), and i have only seen examples with 1 user, or wildcarding the domain. I would also like to know how to do it for any/all users (but that is not what I am needing now) Steven Staples -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: December 11, 2009 9:50 AM To: mysql@lists.mysql.com Subject: Re: Stored Proc's of course. you can have entries u...@domain1.me.com u...@xxx.foo.com the only caveat is to make sure the reverse lookup of your client hosts works as expected as those are the names that mysql will apply at authentication time. - mkichael dykman On Fri, Dec 11, 2009 at 9:36 AM, Steve Staples sstap...@mnsi.net wrote: Silly question here... But can I have multiple definers for a stored proc, rather than allowing `us...@`%`? basically, I want to only allow 1 user name, but from only 2 or 3 IP's. My Googleing didn't turn up anything of use, so now, I am here asking :) Thanks in advance! Steve Staples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.709 / Virus Database: 270.14.101/2555 - Release Date: 12/11/09 05:06:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
errno: 13
Fresh install of 5.1.41 on a brand new (Slackware 13 - 64 bit) machine. Installed from tar. Directory structure is: basedir=/usr/local/mysql and datadir=/storage/mysql/data. I am currently running as root. The permissions on the directories in /storage/mysql/data are 766 (I have double and triple checked this.) I have created the mysql data tables by running mysql_install_db... it seemed to complete without error: r...@mysql3:/usr/local/mysql/scripts# ./mysql_install_db --datadir=/storage/mysql/data --basedir=/usr/local/mysql -uroot Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h 10.10.10.31 password 'new-password' Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe You can test the MySQL daemon with mysql-test-run.pl cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/local/mysql/scripts/mysqlbug script! The latest information about MySQL is available at http://www.mysql.com/ Support MySQL by buying support/licenses from http://shop.mysql.com/ I then ran /usr/local/mysql/bin/mysqld_safe -uroot which produced the following error report: 091211 13:19:18 mysqld_safe Starting mysqld daemon with databases from /storage/mysql/data 091211 13:19:18 [Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line 091211 13:19:18 [Note] Plugin 'FEDERATED' is disabled. 091211 13:19:18 [Warning] /usr/local/mysql/bin/mysqld: ignoring option '--innodb-use-sys-malloc' due to invalid value 'ON' ^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 091211 13:19:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins 091211 13:19:19 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name /storage/mysql/data/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 091211 13:19:19 mysqld_safe mysqld from pid file /storage/mysql/data/mysql3.pid ended ~ The relevant portions of the /storage/mysql/data/mysql directory are (for the first error): -rw-rw 1 root root 0 2009-12-11 13:17 plugin.MYD -rw-rw 1 root root 1024 2009-12-11 13:17 plugin.MYI -rw-rw 1 root root 8586 2009-12-11 13:17 plugin.frm - It appears to me that mysqld is looking for the plugin.frm in the /usr/local/mysql/mysql directory which doesn't exist because my data directory is /storage/mysql/data. The second error, InnoDB: File name /storage/mysql/data/ibdata1 InnoDB: File operation call: 'create' is probably the show stopper. The relevant portions of my.cnf are: # The MySQL server [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 20M max_sp_recursion_depth = 100 table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 basedir=/usr/local/mysql datadir=/storage/mysql/data wait_timeout = 10800 max_connections = 600 and # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /storage/mysql/data innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ ignore_builtin_innodb plugin-load=innodb=ha_innodb.so;innodb_trx=ha_innodb.so;innodb_locks=ha_innodb.so;innodb_lock_waits=ha_innodb.so;innodb_cmp=ha_innodb.so;innodb_cmp_reset=ha_ innodb.so;innodb_cmpmem=ha_innodb.so;innodb_cmpmem_reset=ha_innodb.so # Note: ha_innodb.so is in the 'plugins' directory. This error makes no sense to me. Can anyone kick me in the right direction? Thanks, Carl
RE: errno: 13
Mysql daemon runs as the 'mysql' user Chown -r mysql:mysql /storage/mysql/data Regards, Gavin Towey -Original Message- From: Carl [mailto:c...@etrak-plus.com] Sent: Friday, December 11, 2009 11:55 AM To: mysql@lists.mysql.com Subject: errno: 13 Fresh install of 5.1.41 on a brand new (Slackware 13 - 64 bit) machine. Installed from tar. Directory structure is: basedir=/usr/local/mysql and datadir=/storage/mysql/data. I am currently running as root. The permissions on the directories in /storage/mysql/data are 766 (I have double and triple checked this.) I have created the mysql data tables by running mysql_install_db... it seemed to complete without error: r...@mysql3:/usr/local/mysql/scripts# ./mysql_install_db --datadir=/storage/mysql/data --basedir=/usr/local/mysql -uroot Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h 10.10.10.31 password 'new-password' Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe You can test the MySQL daemon with mysql-test-run.pl cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/local/mysql/scripts/mysqlbug script! The latest information about MySQL is available at http://www.mysql.com/ Support MySQL by buying support/licenses from http://shop.mysql.com/ I then ran /usr/local/mysql/bin/mysqld_safe -uroot which produced the following error report: 091211 13:19:18 mysqld_safe Starting mysqld daemon with databases from /storage/mysql/data 091211 13:19:18 [Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line 091211 13:19:18 [Note] Plugin 'FEDERATED' is disabled. 091211 13:19:18 [Warning] /usr/local/mysql/bin/mysqld: ignoring option '--innodb-use-sys-malloc' due to invalid value 'ON' ^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 091211 13:19:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins 091211 13:19:19 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name /storage/mysql/data/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 091211 13:19:19 mysqld_safe mysqld from pid file /storage/mysql/data/mysql3.pid ended ~ The relevant portions of the /storage/mysql/data/mysql directory are (for the first error): -rw-rw 1 root root 0 2009-12-11 13:17 plugin.MYD -rw-rw 1 root root 1024 2009-12-11 13:17 plugin.MYI -rw-rw 1 root root 8586 2009-12-11 13:17 plugin.frm - It appears to me that mysqld is looking for the plugin.frm in the /usr/local/mysql/mysql directory which doesn't exist because my data directory is /storage/mysql/data. The second error, InnoDB: File name /storage/mysql/data/ibdata1 InnoDB: File operation call: 'create' is probably the show stopper. The relevant portions of my.cnf are: # The MySQL server [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 20M max_sp_recursion_depth = 100 table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 basedir=/usr/local/mysql datadir=/storage/mysql/data wait_timeout = 10800 max_connections = 600 and # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /storage/mysql/data innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ ignore_builtin_innodb plugin-load=innodb=ha_innodb.so;innodb_trx=ha_innodb.so;innodb_locks=ha_innodb.so;innodb_lock_waits=ha_innodb.so;innodb_cmp=ha_innodb.so;innodb_cmp_reset=ha_ innodb.so;innodb_cmpmem=ha_innodb.so;innodb_cmpmem_reset=ha_innodb.so # Note: ha_innodb.so is in the 'plugins' directory. This error makes no sense to me. Can anyone kick me in the right direction? Thanks, Carl This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 6:40 PM, Victor Subervi victorsube...@gmail.comwrote: I'm lost. I set up this database originally with auto_increment and the first value was 0. I thought that was always the case. Is there a problem here? Yes, that should not have happened. For autoincrement fields, both NULL and 0 are magic values to get the next number from the sequence at insert time.
RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
On Fri, Dec 11, 2009 at 10:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? Yes. If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. Not really, this is by design afaik. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) Don't know, but given that it works with InnoDB, you should be able to easily test on a small dataset by monitoring the size of a 1M autoextend tablespace :-)
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
if you have myisam alone tables you can rename the folder of the database. That can work like rename database. If you have innodb table you have to move one by one table because details of those tables will be stored in innodb shared table space. Moving folder cannot work. Thanks, Saravanan --- On Fri, 12/11/09, Michael Dykman mdyk...@gmail.com wrote: From: Michael Dykman mdyk...@gmail.com Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? To: MySql mysql@lists.mysql.com Date: Friday, December 11, 2009, 10:54 PM No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=suzuki_b...@yahoo.com
RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Don't forget triggers, stored routines, views, database/table specific user permissions, and replication/binlog options! Regards, Gavin Towey -Original Message- From: Saravanan [mailto:suzuki_b...@yahoo.com] Sent: Friday, December 11, 2009 2:02 PM To: MySql; Michael Dykman Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? if you have myisam alone tables you can rename the folder of the database. That can work like rename database. If you have innodb table you have to move one by one table because details of those tables will be stored in innodb shared table space. Moving folder cannot work. Thanks, Saravanan --- On Fri, 12/11/09, Michael Dykman mdyk...@gmail.com wrote: From: Michael Dykman mdyk...@gmail.com Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? To: MySql mysql@lists.mysql.com Date: Friday, December 11, 2009, 10:54 PM No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=suzuki_b...@yahoo.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.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: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
In our case, we purposely avoid using any of those features. Just straight up INNODB tables. Permissions would be an issue, but in my case, I have a new dump of a database that I want to 'swap' with the existing one. A simple rename old, rename new to old would have solved it. Hence this thread. :) Therefore permissions should be fine as they go by DB name AFAIK and not some pointer. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, December 11, 2009 2:18 PM To: Saravanan; MySql; Michael Dykman Subject: RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? Don't forget triggers, stored routines, views, database/table specific user permissions, and replication/binlog options! Regards, Gavin Towey -Original Message- From: Saravanan [mailto:suzuki_b...@yahoo.com] Sent: Friday, December 11, 2009 2:02 PM To: MySql; Michael Dykman Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? if you have myisam alone tables you can rename the folder of the database. That can work like rename database. If you have innodb table you have to move one by one table because details of those tables will be stored in innodb shared table space. Moving folder cannot work. Thanks, Saravanan --- On Fri, 12/11/09, Michael Dykman mdyk...@gmail.com wrote: From: Michael Dykman mdyk...@gmail.com Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? To: MySql mysql@lists.mysql.com Date: Friday, December 11, 2009, 10:54 PM No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=suzuki_b...@yahoo.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender
does mysqlhotcopy affect the key buffer?
i run mysqlhotcopy on each database once every three hours. i happened to be running my cache warmer this afternoon, which logs timing for a bunch of standard randomized queries, that when the backup script ran, the query times increased by a factor of about 20 from a nice quick pace back down to what i'd expect for an empty cache. hot times then slowly settled back down to the faster pace as i would expect as cold caches warm up. (i really don't know if key or table caches are the bottleneck.) so i was wondering if mysqlhotcopy has affecting the caches. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join on a where clause.
Hi Paul, all! Paul Halliday wrote: On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi everybody! Neil Aggarwal wrote: Paul: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN 'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER BY count DESC LIMIT 20; I am surprised by the quotes you have around the start_ip and end_ip columns; to me, this makes that look like strings. That because I don't know what I am doing :). No quotes on integers; got it! Like Andy replied: No quotes around column names (or expressions) you want evaluated as variables, these quotes mark (constant) strings. (There is another kind of quotes which serves to solve problems around using large and small letters in names, I'll ignore that here.) From your posting, I see the result you hope to get but not the one you actually get. IMO, just dropping the single quotes around the two column names should produce the data you want to get. Or what is the result you receive? Removing the quotes does work. The query however took 1h15m to complete. Yuck. In general, Andy is right recommending explain. This case, however, may be a bit different: I am guessing this is because even though there is a limit, it is still doing the lookup on everything past 20. Also, if the first address has a count of say 2000, it would be doing the lookup 2000 times for a single address. Is that right? Yes, that may be. First, don't forget that the query has to evaluate all events (within your date/time limits) to find which IP addresses are the 20 with the highest count. Second, I fear the server may first perform the join on all your data and only then do the WHERE, the GROUP BY, and the LIMIT. Which means it would handle much more data than needed. I guess you might see an enormous boost if you split up your statement into two parts (syntax not checked): INSERT INTO events_to_locate SELECT COUNT(src_ip) AS count, src_ip FROM event WHERE timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' GROUP BY src_ip ORDER BY count DESC LIMIT 20; SELECT count, INET_NTOA(events_to_locate.src_ip), mappings.cc FROM events_to_locate, mappings WHERE events_to_locate.src_ip BETWEEN mappings.start_ip AND mappings.end_ip; If your mappings table does not have a src_ip column (your column names are unique in the tables listed in the FROM clause), you need not give the table names in that second statement: SELECT count, INET_NTOA(src_ip), cc FROM events_to_locate, mappings WHERE src_ip BETWEEN start_ip AND end_ip; Division of complexity: Maybe the optimizer is not clever enough to apply this optimization, then using a temporary table which holds just the 20 IP addresses you are interested in should significantly reduce the amount of work. If it is I guess I will have to post process the results. Which is fine, I just like to keep as much in the queries as I can. In general, doing work in the server is better, because it means you need to transfer less data between server and client. As usual, there are exceptions. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
