Questions tagged [mysql]
The mysql tag has no usage guidance.
102
questions
69
votes
12answers
142k views
Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by
I am getting the following error:
Internal Server Error CDbCommand failed to execute the SQL statement:
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #18
of SELECT list is ...
28
votes
3answers
12k views
Reset password with MySQL
Is there a way to reset an admin account's password by modifying the MySQL directly?
I've found the craft_users table where the hashed password is stored but I don't know how to hash a string so that ...
9
votes
2answers
2k views
Craft CLI RC1 Connectivity Issues with MAMP Pro
I've been trying to run the new Craft CLI for the last couple of versions without any success. I figured maybe since we weren't at RC1 yet that it wasn't fully working. After seeing Ryan's video about ...
5
votes
1answer
460 views
Craft CMS with Google Cloud SQL as backend database
Did anybody successfully install Craft on Google Cloud SQL? Or on any MySQL engine with activated replication and active GTID mode?
4
votes
2answers
2k views
Dump SQL query in Twig template
Is possible to dump a query made on a twig template for a statement like this one for example:
craft.entries.section('homepage')
Can we some how see the actual SQL query ?
4
votes
1answer
561 views
4
votes
1answer
92 views
What SQL query can I run to remove all users assigned to a particular group?
So I have a user group containing a few thousand users and I want to remove all of those users with a SQL script.
These users don't have access to the control panel and so don't have any Entries ...
3
votes
1answer
1k views
MySQL Collation: utf8mb4_unicode_ci (emojis)
The Craft docs call for using utf8_unicode_ci for MySQL collation. MySQL 5.5.3 introduced support for utf8mb4_unicode_ci, which, I believe, is required to support the storage of emojis.
Does anyone ...
3
votes
1answer
1k views
How do I build a LIKE query with DbCommand/Query Builder?
I'm trying to construct a fairly simple LIKE query using the Query Builder, and I'm struggling. Here's the SQL that I want:
SELECT `id` FROM `craft_somecustomtable` WHERE ((`somecolumn` LIKE '%foo%') ...
3
votes
1answer
44 views
Workflow for re-using data structures and setups across projects?
I am wondering if there is a way to re-use pre defined field types and image crops over various Craft projects. I currently in the process of e.g. manually defining 20 different image crops I already ...
3
votes
1answer
519 views
Poor performance on midsized site / Queries without index?
I'm running a mid sized website (couple hundred articles, between 30-50k visits per day) on a Digital Ocean 16GB / 8 Cores web server and I have two questions:
Most of the time the website runs ...
3
votes
2answers
104 views
Migrate Craft CMS installation from PgSQL to MySQL - any hints?
We need to migrate a Craft installation with a pgsql dump to MySQL.
Any hints on the procedure?
Thanks,
Matthias
2
votes
2answers
589 views
See the SQL query that gets executed when saving entry
I'm creating a pretty specific entry with some fields. And I want to create the from a third party app. For that, I'd like to see the SQL query that get executed.
I've tried it with events, entries....
2
votes
1answer
195 views
How would I write this differently to make it compatible with MYSQL 5.7
There is an issue with using unique => true in defining an attribute as a primary key when using MYSQL 5.7.*.
protected function defineAttributes()
{
return array(
'eid' => array(...
2
votes
1answer
820 views
createCommand sql query not working in plugin
Hi I have a plugin which I am running an sql statement from.
$users = craft()->db->createCommand("SELECT craft_users.id as userId, craft_users.firstName, craft_users.lastName, craft_users.email ...
2
votes
2answers
177 views
How to UNION Craft blog articles array with Social Media plugin (non craft table)
I'm looking for a way to merge blog article entries with social media entries (non craft table). The idea is to output both entry types together from the same array, this will allow me to order all ...
2
votes
1answer
143 views
Is there a way to convert a Craft MySQL database to PostgreSQL?
I have an existing Craft installation using MySQL and I've only recently realized the error of my ways and would like to convert the database to PostgreSQL.
Is this possible?
2
votes
1answer
220 views
Can't backup DB locally with MAMP PRO 5.5.1
I have followed the instructions for this article (https://craftcms.com/guides/database-backups-in-craft-3-with-mamp) to get local backups working with MAMP. However, when I attempt a backup, I get ...
2
votes
1answer
270 views
Craft 3 Ubuntu installation error via composer setup
After running composer and then initiating craft setup I keep getting this error:
Username: [admin]
Email: abc@test.com
Password:
Confirm:
Site name: test
Site URL: test.com
Site language: [en-US]
...
2
votes
1answer
246 views
MySQL Error 1064 when trying to create a Matrix
Running Craft Version 2.6.2994 on an EC2 instance that has PHP 7.0.21, and MySQL v 5.6.37 installed. When I try to create a new Matrix, I get the following error when saving.
When I test this on my ...
2
votes
1answer
514 views
Entry view count in the last 24 hours
I'm working on a typical craft blog with articles. I know there is a plugin that was used to teach craft plugin development that tracks entry visit counts here:
https://straightupcraft.com/craft-...
2
votes
1answer
57 views
Will craft attempt to reconnect to MySQL if the connection drops?
We have a customer using a cheap managed hosting provider, and their MySQL server very aggressively times out and drops the connection.
I wonder if this could be causing us troubles as outlined here:...
2
votes
1answer
131 views
Error on import to Craft production db
I just copied my database across from local server to production server. I used Craft's control panel to create a backup. Then I used Sequel Pro to import this into the live server.
I got this error:
...
2
votes
1answer
578 views
Problem connecting to MySQL db on installing Craft 3
Having installed Craft 3 using Composer, I'm attempting to run the Craft setup. Everytime I try, I get the following error:
Testing database credentials... failed: SQLSTATE[HY000] [1044] Access ...
2
votes
1answer
294 views
DB back-up gives syntax errors on import in new DB
I had this issue on two different sites on two different occasions and hosts.
When creating a database back-up through the Craft CMS admin and importing that SQL file into a new database through PhP ...
2
votes
1answer
194 views
Error when creating a Matrix or installing a Plugin
We have 2 computers with a new Craft Pro trial installation. The files are exactly the same on both computers. We both use the same DB with the same db.php config settings. We can both see the site ...
1
vote
1answer
738 views
Filtering entries based on category?
I want to filter data based on category, I would prefer to do this using a join statement, but I can't figure out how an entry is related to its category. I had the same problem with matrix field, but ...
1
vote
1answer
6k views
PHP notice unserialize(): Unexpected end of serialized data
Ok so this is an incredibly wierd one and I have no idea how to explain the problem in just one sentence so I just put the error message as the title.
So I am building a site with craft...in fact its ...
1
vote
2answers
147 views
Get last item added to cart
Does anyone know a nice way to get the last lineItem(s) added to a cart in craft commerce via twig template? Example use case, an 'added to cart' confirmation page. I know for this you could query ...
1
vote
2answers
273 views
Find entries that use a specific block in their Matrix field?
Suppose a Mtrix field named MyMatrixField has three blocks called Text, Images, and Buttons.
What MySQL query would list the section and title for all entries whose MyMatrixField field has a Buttons ...
1
vote
1answer
922 views
Randomly getting the “Integrity constraint violation” sql error on saving entry
Randomly getting the "Integrity constraint violation" sql error on saving entry from backend. There no apparent cause of this at least not that I can see, because the same post saves without errors ...
1
vote
1answer
2k views
MySql 5.7 error - field doesn't have a default value
We've just switched server for one of our sites and I am now encountering a MySql error on one of our plugins. The MySql version on the new server is 5.7 and throws an error as a field has no default ...
1
vote
2answers
257 views
Importing a Craft database error
I dumped from Craft my database from server A and am trying to install it to a fresh copy of Craft on server B.
I've got Craft up and running, and when I try importing my Craft SQL file in PHPmyAdmin,...
1
vote
3answers
911 views
How do I get past this MySQL error installing Craft 3 using CLI
I am using https://box.scotch.io/ (vagrant).
I'm trying to install Craft 3 with the cli, but can't get past these errors:
Connection refused
No such file or directory
One or the other error shows ...
1
vote
2answers
154 views
Order entries, exclude 'the' from order criteria
I know this is partly a Twig question, but I've poked around quite a bit here, in Twig docs, Craft docs, and cannot find the right syntax.
I have a bunch of entries with titles like:
Hotel A
Grand ...
1
vote
1answer
62 views
Tracking SQL queries from CP changes
We are developing over multiple environments and production stages, and we would like to be able to track the SQL changes that happen when we make a change to entries etc. on the CP. The changes don't ...
1
vote
1answer
52 views
entry-overview superslow after update?
we have a little problem with craft after updating to the new version.
the entry-overview (foo.com/craft-admin/entries/) seems to load much slower than before when I click an entry (up to 10 seconds)
...
1
vote
1answer
11k views
Internal Server Error: SQLSTATE[42000] 1118 Row size too large
I have a very large entry type in craft with quite a few fields. I've been updating a single record on a regular basis but upon adding a few more paragraphs of rich text in a field, I received this ...
1
vote
1answer
30 views
MySQL 5.6 backup to MySQL 5.7… what to do
I just changed my db server, and I managed to import a db dump to the new system.
What troubles me is that, once done, I noticed I have now MySQL 5.7.32, while the previous database server was 5.6.48.
...
1
vote
1answer
72 views
Craft CMS Entries Disappearing
I've setup a Craft 3 site for a client and we are seeing the strangest thing where pages are just disappearing. Best we can tell no-one has deleted the entry, but we get a 404 on the front-end and it ...
1
vote
1answer
208 views
Local DB SQL 'CDbException' Errors on Craft 2 site
Just tried to get a local site working again but getting all sorts of issues.
I have updated the website online and downloaded the database, installed locally. I have also the craft/app folder to my ...
1
vote
2answers
61 views
Upload failed. The error message was: "SQLSTATE[23000]: Integrity constant violation:
I'm getting this strange error when I'm trying to upload an .jpg in the assets.
Env: Localhost
Craft version 3.1.8
MAMP:
- PHP(7.2.7)
- MySQL: 5.7.21
Upload failed. The error message was: "...
1
vote
1answer
534 views
How to connect to a Remote DB using CraftCMS 3.1+
I tried using Andrew Welch's craft-connect plugin, but the plugin was throwing an error on the REMOTE_DB_SERVER variable. I commented the variable out in the .env file, but I still could not connect. ...
1
vote
1answer
171 views
# of MySQL connections
Assume my website (developed using Craft, of course) is expected to have 1000 concurrent access. How many MySQL db connections would be required to open? Does Craft control this or does it depend on ...
1
vote
1answer
61 views
What could the possible causes be for a mismatch between `craft_fields` and `craft_fieldlayoutfields` MySQL tables?
I'm debugging an Internal Server Error on a site developed by, well, not me. :p The site has an absolutely enormous Matrix field called "Modules", and the blocks in said field have so many fields that ...
1
vote
1answer
157 views
Joining sections (Channel) to query based on a related item attribute
I've been trying this for a while now, so might have read this before. Sorry about that, I'm struggling really hard to get this right. At first I tried (wrongly) with the eager load features.
I have ...
1
vote
1answer
96 views
Does Craft do joined calls or multiple ones when fetching the related items? [Element API]
I'm checking Element API's performance to decide if it's a good idea to replace my own custom API with Element API. Right now, when fetching the related items (Via fields or matrix elements), my API ...
1
vote
1answer
330 views
MySQL CPU pegged on specific query
Very similar (almost exact) to this question: Poor performance on midsized site / Queries without index?
CPU keeps getting pegged to 100% for MySQL, which makes the admin interface very slow (the ...
1
vote
1answer
576 views
Base table or view already exists
Getting the following error on production environment when trying to add a new Field.
CDbCommand failed to execute the SQL statement: SQLSTATE[42S01]: Base table or view already exists: 1050 Table '...
1
vote
1answer
178 views
map values from checkbox field into category field using SQL
In a project I have a checkbox field called gradeLevels with 3 options (k-5,6-8, and 9-12).
Due to design changes, I need to convert this field into a category field. I've made a new category with ...
