MYSQL :: Issue When Upgrading MySQL From Version 4.1 To 5.0

Issue Since Upgrading MySQL From 4.x.x To 5.0.24
I am not sure where to begin with this, but basically my host server upgrade our MySQL version from 4.x.x to 5.x.x and since some of my scripts have changed the way in which data is drawn from the database.

It seems to be a division issue when dealing with averages it extracts the data from the database different than it has on all previous versions of MySQL and I hope someone here can help me in the direction in which to solve this.

Here are a few examples of the issue I am having....

Upgrading To Lastest Version
I'm trying to figure out the easiest way to upgrade my current mySQL installation. Here's the basic details:

- Server is running Linux Red Hat 9
- Current mySQL is 3 (but has nothing in it so I can delete it if necessary)

What's the easiest way to do this?

SQL Version Issue With Hosts
My current server provides phpmyadmin 2.8.1 with MySQL Version 5.0.21 standard log

My new server provides MySQL Version 4.0.27

I have, with success exported my database through phpmyadmin with my current server but i am having no joy in importing to my new server.

I am being told that the two versions are not compatible and, if so, will need to dump my new host.

So, i have come to the mecca to ask you guys if this is true or wether or not this is infact a possibility (as i would love it to be)

I have attempted to import to the new host via phpmyadmin, but the tech support tell me the file is too large (11mb) so i then moved on to attempting the dump using SSH access via putty.

I am receiving errors.

I have tried to export the data again using the compatibility tool and selecting MYSQL40 from phpmyadmin but still recieve errors when uploading to the new host

I have also looked at the following page: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html but my current server doesn't offer SSH access.

Install New Version Of Mysql With Old Version Running
I would like to install the new versin of mysql but currently i have mysql version 3.23 running...

I need to have 2 version of mysql running at the moment in win2000 for different app.

what is the steps for me to install the new version of mysql without causing error and able to run concurrently.

Upgrading Mysql To 4.1.7
I currently run mysql 3.23.58 on a Linux daffy.perf.redhat.com 2.4.18-11

I wanted to update to mysql 4.1.7 and was wondering how easy it? Is it worth it? What are the benifits. What will it do to my current queries that I have written. Most are simple SELECT statements with some INSERT Statements.

Lastly if I should upgrade, what is the best way to do it.

Upgrading To MySQL 4.0
Currently, I'm running 3.23.51 on Red Hat 7.1, and I'm contemplating
upgrading to MySQL 4.0, but I'm not sure what to expect. I don't know if
MySQL 4.0 is fully backwards-compatible with 3.23.x versions, or if
something is going to break if I upgrade.

What are the main advantages of upgrading to 4.0? Speed? Features? I can go
through the whole changelog if need be, but I'd prefer to hear what actual
users are reporting as far as benefits and disadvantages.

Upgrading MySql
I have MySql installed on Win2k Pro. Recently I downloaded the latest version of MySql, wanted to upgrade the previous alpha version.

So any one here can help me guide to upgrade MySql, step by step, so that I do not loose any of my current MySql settings and databases.

Upgrading Mysql To 4.0.24
I have just followed MYSQL reference manual 2.3.15 to upgrade mysql from 4.0.20 to 4.0.24. Everything appeared to upgrade correctly, but the version is still showing 4.0.20. I have searched everywhere for documentation that would point out my error, but have been unable to find the solution.

Upgrading MySQL
I recently upgraded my Mac (running OS 10.4.8) to Apache 2.0.52, MySQL
5.0.24 and PHP 4.3.9.

While I seem to be accessing the latest MySQL from the Terminal, Apache
seems to continue to use the previous install of MySQL.

Is there some config file I need to edit so the new install of MySQL is
used by my Web server?

While I'm at it, my PHP seems to no longer be able to send email, though
I added "sendmail_path = /usr/sbin/postfix" to the php.ini file.

Mysql Upgrading From 3.23 To 4.1
I have linux RH AS 3 server with these installations:

#rpm -qa | grep mysql
libdbi-dbd-mysql-0.6.5-5
mysql-3.23.58-1
mysql-devel-3.23.58-1

How can I upgrade to mysql 4.1 ? Should I try uninstalling the above first
with all their dependencies or should I do a FORCE upgrade over it ?

Upgrading From MySQL V3 To V4
I have MySQL datbase running on a Mac Powerbook with MySQL v4.1.14 installed (upgraded from v3).
I cannot get the syntax correct when trying to SELECT a record that meet a predefined criteria.

$query = "select * from members where member_ID = $ID and cat = type_ID";

I get the following error message:
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 'and cat = type_ID' at line 1

This SQL query works in phpMyAdmin (when I specify the 'member_ID' and 'cat' numners:
select * from members where member_ID =1 and cat = 5

It seems that the $ID and type_ID variables have no values when trying to execute the $query.

Upgrading MySQL From 4.0 To 5.0
I am running almost every 4.0 query/syntax possible, and I can not afford one query to be faulty because it's changed in 5.0.

Upgrading To MySQL 5.x
I wish to upgrade my version of MySQL (currently stuck to version 3.23) and I am wondering if to upgrade to the most stable 4.x or 5.x version. I am concerned about any possible retro-compatibility issue with SQL queries written for the 3.x version. In particular I am inquiring about:

1. Is there any incompatibility issue between the very latest stable 5.x version and SQL queries written for 3.x MySQL versions?

2. Is there any compatibility issue with PHP 4.x or PHP 5.x ?

Upgrading Mysql From 4.0 To 4.1
i'm trying to upgrade mysql from 4.0 to 4.1. mysql_fix_privilege_tables reporting 'Access denied' and man says to use root pass as argument. so how to use it? '$ mysql_fix_privilege_tables -ppass' doesn't work

Upgrading MySQL
I'm having a hard time figuring out how to upgrade to MySQL 4.1 on my FreeBSD server. I've been trying to use the ports, like I do whenever I upgrade all my other software. The install runs without any problems, but the MySQL binary (in usr/local/bin) is never replaced with the new version.
Does anyone have any advice on how to go about upgrading to 4.1 on a FreeBSD server. Am I missing something? Can you point me towards any good articles on how to go about doing this?

Upgrading To MySQL 4 Or 5
A person I am working wth is running My SQL 3.23.58. He needs to upgrade, so that we can use the UNION statement, among other things, but I was wonderting if it were better to upgrade to 4 or 5. By better, I mean working well with PHP 4.3.11, and Apache 2.0.51 on Linux.
I am asking because I know there are some issues with Windows, Apache 2, and PHP 5, and I want tomake this upgrade as smooth as possible.
It seems that most web hosts are using MySQL 4 currently, so I'm guessing that is the one I should go with? It appears, according to MySQL.com, that the stable version of MySQL 4 is 4.1.20. Please let me know if there are any issues that exist with this version of MySQL and the versions of PHP and Apache I mentioned above.
Also, can you upgrade smoothly without losing your database or data? I'm going to back it up just in case, but I'm just curious to know if you can upgrade without losing data, or if the database will absolutely need to be saved and re-uploaded.

Upgrading To Mysql 4.1
I have just upgraded to mysql 4.1...which is great...what isn't great is that it's not working. I keep getting an error message when I try to start it that it cannot find 'localhost'. My last version was 3.23 and I had no problems at all getting it to work.I've gone through the help file and changed the my.ini file and moved it to c:windows and checked everything but stil no joy...anyone had this problem and sorted it?
I'm running:

winxp
mysql 4.1
php 5.0
apache 2

Upgrading From MySQL 3.23 To 4.1 - Need To Upgrade To 4.0 First, But Can't...
I just realised that MySQL on my local server is MySQL 3.23....whoops.

I'm looking to go to 4.1 for the time being. However the manual says that in order to get there I should upgrade to 4.0 first, and then on to 4.1.

But the 4.0 download has been discontinued! So where do I go from here. A completely fresh install (not sure what effect inserting my 3.23 database SQL backup in the 4.1 database will have...nothing?)

or do I just upgrade to 4.1 straight?

Upgrading MySQL On Windows/IIS- Help?
I am running MysQL Version 3.23.49 on a Windows IIS machine. I need to upgrade it to at least 4.0.16 (to install vBulletin 3.6).

Is there a good tutorial on how to do this? I look at the documentation on the MySQL site but I'm hoping there is more out there to help me.

As well, the docs say instead of upgrading to V5 I should upgrade to V4 instead. I'd like to have the most up to date MySQL if I can so I won't have to do the another upgrade for a while.

Fedora C3 And Upgrading Mysql 3.23 To 4.1
How do I upgrade the standard version of mysql on FC3, 3.23, to 4.1 ? I already looked up some info on the internet, but it isn't quite clear to me.

I can backup my data, it isn't that much, just personal stuff. Do i just rpm the client and server, first 4.0 and then 4.1 or do I have to give special options with the rpm command ? Will this affect my webserver ? Do I have to adjust settings for apache or
PHP ?

Error Upgrading MySQL
I began preparations for upgrading MySQL. I downloaded the binary RPMs for
Red Hat Enterprise 4 (we're running CentOS, a clone) for MySQL 5.0.13 from the
MySQL AB web site. Running a test, I got this:

[root@seabass ~] rpm -hUv MySQL* --test
warning: MySQL-client-standard-5.0.13-0.rhel4.i386.rpm: V3 DSA signature: NOKEY,
key ID 5072e1f5
error: Failed dependencies:
libmysqlclient.so.14 is needed by (installed) mod_auth_mysql-2.6.1-2.2.i386
libmysqlclient.so.14 is needed by (installed) php-mysql-4.3.9-3.6.i386
libmysqlclient.so.14 is needed by (installed) dovecot-0.99.11-2.EL4.1.i386
libmysqlclient.so.14 is needed by (installed) perl-DBD-MySQL-2.9004-3.1.i386

Has anyone else encountered this dependency problem? What would happen if I
--force'd RPM to do the upgrade?

Upgrading MySQL With RPMs
I'd like to upgrade to v 4.1 as painlessly as possible (mainly I want to avoid problems with the php-mysql RPM which requires the dynamic libraries that include 3.23.x libraries). Is there an easy way to upgrade my RPM install without breaking a lot of things?

Upgrading MySQL On Linux Server
I need to upgrade MySQL on my server. I've tried to find some tutorial, but Google found nothing interesting.

I wonder what is the easiest and most safe way to upgrade MySQL? I'd like to keep configuration (./configure params, configuration files etc) as is and to not hurt anything.

Rolling Back A DB From Version 5 To Version 4
I've got a local MySQL DB with my MediaWiki data that was created in version 5.0.17, but I recently discovered that my webhost is running version 4.0.27, and when I tried to import the database as-is, I received an error.

I'm looking for suggestions as to how I could roll my database back to the older version of MySQL, in case my webhost decides not to let me upgrade. I've considered using an intermediary, such as converting to MDB or CSV, but so far I haven't had much luck with finding a tool to do so without spending a pile o' cash.

Steps For Upgrading MySQL 4.1 To MySQL 5.x?
I desperately searched the web for a list of steps to be followed to upgrade existing MySQL 4.1 installation to MySQL 5.x. This is the first time I am performing a MySQL upgradation and I don't know how to upgrade without losing any of the current user/system data.

Can anyone pl. provide or direct me to a link that provides a step-by-step procedure to upgrade an existing MySQL 4.1 installation to MySQL 5.x?

Mysql Comma Issue
I want to show show float and int values with comma in mysql database.

Like if i enter 1000 or 1000.00 it should take it as 1,000 or 1,000.00

is there any way to do this?

Mysql Order By Issue
I was with a problem with order by clause, sample:

CREATE TABLE CLIENT (ID INT(3) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(15),
PRIMARY KEY(ID)
);

CREATE TABLE CLIENT_MAIL(IDMAIL INT(3) NOT NULL AUTO_INCREMENT,
ID INT(3) NOT NULL,
MAIL VARCHAR(15),
PRIMARY KEY(IDMAIL) );

INSERT INTO CLIENT VALUES (NULL,'A');
INSERT INTO CLIENT VALUES (NULL,'B');

INSERT INTO CLIENT_MAIL VALUES (NULL,1,'A');
INSERT INTO CLIENT_MAIL VALUES (NULL,1,'B');

INSERT INTO CLIENT_MAIL VALUES (NULL,2,'D');
INSERT INTO CLIENT_MAIL VALUES (NULL,2,'F');

INSERT INTO CLIENT_MAIL VALUES (NULL,2,'C');

and one query:

SELECT CLIENT.ID,CLIENT_MAIL.ID,CLIENT.NAME,CLIENT_MAIL.MAIL FROM CLIENT,CLIENT_MAIL WHERE CLIENT.ID=CLIENT_MAIL.ID ORDER BY CLIENT.NAME,CLIENT_MAIL.MAIL DESC;

show is:

+----+----+------+------+
| ID | ID | NAME | MAIL |
+----+----+------+------+
| 1 | 1 | A | B |
| 1 | 1 | A | A |
| 2 | 2 | B | F |
| 2 | 2 | B | D |
| 2 | 2 | B | C |
+----+----+------+------+

in resume i need show the result

NAME MAIL
B F
B D
B C
A B
A A

PHP/MySQL Interaction Issue?
PHP

session_start();ob_start();$item = $_GET['item'];$user = $_POST['username'];$pass = $_POST['password'];mysql_connect('localhost', 'mysqluser', 'mysqlpass');mysql_select_db("mysqldb");$result = mysql_query("SELECT * FROM users WHERE user = $user AND password = $pass");$row = mysql_fetch_row($result);if ($row <= 0) { header("Location:item.php?item=failed");} else { $_SESSION['login'] = 1; $_SESSION['user'] = $user; header("Location:item.php?item=$item");}ob_flush();

I think it should work, but it's repeatedly telling me Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /my/directory/html/item.php on line 10. I can't figure out for the life of me why it won't work. It connects properly, I've been doing SQL queries all over the site. The variables POST correctly, and the SQL variables are all correct as well. Can anyone tell me what's going on with my query?

MYSQL Performance Issue
The following prg keeps cpu usage as 79% always .When i kill/stop the
prg,then cpu comes down.

main()
{
//code for mysql conn
while(1)
mysql_real_query (&mysqlcon, "SET CHARACTER SET 'utf8';", 25);
}

What might be the reason? Wil it be any config issues?

MySQL Select Issue
Okay, my table is setup like so:

carId int(11)
carName varchar(50)
category varchar(50)

Now, A field in it would be like this:

1
BWM
IMPORTS

Now in phpmyadmin and my scripts.

SELECT * FROM cars WHERE category='IMPORTS';

It pulls up 0 result where there are about 200. it makes no sense.

My phpmyadmin version is 2.6.4-pl2 .

Flash / Php / MySQL Issue
I am trying to create a web site which will have a mysql database that will store an empty xml file for a user.

Question 1: How do i store an XML file into mySQL database? do i have to create a table etc... (probably the dumbest question on this forum i know!!)

In addition:

I have a flash program which i am hoping (via php) will load the users xml file (from the mySQL database).

Question 2: Does anyone know how i can do this? (I can import an xml file - but independently not from an mySQL database.

Then i can attach Comments to the xml. I neeed then to store the ammended xml back in the database (via php).

Question 3: Again does anyone have any help on how to do this?

I Then will need to enable the admin user of the web site to access the users xml files and get them displayed with XSLT associated with the xml. The xslt is a formatting style sheet that tells the browser how to output (display) the xml.

DateTime Issue With MySQL 3.23
I'm trying to use datetime with a MySQL 3.23 DB to record the date that each record is created, I've tried creating a table using

`date` datetime NOT NULL default 'NOW()',
`date` datetime NOT NULL default 'CURRENT_DATE',
`date` datetime NOT NULL default 'CURRENT_TIMESTAMP',

Anyone an idea how to do this with MySQL 3.23 each time I tried the default value reverted back to 00-00-00 00:00:00 and the current date/time is not recorded when records are created ????

Lock Issue In Mysql
i have a big application developed in visual basic 6 and mysql.

what happens if more than one user is trying to update the same row in a

table? like 2 users are trying to update the same record at the same time.

How to handle such a situation?

MySQL Performance Issue
Im running mysql4.0.13 on FrreBSD 5.1-release. The problem is MySQL is using 95% of the cpu leaving 0% idle and pushing the load avg up to > 1. This is the output of mysqladmin processlist..

$ mysqladmin -uroot -p processlist;
Enter password:
+--------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+----+---------+------+-------+------------------+
| 387980 | root | localhost | | Query | 0 | | show processlist |
+--------+------+-----------+----+---------+------+-------+------------------+
$ uptime
8:51PM up 82 days, 5:18, 2 users, load averages: 1.03, 1.07, 1.23

I normally would expect a slow query or a table that requires optimisation, but without any hints from the processlist, where can i start looking?

Mysql Comma Issue
I want to show show float and int values with comma in mysql database.

Like if i enter 1000 or 1000.00 it should take it as 1,000 or 1,000.00

is there any way to do this?

MySQL Routing Issue
I'm trying to route packets from 2 locations to a MySQL server sitting
behind a firewall running iptables. The MySQL server has a ip address
of 192.168.1.12.

Basically I want packets destined to port 3306 to be forwarded or
redirected to 192.168.1.12.

# MySQL
/sbin/iptables -A PREROUTING -t nat -i ppp0 -p tcp --dport 3306 -j DNAT
--to-destination 192.168.1.12
iptables -A INPUT -p tcp --dport mysql -j ACCEPT
iptables -A FORWARD -p tcp -s 192.168.1.12 --sport mysql -j ACCEPT
iptables -A FORWARD -p tcp -s $PRIVIP_1 --dport mysql -j ACCEPT
iptables -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -A FORWARD -p tcp -s $PRIVIP_2 --dport mysql -j ACCEPT

Mysql Upgrade Issue
Recently i have upgraded from 4.1 to 5.0.27 and now expiriencing issues with my database.

I cant post topics/PMs/or login with Russian logins to the forum.
The forum simply doesnt recognize text in Russian.

Can anyone please advice whether if default 4.1 and 5.0.27 collation/chartsets are different and what collation/chartsets i should use in order for my forum to work correctly?

Mysql Administrator Issue
I have installed MYSQL 4.1.7, administrator & control center on Windows XP.

Mysql server runs fine, I can connect to Mysql administrator or windows command line client.

My problem is:
1] While trying to connect in MYSQL Control Center , I get error:
=== Error 2003: Can't connect to Mysql server on 'THESERVER' (10061) ===
[host name is fine, port no is also fine, then where I am doing wrong.... ???]

2] I can connect to Mysql Administrator , BUT..
1) Startup Variables are disabled [I can't alter them...]
2) Clicking SERVICE CONTROL page stops the service and then I could not restart the service again. [seems goophy to me... why its like that ???]
3) While surfing in Mysql Administrator, after a while I get error message:
=== Mysql Error Number 1226
User 'root' has exceeded the 'max_questions' resource (Current value 100)===
[I set max question value to default (0) or any number, it dosent make difference, and I can't work on administrator unless I re-login]

3] I have created a .sql file containing all tables & relationships I want. How can I load
that file in Mysql database. [How can I call/import that .sql file to create tables in Mysql database ??]

I am stuck in above little mess, would you please help me out from above problem ?

Connection Issue To Mysql
I got a setup of mysql/apache/php running on my home server. The server is not the most powerfull 1Go of memory 2.66 Ghz cpu, and a 2Mbits bandwith. It is running under Fedora core 3.
This should still be enough for the hosting of my home website and forum...

the issue I have is that often during the evening the connection to mysql failed for some users and for me as well from time to time. It is happening mainly when there is many visitors on the forum (100 or more)

The error message is the following :
PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) in /var/www/html/lib/init_variables.php on line 30

Do you have any idea of the reason of this problem? to reproduce the issue it is easy i just have to try running the apache benchmark :
ab -c 50 -n 5000 -A "admin:btd3txwn" http://192.168.0.1/testPage.phpl"

this is really annoying for the visitors of my website and it seems that this is also happening on some other website : doing a google search with mysql.sock' (11) show many results ...

Mysql Query Issue
Have attempted to search the forum but not exactly sure what I need, a basic query is causing me some issues, I need to get columns x, y, z from database/table, where status is not closed. Status being a varchar field.This should be easy but there where command I was using was not working, please could someone advise me of the code?

MYSQL Spamming Issue
I have built a guestbook in PHP and MySQL and everything works fine, however, recently, someone has been posting spam with links to sites in the comments field and I have to manually remove them every couple of days using PHPMyAdmin.
Currently, the comments field is "longtext". Not being an expert in MySQL, I was wondering if I can change the structure of the table without causing any problems with the data. If so, what should I use to allow a paragraph of text, but eliminate the ability to post links? and long strings of text?

How To Get The Version Of The Mysql?
There is a running mysql on the server. But I don't know its version. I can logon on the server and have all accounts. And Can anyone tell me how to know the version of the mysql.

Mysql Version
I want to use mysql clustering,could suggest which version of mysql
server include this function ? 4.0 or 4.1 ? binary version?

Version Of MySQL
which i suppose allows one to download the mysql 4.1 file for windows.i have windows xp pro on my pc.but under this heading "Windows downloads (platform notes)"
on this page, there are 3 files for windows namely Windows Essentials (x86),Windows (x86)and Without installer (unzip in C:).i was wondering which one to download for a starter like me.

MYSQL Version
what version of mysql could you recommend to use trigger, and procedure? Is it free?
I am also looking for best tutorial in trigger and procedure exclusive for mysql, can you provide links?

MySQL Version
My host is telling me that MySQL 4.0.14 is installed on the server. Yet my phpinfo() is saying my Client API version is 3.xx.xx (http://www.aaron-wright.com/info.php)

Can anyone explain to me what he's talking about? All I know is that nested queries aren't working, and I need 'em to. You guys know of perhaps a switch in a config file that needs to be thrown? And apparently v4 supports query caching or something. How would one enable that?

PHP Version 5.0.2 MySQL 4.1.7
I am trying to connect from PHP Version 5.0.2 on Windows 2003 Server an IIS to MySQL 4.1.7 but there is an authentication problem.
When I run the following PHP script...

<?php
$connection = mysql_connect("localhost","root","password");
?>

The message I get is...
"Client does not support authentication protocol requested by server; consider upgrading MySQL"This seems to be a problem with MySQL 4.1 because I can get it to work perfectly well with MySQL version 4.0.13

Mysql-3.23.56-Max And Non-max Version
I recently upgraded my machine to SuSE 8.1 and went about getting all the
rpms I could get related to MySQL and install them.

I did notice that unlike before, I have a "max" version of 3.23 running and
I'm 99.99% sure this was not the case with my previous version (an earlier
version of 3.23).

What is the difference between the max and non-maxed version... I've not
found any info on it

Mysql 5 Version
I am trying to run Mysql 5 on fedora. I copied the zip files and tried running following command to start the server:
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &

But geting the error:

[root@varun mysql5]# Starting mysqld daemon with databases from /data/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
060116 04:31:00 mysqld ended

I already have mysql 4 installed and running successfully.. To start with mysql 5, I stopped mysql 4 and executed commands for mysql 5 (as mentioned above).
While I was able to run it successfully on my local linux machine but not on the server machine (fedora)