Tag Archives: mysql

ERROR 1261 (01000): Row 1 doesn‘t contain data for all columns

1. Error Messages:

MariaDB [baidd]> select * from t1  into outfile '/var/lib/mysql/t1.txt';
Query OK, 1 row affected (0.000 sec
MariaDB [baidd]>  load data infile '/var/lib/mysql/t1.txt' into table t2;
ERROR 1261 (01000): Row 1 doesn't contain data for all columns
2. Reason
t2 has more fields than t1, so the import fails.

[Solved] java.sql.SQLException: Unsupported character encoding ‘utf-8

An error is reported when running connection pool in idea: java.sql.sqlexception: Unsupported character encoding ‘UTF-8’

//run   
public static void main(String[] args) throws SQLException {
        ConPool pool = new ConPool("mysql");
        pool.initPool(5);
        PoolCon borrow = pool.borrow();
        ResultSet resultSet = borrow.getCon().createStatement().executeQuery("select * from master_tab");
        while (resultSet.next()) {
            System.out.println(resultSet.getString("name"));
        }
    }

Error reporting interface:

java.sql.SQLException: Unsupported character encoding 'utf-8
			'.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
	at com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2575)
	at com.mysql.jdbc.ConnectionPropertiesImpl.initializeProperties(ConnectionPropertiesImpl.java:2545)
	at com.mysql.jdbc.ConnectionImpl.initializeDriverProperties(ConnectionImpl.java:3143)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:762)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at cn.kgc.base.ConPool.con(ConPool.java:82)
	at cn.kgc.base.ConPool.initPool(ConPool.java:96)
	at cn.kgc.base.ConPool.main(ConPool.java:149)
java.sql.SQLException: Unsupported character encoding 'utf-8
			'.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
	at com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2575)
	at com.mysql.jdbc.ConnectionPropertiesImpl.initializeProperties(ConnectionPropertiesImpl.java:2545)
	at com.mysql.jdbc.ConnectionImpl.initializeDriverProperties(ConnectionImpl.java:3143)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:762)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at cn.kgc.base.ConPool.con(ConPool.java:82)
	at cn.kgc.base.ConPool.initPool(ConPool.java:96)
	at cn.kgc.base.ConPool.main(ConPool.java:149)
java.sql.SQLException: Unsupported character encoding 'utf-8
			'.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
	at com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2575)
	at com.mysql.jdbc.ConnectionPropertiesImpl.initializeProperties(ConnectionPropertiesImpl.java:2545)
	at com.mysql.jdbc.ConnectionImpl.initializeDriverProperties(ConnectionImpl.java:3143)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:762)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at cn.kgc.base.ConPool.con(ConPool.java:82)
	at cn.kgc.base.ConPool.initPool(ConPool.java:96)
	at cn.kgc.base.ConPool.main(ConPool.java:149)
java.sql.SQLException: Unsupported character encoding 'utf-8
			'.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
	at com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2575)
	at com.mysql.jdbc.ConnectionPropertiesImpl.initializeProperties(ConnectionPropertiesImpl.java:2545)
	at com.mysql.jdbc.ConnectionImpl.initializeDriverProperties(ConnectionImpl.java:3143)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:762)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at cn.kgc.base.ConPool.con(ConPool.java:82)
	at cn.kgc.base.ConPool.initPool(ConPool.java:96)
	at cn.kgc.base.ConPool.main(ConPool.java:149)
java.sql.SQLException: Unsupported character encoding 'utf-8
			'.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
	at com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2575)
	at com.mysql.jdbc.ConnectionPropertiesImpl.initializeProperties(ConnectionPropertiesImpl.java:2545)
	at com.mysql.jdbc.ConnectionImpl.initializeDriverProperties(ConnectionImpl.java:3143)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:762)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at cn.kgc.base.ConPool.con(ConPool.java:82)
	at cn.kgc.base.ConPool.initPool(ConPool.java:96)
	at cn.kgc.base.ConPool.main(ConPool.java:149)
Pool connection object initialization not yet halfway exception

Process finished with exit code -4

Problems found in XML file: <value></value> The code is too long when writing, and the </value> Line breaks were made.

<configuration>
	<datasource id="mysql">
		<property>
			<name>driver</name>
			<value>com.mysql.jdbc.Driver</value>
		</property>
		<property>
			<name>url</name>
			<value>jdbc:mysql://192.168.1.200:3306/master_slave_copy?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf-8
			</value>//Error
		</property>
		<property>
			<name>username</name>
			<value>root</value>
		</property>
		<property>
			<name>password</name>
			<value>ok</value>
		</property>
	</datasource>
	<datasource id="oracle"></datasource>
</configuration>

Solution: add </value> Adjust to <value> Same line

<configuration>
	<datasource id="mysql">
		<property>
			<name>driver</name>
			<value>com.mysql.jdbc.Driver</value>
		</property>
		<property>
			<name>url</name>
			<value>jdbc:mysql://192.168.1.200:3306/master_slave_copy?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf-8</value>
		</property>
		<property>
			<name>username</name>
			<value>root</value>
		</property>
		<property>
			<name>password</name>
			<value>ok</value>
		</property>
	</datasource>
	<datasource id="oracle"></datasource>
</configuration>

Run successfully!

[Solved] Sqlyog always reports an error when creating a new connection.

Case 1:

In versions before MySQL 8, the encryption rule is mysql_native_Password, and after MySQL 8, the encryption rule is caching_sha2_password。 There are two ways to solve the problem. The first is to upgrade the graphical interface tool version, and the second is to restore the MySQL user login password encryption rules to MySQL_native_password.
the second solution is as follows. After logging into MySQL database with the command line, execute the following command to modify the user password encryption rules and update the user password. Here, modify the user name as“ root@localhost ”The user password rule of is “mysql_native_password”, and the password value is “OK”, as shown in the figure.

//Using mysql database
USE mysql; 

//change the password rule and password for the 'root'@'localhost' user
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ok'; 

//refresh permissions
FLUSH PRIVILEGES;

Situation II

The virtual machine MySQL is connected, and you need to perform the authorization operation
authorize the root user:
grant all on. To ‘root’ @ ‘%’ identified by ‘use password’
flush privileges;

How to Solve c3p0 connect mysql8.0 Error

Environmental Science:

JDK1.7
mysql-connector-java 8.0.16

C3p0 configuration file

Error message:

Incompatibility between the JDK version and the jdbc driver
java.lang.unsupported classversionerror 

Exception in thread "com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2" 
java.lang.UnsupportedClassVersionError: com/mysql/cj/jdbc/Driver :
Unsupported major.minor version 52.0

Solve the problem: change the JDK to 1.8, restart, or report an error. Replace the configuration information of the URL successfully. Details are as follows:

Connect to the database using a profile

Create c3p0-config.xml in the SRC folder. The name and address cannot be changed

Profile code, note & amp; To escape to & amp; amp

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/webdemo?useSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config>
</c3p0-config>

Test code

package cn.wahll.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class c3p0Demo {
    @Test
    public void c3p0PoolTest() throws Exception {
        //Find the default configuration directly under the configuration file
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection conn = dataSource.getConnection();
        String sql = "INSERT INTO category VALUES('bsafvb','asdgg')";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.executeUpdate();
    }
}

[Solved] MySQL Startup Error: The server quit without updating PID file

Specific reports are as follows:
Starting MySQL
. Error! The server quit without updating PID file (/opt/homebrew/was/mysql/QdeMacBook-Pro.local.pid).

Resolution programme:

linux:
sudo chmod -R 777 /usr/local/var/mysql/

Start:
systemctl restart mysqld

Mac: homebrew installed.

chmod -R 777 /opt/homebrew/was/mysql

Start:
sudo mysql.server restart

Error! MySQL server PID file could not be found!
Starting MySQL
SUCCESS!

[Solved] MySql Error: unblock with ‘mysqladmin flush-hosts

Information troubleshooting

# Check the maximum number of connection errors
show global variables like 'max_connect_errors';

# Check the connection IP
select * from performance_schema.host_cache

 # Flush the database IP cache 
flush hosts


 # View connections
SELECT substring_index(host, ':',1) AS host_name, state, count(*) FROM information_schema.processlist GROUP BY state, host_name;

Solution: enter the database and use the admin permission account

1. Set the value of the variable max_connection_errors to a larger value

set global max_connect_errors=1500;


2. Execute the command to use flush hosts


3、Change the value of the system variable so that MySQL server does not record host cache information (not recommended) e.g. set global host_cache_size=0;

JAVA Code Start Exception Error View Does Not Exsit [Solved]

Foreword: the Java code startup exception and error report view does not exist, but the view can still be seen in Navicat, but it does not exist when you double-click to open the view

Remedy: (all the following operations are performed in Navicat’s SQL command line)
1. Look for the view in the previous database backup
2. The use command allows us to use the database, the use command format: use ;
3. show create view ;
4. Copy the code shown in the previous step how to create
5. Then use the new database;
6. Clean up the code for creating a view copied in 4, and replace the original database name with the new database name
7. Just run

Linux Error: [ERROR] –initialize specified but the data directory has files in it. Aborting.

[error] – initialize specified but the data directory has files in it. About.:

Problem Description:

MySQL is installed in Linux. Because it has been installed and uninstalled before, the uninstallation is not clean. When installing again, the following error occurs when initializing the password

2021-11-23T02:08:16.247240Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-11-23T02:08:16.248753Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2021-11-23T02:08:16.248780Z 0 [ERROR] Aborting

Cause analysis:

reason: the old version of MySQL software does not delete the original data file, that is, the content under the path/var/lib/MySQL/

Solution: delete all contents under the path, including files and folders.

[root@hadoop102 mysql]# rm -rf  /var/lib/mysql/

After successful execution, initialize the password:

[root@hadoop102 opt]# mysqld --initialize --user=mysql
[root@hadoop102 opt]# cat /var/log/mysqld.log
2021-11-23T02:17:47.821574Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-11-23T02:17:47.988844Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-11-23T02:17:48.010498Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-11-23T02:17:48.066559Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 8d49959d-4c03-11ec-be82-000c29bdc989.
2021-11-23T02:17:48.067296Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-11-23T02:17:48.067886Z 1 [Note] A temporary password is generated for root@localhost: UBX6u)ekR:kB

Because there is no data saved in my data, direct deletion will not affect subsequent tasks, but!!! If you have data, you must back it up first~

MySQL | ERROR : Every derived table must have its own alias [Solved]

Reason for resolution:

In the process of multi-level query, you need to give the table alias.

Code example

Wrong query method:

select * from (select s.sno from student s where s.ssex='woman');

Correct query method:

select * from (select s.sno from student s where s.ssex='woman') as mid_sno;

In fact, the difference lies in whether there is an as mid behind it_sno. This is to ensure that each derived table needs to have its own alias.

[Solved] memcached Install Error: failed to ignore SIGHUP: no error

Memcached installs version 1.4.5
the administrator runs CMD with the following error:

Solution:
on the command line, enter schtasks/create/SC OnStart/TN memcached/TR "'c:\memcached\memcached. Exe '- M 512" , enter
note: C:\memcached\memcached.exe replace your installation address, Please refer to memcached installation for more installation details

See below:

ERROR 3021 (HY000): Unknown error 3021 [How to Solve]

An error is reported when implementing MySQL master-slave replication configuration, as shown in the figure:

Reason: it is not allowed to change when starting MySQL master-slave replication
Solution: first turn off MySQL master-slave replication, then refresh, then make changes, and then turn on MySQL master-slave replication
① turn off MySQL master-slave replication

stop slave;

② Refresh

flush privileges;

③ Enable MySQL master-slave replication

start slave;

[Solved] JDBC error: Communications link failure

Change url: In addition?useUnicode=true& CharacterEncoding=UTF-8& serverTimezone=UTC”

 <!-- Database connection pooling -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/user_db?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>