Best practice forum (Archived) - Totara Sync fails with temptables error

Skip to main content

Skip Create an account

Best practice forum (Archived)

Totara Sync fails with temptables error

I am attempting to import some test data but the Totara Sync (ran from cli) fails with this error:

Running totara_sync cron...!!! Error writing to database !!!

Potential coding error - existing temptables found when disposing database. Must be dropped!

The following fictitious csv data causes it to error:

"idnumber","timemodified","username","delete","firstname","lastname","email","city","country"
"1001","1349360516","lucile.herman","0","Lucile","Herman","lucile.herman@example.com","North Laviniachester","US"

I haven't been able to get any user import to work via Totara Sync. I have attempted to recheck the csv as well as the user CSV source settings. The current user CSV source settings are:

The current config requires a CSV file with the following structure:

"idnumber","timemodified","username","delete","firstname","lastname","email","city","country"

The sync log just says "sync started" but that is it, and nothing actually gets imported.

I have triple checked my work so I hope I am not missing something silly.

Permalink | Reply

Hi Jamie,

It could be the database is corrupt, is there anyway you can test for this?

regards,

George.

Permalink | Show parent | Reply

I am having the same problem using Totara 2.2.6:

Test file:

idnumber,timemodified,username,password,delete,firstname,lastname,email,city,country 777,1349360516,tsync1,password,teacher10,T,Sync1,tsync1@null.com,Heidelberg,Germany

When I call admin/tool/totara_sync/run_cron.php via CLI and debugging turned on, I get the attached error message.

Alex (Synergy Learning)

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

Hi Alastair,

there is a 'delete' column in the test file.

I also applied Jamie's fix, but that still triggers the same error. I am on 2.2.6, though.

Alex

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

I restarted the database but the problem still occurs.

MySQL 5.1 with replication to a MySQL 5.1 slave instance.

PHP 5.3

I set the php cli php.ini to output all debugging but I don't get any additional debug info when I run the totara sync cron from the command line. I'm not sure if there is a way to run this script with a debug option.

MySQL error logging is enabled, but no errors are logged in association with this problem.

I'll keep troubleshooting and will update if I find anything else.

Permalink | Show parent | Reply

I forgot to mention Totara version: Version 2.2.5.1 (Build: 20120927.00)

Permalink | Show parent | Reply

Following is the debug output when I run the run_cron.php.

Running totara_sync cron...Default exception handler: Error writing to database Debug: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete,firstname,lastname,email,city,country) VALUES ('1001','1349360516','lucil' at line 1
INSERT INTO mdl_totara_sync_user (idnumber,timemodified,username,delete,firstname,lastname,email,city,country) VALUES (?,?,?,?,?,?,?,?,?)
[array (
0 => '1001',
1 => '1349360516',
2 => 'lucile.herman',
3 => '0',
4 => 'Lucile',
5 => 'Herman',
6 => 'lucile.herman@example.com',
7 => 'North Laviniachester',
8 => 'United States',
)]
* line 403 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 802 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 200 of /admin/tool/totara_sync/lib.php: call to mysqli_native_moodle_database->execute()
* line 234 of /admin/tool/totara_sync/sources/source_user_csv.php: call to totara_sync_bulk_insert()
* line 141 of /admin/tool/totara_sync/sources/classes/source.user.class.php: call to totara_sync_source_user_csv->import_data()
* line 138 of /admin/tool/totara_sync/elements/classes/element.class.php: call to totara_sync_source_user->get_sync_table()
* line 60 of /admin/tool/totara_sync/elements/user.php: call to totara_sync_element->get_source_sync_table()
* line 50 of /admin/tool/totara_sync/lib.php: call to totara_sync_element_user->sync()
* line 16 of /admin/tool/totara_sync/run_cron.php: call to tool_totara_sync_cron()

Permalink | Show parent | Reply

Could the delete field be causing the problem since it is a reserved MySQL keyword?

Permalink | Show parent | Reply

I apologize for so many posts, but I fixed it with the following modifications:

in admin/tool/totara_sync/lib.php

at line 186 I changed:

$sql = "INSERT INTO {{$table}} ("
.implode(',',array_keys($chunk[0]))
. ') VALUES ';

TO:

186 $sql = "INSERT INTO {{$table}} (`"
187 .implode('`,`',array_keys($chunk[0]))
188 . '`) VALUES ';

I added the backticks to escape the fieldnames, which escapes the MySQL keyword "delete" so that it can be used as a field name.

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

Still no joy. Re-checked single quotes, but still get the same error.

Could the problem be the mentioned temptables that have to be dropped? If so, which ones are these?

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

Hi Alastair,

csv file and screenshots sent via email

Thanks

Alex

Permalink | Show parent | Reply

Hi,

A patch has been included in todays (11th Oct 2012) release of 2.2.7 that should hopefully clear this up.

Cheers
David

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply