The following simple INFORMATION_SCHEMA statement will identify and also verify tables that have no rows. These may be candidate tables to remove from your data model.
mysql --defaults-file=.my.cnf -N -e "select CONCAT('SELECT "',table_schema,'.',table_name,'" AS tbl, COUNT(*) AS cnt FROM ',table_schema,'.',table_name,';') as cmd from information_schema.tables where table_schema not in ('mysql','performance_schema','information_schema') and table_rows=0;" | mysql --defaults-file=.my.cnf -N
and the video can be directly played in your browser at http://technocation.org/node/697/play or downloaded (170Mb) at http://technocation.org/node/697/download.
and the video can be directly played in your browser at http://technocation.org/node/697/play or downloaded (170Mb) at http://technocation.org/node/697/download.
I do not recall seeing an announcement about it, pancreatitis but I went looking for the videos today and lo and behold, they were up! Forgive me if I missed a post about it….but if you also missed it, here they are:
<A …
[Read more]I setup 6 shiny new MySQL 5.6.13 MySQL servers, ready for testing and production deployment tomorrow.
I found that the New Relic MySQL Monitoring was throwing the following error.
[2013-08-08 03:53:33 +0000] com.newrelic.plugins.mysql.MySQL | SEVERE | An SQL error occured running 'SHOW ENGINE INNODB STATUS' Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
Simple fix, the user I am gathering metrics requires the PROCESS privilege. Again simple enough.
mysql> grant PROCESS on xxx.* to xxx@'10.%'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES mysql> grant PROCESS on *.* to xxx@'10.%'; Query OK, 0 rows affected (0.00 sec)
As you can see I got an error for a global privilege, again trivial, easy fix to correct syntax. However, it …
[Read more]I’ve just had a new article published with the Software Developers Journal talking about how you can write applications to take full advantage of Continuent Tungsten and Tungsten Replicator.
As a developer of an application there really isn’t a problem better than finding that you have to scale up the application and the database that supports it to handle the increased load. The main bottleneck to most expansion is the database server and in many modern environments that replication is based around MySQL. Application servers are easy to add on to the front-end of your environment.
[Read more]It can be hard for software engineers to understand the following principle, however it is very important for improving performance and obtaining immediate scalability options. The principle is “Do Less Work”. That is, run less SQL statements.
Just one method to achieving the execution of less SQL statements is to eliminate Row At a Time (RAT) processing. In simple terms, do not perform identical repeating SQL statements in a loop. Relational algebra, and the Structure Query Language (SQL) specification is specifically designed to work with sets of data, or as I describe, Chunk At a Time (CAT) processing.
Customer Example
Your online social media website lets you send messages to multiple friends at one time. You enter the message, select the friends you want to receive the message and click send. While the user waits a moment and gets a success message, behind the scenes the application runs the …
[Read more]As part of uncovering a new security improvement in MySQL 5.6 with the default installation being secured with a unique root MySQL password, the MySQL provided rpm’s installed the MySQL configuration file in /usr.
I feel this maybe a bug, however this is just as an explanation
for others to comment on.
The following steps reproduces this.
1. Launch a CentOS 6.4 AWS EC2 Instance
LOG=/tmp/create-instance.tmp.$$
INSTANCE_TYPE=m1.medium
ec2-run-instances ami-eb6b0182 --instance-type ${INSTANCE_TYPE} --region us-east-1 --availability-zone us-east-1b --key db --group db | tee ${LOG}
INSTANCE=`grep ^INSTANCE ${LOG} | awk '{print $2}'`
ec2-describe-instances ${INSTANCE} | tee ${LOG}
while [ `grep ^INSTANCE ${LOG} | awk '{print $6}'` != 'running' ] ; do cat ${LOG}; sleep 5; ec2-describe-instances ${INSTANCE} | tee ${LOG}; done …[Read more]
What if the data you retrieved from the database did not match the data the application claimed to had successfully stored? How comfortable would your organization feel about your skills and the products that are being used to store important information if data integrity was not guaranteed?
MySQL employs a terrible default technique known as silent truncation where the product determines that it knows about your data better than you. Never has the saying “do not assume” because it makes an “ass” out of “u” and “me” been more applicable.
Customer Example
A HTML form for new customers provide input fields for the customer first and last name. Good design was considered with the HTML form client validation to ensure that each field could not exceed 20 characters in length. However, the database design is different, where the first name is only defined as 10 characters. In most cases this is sufficient, …
[Read more]
The -N or –skip-column-names is a convenient option with the
mysql client to skip the header line of output.
However I found when viewing the output via the terminal, some
interesting and unexplained output.
$ mysql -h*** -u*** -p -e "SELECT VARIABLE_NAME,VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS" +--------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------------------+----------------+ | ABORTED_CLIENTS | 710001 | | ABORTED_CONNECTS | 31 | | BINLOG_CACHE_DISK_USE | 0 | | BINLOG_CACHE_USE | 0 | | BYTES_RECEIVED | 2522301004 | | BYTES_SENT | 317785976 | | COM_ADMIN_COMMANDS | 2890667 | | COM_ASSIGN_TO_KEYCACHE | 0 | | COM_ALTER_DB | 0 | | COM_ALTER_DB_UPGRADE | …[Read more]
Installed on a clean CentOS 6.4 AWS instance.
sudo su - cd /tmp wget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar tar xvf MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar yum install -y libaio perl rpm -i MySQL*.rpm
The following output is the sign that security is being considered with new MySQL versions. Woot!
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test database. This is strongly recommended for production servers. See the manual for more instructions. Please report any problems with the …[Read more]
My slides for presentations on MySQL Backup and Recovery Essentials and Understanding and using MySQL in the Cloud from the Oracle Technology Network (OTN) event in Medellín Colombia are now available.
Thank you to the Colombia Oracle Users Group for inviting me to the event.