Tag Archives: mysql

Analysis of JDBC connection to MySQL

JDBC connection to MySQL problem resolution
Recently, a series of problems occurred while configuring JDBC to connect to MySQL. Fortunately, it all worked out in the end. Here is a summary of the problems, hoping to help others with the same problems.
BlueStragglers shares the joy of technology growth

A JDBC connection to MySQL analytical error: com.mysql.jdbc.exceptions.jdbc4.Com municationsException: Communications link failure2. Unknown initial character set index ‘255’ received from server.Initial client character3. Error: references to the entity ‘characterEncoding’ must begin with ‘; ‘End of delimiter 4. Reference content

1. An error: com.mysql.jdbc.exceptions.jdbc4.Com municationsException: Communications link failure
This problem is most likely due to a problem with the mysql-connector-java version of the pom.xml file. I started with version 5.1.44 and found that I kept reporting this error. This issue can be resolved by returning 5.1.6.
Of course, there is another case where this error is reported: MySQL Server will automatically disconnect and report an error if the connection takes more than 8 hours. The solution is usually to modify the configuration file.
Unknown initial character set index ‘255’ received from server.Initial client character
This problem is generally caused by the occurrence of Chinese in MySQL, which can not be properly resolved. The solution is to put a UTF8 encoding requirement at the end of the URL to avoid errors. For example, the source statement is:

<property name="url" value="jdbc:mysql://"/>

This can be modified to the following statement:

<property name="url" value="jdbc:mysql://;characterEncoding=utf8"/>

3. Error: references to the entity ‘CharacterEncoding’ must begin with ‘; ‘End of the delimiter
Encounter this problem, I am quite surprised, actually is Chinese report error. It was later discovered that MySQL had a problem with characterEncoding= UTF8, so it may have been for this reason that the error was reported in Chinese.
& & instead. For example, the following statement:

<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&characterEncoding=utf8" />

The above statement will report an error. If you need to modify it to the following statement, it will not report an error:

<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;characterEncoding=utf8" />

4. References

    JDBC connection to MySQL references to the entity 'CharacterEncoding' must begin with '; 'End of delimiter

solve com.mysql.jdbc . exceptions.jdbc4 .MySQLSyntaxErrorException:

Solve the com. Mysql. JDBC. Exceptions. Jdbc4. MySQLSyntaxErrorException:
1. Error content

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where userid='j2ee'' at line 1
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
	at [email protected]/com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at [email protected]/com.mysql.jdbc.Util.getInstance(Util.java:408)
	at [email protected]/com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at [email protected]/com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at [email protected]/com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at [email protected]/com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at [email protected]/com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at [email protected]/com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
	at [email protected]/com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at [email protected]/com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
	at com.a51work6.jpetstore.dao.mysql.AccountDaolmp.findById(AccountDaolmp.java:33)
	at com.a51work6.jpetstore.ui.LoginFrame.lambda$0(LoginFrame.java:65)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
	at java.desktop/java.awt.Component.processMouseEvent(Component.java:6632)
	at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
	at java.desktop/java.awt.Component.processEvent(Component.java:6397)
	at java.desktop/java.awt.Container.processEvent(Container.java:2263)
	at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5008)
	at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
	at java.desktop/java.awt.Component.dispatchEvent(Component.java:4840)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4918)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4547)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4488)
	at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307)
	at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2762)
	at java.desktop/java.awt.Component.dispatchEvent(Component.java:4840)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
	at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
	at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
	at java.base/java.security.AccessController.doPrivileged(AccessController.java:389)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
	at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
	at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
	at java.base/java.security.AccessController.doPrivileged(AccessController.java:389)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
	at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
	at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

2. Problem analysis
This error is usually caused by an error in the SQL statement in my code. My code is as follows:

			String sql="select userid, password,email,name,addr,userid,city,country,phone"     //userid,password,email,name,addr,city,country,phone
					+"from account where userid=?";
			pstmt.setString(1, id);

Where userid=’ J2EE ‘is an error. I thought I had a problem with id, but it turned out to be a space missing when concatenating the SQL string.
3. Summary
looked at other people’s blogs, and he made the wrong conclusion in this type of error check content:
1, check to see if the format of the SQL statement right
2, check the SQL statement whether the use of punctuation in English
3, “mark> connection, special attention to the SQL statement must add Spaces among

Centos8 solves the problem of “failed to set locale, defaulting to c.utf-8”

Problem description


systemctl status mysqld.service

journalctl -xe

Failed to set locale, defaulting to C.UTF-8
How to solve
To set the system locale, use the localectl command. For example, if you want to use UTF-8 encoded American English (US), run the following command.
download centos docker mirror system default is ISO/IEC 15897 character set
need to utf-8.
to install all of the first character set

dnf install langpacks-en glibc-all-langpacks -y

Set character set

localectl set-locale LANG=en_US.UTF-8

Restart the mysql

A convenient method to count the total number of query results in MySQL

MYSQL keywords:
to check the manual and found this keyword is used in the query result of statistical filtering conditions when the total number of (not restricted by Limit)
For example:

SELECT SQL_CALC_FOUND_ROWS tid FROM cdb_threads WHERE fid=14 LIMIT 1,10;

Let’s say there are 1,000 that satisfy our condition, and we return 10.

for immediate use

SELECT found_rows() AS rowcount;

Return rowcount = 1000;
this saved a SELECT count (*) AS the rowcount repeat query, can save considerable time.

From: http://ginew.blog.163.com/

Enum type and set type of MySQL

Enumeration type and collection type of MySQL

create table consumer(
    id int,
    name char(16),
    sex enum('male','female','other'),
    level enum('vip1','vip2','vip3'),
    hobbies set('play','music','read','run')

Enumeration here means that you can select only one from here, and set means that you can select more than one from set.
Input insert statement

insert into consumer values(1,'egon','male','vip2','music,read')

And you might be wondering, what happens if I put multiple entries in the corresponding enum?

insert into consumer values(2,'eg','male','vip1,vip2','music,read')


InnoDB, tokudb, MyISAM directory structure

Physically, InnoDB tables consist of shared tablespaces, log file groups (redo file groups), and table structure definition files.
innodb has a relatively different directory structure, divided into shared tablespaces, separate tablespaces.
The type is controlled by the parameter innodb_file_per_table. 0: Use shared tablespace;
show variables like “innodb_file_per_table”; See the file directories under
in the data_dir definition.
Tablespace independent
Separate tablespaces are enabled. Each database creates a file of the same name to store table structure files, index files, and data files. However, undo rollback logs to transactions and redo log buffers are still stored in the shared tablespace.
Table_name.frm defines the table structure.
table_name.ibd Stores table indexes and data.

    Each table has its own independent table space, the data and index of each table will exist in its own table space, you can achieve a single table in different databases to move. Space can be reclaimed (except for the DROP TABLE operation, where a table empty cannot be reclaimed by itself). Alter table TableName engine=innodb alter table TableName engine=innodb alter table TableName engine=innodb; Retract unused space. Using Turncate Table for InnoDB with innodb-plugin also shrinks the space. For tables that use separate tablespaces, no matter how they are dropped, tablespace fragmentation will not have a significant impact on performance, and there is still a chance to handle it.


    single table increase is too large, when the single table occupies too much space, the storage space is insufficient, can only think about the solution from the operating system level, the maximum limit of table space is 64TB.

If no separate tablespaces are enabled, they are all stored in IBDATA1. You can set its size and automatically expand it when it exceeds the limit size.

    table space can be split into multiple files for each disk, so the table can be split into multiple files for each disk. The size of the table is not limited by the disk size. Data and files are put together for easy management.


    all data and index to a file, while it is possible to put a large file into multiple small files, but multiple tables and indexes mixed stored in table space, when is a large amount of data, made a lot of delete table after table space will have a lot of space, especially for statistical analysis, for often delete operation of this kind of application the most weak Shared table space. Can’t bounce back after sharing a table space distribution: when there is a temporary indexed or create a temporary table operating table space is enlarged, is to delete related tables didn’t also the way to retract the part space (can be understood as oracle 10 g of table space, but only use 10 m, but the operating system shows the mysql table space for 10 g), database of cold standby is slow.

MySQL has a “double write” mechanism for writing data pages.
MySQL has a “double write” mechanism for writing data pages.
MySQL has a “double write” mechanism for writing data pages. The redo log records page operations at the physical level, and now the page is only 4KB written, which is itself a “faulty” page, so the redo log records the page writes in error. Thus, there is a double write: the pages are copied to the double write buffer, then the pages are written to the shared tablespaces in order, and finally a copy is written to the corresponding tablespaces.
When TOKUDB is started, it reads TOKUDB.DIRECTORY, organizes the table related files according to the key information, and writes them to the INFORMATION_SCHEMA. TOKUDB_FILE_MAP table.
Tokudb. directory defines table/index file information.
tokudb. Environment tokudb version number information.
tokudb.rollback undo record .
log000000000009 tokulog27 redo records.
tokudb_lock_dont_delete_me_* file lock ensures that the same datadir can only be used by one TokuDB process.

_test_table_name_key_name_45ca56_3_1b_b_0. tokudb index file
myisam_table.MYD table data
isam_table. MYI table index

The time of the time database displayed by the front end is inconsistent

These days I have encountered a huge pit. The time of data storage is 2 o ‘clock, but the time displayed on the front page is 16 o ‘clock, which is 14 hours short.
Describe the project background, the service is in the US, the project is a Mexican project, the database time and server time have been set to the Mexican time zone, but it is still not correct.
I have asked the project boss in the middle, and the boss gave me the opinion that the time zone was set on the URL of connecting data, but the front-end page was still not displayed correctly.
After several times of locating, the time to find it directly in the dev location will change. This is because the computer’s time zone is the Chinese time zone. But the time log is still on the server, the log time is correct. So guess the front-end is the time zone problem.
Because the last segment passes the timestamp, the timestamp has no time zone. So the front end also needs to change the time zone to correspond

  id : "crete_time",
		            renderer : function(idValue,value,record){
                        return value ?moment(value).tz("America/Guatemala").format("YYYY-MM-DD HH:mm:ss") : "---";

You can set the time zone using the Moment.tz method

Tar: due to the previous error, it will exit with the last error state

Official website to download the mysql connector – odbc – 8.0.21 – Linux – glibc2.12 – x86-64 – bit. Tar. Gz encountered error: tar ZXVF decompression –
Tar: Exits the previous error state because of the previous error
Solution: the source package placed in /home directory can be decompressed successfully
The reason has not been analyzed yet. If you want to see it, you can comment and reply to discuss and communicate with us

Install / Remove of the Service denied! Error occurred when installing MySQL service

Mysqld –install /Remove of the Service Denied: install /Remove of the Service Denied: mysqld –install /Remove of the Service Denied: install /Remove of the Service Denied: Wrong. Strange.
This time, you need to right-click “Run as Administrator”, and then type mysqld –install
OK, the service is installed successfully!