Skip to content

Category Archives: MySQL

MySQL- Allowing Access to Root Remotely

Thursday, August 19, 2010

MySQL includes all the basic methods needed to secure your user accounts.  However, the syntax and style used to manage it is often confusing to MySQL novices.  Here are a couple tips to get you started, however I strongly suggest your read the MySQL User Account Management documentation.

If you need to change your password for your localhost root account, here is the proper syntax.

mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST' = PASSWORD('new_password');

You can also grant access to all remote servers as well by using the following syntax.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

By default, MySQL only allows access by the ‘root’ account from the host running the database server (‘localhost’).

The percent symbol ("%") in the notation root@"%" means “any host”, but it doesn’t imply localhost. You need to repeat the commands above with root@localhost in order to grant/revoke permissions for localhost.

If you are setting up your server and require NO remote access, add skip-networking to your my.cnf file.

skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

 

Also filed in | Tagged , , , , , , , , , , , , , , | Comments (0)

MySQL Delimiters – Or why I hate stored procedures and Error Code : 1064 You have an error in your SQL syntax.

Thursday, August 19, 2010

MySQL 5.0+ gave DBA’s the ability to write stored procedures and functions. This is a great addition, and one that all “real” DB engines should provide. But, as many DBA have found out, writing stored procedures using most MySQL clients can be a bit of a challenge. Usually after writing their first attempt, they are greeted with, “Error Code : 1064 You have have an error in your SQL syntax;”.

So, what is going on?  Well, by default the MySQL statement delimiter is the semi-colon (;).  However, the mysql command-line utility also uses a semi-colon as a delimiter. So, if the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.

The solution is to temporarily change the command-line utility delimiter using the DELIMITER command, as seen here:

DELIMITER //

CREATE PROCEDURE sku_pricing( OUT low_price DECIMAL(8,2), OUT high_price DECIMAL(8,2), OUT avg_price DECIMAL(8,2) )

BEGIN

SELECT Min(prod_price) INTO low_price FROM sku_items;

SELECT Max(prod_price) INTO high_price FROM items;

SELECT Avg(prod_price) INTO avg_price FROM items;

END; //

DELIMITER ;

Here, DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine, rather than being interpreted by the client. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;.   Any character may be used as the delimiter except for \, as it is an escape character in mySQL.

Related articles
Also filed in Stored Procedure | Tagged , , , , , , , , , , , , , , , , , , , | Comments (1)

How do I store an IP Address in MySQL? INET_AtoN!

Thursday, August 19, 2010

One of the most popular questions out there is, “How do I store an IP Address in MySQL?”.   The fastest and most compact way to store an IP address in the format “10.51.1.10″ is as an unsigned integer.  If you store the actual values as a char(15), you will require 11 extra bytes per row.  In addition, using IP as a condition when querying is much faster when using an unsigned integer.  Testing has shown that range queries can result in as much as a 35 million times improvement over storing an IP address as a character string.  Yes, that is 35 million!

Simply define IP as an INT UNSIGNED.

Use MySQL’s built-in function INET_ATON() to convert the string representation of the IP Address into an unsigned Integer.  INET_NTOA() will perform the reverse translation.

This will result in space savings, and faster queries, who could ask for more than that?

Also filed in | Tagged , , , , , , , , , , | Comments (0)

Show Processlist and Show Full Processlist – MySQL Tip

Thursday, August 19, 2010

Everyone knows that using the MySQL command Show Processlist will display all current connections, like the following :

mysql> show processlist;
show processlist;
+—-+————-+—————–+——+———+——+———————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+—————–+——+———+——+———————————-+——————+
| 1 | root | localhost:32893 | NULL | Sleep | 0 | | NULL |
| 5 | system user | | NULL | Connect | 98 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 5018 | Reading event from the relay log | NULL |
+—–+——+———–+———+———+——-+——-+——————+
3 rows in set (0.00 sec)

What is little known however is that the Show Full Processlist command will display the full query in the Info column.  By default, Show Processlist will only display the first 100 characters. One thing to keep in mind about the Info line is that while the statement (query) might be the one sent to the server, it will display the innermost statement if the statement executes other statements.

Also filed in | Tagged , , , , , , , | Comments (0)

How to increase MySQL performance when loading a Dump File

Thursday, August 19, 2010

In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.

Also filed in Restore Backup | Tagged , , , , , , , , , , , | Comments (0)

MySQL – Should you put an index on a Boolean field to help query performance?

Monday, June 14, 2010

I am often asked if it makes sense to place an index on a Boolean field in order to improve query performance.  In general, because a boolean value can only have three values (True, False, Null), this low cardinality would suggest that adding an index will not help performance, as the query optimizer will still usually perform a table-scan if you have an even distribution of values within your DB.

One situation in which an index on a boolean field (or other low cardinality field) might be useful is if there are relatively few of one of the values, for example 5 True values in a table of millions of records and you are searching for those few values on a regular basis.

However, you might index a boolean value on a combination of fields. Indexing on a single Boolean might be pointless, because there’s only 2 (or 3) values.  However, indexing on 16 boolean values has the potential of  2^16 values.  It might help to make a combined index but you should understand how the combined index can and cannot be used and be aware that the order of the columns matters.

In general, you should always profile your system to see if there are queries that are too slow and consider adding another index to handle those queries. Sometimes a single combined index can be used for multiple queries and others time you will need to make an index for each type of query. Remember that adding indexes slows down modifications to the data so it is possible to have too many indexes. There is always a  trade-off when creating multiple indexes.

Also filed in Query Optimization | Tagged , , , , , , , , , , | Comments (2)

MySQL – Disabling Binary Logging for INSERT and UPDATE

Wednesday, June 2, 2010

I have run into many situations where there would be a benefit to not replicate a large INSERT or UPDATE from the MASTER to SLAVE in order to not block replication for a long period of time. Luckily MySQL provides a means to disable binary logging for your current session. Simply execute SET SQL_BIN_LOG=0 before the SQL Statement that you do not want logged to the BINLOG. This is a session variable, meaning it will be re-enabled when you close the session, or you can set it back to 1.

In order to keep the Slave in-sync, you will need to execute the same query on the Slave. You can also use mk-table-sync from the Maatkit toolkit to re-sync your table data at a later time.

Also filed in Replication | Tagged , , , , , , , , , , , , , , | Comments (1)

MySQL – Constraints on Hierarchical Data in a Self-Referential Table

Tuesday, June 1, 2010

I recently had a client who was using a table structure that utilized a self-referential structure using an ID field and Parent_ID fields. After upgrading to Innodb, his DBA was trying to utilize a foreign key constraint to perform cascading UPDATE and DELETE statements. After much heart-ache, they called me in to find out why it was not working. Unfortunately, I had to share with them the fact that MySQL does not support this type of use of constraints on self-referential tables.

This deviation from SQL standards results affects an ON UPDATE CASCADE or ON UPDATE SET NULL that recurses to update the same table it has previously updated during the cascade. Instead of cascading, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations in MySQL. According to the Innodb developers, this is to prevent infinite loops resulting from cascaded updates, although I would think this could be addressed in future versions. A self-referential ON DELETE SET NULL, on the other hand, is still possible, as is a self-referential ON DELETE CASCADE.

Also filed in | Tagged , , , , , , , , , , , , , , , , , , | Comments (0)

Free MySQL DBA – Pro Bono Work for Qualified Organizations

Friday, May 28, 2010

The Prodromus Group has announced a plan to perform Pro Bono MySQL DBA work for qualified organizations. As part of our mission, we feel that it is important to give back to our community, both locally and globally. If your organization believes that it may qualify, please contact us at [email protected] While we specialize in MySQL, our organization has extensive experience in IT operations and building high quality public facing web sites at an affordable price. We recently helped a local charitable firm develop a web site to solicit on-line donations, and they now receive over 50% of their donations via their website.

Examples of qualified organizations would be those focusing on Environmental and Human Rights initiatives, local schools, fire and police agencies, etc. Our goal is to help these organization receive quality technical advice and service that they may otherwise be unable to afford.

Contact us today at [email protected]

Also filed in Pro Bono | Tagged , , , , , , , , , , | Comments (0)

MySQL – ERROR 1005: Can’t create table (errno: 150) – INNODB

Thursday, May 13, 2010

If you have seen this error, don’t worry, you are not alone. This error is often accompanied by a message that a table or file could not be created, and usually happens when creating a foreign key. In my experience, 99% of the time this is due to an incompatibility between the two fields in the foreign key. Usually it is something simple like unsigned integer to signed integer. The trickiest I have seen is when trying to create a foreign key between two CHAR fields and they do not share the same CHARSET and COLLATE. ALTER the table so that the CHARSET and COLLATE are the same, and try to add the foreign key again.

Let me know if you have other examples of how you have worked around errno: 150 when adding foreign keys.

Also filed in Errors | Tagged , , , , , , , , , , , , | Comments (0)
Visitor TrackingData Recovery SoftwareData Recoverydata recovery software