MySQL

Mac OSX 10.8 PHP, mysql.sock not found

I found that after install MySQL and Apache/PHP – that the database connections didn’t work from PHP. They worked if I tried to connect using the MySQL client though. Must be a problem with the PHP side.

There is a sock file that PHP was trying to use, and it was in /var/mysql/mysql.sock complaining that the file was not there.

Warning Error: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock) in [/filename.php, line number]

Sure enough, the file wasn’t there, and infact the folder was not there either. So I made a folder and created a symlink to the one that was there, in /tmp/mysql.sock

sudo mkdir /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

Restarted Apache and MySQL just to make sure. And hey presto its working.

sudo apachectl restart
sudo /usr/local/mysql/support-files/mysql.server restart

mysql_connect(): A connection attempt failed

mysql_connect(): A connection attempt failed because the connected party did not properly respond after a period of time

Shouldn’t be using these functions anymore anyway as they are discouraged, however, if you are using localhost as your database host, try using the IP address – 127.0.0.1 instead.

I had this problem on Windows 8 Customer Preview, and this has fixed it. I think there must be some confusion over the machine names, or something.

MyODBC and Access – Urgh

Well. I have had to sort out a report for someone who likes using Microsoft Access.

We use the MySQL ODBC driver, called MyODBC Connector. But its relationship with Access is riddled with bugs. Can’t decide who to blame, but its pretty horrific.

So I created a blank Access database and connected the tables I required for my report. Alas, one of the fields has a name length of 16 or so characters, so this crashes Access completely. Great.

I made a copy of the MySQL table and renamed it to something shorter. This worked. So, I needed to alias the table somehow.

I created a VIEW in MySQL with a shorter name:

CREATE VIEW smallname SELECT * FROM longernamedtable;

I was able to link this in Access fine. Not a problem, however, all the VARCHAR fields in the view came out as chinese characters. AARGH

So, I managed to utililise the MERGE engine. Here goes:

CREATE TABLE smallname SELECT * FROM longernamedtable;
DELETE FROM smallname;
ALTER TABLE smallname ENGINE=MERGE;
ALTER TABLE smallname UNION=(longernamedtable);

Now, we have a mirrored table. Brilliant. It linked to Access fine, and the VARCHAR’s were back to normal.

Thought I would share that with you. Because this little problem was starting to drain all the life out of me…

 

PS. The tables need to be ISAM for this to work.

MySQL Rename Field

To rename a field, there are loads of examples and different syntax on the web, so I thought I would blog the only one that works for me:

ALTER TABLE <table_name>
    CHANGE COLUMN <field_name> <new_field_name>;

I think you have to specify the field type even if you aren’t changing it.

ALTER TABLE clients
    CHANGE COLUMN telephone telephonenumber
    VARCHAR(40) DEFAULT NULL;

Copy a record within the same table MySQL

Just a simple copy record function.

First of all, pick a random temporary table name, and ensure you don’t have a table in your database by that name.

Then, copy the record you want to copy, from the source table, into a temp table:

CREATE TEMPORARY TABLE <temp_table>
    SELECT * FROM <source_table>
    WHERE <source_table_id_field> = <id_to_copy>;

Because we want the new record to have a new ID number, lets drop that field from the temporary table:

ALTER TABLE <temp_table> 
    DROP <source_table_id_field>;

Now we just copy it back in, but because we are one field short (the ID one) then we just pass a zero instead and let MySQL go and get a new ID from the auto increment engine.:

INSERT INTO <source_table>
    SELECT 0, <temp_table>.* FROM <temp_table>;

Job done. Just need to tidy up and drop the temporary table (careful you delete the right one)

DROP TABLE <temp_table>;

PS. This will only work if your auto increment ID field is the first field in the table (which is generally the case for every database table I have ever worked with)

How to install MySQL Server on Centos

To install MySQL on Centos is a little different from Ubuntu – as I found out. Obviously APT is replaced with yum on this flavour of Linux, but not only that, yum doesn’t appear to set it up for you after installing it (with root account, etc).

Step 1, Install MySQL

> yum install mysql-server mysql

This will download the files and install them in the correct folders, as apptitude would. Step 2 shows you how to prepare the MySQL service.

Step 2, Configure the service

Tell the system to start the MySQL Daemon upon startup:

chkconfig --levels 235 mysqld on
service mysqld start

Now MySQL is installed and running, but the root account will have no password, so we need to ensure there is a strong one. (Or not if you are a crazy one) – You will get some message when the service starts for the first time related to security measures, I would suggest you do read it.

Step 3, Set up Root user

> mysql -u root
> SET PASSWORD FOR 'root'@'%' = PASSWORD(<password>);

Replace <password> with your desired password.

Obviously change the root user details based on your particular set up. I.e. where the root account can connect from, which servers, ips, etc.

Logging MySQL Queries

I often set up mysql query logging when I am diagnosing a problem.

In the my.cnf file, enter (or update the log parameter)

log=/path/to/logfile.log

Remember to restart the mysql service:

sudo service mysql restart (ubuntu)
sudo service mysqld restart (centos)

And, ensure that the log file has the correct permissions for mysql to write to it.

After that you can tail the file to see what’s going on:

tail -f /path/to/logfile.log

 

Using MAX and DATE_FORMAT to get the latest date from a MySQL table

Don’t use DATE_FORMAT when trying to assertain the latest date in a table. It won’t work. DATE_FORMAT will convert the dates to a string, and then the MAX will compare the strings, not the dates.

SELECT MAX(DATE_FORMAT(date_field, '%d-%m-%Y')) AS latest_date 
FROM table;

Is NOT the same as;

SELECT MAX(date_field) AS latest_date 
FROM table;

The latter is the correct way of doing it.

 

What is database sharding?

Databases, by their own nature, become large beasts. This is a fact of life and whilst a lot of databases don’t encroach on “unusable” sizes, some do.

There is a practise called sharding that allows the database content to be split across servers.

If you were to take a very simple example, take a customer database and look at the main table, “customers” – this is going to be an ever growing table of customers details. If the business in question is very successful, the database may grow very quickly indeed. Historicaly you would have all the details in one table. Customer names, addresses, phone numbers, etc. This isn’t the most efficient way of storing a lot of data.

We have two methods of making it more efficient. Horizontal and Vertical partitioning. You may have heard of partitioning before in relation to disk drives, and its loosely based on the same principle. Putting data in different places to keep things tidy and more efficient.

  1. Horizontal Partitioning is known as sharding. This involves splitting the data and tables and putting some data in one and some in others. In our example, we could have all customers starting with the letters A-M in one table, and the N-Z customers in another. Now potentially the tables are halved in size. And then you just have to change your lookup service to distinguish which table to get the data from. You could split it even further, down to Country, or Post Code, etc.
  2. Vertical Partitioning is where you take the customers table, and split the fields out into separate tables. So you might have all the static details for the customer (stuff that doesn’t change very often, like name, address, email, etc) in one table, and data that changes often in another table. The static tables get cached and therefore become quicker, and the table size shrinks as it hasn’t got all the data in it. This is also sometimes called database normalisation.

That was just a small explanation about Database Sharding for you.

MySQL Error in create table syntax ‘USING BTREE’

We had an odd thing today, whilst performing a apply-db-changes we received the error:

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 'USING
BTREE, KEY `index` (`fields`)' at line 1

It worked fine before, so why won’t ant create this table?

Well its down to the USING BTREE on MySQL Server version 5.0 using a InnoDB or MyISAM table. You can only have BTREE as an index and thus any reference to it on 5.0 seems to fail. May as well just take out the reference to the index type.

Annoying if you can’t work out the error, but now you know.