February 12, 2013
No Comments
I have recently been running some MySql scripts that wrote to a file.
These worked fine locally, but as soon as I deployed them I started to get the error above.
After much looking around I came across this solution.
When I was developing locally, I was connecting with a user that had global privileges. When I was running the code on the server I was connecting with a user that only had privileges for the database I was using.
The issue is that then FILE privilege is a Global setting, so the user did not have access to it, hence the access denied message.
Grant FILE privileges and you can connect as expected
September 18, 2012
No Comments
According to pretty much every source there’s only one sensible way to migrate from 5.0, 5.1 or 5.2 to 5.5 of mysql (or mariadb/percona for that matter), and it’s not pretty (for a server with only two active magento installations for instance, this will take about 3 hours).
The answer is that you have to do an sql dump and restore. This does take an age but does at least provide the reassurance that you are going to have nice clean databases after the upgrade.
So the commands are :-
mysqldump --all-databases --routines --events > fulldump.sql
mysqldump mysql --tables user > users.sql
Then upgrade mysql binaries to 5.5 and empty the database directory – such as /var/lib/mysql and start mysql with it’s script (e.g. /etc/init.d/mysql start). Note, if you’re using a distribution that doesn’t come with start scripts, you may want to run mysql_install as root before starting mysql however you start it.
Then the following will restore the databases and users :
mysql -u root # add -p if you set up a default password when upgrading/installing 5.5
\r mysql
source users.sql
source fulldump.sql
FLUSH PRIVILEGES;
\q
August 15, 2012
No Comments
If you are scratching your head trying to figure out why you keep getting MySQL server gone away error messages despite the fact you have bumped up all the timeout etc configurations to high values then this could be your solution.
MySQL will also give you this error if you try to send a packet that is larger than the packet size defined. We had an application that was using MariaDB which has a default max allowed packet of 16M by default.
The application in question was sending large amounts of data to be stored and so the solution to the gone away issues was simply to increase the max_allowed_packet configuration in my.cnf, restart the mysql daemon and the problems are sorted.
mysql, By:
admin
No Comments
Tags:
allowed,
away,
error,
gone,
maria,
max,
my.cnf,
mysql,
packet,
problem,
server,
solution
August 14, 2012
No Comments
Mytop is a handy utility which shows what queries are currently being executed on your MySQL server – useful for those times when you’re not quite sure if it’s still on the job
Running mytop is easy, just run it from the command line and it’ll give you a top-like list of any queries currently being executed on the server
You need to tell my top how it should connect to your database. You can specify everything as parameters on the command, such as
$ mytop -u=myuser -p=mypassword --db=mydatabase -P=3306
But chances are you’re not changing your database server that often.
Setting the default parameters for mytop
If you want to save yourself the effort of repeatedly typing the same details over and over, just tell mytop what you want it to choose if you don’t tell it any parameters
All you need to do is let mytop know how to connect to your MySQL database in the form of a config file called .mytop in your home folder, with the following contents (replace as necessary):
user=myuser
pass=
host=my.host.name
db=
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
You can add as many or as few of these as you like for easy configuration, like specifying the db flag if you’re primarily accessing only one database. I like to leave the password field blank, and use the –prompt flag to avoid any awkward leaks!
January 16, 2012
1 CommentJanuary 12, 2012
2 Comments
Many database queries rely on seeing if two columns match / differ, however sometimes you may want to check if either column contains a sub-string of the other. MySql allows you to do this, but it isn’t that well documented, so hopefully this will help someone looking for this in the future
SELECT *
FROM
`table`
WHERE
`col1` LIKE CONCAT('%', `col2`, '%')
OR col2 LIKE CONCAT('%',`col1`,'%')
November 10, 2011
No Comments
When migrating one server to another you often hit bumps in the road. Todays was transferring a database from one server to another.
During this standard procedure I found that the restored database was missing a few tables. Irritating as Magento doesn’t like missing tables.
Digging down into the backup and extracting the first missing table I was able to replicate the error which came out as
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE
I eventually found out that some versions of mysql 5.1 export a dump file that contains mysql5.1 specific features and loading the file into mysql 5.0 will not work.
The solution is a little frustrating but if you run the command with the –compatible=mysql40 switch, the dump file extracts fine :-
mysqldump --compatible=mysql40
Don’t ask me why there’s no –compatible=mysql50 flag.
October 13, 2011
1 Comment
If you need to update a large number of rows on a single table then your first reaction may be to write a loop that updates one row at a time.
Of course if the table is large then this can result in a very large number of SQL queries.
Taking a bit of inspiration from this post I have put together this simple PHP function that will allow you to update as many rows as you want.
The trade off is basically memory usage as you build up a big array of row update information preparing for the batch. For this reason you may want to tune the number of rows you update per query.
function bulkUpdateSingleColumn($table, $id_column, $update_column, array &$idstovals){
$sql = "update $table set $update_column = CASE $id_column ";
foreach($idstovals as $id=>$val){
$sql .= " WHEN '$id' THEN '$val' \n";
}
$sql .= " END
WHERE $id_column in (" . implode(',', array_keys($idstovals)) . ")";
//debugging info
echo '<small>'.$sql.'</small>';
$idstovals=array();
db_query($sql);
done();
}
mysql, By:
admin
1 Comment
Tags:
bulk,
ecommerce,
function,
mysql,
performance,
php,
rows,
table,
tip,
update
October 11, 2011
No Comments
Today I was working on an oscommerce site and took a backup of their database, created the usual details from their config file on my local mysql and the above error came up (with admin@localhost for what it’s worth).
Upon investigation, a trigger had been created on the database whilst logged in via a different user than the user specified in the config file.
There’s actually a few solutions depending on your needs :-
Ensure the user (something@somewhere) is created on your new database installation
Delete the trigger (for instance if testing locally and you know it’s not needed)
Edit the DB dump file, find the definition of the trigger and change it to the configured user.
January 15, 2011
No Comments
MySQL has a nice feature that you may well not have used called Triggers.
These are little SQL statements that are tied to events on table and then perform some extra work.
The idea being that you will check the values for a row and if they meet some conditions you will take some further action. You might usually do this kind of thing in code but if you really must be certain that in a certain circumstance a certain action will be taken, then triggers are a good option because regardless which bit of code makes the change you can be sure your rules will be enforced.
Of course this ties you to a specific DB platform so its not something you should over use. There can also be performance issues so you need to bear that in mind.
Anyway, assuming you know what you are letting yourself in for and have read the documentation here is my little summary.
First you need to give your trigger a name. Choose something descriptive.
Then you need to associate it to an even (UPDATE, INSERT etc) and also decide whether to do it BEFORE or AFTER. If you are not sure, use BEFORE – it’s generally the one you want to use.
CREATE TRIGGER do_something BEFORE UPDATE ON table
...
The next bit is boiler plate as far as I can gather, just whack it in. It delimits your actual statement and tells it to be run for each updated row.
FOR EACH ROW BEGIN
Then do your test
IF ((NEW.column < 1) AND (OLD.column >=1)) THEN
Note the NEW and OLD keywords there.
You have a choice of using the NEW and OLD keyword to access existing (OLD) and about to be set (NEW) data for each column.
Note also, this is only possible if you use the BEFORE event as opposed to AFTER, hence generally just sticking to using BEFORE
Now, enforce your requirements
SET NEW.another_column = 0;
SET NEW.and_another_column = 0;
SET NEW.and_another = 'cheese';
Then wrap it up
END IF;
END;
That’s it…
BUT WAIT
There’s another bit that I totally missed which caused me issues – you need to wrap the whole thing in a delimiter statement:
delimiter //
... the statement
END;//
delimiter;
So the full final thing should look like this:
delimiter //
CREATE TRIGGER do_something BEFORE UPDATE ON table
FOR EACH ROW BEGIN
IF ((NEW.column < 1) AND (OLD.column >=1)) THEN
SET NEW.another_column = 0;
SET NEW.and_another_column = 0;
SET NEW.and_another = 'cheese';
END IF;
END; //
delimiter;
Another point to mention is – just use the CLI for handling these, don’t try to use phpMyAdmin etc – it doesn’t really work.
