导读
lower_case_table_names 是将表名转换为小写. 即:
为0时: 不启用转换小写, 也就是区分大小写
为1时: 转换为小写, 也就是不区分大小写.
不支持动态修改.
Command-Line Format |
|
|---|---|
System Variable |
|
Scope | Global |
Dynamic | No |
| No |
Type | Integer |
Default Value (macOS) |
|
Default Value (Unix) |
|
Default Value (Windows) |
|
Minimum Value |
|
Maximum Value |
|
该参数默认是0, 即区分大小写. 但现在又想要不区分大小写了. 也就是想设置其值为1. 首先我们要确保数据库里面的表均为小写, 我们可以通过如下sql查询
代码语言：sql复制select * from (select lower(concat(table_schema,'.',table_name)) as n1, concat(table_schema,'.',table_name) as n2 from information_schema.tables where table_schema not in ('sys','information_schema')) as t where t.n1!=t.n2;
请自行修改
然后我们再修改该参数, 理论上就是可行的了. 但却报错了:
代码语言：txt复制2024-09-11T05:58:38.397733Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-11T05:58:39.389682Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-11T05:58:39.452780Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2024-09-11T05:58:39.453065Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-09-11T05:58:39.453091Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-09-11T05:58:40.296502Z 0 [System] [MY-010910] [Server] /soft/mysql_3306/mysqlbase/mysql/bin/mysqld-debug: Shutdown complete (mysqld 8.0.37-debug) MySQL Community Server - GPL - Debug.
说和数据字典不一致. 啊.这.......
分析
源码分析
首先我们先根据报错Data Dictionary initialization failed 找下相关的错误码. 我们直接使用万能的grep -r来找. (通常位于share/messages_to_error_log.txt)
grep -r 'Data Dictionary initialization failed'
发现对于的名字叫ER_DD_INIT_FAILED (应该是数据字典初始化失败的意思, 额,就是字面意思)
然后我们再使用万能的grep -r 看下是位于哪段代码的报错
grep -r ER_DD_INIT_FAILED
发现就是主函数.....(如果熟悉mysql启动流程的话, 应该直接就能猜到了)
init_sql_command_flags();
/*
plugin_register_dynamic_and_init_all() needs DD initialized.
Initialize DD to create data directory using current server.
*/
if (opt_initialize) {
if (!is_help_or_validate_option()) {
if (dd::init(dd::enum_dd_init_type::DD_INITIALIZE)) {
LogErr(ERROR_LEVEL, ER_DD_INIT_FAILED);
unireg_abort(1);
}
if (dd::init(dd::enum_dd_init_type::DD_INITIALIZE_SYSTEM_VIEWS)) {
LogErr(ERROR_LEVEL, ER_SYSTEM_VIEW_INIT_FAILED);
unireg_abort(1);
}
}
} else {
/*
Initialize DD in case of upgrade and normal normal server restart.
It is detected if we are starting on old data directory or current
data directory. If it is old data directory, DD tables are created.
If server is starting on data directory with DD tables, DD is initialized.
*/
if (!is_help_or_validate_option() &&
dd::init(dd::enum_dd_init_type::DD_RESTART_OR_UPGRADE)) {
LogErr(ERROR_LEVEL, ER_DD_INIT_FAILED);
/* If clone recovery fails, we rollback the files to previous
dataset and attempt to restart server. */
int exit_code =
clone_recovery_error ? MYSQLD_RESTART_EXIT : MYSQLD_ABORT_EXIT;
unireg_abort(exit_code);
}
}也就是dd::init(dd::enum_dd_init_type::DD_RESTART_OR_UPGRADE))的时候返回true了. 我们再看看dd::init在干嘛
我们用类似的方法找到了namespace下的init (sql/dd/impl/dd.cc)
bool init(enum_dd_init_type dd_init) {
if (dd_init == enum_dd_init_type::DD_INITIALIZE ||
dd_init == enum_dd_init_type::DD_RESTART_OR_UPGRADE) {
cache::Shared_dictionary_cache::init();
System_tables::instance()->add_inert_dd_tables();
System_views::instance()->init();
}
return Dictionary_impl::init(dd_init);
}发现又是调用的Dictionary_impl::init .... 继续呗.
找到对应代码为如下, 即调用了run_bootstrap_thread(sql/dd/impl/dictionary_impl.cc), 这应该是最后一层了吧..
代码语言：c 复制 /*
Creation of Dictionary Tables in old Data Directory
This function also takes care of normal server restart.
*/
else if (dd_init == enum_dd_init_type::DD_RESTART_OR_UPGRADE)
result = ::bootstrap::run_bootstrap_thread(
nullptr, nullptr, &upgrade_57::do_pre_checks_and_initialize_dd,
SYSTEM_THREAD_DD_INITIALIZE);这里应该是do_pre_checks_and_initialize_dd更关键. 我们看下其实现
我们在sql/dd/upgrade_57/upgrade.cc中找到如下信息
/*
Initialize InnoDB in restart mode if mysql.ibd is present.
Else, initialize InnoDB in upgrade mode to create mysql tablespace
and upgrade redo and undo logs.
If mysql.ibd does not exist but upgrade stage tracking file exist
This can happen in rare scenario when server detects it needs to upgrade.
Server creates mysql_dd_upgrade_info file but crashes/killed before
creating mysql.ibd. In this case, innodb was initialized above in upgrade
mode. It would create mysql tablespace. Do nothing here, we will treat this
as upgrade.
*/
if (exists_mysql_tablespace) {
if (bootstrap::DDSE_dict_init(thd, DICT_INIT_CHECK_FILES,
d->get_target_dd_version())) {
LogErr(ERROR_LEVEL, ER_DD_SE_INIT_FAILED);
return true;
}
} else {
if (bootstrap::DDSE_dict_init(thd, DICT_INIT_UPGRADE_57_FILES,
d->get_target_dd_version())) {
LogErr(ERROR_LEVEL, ER_DD_UPGRADE_FAILED_INIT_DD_SE);
Upgrade_status().remove();
return true;
}
}即通过bootstrap::DDSE_dict_init去检查mysql.ibd文件.....
越整越复杂....
我们换个关键词搜. 不是还有个[Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0')
对应逻辑为:
代码语言：c 复制 /*
Reject restarting with a changed LCTN setting, since the collation
for LCTN-dependent columns is decided during server initialization.
*/
uint actual_lctn = 0;
exists = false;
if (dd::tables::DD_properties::instance().get(thd, "LCTN", &actual_lctn,
&exists) ||
!exists) {
LogErr(WARNING_LEVEL, ER_LCTN_NOT_FOUND, lower_case_table_names);
} else if (actual_lctn != lower_case_table_names) {
LogErr(ERROR_LEVEL, ER_LCTN_CHANGED, lower_case_table_names, actual_lctn);
return true;
}原来是这里强制判断. 也就是我们只要修改元数据信息里面的LCTN为1即可.
数据库层面分析
系统的数据字典是无法直接访问的, 得使用debug启动才能访问
代码语言：sql复制SET SESSION debug=' d,skip_dd_table_access_check';
SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';应该就是mysql.dd_properties表了.
只有1大字段....., 查询发现是16进制的. 我们使用py来查询. 并找出LSTN的位置
代码语言：python代码运行次数：0复制import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
)
cursor = conn.cursor()
cursor.execute("SET SESSION debug=' d,skip_dd_table_access_check';")
_ = cursor.fetchall()
cursor = conn.cursor()
cursor.execute('select * from mysql.dd_properties')
data = cursor.fetchall()
def find_xx_positions(s,x):
positions = []
xl = len(x)
start = 0
while True:
pos = s.find(x, start)
if pos == -1:
break
positions.append(pos)
start = pos xl
return positions
find_xx_positions(data[0][0].decode(),'LCTN=')
发现就只有这里有... 我们使用hexdump -C 却发现好几处...
也就是还有几张表也记录了这玩意...
但剩下几张表看起来都不像啊.... (还想着直接数据库层面修改呢...) 是时候祭出我们的python了.(上面不是已经使用了么...)
python分析
我们现在使用python来分析mysql.ibd文件
import struct
dd_dict = [
0x004e42c0,
0x005282c0,
0x0056c2c0,
0x005b02d0,
0x005f42d0,
]
filename = '/data/mysql_3306/mysqldata/mysql.ibd'
f = open(filename,'rb')
for x in dd_dict:
page_offset = int(x/16384)
_ = f.seek(page_offset*16384,0)
data = f.read(16384)
aa = struct.unpack('>4LQHQ',data[:34])
print(f'PAGE_TYPE:{aa[-2]}')
全是24(FIL_PAGE_TYPE_LOB_FIRST) 还好我们之前解析过这种PAGE的. 都不用改, 直接拿来用就是了.
代码语言：python代码运行次数：0复制import struct
def first_blob(f,pageno): # 这名字取得... 简单点吧
"""
input: f: file desc pageno FIL_PAGE_TYPE_LOB_FIRST NO
output: binarydata
"""
firstpagno = pageno
f.seek(pageno*16384,0)
data = f.read(16384)
entry = data[96:96 60]
rdata = b''
while True:
if len(entry) < 12:
break
pageno,datalen,lobversion = struct.unpack('>3L',entry[-12:])
datalen = datalen>>16
if pageno == 0 or pageno == 4294967295:
break
elif pageno == firstpagno:
rdata = data[696:696 datalen]
else:
f.seek(pageno*16384,0)
rdata = f.read(16384)[49:49 datalen]
#rdata = read_page(pageno)[39:39 datalen]
next_entry_pageno,next_entry_offset = struct.unpack('>LH',entry[6:12])
if next_entry_pageno >0 and next_entry_pageno < 4294967295:
f.seek(next_entry_pageno*16384,0)
entry = f.read(16384)[next_entry_offset:next_entry_offset 60]
else:
break
return rdata
import struct
dd_dict = [
0x004e42c0,
0x005282c0,
0x0056c2c0,
0x005b02d0,
0x005f42d0,
]
def find_xx_positions(s,x):
positions = []
xl = len(x)
start = 0
while True:
pos = s.find(x, start)
if pos == -1:
break
positions.append(pos)
start = pos xl
return positions
filename = '/data/mysql_3306/mysqldata/mysql.ibd'
f = open(filename,'rb')
for x in dd_dict:
data = first_blob(f,int(x/16384)).decode()
print('PAGENO:',int(x/16384),'OFFSET:',find_xx_positions(data,'LCTN'))
也就是这几个位置存在LCTN信息. 既然找到了确定位置, 那我们就可以直接修改值了. 修改完后记得做下crc32c校验. 好在我们之前也解析过. 于是我们整合整合就可以使用了.
修改lower_case_file_system
上面准备了那么多, 现在我们就可以修改lower_case_file_system啦. mysql层走不通, 我们直接修改mysql.ibd文件, 然后做下crc32的计算, 并写回数据库即可. (一定要先把大写的表给修改掉, 不然不知道发生什么, 或许什么也不会发生). 为了简单, 我这里就不写接口了. 信息直接写死.
代码语言：python代码运行次数：0复制import struct
import sys,os
def create_crc32c_table():
poly = 0x82f63b78
table = []
for i in range(256):
crc = i
for _ in range(8):
if crc & 1:
crc = (crc >> 1) ^ poly
else:
crc >>= 1
table.append(crc)
return table
def calculate_crc32c(data):
crc = 0xFFFFFFFF
for byte in data:
crc = crc32_slice_table[(crc ^ byte) & 0xFF] ^ (crc >> 8)
return crc ^ 0xFFFFFFFF
crc32_slice_table = create_crc32c_table()
filename = '/data/mysql_3306/mysqldata/mysql.ibd'
f = open(filename,'rb')
f2 = open('/tmp/t20240911_test_mysql.ibd','wb')
mpage = [313,330,347,364,381]
PAGENO = -1
while True:
data = f.read(16384)
PAGENO = 1
if data == b'':
break
lctn_offset = data.find(b'LCTN')
if lctn_offset < 1:
f2.write(data)
continue
data = data[:lctn_offset 5] b'1' data[lctn_offset 6:]
checksum_field1 = struct.unpack('>L',data[:4])[0]
checksum_field2 = struct.unpack('>L',data[-8:-4])[0]
c1 = calculate_crc32c(data[4:26])
c2 = calculate_crc32c(data[38:16384-8])
cb = struct.pack('>L',c1)
data = cb data[4:16384-8] cb data[16384-4:]
f2.write(data)
#print('PAGENO:',PAGENO,"CHECKSUM:",checksum_field1,checksum_field2,(c1^c2)&(2**32-1))
然后我们替换mysql.ibd,并修改参数, 再启动瞧瞧:
代码语言：shell复制mv /data/mysql_3306/mysqldata/mysql.ibd /tmp
mv /tmp/t20240911_test_mysql.ibd /data/mysql_3306/mysqldata/mysql.ibd
chown mysql:mysql /data/mysql_3306/mysqldata/mysql.ibd
vim /data/mysql_3306/conf/mysql_3306.cnf
启动失败, 页有问题....(说是381有问题, 就是我们刚才解析的最后1页. 那我们不要这一页呢.)
修改回去之后, 又报错不能修改了...
也就是说在某个字段里面还存在着校验值.(非PAGE的CHECKSUM). (看来是没法走捷径了...)
看来就只有后面研究源码或者表结构了才行.
实际生产环境建议老老实实重建实例. 这里仅为测试环境的演示
当我准备就此结束的时候, 我使用之前写的坏块检查脚本跑了下, 发现刚才那几页是坏块
也就是说逻辑可能没得问题, 而是我们页拼接的时候有问题. 仔细一看,发现是 c1和c2忘记做^了.....
不过也因此确认了应该是381页记录的LCTN才是有效的.
然后再次测试,启动数据库再次, 登录验证: 发现确实修改成功了
啊, 我真棒!
总结
之前学习的ibd文件校验,ibd文件结构等信息再次使用上了. 虽然不推荐生产环境这么干. 但这种方法也确实是可行的. 而且尽量使用交叉验证, 某种方法失败的时候不一定是逻辑有问题, 有可能是哪里的细节有问题.
附源码
前面验证的就不再单独列出来了. 就只列出最后的代码即可. 使用的时候, 注意修改为自己的文件哈, 记得备份!
代码语言：python代码运行次数：0复制import struct
import sys,os
def create_crc32c_table():
poly = 0x82f63b78
table = []
for i in range(256):
crc = i
for _ in range(8):
if crc & 1:
crc = (crc >> 1) ^ poly
else:
crc >>= 1
table.append(crc)
return table
def calculate_crc32c(data):
crc = 0xFFFFFFFF
for byte in data:
crc = crc32_slice_table[(crc ^ byte) & 0xFF] ^ (crc >> 8)
return crc ^ 0xFFFFFFFF
crc32_slice_table = create_crc32c_table()
filename = '/tmp/mysql.ibd'
f = open(filename,'rb')
f2 = open('/tmp/t20240911_test_mysql.ibd','wb')
mpage = [313,330,347,364,381]
PAGENO = -1
while True:
data = f.read(16384)
PAGENO = 1
if data == b'':
break
lctn_offset = data.find(b'LCTN')
if lctn_offset < 1 :#or PAGENO in [381]:
f2.write(data)
continue
print(PAGENO,lctn_offset)
data = data[:lctn_offset 5] b'1' data[lctn_offset 6:]
c1 = calculate_crc32c(data[4:26])
c2 = calculate_crc32c(data[38:16384-8])
cb = struct.pack('>L',(c1^c2)&(2**32-1))
data = cb data[4:16384-8] cb data[16384-4:]
f2.write(data)
#print('PAGENO:',PAGENO,"CHECKSUM:",checksum_field1,checksum_field2,(c1^c2)&(2**32-1))
f2.close()
f.close()
