[MYSQL] mysql 参数lower_case_table_names的修改

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;

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.

grep -r 'Data Dictionary initialization failed'

grep -r ER_DD_INIT_FAILED

  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);
    }
  }

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);
}

  /*
    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);

  /*
    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;
    }
  }

    /*
      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;
    }

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';

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=')

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]}')

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'))

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))

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

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()