For any (not very clear to me the reasons) I forgot to make an update postgresql daemon under the action of mediators of inflammation in the update of one of the Debian servers I. Postgresql daemon has the nice property of not beginning to use the new version (unlike Mysql) while not convincing, what's new fully compatible with the launch – very useful for large databases. The upgrade process limited to, the following 2 steps:

  • pg_dropcluster
  • pg_upgradecluster

Before estropia pg cluster daemon has to be stopped!

pg_dropcluster 9.4 main

This command passes quickly, then we move on to highlight – the simple upgrade

pg_upgradecluster 9.1 main
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.4/main ...
config /etc/postgresql/9.4/main
data   /var/lib/postgresql/9.4/main
locale en_US.UTF-8
Flags of /var/lib/postgresql/9.4/main set as -------------e-C
port   5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Fixing hardcoded library paths for stored procedures...
Upgrading database xpqt...
Analyzing database xpqt...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Copying old server.crt...
Copying old server.key...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

pg_dropcluster 9.1 main

If everything is smooth minlo you should receive a message like the above, which prompts you to out old data from pg.

pg_dropcluster 9.1 main

At the end of this you can now start the Tarpan process again. To me, the bases are small and unfortunately I can not give an estimate for how much time passes the essential upgrade.

Last week we purchased Fantastico Deluxe Installer, which in my humble opinion is one of the most reputable for CPanel servers. We tested it we installed and everything went smoothly. Today a customer informed me about a problem with the enkodinga of wordpress installation. I looked up and immediately get the problem bases were with default encoding instead of Latin1 UTF8 as was supposed. Even more fun is, in phpmyadmin and it says that it uses UTF8 as default, drama. I decided to review the files of Fantastico-it to see if I can find myself someplace where the settings for the default databases at first glance I didn't see anything. I see what's nose started bleeding in my. conf-what to see there was no appropriate settings in the configuration and everything start to what is set by default. Mysql server is hardkodnat to use UTF8 unless configured with other settings and Fantastico-it with Latin1 ( which is a pretty stupid decision). The solution is trivial as always add 2 line in [mysqld] missing part so to be UTF8 as encoding by default, and it all goes to sleep 🙂

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

Нямам никаква идея поради каква причина съм пропуснал тези настройки при положение че си играх да правя няколкофининастройки на mysql-а.

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 a query. In General, the query is looking for results that are missing another table. A Select a sub osnovne and select in the part WHERE the application. In General, the skeleton and is

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

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

In General, a simple request. I wrote it for 30 SEC release her and stuck the machine. After a long and patiently waiting on my part or just ~ 43 sec . Spit my score lol . Pfff Madhouse. Enter in the machine looking CPU is normally loaded almost at idle condition. Shock and awe. Run the query again still the same result. Fuck WTF. Run the query and explain everything I – the second field is only secondTextField full text search No index, and there is a modest tray of about 35 k line. What to read – full text search index is not. It is already clear the problem real quick one

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

And things turned places Query took 0.0005 sec 😀

Be careful how you put the indices of them depends on your marginal rate of application.

p.s Overall I'm hooked on the upper situation not only because there is no index but because not using full text search method 😀

Enhanced by Zemanta

Yesterday after the update of the version of mysql, my server started yelling at me, It has a tray, that has not been closed cleanly and in need of repair, etc.. Blah, What is this table, still have around 30 on this server. One option is to see the log-s what it says on the matter and to run a repair of the table or the other option – far better – is to run a repair, check and optimize all tables. For this purpose, I will use the mysqlcheck tool. Generally the options in this case are both komendi are synonymous to each other:

mysqlcheck -Aor -u root -p

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

Generally, that both commands to use, the effect will be the same – fragrant repair, screening and optimization of all tables. After typing either of the commands, you will be asked for the root password of mysql server-a you.