Tag Archives: mariadb

MySQL: Got error 139 from storage engine [How to Solve]

InnoDB restrictions

1. a table cannot contain more than 1000 columns.
2. The maximum internal key length is 3500 bytes, but MySQL itself limits this to 1024 bytes.
3. except for VARCHAR, BLOB and TEXT columns, the maximum row length is slightly less than half of the database page. That is, the maximum row length is about 8000 bytes. longBLOB and longTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB. innoDB stores the first 768 bytes of VARCHAR, BLOB or TEXT columns in rows, and the rest is stored in scattered pages.
4. The default database page size in InnoDB is 16KB.

Solution 1: replace the engine

ALTER TABLE tableName ENGINE = MyISAM;

How to Solve Navicat open view error

View ‘orgemp.view_allempinfo’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Right click to open the view design, click the top to optimize SQL and check the view SQL.

Check whether the view table does not exist or the field does not exist.

[Solved] MariaDB Add Datas Error: SQL error [1366] [22007]: (cIncorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column

MariaDB add datas error:

SQL error [1366] [22007]: (conn=17) Incorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column `SAOS`.`user`.`userName` at row 1

SQL:

INSERT into `user` values(1, "ZHANGSAN","123456");

The reason for this is the encoding problem. We can check the database character set encoding:

SHOW VARIABLES LIKE 'character%';

Variable_name                           |Value                       |
————————                +—————————-+
character_set_client                    |utf8mb4                     |
character_set_connection                |utf8mb4                     |
character_set_database                  |latin1                      |
character_set_filesystem                |binary                      |
character_set_results                   |utf8mb4                     |
character_set_server                    |latin1                      |
character_set_system                    |utf8                        |
character_sets_dir                      |/usr/share/mariadb/charsets/|

Above is the result displayed. You can see that there are two latin1 encodings. So to change the encoding, find the my.ini file at

whereis my.ini

The results are as follows:

my: /etc/my. cnf

That means this file replaces my Ini, we modify this file.

Add two lines of configuration:

default-character-set=utf8
character-set-server=utf8

Note that this file will tell you that it is read-only when it is modified. You should empower it:

sudo chmod 777  /etc/my.cnf

Then restart MariaDB.

systemctl restart mariadb.service

You need identity authentication. We can just enter the password.

Then view the database character set encoding:

In this way, insert the data just now, and the result is still an error. It seems that you need to continue to modify the configuration file.

[Solved] mariadb Startup Error: Job for mariadb.service failed because the control process exited with error code.

The arm architecture CentOS MariaDB starts with an error job for MariaDB service failed because the control process exited with error code.

As a branch of MySQL, MariaDB is installed differently from mysql, but the specific startup principles are similar

[root@ecs-6ab1 bin]# systemctl start mysql
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

MySQL installed on Kunpeng server (ARM Architecture) suddenly hangs up, thinking it is a problem with the configuration file. Therefore, modify the my.ini file to restart, but this problem will occur when executing systemctl start MySQL command.

    1. this error message can’t be seen in real time. The specific error is just a prompt of startup failure. However, when the installation is OK, the startup failure is generally a problem with the configuration file. Since systemctl start MySQL is a global startup method, it is displayed in/var/log/MariaDB/MariaDB There is no specific error message in the log (mariadb.log has a default configuration in my.ini under etc or in my.cnf.d folder. My configuration is in/etc/my.cnf.d/mariadb-server.cnf, which is true on my server, or there may be something wrong with my global boot configuration, and I didn’t go deep into it)
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

If you can’t see the log, you can start the MySQL startup program to find the location where the program starts

[root@ecs-6ab1 bin]# find /usr /home -name mysqld_safe
/usr/bin/mysqld_safe

After finding the location, you can use mysqld_ Start safe

./mysqld_safe  --user=root --basedir=/var/lib/mysql  --datadir=/var/lib/mysql &

I should also introduce the problem here. After the restart, the loss of the sock leads to a startup failure. After the startup, check the log every time and solve the problem step by step
until there is no error in the log. Use the command line to connect

[root@ecs-6ab1 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Success!

MYSQL8 Startup Error: mysqld_safe error: log-error set to ‘/var/log/mariadb/mariadb.log‘ 

Starting MySQL.2021-11-17T11:04:25.022108Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/data/mysql/data/localhost.localdomain.pid).

After msyql8 compilation and installation, start MySQL with/etc/init.d/mysqld start command. The above errors are reported, and then I rechecked the content of my.cnf, as follows:

[mysqld]
server-id=1
port=3306
datadir=/data/mysql/data
basedir=/usr/local/mysql
socket=/usr/local/mysql/mysql.sock
skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[client]
socket=/usr/local/mysql/mysql.sock


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

There is no problem with the overall configuration of my.cnf, and then the startup fails all the time. Finally, it is found that it is the log directory configured by mysqld_safe was not created

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

So create authorization directly

Note: the path of log error I configured here is under/var/log/MariaDB. The created path is configured according to its own actual path. Be sure not to forget to create mariadb.log, otherwise an error will be reported

mkdir /var/log/mariadb     
touch /var/log/mariadb/mariadb.log

MySQL users and users authorize directories

chown -R mysql:mysql /var/log/mariadb/

Start the MySQL service again

/usr/local/mysql/support-files/mysql.server start

perhaps

/etc/init.d/mysqld start

Other startup methods are implemented according to their actual situation

[Solved] ERROR 1054 (42S22): Unknown column ‘password‘ in ‘field list‘

I don’t remember the MySQL login password. I couldn’t log in. I wanted to modify the MySQL password, but an error was reported
ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password="123" where user="root";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

Looking at the table in detail, it is found that there is no password field in the table, and the password field is changed to authentication_ String, as follows:

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

Solution:

mysql> update user set authentication_string="123" where user="root";        
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,authentication_string from user where user="root";
+------+-----------------------+
| user | authentication_string |
+------+-----------------------+
| root | 123                   |
+------+-----------------------+
1 row in set (0.00 sec)