When building a library mysql prompts the solution to the problem of Specified key was too long key length is 1000 bytes
- 2020-05-13 03:41:43
- OfStack
Index field length is too long,
1. Modify the field length
2. Modify mysql's default storage engine
Add default-storage-engine =INNODB under [mysqld] of /etc/mysql/ my.cnf
However, the INNODB engine was clearly indicated when building the library
Sql code
Check the current engine
There was no InnoDB
Check to see if dynamic loading is supported. The important thing is the have_dynamic_loading line. If it is YES, proceed
Installing a plug-in
It is the first time for Mysql to build a library. There is still a difference between Mysql and Oracle. The following is a brief introduction to the process of building a library for Linux Mysql.
#mysql
> CREATE USER 'bob'@'%' IDENTIFIED BY '123456'; // create user bob.
> GRANT ALL PRIVILEGES ON *.* TO 'bob'@'localhost' IDENTIFIED BY '123456';
// set bob to log in locally and give all permissions to bob, because bob is the administrator!
> GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%' IDENTIFIED BY '123456';
// the user address can be localhost or ip, machine name, domain name. You can also use '%' to connect from any address.
> FLUSH PRIVILEGES; // refresh authorization
> CREATE DATABASE travel CHARACTER SET utf8 COLLATE utf8_general_ci; // create the database travel and set it to utf8.
> quit
#mysql -uroot -p travel < travel201204100.sql // for convenience, I directly imported a piece of data. The password of root is empty by default, so it is directly followed by the database name.
Enter # mysql-ubob-p 123456
> SHOW DATABASES; // view the database
> select user host, password from mysql. user; //bob log in to see all existing users
> \S or SELECT CURRENT_USER(); // view the current user
> use travel;
> show tables;
> show grants; // check your permissions
> show grants for dba @ localhost; // view the permissions of dba
> revoke all on *.* from dba @localhost; // remove permissions from dba
> update user set password=password(" password ") where user='root' // modify root password
mysqladmin-uroot-p old password password new password // change root password
mysql-ubob-pbob192.168.19.182 travel // remote login to the Mysql database at 182
1. Modify the field length
2. Modify mysql's default storage engine
Add default-storage-engine =INNODB under [mysqld] of /etc/mysql/ my.cnf
However, the INNODB engine was clearly indicated when building the library
Sql code
CREATE TABLE `acs` (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Check the current engine
mysql> show engines;
There was no InnoDB
Check to see if dynamic loading is supported. The important thing is the have_dynamic_loading line. If it is YES, proceed
mysql>show variables like "have_%";
Installing a plug-in
mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb.so';
mysql> show engines;
It is the first time for Mysql to build a library. There is still a difference between Mysql and Oracle. The following is a brief introduction to the process of building a library for Linux Mysql.
#mysql
> CREATE USER 'bob'@'%' IDENTIFIED BY '123456'; // create user bob.
> GRANT ALL PRIVILEGES ON *.* TO 'bob'@'localhost' IDENTIFIED BY '123456';
// set bob to log in locally and give all permissions to bob, because bob is the administrator!
> GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%' IDENTIFIED BY '123456';
// the user address can be localhost or ip, machine name, domain name. You can also use '%' to connect from any address.
> FLUSH PRIVILEGES; // refresh authorization
> CREATE DATABASE travel CHARACTER SET utf8 COLLATE utf8_general_ci; // create the database travel and set it to utf8.
> quit
#mysql -uroot -p travel < travel201204100.sql // for convenience, I directly imported a piece of data. The password of root is empty by default, so it is directly followed by the database name.
Enter # mysql-ubob-p 123456
> SHOW DATABASES; // view the database
> select user host, password from mysql. user; //bob log in to see all existing users
> \S or SELECT CURRENT_USER(); // view the current user
> use travel;
> show tables;
> show grants; // check your permissions
> show grants for dba @ localhost; // view the permissions of dba
> revoke all on *.* from dba @localhost; // remove permissions from dba
> update user set password=password(" password ") where user='root' // modify root password
mysqladmin-uroot-p old password password new password // change root password
mysql-ubob-pbob192.168.19.182 travel // remote login to the Mysql database at 182