Future me, looks like you forgot your password again and locked yourself out of Craft. You also made your way back to this Stack Exchange question with @chris' perfect answer. Good! And to make your life really easy this time around, I've already generated a password/hash combos for you that works:
MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not).
There is a good overview of the changes here.
Unfortunately, this affects some core queries that Craft uses when fetching elements.
The good news is that this has been fixed already in Craft 3 since we refactored the way that elements ...
Updated 05/03/16: I had a struggle getting this to work with Homestead 5.2. The MySQL error everyone else is seeing, as well as mcrypt error, as mycrypt is not installed by default in Vagrant box laravel/homestead 0.4.1 (Fixed in vagrant box laravel/homestead 0.4.2 — See answer history for 0.4.1 how-to).
So, steps to success:
vagrant ssh into your box
Craft uses Yii's CPasswordHelper::hashPassword which is a Blowfish hash algorithm, using PHP's crypt function with a "blowFishHashCost", which is a setting that can be found through craft()->config->get('blowfishHashCost')
You could use the SecurityService::hashPassword function to generate a new password, echo it, and update it in the DB manually: ...
To fix this I have created a .my.cnf in my home-directory with the following content:
Craft has a pretty solid engine for relational queries, so unless what you want to do is totally outside the box, there's a good chance you can accomplish what you want by using the relatedTo parameter available to the ElementCriteriaModel. Note that the relatedTo entry in the docs pertain to templates and not plugin development per se, but aside from the ...
After playing with this a little further it seems the unixSocket setting is necessary to get this working so I added:
'unixSocket' => getenv('DB_SOCKET')
To config/db.php and then added:
To my .env file. After that, the installation will go through using the CLI. Perhaps there's still an option that ...
For UBUNTU 16.04 LTS
sudo nano my.cnf
add these two lines:
save file and reset your computer.
We have the same problem. MySQL version (Ver 14.14 Distrib 5.7.9, for osx10.11 (x86_64) using EditLine wrapper)
Had problems SQL statement: SQLSTATE: Syntax error or access violation: 1055 Expression and by adding the code at my.cnf file resolved it for me:
Craft 2.6.2957 added support for PHP 7.1
There's a lot going on here, but 1st thing is 1st.
The vagrant box is running PHP 7.1
Craft 2 (and the current latest version of the Yii framework - 1.1.17) are not yet compatible with PHP 7.1.
When the next Yii 1 release is out that addresses the framework compatibility issues, we'll get Craft 2 up and ...
When you create a database in MySQL in Craft 2, you can set the default charset and collation, so that any SQL statements that get executed that don't explicitly specify what charset/collation to use, MySQL will fall back on those.
Craft 2 will always explicitly set what to use based on what the charset and collation database config settings are set to in (...
As of the next Craft 2.6 release (post 2.6.2945), Craft has exposed an attributes database config setting that you can pass arbitrary PDO attribute key => value pairs into.
So, assuming your MySQL box is setup properly to use SSL and assuming the database user that Craft is using to connect to the box can connect over SSL, then you can do ...
If you don't want to mess about trying to insert code in the Craft/Yii stack, v3 just calls password_hash():
password_hash($your_password_string, PASSWORD_DEFAULT, ['cost' => 13]);
As a possibly helpful addendum (I was just trying to run the site locally and needed to log in); if you find you still can't login after changing the password via the ...
Craft currently won't operate properly if you have GTID enabled because GTID enforces that every table involved in a SQL statements in one transaction have to utilize the same storage engine.
While virtually all of Craft's tables use InnoDB as the storage engine, the craft_searchindex table is still on MyISAM, because InnoDB didn't add full-text support ...
MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not). Either modify the existing mysql queries to be compatible to the new mysql version or make mysql behave as in previous versions , by executing the below queries on the mysql .
SET sql_mode = ''; // current client
SET GLOBAL sql_mode = '';...
The proper fixes have already been answered here; but I'd like to note that another fix is to use MariaDB instead of MySQL. It's a drop-in replacement for it, it's more performant for a number of metrics, and it's created by the original author of MySQL.
Give it a go; you won't regret it.
Lots of smart people have tried to solve this problem and it's a tough one to solve, not just in Craft land but WP, and others. And because of Craft's flexible nature, a change in one table usually means many more updates from others.
Arguably the most reliable way I've seen it done is to use a "top-down" approach. Make the content changes in the production ...
Thanks to @carlcs for pointers managed to get this working with the following:
$criteria = craft()->elements->getCriteria(ElementType::User);
$criteria->limit = NULL;
$query = craft()->elements->buildElementsQuery($criteria);
$params = array(
':interest' => '%"8948"%',
$query->andWhere("field_interests LIKE :interest", $params);
okay, guess I got it.
as soon as I kick the Preparse Field-plugin, everything works smooth. but i guess it's not the plugin that causes the trouble, it's the way craft is writing its logs for deprecated code?
another solution: RTFM..
in Preparse Field-plugin: activate "Disable all Preparse field columns on all element indices".
I faced this problem when I upgraded MySQL to version >= 5.7.5 and I'v fixed it by following these steps:
SSH into your server.
Add sql-mode="" to your MySQL config:
echo -e '[mysqld]\nsql-mode=""' | tee --append /etc/mysql/conf.d/craft.cnf > /dev/null
Restart MySQL service:
service mysql restart
Note: you might need to add sudo to those commands.
You're running into a MySQL/InnoDB server limitation that commonly occurs with a significant number of columns in a single table.
Fortunately, there are several documented workarounds for it:
Turns out this is a bug as of Craft 2.6.2931 with the ColumnType::PK enum
in a record's attribute type definition.
Pretty sure it'd be a bug in < MySQL 5.7 because it's invalid SQL syntax as well unless they're just more forgiving of it there.
This is a non-issue in the upcoming Craft 3 release (the concept of attributes no longer exists and plugins ...
You can see each SQL query that is run for a page by turning devMode on and opening your console to read the 'Profiling Summary Report'.
You can see a total query count and each query beings with system.db.CDbCommand.query.
I'd advise maybe isolating the twig code on its own template to avoid having to work out which query was a result of the twig code you'...
In particular you're looking for the Profiling Summary Report.
Also, you can view the Craft Logs at /admin/utils/logs, when you are logged into the Control Panel.
You need to tweak your MySQL 5.7.5+ config for it to play nicely with Craft.
MySQL 5.7.5 changed the behaviour of the GROUP_BY statement, affecting some of the core Craft queries.
The preferred solution is to add the following to your /etc/mysql/conf.d/sql_mode.cnf file: