Error installing MySQL-python on OSX

Tring to install mysql-python:

sudo python -m pip install MySQL-python

I was getting the error:

    sh: mysql_config: command not found

Traceback (most recent call last):

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

  File "/Applications/XAMPP/xamppfiles/htdocs/associate-fork-jhon-venv/build/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

I solved the issue running:

brew install mysql

Reference:

https://stackoverflow.com/questions/1857861/libmysqlclient15-dev-on-macs/10855483#10855483

MySQL Top (N) By Group

Here is a great link what explains how to get top N elements by group in MySQL :  http://www.erikhaselhofer.com/?p=1793

Mysql Replication: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

if you get this error:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Go to the installlation/source folder of mysql and change the guid in the file ./data/auto.cnf. Generate another guid with http://www.famkruithof.net/uuid/uuidgen, copy the generated guid and replace the existing guid:

from:

[auto]
server-uuid=e80f7b10-d242-11e3-9c1a-0800200c9a66

to:

[auto]
server-uuid=839567f0-d245-11e3-9c1a-0800200c9a66

Resource:
http://planet.mysql.com/entry/?id=103096

mysql .net Unknown column in 'where clause'

I was having the error :

"Unknown column 'Extent1.Name' in 'where clause'"

The code was:

var companies = (from cp in skydeskDB.Companies
    where cp.OrganizationId == parameters.OrganizationId                                    
    &&  cp.Name.Contains(parameters.Filter)                                                    
      select cp
).OrderBy(cp => cp.Name);


the solution was changing  select cp to select new { cp } :

var companies = (from cp in skydeskDB.Companies
    where cp.OrganizationId == parameters.OrganizationId                                    
    &&  cp.Name.Contains(parameters.Filter)                                                    
      select new { cp } // here
).OrderBy(cp => cp.Name);

.Net: Unknown column 'GroupBy1.K1' in 'field list'

I was having the error

Unknown column 'GroupBy1.K1' in 'field list'

The query was:

                    var results =
                        from cs in skydeskDB.Cases
                        where
                        cs.AgentId == AgentId
                        && cs.ClosedAt >= iniDate
                        && cs.ClosedAt < endDate
                        group cs by cs.ClosedAt.Value.Hour into g
                        select new
                        {
                            closedcasecounter = g.Count(),
                            daynumber = g.Key
                        };

After spending a lot of time on internet I found a post that suggested that the problem was a mysql connector  bug so I updated it from 6.6.5 to 6.7.4. But the problem continued so I had to group the data using a for loop :(

If you find any solution, please let me know in the comments section.

Resources:
http://bugs.mysql.com/bug.php?id=46742

MySQL: stored procedures tutorial

Here is the link of a nice ans simple MySQL stored procedures tutorial:

http://www.mysqltutorial.org/stored-procedures-parameters.aspx

MySQL: Get total values from a column

At first I had:

SELECT
COUNT(casestates.id) AS statustotal
, casestates.name AS statusname
FROM
cases
INNER JOIN casestates ON cases.casestateid = casestates.id
WHERE
cases.helpdeskid = 'ee98652e-9fdf-435e-b325-74e7189b6561'
AND casestates.name != 'closed'
GROUP BY
casestates.name

The return was:

-----------------------------------------
| statustotal    | statusname |
-----------------------------------------
|       5           |      New     |
-----------------------------------------
|       18         |      Open    |

-----------------------------------------
|       14         |      Solved  |

-----------------------------------------

Know using Use the ROLLUP and IFNULL clauses:

SELECT
 COUNT(casestates.id) AS statustotal
 ,IFNULL(casestates.name,"Total") AS statusname -- changed here
FROM
 cases
INNER JOIN casestates ON cases.casestateid = casestates.id
WHERE
 cases.helpdeskid = 'ee98652e-9fdf-435e-b325-74e7189b6561'
 AND casestates.name != 'closed'
GROUP BY
 casestates.name
WITH ROLLUP; -- changed here

The return is:

statustotal statusname
-----------------------------------------
| statustotal    | statusname |
-----------------------------------------
|       5           |      New     |
-----------------------------------------
|       18         |      Open    |

-----------------------------------------
|       14         |      Solved  |

-----------------------------------------

|       37         |      Total     |

-----------------------------------------

Resources:

http://stackoverflow.com/questions/7586105/mysql-count-sum-and-group-by

http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html

MYSQL: ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

When I was trying to connect to mysql typing :

mysql -h localhost -u user -p

After typing the password I was getting this error:


ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

I resolved that issue changing the user host value from % to localhost i.e using sqlyog:

Django: _mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' (10061)")

Trying to init my Django project I was getting this error:


_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' (10061)")

I was using this configuration


DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'backendtest',                      # Or path to database file if using sqlite3.
        'USER': 'remoteuser',                      # Not used with sqlite3.
        'PASSWORD': '123456789',                  # Not used with sqlite3.
        'HOST': 'localhost',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '3306',                      # Set to empty string for default. Not used with sqlite3.
    }
}

the solution is pretty simple, change the host value from 'localhost' to 127.0.0.1:

DATABASES = {

    'default': {

        'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.

        'NAME': 'backendtest',                      # Or path to database file if using sqlite3.

        'USER': 'remoteuser',                      # Not used with sqlite3.

        'PASSWORD': '123456789',                  # Not used with sqlite3.

        'HOST': '127.0.0.1',                      # Set to empty string for localhost. Not used with sqlite3.

        'PORT': '3306',                      # Set to empty string for default. Not used with sqlite3.

    }

}

Now all works fine

Reference:

http://stackoverflow.com/questions/11121819/mysqldb-in-python-cant-connect-to-mysql-server-on-localhost

MySQL: grant remote access

GRANT ALL PRIVILEGES ON backendtest.* TO remoteuser@'%' IDENTIFIED BY '123456789';

Resource:
http://31bit.com/technology/86-mysql-database/302-how-to-grant-remote-access-to-a-mysql-database

MySQL: Adding foreing keys

ALTER TABLE phone ADD 
      CONSTRAINT FK_phone_person_num
      FOREIGN    KEY (person_num)
      REFERENCES person(num)

Resource:
http://www.sqlinfo.net/mysql/mysql_Create_foreign_key_contraints.php

Mysql 5 : Installation in Windows 7 - Error 1067 El proceso ha terminado de forma inesperada MySQL

If you try to install mysql 5 in windows 7 and you get the following error

"Error 1067 El proceso ha terminado de forma inesperada MySQL"

Search and delete the files "ib_logfile0" and "ib_logfile1" in the installation directory ( usuarlly "C:\Program Files (x86)\MySQL\MySQL Server 5.0\data")

Reference:

http://www.ajpdsoft.com/modules.php?name=Foros&file=viewtopic&t=291

MySQL - OperationalError: Error on rename (errno: 150)

Trying to execute:

alter table `bismark`.`d_area` drop index `tipo_area_id`

You Get:

Error on rename of '.\bismark\#sql-93c_1' to '.\bismark\d_area' (errno: 150)

You have to delete first the foreign key.

ALTER TABLE d_area DROP FOREIGN KEY tipo_area_id_FK;

Then you can drop the index and the column :D