Lets start with what I did...
I copied a table via PHPmyadmin operations/ copy table/(temp1) structure and data. nothing should make any changes to this copy.phpmyadmin shows 1185199 entries
I copy this structure to a new table(temp2). edit structure changing primary to int auto, and CHAR column to CHAR(16).
I then insert into temp2 select from temp1
Start strange part...
phpmyadmin shows 1187015 entries for temp2. looking at data with phpmyadmin tables match up to pg.47408 temp2 continues to pg.47481 I search ID > 1239186(last id on pg.47408) this gives 7056 results not the 1816 I expected.
questions: Why is the rest of the data not reported? why are my id's not matching number of entries? nothing has been deleted from main tables.
SETUP:
Windows 10 pro on 1TB SSD
MySQL version 8.0.27
Apache server (handles PHP for intermediate between other machines)
hardware: i7 4core(8 thread) 24GB Ram
isolated network.
databases/tables: Innodb for all tables.
5 main databases
1 with 4096 tables on 2TB SSD (slowest growing, read the most of mains)
4 with 1024 tables each on 4 or 8TB HDD(needed least often but most writes)
table structures are the same for all main databases
4 Bigint columns, 1 CHAR(32), 1 Bigint Autoinc.
1 work database on 1TB SSD
5 tables that get data from files via load
4 Bigint columns, 1 CHAR(32), 1 CHAR(4), 1 Bigint Autoinc.
2 tables for synchronization
1 int autoinc., 7 int
---------------------
general process now:
other machines "get work" via PHP script, sync. table is locked read updated unlocked.
machine finishes work- transfers files via network, calls php page which updates sync table again.
server runs 5 tabs in web browser looping thru pages 1 tab per main database each
pg.1 get files to load lock and update sync table
truncate work table
load data
pg.2 loops thru tables using insert select b1,b2,b3,b4,Char(32),null where sort = xxxx
go back to pg.1
