PlanetMysql.ru — информация о СУБД MySQL

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]‘? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

This query uses the INNODB_SYS_TABLES, INNODB_SYS_INDEXES, and INNODB_SYS_FIELDS tables from INFORMATION_SCHEMA and is only available in MySQL 5.6 or Percona Server 5.5. However, it is much much faster than querying the STATISTICS table. It also only shows InnoDB tables.

SELECT t.name AS `Table`,
       i.name AS `Index`,
       GROUP_CONCAT(f.name ORDER BY f.pos) AS `Columns`
FROM information_schema.innodb_sys_tables t
JOIN information_schema.innodb_sys_indexes i USING (table_id)
JOIN information_schema.innodb_sys_fields f USING (index_id)
WHERE t.schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

Assuming that all your tables are InnoDB, both queries will produce identical results. If you have some MyISAM tables in there, only the first query will provide complete results.

+---------------+-----------------------------+--------------------------------------+
| Table         | Index                       | Columns                              |
+---------------+-----------------------------+--------------------------------------+
| actor         | idx_actor_last_name         | last_name                            |
| actor         | PRIMARY                     | actor_id                             |
| address       | idx_fk_city_id              | city_id                              |
| address       | PRIMARY                     | address_id                           |
| category      | PRIMARY                     | category_id                          |
| city          | idx_fk_country_id           | country_id                           |
...
| rental        | rental_date                 | rental_date,inventory_id,customer_id |
| staff         | idx_fk_address_id           | address_id                           |
| staff         | idx_fk_store_id             | store_id                             |
| staff         | PRIMARY                     | staff_id                             |
| store         | idx_fk_address_id           | address_id                           |
| store         | idx_unique_manager          | manager_staff_id                     |
| store         | PRIMARY                     | store_id                             |
+---------------+-----------------------------+--------------------------------------+
42 rows in set (0.04 sec)

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.1, a maintenance release version that includes bug fixes and enhancements to some of the existing features.

The most important feature introduced in this release is Automatic Incremental Backup. The new  argument syntax for the --incremental-base option is introduced which makes it simpler to perform automatic incremental backups. When the options --incremental & --incremental-base=history:last_backup are combined, the mysqlbackup command  uses the metadata in the mysql.backup_history table to determine the LSN to use as the lower limit of the incremental backup. You no longer need to keep track of the actual LSN (as in the option --start-lsn=LSN) or even the location of the previous backup (as in the option --incremental-base=dir:directory_path)

This release also incudes various bug fixes related to some options used in MEB. The most important are few of them as listed below,

1. The option --force now allows overwriting InnoDB data and log files in  combination with the apply-log and apply-incremental-backup options, and replacing the image file in combination with the backup-to-image and backup-dir-to-image options.

2. Resolved a bug that prevented MEB to interface with third-party storage managers to execute backup and restore jobs in combination with the SBT interface and associated --sbt* options for mysqlbackup.

3. When MEB is run with the copy-back option,  it now displays warnings as existing files are overwritten.

For more information about other bug fixes, please refer to the change-log in http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/meb-news.html

The complete MEB documentation is located at http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html.

You will find the binaries for the new release in My Oracle Support,  https://support.oracle.com
Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB 3.7.1 recently, please do so now and let us know how MEB works for you. Send your feedback to mysql-backup_ww@oracle.com.

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.1, a maintenance release version that includes bug fixes and enhancements to some of the existing features.

The most important feature introduced in this release is Automatic Incremental Backup. The new  argument syntax for the --incremental-base option is introduced which makes it simpler to perform automatic incremental backups. When the options --incremental & --incremental-base=history:last_backup are combined, the mysqlbackup command  uses the metadata in the mysql.backup_history table to determine the LSN to use as the lower limit of the incremental backup. You no longer need to keep track of the actual LSN (as in the option --start-lsn=LSN) or even the location of the previous backup (as in the option --incremental-base=dir:directory_path)

This release also incudes various bug fixes related to some options used in MEB. The most important are few of them as listed below,

1. The option --force now allows overwriting InnoDB data and log files in  combination with the apply-log and apply-incremental-backup options, and replacing the image file in combination with the backup-to-image and backup-dir-to-image options.

2. Resolved a bug that prevented MEB to interface with third-party storage managers to execute backup and restore jobs in combination with the SBT interface and associated --sbt* options for mysqlbackup.

3. When MEB is run with the copy-back option,  it now displays warnings as existing files are overwritten.

For more information about other bug fixes, please refer to the change-log in http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/meb-news.html

The complete MEB documentation is located at http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html.

You will find the binaries for the new release in My Oracle Support,  https://support.oracle.com
Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB 3.7.1 recently, please do so now and let us know how MEB works for you. Send your feedback to mysql-backup_ww@oracle.com.

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | Hopkins   |          5 |
|  4 | Helen      | Hopkins   |       NULL |
|  5 | Heidi      | Hopkins   |          4 |
|  6 | Hazel      | Hopkins   |          1 |
+----+------------+-----------+------------+
6 rows in set (0.00 sec)

In order to see who reports to whom, we need to query the table to get the employees, and again to get the managers' names. MySQL will get horribly confused if we use the same table twice, so we alias it each time to indicate which one we mean. I do this by drawing the picture of which tables I need and labelling them - here I've used "e" for employees and "m" for managers:

mysql> select e.first_name as employee, m.first_name as reports_to
    -> from staff e
    -> inner join staff m on e.manager_id = m.id;
+----------+------------+
| employee | reports_to |
+----------+------------+
| Hattie   | Helen      |
| Henry    | Helen      |
| Harry    | Heidi      |
| Heidi    | Helen      |
| Hazel    | Hattie     |
+----------+------------+
5 rows in set (0.02 sec)

Did you spot that there's one row less in this output? That's because we did an inner join, but Helen has no manager since she owns the company, so she doesn't appear in the second set of results. We could easily have left joined instead to include her if we wanted to.

You can use this technique regardless of how many other tables are needed for the query and whether you need to join the table to itself or onto other places. You might have a table of users, and a table of user_friends, linking one user to another. You can start from the users table, join to user_friends, then join to user again with a different alias to pull back the information you need - the possiblities are almost endless.

Lorna is an independent web development consultant, writer and trainer, open source project lead and community evangelist. This post was originally published at LornaJane

Several months ago I suggested having lightning talks at Percona Live MySQL Conference and Expo 2012, and I also offered to help.

Then I forgot about that for a while, until I saw the announcement that there was a call for Lightning Talks. Great! I submitted two proposals, and asked my colleagues to do the same, and also encouraged many good speakers I know to submit something.

The deadline for lightning talks submission passed, and I was told that my offer to help had been accepted, and I was in charge of lightning talks! OK. I would have preferred being told before the CfP, but an offer to help is an offer to help, and thus I went through the motions of evaluating the talks, sending notices to the winners, consoling the losers, and giving hope to the few brave ones who will replace the winners if they don't show up.

The talks that you will see at the conference are in the Lightning Talks page.

Lightning talks are fun and instructional micro events. Their official purpose is to give the audience a chance to learn something in a very limited amount of time. The real purpose is for the speaker to be as entertaining and memorable as possible within the allocated time.

Here are the official rules:

1. All slides will be loaded into a single computer, to minimize delays between talks
2. All speakers will meet 15 minutes before the start, and be given the presentation order. Missing speakers will be replaced by reserve speakers
3. The speaker will have 5 minutes to deliver the talk.
4. When one minute is left, there will be a light sound to remind of the remaining time.
5. When 10 seconds are left, most likely the audience will start chanting the countdown.
6. when the time is finished, the speaker must leave the place to the next one.

For this to be real fun, there must be some cooperation from the audience. Rule #5 is often a spontaneous behavior from the crowd. It's very effective to make the speaker hurry up and close.

If rule #6 were to be enforced in style, there would be a tele-transporter that is triggered at the last second, and the too-slow speaker is instantly moved to the parking lot. My contact at the Star Trek labs tells me that the appliance is not available yet. We'll see if there is an app for that …

Several months ago I suggested having lightning talks at Percona Live MySQL Conference and Expo 2012, and I also offered to help.

Then I forgot about that for a while, until I saw the announcement that there was a call for Lightning Talks. Great! I submitted two proposals, and asked my colleagues to do the same, and also encouraged many good speakers I know to submit something.

The deadline for lightning talks submission passed, and I was told that my offer to help had been accepted, and I was in charge of lightning talks! OK. I would have preferred being told before the CfP, but an offer to help is an offer to help, and thus I went through the motions of evaluating the talks, sending notices to the winners, consoling the losers, and giving hope to the few brave ones who will replace the winners if they don't show up.

The talks that you will see at the conference are in the Lightning Talks page.

Lightning talks are fun and instructional micro events. Their official purpose is to give the audience a chance to learn something in a very limited amount of time. The real purpose is for the speaker to be as entertaining and memorable as possible within the allocated time.

Here are the official rules:

1. All slides will be loaded into a single computer, to minimize delays between talks
2. All speakers will meet 15 minutes before the start, and be given the presentation order. Missing speakers will be replaced by reserve speakers
3. The speaker will have 5 minutes to deliver the talk.
4. When one minute is left, there will be a light sound to remind of the remaining time.
5. When 10 seconds are left, most likely the audience will start chanting the countdown.
6. when the time is finished, the speaker must leave the place to the next one.

For this to be real fun, there must be some cooperation from the audience. Rule #5 is often a spontaneous behavior from the crowd. It's very effective to make the speaker hurry up and close.

If rule #6 were to be enforced in style, there would be a tele-transporter that is triggered at the last second, and the too-slow speaker is instantly moved to the parking lot. My contact at the Star Trek labs tells me that the appliance is not available yet. We'll see if there is an app for that …

I have used tools like MySQL Migration Toolkit (*) and Bullzip’s Access To MySQL utility, both do a excellent job but without relationships. We can spend a lot of hours to identifying and creating relationships until now:

I have write a VBA script to identify MS-Access relationships and create MySQL code with SQL-CREATE sentences, it will be useful after migration process using any free tool:

Option Explicit
'Put this function in new/existing MS-Access module.
Public Sub printRelations()
    Dim sql, fk As String
    Dim i, j As Integer
    For i = 0 To CurrentDb.Relations.Count - 1
        sql = "ALTER TABLE `" & CurrentDb.Relations(i).ForeignTable & _
            "` ADD CONSTRAINT `" & CurrentDb.Relations(i).Name & "` FOREIGN KEY ("
        fk = "("
        For j = 0 To CurrentDb.Relations(i).Fields.Count - 1
            sql = sql & "`" & CurrentDb.Relations(i).Fields(j).ForeignName & "` ,"
            fk = fk & "`" & CurrentDb.Relations(i).Fields(j).Name & "` ,"
        Next j
 
        sql = Left(sql, Len(sql) - 1)
        fk = Left(fk, Len(fk) - 1)
        fk = fk & ")"
        sql = sql & ") REFERENCES `" & CurrentDb.Relations(i).Table & "`" & fk & ";"
 
        Debug.Print sql
    Next i
End Sub

To run the adobe code go to intermediate windows (Ctrl+G) and execute: printRelations and copy the generated SQL code.

Enjoy!

(*)MySQL Migration Toolkit is discontinued but is still available from mirrors like:

http://mirrors.dotsrc.org/mysql/Downloads/MySQLGUITools/

I just finished a database modification, a new foreign key creation  shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.

There is a way to create a new foreign key:

-- Create two tables foo and bar
CREATE TABLE foo (
	id INTEGER NOT NULL PRIMARY KEY,
	bar_id INT NOT NULL    -- foreign key
);
CREATE TABLE bar (
	id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
	ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;

The last sentence returns a generic error message:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD), sometimes happens,  but if MySQL would have shown a different message like "field bar.SOME_FIELD don't exists" I would not be awake until 2 am.

I’m using MySQL 5.5.21 community edition.

if I had noticed that wrong field name

Do you get write laziness in the command line everything what you need to connect to a MySQL server every time?

It may take less than minute, but sometimes one minute is vital (especially if we’re near the end of the world):

ivancp@ubuntu$ mysql -u root -p -h mysqlhost database

When we are hurry, these commands often fail several times per minute.

The solution: we can create shortcuts with bash alias commands in file ~/.bashrc :

# File ~ /. Bashrc
 
# Command "my" to connect to a local server
alias my='mysql -u root -p'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root -h 192.168.1.56 -p'

Next time if you want to access the local server just type the command my [database name] , there only ask for database password. You can use any command aliases, I prefer  “my” and “my2″ they are short and useful.

But if you have several servers comes another problem, how to know in which server I’m?

Open a mysql-cli can be super fast with alias shortcuts, but all terminals have the same default prompt: mysql>  To avoid disasters (ex. run DROP in wrong place) you can change mysql-cli prompt with option --prompt , then finally our .bashrc look like this:

#improved ~/.bashrc 
 
# Command "my" to connect to a local server
alias my='mysql -u root --password=secret --prompt="local> "'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root --password=secret  -h 192.168.1.56 --prompt="server 1> "'

Note I added --password parameter, it can be dangerous, use it under your own risk.

Enjoy!