Web Development > Database Programming and Administration

Ecommerce Website Development

impinge — Thu, 21 Jul 2011 22:18:59 -0500

Hello
I want to know about the latest techniques of Ecommerce Website Development. So please help me out.
Thanks a lot.
For more info please visit:http://www.impingesolutions.com/

How To Determine MyISAM Or Innodb Engine

scott — Thu, 12 May 2011 04:05:11 -0500

How do you determine whether a MySQL table is MyISAM or InnoDB?

ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist

scott — Thu, 16 Dec 2010 16:51:14 -0600

After upgrading MySQL from MySQL 5.0.77 to 5.1.47, we are getting the "ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist" error whenever we run "flush privileges" MySQL command. We cannot insert a new privileged user, and make the user effective.

This occurred on Server version: 5.1.47 MySQL Community Server (GPL) by Remi, installed on CentOS whenever I would execute the 'FLUSH PRIVILEGES' command.

MySQL case sensitive problem on Linux after migrating from Windows

Aladar — Sun, 22 Aug 2010 14:33:32 -0500

We have a POS software custom developed for King Spa, which is based on open-source Openbravo POS. Due to the Mysql server has gone away problem on Windows, we migrated the database to Linux. The Openbravo POS v2.2 uses mixture of uppercase and lowercase table names and the code incorrectly uses the table names in wrong case. This causes the application to fail as the necessarily table cannot be found in Linux MySQL database.

MySQL table names are case-sensitive depending on the filesystem of the server. Since Windows and Mac are a case-insensitive OS, and Linux is case-sensitive; the MySQL database treats the uppercase table name differently than lowercase table name on Linux machines.

Solution: 1. Edit the MySQL configuration file (i.e. /etc/my.cnf), and add the following line in [mysqld] heading.

lower_case_table_names=1

Note: By running the 'mysqld' with lower_case_table_names=1, the database creates the tables in all lower-case names. The existing table names are unaffected. 2. Restart the database so that all database table names are now created in lower-case. 3. Verify that the change we made above did take effect in the database. On Linux shell, run the following command and verify the value of 'lower_case_table_names'.

# mysqladmin -u root -p variables

Note: All database table name comparison are now done in LOWER CASE. When the table name is given to mysql, it is converted to the LOWER CASE and the comparison is made; making it CASE INSENSITIVE comparison. 4. (Important*) You will have to drop the database, and re-import it into the MySQL database running in Linux. Note: Openbravo POS also uses mixture of uppercase and lowercase names in table column names. The mixed-case column names are treated indifferently on both environment, so there is no worries for mixed-case column names.

How do you reset MySQL sequence number?

Aladar — Thu, 21 May 2009 16:42:43 -0500

MySQL doesn't really support sequence numbers, but it does have an auto increment value which may be applied to a primary key of a table. A column with an auto increment "Extra Attribute" set automatically increments the value by 1 from the last number. To reset or modify auto increment value, you may use the following ALTER command.

mysql> alter table {table_name} auto_increment = 1;

mysqldump: Can't get CREATE TABLE for table

aladar — Sat, 28 Jul 2007 22:23:37 -0500

I'm trying to make a backup of entire database, but error occurs.

[root@db root]# mysqldump --all-databases >db.sql mysqldump: Can't get CREATE TABLE for table `nuke_stats_date` (Can't open file: 'nuke_stats_date.MYD'. (errno: 144))

I remember we had trouble with MySQL database when the disk filled to 100%, which we killed MySQL process manually. Since this happened, some of the database tables were corrupted and we were able to fix them using the 'repair' command. However, I cannot repair the above table. Any help will be greatly appreciated.

MySQL Replication: replicating specific databases only

admin — Thu, 05 Jul 2007 23:53:12 -0500

The master and slave database servers do not need to be in sync entirely, with all databases and all tables from the master replicated onto the slave. By default, the slave will replicate everything, but this behavior can be changed with change in configuration options:

// replicate this database replicate-do-db=database_name // don't replicate this database replicate-ignore-db=database_name // replicate this database.table replicate-do-table=database_name.table_name // don't replicate this table replicate-ignore-table=database_name.table_name // allows wildcards, use % as the wildcard character // e.g db% would be all databases beginning with db replicate--wild-do-table=database_name.table_name // ignore all specified tables, with wildcards replicate-wild-ignore-table=db_name.table_name

These options can all be used multiple times in a single configuration. A couple of other useful options:

// allows you to use map databases that use different // database names on each server replicate-rewrite-db=master_db->slave_database // writes replicated statements to the slaves binary logs log-slave-update

MySQL DB Replication: Slave having problem starting Slave I/O thread.

aladar — Tue, 03 Jul 2007 12:21:37 -0500

I've setup MySQL db replication, and started both master and slave but synchronization does not work. Looking at the /var/log/mysqld.log on the slave machine shows the following error messages:

070703 11:55:25 [ERROR] Slave I/O thread exiting, read up to log 'mysql-bin', po
sition 79
070703 11:57:45 [ERROR] Error reading relay log event: slave SQL thread was kill
ed
070703 11:59:45 [Note] Slave SQL thread initialized, starting replication in log
'mysql-bin.000001' at position 4190, relay log './web2-relay-bin.000001' positi
on: 4
070703 11:59:46 [Note] Slave I/O thread: connected to master 'slave@74.205.60.14
6:3306', replication started in log 'mysql-bin.000001' at position 4190

The "show slave status" command shows the following values:
Slave_IO_State: [none]
Slave_IO_Running: No
Slave_SQL_Running: yes

Any insight into fixing this problem? Thanks.

How to run MySQL Commands from a Text File?

aladar — Thu, 05 Apr 2007 17:11:18 -0500

How do you run a SQL command from a text file on MySQL database?

How do you rename mysql table name?

ryan — Sat, 10 Mar 2007 12:01:52 -0600

Is there a way to rename mysql table name? Thanks.

How to query REDO log file information from sqlplus?

admin — Wed, 07 Mar 2007 23:42:15 -0600

To query the redo logs groups and members information including the status of the redo log file, perform the following SQL command:

SQL> select group#, sequence#, bytes, members, status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 104857600 1 CURRENT
2 2 104857600 1 INACTIVE
3 3 104857600 1 INACTIVE
4 4 104857600 1 UNUSED

4 rows selected.

To query the location of redo log file member files, perform the following:

SQL> select type, member from V$LOGFILE;

TYPE MEMBER
---------------------------------------------------------------------------------------
ONLINE /redos/redo01_01.log
ONLINE /redos/redo02_01.log
ONLINE /redos/redo03_01.log
STANDBY /redos/stb_redo04_01.log

4 rows selected.

To query standby log file information, perform the following:

SQL> select group#, archived, status from v$standby_log;

GROUP# ARC STATUS
---------- --- ----------
9 YES UNASSIGNED
10 YES UNASSIGNED
11 YES UNASSIGNED
12 YES UNASSIGNED
13 YES UNASSIGNED

5 rows selected.

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

aladar — Wed, 07 Mar 2007 23:40:37 -0600

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. Any clue how to repair this?

After mysql upgrade, I'm having trouble with mysql passwords

aladar — Mon, 04 Dec 2006 21:58:36 -0600

Q. After MySQL upgrade from 3.5.x to 4.1.x, the mysql passwords do not work anymore. Is there a quick fix for this?

A. The MySQL 4.1.x (or above) may have to respond to auth requests from "old" clients, such as a prebuilt PHP binary or any application that may use built-in mysql libs. To make 4.1.x (or above version) work with the applications used in older versions (i.e. 3.5.x), you'll have to make old-password compatible with the new with command-line option.

Option 1.
For MySQL 4.1 or later, the my.cnf file may be used to specify configuration options. In addition to specifying the basedir and datadir values, you may also set the old-passwords option, which will instruct MySQL to use PHP-compatible passwords:
[mysqld]
basedir = ...
datadir = ...
old-passwords=1
max-connections=150
default-character-set=latin1
default-collation=latin1_bin

Option 2.
Start mysqd with --old-passwords option
# mysqld --old-passwords

To see wheter MySQL is using old or new passwords, perform the following on the MySQL client:

mysql> SELECT Host, User, Password FROM mysql.user
-> WHERE LENGTH(Password) > 16;

If it returns the rows, it is using the new passwords.

MySQL, Character Set and collation

aladar — Mon, 04 Dec 2006 20:29:37 -0600

MySQL documentation states the following in respect to "character set" and "collation".

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

Suppose that we have an alphabet with four letters: 'A', 'B', 'a', 'b'. We give each letter a number: 'A' = 0, 'B' = 1, 'a' = 2, 'b' = 3. The letter 'A' is a symbol, the number 0 is the encoding for 'A', and the combination of all four letters and their encodings is a character set.

Suppose that we want to compare two string values, 'A' and 'B'. The simplest way to do this is to look at the encodings: 0 for 'A' and 1 for 'B'. Because 0 is less than 1, we say 'A' is less than 'B'. What we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): compare the encodings. We call this simplest of all possible collations a binary collation.

But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters 'a' and 'b' as equivalent to 'A' and 'B'; (2) then compare the encodings. We call this a case-insensitive collation. It's a little more complex than a binary collation.

-- End of MySQL documentation citation

Starting with MySQL version 4.1, there are five levels to which character sets and collations can be applied - server, database, table, column and string constant. The rules for applying character set and collation occurs according to precedence. The order of precedence is string constant, column, table, database and server. The character set and collation specific to a particular string constant and column apply first. If those don't exist, the table default is used, and if that doesn't exist the database default, and finally the server default. By default, MySQL uses the latin1 character set, and the latin1_swedish_ci collation. You can see which defaults apply to your installation by running the folloing statements:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)

The server defaults are set up in the configuration file (/etc/my.cnf). For example:

[mysqld]
...
default-character-set=euckr
default-collation=euckr_korean_ci
...

To create a table or a database that makes use of a different character set and collation, simply use the CHARACTER SET (CHARSET is a synonym) and COLLATION clauses.

mysql> CREATE TABLE tablename(column definitions, ...) CHARACTER SET euckr COLLATE euckr_korean_ci;
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE DATABASE dbname CHARACTER SET euckr COLLATE euckr_korean_ci;
Query OK, 1 row affected (0.05 sec)

-- If you already have a database that you need to change character set, use alter database command.
mysql> ALTER DATABASE dbname CHARACTER SET euckr COLLATE euckr_korean_ci;

Client, Results and Connection CHARACTER SET and COLLATION
Setting the correct character set on the server is only a half story. In order to display characters correctly on the client (be it a GUI or Web), it is important how the client gets the data from the server. To establish a client connection to the server with a desired character set, we need to define client connection settings with variables.

set character_set_client = euckr;
set character_set_results = euckr;
set character_set_connection = euckr;

Since those three variables are likely changed at the same time, MySQL offers a command that will allow you to change all three with one command.

set names = euckr;
or
set names euckr collate euckr_korean_ci;

MySQL spits "Too many connections" error

aladar — Wed, 15 Nov 2006 16:50:05 -0600

If you have too many connections on the MySQL database, you may reach a limit and no new connections can be created. The default connection limit is 100, and you may use max_connections system variable to increase the size. To see the current max_connections size, connect to the mysql and type the following commane mysql> show variables like 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. To diagnose this problem, type the following from the MySQL client session: mysql> show processlist; You will see all the connections that exists. Each connection is associated with an ID, and you'll use that ID to disconnect (or kill) the connections. mysql> kill 3004512; To increase the max_connections and max_user_connections, please edit the /etc/my.cnf file and add the following statements. [mysqld] max_connections=300 It is always helpful to check your code and make sure all mysql connections are being closed properly, and change from mysql_pconnect to mysql_connect. The pconnect may not give you much speed advantage if connecting to a local mysql server.