1. InnoDB Stop & MYISAM Start, remove InnoDB redo / tablespace file vi /etc/my.cnf.d/server.cnf ----------------------- # InnoDB disabled default_storage_engine=MYISAM #default_storage_engine = InnoDB # remove InnoDB redo #innodb_log_buffer_size = 4G #innodb_log_file_size = 4G # remove InnoDB tablespace #innodb_data_file_path = ibdata1:12582912;ibdata2:100G;ibdata3:50G:autoextend ----------------------- systemctl restart […]
ERROR 1118 (42000) at line 25: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. vi /etc/my.cnf.d/server.cnf innodb_strict_mode = OFF or > SET GLOBAL innodb_default_row_format='dynamic'; > SET SESSION innodb_strict_mode=OFF; > ALTER TABLE `tablename` ENGINE=InnoDB […]
mariadb : dblink Hint
-- 1. create database for dblink create database IF NOT EXISTS dblink1; -- 2. check dblink for FEDERATED plugin support. show engines; -- FEDERATED -- install plugin federated soname 'ha_federated.so'; -- 3. create server for dblink drop server IF EXISTS dblink1; CREATE SERVER IF NOT EXISTS dblink1 FOREIGN DATA WRAPPER mysql OPTIONS (USER […]
mariadb : sql_mode Hint
SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE; SET @old_sql_mode = @@global.sql_mode; SET sql_mode = ''; SET SQL_MODE = CONCAT(@@SQL_MODE, ',EMPTY_STRING_IS_NULL'); SET SQL_MODE = REPLACE(@@SQL_MODE, 'EMPTY_STRING_IS_NULL', ''); SET sql_mode = @old_sql_mode; /* SET sql_mode = 'ORACLE, EMPTY_STRING_IS_NULL'; PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT */
mariadb : sys_exec : trigger call shell command Hint
# yum install mariadb-devel # cd # wget https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip # mv master.zip lib_mysqludf_sys-master.zip # unzip lib_mysqludf_sys-master.zip # mv lib_mysqludf_sys-master /usr/include/mysql/ # cd /usr/include/mysql/lib_mysqludf_sys-master/ # gcc -m64 -fPIC -Wall -I/usr/include/mysql/server -I. -I/usr/include/mysql/server/private -shared lib_mysqludf_sys.c -o /usr/lib64/mariadb/plugin/lib_mysqludf_sys.so # ll /usr/lib64/mariadb/plugin/lib_mysqludf_sys.so cat /usr/include/mysql/lib_mysqludf_sys-master/lib_mysqludf_sys.sql DROP FUNCTION IF EXISTS lib_mysqludf_sys_info; DROP FUNCTION IF EXISTS sys_get; DROP FUNCTION IF […]
MySQL : Change Table Charset Hint
SELECT CCSA.character_set_name FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "test_db" AND T.table_name = "test_tb"; ALTER TABLE test_tb CONVERT TO CHARACTER SET utf8;
MySQL Join 관련 잘 정리한 정보
출처 : Rapapa Dev Story