Archive

Archive for the ‘MySQL’ Category

pip install MySQL-python fails

August 25, 2015 Leave a comment

Problem:
I was trying to install MySQLdb for python on an Ubuntu box (Virtual Machine). But pip fails with below error.

vagrant@vagrant-ubuntu-trusty-64:~$ sudo pip install MySQL-python
Downloading/unpacking MySQL-python
  Downloading MySQL-python-1.2.5.zip (108kB): 108kB downloaded
  Running setup.py (path:/tmp/pip_build_root/MySQL-python/setup.py) egg_info for package MySQL-python
    sh: 1: mysql_config: not found
    Traceback (most recent call last):
      File "<string>", line 17, in <module>
      File "/tmp/pip_build_root/MySQL-python/setup.py", line 17, in <module>
        metadata, options = get_config()
      File "setup_posix.py", line 43, in get_config
        libs = mysql_config("libs_r")
      File "setup_posix.py", line 25, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    EnvironmentError: mysql_config not found
    Complete output from command python setup.py egg_info:
    sh: 1: mysql_config: not found

Traceback (most recent call last):

  File "<string>", line 17, in <module>

  File "/tmp/pip_build_root/MySQL-python/setup.py", line 17, in <module>

    metadata, options = get_config()

  File "setup_posix.py", line 43, in get_config

    libs = mysql_config("libs_r")

  File "setup_posix.py", line 25, in mysql_config

    raise EnvironmentError("%s not found" % (mysql_config.path,))

EnvironmentError: mysql_config not found

----------------------------------------
Cleaning up...
Command python setup.py egg_info failed with error code 1 in /tmp/pip_build_root/MySQL-python
Storing debug log for failure in /home/vagrant/.pip/pip.log

Solution:

vagrant@vagrant-ubuntu-trusty-64:~$ sudo apt-get install python-dev libmysqlclient-dev
vagrant@vagrant-ubuntu-trusty-64:~$ sudo pip install MySQL-python
Downloading/unpacking MySQL-python
  Downloading MySQL-python-1.2.5.zip (108kB): 108kB downloaded
  Running setup.py (path:/tmp/pip_build_root/MySQL-python/setup.py) egg_info for package MySQL-python

Installing collected packages: MySQL-python
  Running setup.py install for MySQL-python
...
...
Successfully installed MySQL-python
Cleaning up...
vagrant@vagrant-ubuntu-trusty-64:~$ python
Python 2.7.6 (default, Jun 22 2015, 17:58:13)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> exit()
vagrant@vagrant-ubuntu-trusty-64:~$


Source:

http://stackoverflow.com/questions/25865270/how-to-install-python-mysqldb-module-using-pip

Advertisements
Categories: MySQL, python Tags:

bash – Mimic MySQL GROUP BY function with awk

November 20, 2012 1 comment

Problem:
I needed to get GROUP BY results and I did not have the time to wait to restore the mysqldump and run the commands. I had like over 360 zipped files to work with. Restoring each database and running the query even in a script was just too long a process. I opted to just extract the data and use awk to mimic GROUP BY.

Solution:
I used sed to extract the desired results from the mysql dump files. Then took the relevant date fields and used awk to do the group count.

Here is the SELECT…GROUP BY using mysql

$ mysql -u <user> -p<password> 
-e"SELECT date_format(timestamp,'%Y-%m-%d') as 'Date',count(distinct(<field>)) FROM <table>
WHERE <search criteria>
GROUP BY date_format(timestamp,'%Y-%m-%d')
ORDER BY date_format(timestamp,'%Y-%m-%d')" | sed 's/\t/,/g'
2012-10-10,4
2012-10-11,21
2012-10-12,19
2012-10-13,8
2012-10-14,22
2012-10-15,7
2012-10-16,13

Here is the same but now using awk. After extracting the data from the dumps I end up with the following

$ head -n 3 <file-with-extracted-content>
2012-10-01 06:08:54|377772327281
2012-10-01 06:08:55|371202225977
2012-10-01 06:08:55|061712877272
$ 
$ awk -v FS=" |\|" '{print $1, $3}' <file-with-extracted-content> | 
sort -u |  
awk -v OFS=, '{a[$1]+=1}END{for (i in a) print i,a[i]}' | 
sort -t, -k1
2012-10-10,4
2012-10-11,21
2012-10-12,19
2012-10-13,8
2012-10-14,22
2012-10-15,7

Categories: awk, bash, MySQL

MySQL – Moving the datadir

October 29, 2012 Leave a comment

Problem:
My default mysql location is /var/lib/mysql and I needed to restore a database that takes up a few GB of data. My root partition where /var is does not have enough space.

Solution:
Move the location of mysql’s datadir. Follow the steps below. (I had upgraded mysql and had made some changes as noted in this earlier post)

1. Stop mysql

root@computer:/home/user# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

2. Edit my.cnf
In my case I had renamed it to some thing else. So I put the file back.

root@computer:/home/user# cd /etc/mysql
root@computer:/etc/mysql# cp my.cnf.issues my.cnf

Now edit the line
from

datadir = /var/lib/mysql 

to

datadir = /home/user/mysqldatadir

3. Copy the contents of /var/lib/mysql to the new location /home/user/mysqldatadir.
I did this a few times so I am guessing that you only need to copy the folder that has the default mysql database. The one that normally shows up when you have a fresh mysql install. As shown below.

mysq> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
So 
root@computer# cd /var/lib/mysql
root@computer:/var/lib/mysql# ls
debian-5.1.flag  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql_upgrade_info  carsdb

Now copy mysql folder to the new datadir location.
Also copy mysql_upgrade_info file.
Try and preserve the file permissions. The other files ie ibdata1 and ib_logfile* will be created. the folder carsdb is database that I do not need moved. (I intend to copy that over by first dumping the data using mysqldump.)

root@computer:/var/lib/mysql# cp -prv mysql /home/user/mysqldatadir/
root@computer:/var/lib/mysql# cp -prv mysql_upgrade_info /home/user/mysqldatadir/

4. Now change the permissions of the new datadir to belong to mysql

root@computer:/home/user# chown mysql:mysql  mysqldatadir/

5. Start up mysql

root@computer:/var/lib/mysql# cd /home/user/
root@computer:/home/user# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

root@computer:/home/user# ls -al mysqldatadir/
drwxrwxrwx  5 mysql   mysql       4096 Oct 26 16:25 .
drwxr-xr-x 81 user user     4096 Oct 26 16:24 ..
-rw-rw----  1 mysql   mysql   10485760 Oct 26 16:24 ibdata1
-rw-rw----  1 mysql   mysql    5242880 Oct 26 16:24 ib_logfile0
-rw-rw----  1 mysql   mysql    5242880 Oct 26 15:54 ib_logfile1
drwx------  2 mysql   mysql       4096 Jun 22 15:55 mysql
-rw-------  1 mysql   mysql          6 Jun 22 15:55 mysql_upgrade_info

6. Nothing else should change. So your previous users are still valid.

user@computer:~$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

7. If mysql fails to start have a look at the syslog. Should be near the end of the file.

root@computer:/home/user# less /var/log/syslog

Source:
http://rajshekhar.net/blog/archives/90-Moving-the-MySQLs-datadir-directory..html

Categories: MySQL Tags:

MySQL – ERROR 1030 (HY000): Got error 28 from storage engine

October 25, 2012 Leave a comment

Problem:
Got this error when trying to restore a database.
ERROR 1030 (HY000): Got error 28 from storage engine

Solution:
Turns out that I was running out of space on /var partition where mysql is. The database I was restoring takes up about 3 GB, 1.1 GB was not enough. So the restore starts but does not end.

user@computer$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1      19G  3.1G   15G  18% /
tmpfs                 2.0G     0  2.0G   0% /lib/init/rw
udev                   10M   64K   10M   1% /dev
tmpfs                 2.0G     0  2.0G   0% /dev/shm
/dev/sda5     9.2G  4.2G  4.6G  48% /home
/dev/sda7     100G   94G  1.1G  99% /var

Just needed to free up space. Fortunately, I was able to delete the mysql bin longs that were taking up almost 80GB of space.

user@computer$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1      19G  3.1G   15G  18% /
tmpfs                 2.0G     0  2.0G   0% /lib/init/rw
udev                   10M   64K   10M   1% /dev
tmpfs                 2.0G     0  2.0G   0% /dev/shm
/dev/sda5     9.2G  4.2G  4.6G  48% /home
/dev/sda7     100G   13G   83G  14% /var

Once that was done, I was able to restore the database.

Source:
https://forums.mysql.com/read.php?35,84004,224693#msg-224693

Categories: MySQL

MySQL – Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

March 30, 2012 Leave a comment

Problem:
I upgraded mysql server from 5.1 to 5.1.6 and when I try to log in I get the following error.

user@computer:~$ mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Solution:
After a quick search on google this is what helped me.
1. Completely remove mysqlserver-5.1.6
2. I renamed the existing /etc/mysql/my.cnf to /etc/mysql/my.cnf.doesnotwork.
3. Install mysqlserver-5.1.6 again.

Source:
http://forums.debian.net/viewtopic.php?f=17&t=59674

Categories: MySQL

MySQL Error – Operand should contain 1 column(s)

March 26, 2012 Leave a comment

Problem:
I was getting the following mysql error in python.
OperationalError: (1241, ‘Operand should contain 1 column(s)’)

Solution:
Convert the python list to a string.

Here is what I was not doing.

>>>my_list
['First', 'Second', 'Third']

Instead of

INSERT INTO table (data) VALUES (str(my_list))

I was doing this

INSERT INTO table (data) VALUES (my_list)

Source:
This helped narrow down the issue.
http://lists.mysql.com/mysql/189034

Categories: MySQL Tags:

MySQL – Remove a character from a string

July 1, 2011 Leave a comment

Problem:
Had to strip out a plus sign at the beginning of some text in the database.

Solution:
Update command and replace function in MySQL. In this case I was getting rid of the plus sign.

UPDATE `table` SET db_field=REPLACE(db_field, ‘+’, ”) where db_field like ‘+3989%’ limit 5;

Source:
http://eisabainyo.net/weblog/2007/02/26/remove-the-first-character-in-mysql/

Categories: MySQL Tags: , ,