insert_records only inserting 50 records

insert_records only inserting 50 records

by Kimber Warden -
Number of replies: 4

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: -
In reply to Kimber Warden

Re: insert_records only inserting 50 records

by Raad Al-Rawi -

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: -
In reply to Kimber Warden

Re: insert_records only inserting 50 records

by Darko Miletić -

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: -
In reply to Darko Miletić

Re: insert_records only inserting 50 records

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of 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: -
In reply to Dan Marsden

Re: insert_records only inserting 50 records

by Darko Miletić -

I stand corrected. Thanks for clarifying this Dan.

Average of ratings: -