MySQL 5.0.51a and SHOW ENGINES
Hi, It seems that SHOW ENGINES fails on MySQL 5.0.51a (community edition checked). Instead of returning the full data, the first two columns are cut off at 3 characters, while the comment column is cut off at 26 characters. Sample output: # | Engine| Support| Comment --+---++--- 1| MyI | YES| Default engine as of MySQL 2| MEM | YES| Hash based, stored in memo 3| Inn | DEF| Supports transactions, row --+---++--- 4| Ber | NO | Supports transactions and 5| BLA | YES| /dev/null storage engine ( 6| EXA | YES| Example storage engine --+---++--- 7| ARC | YES| Archive storage engine 8| CSV | NO | CSV storage engine 9| ndb | NO | Clustered, fault-tolerant, --+---++--- 10| FED | YES| Federated MySQL storage en 11| MRG | YES| Collection of identical My 12| ISA | NO | Obsolete storage engine As you can see, this is wrong and my application fails properly fetching info about the available storage engines. 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]
Re: MySQL 5.0.51a and SHOW ENGINES
Sorry, a bit too hasty -- It seems to work fine in the command line client! Has the wire protocol changed somehow? 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 Hi, It seems that SHOW ENGINES fails on MySQL 5.0.51a (community edition checked). Instead of returning the full data, the first two columns are cut off at 3 characters, while the comment column is cut off at 26 characters. Sample output: # | Engine| Support| Comment --+---++--- 1| MyI | YES| Default engine as of MySQL 2| MEM | YES| Hash based, stored in memo 3| Inn | DEF| Supports transactions, row --+---++--- 4| Ber | NO | Supports transactions and 5| BLA | YES| /dev/null storage engine ( 6| EXA | YES| Example storage engine --+---++--- 7| ARC | YES| Archive storage engine 8| CSV | NO | CSV storage engine 9| ndb | NO | Clustered, fault-tolerant, --+---++--- 10| FED | YES| Federated MySQL storage en 11| MRG | YES| Collection of identical My 12| ISA | NO | Obsolete storage engine As you can see, this is wrong and my application fails properly fetching info about the available storage engines. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
Hi all, You can read this article, written by a SUN benchmarking guru (hi Dimitri :) ). Best regards. Web link : http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge Tables and Replication
[EMAIL PROTECTED] wrote: Does this sound about right? Anybody see any road hazards? If not, and this line of thinking is reasonable, should the DB with the older records also be replicated so that when a new old records table needs to be created, I don't have to repeat everything on the slave? Most of the problems documented here http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html and some of it here http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html The problems that stand out - A MERGE table cannot maintain uniqueness constraints over the entire table. - Key reads are slower. When you read a key, the MERGE storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. To read the next key, the MERGE storage engine needs to search the read buffers to find the next key. -- raj shekhar Thanks, raj, for underscoring the key reads issue. That might be a deal breaker... David
0x96 character in command file
I am having what I think is a weird problem. Here's my setup: - Im running Windows Vista. - I've tried the exact same thing on Linux with the exact same result. - I'm running the mysql CLI. The client is 5.0.45-community on the Vista system, 4.1.22 on the Linux system. - The server in both cases is 4.1.22 community, running on the Linux server. Here's the problem. I perform the following operations by copying and pasting the commands from an ANSI text file: mysql SET NAMES utf8; Query OK, 0 rows affected (0.04 sec) mysql DROP TEMPORARY TABLE IF EXISTS eo_name_table; Query OK, 0 rows affected, 1 warning (0.04 sec) mysql CREATE TEMPORARY TABLE `giiexpr_db`.`eo_name_table` ( - `eo_name` VARCHAR( 255 ) UNIQUE NOT NULL - ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0.04 sec) mysql INSERT INTO eo_name_table - (eo_name) - VALUES - - (Disposable Paper Products - Uruguay); Query OK, 1 row affected (0.04 sec) mysql SHOW WARNINGS; Empty set (0.04 sec) mysql SELECT * FROM eo_name_table; +-+ | eo_name | +-+ | Disposable Paper Products - Uruguay | +-+ 1 row in set (0.05 sec) That worked just fine. The kicker is that what looks like a hyphen is actually an n-dash, 0x96. If I source the exact same commands from the file I copied them from, I get this: mysql source test_n-dash.sql; -- SET NAMES utf8 -- Query OK, 0 rows affected (0.04 sec) -- DROP TEMPORARY TABLE IF EXISTS eo_name_table -- Query OK, 0 rows affected, 1 warning (0.04 sec) -- CREATE TEMPORARY TABLE `giiexpr_db`.`eo_name_table` ( `eo_name` VARCHAR( 255 ) UNIQUE NOT NULL ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci -- Query OK, 0 rows affected (0.04 sec) -- INSERT INTO eo_name_table (eo_name) VALUES (Disposable Paper Products û Uruguay) -- Query OK, 1 row affected, 1 warning (0.04 sec) -- SHOW WARNINGS -- +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1265 | Data truncated for column 'eo_name' at row 1 | +-+--+--+ 1 row in set (0.04 sec) -- SELECT * FROM eo_name_table -- +---+ | eo_name | +---+ | Disposable Paper Products | +---+ 1 row in set (0.04 sec) In summary, the character 0x96 truncates the quoted value, but is reported as Data truncated... as though the title were too long. Why does this work when I type paste this directly in? More importantly, why does it NOT work when I source the file and what can I do about it? There is much too much data to paste. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.51a and SHOW ENGINES
On Thu, Apr 10, 2008 at 09:32:43AM +0200, Martijn Tonies wrote: It seems that SHOW ENGINES fails on MySQL 5.0.51a (community edition checked). Instead of returning the full data, the first two columns are cut off at 3 characters, while the comment column is cut off at 26 characters. sounds like you are using the wrong value for the length of a utf8 field, where the number of characters is being divided by the max character length. (10 / 3 = 3, 80 / 3 = 26) or it could be the server returning the wrong length. use mysql --column-type-info to see what it is returning. jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select records to send to another table in another database
I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select records to send to another table in another database
I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? If the tables have the same schema, you should be able to just do a mysql dump and pipe that back into itself. Something along the lines of: mysqldump old_table | mysql new_table But that will only work if the tables have the same structure. If they don't, you can work with variations of the above. thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select records to send to another table in another database
At 12:21 PM -0400 4/10/08, Larry Brown wrote: I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select records to send to another table in another database
I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? If the tables have the same schema, you should be able to just do a mysql dump and pipe that back into itself. Something along the lines of: mysqldump old_table | mysql new_table But that will only work if the tables have the same structure. If they don't, you can work with variations of the above. [JS] I think he wants to bring over only certain records. In that case he can use INSERT INTO ... SELECT ..., Where the INSERT INTO references the table in one database and the SELECT references the table in the other database. thnx, Chris -- 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: select records to send to another table in another database
I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; And you can do that using the cli mysql app? thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select records to send to another table in another database
Yes Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Christoph Boget [mailto:[EMAIL PROTECTED] Sent: Thursday, April 10, 2008 1:09 PM To: Paul DuBois Cc: Larry Brown; Mysql Subject: Re: select records to send to another table in another database I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; And you can do that using the cli mysql app? thnx, Chris -- 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: select records to send to another table in another database
At 1:09 PM -0400 4/10/08, Christoph Boget wrote: I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; And you can do that using the cli mysql app? Yes. You can do this in any interface that enables you to issue SQL statements. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select records to send to another table in another database
On Thu, 2008-04-10 at 11:43 -0500, Paul DuBois wrote: For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com That is exactly what I was looking for. Thank you all very much. Larry -- Larry Brown [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]