General developer forum

insert_records only inserting 50 records

 
Picture of Kimber Warden
insert_records only inserting 50 records
Group Particularly helpful Moodlers

I'm trying to insert 800+ rows of data from a .csv file into my plugin's table, which has 43 columns, but only the first 50 rows are being inserted.

$file = fopen($new_roster_file, "r");

$all_rows = array();

$header = null;

while ($row = fgetcsv($file, $delimiter = ",")) {

if ($header === null) {

$header = $row;

continue;

}

$all_rows[] = array_combine($header, $row);

}

print("All rows: <p><p>");

print_r($all_rows);  <---------THIS CORRECTLY PRINTS ALL 800+ OBJECTS

$DB->insert_records('auth_sko_db_import_students', $all_rows);   <-------THIS ONLY INSERTS THE FIRST 50 OBJECTS


Is there a maximum number of objects that can be inserted via $DB->insert_records? Or is there something else I need to change?

 
Average of ratings: -
Picture of Raad Al-Rawi
Re: insert_records only inserting 50 records
 

Hi Kimber


A complete stab in the dark, but I'd look at what data the system is trying to insert for record #51.

I don't know the structure of your plugin table, but it could be the case that for that record there is a value missing for a column that is NOT NULL, for example.


Good luck!


Raad

 
Average of ratings: -
Picture of Darko Miletić
Re: insert_records only inserting 50 records
Group Core developersGroup Particularly helpful Moodlers

Most likely you did not correctly fetched CSV values and thus your data are incorrect. fgetcsv/fputcsv are notably buggy. You should use instead CsvRfc library - https://github.com/ajgarlag/AjglCsvRfc.


What you are trying to do with regards to insert is bad idea. This will incur a huge load on your database since insert_records essentially does call to insert_record in the loop. If amount of records is minimal and it is one time thing OK. But if you plan on running this more often with large record-sets I would reconsider.

What I propose should be better for your DB health and faster (Applies ONLY to MySQL and mysqli extension):

// Prepare massive insert SQL in the format:
// INSERT INTO {table} ('col1', 'col2', 'col3') VALUES(v1, v2, v3);
// INSERT INTO {table} ('col1', 'col2', 'col3') VALUES(v1, v2, v3);
// etc.
$sql = "multiple queries";
// This applies only to MySQL and mysqli extension
try {
try {
$transaction = $DB->start_delegated_transaction();
$DB->change_database_structure($sql);
$transaction->allow_commit();
} catch (Exception $e) {
// Make sure transaction is valid.
if (!empty($transaction) && !$transaction->is_disposed()) {
$transaction->rollback($e);
}
}
} catch (Exception $e) {
// Silence the rollback exception or do something else.
}


Also you would have to ensure your query is not too big since rdbms has a limit on how long a command it can take at a time. You might need to split records in batches like every 300 items before execution etc.


 
Average of ratings: -
Picture of Dan Marsden
Re: insert_records only inserting 50 records
Group Core developersGroup Moodle Course Creator Certificate holdersGroup Particularly helpful MoodlersGroup Plugin developersGroup Plugins guardiansGroup TestersGroup Translators
Darko - your assumptions on insert_records is not correct. it does not loop on a single insert_record call. It is designed for bulk inserts and should bundle groups of data as a single bulk insert. The number of records it inserts at a time depends on various factors including your database type (mysql/postgres etc) and various settings in your database such as max_allowed_packet(mysql) etc.


From memory mysql does default to 50 records at a time under some circumstances which might be why the first batch of 50 works but the 2nd batch fails - I'd check Darko's comments about the data being correct but I'd also try removing the first 50 records from your csv and see if the next group of 50 records fails - it could be there is something about that 2nd group of 50 records that pushes your database over a limit or the data is incorrect somehow.

Obviously make sure you have debugging level set to developer, display errors on etc. 

 
Average of ratings: -
Picture of Darko Miletić
Re: insert_records only inserting 50 records
Group Core developersGroup Particularly helpful Moodlers

I stand corrected. Thanks for clarifying this Dan.

 
Average of ratings: -