Category Archives: MySQL

java.sql.SQLException: Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space

### Cause: java.sql.SQLException: Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space… (errno: 28 “No space left on device”)
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1021]; Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space… (errno: 28 “No space left on device”); nested exception is java.sql.SQLException: Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space… (errno: 28 “No space left on device”)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
at com.sun.proxy.$Proxy17.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:122)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:64)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
at com.sun.proxy.$Proxy43.getSumMoney(Unknown Source)
at com.sprucetec.fi.posc.manager.impl.PeManagerImpl.getSumMoney(PeManagerImpl.java:344)
at com.sprucetec.fi.posc.manager.impl.PeManagerImpl$$FastClassBySpringCGLIB$$3352c52a.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649)
at com.sprucetec.fi.posc.manager.impl.PeManagerImpl$$EnhancerBySpringCGLIB$$1d0d0a07.getSumMoney()
at com.sprucetec.fi.posc.service.ReturnGoodsServiceImpl.getPoscBalance(ReturnGoodsServiceImpl.java:231)
at com.sprucetec.fi.posc.service.ReturnGoodsServiceImpl.getSupplierBalanceInneer(ReturnGoodsServiceImpl.java:197)
at com.sprucetec.fi.posc.service.ReturnGoodsServiceImpl.getSupplierBalance(ReturnGoodsServiceImpl.java:174)
at com.sprucetec.fi.posc.service.ReturnGoodsServiceImpl.getSupplierBlance(ReturnGoodsServiceImpl.java:108)
at com.alibaba.dubbo.common.bytecode.Wrapper30.invokeMethod(Wrapper30.java)
at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)
at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)
at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)
at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.sprucetec.hawkeye.sdk.metric.dubbo.ServiceMetricFilter.invoke(ServiceMetricFilter.java:46)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.sprucetec.hawkeye.sdk.trace.dubbo.ProviderTraceFilter.invoke(ProviderTraceFilter.java:61)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)
at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:113)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170)
at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)
at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space… (errno: 28 “No space left on device”)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:62)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:78)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:303)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:154)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:102)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)
at sun.reflect.GeneratedMethodAccessor282.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
… 45 more

 

Causes the temporary table space to increase mainly used in the following cases.
1, order by or group by (sorting takes the major part).
2, the creation and reconstruction of indexes.
3、distinct operation.
4, union & intersect & minus sort-merge joins.
5、Analyze operations.
6, some exceptions can also cause a spike in TEMP.
Solutions.
1, increase the temporary tablespace
2, targeted problem solving

Python calls the MySQLdb library, and an error “Library not loaded: libmysqlclient.18.dylib” is reported

Leave a reply

Error message:

“…Library not loaded: libmysqlclient.18.dylib

Referenced from: …ython2.7/site-packages/_mysql.so
  Reason: image not found”

the reason:

After upgrading MySQL, libmysqlclient.18.dylib becomes libmysqlclient.20.dylib.

Solution:

Find mysql lib under the installation path of libmysqlclient.20.dylibestablishing a soft connection.

sudo ln -s /usr/local/mysql/lib/libmysqlclient.20.dylib /usr/local/mysql/lib/libmysqlclient.18.dylib

How to Solve MYSQL Error: No module named MySQLdb

Leave a reply

How to Solve MYSQL Error: No module named MySQLdb?

Solution

easy_install mysql-python (mix os)
pip install mysql-python (mix os/ python 2)
pip install mysqlclient (mix os/ python 3)
apt-get install python-mysqldb (Linux Ubuntu, ...)
cd /usr/ports/databases/py-MySQLdb && make install clean (FreeBSD)
yum install MySQL-python (Linux Fedora, CentOS ...)

Caused by: com.ibatis.common.xml.NodeletException: Error parsing XML. Cause: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 281; The content of element type “sqlMapConfig” is incomplete

Leave a reply

It can be clearly seen from the error message that this is a problem on sql

Well, in general, there are many reasons for this problem:

1. Maybe the code call method in your service is wrong (this is the lowest level error, and it is not easy to find…), directly above

Originally it was written as MMIC01.delete, which was my careless (no flash, haha)

 

2. It is also possible that one of your SQL statements is wrong (this is easier to find)

 

Compared with the previous question, this error is already executing the SQL statement, so you can put the executed SQL statement in the tool and run it, and you will know where the problem is.

[MySQL] mysql 5.5 and 5.6 timestamp default default value CURRENT_TIMESTAMP problem

Leave a reply

The behavior of TIMESTAMP in MySQL 5.5:

1. The implicit default value of the first TIMESTAMP NOT NULL field without a default value: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. The implicit default value of the TIMESTAMP NOT NULL field with no default value set later: 0000-00-00 00:00:00

3. The table creation statement that does not support multiple CURRENT_TIMESTAMP default values ​​of 5.5 is similar to this:

CREATE TABLE `audit_log` (
  `id` int ( 10 ) unsigned NOT NULL AUTO_INCREMENT,
  `ent_id` int ( 10 ) unsigned NOT NULL DEFAULT ' 0 ' ,
  `rule_id` int ( 10 ) unsigned NOT NULL DEFAULT ' 0 ' ,
  rules_detail` VARCHAR `( 2048 ) the NOT NULL the DEFAULT '' the COMMENT ' rule details ' ,
  sender_email` VARCHAR `( 512 ) the NOT NULL the DEFAULT '' the COMMENT ' Sender mail review ' ,
  `receiver_email` varchar( 512 ) NOT NULL DEFAULT '' COMMENT'recipient 's mailbox ' ,
  subject` VARCHAR `( 512 ) the NOT NULL the DEFAULT '' the COMMENT ' theme ' ,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `start_time` timestamp NOT NULL DEFAULT ' 0000-00-00 00:00:00 ' ,
  `end_time` timestamp NOT NULL DEFAULT ' 0000-00-00 00:00:00 ' ,
  status` tinyint `( 3 ) the NOT NULL the DEFAULT unsigned ' 1 ' the COMMENT ' current state (by 1, 2 rejected rejected timeout 3, 4 by timeout) ' ,
  reviewer_leader` VARCHAR `( 512 ) the NOT NULL the DEFAULT '' the COMMENT ' moderator ' ,
  PRIMARY KEY (`id`),
  KEY `idx_ent_id` (`ent_id`)
) ENGINE =InnoDB DEFAULT CHARSET=utf8 COMMENT = ' Audit log table '

The behavior of TIMESTAMP in MySQL 5.6:

Supports multiple CURRENT_TIMESTAMP default values, but does not support setting the default value to 0000-00-00 00:00:00

5.6 can be like this:

  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

[MySQL] Note that MySQL5.1 does not support utf8mb4 Error 1115: Unknown character set:’utf8mb4′

Leave a reply

MYSQL5.5 supports utf8mb4, and MYSQL5.1 does not support “utf8mb4”.

When connecting to the database, if utf8mb4 is specified, the error code that will appear is:

Error 1115: Unknown character set:’utf8mb4′

There will be utf8mb4 incompatibility.

 

So if you want to use utf8mb4 to store emoji characters, please upgrade as soon as possible

[MySQL] The principle of group by aggregation function and the reason for aggregation limitation SELECT list is not in GROUP BY clause and contains nonaggregated column

Leave a reply

The principle of group by is 

First divide the data into groups according to the grouping field,

Then perform specific aggregation operations in each group

 

The limitations of ONLY_FULL_GROUP_BY mode are:

SELECT list is not in GROUP BY clause and contains nonaggregated column

In the columns of the select query, there are fields that are not in the group by clause, and there are fields that are not aggregate functions

 

The reason is that:

After group by, all fields and attributes are the attributes of this group

Among the attributes of a group, only if the maximum/minimum/average/total is known, this aggregated attribute is meaningful

The attributes of each individual group member are meaningless 

That’s why this restriction appears

Zabbix import MySQL database error ERROR 1046 (3D000) at line 1: No database selected

Leave a reply

An error is reported when importing the Zabbix database using the following command

Solution:

Edit the database file to be imported

vim /usr/share/doc/zabbix-server-mysql-4.0.7/create.sql.gz

# Add in the first line

use zabbix;

As shown below:

Save and exit!

Then re-import the MySQL database. Note: The import process may take a few seconds.

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -u zabbix -p

MYSQL reported Fatal error encountered during command execution solution

Leave a reply

Add to the connection string

Allow User Variables=True

solve.

Otherwise, errors will be reported from time to time, the stored procedure name will be longer, and the error will be reported, and sometimes it will not be reported. If the parameter is passed 1 digit, it will be normal and 2 digits will be reported as an error…

Database mirroring connection error 4’An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)

Leave a reply

A SQL Server mirroring of the company failed over (active/standby switchover). Check the reason for the main/standby switchover of SQL Server mirroring. The following error was found in the error log:

clip_image001

Date        2019/8/31 14:09:17
 
Log     SQL Server (Archive #4 - 2019/9/1 0:00:00)
 
 
 
Source      spid35s
 
 
 
Message
 
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://xxxxx:7022'. .

Check the system log of the witness server and find the following error message, which indicates that the witness server cannot get a response from the database after more than 10 seconds. Please check the service or network connections. (Check the service and network connections.)

clip_image002

At this point in the analysis, I can only guess that the SQL Server service abnormality or network abnormality caused the witness server to be unable to access the main server for more than 10 seconds, which caused the main/standby switch. When I asked the system administrator to assist in the analysis, the system administrator confirmed Saturday 14 Click on the main server that is using Veeam Backup & Replication to back up the mirror during that time period (he is testing the use of Veeam Backup & Replication to back up the VmWare virtual machine), but will this Veeam Backup & Replication backup cause network interruption? So, on Monday, we found a time to test whether Veeam Backup & Replication backup really caused network interruption. During the test, it was found that it really caused network interruption (it is not clear whether it is a setting problem or other mechanism reasons. ), the test process has indeed verified that the Veeam Backup & Replication backup caused the network interruption, which caused the master-slave switch of the mirroring, hereby record this case!

Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 – Connection is not available, request timed out after 30005ms.

Leave a reply

An online order history data field refresh operation, 3 tables with more than 1 million data. Since the synchronization update is too slow for about 20 minutes or more, the different method is adopted.

The local library refresh is okay, but the following exception occurs when the online library is reached:

### Error updating database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
    at sun.reflect.GeneratedMethodAccessor380.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 24 more
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
    at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
    at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
    at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:338)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at sun.reflect.GeneratedMethodAccessor381.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy521.update(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    ... 28 more
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:669)
    at com.zaxxer.hikari.pool.HikariPool.getConnection (HikariPool.java: 183 )
    at com.zaxxer.hikari.pool.HikariPool.getConnection (HikariPool.java: 148 )
    at com.zaxxer.hikari.HikariDataSource.getConnection (HikariDataSource.java: 128 )
    at com.zaxxer.hikari.HikariDataSource$$FastClassBySpringCGLIB$$eeb1ae86.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$f68c05a.getConnection(<generated>)
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)

Two, the solution

Through the exception, it can be found that the database connection cannot be obtained. It is guessed that the number of connections is insufficient. Therefore, modify the HikariPool connection pool configuration to solve the problem.

before fixing:

spring:
  datasource:
    hikari:
      connection-test-query: SELECT 1 FROM DUAL
      connection-timeout: 30000
      maximum-pool-size: 20
      max-lifetime: 1800000
      minimum-idle: 5
      connection-init-sql: SET NAMES utf8mb4

After modification:

spring:
  datasource:
    hikari:
      connection-test-query: SELECT 1 FROM DUAL
      connection-timeout: 600000
      maximum-pool-size: 500
      max-lifetime: 1800000
      minimum-idle: 20
      validation-timeout: 3000
      idle-timeout: 60000
      connection-init-sql: SET NAMES utf8mb4