Looped Insert changes to Select

Ok, this one has me baffled. Hopefully someone out there can help because I am out of ideas. I have a file I am importing, reading each line, manipulating and inserting into a table. It runs fine for 584 lines and the suddenly the INSERTS become SELECTS! 
I have tried removing the first 584 lines from the csv file and it did the same thing (although at a different line number). I tried removing just 500 lines to leave the last valid line to see what would happen. That time it ran 800 or so lines before switching, so I don't think it is data.

Data Sample (with the full file it is failing on the line starting with 1192):

Code:

1188,"08990",1,"Fast Voip Us",,,"AT&T","89014104272936908990",,"5306918987","6003474FF6CE",1,2020-08-27,,1,"152971","Joel",,0,0,,,,
1189,"08982",1,"Fast Voip Us",,,"AT&T","89014104272936908982",,"5306919020","6003474DBBD2",1,2020-08-27,,1,"144001","Joel",,0,0,,,,
1190,"06903",1,"Fast Voip Us",,,"AT&T","89014104272936906903",,"5306918954","6003474B5143",1,2020-08-27,,1,"135851","Joel",,0,0,,,,
1191,"06911",1,"Fast Voip Us",,,"AT&T","89014104272936906911",,"5306918955","6003474B7187",1,2020-08-27,,1,"135161","Joel",,0,0,,,,
1192,"06929",1,"Fast Voip Us",,,"AT&T","89014104272936906929",,"5306919107","6003474DBEDF",1,2020-08-27,,1,"129501","Joel",,0,0,,,,
1193,"06937",1,"Fast Voip Us",,,"AT&T","89014104272936906937",,"5306918956",,1,2020-08-27,,1,"6600AEL",,,0,0,,,,
1194,"07018",1,"Fast Voip Us",,,"AT&T","89014104272936907018",,"5306918972","6003474ad6a3",1,2020-08-27,,1,"129991","Joel",,0,0,,,,

Code Sample:

PHP Code:

while (($filedata = fgetcsv($file, 5000, ",")) !== FALSE) {
// Skip first row
if($i > 0) {
    //Add a card record
    $data = array(
        'vendor_id'            => $vendors[strtolower(trim($filedata[3]))],
        'carrier'            => $filedata[6],
        'card_num'            => $filedata[7],
        'mdn'                => $filedata[9],
        'macid'            => $filedata[10],
        'invoicenum'        => $filedata[5],
        'cost'                => ($filedata[4] ? $filedata[4]*1.00 : 0.00),
        'location'            => 'lo-p3zd82dgjj',
        'notes'            => $filedata[20],
        'badcard'            => $filedata[18],
        'date_purchased'    => date("Y-m-d",strtotime('2016-01-01')),
        'date_activated'    => date("Y-m-d",strtotime($filedata[12])),
        'date_deactivated'    => ($filedata[13] ? date("Y-m-d",strtotime($filedata[13])): NULL),
        'purchaser'            => ($filedata[2] != 1 ? 'Interstar' : 'ShastaBeam'),
        'is_active'            => $filedata[11],
        'customer_id'        => 0,
    );
    $lastInsertID = $this->simsModel->insert($data);
    echo $this->simsModel->lastQuery . '<br>';
    //$lastInsertID = $insert->insertID();

    // Add Equipment History Record
    $logdata = [
        'card_id'        => $lastInsertID,
        'user_id'          => 0,
        'customer_id'    => 0,
        'date_changed'  => date("Y-m-d H:i:s"),
        'action'          => 'SIM Card imported from Access.',
    ];
    $this->simslogmodel->insert($logdata);
    echo $this->simslogmodel->lastQuery . '<br>';
}
$i++; 


Sample from the echo statements where it changes:

Code:

INSERT INTO `inventory_sims` (`vendor_id`, `carrier`, `card_num`, `mdn`, `macid`, `invoicenum`, `cost`, `location`, `notes`, `badcard`, `date_purchased`, `date_activated`, `date_deactivated`, `purchaser`, `is_active`, `customer_id`) VALUES ('4', 'AT&T', '89014103272867252393', '5308067249', '6003474ECA0C', 'N100-OY-2063951', '0', 'lo-p3zd82dgjj', '', '0', '2016-01-01', '2021-03-22', NULL, 'Interstar', '1', '0')
INSERT INTO `inventory_sims_history` (`card_id`, `user_id`, `customer_id`, `date_changed`, `action`) VALUES ('881', '0', '0', '2021-10-20 09:46:08', 'SIM Card imported from Access.')
INSERT INTO `inventory_sims` (`vendor_id`, `carrier`, `card_num`, `mdn`, `macid`, `invoicenum`, `cost`, `location`, `notes`, `badcard`, `date_purchased`, `date_activated`, `date_deactivated`, `purchaser`, `is_active`, `customer_id`) VALUES ('4', 'AT&T', '89014103272867252559', '5308067236', '6003474EC419', 'N100-OY-2063951', '0', 'lo-p3zd82dgjj', '', '0', '2016-01-01', '2021-03-22', NULL, 'Interstar', '1', '0')
INSERT INTO `inventory_sims_history` (`card_id`, `user_id`, `customer_id`, `date_changed`, `action`) VALUES ('882', '0', '0', '2021-10-20 09:46:08', 'SIM Card imported from Access.')
SELECT 1 FROM `inventory_sims` WHERE `card_num` = '8901260191742830170F' LIMIT 1
INSERT INTO `inventory_sims_history` (`card_id`, `user_id`, `customer_id`, `date_changed`, `action`) VALUES ('', '0', '0', '2021-10-20 09:46:08', 'SIM Card imported from Access.')
SELECT 1 FROM `inventory_sims` WHERE `card_num` = '8901260235704862182F' LIMIT 1
INSERT INTO `inventory_sims_history` (`card_id`, `user_id`, `customer_id`, `date_changed`, `action`) VALUES ('', '0', '0', '2021-10-20 09:46:08', 'SIM Card imported from Access.')
SELECT 1 FROM `inventory_sims` WHERE `card_num` = '8901260191742832499F' LIMIT 1

Hopefully someone out there has a clue as to what is going on. It makes absolutely no sense!

TIA
Marc

possible for duplicate data?

Code:

$this->simsModel->replace($data);

possible for duplicate data?

Code:

$this->simsModel->replace($data);

Didn't make any difference. Still changing from INSERT to SELECT at the same point.

Code:

REPLACE INTO `inventory_sims` (`vendor_id`, `carrier`, `card_num`, `mdn`, `macid`, `invoicenum`, `cost`, `location`, `notes`, `badcard`, `date_purchased`, `date_activated`, `date_deactivated`, `purchaser`, `is_active`, `customer_id`) VALUES ('4', 'AT&T', '89014104272936906903', '5306918954', '6003474B5143', '', 0, 'lo-p3zd82dgjj', '', '0', '2016-01-01', '2020-08-27', NULL, 'ShastaBeam', '1', 0)
REPLACE INTO `inventory_sims` (`vendor_id`, `carrier`, `card_num`, `mdn`, `macid`, `invoicenum`, `cost`, `location`, `notes`, `badcard`, `date_purchased`, `date_activated`, `date_deactivated`, `purchaser`, `is_active`, `customer_id`) VALUES ('4', 'AT&T', '89014104272936906911', '5306918955', '6003474B7187', '', 0, 'lo-p3zd82dgjj', '', '0', '2016-01-01', '2020-08-27', NULL, 'ShastaBeam', '1', 0)
SELECT 1 FROM `inventory_sims` WHERE `card_num` = '89014104272936906929' LIMIT 1
SELECT 1 FROM `inventory_sims` WHERE `card_num` = '89014104272936906937' LIMIT 1

---

Thinking about @ikesela post got me to thinking about duplicates. I don't understand why it would be an issue as I am not concerned about it, but I took the first card number after it switched to SELECT statements and sure enough it appears in the data twice. I removed one and the script ran until it hit the next duplicate record.

So, why? What makes it change to SELECT? Why didn't the idea of using ->replace fix the issue?

I love this job! Thanks @ikesela

@SoccerGuy3 ,

Checking at php.net (https://www.php.net/manual/en/function.fgetcsv.php ) did you account for line endings? As mentioned as one of the notes. Also, are you sure that the longest line length is less then 5000 characters?

@SoccerGuy3 ,

Checking at php.net (https://www.php.net/manual/en/function.fgetcsv.php ) did you account for line endings? As mentioned as one of the notes.  Also, are you sure that the longest line length is less then 5000 characters?

I haven't had any issues with line endings. I would think if there was a problem with them, it would have happened on line #2. My problem started down around line 550.

I am sure they aren't anywhere near 5000 characters, but just to be doubly sure, I tried it with the zero/unlimited option and got the same results. 

For some reason, a duplicate entry caused CI to change from INSERTS to SELECT statements. So bizarre. I could see that if it errored on one line, but then on the next loop shouldn't it have gone back to the INSERTs?

Second to last data example are missing some data compared to other lines (there are empty values inserted). I don't think it should affect query generation at all, but it is worth to give it a shot, and change delete this line/change it, and see if it makes any difference.

Thread about my project that is using CodeIgniter:

Topic: Mysticat.pet - online game themed around cats

@SoccerGuy3 ,

Are you sure that there are no hidden special characters? Also, is the input file clean (does not have dirty data)? Also, what version of PHP & MySQL are you using?

@SoccerGuy3 ,

Are you sure that there are no hidden special characters?  Also, is the input file clean (does not have dirty data)?  Also, what version of PHP & MySQL are you using?

I ran the csv file through BBEdits Zap Gremlins as I had that thought at some point (hidden characters). If I remove the 500 lines or so that precede where it is having the issue those same lines process without a problem (it does "die" later in the file however). Put those lines back and it reverts to the previous problem. If I remove that line (the one it dies on referenced above), it runs along fine for another 300+ lines before switching to SELECTS.

Best I can tell, the switch happens when it hits a record that has duplicate information. Which really doesn't make any sense to me as the ID/Key field is NOT in the csv. It is just an auto-increment in the table. Doing an insert it shouldn't even be looking for duplicate data, right?

Running PHP 8.0.9 and MariaDB 10.6.4

Table structure:

Code:

CREATE TABLE `inventory_sims` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(5) unsigned NOT NULL,
  `customer_id` int(6) unsigned NOT NULL,
  `carrier` varchar(128) DEFAULT NULL,
  `card_num` varchar(128) DEFAULT NULL,
  `mdn` varchar(128) DEFAULT NULL,
  `macid` varchar(128) DEFAULT NULL,
  `invoicenum` varchar(128) DEFAULT NULL,
  `date_activated` datetime DEFAULT NULL,
  `date_deactivated` datetime DEFAULT NULL,
  `badcard` tinyint(1) NOT NULL DEFAULT 0,
  `cost` decimal(8,2) NOT NULL DEFAULT 0.00,
  `location` varchar(128) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `purchaser` varchar(128) DEFAULT NULL,
  `date_purchased` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2656 DEFAULT CHARSET=utf8mb3;

My guess is something in the model looks for duplicate by doing a select before attempting an insert. If the record already exist, in doesn’t insert the line. Then when you print the last query, you’re printing that select because it is the last query if the insert didn’t happen.

CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/

My guess is something in the model looks for duplicate by doing a select before attempting an insert. If the record already exist, in doesn’t insert the line. Then when you print the last query, you’re printing that select because it is the last query if the insert didn’t happen.

That makes a lot of sense (check for dup and then insert). But is it a CI bug when it doesn't return on the next iteration to doing inserts of data that is NOT a dup or is it "dying" or getting stuck with that data because it doesn't know what to do now? I did try switching to the REPLACE language, but that didn't help either.