Open source SQL database often used on Linux with Apache and PHP (LAMP)

Common MySQL Queries

See also: Guide to Tuning MySQL for Performance

Aggregates

Subject

mysql 145 Table is marked as crashed and should be repaired

Got error: 145: Table {sometable} is marked as crashed and should be repaired

I got the this error when trying to do a sqldump of my database.  To fix this error use the utility mysqlcheck from the command line.

This statment shows that the table is marked as crashed:

# mysqlcheck -uuser -ppass database table
mediawiki.searchindex
warning  : Table is marked as crashed
status   : OK

This statement will repair the table:

Subject

MySQL What is happening?

The 5.1 release was very slow getting released

  • Percona did work on innodb XtraDB plugin
  • Google did patches
  • Facebook did a patch set
  • Monty Program - New company started by the founder of MySQL, Forked Maria

MySQL 5.1 Sun/Oracle version we now consider it stable

Oracle bought MySQL, and it has gone OK so far.

Use the Innodb Plugin is much better performance than the included version.

MySQL 5.4

Join Optimizations, Stored Procedures, General Scalability Improvements

Subject

PostgreSQL Commands

Useful SQL for DBAs

List all tables in the current database:

=> SELECT tablename FROM pg_tables where tablename not like 'pg_%';

Limit rows returned as in where rownum <= 10

=> select tablename from pg_tables limit 10;

List tables in one schema

=> select tablename from pg_tables where tableowner='qedit';

List running queries

=> select * from pg_stat_activity;

List database activity

Subject

PostgresQL on Fedora

Installing PostgreSQL on Fedora Core


This will install the PostgreSQL database server and the component required to write PHP scripts that communicate with postgres. We use yum to handle dependencies and gather all of the required packages. For more information on PostgreSQL, see http://www.postgresql.org

1. Install PostgreSQL and the component that allows php to talk to PostgreSQL.

Subject