-- release/opt
BugNumber=117165
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &



1. create table t1 with primary key
2. create local datafile1, use 'load data replace' to load the data to table t1 and record the time.
3. create new local datafile2 whose primary key is the same as datafile1, but other columns are different. Then use 'load data replace' to load data to table t1 and record the time.
4. create more index on table t1. And repeat step 3.


--

1. create table t1 with primary key
2. create local datafile1, use 'load data replace' to load the data to table t1 and record the time.

## Create the following table

create table t1 (
 id int primary key,
 col1 int NOT NULL,
 col2 varchar(500)  NOT NULL,
 col3 int  NOT NULL,
 col4 varchar(500)  NOT NULL,
 col5 char(20)  NOT NULL 
)engine=innodb;

## Generate 1Mil rows by the following perl script

cat datafile1.pl  datafile2.pl
#!/usr//bin/perl

for(my $i=1; $i <= 1000000; $i++) {
  my $col1=$i;
  my $col2 = 'c2' x 150;
  my $col3 = $i;
  my $col4 = 'c4' x 150;
  my $col5 = 'c5'.$i;
  print "$i,$col1,$col2,$col3,$col4,$col5\n";
}

#!/usr//bin/perl

for(my $i=1; $i <= 1000000; $i++) {
  my $col1=$i;
  my $col2 = 'c2' x 151;
  my $col3 = $i+1;
  my $col4 = 'c4' x 151;
  my $col5 = 'c5'.($i+1);
  print "$i,$col1,$col2,$col3,$col4,$col5\n";
}

perl datafile1.pl > datafile1.txt
perl datafile2.pl > datafile2.txt


2. Load the data into table


LOAD DATA LOCAL INFILE 'datafile1.txt'
REPLACE INTO TABLE t1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

3. create new local datafile2 whose primary key is the same as datafile1, but other columns are different. Then use 'load data replace' to load data to table t1 and record the time.
## Generate 1Mil rows by the following perl script

cat datafile2.pl
#!/usr//bin/perl

for(my $i=1; $i <= 1000000; $i++) {
  my $col1=$i;
  my $col2 = 'col2' x 151;
  my $col3 = $i+1;
  my $col4 = 'col4' x 151;
  my $col5 = 'col5'.$i+1;


  print "$col1,$col2,$col3,$col4,$$col5\n";
}

perl datafile2.pl > datafile2.txt


LOAD DATA LOCAL INFILE 'datafile2.txt'
REPLACE INTO TABLE t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';


4. create more index on table t1. And repeat step 3.
ALTER TABLE t1 ADD INDEX idx_col1(col1);
ALTER TABLE t1 ADD INDEX idx_col2(col2);
ALTER TABLE t1 ADD INDEX idx_col3(col3);
ALTER TABLE t1 ADD UNIQUE INDEX idx_col5(col5);




LOAD DATA LOCAL INFILE 'datafile2.txt'
REPLACE INTO TABLE t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';



#### 

### 8.0.41 binary tarball

bin/mysql -uroot -S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.41 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t1 (
    ->  id int primary key,
    ->  col1 int,
    ->  col2 varchar(500),
    ->  col3 int,
    ->  col4 varchar(500),
    ->  col5 char(20)
    -> )engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA LOCAL INFILE 'datafile1.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 1000000 rows affected (25.72 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (49.63 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX idx_col1(col1);
Query OK, 0 rows affected (2.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX idx_col2(col2);
Query OK, 0 rows affected (1 min 22.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX idx_col3(col3);
Query OK, 0 rows affected (2.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD UNIQUE INDEX idx_col5(col5);
Query OK, 0 rows affected (7.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

Query OK, 2000000 rows affected (1 min 30.82 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql>
mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (1 min 45.31 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (1 min 32.81 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (1 min 54.98 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (2 min 8.21 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (2 min 1.89 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0


## With the proposed fix

-- apply Wang Zhengmao's patch on top of 8.0.41 and build


scl enable devtoolset-11 bash
MYSQL_VERSION="Bug117165"
TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf bld/
mkdir bld && cd bld
rm -rf CMakeCache.txt

/home/umshastr/work/binaries/utils/cmake-3.28.1/bin/cmake .. -DWITH_BOOST=../boost/ -DBUILD_CONFIG=mysql_release -DWITH_SSL=system -DWITH_UNIT_TESTS=0  -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja
ninja -j 16
ninja install



bin/mysql -uroot -S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.41 Source distribution

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> LOAD DATA LOCAL INFILE 'datafile1.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 1000000 rows affected (26.67 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (46.47 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX idx_col1(col1);
Query OK, 0 rows affected (1.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX idx_col2(col2);
Query OK, 0 rows affected (1 min 20.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX idx_col3(col3);
Query OK, 0 rows affected (1.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD UNIQUE INDEX idx_col5(col5);
Query OK, 0 rows affected (5.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (1 min 27.76 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (2 min 6.80 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0


mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (1 min 47.34 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (2 min 9.26 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datafile2.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2000000 rows affected (2 min 23.89 sec)
Records: 1000000  Deleted: 1000000  Skipped: 0  Warnings: 0