Problem with "Copy Table Structure / Data"

[Zendak]

I copied a table (MyISAM) using Copy Table Structure / Data. The table contains a few VARCHAR(100) columns but these have mutated into CHAR(100) columns in the new copy.

I need quite a lot of copies of this table so manually correcting this would be a pain.

The problem occurs when copying “structure only” and also when copying “structure and data”.

Using v3.71

Apart from that I love this product!

Thanks in advance

[Shadow]

Theorectically MySql changes every CHAR column to VARCHAR that exceed 3 characters in length. Are you sure it's not just a display error?

[Ritesh]

SQLyog logs up all the queries that it executes in the History Window.

Do you find anything unusual?

[Ritesh]

We are working on your issue and will revert back soon.

[Zendak]

Ritesh wrote on Oct 12 2004, 12:04 PM:

SQLyog logs up all the queries that it executes in the History Window.

Do you find anything unusual?

Sorry about the delay..

Here's a test case with a very simple table containing a VARCHAR column:

Code:

CREATE TABLE `test` (                                                        
         `example_col` varchar(100) default NULL                                    
       ) TYPE=MyISAM

Here's the output from the History window when copying this table:

Code:

[19:17:36][  10 ms] use `medialagoon`
[19:17:36][  10 ms] show full fields from `medialagoon`.`test`
[19:17:36][  10 ms] show table status from `medialagoon` like 'test'
[19:17:45][  10 ms] show keys from `medialagoon`.`test`
[19:17:45][  20 ms] create table `medialagoon`.`test_copy` () type=MyISAM select `example_col` from `medialagoon`.`test` where 1 = 0
[19:17:45][  20 ms] show full fields from `medialagoon`.`test`
[19:17:47][  10 ms] show full fields from `medialagoon`.`test_copy`
[19:17:47][  10 ms] show keys from `medialagoon`.`test_copy`
[19:17:47][   0 ms] show create table `medialagoon`.`test_copy`
[19:17:47][   0 ms] use `medialagoon`

In the new table 'test_copy' the field 'example_col' now strangely has CHAR(100) as the type.

Thanks

[Ritesh]

Which version of MySQL are you using?

In our MySQL server which is 4.1.1-alpha-max-nt, the following SQLs:

Code:

create database testdb;

use testdb;

CREATE TABLE `test` (                                                        
        `example_col` varchar(100) default NULL                                    
      ) TYPE=MyISAM;

create table `test_copy` () type=MyISAM select `example_col` from `test` where 1 = 0;

correctly results in `test_copy` being created and `example_col` datatype is varchar(100).

[Shadow]

Considering MySql's silent column changes, a CHAR(100) column is highly interesting!

[Shadow]

Works correctly with MySql 4.1.3 on W2K.

[Zendak]

Quote:

Which version of MySQL are you using?

Currently using 4.0.14-nt on WinXP locally

I've also tested this connecting to one of my remote sites using 3.23.55-Max-log on Linux, and encountered the exact same behaviour.

[Ritesh]

Behaviour confirmed on 3.23.54.

I will revert back soon with the reason(s).

[Author]

Posts