Tag Archives: mysql

MySQL connection timeout: error sqlstate [HY000] [2002] connection timed out

Why can’t you grab tickets when you travel on holiday?Reveal the key technology of 12306 how to ensure the ticket is not oversold>>>

I found a lot of them on the Internet, but they were all wrong

Later, I realized that it was actually a matter of setting

When your code is deployed to the server, the host value of your MySQL should be

127.0.0.1

Not your server IP

Otherwise, it will report an error

In fact, when your code enters the server, MySQL and the code are under the same IP, so 127.0.0.1 should be used instead of the server IP

Remember! No need to change the configuration

The solution of job failed to start when modifying MySQL character encoding

Found the following from the web: $sudo gedit /etc/mysql/my.cnf

Under [client] add.

default-character-set=utf8

Under [mysqld] add.

default-character-set=utf8

 Then save and exit

$ /etc/init.d/mysql restart

 The result appears:

Since the script you are attempting to invoke has been converted to an Upstart job, you may also use the stop(8) and then start(8) utilities, e.g. stop mysql ; start mysql. The restart(8) utility is also available. start: Job failed to start

It may be a version problem, check the method of modifying the character encoding method for versions after 5.5, and found that the method of modifying [mysqld] has changed to

[mysqld] added under should read.

character-set-server=utf8

collation-server=utf8_general_ci

save and quit

$ /etc/init.d/mysql restart

success

To access the MySQL console.

show variables like 'character%';

+———————————–+———————————–+

| Variable_name | Value |

+———————————–+———————————–+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+————————————+———————————–+

8 rows in set (0.00 sec)

How to Solve Mysql Error 1206: The total number of locks exceeds the lock table size

Mysql solves The total number of locks exceeds the lock table size error
The total number of locks exceeds the lock table size error occurs when a field is modified correctly the first time, but the second time when a local UPDATE of one million data is performed

Quoting the following explanation from the Internet

If you’re running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error: ERROR 1206 (HY000): The total number of locks exceeds the lock table size MySQL is trying to tell you that it doesn’t have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything. If you need a temporary workaround, reduce the amount of rows you’re manipulating in one query. For example, if you need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 rows. If you’re inserting many rows, try to insert portions of the data at a single time.

It turns out that this problem occurs when InnoDB table performs update, insert, and delete operations on large batches of data, so you need to adjust the value of InnoDB global innodb_buffer_pool_size to solve this problem and restart mysql service. Check the current database storage engine and use ENGINE=InnoDB type at creation time. Default innodb_buffer_pool_size=8M

#View the MySQL storage engine
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)

# Check the size of MySQL cache pool
#You can see that the default cache pool size is 8388608 = 8 * 1024 * 1024 = 8 MB. you need to change it to a larger size.
mysql> show variables like "%_buffer_pool_size%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set, 1 warning (0.00 sec)

#Modify innodb_buffer_pool_size
mysql> SET GLOBAL innodb_buffer_pool_size=2147483648;
#change to 2g.
#Note that the changes take effect after mysql 5.7, but the previous version has to be modified in my.cnf and restarted.

The solution to the error code is 2203 during MySQL installation

The solution to the error code is 2203 during MySQL installation

1. Problem description

When installing MySQL on Windows system, the error code is 2203. , the details are as follows

The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203

2. Solutions

Baidu Dafa (lack of authority) reference: the error code is 2203 solution after adding authority (orz)

MySQL official website (no solution found) found the following bug, which means that I am not alone bug 73295the error code is 2203

Personal final solution (not necessarily for you, just a solution)

The control panel found that MySQL installer – Community has been installed

So, open it on the start menu and have a look

Unexpected, new prompt: connector/net version needs to be updated

Then, update it according to the prompts

Go straight to the next step

Perform update

Update failed (orz)

So why not just uninstall the old version of connector/net

Re install, installation successful, problem solved

After the download address of the historical version of MySQL connector net, it is found that in the control panel — > If the program and function are not displayed, it will hurt. Try the next upgrade. The version difference is a little big. It should be the main reason for the upgrade failure

3. Summary

To solve the problem, we need to step by step, and others can install it. That is, if there is a problem in our own environment, we need to find out more, think more, and find out what the possible problems are (company computer, just took over, previously installed Mysql to disk D, disk D was formatted when we took over)

If it helps you, please like it 👍 Yeah, support me

The solution of the table is full in MySQL

There is only one reason why the table is full appears in MySQL. The data capacity of the corresponding table reaches the upper limit of the system. Please refer to the official manual for specific restrictions: http://dev.mysql.com/doc/refman/5.1/zh/introduction.html#table -size。 You can use the show table status statement to view information about the table.

Solution 1:

Execute ALTER TABLE tbl_ name MAX_ ROWS=1000000000;

Solution 2:

Modify the configuration file/etc/my.cnf of MySQL, and add/modify two lines under [mysqld] (the following values are for reference only, please handle according to the actual situation)

tmp_ table_ Size = 256M// temporary table size

max_ heap_ table_ Size = 256M// memory table size

The system default is 16m. Don’t forget to restart mysql.

Solve the problem of unknown column ‘password’ in ‘field list’ in MySQL

There is no password in MySQL 5.7, and it has been changed to authentication_ string

update mysql.user set authentication_string=password('root') where user='root' ;

The complete way to change MySQL password is as follows:

1.vim /etc/my.cnf [mysqld] add skip-grant-tables

2./etc/init.d/mysqld restart

3.Type mysql in the terminal to log in directly to the MySQL database, and then use mysql

4.update mysql.user set authentication_string=password('root') where user='root' ;

5.flush privileges;  

6.quit;

7.Edit /etc/my.cnf to remove skip-grant-tables, then restart MySQL /etc/init.d/mysqld restart

8. mysql -u root -p and enter the password to log in to the MySQL database

In the old version, the command to change the password is as follows: mysql5.7 password is changed to authentication_ string

mysql> update user set password=password(“new password”) where user=”username”;

MySQL from_ Unixtime() and UNIX_ The difference of timestamp() function

UNIX timestamp is a kind of time representation, which is defined as the total number of seconds from 00:00:00 GMT on January 1, 1970 to now.

How to format timestamp in MySQL?

In mysql, because timestamp cannot support MS, MS is often used for storage. How to store the int type time in the database, such as:

one billion three hundred and forty-four million nine hundred and fifty-four thousand five hundred and fifteen

To a time format that we can normally understand with the naked eye?

We usually use from_ Unixtime function.

FROM_ UNIXTIME(unix_ timestamp,format)

unix_ Timestamp is the time stamp to be processed (the parameter is UNIX time stamp). It can be a field name or a UNIX time stamp string directly.

Format is the format to be converted

The explanation of MySQL official manual is: return the UNIX format value of ‘yyyy-mm-dd HH: mm: Ss’ or yyyymmddhhmmss_ The format of the timestamp parameter depends on whether the function is used in string or numeric context. If format has been given, the format of the result depends on the format string.

The format of format is consistent with the time format of daily programming language.

For example:

mysql> select FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S'); 
+-----------------------------------------------+ 
| FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S') | 
+-----------------------------------------------+ 
| 2012-08-14 22:28:35                           | 
+-----------------------------------------------+ 
1 row in set (0.00 sec)

UNIX_ Timestamp() is the relative function of time

UNIX_ TIMESTAMP(), UNIX_ TIMESTAMP(date)

The official manual explains that if it is called without parameters, a UNIX timestamp (seconds after ‘1970-01-01 00:00:00’ GMT) is returned as an unsigned integer. If you use date to call UNIX_ Timestamp (), which returns the parameter value in seconds after ‘1970-01-01 00:00:00’ GMT.

Date can be a date string, a datetime string, a timestamp or a number in yymmdd or yymmdd format of local time.

For example:

mysql> select UNIX_TIMESTAMP('2012-09-04 18:17:23'); 
+---------------------------------------+ 
| UNIX_TIMESTAMP('2012-09-04 18:17:23') | 
+---------------------------------------+ 
|                            1346753843 | 
+---------------------------------------+ 
1 row in set (0.00 sec)

In practical application, we can convert the format to millisecond format for comparison with the database, and we can also convert the int format of the database to ordinary format for comparison.

For example, query all blog data created after 18:00:00 on September 3, 2012

Method 1: convert the date to int

select * from blog where createdTime > UNIX_TIMESTAMP('2012-09-03 18:00:00' );

Method 2: convert int to time format

select * from blog where FROM_UNIXTIME(createdTime, '%Y-%m-%d %H:%i:%S') > '2012-09-03 18:00:00';

The following modifiers can be used in the format string to combine some common date formats:

%Name of month m (January… December)

%W week name (Sunday… Sunday)

%D the date of the month prefixed with English (1st, 2nd, 3rd, etc.)

%Year y, number, 4 digits

%Y year, number, 2 digits

%A abbreviated name of the week (sun… SAT)

%Days in month D, number (00… 31)

%Number of days in month e (0… 31)

%Month m, number (01… 12)

%Month C, number (1… 12)

%B abbreviated month name (Jan… DEC)

%J number of days in a year (001… 366)

%H hours (00… 23)

%K hours (0… 23)

%H hours (01… 12)

%I hours (01… 12)

%I minutes, numbers (00… 59)

%R time, 12 hours (HH: mm: SS [AP] m)

%T time, 24 hours (HH: mm: SS)

%S seconds (00… 59)

%S seconds (00… 59)

%P am or PM

%W days in a week (0 = Sunday… 6 = Saturday)

%U week (0… 52), where Sunday is the first day of the week

%Monday is the first day of the week

%%A text “%”

This article was first published on duwai’s official blog. Please indicate the source for Reprint: http://www.duwaiweb.com/blog/20120904_ ce7309cf-e25a-45f7-8397-0700fa459466.html

For more Java graduation projects, please visit: http://www.lunwenhui.com/java/page_ 5.html

[Err] 1273 – Unknown collation:’utf8mb4’u 0900’u ci’

Recently, I was working on a small project. When the data table file written by the team member was imported to my local, I reported [err] 1273 – unknown collection: ‘utf8mb4_ 0900_ ai_ Ci ‘error

 [Err] 1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

I know it’s the problem of character set encoding, but I changed the encoding of the database to utf8mb4, and then I imported it again. There was no utf8mb4 option when I imported the file

Baidu later found the solution

Solution:

All files in the file will be saved

utf8mb4_ 0900_ ai_ Ci is replaced by utf8_ general_ ci

utf8mb4 is replaced by utf8

Save and run again

Use sqoop to store HDFS data in MySQL and report an error_ 1566707990804_ 0002 failed with state FAILED due to: Tas k failed

Use sqoop to store HDFS data in MySQL database and report an error

Job job_ 1566707990804_ 0002 failed with state FAILED due to: Tas k failed task_ 1566707990804_ 0002_ m_ 0、

I encountered this problem because when creating a table in mysql, varchar (10) is used. If the content in the data is greater than 10, increase the varchar to solve it!