10 votes
Accepted

Query only returning 100 records at a time?

Craft imposes a default limit of 100 for element queries. To disable this you need to specify a limit of null. $criteria->limit = null;
Ben Fleming's user avatar
9 votes

What's the best practice for handling data migration and organization across development environments?

I found a great blog post where the author uses Phinx and MySQL Query Log to create database migrations. I summarized its content into 10 easy steps: Step 1 Enable the MySQL Query Log on your dev ...
jlcharette's user avatar
9 votes
Accepted

Craft 3: Get Data from a custom Table via createCommand()

You can use either an activeRecord for it $customer = Customer::find() ->where(['id' => 123]) ->one(); Or a query $rows = (new \craft\db\Query()) ->select(['id', '...
Robin Schambach's user avatar
8 votes
Accepted

Craft 3: Can't backup database ("mysqldump: command not found")

Craft 3 did away with the PHP-based database backup solution that Craft 2 utilized (for many reasons). Now it requires that the mysqldump executable (if you're using MySQL) or pg_dump executable (if ...
Brad Bell's user avatar
  • 67.4k
8 votes

Accessing Multiple Databases in Craft

From your config/app.php file you could do something like this: return [ 'components' => [ 'otherDb' => [ 'class' => craft\db\Connection::class, '...
Brad Bell's user avatar
  • 67.4k
7 votes

"Eager loading" of relations like Yii's "with" function

This feature has now made it into Craft core as of version 2.6. See this link for documentation on the subject: https://craftcms.com/docs/templating/eager-loading-elements
naboovalley's user avatar
  • 2,844
7 votes
Accepted

Craft 3: Update database after CMS upgrade via console command

You can use Yii's migrate console command. Triggered in Craft 3 via craft migrate --type=app.
Brad Bell's user avatar
  • 67.4k
6 votes
Accepted

Good practice to "Clear Caches" before you "Backup Database"?

Is this helpful, or am I just being OCD? Completely OCD. :) The database backup class already excludes unnecessary tables like asset indexing data, sessions, template caches, etc. during a backup. ...
Brad Bell's user avatar
  • 67.4k
6 votes

How many entries could the Craft database hold before it slows down?

It's a bit of a loaded question, but generally it's going to depend more on environmental limitations than anything with Craft itself. If you're on a shared host along with 200 other accounts using ...
Brad Bell's user avatar
  • 67.4k
6 votes
Accepted

Many db requests with relationship fields

This is how relationships work in Craft at the moment. Each relation equals +1 query. If you don't access your relation on an entry the query isn't executed. So by only fetching title and/or id you ...
naboovalley's user avatar
  • 2,844
6 votes
Accepted

Deploying CraftCMS to Digital Ocean

I would suggest to read through the following articles: https://nystudio107.com/blog/multi-environment-config-for-craft-cms https://nystudio107.com/blog/database-asset-syncing-between-environments-...
HendrikEng's user avatar
  • 1,064
6 votes

Craft 3 cannot find mysqldump command, console works

After a lot of digging I found an SO post which recommended updating the environment variable via PHP: putenv("PATH={$_SERVER["PATH"]}:/usr/local/mysql/bin"); Adding that line into craft/config/...
Crafty Cat's user avatar
6 votes
Accepted

DB CPU running at 100%, Pages Never Load, Moving from MySQL to MariaDB

You're likely running into an issue seen with recent versions of MariaDB (10.6 and later), where importing an SQL dump can result in indexes not being created properly. Essentially, this will make ...
Mats Mikkel Rummelhoff's user avatar
5 votes

Migration of Craft CMS from one host to another

The accepted answer is a good one, but I thought I'd add something as food for thought. If you set up your workflow such that: All of your Craft CMS project is checked into a git repo and deploy it ...
andrew.welch's user avatar
  • 11.5k
5 votes
Accepted

Migration of Craft CMS from one host to another

You need a copy of the database which you can either export via Craft's Control Panel or via phpMyAdmin or a similar database management tool. You need these folders: craft/templates craft/...
Florian's user avatar
  • 428
5 votes

What's the best practice for handling data migration and organization across development environments?

I'm currently implementing a workflow that's very efficient for us. The trick is to use Backup Pro to "backup" locally and then "restore" once the site is deployed. This essentially version controls ...
SteveO's user avatar
  • 326
5 votes

Query only returning 100 records at a time?

By default, Craft sets a limit of 100. Set it to null: limit(null).
awcross's user avatar
  • 99
5 votes

Craft can't connect to the database (using ec2)

So a couple of things: It's probably not a great idea to connect as root -- it'd be better to create a mysql user that has access only to that db. Make sure that the db you're connecting to is indeed ...
andrew.welch's user avatar
  • 11.5k
5 votes

Self-hosting requirements/scaling

No reason at all, that should work perfectly fine. In fact, at least 90% of my projects keep files, database, and assets all on the same server. For the remaining 10%, I'm usually only moving the ...
Lindsey D's user avatar
  • 24k
5 votes

Randomly getting the "Integrity constraint violation" sql error on saving entry

It's not a Craft bug. The error means that the data in your craft_relations table is out of sync with the data in the your craft_elements table. i.e. an ID exists in craft_relations.targetId that ...
Brad Bell's user avatar
  • 67.4k
5 votes
Accepted

Is there a name for Craft 3's database model?

Craft uses a fairly typical relational model, where most1 custom fields have a single column in the content table, set to an appropriate column type chosen by the field type. Unlike EAV, which crams ...
Brandon Kelly's user avatar
4 votes

General speed/performance on large Craft build

We are constantly seeing the deleting stale template cache timeout problem on our servers - try increasing the time PHP can execute for to something very long. It seems like clearing the caches counts ...
Matt Wilcox's user avatar
  • 3,149
4 votes
Accepted

Database import in admin Control Panel

Not currently – if you want to, you can add a feature request. In the meantime, if you're on a Mac I recommend using Sequel Pro for all your importing (and other database related) needs.
Mats Mikkel Rummelhoff's user avatar
4 votes
Accepted

Entry slug auto appends -1 even when the original slug is unique

I don't know the specific rules Craft uses but I believe Craft auto appends the -1 when it tries to save an Element that has a matching URI in the craft_elements_i18n table. A few scenarios that I ...
Ben Parizek's user avatar
  • 13.4k
4 votes
Accepted

date in database -1 day as on frond-end

The database will be storing the date as seconds since 1970 (epoch) and your view on that is not localised. However, the front end date is localised for your time zone. Of this I am more sure than ...
Jeremy Daalder's user avatar
4 votes

Date value different in CP and database

Craft stores all dates in UTC to the database, no matter what timezone you configure in settings. When (user) models are populated, e.g. for use in the CP, DateTime objects are created from these ...
carlcs's user avatar
  • 36.2k
4 votes
Accepted

What is the best practice in Craft 3 to sync schema between environments?

With the release of Craft 3.1 you can now keep fields (and other things) in sync between multiple environments. Read more here: https://docs.craftcms.com/v3/project-config.html
Radmation's user avatar
  • 198
4 votes

Simple save data to db in Craft CMS

I suggest you to read about the Yii Query Builder craft()->db->createCommand() ->insert('mytablename', array( 'field1' => 'foo', 'field2' => 'bar' )); ...
Robin Schambach's user avatar
4 votes
Accepted

Craft 3 : What are the advantages to use PostgreSQL and when should I use it instead of MySQL?

This isn't actual first-hand experience talking, but from what I've read, Postgres will start to see gains when we're talking about very large data sets, either in terms of the size of the columns, or ...
andrew.welch's user avatar
  • 11.5k
4 votes
Accepted

Where are tags stored in the database?

Tags are Elements and are stored in multiple places in the database, so editing the db directly may not be as easy as just finding the Tags you want and deleting them. The tag itself is stored in ...
Ben Parizek's user avatar
  • 13.4k

Only top scored, non community-wiki answers of a minimum length are eligible