Configure a remote connection to mysql
MySQL>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
MySQL>FLUSH PRIVILEGES;
When you connect to mysql remotely
mysql -h127.0.0.1 -u root -p
Error:
ERROR 2003 (HY000): Can’t connect to MySQL Server on ‘127.0.0.1’ (111)
Solutions:
sudo vi /etc/mysql/my.cnf
Delete the following line.
#bind-address = 127.0.0.1
sudo /etc/init.d/mysql restart
Here is a solution, but I changed a lot of parameters before I found this one.
Add in my.conf
[mysqld]
innodb_strict_mode = 0The result is not wrong, the big guy’s explanation for this is
Note: The innodb_strict_mode setting affects the handling of syntax errors in the CREATE TABLE, ALTER TABLE, and CREATE INDEX statements. Innodb_strict_mode also enables record size checking, so INSERT or UPDATE never fails because the record is too large for the selected page size.
And then I looked up the differences between these operations
1, CREATE INDEX must provide the INDEX name, for ALTER TABLE, will be automatically created, if you do not provide;
2, CREATE INDEX can only CREATE one INDEX at a time, ALTER TABLE can CREATE more than one
3, only ALTER TABLE can CREATE the primary key,
In case there are more than one parameter to avoid error, I post my modified parameters
max_allowed_packet = 256M #修改前是 16M
innodb_buffer_pool_size = 512M #修改前 54
innodb_log_file_size = 30M
innodb_log_buffer_size = 32M
#添加的
innodb_file_per_table = 1
innodb_large_prefix=1
innodb_file_format = Barracuda
innodb_strict_mode = 0
Then restart Mariadb and you are done.
systemctl stop mysqld.service2, modify the profile to log in without password
[HTML] view plain copy
Add it at the end
[HTML] view plain copy
3. Start mysql
[HTML] view plain copy
[HTML] view plain copy
5, modify the password, mySQL5.7 use this syntax
[HTML] view plain copy
The
[HTML] view plain copy
[HTML] view plain copy
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
In fact, after installing mysql by default or in the log to generate a default password /var/log/mysqld.log
Login to mysql to reset the default password
[HTML] view plain copy
If the password level does not match the default level requirement, it will be reported
[HTML] view plain copy
You need to change the level and the minimum default password number
[HTML] view plain copy
The
# # # # # # # # # # # # # # # # # # #
Error 1130 on remote connection
[HTML] view plain copy
<?php
$mysqli=mysqli_connect('localhost','root','123','test');
if(mysqli_connect_errno()){
printf("Connect failed:%s\n",mysqli_connect_error());
exit();
}
else
{
$sql="CREATE TABLE testTable(
id int not null primary key auto_increment,
testField varchar(20)
)";
$res=mysql_query($mysql,$sql); //I wrote $mysqli as $mysql, so it doesn't operate on the specified database.
var_dump($res);
echo "<br/>";
if($res===true){
echo "Table testTable successfully created.";
}
else{
printf("Could not create table:%s\n",mysqli_error($mysqli));
}
mysqli_close($mysqli);
}
?>
Output result: (mysqli_error() returns no error message)
The first argument to mysqli_query() should be $mysqli, but I wrote $mysql, which doesn’t exist. So mysqli_query() doesn’t actually find it
Specified database, so no table build operation is performed. So mysqli_error() does not return a value.
Since my VIM configuration is not complete, this error should be detectable by IDE syntax correction.
So an operation error to the database, and mysqli_error() does not return a value, could be a parameter error passed to mysqli_query().
Mysqli_error () returns an error from the last database operation. If the first parameter was written incorrectly, no operation was performed to the specified database
Error message.
mysql> alter table sbtest1 drop column cityname2;
ERROR 1114 (HY000): The table 'sbtest1' is full
mysql>
Error reported while doing sysbench stress test.
Reason.
It is known that the size of the memory table exceeds the specified range. After reviewing the default value of both is 16M.
Need to set tmp_table_size greater than or equal tomax_heap_table_size。
mysql> show variables like '%table_size%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size | 16777216 |
+---------------------+----------+
2 rows in set (0.00 sec)
Neither of them support dynamic modification, and the modification will not take effect.
[mysqld]
max_heap_table_size =32M
tmp_table_size =64M
# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
mysql> show variables like '%table_size%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 33554432 |
| tmp_table_size | 67108864 |
+---------------------+----------+
2 rows in set (0.01 sec)
You can see the modified parameters take effect after the reboot.
Then just perform the operation to delete the field.In mysql, ERROR 1406 (22001):data too long for column
: DEFAULT CHARSET=utf8 is added at the end of the created table
drop table if exists sys_user;
create table sys_user (
user_id bigint(20) not null auto_increment comment 'User ID',
dept_id bigint(20) default null comment 'Department ID',
login_name varchar(30) not null comment 'login account',
user_name varchar(30) not null comment 'user nickname',
user_type varchar(2) default '00' comment 'user type (00 system user)',
email varchar(50) default '' comment 'user email',
phonenumber varchar(11) default '' comment 'phone number',
sex char(1) default '0' comment 'User gender (0 male 1 female 2 unknown)',
avatar varchar(100) default '' comment 'avatar path',
password varchar(50) default '' comment 'password',
salt varchar(20) default '' comment 'salt encryption',
status char(1) default '0' comment 'Account status (0 normal 1 disabled)',
del_flag char(1) default '0' comment 'Delete flag (0 means present 2 means deleted)',
login_ip varchar(50) default '' comment 'last login ip', login_date datetime comment 'last login',
create_by varchar(64) default '' comment 'creator', create_time datetime comment 'create time',
update_by varchar(64) default '' comment 'updater', update_time datetime comment 'update time',
comment varchar(500) default null comment 'Remarks', primary key (user_id)
) engine=innodb auto_increment=100 comment = 'user information table' DEFAULT CHARSET=utf8;
insert into sys_user values(1, 103, 'admin', 'admin', '00', '[email protected]', '15888888888', '1', '', '123456', '111111', '0', '0', '127.0.0.1', '2018-03-16 11-33-00', 'admin', '2018-03-16 11-33-00', 'ry', '2018-03-16 11-33-00', 'ADMIN');
insert into sys_user values(2, 105, 'ry', 'admin', '00', '[email protected]', '15666666666', '1', '', '123456', '222222', '0', '0', '127.0.0.1', '2018-03-16 11-33-00', 'admin', '2018-03-16 11-33-00', 'ry', '2018-03-16 11-33-00', 'TEST');
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL COMMENT 'product name',
`price` float(10,3) NOT NULL,
`description` varchar(20) DEFAULT NULL,
`count` int(11) NOT NULL DEFAULT '0',
`sid` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_index` (`id`) USING HASH,
UNIQUE KEY `sid_index` (`sid`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `sealer`;
CREATE TABLE `sealer` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`city` varchar(255) DEFAULT NULL,
`created_time` datetime DEFAULT NULL,
`updated_time` datetime DEFAULT NULL,
`level` int(11) NOT NULL DEFAULT '0',
`description` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_index_1` (`id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;Next, we need to associate product.SID to sealer. Id for the parent-child table primary foreign key association.
2. Encountering a mistake
The SQL and error message used to create the foreign key is as follows :
alter table `product' add CONSTRAINT `sid_ref` FOREIGN KEY (`sid`) REFERENCES `sealer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTIONThe error message encountered is as follows:
Unable to insert foreign key constraint correctly.
3. Problem analysis
The primary foreign key is more about the primary key of a table being associated with a column of a child table, which requires the same data type and attributes. Will this be a problem?
Requirements: Same data types and constraints
Unsigned. The character length of the number is not consistent.
4. Solutions
Modify the data type in the product.sid, add unsigned and the length of the field, and set it to the same.
5. To summarize
The problem of 1215 is due to the inconsistency of data types between the primary and foreign keys. Similar problems in the future can be handled accordingly.
Problem: Normally, executing the Rebuild Index will be completed quickly, but today I encountered the Job of Rebuild Index Running all the time. Manually Rebuild, again failing, report “Lock Request Time out Period Exceeded. (Microsoft SQL Server, Error: 1222)”, as shown below:
Solution:
Look at the SPID and SQL Text of the current Running, especially for long-running ones, find the SPID associated with the tables that execute Rebuild Index, and KILL it. What I currently have is an SQL that has been running for hours, and after killing the SPID, it can Rebuild Index properly.
SELECT r.session_id, r.status, r.start_time, r.command, s.text, r.wait_time, r.cpu_time,
r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE R.STATUS='running'In addition, you can check the information related to Lock:
select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not nullView the specified SPID
USE master;
GO
EXEC sp_who '267' --process_id;
GO For more detailed information:
DECLARE @SessionID INT=63
SELECT
SPID = er.session_id
,Status = ses.status
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName =DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName =OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
AND @SessionID IS NULL OR er.session_id = @SessionID
ORDER BY
er.blocking_session_id DESC
,er.session_id
The second method is modified in /etc/my.cnf. This method will not revert to the default value after MySQL is restarted. I have extended the timeout, but the program still ends at 30s.This intention to modify a user’s password, the command found on the Internet is as follows
| 1 | mysql> Update user set password=password(" new password ") where user= "username"; |
ERROR 1054(42S22) Unknown column ‘password’ in ‘field List’
The reason for the error is that the password field is no longer in mysql database in version 5.7, so the password field is changed to AUTHENTICation_string
So use the command:
>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Database changed
mysql> select User from user; # Here is the query user command
+-----------+
| User |
+-----------+
| ******* |
| mysql.sys |
| root |
+-----------+
3 rows in set (0.00 sec)
mysql> update user set password=password("*******") where user="*******"; # Change password report error
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******'; # Change password successfully
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges; # Effective immediately
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
n>mysql -u ******* -p # Login successfully with this user.
Enter password: ********
…………………………
mysql>
Error reason: the default database
is not selected
Solution: Double-click on one of the databases in the SCHEMAS list.
Run:
docker run --name mysql -p 3306:3306 -v /root/bo/data/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -it wzj/mysql
docker: Error response from daemon: Conflict. The container name "/mysql" is already in use by container "3131fbf478c984cf393b65dde14342af4e3331f9f5641a4821b426cdca20abba". You have to remove (or rename) that container to be able to reuse that name.
Solution:
check docker ps-a
saw that the docker container run before has not quit, resulting in the container name situation.
Using the following command, delete the container,
docker rm fb087642b497(Mirror ID)
docker rm 7b739cc26085Execute the following command:
docker restart nginx
Restarts