'''
Created on 14 May 2020

@author: ph1jb

Demonstrates MySQL-8.0.20 connector bug: insert ... VALUES(%s,%s, ...) as v

time to run: insert ... VALUES(%s,%s, ...) as v on duplicate key update c1=v.c1, c2=v2, ...
doubles as n columns 
21 cols: ~2s
22 cols: ~5s
23 cols: ~10s
24 cols: ~20s
25 cols: ~40s
whereas inserts with original syntax: insert ... VALUES(%s,%s, ...) on duplicate key update c1=VALUES(c1), c2=VALUES(v2), ...
time to run remains < 1s

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html#mysqld-8-0-20-deprecation-removal

Client:
mysql-connector-python-py3 8.0.20-1ubuntu20.04 all MySQL database driver written in pure Python 3
mysql-client-8.0 8.0.20-0ubuntu0.20.04.1 amd64 MySQL database client binaries
Server:
mysql-community-server 8.0.20-1ubuntu18.04 amd64 MySQL Server

Output
======
VALUES: True
n cols 21
Run time: 0.03934788703918457
n cols 22
Run time: 0.03991842269897461
n cols 23
Run time: 0.03888273239135742
n cols 24
Run time: 0.04139447212219238
n cols 25
Run time: 0.03961658477783203

VALUES: False
n cols 21
Run time: 1.0900123119354248
n cols 22
Run time: 2.0359716415405273
n cols 23
Run time: 4.018187046051025
n cols 24
Run time: 8.130314826965332
n cols 25
Run time: 15.858994007110596

Demo needs database: test
use test;
CREATE TABLE `mysql_on_dup_bug` (
  `id` int unsigned NOT NULL,
  `c0` float DEFAULT NULL,
  `c1` float DEFAULT NULL,
  `c2` float DEFAULT NULL,
  `c3` float DEFAULT NULL,
  `c4` float DEFAULT NULL,
  `c5` float DEFAULT NULL,
  `c6` float DEFAULT NULL,
  `c7` float DEFAULT NULL,
  `c8` float DEFAULT NULL,
  `c9` float DEFAULT NULL,
  `c10` float DEFAULT NULL,
  `c11` float DEFAULT NULL,
  `c12` float DEFAULT NULL,
  `c13` float DEFAULT NULL,
  `c14` float DEFAULT NULL,
  `c15` float DEFAULT NULL,
  `c16` float DEFAULT NULL,
  `c17` float DEFAULT NULL,
  `c18` float DEFAULT NULL,
  `c19` float DEFAULT NULL,
  `c20` float DEFAULT NULL,
  `c21` float DEFAULT NULL,
  `c22` float DEFAULT NULL,
  `c23` float DEFAULT NULL,
  `c24` float DEFAULT NULL,
  `c25` float DEFAULT NULL,
  `c26` float DEFAULT NULL,
  `c27` float DEFAULT NULL,
  `c28` float DEFAULT NULL,
  `c29` float DEFAULT NULL,
  `c30` float DEFAULT NULL,
  `c31` float DEFAULT NULL,
  `c32` float DEFAULT NULL,
  `c33` float DEFAULT NULL,
  `c34` float DEFAULT NULL,
  `c35` float DEFAULT NULL,
  `c36` float DEFAULT NULL,
  `c37` float DEFAULT NULL,
  `c38` float DEFAULT NULL,
  `c39` float DEFAULT NULL,
  `c40` float DEFAULT NULL,
  `c41` float DEFAULT NULL,
  `c42` float DEFAULT NULL,
  `c43` float DEFAULT NULL,
  `c44` float DEFAULT NULL,
  `c45` float DEFAULT NULL,
  `c46` float DEFAULT NULL,
  `c47` float DEFAULT NULL,
  `c48` float DEFAULT NULL,
  `c49` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


insert into test.mysql_on_dup_bug (id,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24) 
values (%s ,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) as v 
on duplicate key update c0=v.c0,c1=v.c1,c2=v.c2,c3=v.c3,c4=v.c4,c5=v.c5,c6=v.c6,c7=v.c7,c8=v.c8,c9=v.c9,c10=v.c10,c11=v.c11,
c12=v.c12,c13=v.c13,c14=v.c14,c15=v.c15,c16=v.c16,c17=v.c17,c18=v.c18,c19=v.c19,c20=v.c20,c21=v.c21,c22=v.c22,c23=v.c23,c24=v.c24
[(1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)]
Run time: 15.914669752120972

'''

from time import time

from mysql.connector import connect, DatabaseError 

def insert(cur, ncols=2, values=False):
        cols = ','.join([f'c{i}' for i in range(ncols)])
        placeholders = ',%s' * ncols
        on_dup = None
        if values:
            on_dup = ','.join([f'c{i}=VALUES(c{i})' for i in range(ncols)]) 
        else:
            on_dup = ','.join([f'c{i}=v.c{i}' for i in range(ncols)])
        data = [(1, *range(ncols))]
        table = 'test.mysql_on_dup_bug'
        statement = (f'insert into {table} (id,{cols}) '
                     f'values (%s {placeholders}) as v '
                     f'on duplicate key update {on_dup}')
        print(f'n cols {ncols}')
#         print(statement)
#         print(data)
        cur.executemany(statement, data) 
        

if __name__ == '__main__':
    mysql_credentials = {'host':'Redacted',
                         'user':'tester1',
                         'password':'Redacted',
                         'database':'test',
                         'autocommit':True,
                         }
    cnx = connect(**mysql_credentials)
    cur = cnx.cursor()
    try:
        for values in (True, False):
            print(f'VALUES: {values}')
            for ncols in range(21, 26):
                start = time()
                insert(cur, ncols, values=values) 
                end = time()
                print(f'Run time: {end - start}') 
            print()                
    except DatabaseError as err:
        print(f'Something went wrong: {err}')