mk-archiver fails to insert records if destination table columns in different order than source table

[GoogleCodeExporter]

If this is a problem report, what steps will reproduce the problem?
1. Create 2 databases, source and destination, each with the same table in
it. Have destination table have same columns as source, but in a different
order. E.g.

Source              Dest
__________          ________________
ID INT              NAME VARCHAR
NAME VARCHAR        ID INT  

2. Run mk-archiver to move records from Source to Dest

3. mk-archiver will fail to insert the records because it tries to insert
the ID column value into the NAME column on the destination table.

Note that I made a change in my copy of the source code: 
starting line 2142

if ( $dst ) {
   $ins_stmt = $nibbler->generate_ins_stmt(
      tbl  => $src->{info},  // generate column position from src table
      cols => \@sel_cols,
   );

The issue is that the Column name list is being generated from the source
table, so the order in the SQL statement is:

INSERT INTO `dest`.`Destination`(`ID`,`NAME`) VALUES (?,?)

However the bound parameters are generated in the order based on the column
order of the destination table (NAME, ID)

I'm not at all a Perl programmer, so there could be problems with this
solution, but it fixed our problem.

What information do you get from mk-<toolname>--version?
Ver @VERSION@ Distrib @DISTRIB@ Changeset 0

What is your MySQL, Perl, DBI, and DBD::mysql version?
MySQL 5.0xx, don't have other info

What is your operating system/distribution?
Linux/Windows

If you think it'll be useful, please run the tool with MKDEBUG=1 and
capture the full output in a file, then attach the file.  For example,
MKDEBUG=1 mk-<toolname> [options] > debug.txt.  Is there anything in this
file that you think we should pay special attention to?  Please gzip the
file if it's large.

Please provide any additional information below.

Original issue reported on code.google.com by shalomsh...@gmail.com on 20 Nov 2008 at 8:42

[GoogleCodeExporter]

Thanks for the analysis.

Original comment by baron.schwartz on 24 Nov 2008 at 12:07

• Changed state: Accepted
• Added labels: Tool-mk_archiver

[GoogleCodeExporter]

Target these for release end of January 2009.

Original comment by baron.schwartz on 4 Jan 2009 at 5:04

• Added labels: Milestone-2009_01

[GoogleCodeExporter]

I'm working on this.

Original comment by dan...@percona.com on 6 Jan 2009 at 7:19

• Changed state: Started

[GoogleCodeExporter]

I've fixed this but the fix is causing other tests to break so I'm not 
committing it
yet. I think there may be a problem in TableNibbler::generate_ins_stmt()--that 
or I'm
not clear on what the return value of this sub is supposed to be. I'll find 
this out
tomorrow. In any case, I've isolated the problem and know how to fix it, but I 
need a
better understanding of some other stuff first.

Original comment by dan...@percona.com on 7 Jan 2009 at 1:47

[GoogleCodeExporter]

r2738 and r2784 have work in progress. r2738 fixes 
TableNibbler::generate_ins_stmt()
to what I think it was meant to do, and r2784 implements it in mk-archiver 
which does
then fix my test for this issue. But it also breaks an older test. I'm inclined 
to
believe that my updates and new test are correct, and that old test which is 
breaking
is wrong/showing us that there's another issue to fix somewhere. Further
investigation will reveal what's wrong.

Original comment by dan...@percona.com on 7 Jan 2009 at 1:17

[GoogleCodeExporter]

This should be fixed in r2785 but, Baron, you should double check this. I 
marked the
test case (you'll see it in the diff) which I changed because I'm sure it was 
wrong.

You'll also want to see how I changed TableNibbler::generate_ins_stmt(). I 
added more
documentation to that sub to explain what I'm doing and why.

I'll leave this issue open. If you agree with the changes, we'll mark this as 
Fixed.

Original comment by dan...@percona.com on 7 Jan 2009 at 6:24

[GoogleCodeExporter]

In my comment 5 I have a typo: r2738 should be r2783.

Original comment by dan...@percona.com on 17 Jan 2009 at 9:14

[GoogleCodeExporter]

I believe this issue is fixed now.

Original comment by baron.schwartz on 31 Jan 2009 at 3:27

• Changed state: Verified

[GoogleCodeExporter]

Original comment by baron.schwartz on 30 Jan 2010 at 5:13

• Changed state: Fixed