Tag: mysql

PHP database can’t connect to localhost but 127.0.0.1 works

There are many reasons for this, so first check that MySQL/MariaDB is running properly, that you are using the correct credentials and that the the appropriate user@localhost has access.

Also check you aren’t using skip-name-resolve and bind-address in your my.cnf file if accessing from outside of the localhost. These are things you will find with a google search.

Apart from this one other reason why 127.0.0.1 will work and localhost will not work is if you are using something other than the default socket=/var/lib/mysql/mysql.sock location in your my.cnf.

If that is the case, you’ll need to update the relevant default_socket= parameter in php.ini (for each driver) to match that socket – see the entries under my.cnf [mysql] and [mysqld] sections for the socket parameter. This is more likely to occur if you are using MariaDB for example.

MySQL compare strings by case

Here’s one method of comparing strings by case in MySQL. You can use the md5 function to hash the string and compare to its lower case value.

This example is for checking for usernames that are not lower case in moodle.

select username, md5(username), md5(lower(username))
from mdl_user
where deleted = 0 and suspended = 0
and md5(username) != md5(lower(username));

If the md5 hash when the string is lower cased is different to the current md5 hash you will get a result.

MySQL adjust auto-incrementing id field value

You can adjust the value of an auto-incrementing id field in MySQL using the following syntax:


ALTER TABLE {table_name} AUTO_INCREMENT = {starting_value};

But you need to be careful that you use the max(id) from that table to avoid hitting an existing value. To make sure, I use the follow meta-SQL to generate my script:


select concat('ALTER TABLE {table_name} AUTO_INCREMENT = ', max(id), ';')
from {table_name};

Simply replace {table_name} with the appropriate table and run the ALTER TABLE SQL that this query produces.

To be precise, the starting value set will be incremented when a new row is added, so it can be equivalent or greater than the largest id value in the table.

MySQL and Unix Timestamps

MySQL comes with two handy functions for working with unix timestamps. The unix timestamp or unix epoch time represents the number of seconds since midnight (UTC) 01/01/1970.

The two functions are:

  • unix_timestamp() to convert a standard date to unix timestamp format
  • from_unixtime() to convert a unix timestamp to standard date format

Note if you want the current date only (not time) in unix format use:

select unix_timestamp(curdate());

If you want the current date AND local time in unix format use:

select unix_timestamp(now());

Select Users from Alfresco Database

The following SQL (designed for MySQL as it uses group_concat) pulls out a list of users from the Alfresco database by searching for the appropriate nodes in the Alfresco node table and pulling user node properties including username, firstName, lastName and email address.

select
    node_id,
    group_concat(string_value)
from (
    select
        node_id,
        qname_id,
        (select local_name from alf_qname where id = qname_id) as qname_type,
        string_value
    from alf_node_properties
    where node_id in (
        select id from alf_node 
        where type_qname_id = (
            select id from alf_qname where local_name = 'person'
        )
        and qname_id in (
            select id
            from  alf_qname 
            where local_name in (
                'username',
                'firstName',
                'lastName',
                'email'     
            )
        )
    )
) alf_users
group by node_id;

MySQL group_concat()

The group_concat() function in MySQL is really nifty. It concatenates all rows that match your group by statement into a single, comma separated string. Essentially, it transposes rows to columns.

Take this example. Say you have a table that stores a set of locations by country, state and city.

You could then write SQL as follows:

select 
  country, 
  state, 
  group_concat(city) as cities 
from locations 
group by country, state

What this is saying is bring back 3 columns, country, state, and for each combination of country and state, a list of all of the cities in that country and state as the third column (called cities).

This is actually a phenomenally handy thing to be able to do, and something that is much harder to do in any other DBMS.

MySQL Engine

To see what storage engine is used by the server by default:

mysql> show variables like '%storage_engine';

To find out what storage engine your tables are using by schema:

mysql> select TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.tables;

Performance Tuning Advice

I’ve spent a lot of time this year investigating and resolving performance issues due to high load on a PHP web application (Moodle). The load we are talking about here is up to around 2000+ visitors per hour (accordingly to Google Analytics).

The following is a list of general advice on performance tuning. Note it isn’t all technical. How you go about the process is just as important as the technical changes.

  • Implement some form of analytics to track load. Google analytics with the real time monitor is fantastic. Don’t switch this off, even during high load, otherwise you will be flying blind and it will hurt your efforts more than help (if you can’t measure it, you can’t improve it).
  • Invest time on proper load testing scripts that simulate high load behaviour so you can measure and improve. Jmeter is great for this, but there are many tools out there to help you.
  • Apache processes will creep up to maximums (e.g. 256 for prefork worker processes) when they are waiting on something (database, disk etc). Tuning at this level includes tweaking Apache config, PHP config and PHP caching (e.g. eAccelerator). Other options include proxies and caching. This will improve load handling and the speed of your site. Also ensure that your application is caching as much as possible (javascript, css, http headers etc).
  • The database (MySQL in this case) has a huge impact on performance. Two very important things to look at are thread cache size (make sure it is on) and if using InnoDB, your InnoDB buffer pool size. Be sure to check out the excellent MySQLTuner perl script to help you get this right. Monitor your database and logs to find what might be holding things up. Apache relies on MySQL being snappy, if it is slow you’ll have bottlenecks further up the chain.
  • For all layers, its important to realise that the defaults won’t do. Tuning/tweaking is required to get the most out of your web server and database server. There’s a wealth of information out there, do your research and monitor your system to find the bottlenecks.
  • Document your changes. If you don’t know what you changed, then how can you tell what helped (or didn’t)? Be systematic. This isn’t the time to go around randomly changing things and hoping for the best.
  • Don’t make assumptions. It might sound strange but this is a huge one. A lot of performance tuning efforts are hindered by people assuming where problems are rather than finding them through proper metrics (e.g. load testing and monitoring). If you can’t measure it, you can’t call it (regardless of your “experience” and so on).

Time (benchmark) your commands

Perhaps unexpected, the time command, doesn’t actually give you the system time (use date for that). What it is does is “time” how long something takes to run (so a different use of the word time).

So for example, let’s say you are doing a mysql import, you can prepend it with time to find how long it took to run:

$ time mysql -u user -p db < import_file.sql

real 1m0.377s
user 0m0.637s
sys 0m0.126s

A quick and useful benchmark, even if it only lets you know how long you have to go make a coffee 🙂

Auto-number Rows in MySQL

The following SQL snippet is a really simple example of how to add an “id” field that is auto-numbered based on the number of rows returned by a query in MySQL.

select
    @id:=@id+1 as id,
    u.*
from
    (select @id := 1000) id,
    mysql.user u

This takes the data in the users table in MySQL and adds a column to the start called “id” which starts at 1000 and increments for each row (1001, 1002, 1003 … n).

Handy for creating things like id fields in inserts etc.