upgrade from 4.1 to 5.0
Hello, I'm going to upgrade from 4.1 to 5.0. My question is how do I handle the data from old version. Do I need to recreate tables in the mysql database when I install the new version or can I use the old mysql database from the old version? Data in other database I believe I can dump and import from the old version. Thank you. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compare tables from two systems
We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. Claire __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange order by problem
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
add a column if not exists
I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to DB after upgrading to mysql 4.1.5
Hi, I have a perl script that connect to mysql using the following: my $dbh = DBI-connect(DBI:mysql:$dbname;host=$host,$dblogin, $dbpasswd) or die Cannot connect to $dbname: . $DBI::errstr; But after I upgraded to 4.1.5 I get the following error message: DBI-connect(dbname;host=host) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client What shall I do? Please help. Thanks. Claire __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help for a query
Hi, Here's a table of mine namedate changeDate n1d1 cd1 n2d1 cd3 n2d2 cd1 n4d1 cd2 n1d2 cd5 n5d1 cd4 n6d2 cd2 I need to select every name for which the changeDate corresponding to d1 is greater than the changeDate corresponding to d2. Any way I can use one statement to do this? Thank you very much. Claire __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
distinct based on two fields
I have a table like this name price type A __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
distinct based on two fields--please help
I have a table like this name price type A10 1 B30 2 A20 1 B20 2 would like to distinct based on name and type, do a sum and get the following result name price type A 30 1 B 50 2 How do I do this with one sql query? Thanks. Claire ___ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query problem
Hi All, I have a query problem here. Say I have a table with employee records of three different departments. If each department manager wants to see employee info of their own department. Three different queries will be needed. Is there a way that I can write one single query and let SQL decide which department info to display at the run time? Thanks. __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
porting Oracle schema to MySQL
Hi, I'm a newbie working on a big project. The idea is to transport an existing Oracle database at http://www.ebi.ac.uk/arrayexpress/ to a local MySQL server. The Schema, scripts and loader for Oracle can be found in the website. But how to create an exactly same database with mySQL? I have no clue. Really need help. Thanks in advance. Claire __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem starting up mysql
Hi all, I installed mysql 4.0.14 binary distribution on RedHat 8.0 following the instructions in the manual. But I can't get it to work. Please help. When I start mysql using the command: shell bin/mysqld_safe --use=mysql the following message shows: Starting mysqld daemon with databases from /var/lib/mysql 030910 01:29:23 mysqld ended I have no idea what is going on here. If I try to test it with: shellcd sql-bench; perl run-all-tests I get the following error message: Got error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/m ysql.sock' (2)' when connecting to DBI:mysql:database=test;host=localhost with u ser: '' password: '' And mysql log has the following message 030904 13:03:06 mysqld started 030904 13:03:07 InnoDB: Started 030904 13:03:07 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 030904 13:03:07 Aborting 030904 13:03:07 InnoDB: Starting shutdown... 030904 13:03:09 InnoDB: Shutdown completed 030904 13:03:09 /usr/local/mysql/bin/mysqld: Shutdown Complete 030904 13:03:09 mysqld ended Anyone can tell me what goes wrong here? Thanks a lot. Claire __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
