Archive | Mysql RSS feed for this section

Mysql slow queries

18 Nov

sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log

Can run some analysis on your slow-query log

Advertisements

Multi lingual support in MySQL/Rails/BASH

16 Apr

We had translations in text files that we needed imported into our mysql database.

Our database had UTF-8 character encoding which is correct.

Swedish characters were appearing like :åäà �
There are a number of issues here:
1. SSH session – if you’re using Putty make sure you’ve set the terminal to be UTF
2. check the type of your file using “file”
3. if necessary use ‘iconv’ to convert to UTF8 – iconv -f ISO-8859-1 -t UTF8 /tmp/fr2.yml
4. Ensure your browser is displaying UNICODE, View->Character Encoding in Firefox
5. If you are using Linux, make sure a UTF LANG is set eg.: export LANG=”en_AU.UTF-8″

mysql “having” vs “where”

13 Mar

if you are using “group by’s” – use “having”

You may use Alias’s if you use HAVING instead of WHERE this is one of the defined differences between the two clauses. Having is also slower and will not be optimized, but if you are placing a complex function like this in your where you obviously aren’t expecting great speed.”

MySQL UDF 5.0.24a

13 Sep

This was installed so that UDF could be used.

If you don’t use the patch and just use the generated .so file you’ll get an error like:

mysql> CREATE FUNCTION lookup RETURNS STRING SONAME ‘udf_example.so’;
ERROR 1126 (HY000): Can’t open shared library ‘udf_example.so’ (errno: 2 /usr/lib/udf_example.so: undefined symbol: __gxx_personality_v0)

To do this:

  1. Install the MySQL 5.0.24a binaries, make sure you use the ‘Max’ server as you need dynamic library loading
  2. Get the MySQL 5.0.24a source, go to the sql/ directory and patch the source using the diff found at http://lists.mysql.com/commits/9750. You will also need to rename udf_example.cc to udf_example.c.

Once you’ve done this, you need to compile the source using:

  1. libtool –mode=compile gcc -I. -I../include/ -c ./udf_example.c
  2. libtool –mode=link gcc -shared -fPIC -o udf_example.so udf_example.lo

Copy the resulting ‘udf_example.so’ to JS Tech Notes › Edit — WordPress/usr/lib. You can run ‘ldconfig -r -s’ to test the library.

Now get into mysql, make sure the server says its ‘5.0.24a-max’.

Test the function:

CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';
select lookup("imvs.org")

							

Can’t find file: *.MYI (errno: 2)

9 May

If you get:  Can't find file: 'myTable.MYI' (errno: 2)

It means MYI file is missing – this is okay, it can be rebuilt.

try to run (in mysql):
repair table myTable;

if you get the same error as above, run it as:
repair table myTable USE_FRM;

this should rebuild MYI  (which is an index file).

You'll have to make sure the MYD file is okay and in /var/lib/mysql/<database>

Mysql Commands

31 Mar

SELECT filename, count(filename) AS n INTO OUTFILE ‘/tmp/dump’ FROM ResultFiles GROUP BY HAVING n>1;

‘INTO OUTFILE’ – dumps to files
‘HAVING n>1’ – searches for dupicates

Mysql – find open processes

31 Mar

mysqladmin processlist –user=root -p