Koozali.org: home of the SME Server

How to install MySQL5 on SME7

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
How to install MySQL5 on SME7
« Reply #15 on: August 24, 2006, 11:42:46 AM »
Quote from: "mike_mattos"
http://dev.centos.org/centos/4/testing/i386/RPMS/*mysql* seem to have been deleted!

the Centos rpm's that CentOS 'yum update mysql' detects work fine on my CentOS 4 system, but won't install on SME! (dependencies!)

Prior to downloading from MySQL, am I missing something obvious?  I looked in centos/4 and in centos/4.3

thx all
Following centos bug 1124 it is now in http://mirror.centos.org/centos/4/centosplus/i386/RPMS/
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)

Offline mike_mattos

  • *
  • 313
  • +0/-0
How to install MySQL5 on SME7
« Reply #16 on: August 24, 2006, 04:43:31 PM »
here is a revised script that works (Aug 24,2006)  Note the new server and new version numbers.

Thanks DarkMirage and Cactus!


wget \
http://mirror.centos.org/centos/4/centosplus/i386/RPMS/mysqlclient14-4.1.14-4.2.c4.1.i386.rpm \
http://mirror.centos.org/centos/4/centosplus/i386/RPMS/mysql-server-5.0.22-1.centos.1.i386.rpm \
http://mirror.centos.org/centos/4/centosplus/i386/RPMS/mysql-5.0.22-1.centos.1.i386.rpm

rpm -U mysql-5.0.22-1.centos.1.i386.rpm \
mysql-server-5.0.22-1.centos.1.i386.rpm \
mysqlclient14-4.1.14-4.2.c4.1.i386.rpm

rm -f /etc/my.cnf.rpmnew
service mysqld restart

mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE ON horde.* TO 'horde'@'localhost';FLUSH PRIVILEGES"
...

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
How to install MySQL5 on SME7
« Reply #17 on: August 24, 2006, 04:48:20 PM »
Can someone confirm that webmail functions properly after this upgrade?
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)

Offline mike_mattos

  • *
  • 313
  • +0/-0
How to install MySQL5 on SME7
« Reply #18 on: August 24, 2006, 09:34:04 PM »
it worked ok for me
this was a new system, and web mail worked when I added accounts
...

Offline Jean-Philippe Pialasse

  • *
  • 2,721
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
How to install MySQL5 on SME7
« Reply #19 on: August 25, 2006, 09:31:31 AM »
it works fine

here my methode with yum:
Code: [Select]

mysqldump -aec >mondump.sql
/etc/rc.d/init.d/mysql stop
yum --enablerepo=centosplus update mysql
/etc/rc.d/init.d/mysql start


And if the webmail has no access to the data :
Code: [Select]

mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE ON horde.* TO 'horde'@'localhost';FLUSH PRIVILEGES"
[/code]

Offline kruhm

  • *
  • 680
  • +0/-0
How to install MySQL5 on SME7
« Reply #20 on: August 27, 2006, 02:34:51 PM »
any reason why nobody is using yum in this thread?

yum --enablerepo=centosplus search mysql

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
How to install MySQL5 on SME7
« Reply #21 on: August 27, 2006, 03:09:51 PM »
Quote from: "kruhm"
any reason why nobody is using yum in this thread?

yum --enablerepo=centosplus search mysql
READ! just before you someone posted his way using yum.

The rpm command comes from people who are used to using rpm from earlier times, it allows for a lot more options, which can be very usefull if you are trying to figure out why things do not work (as long as you know what you are doing off course).

I would indeed also preffer to use yum, especially if posting Howto's as this is the preffered method of installing packages on CentOS (and therefore SME Server).
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)

Offline Rien

  • *
  • 216
  • +0/-0
    • http://www.taurix.nl
How to install MySQL5 on SME7
« Reply #22 on: August 27, 2006, 11:47:15 PM »
Hi Unnilennium,

I've a production server (SME7) with some Joomla! sites on it. The next version of Joomla (1.5) is based on MySQL 5 so I want to be able to migrate to that version.  

My SME-box is also my  mailserver and fileserver. I also have several Dungog modules installed.

How save is it to use your "yum" script on a production server?

Quote from: "unnilennium"
it works fine

here my methode with yum:
Code: [Select]

mysqldump -aec >mondump.sql
/etc/rc.d/init.d/mysql stop
yum --enablerepo=centosplus update mysql
/etc/rc.d/init.d/mysql start


And if the webmail has no access to the data :
Code: [Select]

mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE ON horde.* TO 'horde'@'localhost';FLUSH PRIVILEGES"
[/code]


Thanks,
Rien
(The Netherlands)......

Offline Jean-Philippe Pialasse

  • *
  • 2,721
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
How to install MySQL5 on SME7
« Reply #23 on: August 28, 2006, 01:26:08 AM »
Hi

I have my production server running mysql5 for few days with this methode. it works great and with php 4.3.9-3.15.

What i could add is :
-i didn't need to grant the mysql rights for the webmail it was ok for me.
-i would add another thing a new table is necessary for mysql 5 so you will have  to add it manually (if you forgot it, it may works, but it's better to have a clean install):

So here the complete how to :
Code: [Select]

mysqldump -aec >mondump.sql
/etc/rc.d/init.d/mysql stop
yum --enablerepo=centosplus update mysql
/etc/rc.d/init.d/mysql start


And if the webmail has no access to the data :
Code: [Select]

mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE ON horde.* TO 'horde'@'localhost';FLUSH PRIVILEGES"


Finaly:
Code: [Select]
-- MySQL dump 10.10
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 5.0.22
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table proc
--

DROP TABLE IF EXISTS proc;
CREATE TABLE proc (
db char(64) character set utf8 collate utf8_bin NOT NULL default ,
name char(64) NOT NULL default ,
type enum('FUNCTION','PROCEDURE') NOT NULL,
specific_name char(64) NOT NULL default ,
language enum('SQL') NOT NULL default 'SQL',
sql_data_access enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL default 'CONTAINS_SQL',
is_deterministic enum('YES','NO') NOT NULL default 'NO',
security_type enum('INVOKER','DEFINER') NOT NULL default 'DEFINER',
param_list blob NOT NULL,
returns char(64) NOT NULL default ,
body longblob NOT NULL,
definer char(77) character set utf8 collate utf8_bin NOT NULL default ,
created timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
modified timestamp NOT NULL default '0000-00-00 00:00:00',
sql_mode     set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')  NOT NULL default ,
comment char(64) character set utf8 collate utf8_bin NOT NULL default ,
PRIMARY KEY (db,name,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';

Offline fixit

  • *
  • 216
  • +0/-0
    • http://www.fixitcomputers.com.au
How to install MySQL5 on SME7
« Reply #24 on: August 28, 2006, 01:32:03 AM »
I removed my stupid post because I had no idea that mysql has been updated, my mistake
.........

Offline kruhm

  • *
  • 680
  • +0/-0
How to install MySQL5 on SME7
« Reply #25 on: August 28, 2006, 05:40:28 AM »
@catus
You'll have to forgive me if I don't have time to read every post from every member. You might want to watch your tone of voice -afterall, I'm trying to help.

Even if it is there, it probably isn't a bad idea to post it again -even now. After all, it did take everyone on this thread (including yourself) more than 20 posts to come to that joint conclusion.

Furthermore, even after the yum post (in duplicate), people are  continuing to post scripts that do what yum is already designed to do.

yum --enablerepo=centosplus search mysql
yum --enablerepo=centosplus yum <casesensitivepackagename>

Finally, there's probably good reason as to why these are not in the centos base. If a production server is in question, general wisdom would urge the server admin to follow what the maintainers recommend.

Offline Rien

  • *
  • 216
  • +0/-0
    • http://www.taurix.nl
How to install MySQL5 on SME7
« Reply #26 on: August 28, 2006, 10:57:30 PM »
Hi Unnilennium,

Thanks for your script. I updated MySQL. I had also to fix Horde but now it works fine.

But when I want to create the table proc I got an error.

I did:

Code: [Select]
DROP TABLE IF EXISTS proc;
CREATE TABLE proc (
db char(64) character set utf8 collate utf8_bin NOT NULL default ,
name char(64) NOT NULL default ,
type enum('FUNCTION','PROCEDURE') NOT NULL,
specific_name char(64) NOT NULL default ,
language enum('SQL') NOT NULL default 'SQL',
sql_data_access enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL default 'CONTAINS_SQL',
is_deterministic enum('YES','NO') NOT NULL default 'NO',
security_type enum('INVOKER','DEFINER') NOT NULL default 'DEFINER',
param_list blob NOT NULL,
returns char(64) NOT NULL default ,
body longblob NOT NULL,
definer char(77) character set utf8 collate utf8_bin NOT NULL default ,
created timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
modified timestamp NOT NULL default '0000-00-00 00:00:00',
sql_mode     set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')  NOT NULL default ,
comment char(64) character set utf8 collate utf8_bin NOT NULL default ,
PRIMARY KEY (db,name,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';


The following error pops up:

Quote
#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 '
name char(64) NOT NULL default ,
type enum('FUNCTION','PROCEDURE') NOT NU' at line 2


Any idea what could be wrong?

Thanks,
Rien
(The Netherlands)......

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
How to install MySQL5 on SME7
« Reply #27 on: August 28, 2006, 11:13:33 PM »
Quote from: "DarkMirage"
There seems to be some security in place keeping the web server process www confined to it's own directories. Meaning php can't read files in / or /proc,  or detect programs located in PATH or things like that.

Anyway, it's completely unrelated to MySQL, try a new thread if you wish to continue trying to resolve this...
It is PHP basedir security and is indeed worth a seperate thread, is allows PHP to only access subdirectories in the tree below as specified per the httpd.conf file.
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)

Offline Rien

  • *
  • 216
  • +0/-0
    • http://www.taurix.nl
How to install MySQL5 on SME7
« Reply #28 on: August 28, 2006, 11:51:01 PM »
I think that:

Code: [Select]
default ,

must be:

Code: [Select]
default '',


is that correct ?

Thanks,
Rien
(The Netherlands)......

Offline Jean-Philippe Pialasse

  • *
  • 2,721
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
How to install MySQL5 on SME7
« Reply #29 on: August 29, 2006, 12:01:57 AM »
that's right

here is the new version:

Code: [Select]
CREATE TABLE proc (
db char( 64 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
name char( 64 ) NOT NULL default '',
type enum( 'FUNCTION', 'PROCEDURE' ) NOT NULL ,
specific_name char( 64 ) NOT NULL default '',
language enum( 'SQL' ) NOT NULL default 'SQL',
sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) NOT NULL default 'CONTAINS_SQL',
is_deterministic enum( 'YES', 'NO' ) NOT NULL default 'NO',
security_type enum( 'INVOKER', 'DEFINER' ) NOT NULL default 'DEFINER',
param_list blob NOT NULL ,
returns char( 64 ) NOT NULL default '',
body longblob NOT NULL ,
definer char( 77 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
created timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
modified timestamp NOT NULL default '0000-00-00 00:00:00',
sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) NOT NULL default '',
comment char( 64 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
PRIMARY KEY ( db , name , type )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT = 'Stored Procedures'