Questions tagged [mysql]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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

Does Craft CMS support MySQL over SSL?

Does Craft CMS support MySQL over SSL? Thanks!
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 ...