INSERT INTO newtable from oldtable fails at times

[ebohatch]

I have a function I set up to perform a copy of a table and its data. There are 9 tables that I will be copying.

On my desktop system (Windows 7, Apache 2.2.14, PHP 5.3.1, Mysql 5.1.41) it works well, all tables are created and all data is inserted into the new tables.

 

But when I run this on my webhost system (Linux, Apache 2.2.17, PHP 5.2.16, Mysql 5.0.91) it creates all of the new table structures but only some of the data is copied. These are all small data tables.  One table has over 50 records  and copies fine. O one has only 11 records and doesn't copy, another has only 1  record and does not copy.

 

This is the function: 

function copy_table($db, $oldtableName, $newtableName) {

$db->setQuery("SELECT count(*) FROM `$oldtableName` WHERE 1");

$tablecount = $db->loadResult();

if ( $tablecount ) {

echo "  copying old $oldtableName to new  $newtableName";

$db->setQuery("CREATE TABLE IF NOT EXISTS `$newtableName` LIKE `$oldtableName` ");

$result = $db->query();

echo "--|check result create:".$result;

$db->setQuery("ALTER TABLE `$newtableName` DISABLE KEYS");

$result = $db->query();

$db->setQuery("INSERT INTO `$newtableName` SELECT * FROM `$oldtableName` ");

$result = $db->query();

echo "|--check result insert:".$result."<br>";

$db->setQuery("ALTER TABLE `$newtableName` ENABLE KEYS");

$result = $db->query();

}

}

 

Here are the results from my desktop system:

old adsmanager database tables exist and new ones dont, therefore copying old to new

  copying old #__adsmanager_config to new #__adsmanagerredux_config--|check result create:1|--check result insert:1

  copying old #__adsmanager_adcat to new #__adsmanagerredux_adcat--|check result create:1|--check result insert:1

  copying old #__adsmanager_ads to new #__adsmanagerredux_ads--|check result create:1|--check result insert:1

  copying old #__adsmanager_categories to new #__adsmanagerredux_categories--|check result create:1|--check result insert:1

  copying old #__adsmanager_columns to new #__adsmanagerredux_columns--|check result create:1|--check result insert:1

  copying old #__adsmanager_fields to new #__adsmanagerredux_fields--|check result create:1|--check result insert:1

  copying old #__adsmanager_field_values to new #__adsmanagerredux_field_values--|check result create:1|--check result insert:1

  copying old #__adsmanager_positions to new #__adsmanagerredux_positions--|check result create:1|--check result insert:1

  copying old #__adsmanager_profile to new #__adsmanagerredux_profile--|check result create:1|--check result insert:1

 

 

These are the results from my webhost:

old adsmanager database tables exist and new ones dont, therefore copying old to new

  copying old #__adsmanager_config to new #__adsmanagerredux_config--|check result create:1|--check result insert:

  copying old #__adsmanager_adcat to new #__adsmanagerredux_adcat--|check result create:1|--check result insert:1

  copying old #__adsmanager_ads to new #__adsmanagerredux_ads--|check result create:1|--check result insert:

  copying old #__adsmanager_categories to new #__adsmanagerredux_categories--|check result create:1|--check result insert:1

  copying old #__adsmanager_columns to new #__adsmanagerredux_columns--|check result create:1|--check result insert:1

  copying old #__adsmanager_fields to new #__adsmanagerredux_fields--|check result create:1|--check result insert:1

  copying old #__adsmanager_field_values to new #__adsmanagerredux_field_values--|check result create:1|--check result insert:1

  copying old #__adsmanager_pay4featuredad to new #__adsmanagerredux_pay4featuredad--|check result create:1|--check result insert:1

  copying old #__adsmanager_positions to new #__adsmanagerredux_positions--|check result create:1|--check result insert:1

  copying old #__adsmanager_profile to new #__adsmanagerredux_profile--|check result create:1|--check result insert:

 

 

As you see I get no result from the first (config only 1 record), the third (ads has only 11 records) and the last (profile has 29 records)

 

[PFMaBiSmAd]

If you are using mysql or your class is using mysqli, you can use mysql_error() or the mysqli ->error property to find out why the query is failing.

 

My guess would be that some of the data contains quotes ( ' ) and is breaking the sql syntax and needs to be escaped before putting it into the second query. This works on your development system (probably) because magic_quotes_runtime is on (you should check using a phpinfo() statement) and it doesn't work on your live server because magic_quotes_runtime is off (you should also check in order to confirm that this is the actual cause of the problem.)

 

If magic_quotes_runtime is causing this, DON'T turn it on (because it is scheduled to be removed in the next major php version), you will need to escape the data being put into the query.

[ebohatch]

Thanks for the quick reply.

 

I just checked and Magic_quotes if OFF on both my desktop and on my webhost.  I went into the config file and found 2 text fields that had quotes and removed them, reran the script and same results.

 

I even added the following at the begining of the script:

 

ini_set('error_reporting', E_ALL);

ini_set('display_errors', TRUE);

ini_set('max_execution_time','6000');

ini_set('memory_limit','256M');

ini_set('mysql.connect_timeout','6000');

ini_set('mysql.trace_mode','On');

 

 

I did not get any errors displayed.

[Pikachu2000]

Post your current code.

[PFMaBiSmAd]

I'm going to guess you are getting a duplicate key error or similar. Someone already told you how you can find out exactly why the query is failing -

If you are using mysql or your class is using mysqli, you can use mysql_error() or the mysqli ->error property to find out why the query is failing.

[ebohatch]

Here is the output with errno:

old adsmanager database tables exist and new ones dont, therefore copying old to new

  copying old #__adsmanager_config to new #__adsmanagerredux_config

--|check result create:1||errno[0]|--check result insert:||errno[1136]

  copying old #__adsmanager_adcat to new #__adsmanagerredux_adcat

--|check result create:1||errno[0]|--check result insert:1||errno[0]

  copying old #__adsmanager_ads to new #__adsmanagerredux_ads

--|check result create:1||errno[0]|--check result insert:||errno[1136]

  copying old #__adsmanager_categories to new #__adsmanagerredux_categories

--|check result create:1||errno[0]|--check result insert:1||errno[0]

  copying old #__adsmanager_columns to new #__adsmanagerredux_columns

--|check result create:1||errno[0]|--check result insert:1||errno[0]

  copying old #__adsmanager_fields to new #__adsmanagerredux_fields

--|check result create:1||errno[0]|--check result insert:1||errno[0]

  copying old #__adsmanager_field_values to new #__adsmanagerredux_field_values

--|check result create:1||errno[0]|--check result insert:1||errno[0]

  copying old #__adsmanager_pay4featuredad to new #__adsmanagerredux_pay4featuredad

--|check result create:1||errno[0]|--check result insert:1||errno[0]

  copying old #__adsmanager_positions to new #__adsmanagerredux_positions

--|check result create:1||errno[0]|--check result insert:1||errno[0]

  copying old #__adsmanager_profile to new #__adsmanagerredux_profile

--|check result create:1||errno[0]|--check result insert:||errno[1136]

 

I looked the error code up and it states:

Error: 1136 SQLSTATE: 21S01 (ER_WRONG_VALUE_COUNT_ON_ROW)

Message: Column count doesn't match value count at row %ld

 

For the first table _config there is only 1 row. I checked and there are no null values, I had removed any quotes from within text strings.

 

From what I understand it indicates that 2 values are not separated by a comma therefore they are concatenated.

 

BTW I also added      ini_set('magic_quotes_gpc','Off');    for insurance.

 

 

I am confused???

 

 

[PFMaBiSmAd]

Any chance that the new table - __adsmanagerredux_profile already exists so that the "CREATE TABLE IF NOT EXISTS `$newtableName` LIKE `$oldtableName` " query doesn't actually create the new table LIKE the old table?

[kickstart]

Hi

 

Just about to suggest the above.

 

Drop the new table names before you try the create, etc, just to make sure.

 

All the best

 

Keith

[ebohatch]

No chance, every time I run a test I first drop all ten of the new tables.

 

Thanks

[PFMaBiSmAd]

Posting the table definitions and data for one or all three of the tables that don't work would be the quickest way of getting a solution.

[ebohatch]

Thanks guys,

Based on your suggestions. I was able to track down where/which fields were causing problems.

Actually ended up scraping the INSERT INTO newtable FROM oldtable

It never gives error info, just fails.

I set up a foreach loop to pull each row of the table and convert it to keys and values and then did a

INSERT INTO `$newtableName` ($keys) VALUES ($newrow)

 

this way I could check the error if it failed.

 

Found the culprits.

 

Now it copies all records correctly.

 

 

Again THANKS for your help.  (and you guys are prompt)