ERROR 1214 (HY000) at line 323: The used table type doesn’t support FULLTEXT indexes
Upgrade to 5.6 and use InnoDB Full-Text Search
Category Archives: MySQL
codeigniter + php + mysql – emojis 😉
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/16828197/displaying-utf8-character-on-codeigniter
MySQL – compare tables and generate SQL
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.
mysql – #1241 – Operand should contain 1 column(s)
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
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.sqlRun sed command to find/replace target string
sed -i 's!oldString!newString!g' ./db.sqlReload the database into MySQL
mysql -u user -p databasename < ./db.sqlAs copied from http://stackoverflow.com/questions/11839060/find-and-replace-text-in-the-entire-table-using-a-mysql-query
ERROR 1054 (42S22): Unknown column ‘password_last_changed’ in ‘mysql.user’
Let’s run
mysql_upgrade -u root -pto solve it!
SELECT list is not in GROUP BY clause and contains nonaggregated column
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_SUBSTITUTIONLets restart mysql
sudo service mysql restart
It should work! 😉
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘johnny.category.categoryId’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Let run the following command in mysql…
it worked for me on mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64)Â
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
#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;
