Tag Archives: mysql

ERROR 1075 (42000) Incorrect table definition; there can be only one auto column and it must be defi

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
The table is created as follows:

mysql> create table tb_emp5(
    -> id int(11) not null auto_increment,
    -> name varchar(11),
    -> deptid int(11) not null auto_increment,
    -> salary float,
    -> primary key(id,deptid)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

when creating a MySQL table, if you want to set auto_increment, you must set the primary key, not null, and only one

After the change is as follows:

mysql> create table tb_emp5(
    -> id int(11) not null auto_increment,
    -> name varchar(11),
    -> deptid int(11) not null,
    -> salary float,
    -> primary key(id)
    -> );

mysql> desc tb_emp5;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(11) | YES  |     | NULL    |                |
| deptid | int(11)     | NO   |     | NULL    |                |
| salary | float       | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

[Solved] error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such…

Exception: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
Solution:

# find/ -name 'libmysqlclient*'
/usr/local/mysql/lib/libmysqlclient.a
/usr/local/mysql/lib/libmysqlclient.so.20.3.10
/usr/local/mysql/lib/libmysqlclient.so
/usr/local/mysql/lib/libmysqlclient.so.20
/usr/lib64/mysql/libmysqlclient.so.18
/usr/lib64/mysql/libmysqlclient.so.18.0.0

# ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/local/lib/libmysqlclient.so.20
# vim /etc/ld.so.conf
/usr/local/lib				#Write
# ldconfig -v

How to Solve Sqlyog error 2058

Sqlyog configures a new connection and reports an error: the error number is 2058. It is analyzed that the MySQL password encryption method has changed
solution:
alter user ‘root’ @ ‘localhost’ identified with MySQL_ native_ password BY ‘password’; (note the semicolon)
#password is the root password you set yourself

after reconfiguring the connection of sqlyog, the connection is successful and it is OK.

[Solved] CRITICAL:yum.cli:Config error: Error accessing file for config file:///opt/++

report errors

[root@localhost opt]# ls
apr-1.6.2.tar.gz
apr-util-1.6.0.tar.gz
boost_1_59_0.tar.gz
httpd-2.4.29.tar.bz2
mysql-boost-5.7.20.tar.gz
rh
[root@localhost opt]# yum install -y \
> gcc \
> gcc -c++ \
> ncurses-devel \
> bison \
> cmake
CRITICAL:yum.cli:Config error: Error accessing file for config file:///opt/++
error:
[root@localhost opt]# yum -y install gcc gcc-c++ make pcre pcre-devel expat-devel perl
Enter this command to solve the installation problem!!!

solve!

[error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63] Permission denied (publickey,gssapi-ke

When building mha, the following error was reported when using the masterha_check_ssh script to do ssh communication between the three nodes.

Master: 192.168.0.60 master
backup1: 192.168.0.61 slave1
Backup 2: 192.168.0.62 slave2, while the management node of mha is placed on the second slave

[root@manager mha_master]# masterha_check_ssh –global_conf=/etc/mha_master/mha.cnf –conf=/etc/mha_master/mha.cnf
Sun Sep 26 14:30:23 2021 – [info] Reading default configuration from /etc/mha_master/mha.cnf…
Sun Sep 26 14:30:23 2021 – [info] Reading application default configuration from /etc/mha_master/mha.cnf…
Sun Sep 26 14:30:23 2021 – [info] Reading server configuration from /etc/mha_master/mha.cnf…
Sun Sep 26 14:30:23 2021 – [info] Starting SSH connection tests…
Sun Sep 26 14:30:24 2021 – [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Sun Sep 26 14:30:23 2021 – [debug] Connecting via SSH from [email protected](192.168.0.60:22) to [email protected](192.168.0.61:22)…
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Sun Sep 26 14:30:24 2021 – [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from [email protected](192.168.0.60:22) to [email protected](192.168.0.61:22) failed!
Sun Sep 26 14:30:24 2021 – [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Sun Sep 26 14:30:24 2021 – [debug] Connecting via SSH from [email protected](192.168.0.61:22) to [email protected](192.168.0.60:22)…
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Sun Sep 26 14:30:24 2021 – [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from [email protected](192.168.0.61:22) to [email protected](192.168.0.60:22) failed!
Sun Sep 26 14:30:25 2021 – [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Sun Sep 26 14:30:24 2021 – [debug] Connecting via SSH from [email protected](192.168.0.62:22) to [email protected](192.168.0.60:22)…
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Sun Sep 26 14:30:25 2021 – [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from [email protected](192.168.0.62:22) to [email protected](192.168.0.60:22) failed!
SSH Configuration Check Failed!
at /bin/masterha_check_ssh line 44.
The solution is as follows.

    1. Manually check if the ssh communication between the three nodes is normal , use the format ssh node1 date, each node is executed three times, pay attention to the user who does the ssh communication, for example: I use mysql to do the mutual trust between the three nodes, then we have to switch to mysql to do the check, not the root user
    1. [mysql@slave1 .ssh]$ ssh master date
    1. Sun Sep 26 14:48:30 CST 2021
    1. [mysql@slave1 .ssh]$ ssh slave1 date
    1. Sun Sep 26 14:48:34 CST 2021
    1. [mysql@slave1 .ssh]$ ssh manager date
    1. Sun Sep 26 14:48:38 CST 2021 Use mysql user to execute mutual trust script check, check all ok
    1. Check script.
    1. masterha_check_ssh –global_conf=/etc/mha_master/mha.cnf –conf=/etc/mha_master/mha.cnf
      1. or
    masterha_check_ssh –conf=/etc/mha_master/mha.cnf

[mysql@manager .ssh]$ masterha_check_ssh –global_conf=/etc/mha_master/mha.cnf –conf=/etc/mha_master/mha.cnf
Sun Sep 26 14:29:30 2021 – [info] Reading default configuration from /etc/mha_master/mha.cnf…
Sun Sep 26 14:29:30 2021 – [info] Reading application default configuration from /etc/mha_master/mha.cnf…
Sun Sep 26 14:29:30 2021 – [info] Reading server configuration from /etc/mha_master/mha.cnf…
Sun Sep 26 14:29:30 2021 – [info] Starting SSH connection tests…
Sun Sep 26 14:29:31 2021 – [debug]
Sun Sep 26 14:29:30 2021 – [debug] Connecting via SSH from [email protected](192.168.0.60:22) to [email protected](192.168.0.61:22)…
Sun Sep 26 14:29:31 2021 – [debug] ok.
Sun Sep 26 14:29:31 2021 – [debug] Connecting via SSH from [email protected](192.168.0.60:22) to [email protected](192.168.0.62:22)…
Sun Sep 26 14:29:31 2021 – [debug] ok.
Sun Sep 26 14:29:32 2021 – [debug]
Sun Sep 26 14:29:31 2021 – [debug] Connecting via SSH from [email protected](192.168.0.61:22) to [email protected](192.168.0.60:22)…
Sun Sep 26 14:29:31 2021 – [debug] ok.
Sun Sep 26 14:29:31 2021 – [debug] Connecting via SSH from [email protected](192.168.0.61:22) to [email protected](192.168.0.62:22)…
Sun Sep 26 14:29:31 2021 – [debug] ok.
Sun Sep 26 14:29:33 2021 – [debug]
Sun Sep 26 14:29:31 2021 – [debug] Connecting via SSH from [email protected](192.168.0.62:22) to [email protected](192.168.0.60:22)…
Sun Sep 26 14:29:32 2021 – [debug] ok.
Sun Sep 26 14:29:32 2021 – [debug] Connecting via SSH from [email protected](192.168.0.62:22) to [email protected](192.168.0.61:22)…
Sun Sep 26 14:29:32 2021 – [debug] ok.
Sun Sep 26 14:29:33 2021 – [info] All SSH connection tests passed successfully.
3. If you use root to execute the script, it will report the theme error, remember to switch the user.

[Solved] Git Clone Error: “error: RPC failed; curl 56 GnuTLS recv error (-9): A TLS packet with unexpected length …

1. When git pull is used for several large projects recently, the following errors always appear: </ font>

error: RPC failed; curl 56 GnuTLS recv error (-9): A TLS packet with unexpected length was received.
fatal: The remote end hung up unexpectedly
fatal: early EOF
fatal: index-pack failed

2. According to the online method, there are still problems</ font>

// set proxy
git config --global http.proxy socks5://127.0.0.1:1081
git config --global https.proxy socks5://127.0.0.1:1081
// add cache
git config --global http.postBuffer 1048576000

3. Finally, add the following operations to solve the problem

//Change this value to a larger value, the perfect solution, the default is 1500.
ifconfig eth0 mtu 14000

Using the TP framework, an error is reported sqlstate [08004] [1040] too many connections

Use
when MySQL inserts a large amount of data and needs to compare and duplicate          Db($table)-> where($where)-> find();

In this case, an error will be reported: sqlstate [08004] [1040] too many connections

This is mainly due to too many database connections

resolvent:

        Introduce DB class

                 use think\Db;

        The query statement is changed to:

                 Db::table($table)-> where($where)-> find();

That’s it

CommunicationsException: Communications link failure [How to Solve]

Today, when I was learning mybatis, I always reported an error when executing the query operation! The error is as follows:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

It probably means that there is a problem with the database connection. After checking the database name, user name and password, there are no errors. Some blogs say that they need to change the MySQL configuration file. I really think it’s too troublesome to change

Solution:

A blog commented that if MySQL is version 8.0 or above, usessl in the configuration should be set to false
and then it will be

useSSL=true

How to Solve JDBC connection error in spring MVC integration 

# Error Message:

### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
### The error may exist in com/dao/BookMapper.xml
### The error may involve com.dao.BookMapper.queryAllBook
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
	java.sql.SQLException: Access denied for user 'Charles'@'localhost' (using password: YES)
		at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
		at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
		at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
		at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
		at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
		at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
		at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
		at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
		at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
		at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
		at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
		at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
		at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
		at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
		at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
		at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

Cause analysis:

    1. check the database.properties file, but there is no error </ OL>
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC
username = root
password = admin
      1. the database used is mysql8.0, and the versions of database driver and database connection pool also match the database version </ OL>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>

        <!-- Database connection -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>
        1. related dependencies are also added to the artifacts in project structure

        1. . There is no mistake in the spring Dao configuration file. All reference paths are correct </ OL>
	<context:property-placeholder location="classpath:database.properties"/>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${driver}"/>
        <property name="jdbcUrl" value="${url}"/>
        <property name="user" value="${username}"/>
        <property name="password" value="${password}"/>

        <property name="maxPoolSize" value="30"/>
        <property name="minPoolSize" value="10"/>

        <property name="autoCommitOnClose" value="false"/>
        <property name="checkoutTimeout" value="10000"/>
        <property name="acquireRetryAttempts" value="2"/>
    </bean>

There is also a prompt when the cursor is placed on the value

online solutions have been tried and have not taken effect

Solution:

Finally, I accidentally tried to explicitly assign the attribute of the database connection pool, and the connection was successful