framework: Truncating tables in mysql, sqlite, sqlserver abstractions do not cascade, postgres does. This is unexpected and a leaky abstraction
- Laravel Version: 5.8 (or higher)
- PHP Version: 7.4
- Database Driver & Version: postgresql 12
Description:
When you truncate tables using the laravel illuminate db builder it truncates the table as expected. However, postgresql is different because it changes the DEFAULT behavior of truncate from RESTRICT to CASCADE. This means that you can loose all your data in other “related” tables (something that doesn’t happen with the other sql drivers)
Steps To Reproduce:
Fire up postgresql 12 and mysql.
Create 2 tables in both databases. table_a has a foreign key constraint to table_b.
Change your .env to point to mysql db
Run this code DB::table('table_b')->truncate();
you get an error with mysql, because you cannot truncate tables with foreign key constraints by default - this is expected!!!
Change your .env to point to postgersql db
Run this code DB::table('table_b')->truncate();
it deletes your data in both table_b and table_a, which is totally UNEXPECTED behavior
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Reactions: 19
- Comments: 17 (11 by maintainers)
Guess how I found out about this issue?
Table::truncate()inside the migration deleted our production database yesterday 😄3 years passed, Laravel users still truncates their entire databases…
I think @Antobai is absolutely right. Now is a good time to fix this, especially because there are many database changes in Laravel 11 already. People still wiping out their databases =)
Well, it makes sense to open an issue and add a callout to documentation that warns the using of “truncate” can drop database
Hi, I’m sorry I have to reply to and old and closed isssue, but I have to ask you to please reconsider your opinion on this matter.
A simple method, available to everyone using laravel, on a basic model instance, can wipe out your entire database without warning if you’re using postgres and have a constrained database pattern with many relations. I appreciate that the documentation mentions it now but I don’t think it’s enough for such a dangerous behavior. Truncate method when using postgres should trigger a warning and not just do a “truncate cascade” silently. I understand this is a breaking change but it is a necessary breaking change.
We were also a victim of this behavior this morning, fortunately we were on a test database. Very dangerous!
It is documented in the Query Builder section of the documentation, but not in the Eloquent section.
@driesvints I don’t think anyone is expecting the current behavior. This almost wrecked our company’s production servers and is going end up really screwing something up for someone else. Also, this behavior itself is a breaking change that was introduced in https://github.com/laravel/framework/pull/26389 two years ago. I understand that this is a BC break, but is there a reason we can’t get a fix for this on the 9x branch?
If you want to truncate a table with foreign keys on it, you should call raw
DB::statement('truncate table_b cascade')because you actually know what you’re doing to your database. Having cascade override the default is definitely not expected behavior, it is restrict as you can see in the postgresql docs: 😄https://www.postgresql.org/docs/9.1/sql-truncate.html
RESTRICT Refuse to truncate if any of the tables have foreign-key references from tables that are not listed in the command. This is the default.
It would appear that this terrible pull request was accepted into laravel like 2 years ago and now we are afraid to change it. I don’t think we should be. The worst thing that happens is that their code breaks because they can’t do a cascade delete, in which a simple update to the code would allow them to truncate cascade. If we leave this bug in place, it will potentially wreck people’s databases because cascading truncates are not expected at all.
@driesvints Just wanted to say thank you for your help and time. I understand that this is a hard thing to abstract because the behaviors of these two DBMS are entirely different. Having it as a flag to
truncate()would be a much better solution (if you’re using Msql or something besides postgres, the flag could be a no op) and prevent a nightmare scenario from unfolding for someone.Thanks. I’ve sent in a PR to the docs to warn about this: https://github.com/laravel/framework/issues/35157
Most likely we won’t change this behavior as it’d be a breaking change to anyone expecting the current behavior.
Thanks. I’m currently thinking of a specific truncate method for the postgres grammar maybe. That wouldn’t be breaking I believe. We’re open to PRs if anyone wants to give that a go.