You blink and there is a new version. I have not seen an Planet MySQL release as yet about this new version. Release Notes.
I’d like to say I installed it, but I downloaded the Linux – Generic 2.6 (x86, 64-bit), TAR file, only to find it contains 6 rpm files. #fail, I’m using Ubuntu.
You have to scroll to the bottom of the list (another stupid thing for a generic binary choice) to get Linux – Generic 2.6 (x86, 64-bit), Compressed TAR Archive. Double #fail
Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.
#my.cnf [mysqld] innodb_file_per_table
The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.
The primary reason is we are moving from using a common tablespace to a tablespace per table. InnoDB wil not shrink the common tablespace so this process is necessary in order to purge the diskspace currently being used. You should also reduce your innodb_data_file_path options if specified. For example if currently set to :
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
I would suggest you change to
innodb_data_file_path = ibdata1:100M:autoextend
InnoDB still requires this common tablespace, however now each table has it’s own disk file the volume required is signficantly less.
Today I discovered an interesting upgrade problem with a client migrating from MySQL 5.0 to 5.5. The client who is undertaking the upgrade reported that MySQL 5.5 did not support the DECIMAL(18,5) data type. I easily confirmed this not to be the case:
mysql> drop table if exists x; mysql> create table x (col1 DECIMAL(18,5)); Query OK, 0 rows affected (0.01 sec)
Delving more into the issue in question, I looked at the complete CREATE TABLE statement, recreating the syntax.
mysql> drop table if exists x; mysql> create table x ( MinValue DECIMAL(18,5)); Query OK, 0 rows affected (0.00 sec)
No problem there.
mysql> drop table if exists x; mysql> create table x (Department INT NOT NULL, MinValue DECIMAL(18,5) NULL, MaxValue DECIMAL(18,5) NULL); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MaxValue DECIMAL(18,5) NULL)' at line 1
I could immediately see the problem because if you look closely at the error message it starts with the word “MaxValue”, most likely this is a reserved word. This was easily confirmed with:
mysql> create table x (Department INT NOT NULL, MinValue DECIMAL(18,5) NULL, `MaxValue` DECIMAL(18,5) NULL); Query OK, 0 rows affected (0.00 sec)
And a confirmation with the 5.5 Reference Manual does indeed show there are new reserved words in MySQL 5.5. These are:
Many early startups that use a single server for all services or a single database server for their website talk about how they would like to achieve higher availability with MySQL. This is not possible without at least two database servers. Using MySQL replication you can then support higher availability in several varying capacities. An additional MySQL database server can satisfy several infrastructure needs including:
MySQL replication is very easy to configure and deploy, a task that takes < 30 minutes for an experienced MySQL DBA. However, altering your backup and recovery strategy, modifying your application to support read/write splitting or implementing a fail-over/fail back strategy (also known as a MySQL Pairs implementation) are more complex tasks for implementing a higher availability MySQL solution.
Ronald Bradford is a well respected industry expert in the MySQL community. Ronald is internationally recognized as an Oracle ACE Director in MySQL, a published author of Expert PHP and MySQL and the all-time top individual contributor of MySQL blog content at Planet MySQL.
If so then were is it installed on your publicly accessible website. If the location is where the documentation states not to put it, or in other popular locations then you can easily become open to an attack. The following are apache logs of a simple hacker test to find a potential security hole on a new IP public address for a client.
My advice is never have PHPMyAdmin accessible by default. You should use HTTP authentication, firewall rules and additional security practices all to protect any level of access to your data.
78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpmyadmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpMyAdmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //pma/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //dbadmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:25 -0500] "GET //myadmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:25 -0500] "GET //phppgadmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:26 -0500] "GET //PMA/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:26 -0500] "GET //admin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro" 78.111.81.180 - - [02/Feb/2011:05:29:26 -0500] "GET //MyAdmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
If you are an administrator, 78.111.81.180 should be added to your blacklist permanently.
I should also state I do not use PHPMyAdmin on public servers, and also note the 401 response.
While Oracle provides no official information they are planning on improving MySQL and using as a product to compete with Microsoft SQL Server, it is rather obvious from what little information you can glean from public announcements this is a clear business goal.
Microsoft however are publicly seeking a Senior Product Manager, MySQL Compete in the Marketing department. Your goal is nothing technical, it’s all PR to dispel MySQL as a viable product. I quote “you will equip field and partners to win in competitive engagements against MySQL, and you will influence market perception in favor of Microsoft technologies.” Here is the Full job description for those that want an amusing read.
This information came from an Oracle colleague of mine based in Asia.
Planet MySQL contains the aggregation of MySQL articles from over 500 individuals and countries.
In the MySQL Community Blogging article, Ronald Bradford was recognized as the all-time top individual MySQL blogger at Planet MySQL. Ronald was also recognized as the top MySQL blogger in 2010 with 99 articles. If only I had known, I would have written one more.
I am pleased to announce that the Oracle ACE program has two new MySQL inductees. These people actively contribute to the MySQL community via a varied means in an unbiased and non commercial way.
Giuseppe Maxia (aka DataCharmer) is no stranger to the MySQL community having filled a position with MySQL Inc/Sun Microsystems/Oracle Corporation in the Community team for many years. Giuseppe was an active member in the community before this position, and continues to provide great input with tools such as the MySQL sandbox and many project and code snippets on the MySQL Forge as well as many writings for the Developer zone.
Patrick Galbraith (aka CaptTufo) may be a lesser known name to some, however he is the maintainer of the MySQL Perl DBD::mysql driver, the creator of the Federated Storage Engine for MySQL and is the author of two MySQL books. He also is the creator of the Memcached UDF functions.
Welcome, and I hope to see your continued contributions help grow our MySQL community.
In the past I’ve used the profiling features (e.g. SHOW PROFILES) in MySQL to help with timing SQL statements, especially those in the < 10 millisecond range.
Out of habit I did use this to time all SQL statements however in MySQL 5.5.8 GA I've found this no longer to be representative.
As you can see, the query takes some 50+ms longer with profiling enabled, not to mention they have broken the Source_file column which I've actually used to troll the source code with.
mysql> set profiling=1; 4 rows in set (1.14 sec) 4 rows in set (1.15 sec) 4 rows in set (1.17 sec) mysql> set profiling=0; 4 rows in set (0.37 sec) 4 rows in set (0.37 sec) 4 rows in set (0.37 sec)
Investigating further showed the cause. There appears to be some new overhead that causes profiling to log excessive amount of information.
mysql> show profile source for query 35; +--------------------------------+----------+-----------------------+----------------------+-------------+ | Status | Duration | Source_function | Source_file | Source_line | +--------------------------------+----------+-----------------------+----------------------+-------------+ | starting | 0.000047 | NULL | NULL | NULL | | Waiting for query cache lock | 0.000008 | try_lock | /export/home/pb2/bui | 454 | | checking query cache for query | 0.000112 | send_result_to_client | /export/home/pb2/bui | 1537 | | checking permissions | 0.000008 | check_access | /export/home/pb2/bui | 4613 | | checking permissions | 0.000006 | check_access | /export/home/pb2/bui | 4613 | | checking permissions | 0.000006 | check_access | /export/home/pb2/bui | 4613 | | checking permissions | 0.000009 | check_access | /export/home/pb2/bui | 4613 | | Opening tables | 0.000035 | open_tables | /export/home/pb2/bui | 4732 | | System lock | 0.000015 | mysql_lock_tables | /export/home/pb2/bui | 299 | | Waiting for query cache lock | 0.000056 | try_lock | /export/home/pb2/bui | 454 | | init | 0.000068 | mysql_select | /export/home/pb2/bui | 2545 | | optimizing | 0.000019 | optimize | /export/home/pb2/bui | 858 | | statistics | 0.000036 | optimize | /export/home/pb2/bui | 1049 | | preparing | 0.000025 | optimize | /export/home/pb2/bui | 1071 | | Creating tmp table | 0.000050 | optimize | /export/home/pb2/bui | 1587 | | Sorting for group | 0.000010 | optimize | /export/home/pb2/bui | 1632 | | executing | 0.000006 | exec | /export/home/pb2/bui | 1818 | | Copying to tmp table | 0.000049 | exec | /export/home/pb2/bui | 1965 | | optimizing | 0.000011 | optimize | /export/home/pb2/bui | 858 | | statistics | 0.000053 | optimize | /export/home/pb2/bui | 1049 | | preparing | 0.000014 | optimize | /export/home/pb2/bui | 1071 | | executing | 0.000006 | exec | /export/home/pb2/bui | 1818 | | Sending data | 0.000025 | exec | /export/home/pb2/bui | 2356 | .... | Sending data | 0.000009 | exec | /export/home/pb2/bui | 2356 | | executing | 0.000006 | exec | /export/home/pb2/bui | 1818 | | Sending data | 0.000051 | exec | /export/home/pb2/bui | 2356 | | Sorting result | 0.000034 | exec | /export/home/pb2/bui | 2244 | | Sending data | 0.000052 | exec | /export/home/pb2/bui | 2356 | | end | 0.000008 | mysql_select | /export/home/pb2/bui | 2581 | | removing tmp table | 0.000014 | free_tmp_table | /export/home/pb2/bui | 11121 | | end | 0.000009 | free_tmp_table | /export/home/pb2/bui | 11146 | | query end | 0.000010 | mysql_execute_command | /export/home/pb2/bui | 4310 | | closing tables | 0.000023 | mysql_execute_command | /export/home/pb2/bui | 4362 | | freeing items | 0.000026 | mysql_parse | /export/home/pb2/bui | 5509 | | Waiting for query cache lock | 0.000009 | try_lock | /export/home/pb2/bui | 454 | | freeing items | 0.000051 | NULL | NULL | NULL | | Waiting for query cache lock | 0.000006 | try_lock | /export/home/pb2/bui | 454 | | freeing items | 0.000006 | NULL | NULL | NULL | | storing result in query cache | 0.000017 | end_of_result | /export/home/pb2/bui | 1020 | | logging slow query | 0.000006 | log_slow_statement | /export/home/pb2/bui | 1444 | | cleaning up | 0.000009 | dispatch_command | /export/home/pb2/bui | 1400 | +--------------------------------+----------+-----------------------+----------------------+-------------+ 124504 rows in set (0.48 sec)
Dear Recruiters.
I am always open to hearing about exceptional opportunities that will be a challenging role with hard problems to solve and a great team to work with.
You have been directed to this link because you have contacted me. Please do not consider this as impersonal (after you cut and paste this spiel so many times) it is simply easier to publish my response.
My standard recruiter spiel
This request for additional information will help me determine if I am interested in discussing your opportunity in further detail. Please answer all questions. Please do not be an annoying recruiter like the 2 or 3 a week I have to deal with that refuse to provide details. I’m not going to call you unless I am interested, my time is very valuable. If you can not provide information via email to determine my interest level I can not help you, nor am I motivated to share this with my network.
The details
I get contacted 5 times a week by recruiters. In 90% of cases I would be lucky if I am provided with more then one sentence regarding a position, and hence why you have received a concise response. If your the 10% exception then also please continue reading, you just have less work to do towards getting a meaningful response.
I am not just going to pick up the phone and call you, especially when you provide a single sentence and ask me to call you. My time is extremely valuable, I bill at $250 per hour. If you value my time, then please respect it. To better determine if I am the right person for your inquiry please provide more information including but not limited to:
Simply sending me a full job description is not going to win you points if is does not address these questions.
NOTE: I’m not a junior DBA or even a senior DBA. I’m in the top 1% in the field. I have easily proven my output is 2x-3x of MySQL DBA’s working for major fortune 500 companies where these resources are effectively holding companies to ransom. If you are approaching me for a DBA role please ensure it is exceptional. Very few organizations even need a full-time DBA. What they require is a skilled resource such as myself to create an infrastructure that leads to a dispensable role, not an indispensable role for a DBA. I can assist organizations in this transition that includes working with partners for 24×7 DBA support .
You can find my skills and experience on my website at http://ronaldbradford.com and on various pages. If you have further questions then please ask. I do not give out my resume initially and I will never provide this via a Word document.
On a closing note, those that have started this conversation with “connect with me” via LinkedIn, you a one step from the trash or being reported to LinkedIn for spam (i..e if you have never worked with me, then do not make that claim). I do not accept invitations from people I do not know. I do not accept invitations even from people I do know if I do not want them in my network. I will not open up my professional contacts to recruiters. If you are unable to send a proper email from Linked In, it is rather trivial to track me down. There are no points for being lazy.