LL      IIIII NN   NN KK  KK EEEEEEE RRRRRR  RRRRRR   OOOOO  RRRRRR 
LL       III  NNN  NN KK KK  EE      RR   RR RR   RR OO   OO RR   RR
LL       III  NN N NN KKKK   EEEEE   RRRRRR  RRRRRR  OO   OO RRRRRR 
LL       III  NN  NNN KK KK  EE      RR  RR  RR  RR  OO   OO RR  RR 
LLLLLLL IIIII NN   NN KK  KK EEEEEEE RR   RR RR   RR  OOOOO  RR   RR
                                                           ramblings
____________________________________________________________________

When migrating complex web applications between versions from one server to another, the database isn’t always 100% compatible.

Usually what happens is that table fields have been added, causing a mysqldump being imported to fail due to the number of columns in the .sql file not matching the number of columns in the database.

ERROR 1136 (21S01) at line 18: Column count doesn't match value count at row 1

This is because mysqldump by default uses the INSERT INTO table VALUES (); syntax.

There is an alternative syntax for mysql which will let you import data when even tables have new columns. (at least, as long as these columns are allowed to have NULL data, or have default values.)

Recently, after a long nightmarish experience, migrating between versions of a certain control panel (*cough* plesk *cough*) between two servers , I have used a little script which automaticlly converts the VALUES insert syntax to SET field=value syntax.

It’s a last resort type of thing. In our case, the migration tools that shipped with the web application fail miserably in countless ways, and this script little script i threw together actually did the trick and saved us.

It’s a perl script, of course.

#!/usr/bin/perl
use strict;
use DBI;

# Settings --------------------------------------------------------------------

my $src_db_user = 'someuser';
my $src_db_pass = 'supersecret';
my $src_db_name = 'somedatabase';
my $src_db_host = 'localhost';
my $src_db_port = 3306;
my @tables_to_export = qw(hosting clients Cards Limits Permissions Templates accounts
data_bases db_users dns_recs dns_recs_t dns_zone dom_level_usrs domainaliases
domains forwarding hosting itmpl mail mail_aliases mail_redir
mail_resp pd_users protected_dirs resp_forward resp_freq spamfilter
spamfilter_preferences subdomains sys_users web_users);

# Globals ---------------------------------------------------------------------

my $src_db;

# Functions -------------------------------------------------------------------

# Connects to the source database.
sub connect_src_db()
{
  $src_db = DBI->connect("DBI:mysql:$src_db_name",$src_db_user,$src_db_pass)
    or die "Could not connect to database: $DBI::errstr";
}

# Disconnects from all databases in use.
sub disconnect_dbs()
{
  $src_db->disconnect();
}

# Gets column names from a table 
# Returns each column name as an element in a returned array.
sub get_columns_from_table($)
{
  my ($table) = @_;
  my @columns;
  my $qh = $src_db->prepare("SHOW COLUMNS FROM `$table`");
  $qh->execute();
  while (my $ref = $qh->fetchrow_arrayref())
  {  
    my @cols = @{$ref};
    push @columns,@cols[0];
  }
  $qh->finish();
  return @columns;
}

# Returns a string of SQL statements which effectively
# export all data in a table.
sub export_table($)
{
  my ($table) = @_;
  my @columns = get_columns_from_table($table);
  my $qh = $src_db->prepare("SELECT * FROM `$table`");
  my $output='';
  $qh->execute();
  # TODO: Line below, deleting original records should probably be an option.
  $output .= "DELETE FROM `$table`;\n";
  while (my $ref = $qh->fetchrow_arrayref())
  {
    my $colnum = 0;
    my @col_values = @{$ref};
    $output .= "INSERT INTO `$table` SET ";
    foreach my $col_value(@col_values)
    {
      my $col_name = @columns[$colnum];  
      $col_value = $src_db->quote($col_value);
      $output .= "`$col_name` = $col_value,";
      $colnum += 1;
    }
    $output =~ s/,$/;/g;
    $output .= "\n";
  }
  $qh->finish();
  return $output;
}

# Entry point -----------------------------------------------------------------

my $export_sql = '';
connect_src_db();
foreach my $table(@tables_to_export)
{
  $export_sql .= export_table($table);
}
disconnect_dbs();
print $export_sql;
exit 0;

usage: ./thescript > mydump.sql

You obviously have to edit the list of tables to export and the db authentication info on top of the script.

I left in the list of tables there, as the provided list actually works for migrating most of plesk 8.x to 9.x – it may help someone.

If you’re using this with plesk, you may have to make a few more small changes to the database after migrating.

You will probably have problems with the IP pool, if the ip addresses are different on the new server. This actually causes an error when you click the hosting config button in the control panel for a domain: You can reset the ip tool for each client with this sql query on the target server:

update clients set pool_id='1';

Depending on how complex your plesk setup is, and which modules you have installed, you may or may not have other issues, but if you’re using plesk you should be used to that by now (and probably you’re slowly losing all your hair).

Like I said, it’s a last resort type of thing, but it was actually faster than trying to resolve all the issues with plesk’s migration manager and/or backup system.

Of course, you can use the script with anything else. All it does is convert:

  INSERT INTO `foo` VALUES('foo','bar');

to

  INSERT INTO `foo` SET bleh='foo', blah='bar';

Another advantage of the SET syntax is that it preserves the column mappings, so even when columns switch places (ie, bleh becomes the second column instead of the first) the import will still work correctly.


 
____________________________________________________________________