Database trivia

have you tried sugar CRM ?

2009-08-31T00:44:00.000-07:00

content management in plain english

2009-08-08T00:51:00.001-07:00

How to Empty or Truncate Your MySQL Database Table Tutorial

2009-08-05T00:28:00.001-07:00

one-to-many relationship

2009-07-14T22:40:00.001-07:00

reserved sql keyword as column name

2009-07-14T21:24:00.001-07:00

Junction Table

2009-07-14T05:59:00.000-07:00

stored procedure examples in MySQl 5.0

2009-07-09T03:21:00.000-07:00

a stored procedure is a set of pre compiled SQL routines physically stored on the database.

create procedure proc_name([parameter[,...]])
[characteristic ...] routine_body

parameter:
[IN|OUT|INOUT] param_name type

type:
any valid mysql data type

characteristic:
LANGUAGE SQL
| [not] DETERMINISTIC
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'

routine_body:
valid sql procedure statements
-------------------------------------------------------------------------------------------

parameter types in stored procedures:-

IN: the default. this parameter is passed to the procedure and can be changed inside the procedure, but remains changed outside.

OUT: no value is supplied to the procedure(it is assumed to be null), but it can be modified inside the procedure and is available outside the procedure

INOUT: the characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

Ex:-

mysql> delimiter //
mysql> create procedure displaycustomer (OUT Param1 INT)
-> begin
-> select count(*) into Param1 from CustomerMaster;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> call displaycustomer(@noofc);
Query OK, 0 rows affected (0.05 sec)

mysql> select @noofc;
+--------+
| @noofc |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)

Ex:-
mysql> create procedure square(INOUT P INT) set p=p*p;
Query OK, 0 rows affected (0.00 sec)

mysql> set @Number=10;
Query OK, 0 rows affected (0.00 sec)

mysql> call square(@Number);
Query OK, 0 rows affected (0.00 sec)

mysql> select @Number;
+---------+
| @Number |
+---------+
| 100 |
+---------+
1 row in set (0.00 sec)

------------------------------------------------------------------------------------------------------------

mysql> create procedure procupdate(trnid VARCHAR(6), maxamt INT)
begin
declare num INT;
start transaction;
update TransactionDetail
set productcost=productcost+productcost*0.5
where transactionid=trnid;

update TransactionMaster
set amount=(select sum(productcost) from TransactionDetail where transactionid=trnid)
where transactionid=trnid;

select amount into @num from TransactionMaster
where transactionid=trnid;

IF @num >maxamt then
rollback;
select 'transaction amount more than transaction limit';
select 'action performed rollback';
else
commit;
select 'transaction amount within maximum limit';
select 'action performed commit';
END IF;

end
//
Query OK, 0 rows affected (0.03 sec)
------------------------------------------------------------------------------------------------------------

mysql> create procedure procinssp()
-> begin
-> declare trnamt INT;
-> START TRANSACTION;
-> savepoint spins;
-> insert into TransactionDetail values('TRN013','PRD004',10000);
-> insert into TransactionDetail values('TRN013','PRD008',1000);
-> select sum(productcost) into @trnamt from TransactionDetail
-> where transactionid='TRN013';
->
-> IF @trnamt < 20000 THEN
-> ROLLBACK;
-> select 'sum of product cost < 20000';
-> select 'action performed: Rollback ';
-> END IF;
->
-> IF @trnamt > 30000 and @trnamt < 60000 THEN
-> ROLLBACK TO savepoint spins;
-> select 'sum of product cost between 30000 AND 60000';
-> select 'action performed: Rollback to a save point ';
-> END IF;
->
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)

How to drop a procedure ?

drop procedure [IF EXISTS]

unique key syntax (mysql 5.0)

2009-07-05T12:38:00.001-07:00

producing html o/p in mysql

2009-07-05T02:55:00.000-07:00

zodiac@zodiac-desktop:~$ mysql --user=root --password=rootus --html -e "select *from ProductMaster;" bayross

NB:- --html options produce output only for queries that generate a result set. no o/p is written for queries associated with insert or update statements

how to tell mysql to read queries from a file

2009-07-05T02:52:00.001-07:00

System prompt> mysql [-h host] [-u user username] [-p] < (sql input file) [ > output file]

short cuts for mysql command prompt

2009-07-05T02:18:00.001-07:00

Tested on Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

alter table command examples

2009-07-04T21:47:00.000-07:00

alter table table name add ( (size));

adding new columns at a specific location:-

alter table (table name) add (new column name) (data type) (size) after (old column name);

adding a new column as the first column

alter table (table name) add (new column name) (data type) (size) FIRST;

dropping a column:-

alter table (table name) drop column (column name)

modifying existing columns:-

alter table (table name) modify (column name) (new data type) (new size)[FIRST][AFTER (column name)]

Renaming existing columns:-

alter table (table name) change (old column name) (new column name) (data type) (size)[FIRST][AFTER (column name)]

Renaming a table:-

alter table (old table name) rename [TO] (new table name);

change engine type of a table:-
alter table (table name) type= (type);

adding foreign key via alter table

ex:-
alter table product add constraint fk_companyid
foreign key(cid) references cmaster(cid);

accessing command line help in mysql ?

2009-07-04T00:26:00.001-07:00

deleting users from mysql

2009-07-04T00:22:00.000-07:00

mysql> drop user myuser;
Query OK, 0 rows affected (0.00 sec)

how to determine mysql version ?

2009-07-04T00:16:00.001-07:00

mysql version:-

zodiac@zodiac-desktop:~$ mysqladmin version;
mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.51a-3ubuntu5.4
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 8 min 44 sec

Threads: 2 Questions: 59 Slow queries: 0 Opens: 40 Flush tables: 1 Open tables: 34 Queries per second avg: 0.113

anonymous user in mysql

2009-07-04T00:09:00.000-07:00

on linux based mysql installations anonymous user, having no password and no user name is provided by default.

this user will have access only to test database or any database beginning with the word test.

tested on :- mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486

right outer join

2009-07-03T20:05:00.001-07:00

transaction & ACID test

2009-07-03T19:58:00.001-07:00

what do yo mean by transaction ?

a transaction is a set of sql statements that accomplish a single unit of work.

to help you decide what steps in your sql can be considered as a transaction use the ACID test.

Rules of atomic data

2009-07-01T21:00:00.001-07:00

1st normal form

2009-07-01T20:59:00.001-07:00

functional dependency

2009-07-01T20:57:00.000-07:00

partial functional dependency

2009-07-01T20:52:00.000-07:00

second normal form

2009-07-01T20:44:00.000-07:00

Rule 1: Be in 1NF

Rule 2: Have no Partial functional dependencies

The second normal form focuses on how the primary key in a table relates to the data in it

your 1NF table is also in 2NF if all the columns in the table are the part of the primary key
or it has a single column primary key

transistive dependency

2009-07-01T20:41:00.000-07:00

PRIMARY KEY

2009-07-01T20:26:00.000-07:00

A Primary key can't be NULL

The primary key must be compact

the primary key must contain only the information it needs to be unique and nothing extra

the primary key values can't be changed

foreign key

2009-07-01T20:23:00.001-07:00

Mysql displays an error message when an attempt is made to delete a record in a master table when corresponding records exist in a detail table and prevents detail operation from succeeding.

an insert or update of a value in a record in the detail table is rejected, if a corresponding value does not exist in the master table

a delete operation from the master table is rejected if the corresponding records in the detail table exists

must reference a primary key or unique key columns in a primary table

requires that the foreign key columns and primary key columns have matching data types.

******* can reference a column of the same table.

ON DELETE CASCADE option

when on delete cascade option is specified at foreign key creation time and if a record is deleted in the master table, all corresponding records in the detail table are deleted immediately as well.

NB:-
both the tables must be of innodb type.

Innodb does not automatically create indexes on foreign keys or referenced keys so explicit index creation is required. indexes are needed so that foreign key checks can be fast and do not require a full table scan.

corresponding columns in the primary key/ foreign key relationship must have similar data types so that column values can be compared without a type conversion.

NB:- if the table holds a NULL value, Mysql ignores any UNIQUE or FOREIGN KEY constraints that may be attached to the column.

schema

2009-07-01T20:12:00.001-07:00

natural join

2009-07-01T20:01:00.001-07:00

non-equijoin

2009-07-01T19:52:00.001-07:00

the non-equijoin returns any rows that are not equal. consider the two tables boys and toys, by using non-equijoin , we can see exactly which toys each boy doesn't have

non equi-join tests for inequality

self join

2009-07-01T19:47:00.001-07:00

change-modify-rename examples

2009-07-01T04:47:00.001-07:00

show command examples:-

2009-07-01T04:26:00.000-07:00

to display individual columns in your table

show columns from table name;

show create database database name;

to get the command that will exactly re-create your database;

show index from table name;

this command will display any columns that are indexed and what type of index they have

show warnings
to see all the warning messages

NB:- tested on mysql Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

update query

2009-07-01T04:19:00.000-07:00

tested on mysql Server version: 5.0.51a

10 mysql variables that you should monitor

2009-04-24T02:41:00.000-07:00

These variables are accessible via SHOW STATUS; command

source:- techrepublic

Relationships

2009-04-11T06:46:00.001-07:00

key attributes

2009-04-11T06:43:00.000-07:00

attribute types

2009-04-11T06:40:00.001-07:00

entities and attributes

2009-04-11T06:24:00.000-07:00

entity types & entity sets

2009-04-11T06:22:00.000-07:00

conceptual schema

2009-04-11T06:17:00.001-07:00

query engine architecture under mysql 5.0

2009-04-09T22:23:00.001-07:00

structure of the mysql database

2009-04-09T22:15:00.000-07:00

verified under mysql 5.0

Disk Data Organization in mysql

2009-04-09T21:54:00.001-07:00

each database is stored in a separate directory in existing file system.

Implications:-

1) table names are either case sensitive or insensitive depending on the operating system
2) tables can be dropped/ renamed using OS commands for drop and move

NB:- tested under mySQL 5.0

how to set sql-mode in mysql

2009-04-09T21:49:00.001-07:00

default mode can be set by the command line option --sql-mode="mode" when starting mysqld
ANSI sql can be selected by using the --ansi option mode can be changed during the operation by the command SET [SESSION|GLOBAL] sql-mode="mode"

NB:- tested under mysql 5.0

version specific execution in MySQL

2009-04-09T21:47:00.000-07:00

Ex:- select /*!323 DISTINCT*/ name from employee;

Reduce the incidence of "bad" data by activating strict mode

2009-04-05T10:59:00.001-07:00

The MySQL server can be run in a number of different "modes", each one optimized for a particular purpose. By default, no modes are set; however, it's easy to alter this and run MySQL in "strict mode" by adding the following option to the server command line:
shell> mysqld --sql_mode="STRICT_ALL_TABLES" &

In strict mode, many of the server's automatic corrections are disabled, with MySQL aborting query execution and returning an error instead of internally correcting the bad input. Stricter date checking is also implemented in this mode.

Note: This mode is only available in MySQL 5.0.2 and better

Quickly rebuild indexes

2009-04-05T10:56:00.000-07:00

Normally, every time you make changes to the server's full-text search variables, you need to rebuild the full-text indexes on your tables to ensure that your changes are reflected. This can take a fair amount of time, especially if you have a large volume of data. A quicker alternative is to use the REPAIR TABLE command. Here's how:

NB:- applies to MySQL 4.1.x and/or MySQL 5.0.x.

source:- http://articles.techrepublic.com.com/5100-10878_11-5998264.html?tag=rbxccnbtr1

Automatically retrieve create table code

2009-04-05T10:50:00.000-07:00

NB:- applies to MySQL 4.1.x and/or MySQL 5.0.x.

source:- http://articles.techrepublic.com.com/5100-10878_11-5998264.html?tag=rbxccnbtr1

Monitor the server

2009-04-05T10:44:00.000-07:00

NB:- applies to MySQL 4.1.x and/or MySQL 5.0.x.

source:- http://articles.techrepublic.com.com/5100-10878_11-5998264.html?tag=rbxccnbtr1

Dump table data in CSV format

2009-04-05T10:39:00.000-07:00

Normally, a MySQL dump file contains a complete list of the SQL commands needed to recreate the table. This is very handy if you intend to import the dump file back into MySQL at a later date, but isn't such a great idea if your target application (for example, Excel) doesn't understand SQL. In such situations, you can save yourself some time by telling MySQL to create dump files in CSV format, which can be easily imported into most applications. Here's how to do it using mysqldump:
shell> mysqldump -T . --fields-terminated-by=", " mydbmytable

or better yet

sudo mysqldump -u root -p -T /var/lib/mysql --fields-terminated-by=", " mydb mytable

This will produce a text file in the current directory, containing the records from the table mydb.mytable in comma-delimited format

NB:-
The -T flag followed by the directory name is where MySQL will write its files to. You can have a space between the -T and the start of the directory name or no space: it's up to you as either will work. It is important to note that the directory you specify must be writeable by the user the MySQL server runs as. If it is not, you'll get an error like this:

NB:- applies to MySQL 4.1.x and/or MySQL 5.0.x.

source:- http://articles.techrepublic.com.com/5100-10878_11-5998264.html?tag=rbxccnbtr1

View query results in XML format

2009-04-05T10:33:00.001-07:00

You can have MySQL return query results to you in well-formed XML (instead of the traditional tabular format), by invoking the MySQL command-line client with the additional --xml option. This is useful if you plan to integrate the query output with some other application. Here's an example:

Listing A

shell> mysql --xml

NB:- applies to MySQL 4.1.x and/or MySQL 5.0.x.

source:- http://articles.techrepublic.com.com/5100-10878_11-5998264.html?tag=rbxccnbtr1

Create a more useful command prompt

2009-04-05T10:28:00.000-07:00

By default, the MySQL command-line client displays a simple mysql> prompt. However, you can use special modifiers to alter this prompt and make it more useful, by including the name of the currently-logged in user, the host name, and the currently-selected database. Here's how:
Listing G

mysql> prompt \U:/\d>
PROMPT set to '\U:/\d>'
root@localhost:/db1>

You can obtain a complete list of modifiers supported by the MySQL client from the
http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html

NB:- applies to MySQL 4.1.x and/or MySQL 5.0.x.

source:- http://articles.techrepublic.com.com/5100-10878_11-5998264.html?tag=rbxccnbtr1

Save disk space by compressing MySQL tables

2009-04-05T10:14:00.000-07:00

Takeaway: Databases with thousands of records can take up a lot of hard disk space. MySQL has built-in commands and utilities that can compress MyISAM tables.

The popular MySQL open-source RDBMS can handle tables containing hundreds of thousands of records without breaking a sweat. In fact, this scalability is one of the reasons it's becoming so popular with large organizations and their massive databases.

As you might imagine, however, a database with 100,000 records consumes a fair amount of disk space. And if you're a database administrator, you might have wondered if it was possible to somehow optimize MySQL's use of disk space when dealing with such databases.

Well, there is.

MySQL comes with a built-in tool, myisampack, to "pack", or compress, MyISAM tables (MySQL's default table format) so that they use less disk space. The myisampack command works equally well on small and large MyISAM tables, so even if you have a database with a few hundred records, you can use myisampack to compress it and save yourself a little disk space.

Step 1: Evaluate suitability

As with most good things, myisampack comes with one important caveat: tables compressed with it become read-only. This means that you can't use INSERT, UPDATE or DELETESQL commands to change the table, once compressed. To alter data in a compressed table, you will need to first uncompress it, execute SQL commands to change its contents, and then re-compress it.

Because of this, it's important to spend some time thinking about which of your application's tables should be compressed, and which should be left uncompressed. A good rule of thumb here is to only compress those MyISAM tables that contain non-volatile data, or data that won't change during the application life cycle. Examples of such data could be country and city listings, Web site category listings, application configuration defaults.

Step 2: Compress table(s)

Once you've decided which table(s) to compress, check them with myisamchk to ensure that they don't contain any errors:
shell> myisamchk movies.MYI
Checking MyISAM file: movies.MYI
Data records: 146 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

Note: If you discover errors at this stage, you can fix them by running myisamchk with the -r option.

Assuming no errors, proceed to compress the table(s) with myisampack. You will need to pass myisampack the path to the table's .MYI file, as in the example below:
shell> myisampackmovies.MYI
Compressing movies.MYD: (146 records)
- Calculating statistics
- Compressing file
41.05%
Remember to run myisamchk -rq on compressed tables

Once myisampack has finished compressing the table, it will print the compression percentage for the table.

Step 3: Rebuild table indices

Once the table(s) have been successfully compressed, you need to rebuild the table indices. The myisamchk command can do this too, when invoked with the -rq option.
shell> myisamchk -rqmovies.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table 'movies.MYI'
Data records: 146
- Fixing index 1

You should now verify that the compressed table is free of errors, again with myisamchk:
shell> myisamchk movies.MYI
Checking MyISAM file: movies.MYI
Data records: 146 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

Step 4: Reload the compressed table(s)

Once the table(s) have been successfully compressed, you need to tell MySQL about them and force the RDBMS to reload the compressed, read-only versions. This is easily accomplished by issuing the FLUSH TABLE command to MySQL using the command-line MySQL client, as follows:
mysql> FLUSH TABLE movies;
Query OK, 0 rows affected (0.05 sec)

You can now verify the table data with a SELECT query, as follows:
mysql> SELECT title FROM movies;
+---------------------+
| title |
+---------------------+
| Drive Me Crazy |
| The Insider |
| The Bachelor |
| The Bone Collector |
...
+---------------------+
4 rows in set (0.00 sec)

Notice that any attempt to change the compressed table—for example, with a DELETE query—will fail:
mysql> DELETE FROM movies WHERE id=146;
ERROR 1036 (HY000): Table 'movies' is read only

And you're done!

Note: To uncompress a MyISAM table packed with myisampack, invoke myisampack as in Step 2 and pass it the additional --unpack option. Here's an example:

http://articles.techrepublic.com.com/5100-10878_11-5852557.html

enabling/disabling query log without restart in mysql

2009-04-05T01:14:00.000-07:00

set sql_log_off = ‘ON’;
turns of the log without restarting

set sql_log_off = ‘OFF’;
turns it back on

this works on 5.0, anyway

LOG and status files in mysql

2009-04-05T01:01:00.000-07:00

The general query log contains a record of when clients connect and disconnect, and the text of every single query received by the server(whether or not it was processed successfully). This log is useful for determining the frequency of a given type of a statement or for troubleshooting queries that are not otherwise logged.

The binary log contains a record of queries that update data. it is stored in binary format, but its content can be viewed usin g the mysqlbinlog utility. the server logs only successful queries to this file, and only queries that modify data.(For ex, the server logs DELETE statements to the binary log, but not select statements ) the binary log is used for communication between master and slave replication servers. it can also be used in data recovery.

the slow query log contains the text queries that take a long time to execute, as well as information about their execution status. By Default, a long time is more than 10 seconds. this can be changed by setting the long_query_time server variable. if the server is started with the --log-long-format option, it also writes to the slow query log queries for which no index was used, whether or not queries took a long time to execute. the contents of the log file are helpful for identifying queries that should be optimized.

none of the preceeding logs are enabled by default. you must enable them explicitly using appropriate startup options, which are shown in the following tables. each option may be given in --option or --option=file_name form. if no filename is specified, the server uses a default name, as shown in the table

By default, each log file is created under the data directory unless you specify an absolute pathname. host_name stands for the server host name. nnn in the name means that the server writes a numbered series of logs, creating a new log each time the server starts up or the logs are flushed.

mysqlaccess

2009-04-04T09:13:00.000-07:00

mysqlaccess [host] user database [options]

Use this to check privileges of a user for a specific host and database. This utility is executed from the command line, so it can be useful, for instance, as a preliminary tool to check for user permissions before proceeding with a customized program that uses one of the APIs.
With regard to the syntax, the hostname is the first argument and is optional. If not given, localhost is assumed. The username given in the second argument is the name of the user for which the utility is checking privileges. The third argument is the database for which it is checking against. The fourth argument involves several possible options, one of which could be the username by which the utility will access the server to gather information on the user named in the second argument. Here is an example of how you might use this utility:

mysqlaccess localhost marie db1 -U russell -P

In this example, the utility is given the hostname, then the user for which I'm inquiring about, then the database name for which I want user privilege information. The -U option is used to specify the username with which to access the server to gather information. This user has full access to the mysql database. The -P instructs the utility to prompt me for a password. The results of the preceding inquiry is shown here:

First, a table is presented displaying the privileges for the combination of the database named, the host given, and the user. This user has only SELECT privileges.
Additionally, the results are given in raw form for each component. This user's privileges are the same for all databases and hosts (i.e., there are no entries in the db or the host tables in the mysql database), so there aren't any results for those particular components. For the user component, the command displays details without labels, but they are presented in the order that they are found in the user table in the mysql database. The third field is the password in the encrypted format that it stored. The Ys and Ns are the settings for each user privilege.
Several options are available with this utility. Here is an alphabetical list:
--brief, -b
Provides a brief display of results from an inquiry.
--commit
Copies grant rules from temporary tables to the grant tables.
--copy
Reloads temporary tables with original data from the grant tables so that privileges take effect.
--db= database, -d database
Explicitly specifies the database against which to query the user privileges.
--debug= level
Sets the debugging level. The choices are from 0 to 3.
--help, -?
Displays basic help information.
--host= host, -h host
Specifies the host on which to obtain privilege information. The localhost is the default.
--howto
Displays basic examples of usage with sample results.
--old-server
Stipulates that the server to which the utility is connecting is running an older version of MySQL (prior to 3.21) requiring a different method with regard to WHERE clauses in SQL statements.
--password= password, -p password
Provides the password of the user logging into the server, not the user on which to check for privileges.
--plan
Displays plans for further development of the utility by its developers.
--preview
Displays the differences in temporary grant tables before they are committed.
--relnotes
Displays notes on each release of the utility.
--rhost= host, -H host
If the utility is not being run on the same server as the MySQL server that's being queried, use this option to specify the address of the MySQL server to query.
--rollback
Undoes the last change to user privileges.
--spassword= password, -P password
Provides the password when using a superuser.
--superuser= user, -U user
Provides a superuser's username.
--table, -t
Instructs the utility to display data in an ASCII table format.
--user= user, -u user
Provides the username for logging in to the server, not the user on which to check for privileges.
--version, -v
Displays the version of the utility.

mysqlhotcopy database [path]

2009-04-04T09:11:00.000-07:00

mysqlhotcopy database [path]

Use this to make backup copies of databases while the server is active. It only works on MyISAM and ISAM tables, though. It makes a simple copy of each database directory and each table file. This results in a separate directory for each database and usually three files for each table: one for the schema, another for the data, and a third for the index. It places a read lock on all of the tables in the database while copying them. Here is an example of how you can copy a database with mysqlhotcopy:
mysqlhotcopy -u russell -p password db1 /tmp/backup

Note that unlike other MySQL utilities, there is a space between the -p and the password. Next the database (db1) is specified. Finally, the path to write the backup directories is given. To restore databases or tables that were copied by mysqlhotcopy, just copy the table files to be restored to their original data directories. Here are the following options:

--addtodest
Instructs the utility not to abort the session or to rename the backup directory, but to add new files to the directory.

--allowold
Instructs the utility to rename an existing backup directory to *_old so that the copying may be completed. If the new copy is successful, the old directory is deleted. If it's unsuccessful, the old directory is restored.

--checkpoint= database.table
Instructs the utility to save logging information to the named database and table.

--debug
Used to enable debugging information.

--dryrun, -n
This option has the utility test the backup process without actually making a copy.

--flushlog
Instructs the utility to flush logs after all tables are locked.

--help, -?
Displays basic help information.

--keepold
Instructs the utility when using the --allowold option not to delete the old directory if the copying is successful.

--method= method
Sets the method used by the utility for copying files. The choices are cp or scp.

--noindices
Instructs the utility to copy only the headers of index files. Indexes may be rebuilt when restoring copies.
--password= password, -p password

Provides the password to pass to the server. A space is permitted after the -p option, before the password.
--port= port, -P port

Specifies the port number to use for connecting to the server.
--quiet, -q

Suppresses all messages except for error messages.
--regexp= expression

Provides a regular expression for determining which databases to copy based on the name.
--resetmaster

Instructs the utility to execute a RESET MASTER statement after tables are locked.
--resetslave

Instructs the utility to execute a RESET SLAVE statement after tables are locked.
--socket= filename, -S filename

Provides the path and name of the server's socket file.
--sufix= string

Specifies the suffix for the copies of databases. The default is _copy.
--tmpdir= path

Specifies the temporary directory to use. The default is /tmp.
--user= user, -u user

Specifies the username for connecting to the server.

mysqlbug

2009-04-04T09:07:00.000-07:00

This is a script for reporting bugs to MySQL AB developers. Executed at the command line of the server, this script gathers information on the version of MySQL and related libraries installed, the operating system, as well as how MySQL was compiled.

To run the utility, simply type the command without any options or arguments. After a few moments a text editor (e.g., Emacs) will be started with a form for reporting the bug. Several of the details will be filled in with information gathered by the script. You can modify this information, and you are expected to answer questions about the bug discovered. This includes a description of how to reproduce the problem or what circumstances occurred that may have caused or contributed to the problem. If you discovered a workaround solution, report this, as well. The report created (saved in the /tmp directory on Unix systems) should be emailed to dev-bugs@mysql.com. Go to http://bugs.mysql.com to report bugs online.

using files from within the mysql command line

2009-04-02T23:52:00.001-07:00

mysql> source test.sql

delete vs truncate operations in mysql

2009-04-02T23:50:00.000-07:00

delete from sales;
Query OK, 6 rows affected (0.00 sec)

now we re insert records to the table

truncate sales;
Query OK, 0 rows affected (0.00 sec)

delete informs you how many rows have been removed,but TRUNCATE doesn't;TRUNCATE just removes the lot without counting them.it actually does this by dropping and re-creating the table;

if a table contains an AUTO_INCREMENT column, emptying it completly with either TRUNCATE TABLE or a DELETE that includes no WHERE cluase might have the effect of resetting the sequence. in this case, the next record inserted into the table is assigned an AUTO_INCREMENT value of 1. if this side effect is undesirable, empty the table using a WHERE clause that will always evaluates to true
ex:- DELETE FROM table_name WHERE 1;

How to get the list of tables in mysql from a particular database, based on engine type(MyISAM,InnoDB,MERGE,HEAP,BDB etc) ?

2009-04-02T22:21:00.000-07:00

SHOW TABLE STATUS FROM [name of database];

This also shows other information that might be of interest.ex :- it displays when a table was created or last modified

or better yet

$ mysql -u username -p -e "SHOW TABLE STATUS FROM database_name" | awk '{print $1, $2}'|column -t

source sujoy

SELECT DISTINCT `TABLE_NAME`FROM information_schema.tables
WHERE table_schema = "medvista"
AND ENGINE = "MyISAM";

mysql command line history

2009-04-02T19:50:00.000-07:00

See the file ~/.mysql_history

see:- http://www.cyberciti.biz/faq/howto-clear-mysql-command-history/

example for enum column type in mysql

2009-04-02T19:48:00.000-07:00

alter table complaints
change range
rage enum('polite','aggrieved','irate','furious','abusive') not null;

various table types in mysql

2009-04-02T02:30:00.000-07:00

MyISAM tables
--------------

MyISAM data files are given the extension .MYD and the indexes have the extension .MYI

there are three types of MyISAM tables: static,dynamic or compressed, each table is
represented in the disk by an .frm format file

static tables are the default format, which exists if there are no varchar,blob or text format

supports FULLTEXT searching

can be converted into fast,compressed , read-only tables

can be used to set up MERGE tables

HEAP tables
-----------

HEAP tables are the fastest table types because they are stored in memory and use a hashed index.

the downside is that, because they are stored in memory, all the data will be lost in the case of a crash.

HEAP tables are often used to give faster access to an already existing table - to leave the orginal table for inserting and updating and then have the new table for fast reading.

because heap tables use memory , the tables are always limited by the mysqld variable max_heap_table_size

they cannot be used for order by
they only use whole key tosearch for a row , not parts of key
do not support AUTO_INCREMENT column
do not support BLOB or TEXT columns

besides the max_heap_table_size limit and the memory limit on your machine, a limit of 4GB per table could be reached on some setups because this is the limit imposed by a 32-bit machines address space

InnoDB tables
-------------

InnoDB tables are a transaction safe table type(this means you have COMMIT and ROLLBACK capabilities).

InnoDB uses row level locking so that only the row is locked.

for performance purposes you should use InnoDB tables if your data performs large numbers of INSERTS or UPDATES relative to SELECTS

BDB tables
------------

it is a transaction capable table type

Ex:-
create table bdbtest(f1 INT, f2 CHAR(10))TYPE=BDB;

MERGE tables
------------

MERGE tables are amalgamations of identical MyISAM tables.you'd normally use them only when your MyISAM tables are getting too big.

more speed in some situations(you could split different tables onto different disks, and use a MERGE table to access them as one table)

create table sales_rep1(
id INT AUTO_INCREMENT PRIMARY KEY,
employee_number INT(11),
surname VARCHAR(40),
first_name VARCHAR(30),
commission TINYINT(4),
date_joined DATE,
birthday DATE
)TYPE=MyISAM;

create table sales_rep2(
id INT AUTO_INCREMENT PRIMARY KEY,
employee_number INT(11),
surname VARCHAR(40),
first_name VARCHAR(30),
commission TINYINT(4),
date_joined DATE,
birthday DATE
)TYPE=MyISAM;

create table sales_rep1_2(
id INT AUTO_INCREMENT PRIMARY KEY,
employee_number INT(11),
surname VARCHAR(40),
first_name VARCHAR(30),
commission TINYINT(4),
date_joined DATE,
birthday DATE
)TYPE=MERGE
UNION=(sales_rep1,sales_rep2);

mysql> insert into sales_rep1 (surname,first_name,commission,date_joined,birthday) values ('Tshwete','Paul',15,'1999-01-03','1970-03-04');
Query OK, 1 row affected (0.01 sec)

mysql> insert into sales_rep2 (surname,first_name,commission,date_joined,birthday) values ('Grobler','Peggy-Sue',12,'2001-11-19','1956-08-25');
Query OK, 1 row affected (0.00 sec)

mysql> select first_name, surname from sales_rep1_2;
+------------+---------+
| first_name | surname |
+------------+---------+
| Paul | Tshwete |
| Peggy-Sue | Grobler |
+------------+---------+
2 rows in set (0.00 sec)

when creating MERGE table , you can specify which table to insert to,

Ex:-

create table sales_rep1_2(
id INT AUTO_INCREMENT PRIMARY KEY,
employee_number INT(11),
surname VARCHAR(40),
first_name VARCHAR(30),
commission TINYINT(4),
date_joined DATE,
birthday DATE
)TYPE=MERGE
UNION=(sales_rep1,sales_rep2)
INSERT_METHOD = LAST

the INSERT_METHOD can be NO,FIRST, or LAST

mysqlcheck

2009-04-01T23:47:00.000-07:00

syntax:-
mysqlcheck [options] databasename [tablenames] or
mysqlcheck [options] --databases databasename1 [databasename2, databasename3,....] or
mysqlcheck [options] --all-databases

analyzing tables with mysqlcheck
--------------------------------

the server needs to be running for you to use mysqlcheck, and it works only with MyISAM tables

to use it to analyze tables, youn use the -a option

ex:- mysqlcheck -a mydb Persons ajdb users -u root -p;

repairing tables with mysqlcheck
--------------------------------

to repair tables use the -r option

mysqlcheck -r databasename tablename[tablenames] -u username -p password;

checking and repairing MyISAM tables:-

a recommended table-checking strategy is to run mysqlcheck with no options . if any errors occur, run it again, first with the --repair and --quick options to attempt a quick repair.
if that fails, run mysqlcheck with --repair for a normal repair, and then if necessary with --repair and --force

benchmarking function in mysql

2009-04-01T23:07:00.001-07:00

the BENCHMARK() function tells you how long it takes MySQL to perform a function a certain no: of times. it can help to give you a basic idea of the
difference in capabilities between machines.

the syntax is as follows:-

SELECT BENCHMARK(no: of repetitions,expression);

Ex:- select BENCHMARK(1000000,SQRT(999));

Warning:- you should use BENCHMARK() with caution to compare machines. for a live database, there are many other factors , such as disk speed, which are to be taken into account with this test. its main aim is to help in the optimization of functions.

Disabling Network Access in mysql

2009-04-01T05:40:00.001-07:00

skip-networking

To disable remote access via TCP/IP, add skip-networking to my.cnf, or start mysqld using the --skip-networking switch. MySQL will then only accept connections from the local host via a socket.
If network access is disabled, users are not able to connect to MySQL from remote hosts even if they have a user account that would otherwise allow them to do so.

If you do allow remote access to MySQL, any firewall that sits between your MySQL server and the outside world must allow traffic on the appropriate port. By default, this is port 3306.

Mysql table terminology

2009-04-01T05:02:00.000-07:00

The components of a MySQL table are

1. A table is a structured collection of data, which is composed of...

2. a body, that contains rows of data and...

3. a table definition that contains columns that describe the type of data contained in the rows.

4.

Each column has a name (such as book_id) and a type (such as INTEGER). Each type is a defined set of values (like integers or letters in the alphabet). If data is of a given type, it must be one of the values from that type (for example, a column with type INTEGER must contain a whole number value such as 1 or 6502, not a string value such as one or a decimal number such as 3.14).

5.

Each row contains one value for every column in the heading. The values must be of the type defined in the column to which they correspond. The column book_id is of type SMALLINTonly values of that type may be stored in that position in the row.

6. The number of columns a relation has defines its degree.

7. The number of rows a relation has defines its cardinality.

Corrupted Table Data in Mysql

2009-04-01T04:48:00.000-07:00

CHECK TABLE table;
REPAIR TABLE table;

Occasionally the MySQL data files might become corrupt and need repairing. The easiest way to check the integrity of a table is by using the CHECK TABLE SQL command. The following command checks the book table for errors:

CHECK TABLE book;

If the output looks similar to the following, the table is okay and does not need any further action:

Sometimes, however, you see errors in the Msg_text column telling you about problems with the table. When this happens, issue the REPAIR TABLE command, and MySQL attempts to fix the problem.
The myisamchk utility that ships with MySQL can be used to check and repair MyISAM tables with a higher degree of flexibility than the SQL commands. When myisamchk is run with no options, it simply checks a table for errors. A number of command line switches can be added to gain additional information or instruct the utility to begin a repair process.

The MyISAM table files that myisamchk examines are stored in your data directory and have .MYI file extensions. If your data directory is /var/lib/mysql, you can check all the tables in the sampdb database using the following command:

myisamchk /var/lib/mysql/sampdb/*.MYI

The usual way to perform a quick table check is to use the --fast switch, which checks only tables that have not been properly closed.

The --medium-check option performs a more thorough check of the tables and finds the vast majority of errors. The --extend-check option is the most thorough option but is very slowonly use this if --medium-check does not find a problem. After you have determined that a table file is corrupt, you can repair it using the --recover switch. Before repairing a table with myisamchk, you should stop mysqld you do not want the MySQL server writing to your table files while you repair them.

Sometimes myisamchk reports that it cannot fix a problem using this option and then you should try using --safe-recover instead. This option is much slower but also more thorough.

source scott grannemann

Server Crash in mysql

2009-04-01T04:46:00.000-07:00

If your MySQL server keeps crashing, it's time to file a bug report. Even though MySQL is thoroughly tested, there might still be rare bugs, and you should know how to begin to diagnose this kind of problem.

First, you should make sure that it is actually the MySQL server that is crashing and not your client program. Check the amount of time that the server has been up by issuing the mysqladmin version command. The output contains a line like this:

Uptime: 109 days 23 hours 31 min 51 sec

In this case, the server has been running for a long time, so it is clear that mysqld did not crash. Instead, you need to debug your client program.

If the uptime reported is very small or mysqld is not running at all after a suspected crash, the first step is to check that your tables are not corrupt. Stop mysqld and run the following command from the data directory to check every table in the system:

myisamchk --silent --force */*.MYI

Restart mysqld with the --log switch, or add log to the [mysqld] section of my.cnf. Every SQL command is logged to a file, and you should try to find a way to reproduce the crash consistently using the same command. You should then query the bugs database at http://bugs.mysql.com/ before filing a bug report.

Another possible cause of your crashes is an underlying hardware problem. In particular, faults with your system's RAM or hard drives can cause peculiar errors in MySQL. Perform a thorough check on your system's hardware if you experience intermittent, unexplained server crashes.

source scott grannemann

Common Errors

2009-04-01T04:39:00.000-07:00

This section lists some of the most common error messages you will encounter and the steps necessary to work out what is wrong.
Can't Connect to MySQL Server
ERROR 2002: Can't connect to local MySQL server

through socket '/var/lib/mysql/mysql.sock' (2)
ERROR 2003 (HY000): Can't connect to MySQL server
on '123.45.67.89' (113)

Usually these errors mean that there is no mysqld process running on the server machine. If you are unable to connect to a local MySQL server, check your system's process list using ps on Unix or Task Manager on Windows and start the MySQL server if it is not running.It's possible that the local socket file has been removed. The default location is /tmp/mysql.sock, and some systems periodically clean out the contents to /tmp. It could even be that another user has deleted this fileinadvertently or otherwisebecause the /tmp directory is often writable by all users. If you experience this issue, change the location of the socket file to another directory owned by the mysql user by adding these lines in my.cnf:

[mysqld]
socket=/path/to/mysql.sock
[client]
socket=/path/to/mysql.sock

To check that mysqld is listening on the port you are expecting, use the netstat command to check that it is indeed accepting network connections. On Unix/Linux use this command:

shell> netstat l

On Windows, the command is slightly different:

> netstat a | find "LISTENING"

If you are unable to connect to a remote MySQL server and you are sure mysqld is running, check your firewall settings. You must allow TCP/IP traffic on port 3306or whatever other port the server runs onto allow remote connections.

Note that this error message only indicates an incapability to communicate with a MySQL server, not an authentication problem. If you are able to connect to mysqld but use an invalid password, you see one of the errors from the next section.
Access Denied

ERROR 1045 (28000): Access denied for user

'user'@'host' (using password: YES)

You might have supplied an incorrect username or password, or a database name that the given username does not have access to when connecting from the current host. Check the privilege tables in the mysql database to make sure the connection arguments are correct.If you use the short p switch instead of password, remember that the password must follow immediately with no space. Otherwise the argument following p is treated as the database name.

Too Many Connections, ERROR 1040: Too many connections

The maximum number of simultaneous connections allowed is defined in the max_connections system variable, and the default value is 100. A new value can be set in my.cnf using the following lines:

[mysqld]
max_connections=200

Increasing the max_connections value is usually not the answer. MySQL has exceeded the maximum number of connections for a reason, and you should try to find out why it ran out. You should only increase this value if you are sure your system can support more connections.
In fact, mysqld allows one more connection than the specified number, so a superuser can connect even when the maximum connections have been exceeded. To diagnose problems, run the mysqladmin processlist command and try to work out why there are so many connections.
MySQL Server Has Gone Away

ERROR 2006: MySQL server has gone away

This error usually indicates a timeout where mysqld has closed the connection with the client. The default timeout interval is eight hours, but this value can be changed using the wait_timeout system variableits value is in seconds. When this error is the result of a timeout, the mysql program attempts to reconnect automatically.
You also see this error if the mysqld process stops running while you are connected to a MySQL servereven if it is restarted instantly. If mysql is unable to reconnect, you should check whether mysqld is still running.

Got Error from Table Handler, Error 1030: Got error 141 from table handler

This error indicates an internal problem with the database table storage file. It can usually be fixed using the myisamchk utility. The error number reported in this message indicates the nature of the problem. Use the perror command from the shell to find the corresponding error message.

shell> perror 141
141 = Duplicate unique key or constraint on write
or update

When you encounter this kind of error, you should stop mysqld and run myisamchk --recover on the corresponding .MYI file. Sometimes the errors reported are symptomatic of other system issues, as in the following case:

$ perror 28
Error code 28: No space left on device

source scott grannemann

Mysql Getting Help

2009-04-01T04:33:00.001-07:00

If you have a troublesome problem and feel you need help to figure it out, you can look to several places. First of all, make sure you have read the relevant sections of the online manual at http://dev.mysql.com/doc/refman/5.0/en/index.html.

Several MySQL mailing lists cover a wide range of topics, and lists are available for regional user groups and different languages. Check http://lists.mysql.com/ for the latest mailing liststhere will almost certainly be one where you can ask for assistance.

MySQL AB provides technical support for an annual fee with various service levels for those who need the assurance of having somewhere to turn when things do go wrong. All support packages include access to the MySQL Knowledge Base, a search able library of technical articles that often gives you the answers you need very quickly.

The higher levels of support include 24hour, 7daysaweek telephone assistance, guaranteed emergency response time, and remote troubleshooting by a MySQL expert. For more information, see www.mysql.com/support/.

Performing Database Maintenance

2009-04-01T02:21:00.000-07:00

MySQL features a series of statements that can (and should) be used to ensure that databases are correct and functioning properly.
Here are some statements you should be aware of:

ANALYZE TABLE is used to check that table keys are correct. ANALYZE TABLE returns status information, as seen here:
• Input
ANALYZE TABLE orders;

• Output

CHECK TABLE is used to check tables for a variety of problems. Indexes are also checked on a MyISAM table. CHECK TABLE supports a series of modes for use with MyISAM tables. CHANGED checks tables that have changed since the last check, EXTENDED performs the most thorough check, FAST only checks tables that were not closed properly, MEDIUM checks all deleted links and performs key verification, and QUICK performs a quick scan only. As seen here, CHECK TABLE found and repaired a problem:
• Input
CHECK TABLE orders, orderitems;

• Output

If MyISAM table access produces incorrect and inconsistent results, you might need to repair the table using REPAIR TABLE. This statement should not be used frequently, and if regular use is required, there is likely a far bigger problem that needs addressing.

If you delete large amounts of data from a table, OPTIMIZE TABLE should be used to reclaim previously used space, thus optimizing the performance of the table.

source Ben forta

How to run a single host command without exiting mysql ?

2009-04-01T02:04:00.000-07:00

use the \! command. The following example runs the system command pwd on a UNIX/Linux system to determine the path of the current working directory:

mysql> \! pwd
/home/chris/public_html

Capturing Output from MySQL

2009-04-01T02:03:00.000-07:00

If you want to divert the output of a SQL query to a log file, use the \T command along with a filename. For instance, to write the output of a query to query.txt, do the following:

mysql> \T output.txt
Logging to file 'query.txt'

how to show connection status in mysql?

2009-04-01T02:01:00.000-07:00

The \s command returns the status of the current database connection, including the current database name and the connection parameters.

mysql> \s;
--------------
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Connection id: 10
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 4 hours 1 min 39 sec

System-Related Functions in mysql

2009-04-01T00:35:00.000-07:00

CURRENT_USER(), SESSION_USER(), SYSTEM_USER(),and USER() Functions

The CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), and USER() functions all return the username and the hostname under which the current session is authenticated. In each case, the functions take no arguments. For example, the syntax for the USER() function is as follows:

USER()

You can use the functions in any SELECT statement to return the username and hostname, as shown in the following example: SELECT USER();

The value returned by these functions is in the form of @, such as root@localhost.

Note that, under some circumstances, the CURRENT_USER() function can sometimes return a value different from the other three functions. For example, if a client specifies a username, but the client is authenticated anonymously, a difference can occur. For more information about these functions, see the
MySQL product documentation.

How to get current connection id in mysql?

SELECT CONNECTION_ID();

How to retrieve the last value that was inserted in an AUTO_INCREMENT column (in mysql) ?.

SELECT LAST_INSERT_ID();

how get information on global variables ?

SHOW GLOBAL VARIABLES;

How to use the SHOW VARIABLES Statement to Retrieve Server System Variable Settings ?

SHOW [GLOBAL | SESSION] VARIABLES [LIKE ‘< value >’]

how to lock and unlock tables in mysql

2009-04-01T00:09:00.001-07:00

To lock a table in a MySQL database, you should use the LOCK TABLES statement, as shown in the following

syntax:

LOCK {TABLE | TABLES}
< table name > [AS < alias >] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[{, < table name > [AS < alias >] {READ [LOCAL] | [LOW_PRIORITY] WRITE}}...]

you must specify a READ lock type or a WRITE lock type. If you specify READ, any connection can read from the table, but no connection can write to the table

If you specify READ LOCAL, nonconflicting INSERT statements can be executed by any connection

If you specify WRITE, the current connection can read or write to the table, but no other connections can access the table until the lock has been removed. If you specify LOW_PRIORITY WRITE, other connections can obtain READ locks while the current session is waiting for the WRITE lock.

Once you lock a table, it remains locked until you explicitly unlock the table with the UNLOCK TABLES statement (described in the text that follows) or end your current session.

Ex:- LOCK TABLE Books READ;
(places a lock on the Books table)

LOCK TABLES Books READ, BookOrders WRITE;
(places locks on the Books and BookOrders tables:)

The UNLOCK TABLES Statement
----------------------------

To unlock one or more locked tables, you must use the UNLOCK TABLES statement, shown in the following syntax:
UNLOCK {TABLE | TABLES}

As you can see, you must specify the UNLOCK keyword along with the TABLE or TABLES keyword. As with the LOCK TABLES statement, the TABLE and TABLES keywords are synonymous, which means that you can use either one, regardless of the number of tables that you’re unlocking.

One thing to notice about the UNLOCK TABLES statement is that no table names are specified. When you use this statement, all tables that have been locked from within the current session are unlocked.

For example, to unlock the Books and BookOrders tables, you would use a statement similar to the following:
UNLOCK TABLES;

If only one table is locked, you can still use this statement, although you can also use the TABLE keyword,rather than TABLES. Either way, any locked tables are now unlocked.

Locking Your Nontransactional Tables

Follow these steps to complete this exercise:

1.

Open the mysql client utility, type the following command, and press Enter:
use test
You should receive a message indicating that you switched to the test database.

2.

To demonstrate how to lock a table, you should first create the table in the test database. Execute the following SQL statement at the mysql command prompt:
CREATE TABLE t1 (ID INT NOT NULL);
You should receive a message indicating that the statement executed successfully and that the statement affected no rows.

3.

Now you can specify a READ lock on the t1 table. Execute the following SQL statement at the mysql command prompt:
LOCK TABLE t1 READ;
You should receive a message indicating that the statement executed successfully, affecting no rows.

4.

Next, you should try to insert a row in the t1 table to demonstrate that placing a READ lock prevents you from adding data to the table. Execute the following SQL statement at the mysql command prompt:
INSERT INTO t1 VALUES (101);

You should receive a message indicating that the table has been locked with a READ lock and cannot be updated.

5.

Now you can specify a WRITE lock on the t1 table. Execute the following SQL statement at the mysql command prompt:
LOCK TABLE t1 WRITE;
You should receive a message indicating that the statement executed successfully and that the statement affected no rows.

6.

Next, you should insert a row in the t1 table to demonstrate that placing a WRITE lock does not prevent you from adding data to the table. Execute the following SQL statement at the mysql command prompt:
INSERT INTO t1 VALUES (101);
You should receive a message indicating that the statement executed successfully, affecting one row.

7.

Now you should unlock the table. Execute the following SQL statement at the mysql command prompt:
UNLOCK TABLES;
You should receive a message indicating that the statement has executed successfully and that the statement affected no rows.

8.

Finally, you should remove the table from the test database. Execute the following SQL statement at the mysql command prompt:
DROP TABLE t1;
You should receive a message indicating that the statement executed successfully, affecting no rows.

source:- robert sheldon & geoff moes

mysql database

2009-03-29T04:44:00.001-07:00

there is a database named mysql in which all the information about users and their privileges are stored.

table in mysql

db database specific privileges
host
user for global privileges
columns_priv
tables_priv table specific privileges

select user,db from db;

Regular expression examples

2009-03-29T04:42:00.001-07:00

Regular expressions:-

select 'hello' regexp 'e';
select 'hello' regexp '^e'; /*beginning*/
select 'hello' regexp 'o$'
select 'hello' regexp '^hello$'
select 'hello' regexp '^h...o$'
select 'hed3rd3r34edwecfo' regexp '^h.+o$' /* + means one or more characters */
select 'hssso' regexp 'hs{3}o$ '
select 'hssso' regexp 'hs{3,10}o$ '
select 'hello' regexp '^h[el]+o$ '
select 'hello' regexp '^h[el]*o$ '
select 'hello' regexp '^h[el]{3}o$ '
select 'hello' regexp '^h[el]{3,5}o$ '
select 'fnefnefeklfefhehdvgwcsgv' regexp '^[a-z]+$'
select 'fnefnefeklfefhehdvgwcsgv' regexp '^[^m]+$' /* ^ within [] means match any character apart from that follows it*/
'aarklon@gmail.com' regexp '^[A-Za-z0-9_]+@[A-Za-z0-9\-]+\.[A-Za-z0-9.\-]+$'

Ex:- select name from recipes where name regexp 'veg|carrot|cabbage' and not regexp 'coconut';

Host 'host_name' is blocked

2009-03-26T04:50:00.000-07:00

If you get the following error, it means that mysqld has received many connect requests from the host 'host_name' that have been interrupted in the middle:

Host 'host_name' is blocked because of many connection errors.Unblock with 'mysqladmin flush-hosts'

The number of interrupted connect requests allowed is determined by the value of the max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement. See Section 5.1.3, “Server System Variables”.

By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:

shell> mysqld_safe --max_connect_errors=10000 &

If you get this error message for a given host, you should first verify that there isn't anything wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.

How to get the list of database engines available under mysql ?

2009-03-22T08:44:00.000-07:00

soln:- use the command SHOW ENGINES or show table types;

access-denied-for-user-rootlocalhost

2009-03-01T07:54:00.001-08:00

If you are getting access-denied-for-user-rootlocalhost messege while accessing mysql from the shell then make sure that you have done

su -

from the command prompt after that fire mysql even if you are getting that error messege then you need to reset the mysql password you can use following steps for it

first locate your mysql’s hostname.pid file then

kill `cat /mysql-data-directory/host_name.pid`
mysqld_safe –skip-grant-tables &
mysqladmin -u root flush-privileges password “newpassword”

New password will be the password that you want to set now.

NB:- warning untested

MySQL Change root Password

2009-03-01T07:52:00.000-08:00

Posted By vivek On January 2, 2006 @ 11:17 pm In AIX, BASH Shell, CentOS, Debian / Ubuntu, FreeBSD, HP-UX Unix, Linux, MySQL, Solaris-Unix, Suse, UNIX, Ubuntu Linux | 15 Comments

Q. How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?

A. Setting up mysql password is one of the essential tasks. root user is MySQL admin account. Please note that Linux / UNIX login root account for your operating system and MySQL root are different. They are separate and nothing to do with each other (indeed some admin removes root account and setup admin as mysql super user).
mysqladmin command to change root password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p oldpassword newpass

Enter password:
Change MySQL password for other user

To change a normal user password you need to type (let us assume you would like to change password for vivek):

$ mysqladmin -u vivek -p oldpassword newpass

Changing MySQL root user password using mysql sql command

This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user vivek:

1) Login to mysql server, type following command at shell prompt:

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user vivek:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use while using PHP or Perl scripting.

URL to article: http://www.cyberciti.biz/faq/mysql-change-root-password/

Recover MySQL root password

2009-03-01T07:47:00.000-08:00

Recover MySQL root password

Posted By nixcraft On April 18, 2006 @ 4:18 pm In Debian Linux, Linux, MySQL, OpenBSD, RedHat/Fedora Linux, Solaris, Suse Linux, Tips, Troubleshooting, UNIX, Ubuntu Linux | 59 Comments

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password

Step # 3: Connect to mysql server as the root user

Step # 4: Setup new root password

Step # 5: Exit and restart MySQL server

Here are commands you need to type for each step (login as the root user):
Step # 1 : [1] Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: [2] Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+ Done mysqld_safe --skip-grant-tables

Step # 6: [3] Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

Article printed from nixCraft: http://www.cyberciti.biz/tips

Warning: untested

mysql backup & restore

2009-03-01T07:46:00.001-08:00

backup

mysqldump -u root -p --all-databases > mybackupfile.dump

or

mysqldump -u root -p --all-databases | gzip > /tmp/mysql.dump.gz

Restore using mysql

If you have to re-build your database from scratch, you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.

Here's how you would restore your custback.sql file to the Customers database.

mysql -u sadmin -p pass21 Customers < custback.sql

Easy isn't it ? Here's the general format you would follow:

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

Now how about those zipped files? You can restore your zipped backup files by first uncompressing its contents and then sending it to mysql.

raw fast and easy offline back up method

1 stop the mysql server
/etc/init.d/mysqld stop

2 copy mysqls data files and directories. ex if your mysql data directory is /var.lib/mysql and you want to save it to /tmp/mysql-backup

cp -r /var/lib/mysql /tmp/mysql-backup

instead of cp you can use rsync,tar,gzip..etc

3 start the server again

/etc/init.d/mysqld start

Viewing a users privileges

2009-02-13T03:44:00.000-08:00

SHOW GRANTS;
SHOW GRANTS FOR user;

ex:-

SHOW GRANTS FOR 'siju'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for siju@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'siju'@'localhost' IDENTIFIED BY PASSWORD '*E0276DB476A226D16E63FCD535DC176528FA57D4' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Issuing SHOW GRANTS with no FOR clause shows the privileges for the current user. It is equivalent to

SHOW GRANTS FOR CURRENT_USER();

Setting passwords in Mysql

2009-02-13T03:38:00.000-08:00

SET PASSWORD = PASSWORD('password');
SET PASSWORD FOR user@host = PASSWORD('password');

When you are connected to MySQL as a regular user, use the first syntax to change your own password. If you connected as an administrator, you can change the password for another user as follows:

SET PASSWORD FOR zak@localhost =
PASSWORD('flibble');

In fact to change another user's password you must have the UPDATE privilege on the mysql.user table. The SET PASSWORD command is available in all versions of MySQL, but you can also modify passwords by changing values in the user table. The previous command is equivalent to the following UPDATE statement:

UPDATE user
SET Password = PASSWORD('flibble')
WHERE User = 'zak' AND Host = 'localhost';

Remember, you must issue a FLUSH PRIVILEGES command after making any changes to the user table. This is not required if you use the SET PASSWORD command.

myisamchk utility

2009-02-12T03:52:00.001-08:00

The myisamchk utility is designed to check and repair any data tables using the default MYISAM table format natively supported by mysql.
normally, myisamchk should be run as the mysql user created at installation from the directory in which the tables reside.inorder to check a database, do su mysql, change into the directory name corresponding the name of the database , and run myisamchk with one or more options suggested below

Ex:- myisamchk -e -r *.MYI

-c check the table for errors
-e perform an extended check
-r recover/correct errors found

How To Repair Corrupted MySQL Tables Using myisamchk ?

myisamchk /var/lib/mysql/*/*.MYI >> /tmp/myisamchk_log.txt

myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/mysql/user.MYI' is usable but should be fixed

sudo myisamchk -r /var/lib/mysql/mysql/user.MYI

- recovering (with sort) MyISAM-table '/var/lib/mysql/mysql/user.MYI'
Data records: 7
- Fixing index 1

You may get error message: clients are using or haven’t closed the table properly, if the tables are still getting used by your application and other tables. To avoid this error message, shutdown mysqld before performing the repair, if you can afford to shutdown the DB for a while. If not, use FLUSH TABLES to force mysqld to flush any table modification that are still in memory.

How to use myisamchk to get information about a table ?

sudo myisamchk -dvv /var/lib/mysql/mysql/user.MYI

MyISAM file: /var/lib/mysql/mysql/user.MYI
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2008-10-28 6:51:38
Recover time: 2009-02-13 6:34:10
Status: checked,optimized keys
Data records: 7 Deleted blocks: 0
Datafile parts: 7 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 536 Keyfile length: 2048
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Recordlength: 344

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 1 180 unique char packed stripped 0 1024 1024
181 48 char stripped 0

Field Start Length Nullpos Nullbit Type
1 1 180 no endspace
2 181 48 no endspace
3 229 41 no endspace
4 270 1
5 271 1
6 272 1
7 273 1
8 274 1
9 275 1
10 276 1
11 277 1
12 278 1
13 279 1
14 280 1
15 281 1
16 282 1
17 283 1
18 284 1
19 285 1
20 286 1
21 287 1
22 288 1
23 289 1
24 290 1
25 291 1
26 292 1
27 293 1
28 294 1
29 295 1
30 296 1
31 297 10 blob
32 307 10 blob
33 317 10 blob
34 327 4 no zeros
35 331 4 no zeros
36 335 4 no zeros
37 339 4 no zeros

All myisamchk options ?

myisamchk --help

source:- http://www.thegeekstuff.com/2008/09/how-to-repair-corrupted-mysql-tables-using-myisamchk/

mysqladmin utility

2009-02-12T03:49:00.001-08:00

This utility is the principal tool for quick mysql database administration. in addition to the usual parameters, the following commands are supported.

create
drop
password

reload reload the grant tables that control permissions
status provide the status of the server
shutdown shut down the server

variables

show the variables that control mysql operation and their current values

version

provide the version number of the server and how long it has been running

EXAMPLES

How to display all MySQL server system variables and the values?

mysqladmin -u root -p variables

How to display all the running process/queries in the mysql database?

mysqladmin -u root -p processlist

How to create a MySQL Database?

mysqladmin -u root -p create databasename

How to check mysql server is up and running ?

mysqladmin -u root -p ping
Enter password:
mysqld is alive

How do I find out what version of MySQL I am running?

mysqladmin -u root -p version
Enter password:

mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.51a-3ubuntu5.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 hour 38 min 43 sec

What is the current status of MySQL server?

user@ubuntu:~$ mysqladmin -u root -p status
Enter password:
Uptime: 6034 Threads: 1 Questions: 3 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.000

how to combine multiple mysqladmin commands together ?

mysqladmin -u root -p process status version

How to kill a hanging MySQL Client Process?

mysqladmin -u root -p processlist

This will give all the processes along with their IDs

now to kill a process

mysqladmin -u root -p kill PID

What is the safe method to shutdown the MySQL server?

mysqladmin -u root -p shutdown

How to Delete/Drop an existing MySQL database?

mysqladmin -u root -p drop databasename

mysqlshow

2009-02-12T03:47:00.000-08:00

This little utility can provide quick information about your mysql installation and its component databases

1) with no parameters, it lists all available databases

Ex:- mysqlshow -u root

restaurants
sample
tc_gen

2) with a database as a parameter, it lists the tables in that database
3) with both a database and a table name, it lists columns in that table

4) with a database, table and column , it lists the details of the specified columns

Ex:-

mysqlshow -u username -p password databasename table name field name

5)

mysqlshow -i -u username -p password databasename tablename

This will display the following information

* Name
* Engine
* Version
* Row_format
* Rows
* Avg_row_length
* Data_length
* Max_data_length
* Index_length
* Data_free
* Auto_increment
* Create_time
* Update_time
* Check_time
* Collation
* Checksum
* Create_options
* Comment

mysqlimport

2009-02-12T03:46:00.000-08:00

The mysqlimport command is used to read large quantities of data from input file. the usual command specific parameters are filename and a database. generally you will be reading in a file created by mysqldump.but it is possible to read in a file created by mysqlimport as well.

does anyone know of a quick piece of code to check my MySQL version?

2009-02-04T20:54:00.000-08:00

To determine the version of the MySQL server running on host ‘elephant’ use:

mysql -h elephant -V
mysql Ver 12.21 Distrib 4.0.14, for pc-linux (i686)

user@ubuntu:~$ mysql -h localhost -V
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

source:- http://www.tech-recipes.com/rx/272/determine-the-version-of-mysql-server/

currently logged in users

2009-02-04T18:22:00.000-08:00

suppose i am logged in the mysql server with full administrative privileges now what is the command to get the list of all currently logged in users ? just like we type w command in Linux/UNIX ?

Jay alverson

SHOW PROCESSLIST

Syntax: SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

ex: mysql> SHOW FULL PROCESSLIST\G

see: http://dev.mysql.com/doc/#refman

how to create new user in mysql

2009-01-29T03:22:00.000-08:00

zodiac@zodiac-desktop:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all privileges on *.* to soumya@localhost identified by 'budhoos' with grant option;
Query OK, 0 rows affected (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to soumya@'%' identified by 'budhoos' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant reload,process on *.* to admin@localhost;
Query OK, 0 rows affected (0.00 sec)

grant all privileges on *.* to soumya@'%' identified by 'budhoos' with grant option;

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

OR

create user 'myuser'@'localhost';
set password for 'myuser'@'localhost' = password('myuserpassword');

see:- http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

How to repair a table in mysql ?

2009-01-23T01:59:00.000-08:00

REPAIR TABLE table name;
REPAIR TABLE table name QUICK;
REPAIR TABLE table name EXTENDED;

if QUICK is given mysql will do a repair of the index tree.
if EXTENDED is given, it will create index ROW by ROW

NB:- warning untested

Left Outer Join

2008-11-09T07:23:00.001-08:00

Access Your MySQL Server Remotely Over SSH

2008-11-07T01:28:00.000-08:00

So you've got MySQL on your web server, but it's only opened to local ports by default for security reasons. If you want to access your database from a client tool like the MySQL Query Browser, normally you'd have to open up access from your local IP address… but that's not nearly as secure.

So instead, we'll just use port-forwarding through an SSH tunnel, so your MySQL client thinks it's connecting to your localhost machine, but it's really connecting to the other server through the tunnel.

If you are using the command line ssh, the command would look like this. (You can do the same thing graphically in Putty or SecureCRT options if you need to)

ssh -L 3306:localhost:3306 geek@webserver.com

The syntax is ssh -L hostname @. We're using localhost as the hostname because we are directly accessing the remote mysql server through ssh. You could also use this technique to port-forward through one ssh server to another server.

If you already have mysql running on your local machine then you can use a different local port for the port-forwarding, and just set your client tools to access MySQL on a different port.

Warning :- untested

Renaming an Oracle table column

2008-10-18T23:53:00.001-07:00

What is a name? Plenty, if you ask many Oracle developers. Back in the early days of Oracle7 it was a very cumbersome procedure to rename a column within a table, and some brave DBA's would go into the data dictionary and re-name the column directly!

In Oracle 9i and above we have a simple rename column command that makes it easy to rename any table column. Internally, the rename column syntax adjusts the Oracle data dictionary only, since the column names are not stored directly inside the segment itself:

SQL> desc sales

Name Null? Type
---------------- -------- ---------------
STORE_KEY VARCHAR2(4)
BOOK_KEY VARCHAR2(6)
ORDER_NUMBER VARCHAR2(20)
ORDER_DATE DATE

SQL> alter table sales rename column order_date to date_of_order;

SQL> desc sales

Name Null? Type
---------------- -------- ---------------
STORE_KEY VARCHAR2(4)
BOOK_KEY VARCHAR2(6)
ORDER_NUMBER VARCHAR2(20)
DATE_OF_ORDER DATE

Of course, you must remember that PL/SQL and SQL that referenced the renamed column must be re-compiled.

http://www.dba-oracle.com/t_rename_column_table.htm

data warehousing - top-down

2008-08-12T04:59:00.000-07:00