Mysql localhost != 127.0.0.1?
As you can see here, a UNIX mysqld uses sockets if used without a host name or with the host name localhost. So it makes a difference, and in the GRANT system this difference becomes evident.
mysql
In MySQL, should I quote numbers or not?
MySQL is a lot like PHP, and will auto-convert data types as best it can. Since you’re working with an int field (left-hand side), it’ll try to transparently convert the right-hand-side of the argument into an int as well, so ‘9’ just becomes 9. Strictly speaking, the quotes are unnecessary, and force MySQL to do … Read more
The total number of locks exceeds the lock table size
This issue can be resolved by setting the higher values for the MySQL variable innodb_buffer_pool_size. The default value for innodb_buffer_pool_size will be 8,388,608. To change the settings value for innodb_buffer_pool_size please see the below set. Locate the file my.cnf from the server. For Linux servers this will be mostly at /etc/my.cnf Add the line innodb_buffer_pool_size=64MB … Read more
Entity-Attribute-Value Table Design
I’m going to offer a contrary opinion to most of the comments on this question. While EAV is EVIL for all of the reasons that you can find thoroughly explained many times here on SO and DBA.SE and elsewhere, there is one really common application for which most of the things that are wrong with … Read more
mysql select int as currency or convert int to currency format?
FORMAT() function: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_format SELECT CONCAT(‘$’, FORMAT(val, 2)) … ;
MySQL Cross Server Select Query
how about using federated tables on one of the servers? create the federated tables based on the remote tables you will use in the query and just run your query as if your database was all local. example below from MySQL site The procedure for using FEDERATED tables is very simple. Normally, you have two … Read more
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
this shows the different ways: — DB2 select * from table fetch first 10 rows only — Informix select first 10 * from table — Microsoft SQL Server and Access select top 10 * from table — MySQL and PostgreSQL select * from table limit 10 — Oracle select * from (select * from table) … Read more
Error: select command denied to user ”@” for table ”
I’m sure the original poster’s issue has long since been resolved. However, I had this same issue, so I thought I’d explain what was causing this problem for me. I was doing a union query with two tables — ‘foo’ and ‘foo_bar’. However, in my SQL statement, I had a typo: ‘foo.bar’ So, instead of … Read more
What effects does using a binary collation have?
Binary collation compares your string exactly as strcmp() in C would do, if characters are different (be it just case or diacritics difference). The downside of it that the sort order is not natural. An example of unnatural sort order (as in “binary” is) : A,B,a,b Natural sort order would be in this case e.g … Read more
Unique constraint that allows empty values in MySQL
Yes, you can do this. See the MySQL reference (version 5.5). A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple … Read more