Category Archives: MySQL

phpmyadmin – Your password does not satisfy the current policy requirements

MySQL

 mysqli_query(): (HY000/1819): Your password does not satisfy the current policy requirements

I generally use the PHPMyAdmin to generate the passwords,
what I’v done in this case, but I got the following error….

SHOW VARIABLES LIKE 'validate_password%';
Variable_nameValue
validate_password_check_user_nameOFF
validate_password_dictionary_file
validate_password_length8
validate_password_mixed_case_count1
validate_password_number_count1
validate_password_policyMEDIUM
validate_password_special_char_count1
SET GLOBAL validate_password_policy = 0;

#1366 – Incorrect integer value: ” for column ‘upd_iid’ at row 1

MySQL

While I was switching a legacy site from servers… some SQLs weren’t working…

#1366 - Incorrect integer value: '' for column 'upd_iid' at row 1

This worked for me…
I’v edited my /etc/mysql/my.cnf  and comment the original “sql_mode” and added this one

sql_mode = ""

Save and restart mysql!

https://stackoverflow.com/questions/14762904/incorrect-integer-value-for-column-id-at-row-1/53154716#53154716

live MySQL queries

CentOS, MySQL

On your mysql console…

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> SET GLOBAL general_log = 'ON';

Then!

tail -f -n200 /var/run/mysqld/mysqld.log

 

After debugging… don’t forget to turn it OFF!

mysql> SET GLOBAL general_log = 'OFF';

 

 

codeigniter + php + mysql – emojis 😉

CodeIgniter, MySQL, PHP

This is how…

On mysql set your table(s)’s filed(s) to utf8mb4_general_ci.

codeigniter config/database.php
$db['default']['char_set'] = 'utf8mb4';
$db['default']['dbcollat'] = 'utf8mb4_general_ci';
Resources

https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc

https://stackoverflow.com/questions/16828197/displaying-utf8-character-on-codeigniter

MySQL – compare tables and generate SQL

MySQL, Utilities

PHP MySql compare table structure – ONLINE!

This site can compare two MySQL database table structures and generate the necessary SQL statements to update the second table to be identical to the first.
It compares the table creation SQL statements of two MySQL databases as outputted from the mysqldump command or phpmyadmin.
The site determines what changed from one database to the other and generates schema alteration SQL statements to create, drop and alter tables that should be applied to one database to make it have the same schema of the other.

http://www.infoom.se/compare-mysql-online/

mysql – #1241 – Operand should contain 1 column(s)

MySQL

So!, I was/am writing a subquery to get a value to rating_stars… and I got this (beginner) error…

mysql – #1241 – Operand should contain 1 column(s)

(SELECT value, (sum(value)/count(value)) FROM model_ratings WHERE modelId = id) AS rating_stars

Subquery should return one column, whatever corresponds to rating_stars.

(...) (SELECT (sum(value)/count(value)) FROM model_ratings WHERE modelId = id) AS rating_stars (...)

mysql – search & replace HUGE databases

MySQL

So!, I have with almost 3Gb… and I need to make a search & replace…
This looks the best way to me…

EXPORT FROM MYSQL > LINUX SEARCH & REPLACE > IMPORT TO MYSQL

Dump database to text file

mysqldump -u user -p databasename > ./db.sql

Run sed command to find/replace target string

sed -i 's!oldString!newString!g' ./db.sql

Reload the database into MySQL

mysql -u user -p databasename < ./db.sql

As copied from http://stackoverflow.com/questions/11839060/find-and-replace-text-in-the-entire-table-using-a-mysql-query

SELECT list is not in GROUP BY clause and contains nonaggregated column

MySQL

 

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ” which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

Lets edit my.cnf

sudo nano /etc/mysql/my.cnf

and add the following lines to it, save and exit nano….

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Lets restart mysql

sudo service mysql restart

It should work! 😉