mysql maxallowedpacket maximum_max_allowed_packet

最新推荐文章于 2022-09-29 18:06:28 发布

简单的艾伦

最新推荐文章于 2022-09-29 18:06:28 发布

阅读量100

点赞数

文章标签： mysql maxallowedpacket maximum

本文链接：https://blog.csdn.net/weixin_32955259/article/details/113332459

版权

MySQL 批量导入异常处理最大允许包大小代码优化

关键词由CSDN通过智能技术生成

from mysql_tool import *

import copy

s = '''

INSERT INTO `qqzone`.`myu` (`id`, `uid`, `age`, `gender`, `marriageStatus`, `education`, `consumptionAbility`, `LBS`, `interest1`, `interest2`, `interest3`, `interest4`, `interest5`, `kw1`, `kw2`, `kw3`, `kw4`, `kw5`, `topic1`, `topic2`, `topic3`, `topic4`, `topic5`, `ct`, `appIdAction`, `appIdInstall`, `os`, `carrier`, `house`) VALUES ('33', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

'''.replace('\n', '')

indb_fields_s = '{}{}'.format(s.split('VALUES')[0], ' VALUES ').replace('`id`,', '')

fields_l = [i.replace(' ', '').replace('`', '') for i in s.split('(')[1].split(')')[0].split(',')]

val_d = {}

for i in fields_l:

val_d[i] = 'NULL'

del val_d['id']

indb_step, indb_step_s = 2000, ''

f = 'userFeature.data'

with open(f, 'r') as fr:

sql_d_l, indb_step_c = [], 0

for i in fr:

break

sql_d = copy.deepcopy(val_d)

l = i.replace('\n', '').split('|')

for ii in l:

try:

ix_ = ii.index(' ')

k, v = ii[0:ix_], ii[ix_ + 1:]

# if k == 'appIdInstall':

# continue

sql_d[k] = v

except Exception as e:

print(e)

sql_d_l.append(sql_d)

indb_step_c += 1

if indb_step_c % indb_step == 0:

s_l = []

for d in sql_d_l:

s_l.append('("{}")'.format('","'.join([d[k] for k in d])))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

print(logs_s)

sql_d, sql_d_l, indb_step_c = val_d, [], 0

if indb_step_c % indb_step != 0:

s_l = []

for d in sql_d_l:

s_l.append('("{}")'.format('","'.join([d[k] for k in d])))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

print(logs_s)

f = 'train.csv'

indb_step, indb_step_s = 20000, ''

indb_fields_s = 'INSERT INTO `qqzone`.`myt` ( `aid`, `uid`, `label`) VALUES '

with open(f, 'r') as fr:

sql_l, indb_step_c = [], 0

for i in fr:

if 'aid' in i:

continue

sql_l.append([ii for ii in i.replace('\n', '').split(',')])

indb_step_c += 1

if indb_step_c % indb_step == 0:

s_l = []

for l in sql_l:

s_l.append('("{}")'.format('","'.join(l)))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

# print(logs_s)

print(e)

sql_d, sql_d_l, indb_step_c = val_d, [], 0

if indb_step_c % indb_step == 0:

s_l = []

for l in sql_l:

s_l.append('("{}")'.format('","'.join(l)))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

# print(logs_s)

print(e)

sql_d, sql_d_l, indb_step_c = val_d, [], 0

(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')

(1153, "Got a packet bigger than 'max_allowed_packet' bytes")

(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')

(1153, "Got a packet bigger than 'max_allowed_packet' bytes")

from mysql_tool import *

import copy

s = '''

'''.replace('\n', '')

indb_fields_s = '{}{}'.format(s.split('VALUES')[0], ' VALUES ').replace('`id`,', '')

fields_l = [i.replace(' ', '').replace('`', '') for i in s.split('(')[1].split(')')[0].split(',')]

val_d = {}

for i in fields_l:

val_d[i] = 'NULL'

del val_d['id']

indb_step, indb_step_s = 2000, ''

f = 'userFeature.data'

with open(f, 'r') as fr:

sql_d_l, indb_step_c = [], 0

for i in fr:

break

sql_d = copy.deepcopy(val_d)

l = i.replace('\n', '').split('|')

for ii in l:

try:

ix_ = ii.index(' ')

k, v = ii[0:ix_], ii[ix_ + 1:]

# if k == 'appIdInstall':

# continue

sql_d[k] = v

except Exception as e:

print(e)

sql_d_l.append(sql_d)

indb_step_c += 1

if indb_step_c % indb_step == 0:

s_l = []

for d in sql_d_l:

s_l.append('("{}")'.format('","'.join([d[k] for k in d])))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

print(logs_s)

sql_d, sql_d_l, indb_step_c = val_d, [], 0

if indb_step_c % indb_step != 0:

s_l = []

for d in sql_d_l:

s_l.append('("{}")'.format('","'.join([d[k] for k in d])))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

print(logs_s)

f = 'train.csv'

indb_step, indb_step_s = 10000, ''

indb_fields_s = 'INSERT INTO `qqzone`.`myt` ( `aid`, `uid`, `label`) VALUES '

with open(f, 'r') as fr:

sql_l, indb_step_c = [], 0

for i in fr:

if 'aid' in i:

continue

sql_l.append([ii for ii in i.replace('\n', '').split(',')])

indb_step_c += 1

if indb_step_c % indb_step == 0:

s_l = []

for l in sql_l:

s_l.append('("{}")'.format('","'.join(l)))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

# print(logs_s)

print(e)

sql_l, indb_step_c = [], 0

if indb_step_c % indb_step == 0:

s_l = []

for l in sql_l:

s_l.append('("{}")'.format('","'.join(l)))

indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))

try:

mysql_write(indb_step_s)

indb_step_c = 0

except Exception as e:

logs_l = [e, indb_step_s]

logs_s = '||'.join(logs_l)

# print(logs_s)

print(e)

sql_l, indb_step_c = [], 0

https://dev.mysql.com/doc/refman/8.0/en/program-variables.html

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes: