Creating a User Without a Password in MySQL

I'm not sure why you would want to do this but you can create a MySQL user without a password:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9972

Server version: 5.0.67 Source distribution

 

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

 

mysql> create user barney;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select user, host, password

    -> from mysql.user

    -> where user = 'barney';

+--------+------+----------+

| user   | host | password |

+--------+------+----------+

| barney | %    |          |

+--------+------+----------+

1 row in set (0.00 sec)

 

mysql> exit

Bye

UNIX >

 

A user without a password can logon without using –p:

 

UNIX > mysql -u barney

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9981

Server version: 5.0.67 Source distribution

 

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

 

mysql> exit

Bye

UNIX >

 

And if he does include a –p, he can just press Enter when prompted to supply a password:

 

UNIX > mysql -u barney -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9982

Server version: 5.0.67 Source distribution

 

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

 

mysql> exit

Bye

UNIX >

 

Dropping a user without a password is carried out in the usual way:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10016

Server version: 5.0.67 Source distribution

 

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

 

mysql> drop user barney;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

Renaming a Column in MySQL

You can rename a column as shown below. You need to include the column’s definition, even if you are not changing it, otherwise MySQL displays an error:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.09 sec)

 

mysql> desc andrews_table;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| col1  | varchar(5) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.01 sec)

 

mysql> alter table andrews_table

    -> change col1 col2 varchar(5);

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| col2  | varchar(5) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.06 sec)

 

mysql>

 

And you can change a column’s definition at the same time, if you wish:

 

mysql> alter table andrews_table

    -> change col2 col3 varchar(10);

Query OK, 0 rows affected (0.18 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col3  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)

 

mysql>

Modifying Column Definitions in MySQL

If you change a column definition, MySQL converts the data in that column for you. If it finds data which it cannot convert, it displays a warning and you can see details by using the show warnings command. In the example, a varchar column is converted to int. MySQL is unable to convert the value ABC so it sets it to zero instead:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into andrews_table

    -> values ('ABC');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table

    -> values ('123');

Query OK, 1 row affected (0.00 sec)

 

mysql> alter table andrews_table

    -> change col1 col1 int;

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

(N.B. I have reformatted the output from the show warnings command to make it fit on the page.)

mysql> show warnings;

+---------+------+-----------------------------------+

| Level   | Code | Message                           |

+---------+------+-----------------------------------+

| Warning | 1366 | Incorrect integer value:          |
|         |      | 'ABC' for column 'col1' at row 1  |

+---------+------+-----------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_table;

+------+

| col1 |

+------+

|    0 |

|  123 |

+------+

2 rows in set (0.00 sec)

 

mysql>

Arithmetic in MySQL

Here are some examples of arithmetic in MySQL. They are fairly self explanatory but there are a few points to note:

1. You can select pi directly.
2. The square root of -2 gives the answer -NaN. NaN stands for Not a Number.
3. The order of precedence seems to be fairly standard and you can alter it by using brackets.

mysql> select 5+3/2, (5+3)/2;

+--------+---------+

| 5+3/2  | (5+3)/2 |

+--------+---------+

| 6.5000 |  4.0000 |

+--------+---------+

1 row in set (0.00 sec)

 

mysql> select pi();

+----------+

| pi()     |

+----------+

| 3.141593 |

+----------+

1 row in set (0.00 sec)

 

mysql> select power(2,3), power(4,0.5);

+------------+--------------+

| power(2,3) | power(4,0.5) |

+------------+--------------+

|          8 |            2 |

+------------+--------------+

1 row in set (0.00 sec)

 

mysql> select power(-2,0.5);

+---------------+

| power(-2,0.5) |

+---------------+

|          -NaN |

+---------------+

1 row in set (0.00 sec)

 

mysql> select power(-3,2);

+-------------+

| power(-3,2) |

+-------------+

|           9 |

+-------------+

1 row in set (0.00 sec)

 

mysql>

My SQL show create table command

You can see the layout of a table with the desc command but if you want to see how it was created, you should use the show create table command. I had to reformat the output below to fit it on the screen. If you try it yourself, you will see what I mean:

mysql> create table andrews_table
    -> (col1 varchar(10), primary key(col1));
Query OK, 0 rows affected (0.04 sec)

mysql> desc andrews_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1  | varchar(10) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.04 sec)

mysql> show create table andrews_table;
+---------------+-------------------------------------------+
| Table         | Create Table                              |
+---------------+-------------------------------------------+
| andrews_table | CREATE TABLE `andrews_table`              |
|               | (`col1` varchar(10) NOT NULL default '',  |
|               | PRIMARY KEY  (`col1`)) ENGINE=MyISAM      |
|               | DEFAULT CHARSET=latin1                    |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

How to Create a MySQL Table with a Primary Key

This is one way to create a table with a primary key. In this example, the primary key consists of one column. In a future post, I will show how to set up a primary key with more than one column: 

mysql> create table andrews_table

    -> (col1 varchar(10), primary key(col1));

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

mysql>
  

The purpose of a primary key is to ensure that the values in the primary key column remain unique. So, if you try to insert Fred into the primary key column again, you would expect the insert to fail:

 

mysql> insert into andrews_table values ('Fred');

ERROR 1062 (23000): Duplicate entry 'Fred' for key 1

mysql> select * from andrews_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>

MySQL show tables from command

If you are using one database, you can see the tables in a different one with the show tables from command:

 

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show tables from andrew;

+------------------+

| Tables_in_andrew |

+------------------+

| andrews_table    |

+------------------+

1 row in set (0.00 sec)

 

mysql>

And you can look at the contents of a table from another database by concatenating the database name, a dot and the table name:

mysql> select * from andrew.andrews_table;

Empty set (0.01 sec)

 

mysql>

MySQL Length Function

You can use the length() function to see how long column values in a table are as follows:

 

mysql> insert into andrews_table values ('Joe');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Brian');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Andrew');

Query OK, 1 row affected (0.00 sec)

 

mysql> select col1, length(col1) from andrews_table;

+--------+--------------+

| col1   | length(col1) |

+--------+--------------+

| Andrew |            6 |

| Brian  |            5 |

| Fred   |            4 |

| Joe    |            3 |

+--------+--------------+

4 rows in set (0.00 sec)

 

mysql>

MySQL Limit Clause

You can restrict the number of rows a query returns by using the limit clause at the end. It is followed by 2 numbers, which are separated by a comma. The first number tells the query which row to start at (the table's first row is numbered zero). The second number tells the query how many rows to return. You can see what I mean in the examples below:  

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into andrews_table values ('Brian');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Joe');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Andrew');

Query OK, 1 row affected (0.00 sec)

 

mysql> select col1 from andrews_table limit 0,2;

+-------+

| col1  |

+-------+

| Brian |

| Joe   |

+-------+

2 rows in set (0.00 sec)

 

mysql> select col1 from andrews_table limit 2,2;

+--------+

| col1   |

+--------+

| Fred   |

| Andrew |

+--------+

2 rows in set (0.00 sec)

 

mysql> select col1 from andrews_table limit 1,3;

+--------+

| col1   |

+--------+

| Joe    |

| Fred   |

| Andrew |

+--------+

3 rows in set (0.00 sec)

 

mysql>

Renaming a Table in MySQL

Here are 2 different ways to do this: 

mysql> create table old_name

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.03 sec)

 

mysql> alter table old_name

    -> rename to new_name;

Query OK, 0 rows affected (0.01 sec)

 

mysql> rename table new_name to old_name;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

MySQL create table ... like ...

If you want to create a table with the same description as another table, you can do this using like. In the example below, I create a table called andrews_table with 1 row of data. Then I use like to create a table called another_table with the same description. This table is empty to start with. To copy the data from andrews_table into another_table, I use a simple insert statement:

 

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.12 sec)

 

mysql> create table another_table

    -> like andrews_table;

Query OK, 0 rows affected (0.00 sec)

 

mysql> desc another_table;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col1  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.06 sec)

 

mysql> select * from another_table;

Empty set (0.00 sec)

 

mysql> insert into another_table

    -> (select * from andrews_table);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from another_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>

MySQL BLACKHOLE Engine (again)

My previous post about this was run on a MySQL installation on UNIX. I decided to have another go using the MySQL on my home PC, which is installed on Windows XP. First I had a look for the have_blackhole_engine variable but I could not find it:
  

mysql> SHOW VARIABLES LIKE 'have_blackhole_engine';
Empty set (0.00 sec)

mysql>

But I decided to carry on and created a table using the BLACKHOLE engine:

mysql> use test

Database changed

mysql> create table andrew (col1 varchar(10));
Query OK, 0 rows affected (0.41 sec)

mysql> alter table andrew engine = blackhole;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Then I inserted a row into the table and looked for it but it had gone so this time it had worked as expected:

mysql> insert into andrew values ('Fred');
Query OK, 1 row affected (0.00 sec)

mysql> select * from andrew;
Empty set (0.00 sec)

mysql>

MySQL BLACKHOLE Engine

Various engines are available in MySQL to store your table data. I have already looked at MyISAM and InnoDB in a different post. This time I am going to look at the BLACKHOLE engine. You can look at the different engines available using the show engines command. I have had to display its output in a very small font to fit it in but you can try it on your own system if you wish:

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10224

Server version: 5.0.67 Source distribution

 

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

 

mysql> show engines;

+------------+---------+----------------------------------------------------------------+

| Engine     | Support | Comment                                                        |  +------------+---------+----------------------------------------------------------------+

| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |      

| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |

| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |

| BerkeleyDB | NO      | Supports transactions and page-level locking                   |

| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |

| EXAMPLE    | NO      | Example storage engine                                         |

| ARCHIVE    | NO      | Archive storage engine                                         |

| CSV        | NO      | CSV storage engine                                             |

| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |

| FEDERATED  | NO      | Federated MySQL storage engine                                 |

| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |

| ISAM       | NO      | Obsolete storage engine                                        |

+------------+---------+----------------------------------------------------------------+

12 rows in set (0.00 sec)

mysql>
  

Now let's try to use the BLACKHOLE engine. According to the output from the show engines command, if you set up a table using this engine, any data you add to it will be discarded:
  

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.02 sec)

 

mysql> alter table andrews_table engine = BLACKHOLE;

Query OK, 0 rows affected, 1 warning (0.17 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into andrews_table

    -> values ('Andrew');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from andrews_table;

+--------+

| col1   |

+--------+

| Andrew |

+--------+

1 row in set (0.00 sec)

mysql>
  

A row was added to the table but it did not disappear so the BLACKHOLE engine did not work as expected. This seems to be due to the have_blackhole_engine variable being set to NO:

 

mysql> SHOW VARIABLES LIKE 'have_blackhole_engine';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| have_blackhole_engine | NO    |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql>

MyISAM versus InnoDB Tables in MySQL

This is a worked example comparing 2 MySQL table types:

1. MyISAM – you cannot rollback DML on these tables.
2. InnoDB – you can (sometimes) rollback DML on these tables.

First, create a table of each type:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10370

Server version: 5.0.67 Source distribution

 

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

 

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table andrews_myisam

    -> (col1 varchar(10)) engine = myisam;

Query OK, 0 rows affected (0.00 sec)

 

mysql> create table andrews_innodb

    -> (col1 varchar(10)) engine = innodb;

Query OK, 0 rows affected (0.38 sec)

 

mysql>

 

Then insert a row in each table and rollback the insert. Note the warning that you cannot rollback non-transactional tables:

 

mysql> insert into andrews_myisam values ('MYISAM');

Query OK, 1 row affected (0.06 sec)

 

mysql> insert into andrews_innodb values ('INNODB');

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+---------------------------------------------------------------+

| Level   | Code | Message                                                       |

+---------+------+---------------------------------------------------------------+

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

+---------+------+---------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql>

 

Query the tables and note that the rollback has failed in BOTH tables and the inserted rows are still there:

 

mysql> select * from andrews_myisam;

+--------+

| col1   |

+--------+

| MYISAM |

+--------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

This is because MySQL’s default behaviour is to commit DML immediately. To alter this, you need to set autocommit = 0. Do this then repeat the test. This time the DML deletes the rows inserted above:

 

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> delete from andrews_myisam;

Query OK, 1 row affected (0.00 sec)

 

mysql> delete from andrews_innodb;

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+---------------------------------------------------------------+

| Level   | Code | Message                                                       |

+---------+------+---------------------------------------------------------------+

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

+---------+------+---------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_myisam;

Empty set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

The delete statement on the MyISAM table could not be rolled back, as you might expect. Setting autocommit to zero prevented the delete statement on the InnoDB being committed immediately. The rollback therefore worked and the inserted row reappeared in the table.

Granting Access to a MySQL Database

In this example, I had a go at granting access to a database. First, I created a database and a user:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8718

Server version: 5.0.67 Source distribution

 

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

 

mysql> create database fred;

Query OK, 1 row affected (0.00 sec)

 

mysql> create user 'john'@'localhost'

    -> identified by 'smith';

Query OK, 0 rows affected (0.00 sec)

 

mysql> exit

Bye

UNIX >

 

Then I logged in as the new user and tried to use the new database:

 

UNIX > mysql -u john -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8721

Server version: 5.0.67 Source distribution

 

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

 

mysql> use fred

ERROR 1044 (42000): Access denied for user 'john'@'localhost' to database 'fred'

mysql> quit

Bye

UNIX >

 

That failed and the reason for failure was fairly obvious so I decided to see what I could do about it:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8734

Server version: 5.0.67 Source distribution

 

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

 

mysql> grant all on fred.* to john;

Query OK, 0 rows affected (0.28 sec)

 

mysql> exit

Bye

UNIX >

 

Next, I logged back in as the new user and tried to access the database again. It seemed to work OK. N.B. The grant all command even allows the user to drop the database:

 

UNIX > mysql -u john -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8735

Server version: 5.0.67 Source distribution

 

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

 

mysql> use fred;

Database changed

mysql> drop database fred;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

 

Finally I dropped the new user:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8761

Server version: 5.0.67 Source distribution

 

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

 

mysql> drop user john;

Query OK, 0 rows affected (0.00 sec)

 

mysql>