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 […]
oracle 11g : sga memory setup Hint
SQL> show sga ; Total System Global Area 6547296256 bytes Fixed Size 2213856 bytes Variable Size 4429187104 bytes Database Buffers 2013265920 bytes Redo Buffers 102629376 bytes SQL> show PARAMETERs memory ; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 memory_max_target big integer 6272M […]
oracle : dblink limit, open_links Hint
SELECT name, VALUE FROM v$parameter WHERE name = 'open_links'; ALTER SYSTEM SET open_links = 50 SCOPE = spfile; COMMIT; systemctl restart oracle.service
Toad for oracle over ssh with putty Hint
Download & Install putty https://www.putty.org/ Putty Configuration Connection > SSH > Tunnels Check [Local ports ....] Check [Remote ports ....] Source Port : 15219 Destination : yourdb.server.com:1521 Click [Add] Do not click Open button, You must save this configure... Session : Input your Host, ssh Port, Saved Session name, and Click [Save] […]
KeepAlive : ssh, oracle Hint
# vi /etc/ssh/sshd_config KeepAlive yes ClientAliveInterval 60 # vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora SQLNET.EXPIRE_TIME=1
oracle : use dblink by command line
must be added tnsnames [DB Links] object not enough ~ # vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora REMOTETNS1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE))) # su - oracle -c "expdp dbuser/xxxxxx@REMOTETNS1 dumpfile=file.dmp logfile= file.log directory=BACKUP_DIR reuse_dumpfiles=y"