Uninstalling Binary installation
hello list, I am a newbie. I have to upgrade mysql 3.23 binary installtion to mysql 5 in my system to start with. I figured out that uninstalling binary 3.23 and then installing a fresh installation would be a good option. since upgrading 3.23 to 4 and 4 to 5 can be a daunting task. Any suggestions to this ? Are there any scripts for uninstalling binary installation.? any suggestions? Thanks, __ 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]
Re: Uninstalling Binary installation
It would be helful if you could tell us what OS you are using please? Your email address suggests you could be running some flavour of Linux? If so, can you tell us what package management system your OS uses? I run SuSE Linux 9.2 pro, which uses the Redhat Package Management system - RPM. Does shell:/ # rpm -qv mysql return anything at all on your system? If your system uses RPM, you should get something like this for the installed packages. karsites:/ # rpm -qv mysql mysql-3.23.x.rpm karsites:/ # Keith In theory, theory and practice are the same; in practice they are not. On Sun, 16 Apr 2006, tuxlinsecure wrote: To: mysql@lists.mysql.com From: tuxlinsecure [EMAIL PROTECTED] Subject: Uninstalling Binary installation hello list, I am a newbie. I have to upgrade mysql 3.23 binary installtion to mysql 5 in my system to start with. I figured out that uninstalling binary 3.23 and then installing a fresh installation would be a good option. since upgrading 3.23 to 4 and 4 to 5 can be a daunting task. Any suggestions to this ? Are there any scripts for uninstalling binary installation.? any suggestions? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should we petition GoDaddy?
On 4/16/06, Nicolas Verhaeghe [EMAIL PROTECTED] wrote: I am slowly considering leaving GoDaddy, who has a very good bandwidth and ok tech support (I have seen better but much much worse) and acceptable prices, but unfortunately does not support MySQL 5 and PHP 5 either. I wonder if I could give these people a chance but one thing I'd like to do is see if we could petition these guys and simply threaten to close out our accounts if they do not get on with the music. The current version of MySQL is 3.23. I have no time upgrading my dedicated server to a RedHat machine with custom PHP and MySQL, I don't have the time to do that. Who think we could make them make the right move and pretty quickly? When you do leave, make sure and tell them why. Also stress your opinion of their move to MS software. Dotan http://IE-Only.com
~mytop output~
Hi, Iam using mytop script to monitor the processes on mysql server. This is output from mytop: MySQL on localhost (5.0.15-standard-log) up 0+02:24:46 [13:08:20] Queries: 22.6M qps: 2733 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00 qps now: 2801 Slow qps: 0.0 Threads:8 ( 4/ 1) 00/00/00/00 Key Efficiency: 98.3% Bps in/out: 0.0/ 2.3 Now in/out: 8.4/ 1.3k My problem is iam unable to see Se/In/Up/De(%) values in my mytop output. Iam running insert, select update queries but still iam unable to see the values for Se/In/Up/De(%) Can anyone help me fix the issue ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing raw MySQL files
Hi everyone. I used to run mysql on a local server here, which has some databases which are very important to me on it. The server was running mysql on Arch Linux, but unfortunatly this crashed, I was not able to rebuild the operating system but I was able to recover the raw database files off of the hard disk. Is there a way of importing these to my MySQL 3.2 server? (I beleive that Arch Linux was running 5.0). I have tried simply placing them in the mysql database folder but I get errors such as Incorrect information in file: './my0007/ee_pm.frm... when I try and perform any operations in PhpMyAdmin. Any help would be appreciated as I do not want to have to rebuilt the databases from scratch/ Cheers Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is this? -- errno=2006 errmsg=Server gone
This is software I use: D:\MDmysql --version mysql Ver 14.7 Distrib 4.1.12, for Win32 (ia32) C:\Apache\Apache2\binApache.exe -v Server version: Apache/2.0.54 Server built: Apr 16 2005 14:25:31 C:\Apache\Apache2\binver Microsoft Windows 2000 [Version 5.00.2195] Exactly which parameters can I tweak to fix this error (i.e. errno=2006 errmsg=Server gone)?? At this stage _any_ help/hint/guess would be really appreciated. regards, martin Dilipkumar wrote: Hi, If this is related to mysql please let us know what version you are using (Mysql) and which OS. This error can be fixed tunning your vaiables for mysql. Martin Olsson wrote: Hi, I'm still struggling with the errno=2006 err=server is gone. I thought I could provide some more info if that makes the problem any more clear: Basically, I'm uploading a file to a database. With no modifications to the form or the handler script it works on _some_ images and breaks on some. It's not a image size issue, I can happily upload some images larger than 2mb and it breaks on a 57kb image. Further I have examples of gif/jpg/png that works so it's not a file format issue either. The server runs locally (just as apache) and there is just one of them (no master/slave and/or replication stuff). I'm running everything on a win2k system with all service packs. I certainly wasn't shutting the server down at the moment and this error is 100% reprod on the specific files that break it. --- Joerg Bruehe; you pointed out that: this message is issued if the client gets an error reported while sending a command to the server. How can I determine if this is in fact the case? And in particular, how can I get my hands on the exact error sent? In general, is there any types of logging I can check and/or enable to further dig into the cause of this problem? regards, martin Martin Olsson wrote: Hi, I get this weird error message: ErrNo: 2006 Error: MySQL server has gone away. What does it mean? I couldn't find anything useful on google and the error message isn't exactly verbose.. :) regards, martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing raw MySQL files
Hi Chris, Looks like you may have to either upgrade your current server to the version the Arch Linux was running or install a second temp server of that version, export the databases and then import them into the ver 3.2 server. Personally, I'd look closely at upgrading if at all possible. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Christopher Beale [mailto:[EMAIL PROTECTED] Sent: Monday, 17 April 2006 7:46 PM To: mysql@lists.mysql.com Subject: Importing raw MySQL files Hi everyone. I used to run mysql on a local server here, which has some databases which are very important to me on it. The server was running mysql on Arch Linux, but unfortunatly this crashed, I was not able to rebuild the operating system but I was able to recover the raw database files off of the hard disk. Is there a way of importing these to my MySQL 3.2 server? (I beleive that Arch Linux was running 5.0). I have tried simply placing them in the mysql database folder but I get errors such as Incorrect information in file: './my0007/ee_pm.frm... when I try and perform any operations in PhpMyAdmin. Any help would be appreciated as I do not want to have to rebuilt the databases from scratch/ Cheers Chris -- 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: Importing raw MySQL files
Hi everyone. I used to run mysql on a local server here, which has some databases which are very important to me on it. The server was running mysql on Arch Linux, but unfortunatly this crashed, I was not able to rebuild the operating system but I was able to recover the raw database files off of the hard disk. Is there a way of importing these to my MySQL 3.2 server? (I beleive that Arch Linux was running 5.0). I have tried simply placing them in the mysql database folder but I get errors such as Incorrect information in file: './my0007/ee_pm.frm... when I try and perform any operations in PhpMyAdmin. Any help would be appreciated as I do not want to have to rebuilt the databases from scratch/ Cheers Chris Chris, you may be in luck. MySQL MyISAM table files are binary compatible across platforms. That is to say that database table files from Arch Linux will work fine on Mac, Solaris, Windows, etc., without modification. I'm not as familiar with InnoDB tablespaces but they may work as well. Definitely try this with a copy of your files, not the originals. In all likelihood you need to update to at least the same version of the server software you were running on the now-crashed server. In other words, if you update to 5.0.x and place the table files in the proper path, you should be OK. MySQL 3.2 is a very old version and likely simply isn't compatible with tables created in 5.0. If the situation were reversed (you had a 3.2 server that crashed and you wanted to bring the tables into 5.0) that might work fine. Hope this helps, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
I just did this last week on a 5.0.18 machine. It's supported by the
mysqlmanager out of the box. Here are a copy of my notes, and worked
well on a Suse machine. The locations of your files may not be the
same.
# stop the server, if running
/etc/init.d/mysql stop
# edit /etc/my.cnf to set up the instances and
# and also tell the startup script to use mysqlmanager
[mysql.server]
use-manager
[mysqld07]
port= 3307
socket = /srv/mysql/lx07sock
pid-file= /srv/mysql/lx07/lx09.pid07
datadir = /srv/mysql/lx07/data
log-error = /srv/mysql/lx07/mysql.error.log
[mysqld20]
port= 3320
socket = /srv/mysql/lx20sock
pid-file= /srv/mysql/lx20/lx09.pid20
datadir = /srv/mysql/lx20/data
log-error = /srv/mysql/lx20/mysql.error.log
# run commands as user mysql
su mysql
# go to the main mysql directory
cd /srv/mysql
# make a directory for each instance
mkdir lx07
mkdir lx20
mysql_install_db --datadir=/srv/mysql/lx07/data --user=mysql --verbose
mysql_install_db --datadir=/srv/mysql/lx20/data --user=mysql --verbose
# start the server
/etc/init.d/mysql start
# connect to first instance and configure so root can log in
# from anywhere. You may or may not want to do this.
mysql --socket=/srv/mysql/lx07sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password;
use mysql;
update user set password=password('password') where user='root';
flush privileges
# same for second instance
mysql --socket=/srv/mysql/lx20sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password';
use mysql;
update user set password=password('password') where user='root';
flush privileges
Regards,
Rich
-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 15, 2006 12:53 AM
To: mysql@lists.mysql.com
Subject: ~ How to install 3 instances of mysql~
Hi,
I need to install 3 instances of mysqld server on a single
machine. Can anyone let me know how this can be acheived ?
It would be helpful if someone can send me some links and
suggestions regarding the same. Also pls lemme know what kind
of a configuration file i need to have in order to acheive the same.
Thanks in advance,
Abdul.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
Thanks Rich.
On Mon, 2006-04-17 at 08:28 -0500, Duzenbury, Rich wrote:
I just did this last week on a 5.0.18 machine. It's supported by the
mysqlmanager out of the box. Here are a copy of my notes, and worked
well on a Suse machine. The locations of your files may not be the
same.
# stop the server, if running
/etc/init.d/mysql stop
# edit /etc/my.cnf to set up the instances and
# and also tell the startup script to use mysqlmanager
[mysql.server]
use-manager
[mysqld07]
port= 3307
socket = /srv/mysql/lx07sock
pid-file= /srv/mysql/lx07/lx09.pid07
datadir = /srv/mysql/lx07/data
log-error = /srv/mysql/lx07/mysql.error.log
[mysqld20]
port= 3320
socket = /srv/mysql/lx20sock
pid-file= /srv/mysql/lx20/lx09.pid20
datadir = /srv/mysql/lx20/data
log-error = /srv/mysql/lx20/mysql.error.log
# run commands as user mysql
su mysql
# go to the main mysql directory
cd /srv/mysql
# make a directory for each instance
mkdir lx07
mkdir lx20
mysql_install_db --datadir=/srv/mysql/lx07/data --user=mysql --verbose
mysql_install_db --datadir=/srv/mysql/lx20/data --user=mysql --verbose
# start the server
/etc/init.d/mysql start
# connect to first instance and configure so root can log in
# from anywhere. You may or may not want to do this.
mysql --socket=/srv/mysql/lx07sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password;
use mysql;
update user set password=password('password') where user='root';
flush privileges
# same for second instance
mysql --socket=/srv/mysql/lx20sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password';
use mysql;
update user set password=password('password') where user='root';
flush privileges
Regards,
Rich
-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 15, 2006 12:53 AM
To: mysql@lists.mysql.com
Subject: ~ How to install 3 instances of mysql~
Hi,
I need to install 3 instances of mysqld server on a single
machine. Can anyone let me know how this can be acheived ?
It would be helpful if someone can send me some links and
suggestions regarding the same. Also pls lemme know what kind
of a configuration file i need to have in order to acheive the same.
Thanks in advance,
Abdul.
This email has been Scanned for Viruses!
www.newbreak.com
This email has been Scanned for Viruses!
www.newbreak.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to connect to mySQL?
徐晶 wrote: Hi, I want to know how I can connect to MySQL with ODBC driver? Is there something else to be installed besides MySQL for Windows? http://dev.mysql.com/doc/refman/5.1/en/odbc-connector.html PB Thanks a lot! Best Wishes, -- John Xu, EE, BUPT, P.R. China 北京邮电大学-电子工程学院 徐晶 Addr:北京邮电大学219#信箱 Zip:100876 Dorm:北京邮电大学 学6-347 TEL:010-62284785 Mobile:13581764924 QQ:46119403 MSN:[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.1/313 - Release Date: 4/15/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.1/313 - Release Date: 4/15/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~mytop output~
Mytop was written by Jeremy for Mysql 3.x, 4.0, and 4.1 , it is not fully compatible with 5.0, but to just get the values you requested, all you need to do is a small hack of the code 1)# which mytop ( to locate your mytop executable) 2) open the file for editong (i.e. vi mytop' (or use your favourite editor) ) 3) change all the lines which say show status to show global status ( its a 5.0 thing) 4) start mytop now This would display the values you were asking for, but when you do a reset counters, some of the counters will not work as expected, you will see, but i guess one can live with it, just for the fact that mytop is so cool ... Also just play with the code yourself, I might have missed something. Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/17/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, Iam using mytop script to monitor the processes on mysql server. This is output from mytop: MySQL on localhost (5.0.15-standard-log) up 0+02:24:46 [13:08:20] Queries: 22.6M qps: 2733 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00 qps now: 2801 Slow qps: 0.0 Threads:8 ( 4/ 1) 00/00/00/00 Key Efficiency: 98.3% Bps in/out: 0.0/ 2.3 Now in/out: 8.4/ 1.3k My problem is iam unable to see Se/In/Up/De(%) values in my mytop output. Iam running insert, select update queries but still iam unable to see the values for Se/In/Up/De(%) Can anyone help me fix the issue ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is the more detailed document of MySQL Network?
On Mon, Apr 17, 2006 at 11:26:38AM +0800, 古雷 wrote: Hello: For example: What does Web Access and Remote Troubleshooting mean in this page https://shop.mysql.com/network.html?rz=s2 'Web Access' means you have access to the web-based support system. 'Remote Troubleshooting' means that MySQL support engineers will log in to your systems to troubleshoot problems. There is more information about this here: http://www.mysql.com/company/legal/supportpolicies/policies-08.html Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling Binary installation
Hi, thanx for looking into this issue OS version: Red Hat Enterprise Linux AS release 3 (Taroon Update 3) mysql : mysql-3.23.58-pc-linux-i686.tar.gz I need to write an uninstall script for this,since it is to be done on many systems. and then install mysql 5 binary. Thanks, [EMAIL PROTECTED] wrote: It would be helful if you could tell us what OS you are using please? Your email address suggests you could be running some flavour of Linux? If so, can you tell us what package management system your OS uses? I run SuSE Linux 9.2 pro, which uses the Redhat Package Management system - RPM. Does shell:/ # rpm -qv mysql return anything at all on your system? If your system uses RPM, you should get something like this for the installed packages. karsites:/ # rpm -qv mysql mysql-3.23.x.rpm karsites:/ # Keith In theory, theory and practice are the same; in practice they are not. On Sun, 16 Apr 2006, tuxlinsecure wrote: To: mysql@lists.mysql.com From: tuxlinsecure Subject: Uninstalling Binary installation hello list, I am a newbie. I have to upgrade mysql 3.23 binary installtion to mysql 5 in my system to start with. I figured out that uninstalling binary 3.23 and then installing a fresh installation would be a good option. since upgrading 3.23 to 4 and 4 to 5 can be a daunting task. Any suggestions to this ? Are there any scripts for uninstalling binary installation.? any suggestions? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: VPN Recommendation
Michael, I use OpenVPN for all our mysql replication to across WAN links. This is entirely on Linux. We did have weird problems with UDP but they were entirely solved by using TCP. The OpenVPN/cpu usage is never the limiting factor on replication. We do have a full T1 though so bandwidth issues haven't been a noticeable issues. Traffic graphs do show more utilization during the day but that is to be expected. Sorry our situations are quite different but I figured it would give you more information. Quoting Michael Louie Loria [EMAIL PROTECTED]: Hello, What is the recommended speed for my VPN Server and Client (OpenVPN)? The VPN Server is also the Database Server using MySQL (win32). The VPN Client contains the application (using MyODBC) connecting to the VPN Server and acessing the Server. My speed VPN Server (512Kbps) and Client (256Kbps) is kinda slow particularly the queries even when I used stored procedures and functions. So I would like to know some of your suggestions on my setup. Thanks, Michael Louie Loria LoRz Technology Solutions htttp://www.lorztech.com James Nobis Web Developer / System Engineer Academic Superstore 2101 E. Saint Elmo Rd, Ste 360, Austin, TX 78744 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb vs myisam
Hi. Thanks for your response [EMAIL PROTECTED] wrote: Do you have any idexes on the table? No. The table looks like this. Would there be any advantage in creating indexes for it? | id | | LastUpdated| | 32957c615b37b5674f99d1cfd06d6a23 | | 20060416075614 | | 33d1d3a8b63c983a67ec5ab38d148774 | | 20060416003803 | | 3b0d2f1e5c2fe60377220aa146abd926 | | 20060411095312 | | 3c61d917967fb3ed45fa2ed8efb67aa8 | | 20060413150746 | | 4e0debc18ce34d8f131e05664f9df6da | | 20060411104352 | What does your mysql show create table tbl_name \G /~ mysql show create table sessions \G *** 1. row *** Table: sessions Create Table: CREATE TABLE `sessions` ( `id` char(32) NOT NULL, `a_session` longtext, `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql \_ Thanks. Kind regards. In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Luke Vanderfluit wrote: To: mysql@lists.mysql.com From: Luke Vanderfluit [EMAIL PROTECTED] Subject: innodb vs myisam Hi. I have the following myisam table: The table is only 32,000 rows, but over 60Megs in size. And mysql seems to be wanting to write to that file alot, so it may well be trying to seek all over the disk looking for the right spot all the time. Does innodb do a better job at keeping the file on the disk smaller? Does an innodb table take up less disk space than myisam? Is an innodb table compacter and would therefore require less disk seek time or I/O than myisam? Kind regards. Luke. -- Luke
need help to delete duplicates
hey all, I have a table mytable that looks like this: id tinyint primary key auto_increment row1 varchar 150 row2 varchar 150 I would like to remove all duplicates, which means that if n records have the same row1 and row2, keep only one record and remove the duplicates. Any idea how to do this? thanks in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help to delete duplicates
Sample Data: ID-Row1-Row2 1-A-B 2-A-B Row1 and Row2 are duplicate, so you only want one. Which ID do you want? -will On 4/17/06, Patrick Aljord [EMAIL PROTECTED] wrote: hey all, I have a table mytable that looks like this: id tinyint primary key auto_increment row1 varchar 150 row2 varchar 150 I would like to remove all duplicates, which means that if n records have the same row1 and row2, keep only one record and remove the duplicates. Any idea how to do this? thanks in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump question
Hello, I have just created a new fedora 4 box with the latest mysqldump mysqldump Ver 10.9 something has changed. Before all my tables entries had their own insert statements for each row. Now each table has one insert with all the values appended to the end. is there switch that puts it back to the old way of separate inserts for each row? Thanks Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump question
Randy Paries wrote: Hello, I have just created a new fedora 4 box with the latest mysqldump mysqldump Ver 10.9 something has changed. Before all my tables entries had their own insert statements for each row. Now each table has one insert with all the values appended to the end. is there switch that puts it back to the old way of separate inserts for each row? Thanks Randy Hi Randy, since 4.something extended-inserts are used by default. Use mysqldump --extended-insert=false to get the old behaviour. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help to delete duplicates
If the ID doesn't represent anything, you can CREATE TABLE new_table SELECT DISTINCT Row1, Row2 FROM old_table And then recreate your index(es). All your autoincrement IDs will be changed. On 4/17/06, Patrick Aljord [EMAIL PROTECTED] wrote: On 4/18/06, William Fong [EMAIL PROTECTED] wrote: Sample Data: ID-Row1-Row2 1-A-B 2-A-B Row1 and Row2 are duplicate, so you only want one. Which ID do you want? one of them has to be deleted, it doesn't really matter which one it is. id isn't attached to any other table and doesn't represent important data or anything. So i just want to end up with only one record having Row1= A and Row2= B. it doesn't matter if id=1 or 2
How can I use a value computed in my SQL query for further computations?
Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations?
To add to this, I will also want to be able to ORDER BY those three new columns (totalviews, totalclicks, grandtotal) as well.. I'm using mySQL 5 and innodb tables. I saw this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html But it says: Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement does not work as expected: mysql SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5; So that seems pretty useless for my needs. And I'm using this in combination with PHP and Ruby for what it's worth. It seems silly that I would have to use PHP's multisort() to sort/order data that I already have in a database, and it seems silly that I should have to use PHP to do basic math on the table when mySQL can do it probably faster. -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006 7:33 PM To: mysql@lists.mysql.com Subject: How can I use a value computed in my SQL query for further computations? Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; -- 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: How can I use a value computed in my SQL query for further computations?
[snip] Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; [/snip] Use variables; http://dev.mysql.com/doc/refman/5.1/en/set-statement.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations?
[snip] Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. [/snip] More http://dev.mysql.com/doc/refman/5.1/en/example-user-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations? [solved]
Okay, well it turns out that this works exactly how I want/expect it to. The documentation was a bit confusing. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (@tv:=(views * ppview)) AS totalviews, (@tc:=(clicks * ppclick)) AS totalclicks, @tv + @tc AS grandtotal FROM advertisements a ORDER BY grandtotal desc; Thanks Jay for your ideas. Daevid. -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006 7:42 PM To: mysql@lists.mysql.com Subject: RE: How can I use a value computed in my SQL query for further computations? To add to this, I will also want to be able to ORDER BY those three new columns (totalviews, totalclicks, grandtotal) as well.. I'm using mySQL 5 and innodb tables. I saw this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html But it says: Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement does not work as expected: mysql SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5; So that seems pretty useless for my needs. And I'm using this in combination with PHP and Ruby for what it's worth. It seems silly that I would have to use PHP's multisort() to sort/order data that I already have in a database, and it seems silly that I should have to use PHP to do basic math on the table when mySQL can do it probably faster. -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006 7:33 PM To: mysql@lists.mysql.com Subject: How can I use a value computed in my SQL query for further computations? Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fetch and updation in single step/query
Dear Friends, I run several processes and they need to query the mysql 5.0.8 database simultaneously .I have a config table which have the record id. I need to fetch that and increment that .What I feel that the same record id is fetched by different simultaneosly before i update .Can anyone help me in either: 1)telling me a single query which will fetch and incr. in the same query. so the problem of simultaneously queries are solved. 2)A system by which delaying the other queries are done, I use PHP . Expecting a quick reply. Thanks, Abhishek Jain
My Left Joins are Doubling the SUM()
My Left Joins are Doubling the SUM() SELECT packageItemID, packageItemName,packageItemPrice ,SUM(packageItemTaxAmount) as packageItemTaxAmount ,SUM(packageCreditAmount) as packageCreditAmount FROM packageItem LEFT JOIN packageCredit ON packageItemID=packageCreditItemID LEFT JOIN packageItemTax ON packageItemTaxItemID=packageItemID AND packageItemTaxActive=1 GROUP BY packageItemID ORDER BY packageItemID packageItem Table packageItemID | packageItemName | packageItmePrice 1 | Delta Hotel | 100.00 packageCredit Table packageCreditID | packageCreditItemID | packageItemType | packageCreditAmount 1 | 1 | Deposit | 25.00 2 | 1 | Balance | 92.00 packageItemTax packageItemTaxID | packageItemTaxItemID | packageItemTaxName | packageItemTaxAmount 1 | 1 | GST | 7.00 2 | 1 | HST | 10.00 The desired result of the query should be: 1 | Delta Hotel | 100.00 | 17.00 | 117.00 But it keeps doubling the tax and the credit amounts and results look like this 1 | Delta Hotel | 100.00 | 34.00 | 234.00 is there a way to execute this query without this happening? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
