sql

[SOLVED] MySQL Error 1016: Can't open file: 'user_tracking.MYD'. (errno: 145)

Jul
02

We've been running osCommerce website flawlessly for over two years, but recently encountered the following MySQL error.

1016: Can't open file: 'user_tracking.MYD'. (errno: 145)

We're running mysql 3.23.58 version, and recently restarted the mysqld by killing the mysql processes. This may have corrupted the table file named above.

You may use phpMyAdmin to "repair" the corrupted table. Or, use the command line from the mysql client.

[SOLVED] mysqldump: Can't get CREATE TABLE for table

Jul
02

Have you tried to make a backup of a MySQL database, and run into a problem with errors?

[SOLVED] ERROR 1206 (HY000): The total number of locks exceeds the lock table size

May
23

We have a customized Openbravo POS v2.2 running with a dozen workstations operating 24x7 a year around. The size of the database has grown to 10GB, and the performance started to degrade so we decided to archive old data and start fresh. According to Openbravo POS administrative guide, we performed a DELETE operation on transaction data.

[SOLVED] MySQL Crash with Fatal error: cannot allocate memory for the buffer pool

May
23

We have a dozen websites mostly running Wordpress, and one Drupal website on Digital Ocean with a 1GB instance. The server was running flawlessly for 6 months, and then the MySQL starts to crash every few days unexpectly with the following error in the mysql error log.

MySQL Character Set and Collation

Jun
29

MySQL documentation states the following in respect to "character set" and "collation" of the data storage in the database.

How to change MySQL user and root password?

Jun
09

We have been using DirectAdmin control panel for a number of years, and each time we setup a server it is necessary to change the root password. By default, Direct Admin creates a MySql superuser called "da_admin" with a preassigned password, but we do not know what the root password is. Since we use both DirectAdmin and command-line to manage our server, it is essential that we have proper MySQL root password assigned.

How to change primary key values

May
06

To change values of existing integer field (`table` is the table name, and "id" is the column name), you may use the following update SQL command:

update `table` set id=id+10000;

Let's say that you have an ecommerce website, and you want your order number to begin 10001. But, you already have a handful of orders already so you'll just update the existing values.

If you're updating your primary key values, MySQL will automatically adjust auto_increment value to begin after the largest number stored in the database.

What is the difference between "Primary Key" and "Unique Key"?

Apr
24

I've had a chance to with a Postgres database and came across a table with two primary keys. This intrigue me to lookup a definition of primary and unique key. I always thought there will only be one primary key in a table, and the values have to be unique. But, what I saw in the PostgreSQL v8.1.23 was something otherwise. It allowed duplicate entries, and also allowed multiple primary keys in a table. Is this possible? Yes, it's called composite Primary Keys.

Pgsql Command-line Tools

Apr
23

Although I have been using pgsql for a number of years, every time I wanted to do something I do have to look up a reference as I'm not a regular user of pgsql. Here are a few command-line references which may be helpful to beginners.

To make a backup of a postgres database:

Login as a database user with read access to the db, and run the following command.

%> pg_dump -U {username} {database} > {database.sql}

To restore a backup from a SQL file,

%> psql -U {username} {database} < {database.sql}

To create a database

How to find duplicate values in a table column?

Feb
04

There was an error with one of our application, and I needed to find table rows with duplicate values. The table column was not defined to have a unique index, but we weren't anticipating duplicate values. I needed an easier way to identify duplicate values in a table with simple SQL statement.

Consider the following "employee" table:

Pages

Subscribe to RSS - sql