Tagged: mysql

Remove duplicate after union two collections in Laravel 5

Problem: Having two collections/MySQL query results and combine them using union():

$result1->union($result2);

then want to remove all duplicate on that union result.

Solution: using unique() to remove duplicates. You can add a parameter like unique(‘id’) to specifically remove which tag name. Example:

$result1 = $result1->unique()->values()->all();

Remember to assign it to a variable or else it will remain the same.

“too many connections mysql” error occurred in MariaDB, CentOS 7

Problem: Too many connections error occurred and you can’t access via PHPMyAdmin or command line.

This is because no more connection can be made, so you have to stop MySQL service and change the setting.

Solution: first stop MySQL service, I user MariaDB so the service will be mariadb:

systemctl stop mariadb
systemctl start mariadb

then enter MySQL prompt via MySQL, and enter below query to check current maximum connection:

SHOW VARIABLES LIKE "max_connections";

The default value is 151. You can set a higher one like

SET GLOBAL max_connections = 1000;

3/3/2020: And you should add a timeout parameter to close any connections that no longer used by user. Append parameter in /etc/my.cnf:

sudo vim /etc/my.cnf

Then add wait_timeout = 600 under [mysqld] to timeout any idle connection exceed 10 minutes.

Failed to remote connect to MySQL because of many connection errors;unblock with ‘mysqladmin flush-hosts’

Problem: My window 10 want to remote connect to Linux MySQL server, but popup with the following error:

This is because the command uses host cache tables to exceed the maximum number of connections for a particular host, MySQL server will not able to make new connections.

Solution: enter Linux server command prompt, enter below command:

mysqladmin flush-hosts

or enter MySQL command prompt, enter below query:

FLUSH HOSTS;

Enable remote MySQL/MariaDB connection

Problem: Want an outside notebook/website able to remote connect AWS server’s database.

Solution: First allow all IP to access 3306 in the security group on the AWS portal.

then change my.cnf file, add bind-address:

[mysqld]
    ...
    skip-networking
    ...
    bind-address = <some ip-address>
    ...

the ip address should be 127.0.0.1 or localhost

At last, go to the mysql command-line client by entering mysql, and enter below command to grant access for outside IP address

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD' with grant option;

so that specific user from specific IP can remote connect to this database.

Run backup script to backup MySQL Database using cron

Problem: already add backup command in cron:

mysql -s -r -u root -e ‘show databases’ -N | while read dbname; do mysqldump -u root --complete-insert --single-transaction “$dbname” > /backup/sql/”$dbname”.sql; done

and add dbusername and dbpassword in ~/.my.cnf

[client]
user = dbusername
password = dbpassword

but fail to backup due to ‘INFORMATION_SCHEMA.GLOBAL_VARIABLES’ feature is disabled.

According to suggestion, enable MySQL 5.6 compatibility.

Solution: enter MySQL mode and enter

set global show_compatibility_56=on;

or add below directive to /etc/my.cnf :

show_compatibility_56=ON

Insert empty ” to integer type column cause error in MySQL

Problem: MySQL Error “Incorrect integer value for column.. ” when I want to insert empty string to integer type column.

It is because SQL_MODE being in STRICT mode. Normally it should work before MySQL 5.7

Solution: edit my.cnf file and remove strict mode value

First use

mysql --help | grep /my.cnf | xargs ls

to find my.cnf location

Then edit /etc/my.cnf, change sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES to

sql_mode=""

Then restart MySQL, for CentOS:

service mysqld restart

Can not uninstall MySql completely in CentOS 7

Problem: Want to download MariaDB via yum, but There have conflicts between MariaDB and MySql.

This a problem because these two RDBMS use the same port, simply just use one of them.

Solution: Completely uninstall MySql

First using

 yum list installed | grep mysql

to find out all packages that contain “mysql”

then remove one by one, below commend is to remove the first one(you can keep php-mysqlnd.x86_64 because it is PHP package)

sudo yum remvoe mysql-community-client.x86_64 -y

Then clean MySQL files inside /var/lib/mysql/ and /etc/

sudo rm -r /var/lib/mysql/*
sudo rm -rf /etc/my.cnf

Finally, install MariaDB

sudo yum install MariaDB-server MariaDB-client -y

Chinese Character from mysql turned into a question mark in PHP

Problem: already knew the solution below and follow them but still not work:

DB (phpmyadmin) -- change encoding to utf8_general_ci

Backend -- header(“Content-Type:text/html; charset=utf-8”); and mysqli_set_charset( “utf8”,$connect); added.

frontend -- <meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″>

Solution: Check again if the codes and methods are all correct. This time the
mysqli_set_charset parameter is not correct -- should be mysqli_set_charset($connect,”utf8″);

Close Bitnami banner
Bitnami