We can easily kill all mysql requests of a certain user with the elegant one:

select concat('KILL ',id,';') from information_schema.processlist where user='user123';

We replace user123 with the user we want and run in mysql and everything is OK 🙂

We bought the Fantastico Deluxe installer last week, which in my humble opinion is one of the most decent for CPanel servers. We installed it, tested it and everything went smoothly. Today a client reported a problem with the encoding of a wordpress installation. I reviewed things and immediately shed light on the problem, the databases were encoded by default Latin1 instead of UTF8 as it was supposed. It's even more fun, that in phpmyadmin it is written that UTF8 is used by default, drama. I decided to look at the Fantastico files to see if there was somewhere where I could provide the default database settings at first glance I didn't see anything. Then something rushed me to see what is in my.conf and what to see there were no corresponding settings in the configuration and everything works as it is set by default. The Mysql server is hardcode to use UTF8 if it is not configured with other settings and the Fantastico is obviously with Latin1 ( which is a pretty stupid decision). The solution as always is trivial added 2 order c [mysqld] part to make UTF8 the default encoding and everything falls asleep 🙂

character-set-server=utf8
collation-server=utf8_general_ci

I have no idea why I missed these settings given that I was playing to do a few “fine” mysql settings.

Enhanced by Zemanta

Преди няколко дни излезе XAMPP 1.8.0 вчера след надграждане от версия 1.7.7 имах доста интересен проблем. Phpmyadmin-а не ми се отваряше и изгърмяваше със 403

Access forbidden!


New XAMPP security concept:

Access to the requested object is only available from the local network.

This setting can be configured in the filehttpd-xampp.conf”.

Веднага отворих httpd-xampp.conf който при мен се намира в /opt/lampp/etc/extra/, на пръв поглед всичко изглеждаше наред. Правилата за локалната мрежа бяха наред. Отделно че отварях от localhost. WTF ??? Погледнах log-а гледам че достъпа ми е отрязан от конфигуацията. Тука вече нещата ме ахнаха и честно казано донякъде малко на късмет открих проблема. След като преглеждах httpd.conf-а видях в Allow/Deny клаузите един последен ред Require all granted. О да еврика. Това е новия контролен механизъм който влезе в apache 2.4.x. С него се дава достъп или се отказва такъв на всички изискани, в общи линии се имитира Allow/Deny функционалността :). За да поправим проблема добавяме Require all granted в директивите за папката /opt/lampp/phpmyadmin. After the changes, it looks like this to me

<Directory “/opt / lampp / phpmyadmin”>
AllowOverride AuthConfig Limit
Order allow,deny
Allow from all
Require all granted
</Directory>

 

Viangi can try other savages, for example to rename the phpmyadmin folder to something else and make an alias to no. But it's uglier and not very meaningful 🙂

p.s I was asked why I use XAMPP and not a clean installation of all components as my Debian gave birth to them – the answer is very very simple – LAZINESS. I'm too lazy to write a few commands and then touch my conf and so on. It is much easier to download the whole package, unzip and burn 😉

Enhanced by Zemanta

Image representing MySQL as depicted in CrunchBase

Some time ago I wrote about MySQL Full Text Search 🙂 Today I had a very interesting experience with one request. In general, the query looks for results that are missing from another table. One basic Select and one sub select in the WHERE part of the query. In general, the skeleton is

SELECT DISTINCT (
`field`
)
FROM `table1`
WHERE `someID` =44
AND `firsTextField` NOT
IN (

SELECT DISTINCT (
`secondTextField`
)
FROM `table2`
WHERE `otherID` =44
)

Basically a simple query. I wrote it for 30 sec I run it and the machine loops. After a long and patient wait on my part or more precisely ~ 43 sec . I spat out the result lol . Pffff madhouse. I enter the machine watching the CPU is normally loaded almost in idle condition. Shock and horror. I run the query again the same result. Fuck WTF. I run explain the query and everything shines – the second field secondTextField is only full text search without index, and there the plate is modest of about 35k lines. Who to read – full text search is not an index. The problem is quickly clear one

ALTER TABLE `links` ADD INDEX ( `linkUrlID` ) 

And things fell into place Query took 0.0005 sec 😀

Be careful how you put your indexes, the speed of the query depends on them marginally.

p.s In general, I am to blame for the above situation not only because it lacks an index but because it does not use the full text search method 😀

Enhanced by Zemanta

Yesterday after updating the mysql version, my server started screaming at me, that there is a plate, which has not been closed cleanly and needs repair and so on. Blah, what will be this table, after all, I have about 30 on this server. One option is to see in the logs what is written on the issue and run a fix on the table or the other option – far better – is to run a fix, check and optimize all tables. For this purpose I will use the mysqlcheck tool. In general, the options in this case are as both commands are synonymous with each other:

mysqlcheck -Aor -u root -p

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Generally, whichever command you use, the effect will be the same – aromatic correction, проверка и оптимизация на всички таблици. After writing either of the two commands, you will be asked for the root password of your mysql server.