Upgrading MySQL 4.0 to 5.0
Has anyone upgraded MySQL 4.0 to 5.0 on a Mac running Mac OS X Panther, 10.3.9 Sever with Lasso 8.5.4? I'm debating whether to just upgrade to MySQL to 5.0 or jump to Apples Leopard Server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown column error after upgrading from 4.0 to 5.0
Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the following command: select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1' give the following error: ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause' What's wrong with that command? And why it worked correctly under 4.0? I tried to eliminate the aliases and use directly the real tables names but nothing changed. Obviously the column exists, the following command works: select products.products_id from products Thanks. -- ___ __ |- [EMAIL PROTECTED] |ederico Giannici http://www.neomedia.it ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown column error after upgrading from 4.0 to 5.0
Try not mixing left join and comma-joins, and use an INNER JOIN keyword between m.manufacturers_id, products_to_categories Baron Federico Giannici wrote: Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the following command: select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1' give the following error: ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause' What's wrong with that command? And why it worked correctly under 4.0? I tried to eliminate the aliases and use directly the real tables names but nothing changed. Obviously the column exists, the following command works: select products.products_id from products Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown column error after upgrading from 4.0 to 5.0
Hi Frederico, the precedence between the comma-operator and JOIN changed with 5.0.12. See http://dev.mysql.com/doc/refman/5.0/en/join.html Excerpt from that article: Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are. Example: CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); INSERT INTO t3 VALUES(1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3); Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3: End excerpt. /Johan Federico Giannici skrev: Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the following command: select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1' give the following error: ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause' What's wrong with that command? And why it worked correctly under 4.0? I tried to eliminate the aliases and use directly the real tables names but nothing changed. Obviously the column exists, the following command works: select products.products_id from products Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to 4.0
Hi, i was sleep during 2 years and yet no update mysql 3.2X... :) First i download : MySQL-server-4.0.23-0.i386.rpm MySQL-client-4.0.23-0.i386.rpm MySQL-devel-4.0.23-0.i386.rpm MySQL-shared-4.0.23-0.i386.rpm MySQL-shared-compat-4.0.23-0.i386.rpm But when rpm -Uvh *.rpm warning: MySQL-client-4.0.27-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing...### [100%] package rhn-org-trusted-ssl-cert-1.0-2 is already installed file /usr/share/rhn/RHN-ORG-TRUSTED-SSL-CERT from install of rhn-org-trusted-ssl-cert-1.0-2 conflicts with file from package rhn-org-trusted-ssl-cert-1.0-2 I can repair this? Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems upgrading from 4.0 to 4.1 with TIMESTAMPS
Hi! I'm a hosting provider with several thousands of Mysql Databases under 4.0 version. I'm specially worried about TIMESTAMP format changes you did in 4.1. I'll have to update database server soon to have my systems updated. I think that if i update my server version thousands of tables are going to have problems with TIMESTAMP types. I was looking for solutions as to put +0 when selecting TIMESTAMP fields. But I can't imagine to change that in thousands lines of PHP code by hand, I'm sure lots of my customers will have no idea about how to fix that mistake so I'll have to fix their PHP code!. I'm almost sure that all ISP hosting providers are having or will have this problem soon. Anyone have a good idea to upgrade servers to 4.1? Do you think that should I update my servers, wait for a better solution in another version... What do you think I must to do? _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading from 4.0 to 4.1
I had to migrate from 4.0.11 to 4.1.12. I saved the data directory from the older version. I also exported all the information using phpMyAdmin's export tool. I have one huge .sql file. When I upgraded to 4.1.12, I followed instructions to copy the contents of the old data directory to the new one. This didn't work...MySQL had trouble starting up. So, I restored the new data folder, and ran the .sql file to recreate all the table and to insert all the new records. This works fine. What is the easiest way to upgrade and migrate the data? Did I do something wrong by just copying the contents of the data directory? Should that method work? -James
Re: Upgrading from 4.0 to 4.1
When mysql had trouble starting up what error messages did it give in the .err file in the datadir? -Eric James Tu wrote: I had to migrate from 4.0.11 to 4.1.12. I saved the data directory from the older version. I also exported all the information using phpMyAdmin's export tool. I have one huge .sql file. When I upgraded to 4.1.12, I followed instructions to copy the contents of the old data directory to the new one. This didn't work...MySQL had trouble starting up. So, I restored the new data folder, and ran the .sql file to recreate all the table and to insert all the new records. This works fine. What is the easiest way to upgrade and migrate the data? Did I do something wrong by just copying the contents of the data directory? Should that method work? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems upgrading from 4.0 to 4.1
Hello Sergei On Thu, Apr 07, 2005 at 03:14:10PM +0200, Sergei Golubchik wrote: I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): Christian, do you have any of that tables ? Can you upload one of them (preferably, the smallest one) to our ftp ? beta_hardware_verwaltung.hardware_log info : Found wrong packed record at 0 info : Found wrong packed record at 56 info : Found wrong packed record at 112 Did you found the files on the incoming/ dir and do you have any news on this case? If it's not something extremly stupid from my side, can you open a bug report to make the issue easier to track? bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 ch@westend.comD-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems upgrading from 4.0 to 4.1
Hi! On Apr 11, Christian Hammers wrote: Hello Sergei On Thu, Apr 07, 2005 at 03:14:10PM +0200, Sergei Golubchik wrote: I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): Christian, do you have any of that tables ? Can you upload one of them (preferably, the smallest one) to our ftp ? beta_hardware_verwaltung.hardware_log info : Found wrong packed record at 0 info : Found wrong packed record at 56 info : Found wrong packed record at 112 Did you found the files on the incoming/ dir and do you have any news on this case? If it's not something extremly stupid from my side, can you open a bug report to make the issue easier to track? Yes, I found the file and repeated this problem, thanks. I opened a bugreport #9824 for it. about 15% of my tables (seemed be quite random - it happens on the tables that have live checksum enabled (CHECKUM=1 in CREATE TABLE). workarounds - disable checksum (in 4.0, before upgrade) or copy the table with create ... select. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems upgrading from 4.0 to 4.1
Hello Sergei On Thu, Apr 07, 2005 at 03:14:10PM +0200, Sergei Golubchik wrote: I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): Christian, do you have any of that tables ? Can you upload one of them (preferably, the smallest one) to our ftp ? I've uploaded the file into your write-only ftp.mysql.com:/pub/mysql/upload/ directory as Christian_Hammers_Tablecorruption_40to41.tar.gz The included table is correct according to a CHECK TABLE with MySQL-4.0.24 but reproducible gets emptied during a REPAIR TABLE on MySQL-4.1.10a. bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 ch@westend.comD-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems upgrading from 4.0 to 4.1
Hello I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): beta_hardware_verwaltung.hardware_log info : Found wrong packed record at 0 info : Found wrong packed record at 56 info : Found wrong packed record at 112 info : Found wrong packed record at 168 info : Found wrong packed record at 244 ... info : Found block that points outside data file at 47476 info : Found wrong packed record at 47736 info : Found block with too small length at 47748; Skipped info : Found wrong packed record at 48020 ... warning : Number of rows changed from 883 to 4 status : OK Luckily I made dumps before. Interesting to see was, that I could copy a table with /bin/cp still do a SELECT * from it but it, too, got emptied by a REPAIR statement. In the incompatibility list in the documentation some cases are mentioned where one has to do a REPAIR. But in which cases a repair does have such problems? Of course I did do a CHECK TABLE on all tables before upgrading, and I never used something different than latin1 nor table sizes near 4GB. All tables were checked and dumped on a regular basis without problems. After recreating them I had no further problems so far. bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 ch@westend.comD-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert to character set (upgrading from 4.0 to 4.1)
Hello. As said at: http://dev.mysql.com/doc/mysql/en/charset-conversion.html You should avoid trying to convert directly from latin1 to the real character set. If you have a backup, import a table from it. Check that your character_set_xxx variables have a corresponding values. See: http://dev.mysql.com/doc/mysql/en/charset-defaults.html Eli [EMAIL PROTECTED] wrote: Hi.. I encountered some problems with character sets. On the 4.0 version I stored UTF-8 strings. When upgraded to 4.1, I saw the default character set was 'latin1', so I converted to UTF-8 using this: ALTER TABLE my_table CONVERT TO CHARACTER SET 'utf8', DEFAULT CHARACTER SET 'utf8'; When viewing the pages, I see that some of the characters were corrupted (the same chars always). I tried to return to convert back to 'latin1' but the problem remained. What was the problem? How can I fix it? BTW: for some reason I cannot reply to posts in this mailing list using Thunderbird. (On PHP mailing list replying works). please help... -thanks, Eli -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert to character set (upgrading from 4.0 to 4.1)
Hi.. I encountered some problems with character sets. On the 4.0 version I stored UTF-8 strings. When upgraded to 4.1, I saw the default character set was 'latin1', so I converted to UTF-8 using this: ALTER TABLE my_table CONVERT TO CHARACTER SET 'utf8', DEFAULT CHARACTER SET 'utf8'; When viewing the pages, I see that some of the characters were corrupted (the same chars always). I tried to return to convert back to 'latin1' but the problem remained. What was the problem? How can I fix it? BTW: for some reason I cannot reply to posts in this mailing list using Thunderbird. (On PHP mailing list replying works). please help... -thanks, Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Invalid ENUM values after upgrading from 4.0 to 4.1
I have the same problem with cp1251 and, seems, it's a bug. I have posted correspoding message two days ago. Without any reply 8((( I migrate 3.23.55 - 4.1.1 and I don't know about existing such problem in 4.0. Look in your table.frm - your field is in correct charset. More of, inserts and updates of your tables are working correct (check it ;). The problem, IMHO, is in 'show columns ...' implementation. For me, it's critically (in my apps, I grab allowed values from table structure). Hi, I'm facing a problem with the new 4.1 branch. I've built MySQL from sources with default-language=hebrew and extra-languages=utf. I've also used default-collation=hebrew_general_ci. After installing, this is what I get for few of the variables: mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_server | hebrew | | character_set_system | utf8 | | character_set_database | hebrew | | character_set_client | hebrew | | character_set_connection | hebrew | | character-sets-dir | /usr/local/mysql/share/mysql/charsets/ | | character_set_results| hebrew | +--++ 7 rows in set (0.00 sec) mysql show variables like '%coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | hebrew_general_ci | | collation_database | hebrew_general_ci | | collation_server | hebrew_general_ci | +--+---+ 3 rows in set (0.00 sec) Sounds great, right? But when I try to work with databases, and their tables, that were upgraded from 4.0 with the new 4.1 server, I see question marks (???) for values in ENUM() field type. By the way, I noticed that default values for such fields (when having ``NOT NULL``) display perfectly in Hebrew. Explanation? Is there any specific ``upgrade procedure`` I should do to overcome this problem? Thanks in advance. Noor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Invalid ENUM values after upgrading from 4.0 to 4.1
It's known bug, will be fixed in 4.1.2 - Original Message - From: Juri Shimon [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Noor Dawod [EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 12:37 PM Subject: Re: Invalid ENUM values after upgrading from 4.0 to 4.1 I have the same problem with cp1251 and, seems, it's a bug. I have posted correspoding message two days ago. Without any reply 8((( I migrate 3.23.55 - 4.1.1 and I don't know about existing such problem in 4.0. Look in your table.frm - your field is in correct charset. More of, inserts and updates of your tables are working correct (check it ;). The problem, IMHO, is in 'show columns ...' implementation. For me, it's critically (in my apps, I grab allowed values from table structure). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invalid ENUM values after upgrading from 4.0 to 4.1
Hi, I'm facing a problem with the new 4.1 branch. I've built MySQL from sources with default-language=hebrew and extra-languages=utf. I've also used default-collation=hebrew_general_ci. After installing, this is what I get for few of the variables: mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_server | hebrew | | character_set_system | utf8 | | character_set_database | hebrew | | character_set_client | hebrew | | character_set_connection | hebrew | | character-sets-dir | /usr/local/mysql/share/mysql/charsets/ | | character_set_results| hebrew | +--++ 7 rows in set (0.00 sec) mysql show variables like '%coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | hebrew_general_ci | | collation_database | hebrew_general_ci | | collation_server | hebrew_general_ci | +--+---+ 3 rows in set (0.00 sec) Sounds great, right? But when I try to work with databases, and their tables, that were upgraded from 4.0 with the new 4.1 server, I see question marks (???) for values in ENUM() field type. By the way, I noticed that default values for such fields (when having ``NOT NULL``) display perfectly in Hebrew. Explanation? Is there any specific ``upgrade procedure`` I should do to overcome this problem? Thanks in advance. Noor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nasty Time Upgrading to 4.0 RPM dependencies - libmysqlclient.so.10 and php
Hello All! I really need a hand here. I'm volunteering at this NGO (non-profit org) in New Delhi. I and another developer built a .NET/MySQL app for managing the communications department. The app worked sweet on our machines, but when we moved it to the server and ran a UNION query (one of many) BANG! So We need to upgrade from 3.something to 4.0. It's a Cobolt Server, which I have no experience with, but everything was installed with RPM. I got the client and devel packages installed okay using rpm -Uvh --replacefiles but the server won't install because of 2 dependencies: libmysqlclient.so.10 is needed by mod_auth_mysql-1.11-1 libmysqlclient.so.10 is needed by php-mysql-4.1.2-7 Now I've done my research and it seems that MySQL4 has a lib file called libmysqlclient.so (no 10) and this needs to be used, so I've got to upgrade these packages somehow. I have no idea how to do this without building source RPMS (and even that I'm not sure about). Unfortunately, I don't have RPM build on this puter and it requires 15 major dependencies and I'm on a 2k line. This tape will self destruct in 3 seconds. BOOM PLEASE we're a really cool org and we need your help! address: http://deepalaya.org Jacob Singh mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nasty Time Upgrading to 4.0 RPM dependencies - libmysqlclient.so.10 and php
Dear Mr. Singh, You need to install Mysql-shared-compat available at http://www.mysql.com/downloads/mysql-4.0.html for more information take a look at http://www.mysql.com/doc/en/Linux-RPM.html Enjoy Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 1:12 PM Subject: Nasty Time Upgrading to 4.0 RPM dependencies - libmysqlclient.so.10 and php Hello All! I really need a hand here. I'm volunteering at this NGO (non-profit org) in New Delhi. I and another developer built a .NET/MySQL app for managing the communications department. The app worked sweet on our machines, but when we moved it to the server and ran a UNION query (one of many) BANG! So We need to upgrade from 3.something to 4.0. It's a Cobolt Server, which I have no experience with, but everything was installed with RPM. I got the client and devel packages installed okay using rpm -Uvh --replacefiles but the server won't install because of 2 dependencies: libmysqlclient.so.10 is needed by mod_auth_mysql-1.11-1 libmysqlclient.so.10 is needed by php-mysql-4.1.2-7 Now I've done my research and it seems that MySQL4 has a lib file called libmysqlclient.so (no 10) and this needs to be used, so I've got to upgrade these packages somehow. I have no idea how to do this without building source RPMS (and even that I'm not sure about). Unfortunately, I don't have RPM build on this puter and it requires 15 major dependencies and I'm on a 2k line. This tape will self destruct in 3 seconds. BOOM PLEASE we're a really cool org and we need your help! address: http://deepalaya.org Jacob Singh mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading 3.23-4.0 under WinXP ?
- Original Message -
From: Thomas Andersson
Sent: Saturday, August 23, 2003 8:15 AM
Subject: Upgrading 3.23-4.0 under WinXP ?
Hi!
I'm pretty green at this and I'm a bit clueless here, After
reinstalling my
system I upgraded from 3.23 to 4.0 of MySQL, then I moved my old dbs
in
there and most seems to work OK. Now PHPMyAdmin tells me my userbase
is out
of date and that I need to run mysql_fix_privilege_tables...
HOW do I run this on my WinXP system? What else do I need to do?
Is it DOs, should it run from within SQl?
mysql_fix_privilege_tables is a shell script for *nix. You can open it
in a text editor and see the ALTER and UPDATE queries that it would run.
I will post below the ones that I think are needed to upgrade the system
tables from 3.23 - 4.0. BTW, if you haven't modified the default
permissions or grants on anything, you can just drop the mysql
database (delete it from the data directory after stopping MySQL), and
then just reinstall MySQL 4.0 and it will put the correct default tables
there. Again, don't do this if you've changed the default MySQL
permissions (or you don't want to recreate them :-)).
Anyway, here's the queries from mysql_fix_privilege_tables that I think
you need to run on the mysql database (as the root user; and you ignore
any duplicate column errors, etc.):
USE mysql;
alter table user change password password char(16) NOT NULL;
alter table user add File_priv enum('N','Y') NOT NULL;
alter table user add Grant_priv enum('N','Y') NOT NULL,add
References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT
NULL,add Alter_priv enum('N','Y') NOT NULL;
alter table host add Grant_priv enum('N','Y') NOT NULL,add
References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT
NULL,add Alter_priv enum('N','Y') NOT NULL;
alter table db add Grant_priv enum('N','Y') NOT NULL,add References_priv
enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add
Alter_priv enum('N','Y') NOT NULL;
UPDATE user SET
Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,
Alter_priv=Create_priv;
UPDATE db SET
References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri
v;
UPDATE host SET
References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri
v;
ALTER TABLE user
ADD ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL,
ADD ssl_cipher BLOB NOT NULL,
ADD x509_issuer BLOB NOT NULL,
ADD x509_subject BLOB NOT NULL;
alter table user
add Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER alter_priv,
add Super_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_db_priv,
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Super_priv,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Create_tmp_table_priv,
add Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Lock_tables_priv,
add Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Execute_priv,
add Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Repl_slave_priv;
update user set show_db_priv= select_priv, super_priv=process_priv,
execute_priv=process_priv, create_tmp_table_priv='Y',
Lock_tables_priv='Y', Repl_slave_priv=file_priv,
Repl_client_priv=file_priv where user;
alter table user
add max_questions int(11) NOT NULL AFTER x509_subject,
add max_updates int(11) unsigned NOT NULL AFTER max_questions,
add max_connections int(11) unsigned NOT NULL AFTER max_updates;
alter table db
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;
alter table host
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;
FLUSH PRIVILEGES;
(Everything seems to work except my guestbooks after the reinstall).
Not sure about that not working. :-/
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading 3.23-4.0 under WinXP ?
Hi! I'm pretty green at this and I'm a bit clueless here, After reinstalling my system I upgraded from 3.23 to 4.0 of MySQL, then I moved my old dbs in there and most seems to work OK. Now PHPMyAdmin tells me my userbase is out of date and that I need to run mysql_fix_privilege_tables... HOW do I run this on my WinXP system? What else do I need to do? Is it DOs, should it run from within SQl? (Everything seems to work except my guestbooks after the reinstall). Please help and bare with my lack of knowledge! Best Wishes Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to 4.0
Hi all !! I am trying to upgrade to MySql 4.0 from 3.23 version. I dont know how to proceed. I was looking for some files for upgrading but i found nothing. Any help?? Thanks Broad Telecom S.A. (BTESA) - Spain Manuel Díaz-Regañón phone: 913274363 e-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to 4.0
On Tue, 2003-06-17 at 16:05, Manuel Diaz-Regañón wrote: Hi all !! I am trying to upgrade to MySql 4.0 from 3.23 version. I dont know how to proceed. I was looking for some files for upgrading but i found nothing. Any help?? on which os ? if it's FreeBSD, I've just done it this morning :) as root : #pkg_deinstall mysql-server (...) #port_install -rR databases/mysql40-server (...) #mysqld_safe (it isnot anymore safe_mysqld ) and it runs :)) Well, better do some back-up first -- Anselme [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Upgrading to 4.0
Thank you for your answer Anselme!! I am working on Windows98. I've read some documents on mysql home page which explain the difference between version. That information talks about some script to be run for upgrading version but there is no related file to download. Could you help me?? Thank you very much. Broad Telecom S.A. (BTESA) - Spain Manuel Díaz-Regañón phone: 913274363 e-mail: [EMAIL PROTECTED] -Mensaje original- De: Anselme [mailto:[EMAIL PROTECTED] Enviado el: martes, 17 de junio de 2003 16:15 Para: [EMAIL PROTECTED]; Mysql_MailingList Asunto: Re: Upgrading to 4.0 On Tue, 2003-06-17 at 16:05, Manuel Diaz-Regañón wrote: Hi all !! I am trying to upgrade to MySql 4.0 from 3.23 version. I dont know how to proceed. I was looking for some files for upgrading but i found nothing. Any help?? on which os ? if it's FreeBSD, I've just done it this morning :) as root : #pkg_deinstall mysql-server (...) #port_install -rR databases/mysql40-server (...) #mysqld_safe (it isnot anymore safe_mysqld ) and it runs :)) Well, better do some back-up first -- Anselme [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to 4.0 on Solaris
First could I apologize for joining a mailing list primarily to ask for help. However I am not a professional Unix or MySQL manager and am supporting an installation to allow Masters students in Bioinformatics to do summer projects. Last year I managed to install v. 3.23 of MySQL on a Sun box running Solaris 8, but would like to upgrade to 4.0 to be able to use UNION. To put it mildly, I'm having problems. These are two problems. 1. The upgrade instructions do not say anything about how you upgrade your old database. Section 2.3.2 of the manual only talks about things such as running things like mysql_fix_privilege_tables. From asking around I gather you are expected to use mysqldump to transfer databases, however I am totally in the dark as what stage of the new install you add the old tables. 2. In addition, I am having difficulty (this weekend - it was ok last weekend but then I scrapped it and went back to 3.23) in starting mysql 4.0 after doing an install. I did an mysqldump on 3.23, shut down the old server, and changed the mysql directory name to mysql3.23. I follow the instructions in 2.2.9, run scripts/mysql_install_db and check that the grant tables have been created in data. However when I try bin/safe_mysqld --user=mysql the daemon starts and then quits. The .err log says it can't find host.frm, but the file has definitely been created. I've installed the distribution as /usr/local/mysql - so everything would appear to be in the standard location. I'd be most grateful for any suggestions on either point. David -- ___ David P.Leader, Biochemistry and Molecular Biology Davidson Building, University of Glasgow, Glasgow G12 8QQ, UK Phone: +44 41 330-5905[EMAIL PROTECTED] http://doolittle.ibls.gla.ac.uk/leader ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to 4.0 on Solaris
Have You deleted or updated te my.cnf file? It seems to me that MySql is looking in the wrong dir or it dosn't have permision in data dir ( user mysql.mysql rw permission). Santino At 16:46 +0100 7-06-2003, David Leader wrote: First could I apologize for joining a mailing list primarily to ask for help. However I am not a professional Unix or MySQL manager and am supporting an installation to allow Masters students in Bioinformatics to do summer projects. Last year I managed to install v. 3.23 of MySQL on a Sun box running Solaris 8, but would like to upgrade to 4.0 to be able to use UNION. To put it mildly, I'm having problems. These are two problems. 1. The upgrade instructions do not say anything about how you upgrade your old database. Section 2.3.2 of the manual only talks about things such as running things like mysql_fix_privilege_tables. From asking around I gather you are expected to use mysqldump to transfer databases, however I am totally in the dark as what stage of the new install you add the old tables. 2. In addition, I am having difficulty (this weekend - it was ok last weekend but then I scrapped it and went back to 3.23) in starting mysql 4.0 after doing an install. I did an mysqldump on 3.23, shut down the old server, and changed the mysql directory name to mysql3.23. I follow the instructions in 2.2.9, run scripts/mysql_install_db and check that the grant tables have been created in data. However when I try bin/safe_mysqld --user=mysql the daemon starts and then quits. The .err log says it can't find host.frm, but the file has definitely been created. I've installed the distribution as /usr/local/mysql - so everything would appear to be in the standard location. I'd be most grateful for any suggestions on either point. David -- ___ David P.Leader, Biochemistry and Molecular Biology Davidson Building, University of Glasgow, Glasgow G12 8QQ, UK Phone: +44 41 330-5905[EMAIL PROTECTED] http://doolittle.ibls.gla.ac.uk/leader ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble upgrading to 4.0 on Mandrake Linux
I am running Mandrake Linux 8.2, with the RPMs upgraded to 9.0. It had MySQL 3.23 running on it. I downloaded the current version of 4.0 from mysql.com tonight and isntalled it. I changed the old files from /usr/share/mysql to /usr/share/mysql3.23 and /var/lib/mysql to /var/lib/mysql3.23 (where the databases are stored). I created a new /usr/share/mysql and unpacked the MySQL 4.0.x tarball into the directory, then followed the install instructions (basically changing /usr/local/ to /usr/share/ in bin/mysqlaccess and running scripts/mysql_install_db). I copied the 4.0 executables to /usr/bin (which I did without thinking -- realizing I forgot to backup the original 3.23 binaries in that directory -- my big mistake). Now when I type mysqld_safe --user=mysql the daemon starts, but I get another message almost immediately that it is exiting. What's wrong and what do I need to do to get 4.0 working? Why is the daemon quitting on me? Is anyone else using Mandrake and encountering similar problems? Thanks! Hal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Trouble upgrading to 4.0 on Mandrake Linux
Hal Vaughan wrote: I am running Mandrake Linux 8.2, with the RPMs upgraded to 9.0. It had MySQL 3.23 running on it. I downloaded the current version of 4.0 from mysql.com tonight and isntalled it. I changed the old files from /usr/share/mysql to /usr/share/mysql3.23 and /var/lib/mysql to /var/lib/mysql3.23 (where the databases are stored). I created a new /usr/share/mysql and unpacked the MySQL 4.0.x tarball into the directory, then followed the install instructions (basically changing /usr/local/ to /usr/share/ in bin/mysqlaccess and running scripts/mysql_install_db). I copied the 4.0 executables to /usr/bin (which I did without thinking -- realizing I forgot to backup the original 3.23 binaries in that directory -- my big mistake). Now when I type mysqld_safe --user=mysql the daemon starts, but I get another message almost immediately that it is exiting. What's wrong and what do I need to do to get 4.0 working? Why is the daemon quitting on me? Is anyone else using Mandrake and encountering similar problems? Thanks! Hal There should be an error log. I'm not sure where it goes for a binary installation. Mine is /usr/local/mysql/var/hostname.err Maybe in /var/lib/mysql somewhere Anyway, look for your_hostname.err somewhere. Probably you don't have directory permissions set up properly. See the INSTALL-SOURCE file for details of how to set the permissions. It's around 20-30% through the file. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
