sql

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.

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

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.

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 documentation states the following in respect to "character set" and "collation" of the data storage in the database.

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.

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.

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.

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

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: