Database Basement: Tables, Queries, Backups, and Tiny Data Gremlins
Welcome to Web Dev → Databases — the Fyrbloc place for MySQL, MariaDB, PostgreSQL, SQLite, database design, queries, migrations, backups, imports, exports, indexes, relationships, errors, permissions, and that terrifying moment when a command asks:
Are you sure?
and suddenly you are not sure about anything.
Databases are where your project keeps its memory.
Users, posts, products, orders, settings, comments, logs, sessions, tags, uploads, payments, messages, and all the tiny bits of information that make a website or app feel alive.
So yes — databases are important.
They are also very good at punishing vague thinking.
A messy database can turn a simple project into a haunted filing cabinet with login forms.
What belongs here?
Use Web Dev → Databases for topics like:
- MySQL
- MariaDB
- PostgreSQL
- SQLite
- Database design
- Tables and columns
- Primary keys
- Foreign keys
- Relationships
- Indexes
- Query problems
- Slow queries
- Imports and exports
- Backups and restores
- Migrations
- Seeders
- Permissions
- Connection errors
- Duplicate data
- Missing data
- Character encoding
- Collation issues
- Database size
- phpMyAdmin/Adminer tools
- Laravel migrations
- Flarum database issues
- WordPress database issues
- “I deleted something and now I am breathing manually” moments
If your issue involves data being saved, loaded, missing, duplicated, corrupted, slow, or behaving like it has developed opinions, post it here.
Database help template
Copy this when asking for help:
Database type:
App/framework/script:
Hosting/server:
What I am trying to do:
What happened:
What I expected:
Exact error:
Table involved:
Query/migration involved:
What I already tried:
Backup available:
Yes / No
Private info removed:
Yes
Example:
Database type:
MySQL
App/framework/script:
Laravel
Hosting/server:
CloudPanel
What I am trying to do:
Add a foreign key from orders to users.
What happened:
Migration fails.
What I expected:
orders.user_id should link to users.id.
Exact error:
Cannot add foreign key constraint
Table involved:
orders, users
Query/migration involved:
Added below.
What I already tried:
Checked column names and migration order.
Backup available:
Yes
Private info removed:
Yes
That is a strong database help post.
Much better than:
Database broke.
Databases do not simply “break.”
They quietly document your decisions and return them later as consequences.
Always include the exact error
Database errors usually tell you where to look.
Common examples:
Access denied for user
Unknown database
Table does not exist
Column not found
Duplicate entry
Cannot add foreign key constraint
Data too long for column
SQLSTATE[HY000]
Lock wait timeout exceeded
Connection refused
Not helpful:
It says SQL something.
or:
Database angry.
The database may be angry.
But paste the message anyway.
Basic database checks
Before posting, check the boring stuff first.
Boring checks save projects.
Check database credentials
DB_HOST
DB_PORT
DB_DATABASE
DB_USERNAME
DB_PASSWORD
Safe example:
DB_HOST=localhost
DB_DATABASE=my_app
DB_USERNAME=hidden
DB_PASSWORD=hidden
Do not post real passwords.
The internet does not need a tour of your database.
Check whether the database exists
For MySQL/MariaDB:
mysql -u username -p -e "SHOW DATABASES;"
Use the real username, but do not post the password publicly.
Check tables
SHOW TABLES;
Check table structure
DESCRIBE users;
Check recent records
SELECT * FROM users ORDER BY id DESC LIMIT 10;
Do not run random delete/update queries on production.
A SELECT looks.
A DELETE bites.
Backups before risky work
Before doing risky database work, make a backup.
Risky work includes:
- Imports
- Restores
- Migrations
- Big updates
- Deleting rows
- Changing column types
- Dropping tables
- Changing indexes
- Moving servers
- Running old SQL files
- Editing production data manually
A basic MySQL backup command:
mysqldump --no-tablespaces -u username -p database_name > database-backup.sql
Compressed backup:
mysqldump --no-tablespaces -u username -p database_name | gzip > database-backup.sql.gz
A backup you have not tested is not full confidence.
It is a lucky charm with a file extension.
Useful, maybe.
Verified, better.
Restoring database backups
Restore carefully.
Plain SQL:
mysql -u username -p database_name < database-backup.sql
Compressed SQL:
gunzip < database-backup.sql.gz | mysql -u username -p database_name
Before restoring, check:
Correct database?
Correct server?
Correct file?
Do I need to preserve current data?
Do I have a backup of the current state?
Will this overwrite anything?
The most dangerous database command is the one run in the wrong place with full confidence.
Confidence is not a backup.
Relationships and foreign keys
Foreign keys are useful because they keep data connected properly.
Example:
users.id
orders.user_id
This means an order belongs to a user.
Common foreign key problems:
Column types do not match
Referenced table does not exist yet
Referenced column is not indexed
Migration order is wrong
Existing data violates the relationship
Table engine does not support foreign keys
Unsigned/signed mismatch
Example Laravel-style problem:
users.id is unsignedBigInteger
orders.user_id is integer
Those may not match.
The database notices.
The database always notices.
Indexes and speed
Indexes help databases find data faster.
Useful for columns used in:
WHERE
JOIN
ORDER BY
GROUP BY
Search filters
Foreign keys
Example:
CREATE INDEX idx_posts_user_id ON posts(user_id);
But do not index everything.
Too many indexes can slow down writes and make the database heavier.
A database without indexes can be slow.
A database with random indexes everywhere can become a drawer full of labelled drawers.
Organised-looking chaos.
If a query is slow, include:
Table size
Query
Indexes
EXPLAIN output if possible
App/framework
Database type
Useful MySQL check:
EXPLAIN SELECT * FROM posts WHERE user_id = 5;
Common query mistakes
Selecting too much
Not ideal:
SELECT * FROM users;
Better when possible:
SELECT id, name, email FROM users LIMIT 50;
Only fetch what you need.
The database is not a buffet.
Missing limits
Dangerous on large tables:
SELECT * FROM logs;
Better:
SELECT * FROM logs ORDER BY id DESC LIMIT 100;
Logs can be enormous.
Opening all logs at once is how laptops start making aircraft noises.
Unsafe user input
Bad:
$sql = "SELECT * FROM users WHERE email = '$email'";
Use prepared statements, query builders, or ORM methods.
User input is not trustworthy.
Even if the user says “promise.”
The database does not accept promises.
Laravel database topics
Laravel database posts can include:
- Migrations
- Seeders
- Factories
- Eloquent models
- Relationships
- Query builder
- Pagination
- Validation
- Database config
- Foreign keys
- Rollbacks
- Soft deletes
- Pivot tables
- Mass assignment
- Duplicate records
Useful Laravel commands:
php artisan migrate
php artisan migrate:status
php artisan migrate:rollback
php artisan db:seed
php artisan tinker
Be careful:
php artisan migrate:fresh
That drops all tables and rebuilds them.
Fine locally.
Dangerous in production.
It is not a “refresh button.”
It is a database bulldozer.
Flarum database topics
Flarum database issues may involve:
- Users
- Discussions
- Posts
- Tags
- Settings
- Permissions
- Extensions
- Migrations
- Upgrade errors
- Missing tables
- Failed extension migrations
- Backup/restore
- Search/index issues
- Slug conflicts
Useful Flarum commands:
php flarum info
php flarum migrate
php flarum cache:clear
Before database work on Flarum:
Back up files
Back up database
Save composer.json
Save composer.lock
Save config.php
Flarum extensions can change database structure.
That is normal.
But do not install, remove, migrate, and panic-click all at once.
That creates a digital soup nobody ordered.
Import/export problems
Common import problems:
File too large
Timeout
Wrong database selected
Character encoding issue
Duplicate table exists
Permission denied
SQL mode conflict
Version mismatch
If import fails, include:
Database type/version
File size
Import method
Exact error
Hosting/server
Was target database empty?
Backup available?
For large SQL files, command line is usually better than browser-based tools.
Browser imports can time out and then act like nothing happened, which is rude.
Character encoding and weird symbols
If text looks broken, like:
é
’
�
you may have encoding/collation problems.
Useful details:
Database charset
Table collation
App charset
Import file encoding
Where the text came from
Common modern choice:
utf8mb4
For MySQL/MariaDB, utf8mb4 is usually preferred because it handles more characters, including emoji.
Without it, your database may look at an emoji and decide life is too hard.
Good database post titles
Use clear titles like:
MySQL access denied after moving Laravel site
Cannot add foreign key constraint in migration
Flarum migration failed after enabling extension
Slow query when loading dashboard records
Database import fails because SQL file is too large
Duplicate entry error when saving user email
Avoid:
Database help
SQL issue
Broken
Problem
Those titles are too vague.
Databases like structure.
So should your title.
Good replies in database threads
Helpful replies:
Check whether both foreign key columns use the same type and unsigned setting.
This looks like a permissions issue for the database user.
Run DESCRIBE on both tables and compare the column types.
Add an index to the column used in the WHERE clause and test with EXPLAIN.
Make a backup before running that migration on production.
Less helpful replies:
Just delete it.
Use another database.
Start again.
No idea.
No.
Do not casually tell someone to delete things.
Database advice needs a seatbelt.
Database safety rules
Never post:
Database passwords
Full connection strings with secrets
Customer/user data
Private emails
Payment details
Full production dumps
API keys
Admin credentials
Private backups
Personal data
Safe:
DB_HOST=localhost
DB_DATABASE=my_app
DB_USERNAME=hidden
DB_PASSWORD=hidden
Unsafe:
Here is my live database login.
No.
The database is not a group chat.
Database help checklist
Before posting, check:
Did I include the database type?
Did I include the app/framework?
Did I include the exact error?
Did I say what table/query/migration is involved?
Did I say what changed recently?
Did I mention whether I have a backup?
Did I remove passwords and private data?
Did I include only safe sample data?
Did I ask a clear question?
That is enough.
Clear database posts get better replies.
Vague database posts make everyone ask for table names first.
Save the warm-up round.
Quick database starter prompt
Use this:
Database issue:
Database type:
App/framework:
Server/hosting:
What I expected:
What happened:
Exact error:
Table/query/migration:
What I tried:
Backup available:
Yes / No
Private info removed:
Yes
Example:
Database issue:
Duplicate email error when creating users.
Database type:
MySQL
App/framework:
Laravel
Server/hosting:
CloudPanel
What I expected:
User should be created if email is new.
What happened:
Signup fails with duplicate entry error.
Exact error:
Duplicate entry 'test@example.com' for key 'users_email_unique'
Table/query/migration:
users table
What I tried:
Checked existing users and validation rules.
Backup available:
Yes
Private info removed:
Yes
Clean.
Useful.
Debuggable.
Not a haunted spreadsheet.
Final note
Databases are powerful because they remember everything.
That is also why mistakes matter.
Design tables carefully.
Use backups.
Check queries.
Protect private data.
Do not run destructive commands casually.
Ask before doing anything that smells like “drop,” “truncate,” or “delete.”
A good database keeps the build alive.
A bad database decision becomes a story people tell later with a tired face.
Build. Store. Query. Back Up. Ship.
