100 debugging challenges in MySQL development

Tags

Perfect — let’s build a comprehensive list of 100 different types of development-related problems in MySQL programming. I’ll group them by categories (schema design, indexing, queries, transactions, replication, performance, security, etc.) so it’s structured and easier to navigate.

🔹 Schema Design Problems

  1. Using VARCHAR(255) everywhere without reason.
  2. Storing numbers as VARCHAR instead of INT/DECIMAL.
  3. No primary key in tables.
  4. Overuse of composite primary keys.
  5. Using TEXT where VARCHAR is sufficient.
  6. Storing JSON in TEXT instead of JSON type.
  7. Not normalizing repeated fields (1NF violation).
  8. Over-normalization leading to too many joins.
  9. Using ENUM where lookup tables are better.
  10. Schema drift between dev, test, and prod databases.

🔹 Indexing Problems

  1. Missing indexes on foreign keys.
  2. Duplicate indexes created accidentally.
  3. Over-indexing tables, slowing down writes.
  4. Using FULLTEXT where LIKE '%...%' would suffice.
  5. No composite index for multi-column WHERE clauses.
  6. Wrong index order (INDEX(a,b) vs INDEX(b,a)).
  7. Indexes not used due to datatype mismatch.
  8. Index fragmentation over time.
  9. Unused indexes wasting storage.
  10. Index not covering query (forcing table lookups).

🔹 Query Writing Problems

  1. SELECT * used in production queries.
  2. Hard-coded values instead of parameters.
  3. Queries not parameterized (SQL injection risk).
  4. Cartesian product from missing join conditions.
  5. Using subqueries instead of JOINs.
  6. Correlated subqueries slowing performance.
  7. Misuse of DISTINCT to “fix” duplicate joins.
  8. ORDER BY without an index.
  9. GROUP BY non-indexed column.
  10. HAVING used instead of WHERE.
  11. Joins across different collations.
  12. Using IN (SELECT ...) instead of JOIN.
  13. LIKE with leading wildcard ('%value').
  14. Misuse of UNION instead of UNION ALL.
  15. Query depending on implicit type casting.

🔹 Transaction & Concurrency Problems

  1. Forgetting to COMMIT in transactions.
  2. Long-running transactions locking rows.
  3. Deadlocks between concurrent transactions.
  4. Phantom reads in REPEATABLE READ isolation.
  5. Lost updates from concurrent UPDATE.
  6. Unnecessary use of SERIALIZABLE isolation.
  7. Lock escalation on large updates.
  8. Gap locks blocking inserts.
  9. Using autocommit when multiple operations need atomicity.
  10. Forgetting to rollback on error.

🔹 Replication & Clustering Problems

  1. Replication lag causing stale reads.
  2. Non-deterministic functions (NOW(), RAND()) in statements replicated.
  3. Large transactions slowing replication.
  4. Row vs statement-based replication mismatch.
  5. GTID replication conflicts after failover.
  6. Replication break due to schema drift.
  7. Circular replication loops.
  8. Writes going to read replicas accidentally.
  9. Incorrect failover handling in application.
  10. Binlog corruption breaking replication.

🔹 Stored Procedures & Functions Problems

  1. Lack of error handling inside stored procs.
  2. Overuse of stored procedures for business logic.
  3. Recursive stored procedure calls causing stack overflow.
  4. Deterministic flag misused in stored functions.
  5. Procedures returning inconsistent result sets.
  6. Using triggers for application logic.
  7. Multiple triggers on same event causing order dependency.
  8. Infinite trigger loops (insert/update triggers firing each other).
  9. Triggers silently failing without raising error.
  10. Using UDFs with untrusted code.

🔹 Performance Problems

  1. Table scans due to missing indexes.
  2. Slow joins with large intermediate results.
  3. Temporary table creation on disk.
  4. Filesort for ORDER BY without index.
  5. Using OFFSET in pagination causing slow queries.
  6. Non-sargable queries (WHERE YEAR(date)=2025).
  7. Query plan changes due to bad statistics.
  8. MySQL choosing wrong index.
  9. Large IN() lists slowing optimizer.
  10. Ignoring EXPLAIN for query analysis.

🔹 Storage Engine Problems

  1. Mixing InnoDB and MyISAM incorrectly.
  2. Forgetting to use InnoDB for transactions.
  3. MyISAM table crashes requiring repair.
  4. InnoDB buffer pool too small for workload.
  5. Temp tables on disk because MEMORY engine not used.
  6. Incorrect use of MEMORY tables for large datasets.
  7. Failure to adjust innodb_log_file_size for big transactions.
  8. Foreign keys ignored in MyISAM tables.
  9. Too many partitions (overhead > performance gain).
  10. Poor sharding strategy.

🔹 Migration & Version Upgrade Problems

  1. Breaking changes between MySQL versions.
  2. Collation changes breaking string comparisons.
  3. Default authentication plugin mismatch (caching_sha2_password vs mysql_native_password).
  4. Deprecated features still in use (TIMESTAMP DEFAULT 0).
  5. Migration script dropping indexes unintentionally.
  6. Data truncation during migration.
  7. Incompatible schema with ORMs.
  8. Downgrade not possible after upgrade.
  9. Application not tested against new optimizer.
  10. Timezone mismatches after upgrade.

🔹 Security Problems

  1. Hardcoded MySQL credentials in source code.
  2. No TLS encryption between app and DB.
  3. Over-granting privileges (GRANT ALL ON *.*).
  4. SQL injection from dynamic queries.
  5. No audit logging for sensitive queries.

✅ That’s 100 MySQL programming problems across all major aspects of development. They range from schema design flaws to security issues, touching the entire lifecycle of database-backed software.

LinkedIn Reddit