Tag Archives: mysql

MySQL reports an error lock wait timeout exceeded; Try restarting transaction problem solving method

Spring transaction nesting causes deadlock

In the case of high concurrency, spring transactions cause database deadlock, and subsequent operations timeout and throw exceptions.  
MySQL database adopts InnoDB mode. The default parameter is InnoDB_ lock_ wait_ Timeout set the lock waiting time to 50s. Once the database lock exceeds this time, an error will be reported.

This MySQL deadlock is mainly caused by adding, modifying or deleting transactions (adding @ transactional annotation or configuring the configuration file), and then catching the exception, resulting in the transaction not receiving the exception, waiting for the exception, not ending, and will not trigger the rollback operation, so the table is locked.

Throw the exception in the catch so that the transaction can trigger rollback and release the lock resource of the table: throw new runtimeException (E);

 

Or, the current call transaction method is set to Propagation.SUPPORTS:  @ Transactional(propagation=Propagation.SUPPORTS)

 

  perhaps   Use the following statement to find the data submitted for the transaction and kill the thread.

select * from information_ schema.innodb_ trx

kill 20057;

 

MySQL master-slave synchronization error 1507

After MySQL manually deleted partons from the database, the main database was not modified. Later, problems occurred after deleting partons from the main database

Fault site

Last_Errno: 1507
                   Last_Error: Error 'Error in list of partitions to DROP' on query. Default database: ''. Query: 'ALTER TABLE DROP PARTITION part_20170209'

 

 

Solution:

Check the partition to be deleted according to the error reported from the database to the corresponding location. It is found that there is no partition. The problem is clear here. The rest is simple repair operations

mysql> stop slave ; Query OK, 0 rows affected (0.00 sec)  mysql> set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec)  mysql> start slave; Query OK, 0 rows affected (0.01 sec)

Repeat until the problem is fixed. If there are many same errors, you can also write a script to continuously monitor the MySQL slave status. If the same errors are matched, you can skip. Let’s take a look at the situation after repair

Seconds_Behind_Master: 1265 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:

I felt it was ok, there was a little delay, but I found it in time

 

 

Summary:

1. In case of such a problem, first of all, it is certain that the drop partition operation cannot be performed manually with a large permission account, resulting in no error found in the partition when the master database is executed

      To control such problems, we can start with permissions and strictly control user permissions. Non DBA personnel are not allowed to have operation permissions directly (our permissions are very strict)

Solution to error reporting in starting MySQL

　　

After MySQL is installed, start:/etc/init.d/mysql start, but an error is reported: starting MySQL.. manager of PID file quit without updating f). The following is my summary of the solutions to error reporting. I have installed MySQL in many environments and will report various errors for various reasons. Now make a summary for future reference

1、 View error log

The error report log is the direction light for us to solve the problem. Without the direction light, we have no direction. I once reported an error during installation. There are such errors in the log:
/usr/local/MySQL/bin/mysqld: error while loading shared libraries: libstdc + +. So. 5: cannot open shared object file: no such file or directory. The library file libstdc + +. So. 5 is obviously missing. At this time, we can search the package related to libstdc and install it

2、 Maybe SELinux caused it

If it is CentOS system, SELinux will be enabled by default. At this time, you can close it first, open/etc/SELinux/config, change SELinux = enabling to SELinux = disabled, save the disk, exit and try restarting the machine

3、 Residual data

It is also possible that MySQL is installed on the machine for the second time. There is residual data that affects the startup of the service. Go to the MySQL data directory/data to have a look. If mysql-bin.index exists, delete it as soon as possible. It is the culprit

4、 Determine the permissions of the data directory

The data directory is generally & lt; prefix>/ Data, check its ownership. If you are not the MySQL owner, MySQL users and groups will be created when installing mysql. At this time, you need to modify the users and groups:

chown -R mysql:mysql /var/data

5、 No data directory specified

The/etc/my.cnf configuration file will be used when MySQL does not specify a configuration file at startup. Please open this file to see if a data directory (dataDir) is specified under the [mysqld] section. If not, please set this line under [mysqld]:

datadir =/usr/local/mysql/data

6、 And the MySQL process is running

If this is the second time MySQL is installed on the machine, it is very likely that this will happen. At this time, it is very likely that there are MySQL processes running on the system. You can execute the following command to view:

ps -ef | grep mysql

If the result is more than one line, kill it quickly

7、 The skip federated field is causing trouble

You can check whether there is a skip federated field in the/etc/my.cnf file that has not been commented out. If so, comment it out immediately

8、 Error log directory does not exist

Maybe you still don’t know where the MySQL error log is?At this time, you can execute the command to view:

cd mysql/bin

./mysql_safe

At this time, an error will be reported, and the error will be displayed in the error. Write the error in the XX file in XX directory. Here, check whether this directory and file exist. If not, solve it according to the log instructions. If not, create a directory and modify the permissions and attribution. It is usually/var/lib/MySQL/mysql.error. Note that mysql.error does not need to be created

cd /var/lib

mkdir mysql

chmod 777 mysql

chown mysql:mysql mysql

Ubuntu 16.04 troubleshooting MySQL error [How to Solve]

problem description

Ubuntu 16.04 errors are reported when installing the MySQL server and MySQL client. In order to make the description as simple as possible, take MySQL client as an example:

$ sudo apt install mysql-client

## ubuntu 16.04 It is recommended to use apt for all operations


## Omit a bunch of prompts and go straight to the error message

...
Unselected package mysql-client is being selected.
(The database is being read ... The system currently has 277491 files and directories installed.)
Preparing to unpack ... /mysql-client_5.7.15-0ubuntu0.16.04.1_all.deb ...
Unpacking mysql-client (5.7.15-0ubuntu0.16.04.1) ...
Setting up mysql-common (5.7.15-0ubuntu0.16.04.1) ...
update-alternatives: error: candidate path /etc/mysql/my.cnf.fallback does not exist
dpkg: Error processing package mysql-common (--configure).
 Subprocess installed post-installation script returned error status 2
dpkg: Dependency issues prevented the configuration of libmysqlclient20:amd64 from continuing.
 libmysqlclient20:amd64 depends on mysql-common (>= 5.5); however.
  Package mysql-common has not been configured.

dpkg: Error processing package libmysqlclient20:amd64 (--configure).
 Dependency issue - still not configured
dpkg: Dependency problem preventing configuration of libmysqlclient-dev from continuing.
 libmysqlclient-dev depends on libmysqlclient20 (= 5.7.15-0ubuntu0.16.04.1); however.
  Package libmysqlclient20:amd64 is not yet configured.

dpkg: Error processing package libmysqlclient-dev (--configure).
 Dependency issue - still not configured
dpkg: Dependency issue preventing mysql-client-5.7 configuration from continuing.
 mysql-client-5.7 depends on mysql-common (>= 5.5); however.
  Package mysql-common has not been configured.

dpkg: Error processing package mysql-client-5.7 (--configure).
 Dependency issue - still not configured
dpkg: Dependency issue preventing mysql-client configuration from continuing.
 mysql-client � The apport report was not written because the error message indicated that this was an error due to a previous issue.
                                                                                   No apport report was written because the error message indicated that this was an error due to a previous issue.
                                              The apport report was not written because the MaxReports limit was reached.
                                                                                                  The apport report was not written because the MaxReports limit was reached.
                                              Relies on mysql-client-5.7; however.
  Package mysql-client-5.7 has not been configured.

dpkg: Error processing package mysql-client (--configure).
 Dependency issue - still not configured
An error occurred while processing.
 mysql-common
 libmysqlclient20:amd64
 libmysqlclient-dev
 mysql-client-5.7
 mysql-client
E: Sub-process /usr/bin/dpkg returned an error code (1)

Error reporting focuses on:

update-alternatives: Error: candidate path /etc/mysql/my.cnf.fallback does not exist
dpkg: Error while processing package mysql-common (--configure).

Error information in English version:

update-alternatives: error: alternative path /etc/mysql/my.cnf.fallback doesn't exist dpkg: error processing package mysql-common (--configure):

solution:

mkdir MYSQL
cd MYSQL
sudo apt download mysql-common
ar xvf mysql*
tar xvf dat*
sudo cp ./etc/mysql/my.cnf.fallback /etc/mysql/my.cnf.fallback
sudo dpkg -i mysql*deb 
cd
sudo apt -f install

Note:

1. If there is no/etc/mysqlThis file is truncated to create yourself

2.If version < Ubuntu

 

 

Solve the problem of MySQL error reporting and lack of libaio. So. 1

MySQLÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐÐ

Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison: Russian Chinese comparison

64 bit

wget http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm

rpm-ivh libaio-0.3.107-10.el6.x86 64.rpm

Action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action, action

yum install libaio.so.1

MySQL reports an error using load data local infile

An error occurs when trying to execute the following statement in MySQL 8.0 on Windows system

mysql> LOAD DATA LOCAL INFILE '/path/filename' INTO TABLE tablename;

 

ERROR   one thousand one hundred and forty-eight  ( 42000): The used command is not allowed with this   MySQL version

The instruction used is not allowed in the current MySQL version

But in fact, MySQL 5.1 and above support this command. This error is reported because it is not allowed to load data locally when the system variable local infile = 0 in MySQL. Find the configuration file my.ini in the installation directory, modify the local infile = 1, or add the — local infile option when starting mysql

mysql -uroot –local-infile=1 -p

password:******

 

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

MySQL service is running with — secure file priv option, so this statement cannot be executed

The reason for the error is that after MySQL version 5.7.6, the imported file can only be imported in secure_file_Under the folder specified by priv (due to insufficient permissions), we can use show variables like ‘% secure%’; The command displays the file directory, places the files to be imported in the folder specified by the secure file priv parameter, and then imports them from here

 

ERROR 2 (HY000): File not found (OS errno 2 – No such file or directory)

File not found

When providing file paths under Windows system, you need to change all “\ to” \ \ “, such as C: \ \ program files \ \ MySQL \ \ MySQL server 8.0 \ \ file

Does MySQL report an error “MySQL: unknown option” for all parameters

error:

[root@XXXX tmp]# mysql -uroot -p
mysql: unknown option ‘–You have new mail in /var/spool/mail/root’
[root@XXXX tmp]#
[root@XXXX tmp]# mysql –?
mysql: unknown option ‘–You have new mail in /var/spool/mail/root’
[root@XXXX tmp]#

———————

It was only later discovered that there was a problem with the my.cnf provided in the package. There was an extra line at the end of the file.

 

[Solved] Error when connecting to MySQL when creating Django project

After changing the data configuration in the setting file of the project:

DATABASES = {

    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'cesi_DB',    #Your database name
        'USER': 'sheng', # your database username
        'PASSWORD': 'xxoo', # your database password
        'HOST': '10.0.0.11', # your database host, leave blank to default to localhost
        'PORT': '3306', # your database port
    }
}

Error Message:

    'Did you install mysqlclient or MySQL-python?' % e
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named 'MySQLdb'.
Did you install mysqlclient or MySQL-python?

  Solution:

The engine that needs to change the database:

MYsqlDB ==》pymysql

In application (app01)__init__Add to the document:

import pymysql

pymysql.install_as_MySQLdb()

 

.NET core 2.0 preview uses Mysql to report errors [Solved]

Shotscreen

Development environment:

vs2017 preview 15.3+MySql.Data.EntityFrameworkCore 7.0.7-m61

Error reporting:

Happened System.TypeLoadException
HResult=0x80131522
Message=Method ‘Clone’ in type ‘MySQL.Data.EntityFrameworkCore.Infraestructure.Internal.MySQLOptionsExtension’ from assembly ‘MySql.Data.EntityFrameworkCore, Version=7.0.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d’ does not have an implementation.
Source=<Unable to calculate the source of the anomaly>
StackTrace:
at MySQL.Data.EntityFrameworkCore.Extensions.MySQLDbContextOptionsExtensions.UseMySQL(DbContextOptionsBuilder optionsBuilder, String connectionString, Action`1 MySQLOptionsAction)
at testproj.MemberPoint.Startup.<ConfigureServices>b__4_0(DbContextOptionsBuilder options) in D:\testproj\testprojSVN\CommonFile\trunk\NetCore\Business\testproj.MemberPoint\Startup.cs:line 28
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass0_0`1.<AddDbContext>b__0(IServiceProvider p, DbContextOptionsBuilder b)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.DbContextOptionsFactory[TContext](IServiceProvider applicationServiceProvider, Action`2 optionsAction)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass5_0`1.<AddCoreServices>b__0(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactoryService(FactoryService factoryService, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitSingleton(SingletonCallSite singletonCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.<>c__DisplayClass17_0.<RealizeService>b__0(ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType)
at Microsoft.Extensions.Internal.ActivatorUtilities.GetService(IServiceProvider sp, Type type, Type requiredBy, Boolean isDefaultParameterRequired)
at Microsoft.AspNetCore.Mvc.Controllers.ControllerActivatorProvider.<>c__DisplayClass5_0.<CreateActivator>b__0(ControllerContext controllerContext)
at Microsoft.AspNetCore.Mvc.Controllers.ControllerFactoryProvider.<>c__DisplayClass5_0.<CreateControllerFactory>g__CreateController0(ControllerContext controllerContext)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.MoveNext()

 

After various searches, I found the answer here: https://blogs.msdn.microsoft.com/dotnet/2017/05/12/announcing-ef-core-2-0-preview-1/

Note the passage.

If you are using a third party database provider, then check to see if they have released an update that depends on 2.0.0-preview1-final. If they have, then just upgrade to the new version. If not, then you will not be able to upgrade since version 2.0 contains several breaking changes and 1.* providers are not expected to work with it.

So the answer is: there is no answer. Sit back and wait for MySQL.Data.EntityFrameworkCore to be updated.