Sébastien Wains

Linux

Red Hat/CentOS

SQL

Upgrading to PHP5/MySQL5 under CentOS 4.5

October 15, 2007 - 4 comments

I’m planning on moving to PHP 5 and MySQL 5 on our CentOS 4 production server.

Before doing anything on the production machine, I’m testing the process on a fresh installation of CentOS on a virtual machine…

I first tried to upgrade PHP… MySQL updates should be pulled out from the repository as well but it didn’t work.

# yum update php --enable=centosplus
Setting up Update Process
Setting up repositories
update                    100% |=========================|  951 B    00:00
base                      100% |=========================| 1.1 kB    00:00
addons                    100% |=========================|  951 B    00:00
extras                    100% |=========================| 1.1 kB    00:00
Reading repository metadata in from local files
primary.xml.gz            100% |=========================| 140 kB    00:00
update    : ################################################## 451/451
Added 451 new packages, deleted 0 old in 19.89 seconds
primary.xml.gz            100% |=========================| 580 kB    00:01
base      : ################################################## 1518/1518
Added 1518 new packages, deleted 0 old in 55.58 seconds
primary.xml.gz            100% |=========================|  157 B    00:00
Added 0 new packages, deleted 0 old in 0.04 seconds
primary.xml.gz            100% |=========================|  54 kB    00:00
extras    : ################################################## 228/228
Added 228 new packages, deleted 0 old in 6.44 seconds
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package php.i386 0:5.1.6-3.el4s1.7 set to be updated
--> Running transaction check
--> Processing Dependency: php-cli = 5.1.6-3.el4s1.7 for package: php
--> Processing Dependency: php = 4.3.9-3.22.9 for package: php-pear
--> Processing Dependency: php = 4.3.9-3.22.9 for package: php-mysql
--> Processing Dependency: php-common = 5.1.6-3.el4s1.7 for package: php
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package php-cli.i386 0:5.1.6-3.el4s1.7 set to be updated
---> Package php-common.i386 0:5.1.6-3.el4s1.7 set to be updated
---> Package php-pear.noarch 1:1.4.11-1.el4s1.1 set to be updated
---> Package php-mysql.i386 0:5.1.6-3.el4s1.7 set to be updated
--> Running transaction check
--> Processing Dependency: php-pdo for package: php-mysql
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: php-mysql
--> Processing Dependency: libmysqlclient.so.15 for package: php-mysql
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package php-pdo.i386 0:5.1.6-3.el4s1.7 set to be updated
---> Package mysql.i386 0:5.0.27-1.el4.centos set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient.so.14 for package: mysql-server
--> Processing Dependency: libmysqlclient.so.14(libmysqlclient_14) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.14 for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient_r.so.14 for package: mysql-server
--> Processing Dependency: libmysqlclient_r.so.14(libmysqlclient_14) for package: mysql-server
--> Processing Dependency: mysql = 4.1.20-2.RHEL4.1.0.1 for package: mysql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package perl-DBD-MySQL.i386 0:3.0008-1.el4.centos set to be updated
---> Package mysql-server.i386 0:5.0.48-1.el4.centos set to be updated
--> Running transaction check
--> Processing Dependency: perl(:MODULE_COMPAT_5.8.8) for package: perl-DBD-MySQL
--> Processing Dependency: mysql = 5.0.48-1.el4.centos for package: mysql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
warning: package mysql = 5.0.27-1.el4.centos was already added, replacing with mysql <= 5.0.48-1.el4.centos
---> Package mysql.i386 0:5.0.48-1.el4.centos set to be updated
---> Package perl.i386 4:5.8.8-4.el4s1 set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient_r.so.15 for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15 for package: php-mysql
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: php-mysql
--> Processing Dependency: libmysqlclient.so.15 for package: mysql
--> Processing Dependency: mysql-libs = 5.0.48-1.el4.centos for package: mysql
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15 for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15 for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient_r.so.15(libmysqlclient_15) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: mysql
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package mysql.i386 0:5.0.27-1.el4.centos set to be updated
---> Package mysql-libs.i386 0:5.0.48-1.el4.centos set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
 Package                 Arch       Version          Repository        Size
=============================================================================
Updating:
 php                     i386       5.1.6-3.el4s1.7  centosplus        1.1 M
Installing for dependencies:
 mysql-libs              i386       5.0.48-1.el4.centos  centosplus        1.8 M
 php-cli                 i386       5.1.6-3.el4s1.7  centosplus        2.0 M
 php-common              i386       5.1.6-3.el4s1.7  centosplus        135 k
 php-pdo                 i386       5.1.6-3.el4s1.7  centosplus        219 k
Updating for dependencies:
 mysql                   i386       5.0.48-1.el4.centos  centosplus        2.7 M
 mysql                   i386       5.0.27-1.el4.centos  centosplus        3.3 M
 mysql-server            i386       5.0.48-1.el4.centos  centosplus        9.7 M
 perl                    i386       4:5.8.8-4.el4s1  centosplus         11 M
 perl-DBD-MySQL          i386       3.0008-1.el4.centos  centosplus        145 k
 php-mysql               i386       5.1.6-3.el4s1.7  centosplus         77 k
 php-pear                noarch     1:1.4.11-1.el4s1.1  centosplus        345 k

Transaction Summary
=============================================================================
Install      4 Package(s)
Update       8 Package(s)
Remove       0 Package(s)
Total download size: 33 M
Is this ok [y/N]:

As you can notice, it pulls out 2 different versions of the package “mysql”.

This is the explanation of the issue given on IRC :

<sw> wolfy, toracat : well i'm working on a fresh install on a VM before I do anything on the production server.. So i assume I did nothing wrong.. it does try to pull out both version because it complains they are conflicting <sw> file /usr/bin/mysql conflicts between attempted installs of mysql-5.0.27-1.el4.centos and mysql-5.0.48-1.el4.centos <Evolution> sw: what command are you running for the install? <sw> Evolution, yum update php --enablerepo=centosplus <Evolution> sw: that only pulls in php-mysql, which just needs mysql. you likely have mysql-server already installed <Evolution> and that's what's conflicting. <wolfy> Evolution: but shouldn't the new mysql obsolete the older one ? <sw> the installed version of mysql-server is mysql-server-4.1.20-2.RHEL4.1.0.1 <Evolution> wolfy: it should and does, but php packaging changed, and that's caused a few issues in the upgrade. <Evolution> it's a valid mixed arch case, as php-pear went from an i386 arch to a noarch

The working command :

# yum --enablerepo centosplus install php php-pear php-mysql mysql mysql-server
Setting up Install Process
Setting up repositories
update                    100% |=========================|  951 B    00:00
base                      100% |=========================| 1.1 kB    00:00
centosplus                100% |=========================|  951 B    00:00
addons                    100% |=========================|  951 B    00:00
extras                    100% |=========================| 1.1 kB    00:00
Reading repository metadata in from local files
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package php.i386 0:5.1.6-3.el4s1.7 set to be updated
---> Package mysql.i386 0:5.0.48-1.el4.centos set to be updated
---> Package mysql-server.i386 0:5.0.48-1.el4.centos set to be updated
---> Package php-pear.noarch 1:1.4.11-1.el4s1.1 set to be updated
---> Package php-mysql.i386 0:5.1.6-3.el4s1.7 set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient_r.so.15 for package: mysql-server
--> Processing Dependency: php-cli = 5.1.6-3.el4s1.7 for package: php
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: mysql
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: php-mysql
--> Processing Dependency: libmysqlclient.so.15 for package: mysql
--> Processing Dependency: php-pdo for package: php-mysql
--> Processing Dependency: mysql-libs = 5.0.48-1.el4.centos for package: mysql
--> Processing Dependency: php-common = 5.1.6-3.el4s1.7 for package: php-mysql
--> Processing Dependency: libmysqlclient.so.15 for package: php-mysql
--> Processing Dependency: libmysqlclient.so.15 for package: mysql-server
--> Processing Dependency: php-common = 5.1.6-3.el4s1.7 for package: php
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.14 for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient_r.so.15(libmysqlclient_15) for package: mysql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package php-pdo.i386 0:5.1.6-3.el4s1.7 set to be updated
---> Package mysql-libs.i386 0:5.0.48-1.el4.centos set to be updated
---> Package php-common.i386 0:5.1.6-3.el4s1.7 set to be updated
---> Package php-cli.i386 0:5.1.6-3.el4s1.7 set to be updated
---> Package perl-DBD-MySQL.i386 0:3.0008-1.el4.centos set to be updated
--> Running transaction check
--> Processing Dependency: perl(:MODULE_COMPAT_5.8.8) for package: perl-DBD-MySQL
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package perl.i386 4:5.8.8-4.el4s1 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
 Package                 Arch       Version          Repository        Size
=============================================================================
Updating:
 mysql                   i386       5.0.48-1.el4.centos  centosplus        2.7 M
 mysql-server            i386       5.0.48-1.el4.centos  centosplus        9.7 M
 php                     i386       5.1.6-3.el4s1.7  centosplus        1.1 M
 php-mysql               i386       5.1.6-3.el4s1.7  centosplus         77 k
 php-pear                noarch     1:1.4.11-1.el4s1.1  centosplus        345 k
Installing for dependencies:
 mysql-libs              i386       5.0.48-1.el4.centos  centosplus        1.8 M
 php-cli                 i386       5.1.6-3.el4s1.7  centosplus        2.0 M
 php-common              i386       5.1.6-3.el4s1.7  centosplus        135 k
 php-pdo                 i386       5.1.6-3.el4s1.7  centosplus        219 k
Updating for dependencies:
 perl                    i386       4:5.8.8-4.el4s1  centosplus         11 M
 perl-DBD-MySQL          i386       3.0008-1.el4.centos  centosplus        145 k

Transaction Summary
=============================================================================
Install      4 Package(s)
Update       7 Package(s)
Remove       0 Package(s)
Total download size: 30 M
Is this ok [y/N]: y
Downloading Packages:
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating  : perl                         ####################### [ 1/19]
  Installing: php-common                   ####################### [ 2/19]
  Installing: mysql-libs                   ####################### [ 3/19]
  Updating  : perl-DBD-MySQL               ####################### [ 4/19]
  Updating  : mysql                        ####################### [ 5/19]
  Installing: php-pdo                      ####################### [ 6/19]
  Installing: php-cli                      ####################### [ 7/19]
  Updating  : php                          ####################### [ 8/19]
  Updating  : php-pear                     ####################### [ 9/19]
  Updating  : mysql-server                 ####################### [10/19]
  Updating  : php-mysql                    ####################### [11/19]
  Cleanup   : mysql-server                 ####################### [12/19]
  Cleanup   : php-mysql                    ####################### [13/19]
  Cleanup   : mysql                        ####################### [14/19]
  Cleanup   : php-pear                     ####################### [15/19]
  Cleanup   : perl                         ####################### [16/19]
  Removing  : perl-Filter                  ####################### [17/19]
  Cleanup   : php                          ####################### [18/19]
  Cleanup   : perl-DBD-MySQL               ####################### [19/19]

Dependency Installed: mysql-libs.i386 0:5.0.48-1.el4.centos php-cli.i386 0:5.1.6-3.el4s1.7 php-common.i386 0:5.1.6-3.el4s1.7 php-pdo.i386 0:5.1.6-3.el4s1.7
Updated: mysql.i386 0:5.0.48-1.el4.centos mysql-server.i386 0:5.0.48-1.el4.centos php.i386 0:5.1.6-3.el4s1.7 php-mysql.i386 0:5.1.6-3.el4s1.7 php-pear.noarch 1:1.4.11-1.el4s1.1
Dependency Updated: perl.i386 4:5.8.8-4.el4s1 perl-DBD-MySQL.i386 0:3.0008-1.el4.centos
Complete!

MySQL : selecting duplicate entries

August 3, 2007 - No comment

This is the correct query :
SELECT field from `table` GROUP BY field HAVING COUNT(field) > 1;

Here’s an example where I have two duplicate entries (test and test3).

# let's see the records
mysql> SELECT field from `table`;
+-------+
| field |
+-------+
| test  |
| test  |
| test2 |
| test3 |
| test3 |
+-------+
5 rows in set (0.00 sec)

# THIS IS NOT WHAT WE WANT !!! THIS WILL ONLY RETURN THE FIRST ENTRY (duplicate or not)
mysql> SELECT field from `table` HAVING COUNT(field) > 1;
+-------+
| field |
+-------+
| test  |
+-------+
1 row in set (0.00 sec)

# This is what we want to do
mysql> SELECT field from `table` GROUP BY field HAVING COUNT(field) > 1;
+-------+
| field |
+-------+
| test  |
| test3 |
+-------+
2 rows in set (0.00 sec)

Thanks again to Marcel the Admin Blogger for some useful feedback on this article

Lock MySQL table(s) in order to make a backup

July 18, 2007 - 4 comments

In order to make a backup of a database, you have to make sure it’s not modified while it is in the process of backup.

Let’s see here how to lock a single table or a full database..

Postfix chroot + MySQL

November 18, 2006 - 2 comments

Scenario :

You run Postfix non-chrooted with a MySQL DB as backend.

The issue :

You can’t run Postfix chrooted because the MySQL sock is not located in the chroot area.

The fix :

Postfix new MySQL syntax > Postfix 2.2

- No comment

I found out just yesterday that Postfix uses a new syntax for SQL lookups.
It seems like my old syntax was still compatible with Postfix 2.2.8.
I guess it will be backward compatible for a while.

CentOS/RHEL Postfix SMTP AUTH

November 15, 2006 - 9 comments

Thanks to Luca Gibelli for his document about Postfix SMTP AUTH.
It helped me a lot, this article is largely based on his work, I adapted it to match RHEL/CentOS systems.

CentOS 4.3 + Postfix 2.2.9 + TLS/SSL + RBL blocking + SpamAssassin 3.1.2 + Amavisd-new + SPF + pop-before-smtp + procmail + vmail + disclaimer

June 4, 2006 - 3 comments

Edit 16 nov 2006 : please consider using SMTP AUTH instead of pop-before-smtp : http://www.wains.be/?p=157

This is not aimed to be a step by step guide.. This is just a review of my current config with some helpful notes, it is probably meant for already advanced postfix users seeking for tips and tricks.

WordPress may have reformated the code in some weird way.. don’t do massive copy paste and hope it works fine straight away, please review everything carefully !!

If you have troubles figuring out something, drop me a line..

Postfix + MySQL + proxymap

April 4, 2006 - 1 comment

I discovered I could improve Postfix performances when using MySQL as backend

According to http://spike.porcupine.org/test/html/proxymap.8.html :

To consolidate the number of open lookup tables by
sharing one open table among multiple processes.
For example, making mysql connections from every
Postfix daemon process results in “too many connec-
tions” errors.

Edit /etc/postfix/main.cf :

# Allow proxy for the settings using MySQL
proxy_read_maps = $virtual_alias_maps $virtual_mailbox_maps $transport_maps $virtual_uid_maps $virtual_gid_maps

# add proxy: in front of any mysql:
virtual_mailbox_base = /var/spool/postfix/vmail
virtual_minimum_uid = 1000
virtual_mailbox_maps = proxy:mysql:/etc/postfix/vmailsql/vmailbox
virtual_alias_maps = proxy:mysql:/etc/postfix/vmailsql/valias
transport_maps = proxy:mysql:/etc/postfix/vmailsql/transport
virtual_uid_maps = proxy:mysql:/etc/postfix/vmailsql/vuid
virtual_gid_maps = proxy:mysql:/etc/postfix/vmailsql/vgid
local_recipient_maps = $virtual_mailbox_maps

CentOS 3.6 + Postfix 2.2.9 + TLS + Virtual Users + MySQL backend + …

- No comment

This article will review my current configuration,
this is an update to a previous article : CentOS + Postfix + virtual users + Squirrelmail + …

What I stopped using since the previous article :
- vacation message because the script I was using had a few annoying bugs that I haven’t figured out yet.
- squirrelmail replaced by roundcube webmail (still beta !!), I don’t have many webmail users so I can afford using roundcube, which still has a lot of bugs. If you want a rock solid webmail, go for squirrelmail.

MySQL issue after upgrading from Courier-Imap 3 to 4

February 11, 2006 - No comment

Along with CentOS 4.2 came MySQL 4.1, great news ! BUT…

My old Courier-Imap version was only compatible with MySQL 3.x..

About me

Internet & Technology junkie.
Photographer.
Backpacker.

I'm the Linux registered user #350986 and RedHat Certified Engineer #804007797825578 (Verify).

This « blog » is some kind of personal notebook about my discoveries in the IT world.

While some tips may seem obvious (we learn a new thing every day eh), hopefully you'll find some of them informative !

Check out my wishlist at Amazon !

sip:dummy@voip.wains.be

Categories

Apache (18)

Apple/Mac OS (6)

Asterisk (5)

Bash (15)

DNS (7)

Debian/Ubuntu (25)

Firefox (5)

Gnome (7)

Hardware (16)

High-Availability (1)

Howto (112)

Iptables (7)

LDAP (9)

Linux (237)

Linux-Anti-Theft (1)

Misc (11)

NSLU2 (5)

Networking (14)

Postfix (24)

Proxy (10)

RADIUS (2)

RHCE (22)

RRDWeather (5)

Red Hat/CentOS (30)

Rewards (2)

SQL (12)

SSH (11)

Scripts (4)

Security (63)

Tools (14)

VPN (6)

Versioning (10)

Virtualization (1)

VoIP (6)

Wifi (9)

Windows (12)

Links

Belgian spammers

Christopher Murray

Dedicated Servers

Open Source Heaven

RRDWeather

The Admin Blogger

Yannick

My favorite projects

The NSLU2 Linux Project

Unless stated otherwise, the work presented on this website is licensed under a
Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Copyright © Sébastien Wains

Search

About me

Categories

Links

My favorite projects