I created a php script that takes values from 1 table and inserts them into another.
This executes pretty quickly (1000 rows per second).
Everything went well. There are no duplicates in the table and I think all the values from the other table are inserted.
But something strange caught my attention:
There are a total of 903388 rows in the table but the highest ID that has auto-increment enabled is 898582.
That's a difference of 4806.
The other table has almost the same amount of items as the ID but that is always off by a bit due to the original table always getting new values.
Due to such a large database a MRE could not be produced
So now my question is:
How can the auto-increment value be lower than the total amount of rows?
The datatype of the original tables ID:
bigint(20)
The datatype of the IMAGES table ID:
int(11)
Show create table IMAGES;
CREATE TABLE `IMAGES` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`SRC` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL COMMENT 'Pad naar afbeelding',
`VERWIJDERD` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0=image is online, 1=image is verwijderd',
`DATUM` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Datum van upload',
`IP` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL COMMENT 'ip van uploader',
`SITE` tinyint(1) DEFAULT NULL COMMENT 'site waar image is geupload',
`OID` int(11) DEFAULT NULL COMMENT 'occasion id',
`POSITIE` int(11) NOT NULL DEFAULT 0 COMMENT 'sorteer id',
`TYPE` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0=normaal 1=schade',
PRIMARY KEY (`ID`),
KEY `OID` (`OID`)
) ENGINE=InnoDB AUTO_INCREMENT=898583 DEFAULT CHARSET=latin1
My PHP script that fills the table:
<form action="" method="post">
<label for="fname">Rijen:</label><br/>
offset<input type="number" name="offset"><br/>
amount<input type="number" name="amount"><br/>
<button type="submit" name="submit">Go</button>
</form><br/>
<?php
$per_insert = 100;
if(isset($_POST['submit'])){
echo "Offset: ".$_POST['offset']."<br/>";
echo "Limit: ".$_POST['amount']."<br/>";
$msc = microtime(true);
$count = (is_numeric($_POST['offset']) ? (int)$_POST['offset'] : 0);
$amount = (is_numeric($_POST['amount']) ? (int)$_POST['amount'] : 0);
$qcount = 0;
$filter_array = array('images/occ/', null, '');
for ($i = $count+$per_insert; $i <= $amount; $i+=$per_insert){
$valuesq = array();
$olddataq = $mysqli->query("SELECT `ID`,SITE,DATUM,IP,`IMG_1`,`IMG_2`,`IMG_3`,`IMG_4`,`IMG_5`,`IMG_6`,`IMG_7`,`IMG_8`,`IMG_9`,`IMG_10`,`IMG_11`,`IMG_12`,`IMG_13`,`IMG_14`,`IMG_15`,`IMG_16`,`IMG_17`,`IMG_18`,`IMG_19`,`IMG_20`,`IMGS_1`,`IMGS_2`,`IMGS_3`,`IMGS_4`,`IMGS_5`,`IMGS_6`,`IMGS_7`,`IMGS_8`,`IMGS_9`,`IMGS_10`,`IMGS_11`,`IMGS_12`,`IMGS_13`,`IMGS_14`,`IMGS_15`,`IMGS_16`,`IMGS_17`,`IMGS_18`,`IMGS_19`,`IMGS_20` FROM `OCCASIONS` LIMIT ".$per_insert." OFFSET ".$count.";");
$qcount++;
$schade = $normaal = 0;
while($olddata = $olddataq->fetch_assoc()){
$olddata = array_diff($olddata, $filter_array);
$id = $olddata['ID'];
$datum = $olddata['DATUM'];
$ip = $olddata['IP'];
$site = $olddata['SITE'];
unset($olddata['DATUM']);
unset($olddata['ID']);
unset($olddata['IP']);
unset($olddata['SITE']);
while ($data = current($olddata)) {
$key = explode('_',key($olddata));
if($key[0] == 'IMG'){
//normale image
$datacheck = check_fotodata($data, $id, $key[1], 0);
if($datacheck === false){
$valuesq[] = "('".$data."','".$datum."','".$ip."',".$site.",".$id.", ".$key[1].", 0,0)";
}else{
$valuesq[] = $datacheck;
}
}else{
//schade image
$datacheck = check_fotodata($data, $id, $key[1], 1);
if($datacheck === false){
$valuesq[] = "('".$data."','".$datum."','".$ip."',".$site.",".$id.", ".$key[1].", 1,0)";
}else{
$valuesq[] = $datacheck;
}
}
next($olddata);
}
}
$count += $per_insert;
//var_dump($valuesq);
$mysqli->query("INSERT INTO IMAGES (SRC, DATUM, IP, SITE, OID, POSITIE, TYPE, VERWIJDERD) VALUES ". implode(",", $valuesq));
$qcount++;
}
$msc = microtime(true)-$msc;
echo "buildtime: <br/>";
echo $msc . ' s<br/>'; // in seconds
echo ($msc * 1000) . ' ms<br/>'; // in millseconds
echo $qcount . "<br/>";
$msc = microtime(true);
}
function check_fotodata($image, $oid, $pos, $type){
global $qcount, $mysqli;
$checkdataq = $mysqli->query("SELECT * FROM FOTODATA WHERE KID = ". $oid ." AND IMG = '". $image ."'");
$qcount++;
if($checkdataq->num_rows > 0){
$checkdata = $checkdataq->fetch_assoc();
if($checkdata['INFO'] == 'Verwijderd'){
$del = 1;
}else{
$del = 0;
}
return "('".$checkdata['IMG']."', '".$checkdata['DATUM']."', '".$checkdata['IP']."', '".$checkdata['SITE']."', '".$checkdata['KID']."',".$pos.",".$type.",".$del.")";
}else{
return false;
}
}
Please check count of table by below query first then compare. Count of table and max id should be equal if you use auto_increment_offset 1 and auto_increment_increment 1 in MySQL.
select count(*) from IMAGES;
Please comment what you find.