Tag Archives: mysql

MySQL Changes the configuration file (my.ini) Error [How to Solve]

1. Background

After installing mysql, change the configuration file my.int, and change its data directory. Restart MySQL and it cannot be started.

2. Troubleshooting

Check the log and find that the error is:

[ERROR] Can't find error-message file '/data/mysql/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

By searching the errmsg.sys file, copy it to the error directory and give corresponding permissions. Restart or report the same error. Change the MySQL configuration file back to the default. It is found that it can be started normally, and there is no other relevant error information in the log.

3. Solution

After troubleshooting for a period of time without results, I thought about whether the file could not be found due to the Linux security group SELinux. I closed SELinux randomly and found that the problem was solved.

setenforce 0           #Temporarily disable selinux
Change SELINUX=enforcing to SELINUX=disabled in the /etc/selinux/config file # permanently disable selinux
getenforce #Check the status of selinux

[Solved] MySQL Add New Field Error: ERROR 1118 — Row size too large. The maximum row size for the used table type

error message

1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
You have to change some columns to TEXT or BLOBs

Error reason

When MySQL builds a table, there is a limit on the maximum length of a single row: the total number of bytes set in all fields in a table is not more than 65535 bytes

Note 1: when the size of a single field exceeds 65535, it is converted to text
note 2: the sum of other fields does not exceed 65535 bytes (excluding blob/text)
note 3: the database uses UTF-8 encoding, and one character = three bytes
error reporting example: there are 10 varchar fields in the database, If each size is 3000, the current calculation length of the database single line is 3000 * 10 * 3 = 90000 & gt; 65535

Solution: set the large field type of the database table to text, or reduce the length of some parts that can be reduced to less than 65535

MySQL connection error no suitable driver found [How to Solve]

I thought the driver was not loaded before, so I added it

Class.forName("com.mysql.cj.jdbc.Driver");

After discovering the error, I read the error code again: no suitable driver found for localhost: 3306/upload_file

It turned out that the URL was misspelled and not prefixed: jdbc:mysql://

Attach the complete URL, and remember to add the suffix parameter

jdbc:mysql://localhost:3306/upload_file?&useSSL=false&serverTimezone=UTC

MySQL database insert into statement with parameters Error

When using MySQL insert statement, I suddenly found that when inserting non numeric parameters, the database will report an error

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column '2ddd22' in 'field list'

Cloumn is followed by the parameter to be inserted

import mysql.connector
 
class MysqlGroup(object):
    def __init__(self,host,user,password,database,charset):
        self.mydb=mysql.connector.connect(host = host,user = user,port = 3306,password = password,database = database,charset = charset,buffered = True)
        self.mycursor=self.mydb.cursor(buffered = True)
    
    def mysql_increase(self, surface_name, column_name1, column_name2, parameter1, parameter2):  
        self.mycursor.execute(
            "INSERT INTO %s (%s, %s) VALUES (%s, %s)" % (surface_name,column_name1, column_name2, parameter1, parameter2))
        self.mydb.commit()
 
                               
con1 = MysqlGroup('database address', 'database username', 'database password', 'database name', 'encoding method')    
jk = con1.mysql_increase('data','user', 'password','2ddd22','3333')

The reason for the error is that the database incorrectly identifies the value to be inserted as a column name
The solution is to add single quotes’ to both sides of the value to be inserted in the SQL statement, with the following code.
import mysql.connector

class MysqlGroup(object):
def __init__(self,host,user,password,database,charset):
self.mydb=mysql.connector.connect(host = host,user = user,port = 3306,password = password,database = database,charset = charset,buffered = True)
self.mycursor=self.mydb.cursor(buffered = True)

def mysql_increase(self, surface_name, column_name1, column_name2, parameter1, parameter2):
self.mycursor.execute(
“INSERT INTO %s (%s, %s) VALUES (‘%s’, ‘%s’)” % (surface_name,column_name1, column_name2, parameter1, parameter2))
self.mydb.commit()

con1 = MysqlGroup(‘database address’, ‘database username’, ‘database password’, ‘database name’, ‘encoding method’)
jk = con1.mysql_increase(‘data’,’user’, ‘password’,’2ddd22′,’3333′)
Disadvantage, after adding single quotes, the SQL statement will not recognize the keyword like Null and will treat it as a character. To use keywords, you need to write the SQL statement directly. For example：
import mysql.connector

def mysql_insert_null(self, surface_name, column_name1, column_name2):
self.mycursor.execute(
“INSERT INTO %s (%s, %s) VALUES (Null, Null)” % (surface_name,column_name1, column_name2))
self.mydb.commit()

Linux environment installation MYSQL Error: Redirecting to /bin/systemctl start mysqld.service Failed to start mysqld.service: Unit not found.

Use service mysqld start to start MySQL in centos7 with an error:

Before mentioning this error, let’s first mention the development and current background of MySQL:

MySQL is an open source relational database management system (RDBMS), which uses the most commonly used database management language – Structured Query Language (SQL) for database management
MySQL is open source, so anyone can download it under the license of the general public license and modify it according to personalized needs
MySQL was acquired by sun on January 16, 2008. Since then, as sun was acquired by Oracle, the ownership of MySQL also fell into Oracle’s hands
next, we will mention a new database, MariaDB:

MariaDB database management system is a branch of MySQL, which is mainly maintained by the open source community. The purpose of adopting GPL license is to be fully compatible with MySQL, including API and command line, so that it can easily become a substitute for MySQL
the MariaDB database was developed by the founder of MySQL.

Here’s how to uninstall MariaDB and install MySQL:

View the list of installed MariaDB:
RPM – QA | grep MariaDB

Uninstall all installed MariaDB (according to your own MariaDB list):
RPM – E — nodeps mariadb-libs-5.5.64-1.el7.x86_ 64
rpm -e –nodeps mariadb-5.5.64-1.el7.x86_ 64
rpm -e –nodeps mariadb-server-5.5.64-1.el7.x86_ 64
rpm -e –nodeps mariadb-devel-5.5.64-1.el7.x86_ 64

check the installed MariaDB list again and ensure that all are uninstalled:
RPM – QA | grep MariaDB

download the repo source of MySQL: (version 5.6)
WGet http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

Errors may occur: – bash: WGet: if the command is not found, execute the installation WGet. If the error is not reported, do not execute:

Yum – y install WGet

install mysql-community-release-el7-5.noarch.rpm
sudo RPM – IVH mysql-community-release-el7-5.noarch.rpm

install MySQL
sudo Yum install MySQL server

wait for the installation to complete

service mysqld start

View the current MySQL status

Saved MySQL that I haven’t installed successfully for many days

MySQL Error: Waiting for table metadata lock [How to Solve]

When MySQL performs DDL operations such as alter table, sometimes a waiting scenario of Waiting for table metadata lock appears. Moreover, once the operation of alter table TableA stalls in the Waiting for table metadata lock state, any subsequent operations on TableA (including reading) cannot be performed, because they will also enter the Waiting for table metadata lock lock during the opening tables stage. Waiting in the queue. If such a lock waiting queue appears in the core table of the production environment, it will cause catastrophic consequences.

The reason that causes the alter table to produce the Waiting for table metadata lock is actually very simple, generally the following simple scenarios:

Scenario 1: Long transaction runs, blocking DDL, and then blocking all subsequent operations of the same table

Through show processlist, you can see that there are ongoing operations (including reading) on TableA. At this time, the alter table statement cannot obtain the metadata exclusive lock and will wait.

This is the most basic situation, which does not conflict with the online ddl in mysql 5.6. Generally during the operation of the alter table (see the figure below), the metadata exclusive lock will be acquired in the after create step. When the altering table is in the process (usually the most time-consuming step), the reading and writing of the table can be normal Proceeding, this is the performance of online ddl, and will not block writes during the entire alter table process as before. (Of course, not all types of alter operations can be online. For details, please refer to the official manual: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html )
Processing method: kill the session where the DDL is located.

Scenario 2: Uncommitted things, blocking DDL, and then blocking all subsequent operations of the same table

You can’t see any operations on TableA through show processlist, but there are actually uncommitted transactions, which can be viewed in information_schema.innodb_trx . Before the transaction is completed, the lock on TableA will not be released, and the alter table also cannot obtain the exclusive lock of metadata.

Processing method: through select * from information_schema.innodb_trx \G, find the sid of the uncommitted thing, then kill it and let it roll back.

Scene three:

You can’t see any operations on TableA through show processlist, and there are no ongoing transactions in information_schema.innodb_trx. This is probably because in an explicit transaction, a failed operation was performed on TableA (for example, a non-existent field was queried). At this time, the transaction did not start, but the lock acquired by the failed statement was still valid and was not released.. The failed statement can be found in the performance_schema.events_statements_current table.

The description of this in the official manual is as follows:

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

In other words, except for syntax errors, the locks acquired by other incorrect statements will still not be released before the transaction is committed or rolled back. because the failed statement is written to the binary log and the locks protect log consistency But it is difficult to understand the reason for this behavior because the wrong statement will not be recorded to the binary log at all.

Processing method: find its sid through performance_schema.events_statements_current, kill the session. You can also kill the session where the DDL is located.

In short, the statement of alter table is very dangerous (in fact, his danger is caused by uncommitted things or long transactions). Before the operation, it is best to confirm that there are no ongoing operations on the table to be operated, no uncommitted transactions, There is also no error statement in an explicit transaction. If there is an alter table maintenance task and it is run when unsupervised, it is best to set the timeout time through lock_wait_timeout to avoid long waits for the metedata lock.

lock_wait_timeout represents the timeout (in seconds) for acquiring the metadata lock, and the allowed value range is 1 to 31536000 (1 year). The default value is 31536000. See https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lock_wait_timeout for details. The default value is one year! ! !

Adjust it to 30 minutes

set session lock_wait_timeout = 1800;
set global lock_wait_timeout = 1800;

Docker Run MYSQL Container Error: [ERROR] –initialize specified but the data directory has files in it. Aborting.

background

Start the MySQL container by running the local MAC docker

docker run -d -p 3306:3306 --name mysql1 -v /Users/polo/data/conf:/etc/mysql/conf.d -v /Users/polo/data/mysql:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

phenomenon

Check the container log and find that an error is reported, and the local Navicat cannot be connected

Root cause

Files already exist in the directory mounted through – V, so when – V selects the local directory, remember to select an empty directory/nonexistent directory (it will be created automatically)

Solution

Reference links for problem solving

Replace the -v mounted local directory with an empty directory/nonexistent directory

Start the log of MySQL container normally

No error log

How to Solve MySQL Secure file priv error

Problem description

When I want to export data in mysql, I find an error. The error contents are as follows

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Principle interpretation

secure file priv is a system variable in MySQL that controls the operation of importing and exporting data, such as load data or select... Into output or load_FILE()

Use show global variables like 'secure_File% ' view the set value of this variable, which may have the following three values

no value. That is, this variable has no effect

2﹥ point to a path. That is, import and export can only use this path

three ⃣ ️ NULL。 Import and export operations are prohibited

The default value of this system variable is different for different platforms

terms of settlement

Environment: MySQL 8.0.26 (installed with homebrew ), macbook pro 2020 Intel Edition

one ⃣ Create corresponding files, ~ /.My.CNF , and use your customary text editor. I use nano , which contains the following contents

[mysqld]
secure_file_priv = ''

two ⃣ Restart the MySQL service and log in

brew services stop mysql
brew services start mysql

mysql -uroot -p

3﹥ check whether the modification is successful in MySQL,

show global variables like 'secure_file%';

# If the modification is successful it should display the following, showing a null value
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

Done!

Link:
https://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

An error is reported when sqoop imports data into MySQL database: error tool. Exporttool: error during export: export job failed!

Problem Description:

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143

21/10/10 08:51:52 INFO mapreduce.Job:  map 100% reduce 0%
21/10/10 08:51:53 INFO mapreduce.Job: Job job_1633826412371_0001 failed with state FAILED due to: Task failed task_1633826412371_0001_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0

21/10/10 08:51:54 INFO mapreduce.Job: Counters: 9
    Job Counters 
        Failed map tasks=4
        Launched map tasks=4
        Other local map tasks=3
        Data-local map tasks=1
        Total time spent by all maps in occupied slots (ms)=52317
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=52317
        Total vcore-milliseconds taken by all map tasks=52317
        Total megabyte-milliseconds taken by all map tasks=53572608
21/10/10 08:51:54 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
21/10/10 08:51:54 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 110.2385 seconds (0 bytes/sec)
21/10/10 08:51:54 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead
21/10/10 08:51:54 INFO mapreduce.ExportJobBase: Exported 0 records.
21/10/10 08:51:54 ERROR tool.ExportTool: Error during export: Export job failed!

Solution:

① First, check whether the table structure in hive is consistent with MySQL. (desc table name)

If the table structure is consistent, it may be caused by inconsistent character sets of MySQL tables

② Sqoop import data to MySQL database command:

bin/sqoop export \
> --connect “jdbc:mysql://master:3306/mysql?useUnicode=true&characterEncoding=utf-8” \
> --username root \
> --password 000000 \
> --table QX_diyu_results \
> --num-mappers 1 \
> --export-dir /user/hive/warehouse/diyu_resaults \
> --input-fields-terminated-by ","

It can be seen from the command that the code I use is utf8, so the character set of the MySQL table should be modified to utf8

After the modification, run the command again. The following results show that the data import is successful:

[Solved] Hive export MYSQL Error: Container [pid=3962,containerID=container_1632883011739_0002_01_000002] is running 270113280B beyond the ‘VIRTUAL’ memory limit.

Problem description

Container [pid=3962,containerID=container_1632883011739_0002_01_000002] is running 270113280B beyond the ‘VIRTUAL’ memory limit.

Current usage: 91.9 MB of 1 GB physical memory used; 2.4 GB of 2.1 GB virtual memory used. Killing container.

Cause of problem

When yarn starts running, it checks the virtual memory and throws an exception.

Solution:

Modify Hadoop_Home/etc/Hadoop/yen-site.xml file

Add the following to save and exit:

1 <property>
2 <name>yarn.nodemanager.vmem-pmem-ratio</name>
3 <value>3.0</value>
4 </property>

Problem solved!

DebugAH

How to Solve Your Programmer Error