Re: MySQL 5.0.51a and SHOW ENGINES

[Martijn Tonies]

Hello Jim,


> 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.

That doesn't work with 5.0 as far as I can tell.

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 Master and Slave Database Server

[Kaushal Shriyan]

Hi,

what are the different test cases to test MySQL Master and Slave Replication
Database Server.

Thanks and Regards

Kaushal

Re: select records to send to another table in another database

[Larry Brown]

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]

Re: select records to send to another table in another database

[Paul DuBois]

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

[Jerry Schwartz]

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

[Christoph Boget]

> > 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

[Jerry Schwartz]

> 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  | mysql 

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

[Paul DuBois]

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

[Christoph Boget]

> 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  | mysql 

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]

select records to send to another table in another database

[Larry Brown]

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: MySQL 5.0.51a and SHOW ENGINES

[Jim Winstead]

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]

0x96 character in command file

[Jerry Schwartz]

I am having what I think is a weird problem. Here's my setup:

- I’m 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: Merge Tables and Replication

[dpgirago]

> [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
>  and
> some of it here
> 

> 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

Re: Help needed to tune Innodb on ZFS (on Solaris)

[JOUANNET, Rodolphe]

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: MySQL 5.0.51a and SHOW ENGINES

[Martijn Tonies]

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]

MySQL 5.0.51a and SHOW ENGINES

[Martijn Tonies]

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]