ERROR: The total number of locks exceeds the lock table size Error Code: 1206

ERROR: The total number of locks exceeds the lock table size Error Code: 1206

Locate the config file of mysql in Ubuntu 18.04

/etc/mysql/mysql.conf.d/mysqld.cnf

Add the following line to the end of the file

innodb_buffer_pool_size        = 1G

Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×

How to change string to DATETIME in SQL?

SET `time_str` = STR_TO_DATE(`time_str`,'%d/%m/%Y %H:%i')

What does the numbers in the SQL clause “GROUP BY 1” mean?

SELECT account_id, open_emp_id
         ^^^^        ^^^^
          1           2

FROM account
GROUP BY 1;

GROUP BY 1 refers to the first column in select statement which is account_id.

MySQL - innodb_buffer_pool_size

테이블이 엄청나게 커지면서 실험에 bottleneck이 되었는데 검색하다보니 innodb_buffer_pool_size 요놈 때문인 거 같다. index를 만들었는데도 너무 느렸는데 확인해보니 innodb_buffer_pool_size가 index데이터를 cache하는 설정이다... 서버에 메모리는 빵빵하니 크게 설정해놓으면 놓을 수록 살짝 메모리DB처럼 되간다고 보면 된다고 하네...

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB. 

MySQL - Index Cardinality

Index를 만들고 나서도 query가 너무 느려서 좀더 검색해밨는데 Cardinality때문이었다. Cardinality가 Index를 만드는데 아주 중요한 역할을 한다.

예를 들면Gender이라는 column에 값이 male과 female이 있다고 할 때 남자 50% 여자 50%의 비례로 분포되었다고 하면, cardinality가 2가 된다. 그 말인 즉, where gender = "male"로 검색했을때 100%의 row들을 검색하던 것이 50%가 된다는 뜻이다. 다시 말해서 그래도 엄청난 양의 row들을 scan해야 된다는 뜻....

varchar 유형의 column을 255btye로 끊여서 index를 만들었었는데 후에 들어오는 row의 값들이 길이가 늘어나면서 cardinality가 엄청나게 작은 수치로 되어서 얘들에 대해서는 검색이 엄청 늘어난 것이었다...


원문: https://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/

Spring JDBC DBCP to get rid of noroute, last packet was sent 1ms ago exceptions

When using Spring JDBC for connection, it creates one connection for each call and it will end up with running out listening ports since it will take 60 seconds to release the connection (until then, it is TIME_WAIT: it can be checked using command: (netstat -nat | grep TIME_WAIT | wc -l) ).

To get rid of it, I found that using DCBP BasicDataSource instead of spring datasource with pooling.

Download : DCBP jar from https://commons.apache.org/

MySQL batch insert speed

About MySQL insert speed: http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

• Connecting: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting row: (1 × size of row)
• Inserting indexes: (1 × number of indexes)
• Closing: (1)

In addition, rewriteBatchedStatements=true is the important parameter. rewriteBatchedStatements=true improves the performance so dramatically by rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() (Source). That means that instead of sending the following nINSERT statements to the mysql server each time executeBatch() is called :

INSERT INTO X VALUES (A1,B1,C1)
INSERT INTO X VALUES (A2,B2,C2)
...
INSERT INTO X VALUES (An,Bn,Cn)

It would send a single INSERT statement :

INSERT INTO X VALUES (A1,B1,C1),(A2,B2,C2),...,(An,Bn,Cn)

Remove duplicates rows in MySQL

Remove duplicate entries in MySQL:

 ALTER IGNORE TABLE RESIM.LOD_RANDOMRECORDSIM  
 ADD UNIQUE INDEX USERIDARTISTURI (USERID, ARTISTURI);  

If it is not working with InnoDB, execute the query below first:

 SET SESSION old_alter_table=1;  

Always backup before doing such kind of tasks...

SQL 자주 쓰는 쿼리

• Table RandomSample20ForEachPR에서 TUIMSimPrecision이라는 Column 삭제 

 ALTER TABLE aboutme.RandomSample20ForEachPR DROP COLUMN TUIMSimPrecision  

• Table RandomSample의 TopicCount라는 Column을 TestFlag로 이름 변경

 ALTER TABLE aboutme.RandomSample CHANGE TopicCount TestFlag INT  

• Table RandomSample20ForEach의 MUIMSim이라는 Column의 데이터속성 변경 

 ALTER TABLE aboutme.RandomSample20ForEach MODIFY COLUMN MUIMSim decimal(12,10)  

• SUM() and LIMIT  같이 쓸때

 SELECT SUM(SampleFlag) FROM   
      (SELECT SampleFlag FROM aboutme.RandomSample20ForEach WHERE TwitterURI = 'http://www.twitter.com/parklize' ORDER BY TUIMSim DESC LIMIT 10)   
      AS SuccessRate  

• INSERT INTO... SELECT FROM

 INSERT INTO table2  
 (column_name(s))  
 SELECT column_name(s)  
 FROM table1;  

• Subquery  limit 문구넣고 실행

 INSERT INTO aboutme.RandomSample50 (TwitterURI, LINK, TopicCount, CreateTime, TestFlag)   
 SELECT TwitterURI, LINK, TopicCount, CreateTime, 1 FROM aboutme.RandomSample  
 WHERE PositiveTestCases = '1' AND TwitterURI IN  
 (SELECT * FROM (SELECT DISTINCT TwitterURI FROM aboutme.RandomSample ORDER BY RAND() LIMIT 50) temp);  

• Substring

SELECT SUBSTRING_INDEX(UGCID2, '/', -1) FROM UGCListFor150URL  

http://www.twitter.com/parklize/status/562289797117800448->562289797117800448

• MAMP 에서 MySQL접속 및 sql 파일 실행하기

 
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot  

MySql접속후 아래 명령으로 .sql파일 실행시킨다.

 mysql> source file_name  
 mysql> \. file_name  

• 현재 connection수 확인하고 kill하기

 SHOW STATUS WHERE `variable_name` = 'Threads_connected';  
 SHOW PROCESSLIST;  
 KILL 9690; // use id to kill the process  

• Get all "KILL" command for ending all processes over 200 secs.

 select concat('KILL ',id,';') from information_schema.processlist  
 where user='root' and time > 200;  

• update from select

 update RandomSampleUIM uim, (select distinct Topic, BabelID from _RandomSampleUIM) olduim set uim.`BabelID` = olduim.`BabelID` where uim.`Topic` = olduim.`Topic`;  

• my.conf 설정 (/etc/mysql/my.conf)
• max_connections: 연결가능한 connections 수를 설정
• Ubuntu MySQL start/stop

• /etc/init.d/mysql start/stop/restart

MySQL - max_allowed_packet in MAMP

Copy "/Applications/MAMP/Library/support-files/my-medium.cnf" file to "/Applications/MAMP/Library/my.cnf" and change the value "max_allowed_packet = 1M" to "max_allowed_packet = 16M"

MySQL - Does not support LIMIT in IN/ALL/ANY/SOME subquery: Error Code 1235

           UPDATE aboutme.RandomSample SET TestFlag = 1 WHERE UGCID in  
           (SELECT UGCID FROM   
                (SELECT UGCID FROM aboutme.RandomSample WHERE TwitterURI = @twitter_uri ORDER BY RAND() LIMIT 10) tmp  
           );  

java.sql.SQLException: Incorrect string value:, UTF-8 to utf8mb4 MySQL

Alter DB and Table to utf8mb4

 
ALTER DATABASE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;  
ALTER TABLE UGCList CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  

Check variables

 
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';  

For MySQL configuration, add two lines for utf8mb4

 [mysqld] 
 character-set-server = utf8mb4  
 collation-server = utf8mb4_unicode_ci  

In your program call SQL, use SET NAMES before update or insert

 String set_names = "SET NAMES 'utf8mb4'";  
 ps = con.prepareStatement(set_names);  
 ps.executeUpdate();  

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'utf8mb4' tells the server, “future incoming messages from this client are in character set utf8mb4.” It also specifies the character set that the server should use for sending results back to the client.

UTF-8 and utf8mb4

There are known issues storing 4byte utf characters in some versions of MySQL. Apparently you must use utf8mb4 to represent 4 byte UTF characters, as the normal utf8 character set can only represent characters up to 3 bytes in length and so can't store character which are outside of the



Restart MySQL

sudo /etc/init.d/mysql restart

Java Date to SQL DateTime with timestamp

Date class is java.util.Date and while it is formatted as String (currentTime below), it can be inserted into DB field set as DATETIME.

                     // Change to string store in MySQL (DATETIME)  
                     Date dt = ugc.getCreate_time();  
                     java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
                     String currentTime = sdf.format(dt);  

Inserted record format: 2013-11-04 14:09:59

MySQL INT, BIGINT, TINYINT의 차이 및 INT(10), BIGINT(10), TINYINT(10)의 차이

INT(10)이 10자리수의 Integer을 저장한다고 생각했는데 자세히 찾아보니 그런거 아니다.

일단은 TINYINT, INT, BIGINT는 항상 아래의 최대치 (혹은 범위)를 가진다.

• TINYINT: 127
• INT: 2147483647
• BIGINT: 9223372036854775807

그럼 INT(10)의 10은 무슨 작용을 하는 존재일까?

ZEROFILL 속성과 같이 쓰지 않는 경우라면 아무런 작용을 하지 않는다.  해당 속성을 같이 사용할 때 숫자 10은 모자라는 부분을 0으로 채워서 10자리로 데이터베이스 에서 나타낸다고 보면 된다.

• 예). ZEROFILL속성을 사용하는 INT(5) 로 설정된 Column에 99를 넣었다고 하면, 데이터 베이스에 00099로 나타나게끔 하는 작용을 한다.

원문 보기: http://waynewhitty.ie/blog-post.php?id=19