Let’s run
mysql_upgrade -u root -pto solve it!
Tag Archives: mysql
#1133 – Can’t find any matching row in the user table (user table)
SET PASSWORD FOR 'johnny'@'localhost' = PASSWORD('somepassword69')Mensagens do MySQL :
#1133 - Can't find any matching row in the user table (user table)
How to solve it? Before the SET PASSWORD try to run FLUSH PREVILEGES.
FLUSH PREVILEGES;SET PASSWORD FOR 'johnny'@'localhost' = PASSWORD('somepassword69'); FLUSH PREVILEGES;
MySQL – Your password does not satisfy the current policy requirements.
Lets check our current policy rules
SHOW VARIABLES LIKE 'validate_password%';
This is how we can change this values…
SET GLOBAL validate_password_length = 7;
SET GLOBAL validate_password_number_count = 0;
SET GLOBAL validate_password_mixed_case_count = 0;
SET GLOBAL validate_password_special_char_count = 0;
mysql – Can’t create/write to file ‘/tmp/
root@colo18:~# mysqldump -u root --all-databases > colo18.sql mysqldump: Couldn't execute 'show fields from `crongal`': Can't create/write to file '/tmp/#sql_b8c_0.MYI' (Errcode: 13) (1)
Lets set the proper permissions to /temp
sudo chmod 1777 /tmpIt will solve the issue.
Resources:
MySQL vs/and/plus/more JSON
Here are a few things that we can use / read about MySQL and JSON.
Small benchmark tests of reading 20000
Krasimir Tsonev has made a small benchmark tests of reading 20000 from a MySQL db and from a JSON file.
I’m gonna just put the *end* results. You can read all the article on Krasimir’s blog about MySQL vs JSON file data storing benchmark results.
MySQL
ab -n 30 -c 30 http://localhost/mqsql.php
Concurrency Level: 30
Time taken for tests: 30.518 seconds
JSON
ab -n 30 -c 30 http://localhost/json.php
Concurrency Level: 30
Time taken for tests: 3.384 secondsMySQL query to JSON
| username | |
| mike | [email protected] |
| jane | [email protected] |
| stan | [email protected] |
The MySQL query
SELECT
CONCAT("[",
GROUP_CONCAT(
CONCAT("{username:'",username,"'"),
CONCAT(",email:'",email),"'}")
)
,"]")
AS json FROM users;The JSON result
[
{username:'mike',email:'[email protected]'},
{username:'jane',email:'[email protected]'},
{username:'stan',email:'[email protected]'}
]
Got this from http://www.thomasfrank.se/mysql_to_json.html.
Simply backups under Linux
Backup a folder
tar -cvpzf /BackupDirectory/backupfilename.tar.gz /ImportantData/directory/path
c = Create
v = Verbose mode
p = Preserving Files and Directory Permissions.
z = This will tell tar that compress the files further to reduce the size of tar file.
f = It is allows to tar get file name.
Backup a MySQL database
mysqldump --opt -u [username] -p[password] [dbname] > [dbname.sql]
Backup all MySQL databases
mysqldump -u root -p --all-databases > all_databases.sql
“MySQL server has gone away” with CodeIgniter and MySQL
I’v stopped using CodeIgniter for 7 months, but I maintaining a product that was developed under CI.
Today I’v got a error saying “MySQL server has gone away” after a long time PHP execution.
I’v managed to solve it by, on the model that was getting me the error, *reconnect* to the database .
Before the *command* i’v added $this->db->reconnect();
$this->db->reconnect();
$this->db->insert('photoGallery', $data);
return $this->db->insert_id();
Hope it helps anyone out there.
MySQL: delete a row ignoring foreign key constraint
Cannot delete or update a parent row: a foreign key constraint fails DELETE IGNORE FROM `database`.`table` WHERE `table`.`tableId` = 16 LIMIT 1;
The IGNORE keyword causes MySQL to ignore all errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of IGNORE are returned as warnings.
WordPress – find and replace
I got some wordpress blogs (20+) running on some servers, and from time to time, i need to make some FIND&REPLACE on my posts…
This is the simple way! Directly on mysql.
UPDATE wp_posts SET post_content = REPLACE ( post_content, 'Item to replace here', 'Replacement text here');
Please note the for security reasons the prefix wp_ is diferent… if you play by the rules you will need to see witch is is… wp-config.php has it.
mysql> UPDATE wpspt_posts SET post_content = REPLACE ( -> post_content, -> 'static.mycdn.com/banners/', -> 'static.mycdn.com/bnnrs/'); Query OK, 1477 rows affected (0.42 sec) Rows matched: 10795 Changed: 1477 Warnings: 0
This is a small hack to ABP – Ad Blocker Plus…
Horray!