Re: mysqldump corrupting utf8 data
How are you importing the dump into mysql? Importing by piping the dump file to mysql may broke some chars due to shell. Have you tried this: (with in mysql client) SET NAMES UTF8; SET CHARACTER SET UTF8 source /pathto/dump.sql Cheers, --Ravi Sean O'Hara wrote: Hi All, I've been googling all morning trying to find info on how to do a mysqldump of a utf8 encoded database from which I can restore without corrupting all the non ascii characters. If anyone has any pointers on this, I'd be most grateful. Here is my setup. I am building a ruby on rails app and all the data is being entered from that application. The data is displayed fine if when it hasn't undergone a backup with msyqldump. I'm using mysql server 4.1.16 on Fedora Core 4. Here is an example show create table on one of the relevant tables: artists | CREATE TABLE `artists` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `biography` text, `created_at` datetime default NULL, `updated_at` datetime default NULL, `sort_name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | The restored table has the same show create table definition. I've used enca on the dump file to see if it's been encoded properly: enca -L none testdump1.sql Universal transformation format 8 bits; UTF-8 So that seems to be in order. Here's the top of the dump file: 1 -- MySQL dump 10.9 2 -- 3 -- Host: localhostDatabase: alienrails_production 4 -- -- 5 -- Server version 4.1.16 6 7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 10 /*!40101 SET NAMES utf8 */; 11 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 12 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 13 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 14 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 15 16 -- 17 -- Table structure for table `artist_images` 18 -- Obviously I'm mussing something, but I have no idea what. Thanks in advance, Sean --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]
One question about mysql_close
Hi, I have a question about Mysql C library function mysql_close. If my program doesn't invoke mysql_close before exiting, is there any side effect? I don't know whether this is the proper list I should send email for this topic. If I should send my email to another topic, which list is better? Thanks, James _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One question about mysql_close
Hi, Yes Aborted connects will start increasing than the connections. Thanks Regards Dilipkumar - Original Message - From: ��?��╁�� [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 11:34 AM Subject: One question about mysql_close Hi, I have a question about Mysql C library function mysql_close. If my program doesn't invoke mysql_close before exiting, is there any side effect? I don't know whether this is the proper list I should send email for this topic. If I should send my email to another topic, which list is better? Thanks, James _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Monitoring Slow Queries
Hi, Though I understand very well that it has been discussed lots of time before but I don't have time to browse through the previous archives and dig out the stuff I need. So, guys, I would be thankful if you could give me your valuable advice that I need right now. I just need to know 1) What parameters I need to set in my.cnf to log slow queries so that they stick out conspicuously and get noticed, and 2) How I can find out from the log that MySQL creates as a result of 1) as to which queries are running slow. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring Slow Queries
On Wednesday 02 August 2006 09:28, Asif Lodhi wrote: Hi, Though I understand very well that it has been discussed lots of time before but I don't have time to browse through the previous archives and dig out the stuff I need. So, guys, I would be thankful if you could give me your valuable advice that I need right now. I just need to know 1) What parameters I need to set in my.cnf to log slow queries so that they stick out conspicuously and get noticed, and http://dev.mysql.com/doc/mysql/search.php?version=4.1q=slow+query+loglang=en 2) How I can find out from the log that MySQL creates as a result of 1) as to which queries are running slow. http://dev.mysql.com/doc/mysql/search.php?version=4.1q=slow+query+loglang=en -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query cache about the federated engine
I have a problem about the performance of federated engine. The mysql5.0 reference manual says that the FEDERATED tables do not work with the query cache, aren't they? How about the query cache used by the federated engine in the mysql5.1? If the query cache cann't be used with the federated engine tables still, how to enhance the query performance against to the federated tables effectively?
How does the federated engine table work
How does the federated engine table work when the query statement include a join substatement, it joins a local table with innodb engine and a federated table. How does the server deal with the query substatement like this?
question about the query against to the federated engine tables
I have a question about the query against to the federated engine tables. As the following example: select Id,name from e01_system.category Note: the e01_system.category is a federated table. I select only tow fields from the federated table category with the above sql statement. But it seemed as if gets all the fields of the table (the table has more then ten fields actually), so the performance of the sql stetement is very slow when the table's size is very big.I conclude this through monitoring the throughput of the network with a network tool named sniffer. I think the federated table works not as what i expected.
Re: Relay Log Lost on Slave
Hi Dilipkumar, I checked the output of show slave status, and memorize; RELAY_MASTER_LOG_FILE, and EXEC_MASTER_LOG_POS. Then, change master to master_log_file='xx, master_log_pos=xx; However, still I get the same error message. Umm. Kenji On 8/2/06, Dilipkumar [EMAIL PROTECTED] wrote: Hi, If you relay log is lost try out the this :- Run the Change Master Position script, See the log output from where did the replication stopped. So you can start your replication. Thanks Regards Dilipkumar - Original Message - From: Kenji HIROHAMA [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 9:36 AM Subject: Relay Log Lost on Slave Hi, Under my replication environment, what should I do if I lose the current relay-log file on slave side? 1. one master and one slave replication is working 2. stop the master and the slave 3. remove the current relay log file manually 4. I can't start replication with start slave command the error message is; ERROR 29 (HY000): File 'xxx-relay-bin.25' not found (Errcode: 2) Should I sync the data manually and start replication from the beginning? Thanks, -- [EMAIL PROTECTED] Kenji Hirohama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- [EMAIL PROTECTED] Kenji Hirohama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relay Log Lost on Slave
Hi, First reset slave and then change master to script run it. Thanks Regards Dilipkumar - Original Message - From: Kenji HIROHAMA [EMAIL PROTECTED] To: Dilipkumar [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 3:29 PM Subject: Re: Relay Log Lost on Slave Hi Dilipkumar, I checked the output of show slave status, and memorize; RELAY_MASTER_LOG_FILE, and EXEC_MASTER_LOG_POS. Then, change master to master_log_file='xx, master_log_pos=xx; However, still I get the same error message. Umm. Kenji On 8/2/06, Dilipkumar [EMAIL PROTECTED] wrote: Hi, If you relay log is lost try out the this :- Run the Change Master Position script, See the log output from where did the replication stopped. So you can start your replication. Thanks Regards Dilipkumar - Original Message - From: Kenji HIROHAMA [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 9:36 AM Subject: Relay Log Lost on Slave Hi, Under my replication environment, what should I do if I lose the current relay-log file on slave side? 1. one master and one slave replication is working 2. stop the master and the slave 3. remove the current relay log file manually 4. I can't start replication with start slave command the error message is; ERROR 29 (HY000): File 'xxx-relay-bin.25' not found (Errcode: 2) Should I sync the data manually and start replication from the beginning? Thanks, -- [EMAIL PROTECTED] Kenji Hirohama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- [EMAIL PROTECTED] Kenji Hirohama -- 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]
Database Return Errors
Hi, I am developing a VB6 app with a MySQL-5.0.22/WinXP backend. I have skimmed the Stored Procedures/Triggers docs and it looks like I can define custom error-names or number - though I have also seen the Handlers in the same doc. The question is: Can I get the error-codes or error-names that MySQL returns in VB6',s ADO.Erross collection? Will I get one if MySQL throws an error - such as when a duplicate constraint is violated? - so that I can display meaningful error messages to the user. -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database Return Errors
Have you checked out MyConnector/NET and the MySqlException class? -Original Message- From: Asif Lodhi [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 6:17 AM To: mysql@lists.mysql.com Subject: Database Return Errors Hi, I am developing a VB6 app with a MySQL-5.0.22/WinXP backend. I have skimmed the Stored Procedures/Triggers docs and it looks like I can define custom error-names or number - though I have also seen the Handlers in the same doc. The question is: Can I get the error-codes or error-names that MySQL returns in VB6',s ADO.Erross collection? Will I get one if MySQL throws an error - such as when a duplicate constraint is violated? - so that I can display meaningful error messages to the user. -- TIA, Asif -- 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 5: Error 1130: host X is not allowed to connect to this mysql ....
Hello there I just finished installing mysql 5 on my linux server and I reset the root password as an initial post-install setting. now that i try to connnect from other machines in the network I always get a message similar to the following: C:\Documents and Settings\msentissimysql -u root -h 192.168.3.60 ERROR 1130 (0): Host '192.168.3.10' is not allowed to connect to this MySQL server I looked for the my.cnf file and it is nowhere to be found ? some guidance please. Thanks Simo Sentissi Collaboration Networks 406-579-8256 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help for Loosely Couple Properties
They are user defined properties. -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 01, 2006 8:11 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Help for Loosely Couple Properties On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: I have a table that contains properties that can be associated with any table whose primary key is a LONG. Lets say that there is just one kind of property. The table looks something like this: TABLE StringVal REF_ID BIGINT// row to associate property with TYPE_ID BIGINT// type of string property VAL VARCHAR // property value P_KEY( REF_ID, TYPE_ID ) There is another table to represent a specific StringVal type along with its default value: TABLE StringType ID BIGINT // The TYPE ID NAMEVARCHAR // The unique name of this property DEF_VAL VARCHAR // The default value of this property Actually, the rub is that you are not using specific columns for specific entity attributes, and are pretty much storing everything in one gigantic table. Any particular reason for this? For instance, why not have a column called color, instead of overcomplicating things? -jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 5: Error 1130: host X is not allowed to connect to this mysql ....
Simo Sentissi schrieb: Hello there I just finished installing mysql 5 on my linux server and I reset the root password as an initial post-install setting. now that i try to connnect from other machines in the network I always get a message similar to the following: C:\Documents and Settings\msentissimysql -u root -h 192.168.3.60 ERROR 1130 (0): Host '192.168.3.10' is not allowed to connect to this MySQL server I looked for the my.cnf file and it is nowhere to be found ? some guidance please. Thanks Simo Sentissi Collaboration Networks 406-579-8256 The password you changed only gives you local access. Log in to your linux server via SSH or directly at the machine. Then log in to mysql as root with your given password (mysql -u root -p). Then execute something like this: GRANT ALL PRIVILEGES ON *.* TO msentissi@192.168.3.10 identified by yourpassword; This will allow you to login from your Windows machine. You may also want to add with grant option to the line above, which will give you full administrator privileges on the mysql server. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump error
Hi ALL I am getting the below error [EMAIL PROTECTED] drupal]# mysqldump --tab=/usr/local/drupal/drupalbckup/ --opt drupal mysqldump: Got error: 1: Can't create/write to file '/usr/local/drupal/drupalbckup/access.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump error
I am getting the below error [EMAIL PROTECTED] drupal]# mysqldump --tab=/usr/local/drupal/drupalbckup/ --opt drupal mysqldump: Got error: 1: Can't create/write to file '/usr/local/drupal/drupalbckup/access.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' Change the owner of that directory so the mysql server can write into it. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
determine safest value for max_connections
Hello folks. How do I determine what is the safest value that I can set for max_connections in my.cnf? The default value of 100 is proving to be a shortfall during some cases of peak hour traffic. Could you point me to a resource available if any? 1. Our server is RHEL 3, 2*3.00 GHz, 4GB ram. This acts both as our web and db server. 2. The tables are of INNODB type. 3. mytop shows qps as 66; Hits/s as 12.3 Regards, Rithish.
RE: identify process that created the connection
Thanks Ravi. That definitely did help. However, the scenario that I wish to monitor is when there are a lot of sleeping threads, it is peak-hour, and the number of threads is dangerously near to the max_connections value. Hence, I would want to log similar information as described in the blog, but on another server. Hence, I would not be using up any up any conenctions on my live server for this. My issue is that I want to log the MySQL connection id of server1 in server 2. How will I achieve this, as connection_id() will return the current connection id (i.e. for server2)? Regards, Rithish. -Original Message- From: Ravi Prasad LR [mailto:[EMAIL PROTECTED] Sent: Friday, July 28, 2006 9:36 AM To: Rithish Saralaya Cc: MySQL general mailing list Subject: Re: identify process that created the connection This blog may help, http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connecti on/ Cheers, Ravi Rithish Saralaya wrote: Hello people. Is it possible to find the process that invoked the mysql thread, given a mysql thread id? We have a web application that runs on Linux-Apache-MySQL-PHP; and I sometimes see numerous mysql threads in sleeping mode when I run mytop. I think the sleeping mysql threads could be due to the fact that some of my web-page(s) have obtained a mysql connection, executed their queries, but have not terminated(and have not released the mysql connection also). If I could know the httpd processes that have created these connections, I would be able to find out the pages that are the culprit. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump error
[EMAIL PROTECTED] drupal]# mysqldump --tab=/usr/local/drupal/drupalbckup/ --opt drupal mysqldump: Got error: 1: Can't create/write to file '/usr/local/drupal/drupalbckup/access.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' Change the owner of that directory so the mysql server can write into it. Regards Dominik Thanks it did worked but i want it to dump on a single file for example backup.sql, as i could see lot of file Please write to the list next time. If you want a single file use mysqldump [your options] [your database] backup.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Version 5.1.6-alpha-log unexpected total crash
Dear MySQL, I am getting an unexpected crash in MySQL 5.1.6, with nothing written to the log. This happens with InnoDB tables and an Foreign Key error. Tables are something like: CREATE TABLE tax ( `type` varchar(8) NOT NULL, PRIMARY KEY (`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `service` ( `service_code` varchar(16) NOT NULL default '', `country_code` varchar(3) NOT NULL default 'GBR', `tax_type` varchar(8) default NULL, PRIMARY KEY (`service_code`,`country_code`), KEY `tax_type` (`tax_type`), CONSTRAINT `service_ibfk_2` FOREIGN KEY (`tax_type`) REFERENCES `tax` (`type`) ON DELETE SET NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Then the query: UPDATE service SET tax = '' WHERE .. This should say 'Foreign Key Error' (or similar). What I get is: 'Unable to execute Query. SQL Error (2013) Lost connection to MySQL server during query' After which MySQL needs restarting. I have executed the usual 'CHECK TABLE tax EXTENDED' and alike, which results no errors. Obviously this is quite worrying. Can any expert on InnoDB Referential Integrity offer any clues? Thanks for your help, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Version 5.1.6-alpha-log unexpected total crash MORE DETAILS
MySQL, After thinking hard on the subject, I realise the server in question was running a data set from an InnoDB HotBackup. (Which is on trial so I have no support.) The source machine is a PowerPC IBM. The crashing server is an Intel Pentium. The difference being that one is big-endian, the other little-endium. So some RI reference in the InnoDB table space might be garbage on the Pentium server. Might this explain the crash? Any thoughts would be very welcome! Ben Ben Clewett wrote: Dear MySQL, I am getting an unexpected crash in MySQL 5.1.6, with nothing written to the log. This happens with InnoDB tables and an Foreign Key error. Tables are something like: CREATE TABLE tax ( `type` varchar(8) NOT NULL, PRIMARY KEY (`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `service` ( `service_code` varchar(16) NOT NULL default '', `country_code` varchar(3) NOT NULL default 'GBR', `tax_type` varchar(8) default NULL, PRIMARY KEY (`service_code`,`country_code`), KEY `tax_type` (`tax_type`), CONSTRAINT `service_ibfk_2` FOREIGN KEY (`tax_type`) REFERENCES `tax` (`type`) ON DELETE SET NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Then the query: UPDATE service SET tax = '' WHERE .. This should say 'Foreign Key Error' (or similar). What I get is: 'Unable to execute Query. SQL Error (2013) Lost connection to MySQL server during query' After which MySQL needs restarting. I have executed the usual 'CHECK TABLE tax EXTENDED' and alike, which results no errors. Obviously this is quite worrying. Can any expert on InnoDB Referential Integrity offer any clues? Thanks for your help, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Spreading Database across multiple disks
Tripp, YMMV, but I'm a firm believer that keeping things as simple as possible pays big dividends. I believe the advice to spread out IO tasks among different disks is good advice, when dealing with direct-attached disks you deal with directly (i.e. not part of a RAID). The setup and maintenance is not inconsiderable, however, and performance will lag behind an array no matter what you do. If you're building a high-performance server, you may well want to consider moving away from individual disks and using hardware RAID for performance and redundancy. Once you're on such a device, you need to spend less/no time worrying about directing data to individual disks, because all your data is spread among mutiple disk spindles and can also use RAM caching in the device. You also likely have the option of dedicating a group of disks within the device for logging, while still utilizing all that hardware RAID has to offer. At my last job, we used an Apple XServe RAID to host our MySQL data, and performance was astounding. We also used Sun 3310 arrays to host our Sybase data, and performance was likewise astounding, though setup and administration was more complex than the Apple (you had more options too though). I say astounding considering what these advanced little boxes cost, which is just several thousand dollars. The Apple box has been certified on Mac OS X, Windows, Linux and Netware, so it's pretty versatile. There are also PCI card solutions that let you create a hardware RAID within your server box of course - ATA, SATA, and SCSI. Lots of options to choose from so you can select what fits your needs and budget. If you're wanting to build a high-availability setup, two server boxes with one Apple RAID, each using half the array, could be a very good setup, insulating you from hardware and software disasters. Expense is relative - I'd estimate you'd be looking at $25K for such a setup, easily more depending on your server boxes. You can do the same thing with many SCSI arrays (Sun 3310/3320s included) with some advanced setup work. Hope this helps, Dan On 8/1/06, Tripp Bishop [EMAIL PROTECTED] wrote: Howdy all, We're looking at building a new database server and I'm looking into strategies for optimizing disk i/o. Bit of background. We will be running a single database on this box under MySQL 5.0.15. All of the tables are INNODB. We have about 130 tables in the db. I've read that it's a good idea to have the innodb log files written out to a seperate physical drive so that those operations don't bog down the rest of the database disk I/O operations. Configuring INNODB to do that looks straightforward. Then there's the data. I know that I can create multiple shared table spaces and locate them on seperate disks but that doesn't seem to give me control over where individual tables' data are written on the disk array. Is there a way using innodb_file_per_table to control where the individual .idb files are located in the disk array? Do I have to use symbolic links to trick innodb or is there a cleaner way? Also, I've been told that innodb table spaces never shrink. Is this true or is there a way to periodically cleanup the idb files to reclaim unused space? Cheers, Tripp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get v5.0.22 to work;alternatives?
http://downloads.mysql.com/archives.php?p=mysql-5.0 On 8/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: n http://dev.mysql.com/downloads/ Look for older releases Thanks but I want an older _build_ of the 5.0 release, not an older release. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doing a join
I have two tables: MEMBERS: MEM_ID ... GROUPS: GRO_ID: ... And one joiner MEM_GRO: MEM_ID, GRO_ID I want to print out a list like this GROUP_NAME, NUMBER_OF_MEMBERS Even when the number of members is 0, how do I do that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query cache about the federated engine
--On August 2, 2006 5:25:51 PM +0800 wangxu [EMAIL PROTECTED] wrote: I have a problem about the performance of federated engine. The mysql5.0 reference manual says that the FEDERATED tables do not work with the query cache, aren't they? How about the query cache used by the federated engine in the mysql5.1? If the query cache cann't be used with the federated engine tables still, how to enhance the query performance against to the federated tables effectively? Well for one if the machine isn't on the same LAN you'll never have good DB performance -- not sure if this is the case or not, just a helpful hint. For two, the reason why federated doesn't use the query cache is then you have to somehow invalidate the remote cache(s) which would require some form of protocol extension, either in the SQL or the over the wire protocol so that the server with the federated table could ask if it could cache a result, and the server that's serving to the federated client could inform that server when it's cache isn't valid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spreading Database across multiple disks
I'd recommend RAID 10 - with 4 disks instead of 2, you'll roughly double performance over RAID 1, both for reading and writing. You might also look at 6 disks - 4 73's in RAID 10 for your data, plus 2 smaller, slower less expensive disks in RAID 1 (18 gb or 36 gb maybe) for your boot disks. As far as I know, the way to reclaim space from innodb is to use file-per-table and then OPTIMIZE the tables. http://bugs.mysql.com/bug.php?id=14206 Need 4.1 or newer. HTH, Dan On 8/2/06, Tripp Bishop [EMAIL PROTECTED] wrote: Dan, Thanks for the input. We're trying to build our version of a high performance box. The basic specs are: Dual Opteron 265 (dual core) CPU 8 GB of RAM 140 GB RAID 10 (4 x 72GB 15K RPM SCCI) Adaptec SCCI 2130SLP (Single Channel) RAID controller KISS is always a good idea. I'm not sure whether to go RAID 1 or RAID 10. I like the idea of spreading the disk i/o out and trying to minimize the impact of disks on the db performance. One the otherhand it's new territory for me so I don't know all of the ins and outs, which is a little disconcerting. Part of my motivation for chopping things up is that I'm interested in having some control over where MySQL puts stuff. I'd also like to find a way to regain disk space. Right now we have all of our data being written to ibdata1 so we have this massive file and never shrinks. It's now 27GBs. We have about 12GB of data so there a huge amount of wasted space. Isn't it true that by having each table in it's own idb file that you can recover disk space when records are deleted? I know that if you have a monolithic ibdata1 file that even dropping a database won't recover disk space. That's really what I want to avoid. Thanks again for sharing your ideas. Cheers, Tripp --- Dan Buettner [EMAIL PROTECTED] wrote: Tripp, YMMV, but I'm a firm believer that keeping things as simple as possible pays big dividends. I believe the advice to spread out IO tasks among different disks is good advice, when dealing with direct-attached disks you deal with directly (i.e. not part of a RAID). The setup and maintenance is not inconsiderable, however, and performance will lag behind an array no matter what you do. If you're building a high-performance server, you may well want to consider moving away from individual disks and using hardware RAID for performance and redundancy. Once you're on such a device, you need to spend less/no time worrying about directing data to individual disks, because all your data is spread among mutiple disk spindles and can also use RAM caching in the device. You also likely have the option of dedicating a group of disks within the device for logging, while still utilizing all that hardware RAID has to offer. At my last job, we used an Apple XServe RAID to host our MySQL data, and performance was astounding. We also used Sun 3310 arrays to host our Sybase data, and performance was likewise astounding, though setup and administration was more complex than the Apple (you had more options too though). I say astounding considering what these advanced little boxes cost, which is just several thousand dollars. The Apple box has been certified on Mac OS X, Windows, Linux and Netware, so it's pretty versatile. There are also PCI card solutions that let you create a hardware RAID within your server box of course - ATA, SATA, and SCSI. Lots of options to choose from so you can select what fits your needs and budget. If you're wanting to build a high-availability setup, two server boxes with one Apple RAID, each using half the array, could be a very good setup, insulating you from hardware and software disasters. Expense is relative - I'd estimate you'd be looking at $25K for such a setup, easily more depending on your server boxes. You can do the same thing with many SCSI arrays (Sun 3310/3320s included) with some advanced setup work. Hope this helps, Dan On 8/1/06, Tripp Bishop [EMAIL PROTECTED] wrote: Howdy all, We're looking at building a new database server and I'm looking into strategies for optimizing disk i/o. Bit of background. We will be running a single database on this box under MySQL 5.0.15. All of the tables are INNODB. We have about 130 tables in the db. I've read that it's a good idea to have the innodb log files written out to a seperate physical drive so that those operations don't bog down the rest of the database disk I/O operations. Configuring INNODB to do that looks straightforward. Then there's the data. I know that I can create multiple shared table spaces and locate them on seperate disks but that doesn't seem to give me control over where individual tables' data are written on the disk array. Is there a way using innodb_file_per_table to control where the individual .idb files are located in the disk array? Do I have to use symbolic links to trick innodb or is there a cleaner way?
Re: Monitoring Slow Queries
Though I understand very well that it has been discussed lots of time before but I don't have time to browse through the previous archives and dig out the stuff I need. So, guys, I would be thankful if you could give me your valuable advice that I need right now. I just need to know 1) What parameters I need to set in my.cnf to log slow queries so that they stick out conspicuously and get noticed, and 2) How I can find out from the log that MySQL creates as a result of 1) as to which queries are running slow. mysqlsla is kind of handy... http://hackmysql.com/mysqlsla mysqlsla analyzes general, slow, and raw MySQL statement logs. Formerly called mysqlprofile, the new name reflects what the script really does: combined MySQL Statement Log Analysis. mysqlsla can read multiple MySQL general and slow logs (and logs containing raw SQL statements), combine them, then run various analyses on all the queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unexpected trigger behavior on trigger
I have two databases that effect each other when triggers get excecuted. There is a schedules database that updates registration database. The problem I have is with the enrolled, attended, waitlisted, completed, cancelled, etc. booleans values. The registration db has triggers on it that enforce certain logical rules to be enforced to ensure that logical registration statuses get correctly (Ex. the user of the database should be enrolled=true if cancelled=true) The problem I have is that, ON schedule UPDATEs, the registration data gets defaulted back to enrolled = true even if pre-existing regitration data in the db is already set to say attend, or completed status. I will paste the two triggers below if you might just notic something wrong with the logic causing this unexpected behavior when the trigger defaults pre-existing registration data to match the enrolled state: CREATE TRIGGER trigger_on_schedule_updates AFTER UPDATE ON schedules FOR EACH ROW BEGIN UPDATE registration_and_attendance SET class_id = new.class_id, start_date = new.start_date, end_date = new.end_date WHERE schedule_id = new.id; END; CREATE TRIGGER trigger_registration_and_attendance_before_update BEFORE UPDATE ON registration_and_attendance FOR EACH ROW BEGIN IF (new.enrolled = true) THEN SET new.attended = false; SET new.completed = false; SET new.waitlisted = false; SET new.cancelled = false; END IF; IF (new.attended = true) THEN SET new.enrolled = true; SET new.waitlisted = false; SET new.completed = false; SET new.cancelled = false; END IF; IF (new.completed = true) THEN SET new.enrolled = true; SET new.attended = true; SET new.waitlisted = false; SET new.cancelled = false; END IF; IF (new.waitlisted = true) THEN SET new.enrolled = false; SET new.attended = false; SET new.completed = false; SET new.cancelled = false; END IF; IF (new.cancelled = true) THEN SET new.enrolled = false; SET new.attended = false; SET new.completed = false; SET new.waitlisted = false; SET new.overflow_registrant = false; END IF; END; Ferindo -- justferindo
RE: Check out this Free software I found to document your IT infrastructure
You know this might be a little bit more convincing if you gave the name of the product and a little bit more personal reason why you recommended it other than check out brand x product I vote this is spam. -Original Message- From: itguy321 [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 1:06 PM To: mysql@lists.mysql.com Subject: Check out this Free software I found to document your IT infrastructure Hey guys, I just found this great free software that lets you document your entire IT infrastructure. In my opinion it's the best software out there and the fact that it is free is just an added bonus. Just thought I would let you guys know if you want to give it a shot it’s working out great for us. http://www.ecora.com/ecora/products/documentor.asp -- View this message in context: http://www.nabble.com/Check-out-this-Free-software-I-found-to-document-your-IT-infrastructure-tf2041675.html#a5620241 Sent from the MySQL - General forum at Nabble.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: Check out this Free software I found to document your IT infrastructure
Mee too. John Meyer [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED]@gmail.com... You know this might be a little bit more convincing if you gave the name of the product and a little bit more personal reason why you recommended it other than check out brand x product I vote this is spam. -Original Message- From: itguy321 [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 1:06 PM To: mysql@lists.mysql.com Subject: Check out this Free software I found to document your IT infrastructure Hey guys, I just found this great free software that lets you document your entire IT infrastructure. In my opinion it's the best software out there and the fact that it is free is just an added bonus. Just thought I would let you guys know if you want to give it a shot it's working out great for us. http://www.ecora.com/ecora/products/documentor.asp -- View this message in context: http://www.nabble.com/Check-out-this-Free-software-I-found-to-document-your-IT-infrastructure-tf2041675.html#a5620241 Sent from the MySQL - General forum at Nabble.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: Is this query possible?
I'm not sure why you split out track, track is really kind of an attribute of a song. Especially since you have artist and album with the song. Wouldn't album be the same as cd title? I'm not quite getting the logic of your schema. It would have been helpful if you provided your current query. Anyway, you want to find out what CDs contain a particular song. So start your query out by finding the song, then you need to find what tracks it's associated with, then what cd those tracks are associate with. Something like this should work: SELECT cd.title,song.title,track FROM song JOIN track ON song.id=song_id AND song.id='X' JOIN cd ON track.cd_id=cd.id Same query, worded slightly different: SELECT cd.title,song.title,track FROM song JOIN track ON song.id=song_id JOIN cd ON track.cd_id=cd.id WHERE song.id='X' - Original Message - From: Tanner Postert [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 01, 2006 8:21 PM Subject: Is this query possible? ok, here is the schema that I am working with: CREATE TABLE `cd` ( `id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `title` varchar(100) NOT NULL, `description` text NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM CREATE TABLE `song` ( `id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `title` varchar(50) NOT NULL, `artist` varchar(50) NULL, `album` varchar(50) NULL, `featuring` varchar(50) NULL, `length` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM CREATE TABLE `track` ( `id` int(10) unsigned NOT NULL auto_increment, `song_id` int(10) unsigned NOT NULL, `cd_id` int(10) unsigned NOT NULL, `track` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cd_id` (`cd_id`,`track`), UNIQUE KEY `song_id` (`song_id`,`cd_id`) ) ENGINE=MyISAM the query now, is pulling back all the cd table data, as well as the total length of the songs that are tracks on that CD, as well as the number of tracks. that query is working fine. my goal now, is to pull the same data, but only for CDs that contain a specific track. but if i add where song.id = 'X' the the count only returns 1 and the sum only returns the length for that 1 song. I'd like the full length and track count, but only for CDs that contain a specific song. SELECT cd.*, count(track.track) as tracks, sum(song.length) as length from cd LEFT JOIN (track, song) on (track.cd_id = cd.id and track.song_id = song.id) GROUP BY cd.id ORDER BY dt DESC LIMIT 0,1 thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
logic/db question
hi... i have a tbl fooTBL name parentID ID so a name can have might have a parentID, as well as an ID. 'name's are associated with other 'name's via the parentID. in other words, if a name's parentID == a name's ID, name1 is the parent of name2. ie nameparentIDID stanford'' 1 fall-06 1 2 spring-061 3 summer-061 4 acct 2 5 biol 2 6 math 2 7 acct 3 8 biol 3 9 math 3 10 acct 4 11 biol 4 12 math 4 13 stanford'' 14 fall-06 14 15 spring-0614 16 summer-0614 17 acct 15 18 biol 15 19 math 15 20 etc my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? searching google gives some insight into how to handle recursion/tree issues. i haven't as of yet come across anything that gets me to where i need to be. can this be handled with only a single table? i also need the select/delete/insert queries to be reasonably fast.. i had done this awhile ago.. but can't recall how i did it.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is this query possible?
I've dealt with this in terms of Books and Titles. Those two are separate: one title can have many book editions published in it. Also, you can have a book with multiple titles (anthology, for instance). I suppose it is possible for album not to be the same as cd title, particularly if you have old vinyl albums around that you want to sell. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 2:13 PM To: Tanner Postert; mysql@lists.mysql.com Subject: Re: Is this query possible? I'm not sure why you split out track, track is really kind of an attribute of a song. Especially since you have artist and album with the song. Wouldn't album be the same as cd title? I'm not quite getting the logic of your schema. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check out this Free software I found to document your IT infrastructure
itguy321 wrote: (garbage snipped) http://www.ecora.com/ecora/products/documentor.asp Full original spam source at end, for the benefit of our CCs. Reports sent, omitting the MySQL hosts obviously. Ignoring the bogus yahoo address, the actual sender and the spamvertised host have been sent spam reports, as a precursor to blacklisting. The sender, meanwhile, is using a private listing service to hide behind; this service is also now contacted about your spamming activity, sirrah. If they are a respectable service, you will get the boot; if they are not, i expect they shall shortly be blacklisted as well. Have a *great* day. Tracking link: http://www.nabble.com/check-out-this-free-software-i-found-to-document-your-it-infrastructure-tf2041675.html#a5620241 No recent reports, no history available Resolves to 72.21.53.35 Routing details for 72.21.53.35 http://www.spamcop.net/sc?action=showroute;ip=72.21.53.35;typecodes=17 [refresh/show] http://www.spamcop.net/sc?action=rcache;ip=72.21.53.35 Cached whois for 72.21.53.35 : [EMAIL PROTECTED] *Domain Name:* ECORA.COM *Administrative Contact :* Bakman, Alex [EMAIL PROTECTED] 2 International Drive Suite 150 Portsmouth, NH 03801-3149 US Phone: (603) 436-1616 Fax: (603) 436-1604 *Technical Contact :* IT, Ecora [EMAIL PROTECTED] 2 International Drive Portsmouth, NH 03801 US Phone: 603-334-3100 - Domain Name.. nabble.com Creation Date 2004-12-28 Registration Date 2004-12-28 Expiry Date.. 2006-12-28 Organisation Name Weizhen Lin Organisation Address. P O Box 99800 Organisation Address. Organisation Address. EmeryVille Organisation Address. 94662 Organisation Address. CA Organisation Address. US Admin Name... PrivateRegContact Admin Admin Address P O Box 99800 Admin Address Admin Address EmeryVille Admin Address 94662 Admin Address CA Admin Address US Admin Email.. [EMAIL PROTECTED] Admin Phone.. +1.5105952002 Admin Fax Tech Name PrivateRegContact TECH Tech Address. P O Box 99800 Tech Address. Tech Address. EmeryVille Tech Address. 94662 Tech Address. CA Tech Address. US Tech Email... [EMAIL PROTECTED] Tech Phone... +1.5105952002 Tech Fax. Name Server.. dns1.nabble.com Name Server.. dns2.nabble.com original post source: 8 - Return-path: [EMAIL PROTECTED] Envelope-to: [EMAIL PROTECTED] Received: from lists2.mysql.com ([213.136.52.31]:59977 helo=lists.mysql.com) by dirtybill.solutionsforprogress.com with smtp (Exim 4.50) id 1G8MOg-0006lJ-8Z for [EMAIL PROTECTED]; Wed, 02 Aug 2006 15:28:54 -0400 Received: (qmail 19122 invoked by uid 510); 2 Aug 2006 19:25:11 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/200528 Delivered-To: mailing list mysql@lists.mysql.com Received: (qmail 19100 invoked by uid 509); 2 Aug 2006 19:25:11 - Received-SPF: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates 72.21.53.35 as permitted sender) Message-ID: [EMAIL PROTECTED] Date: Wed, 2 Aug 2006 12:06:10 -0700 (PDT) From: itguy321 [EMAIL PROTECTED] To: mysql@lists.mysql.com MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Nabble-Sender: [EMAIL PROTECTED] X-Nabble-From: itguy321 [EMAIL PROTECTED] X-SA-Exim-Connect-IP: 213.136.52.31 X-SA-Exim-Mail-From: [EMAIL PROTECTED] Subject: Check out this Free software I found to document your IT infrastructure X-Spam-Checker-Version: SpamAssassin 3.0.3 (2005-04-27) on dirtybill.solutionsforprogress.com X-Spam-Level: X-Spam-Status: No, score=-2.6 required=5.0 tests=BAYES_00,MIME_QP_LONG_LINE autolearn=ham version=3.0.3 X-SA-Exim-Version: 4.2 (built Thu, 03 Mar 2005 10:44:12 +0100) X-SA-Exim-Scanned: Yes (on dirtybill.solutionsforprogress.com) Hey guys, I just found this great free software that lets you document your entire IT infrastructure. In my opinion it's the best software out there and the fact that it is free is just an added bonus. Just thought I would let you guys know if you want to give it a shot it=E2=80=99s working out gr= eat for us. http://www.ecora.com/ecora/products/documentor.asp --=20 View this message in context: http://www.nabble.com/Check-out-this-Free-sof= tware-I-found-to-document-your-IT-infrastructure-tf2041675.html#a5620241 Sent from the MySQL - General forum at Nabble.com. - 8 - -- MySQL General Mailing List For list archives:
Re: logic/db question
Bruce, my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? This is an edge list tree model, ID being the child node, parentID being the parent node, the row denoting the edge between ID and parentID. Unless you know in advance how many levels there are, you need an sproc to retrieve subtrees.There is an example with discussion in Listing 7 at http://localhost/artful/mysqlbook/sampler/mysqled1ch20.html. PB - bruce wrote: hi... i have a tbl fooTBL name parentID ID so a name can have might have a parentID, as well as an ID. 'name's are associated with other 'name's via the parentID. in other words, if a name's parentID == a name's ID, name1 is the parent of name2. ie nameparentIDID stanford'' 1 fall-06 1 2 spring-061 3 summer-061 4 acct 2 5 biol 2 6 math 2 7 acct 3 8 biol 3 9 math 3 10 acct 4 11 biol 4 12 math 4 13 stanford'' 14 fall-06 14 15 spring-0614 16 summer-0614 17 acct 15 18 biol 15 19 math 15 20 etc my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? searching google gives some insight into how to handle recursion/tree issues. i haven't as of yet come across anything that gets me to where i need to be. can this be handled with only a single table? i also need the select/delete/insert queries to be reasonably fast.. i had done this awhile ago.. but can't recall how i did it.. thanks -bruce -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: logic/db question
hi peter... tried to get to the link...saw that it's 'localhost'!! what's the real/actual url... -bruce -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 2:15 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: logic/db question Bruce, my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? This is an edge list tree model, ID being the child node, parentID being the parent node, the row denoting the edge between ID and parentID. Unless you know in advance how many levels there are, you need an sproc to retrieve subtrees.There is an example with discussion in Listing 7 at http://localhost/artful/mysqlbook/sampler/mysqled1ch20.html. PB - bruce wrote: hi... i have a tbl fooTBL name parentID ID so a name can have might have a parentID, as well as an ID. 'name's are associated with other 'name's via the parentID. in other words, if a name's parentID == a name's ID, name1 is the parent of name2. ie nameparentIDID stanford'' 1 fall-06 1 2 spring-061 3 summer-061 4 acct 2 5 biol 2 6 math 2 7 acct 3 8 biol 3 9 math 3 10 acct 4 11 biol 4 12 math 4 13 stanford'' 14 fall-06 14 15 spring-0614 16 summer-0614 17 acct 15 18 biol 15 19 math 15 20 etc my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? searching google gives some insight into how to handle recursion/tree issues. i haven't as of yet come across anything that gets me to where i need to be. can this be handled with only a single table? i also need the select/delete/insert queries to be reasonably fast.. i had done this awhile ago.. but can't recall how i did it.. thanks -bruce -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006 -- 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: Spreading Database across multiple disks
RAID 10 = good choice. I've worked a lot more with MyISAM, where OPTIMIZE TABLE does lock it for the duration. I note that for InnoDB, OPTIMIZE TABLE is mapped to ALTER TABLE, and so I expect it will be locked for the duration as well. Perhaps someone else can confirm - all my InnoDB tables right now are too small to run such a test on. Speed - the OPTIMIZE TABLE process, for tables with millions of rows, is not quick if a lot of deletes or updates have been done. Inserts generally have less effect. If the data is static then it is very quick after the initial optimize. For tables with up to 30 million records like what you have, I would think you'd want to plan middle-of-the-night maintenance periods. You might also look at your table structure and consider MERGE or ARCHIVE tables to speed up operations - just a thought. Dan On 8/2/06, Tripp Bishop [EMAIL PROTECTED] wrote: Yeah, after thinking and reading a little more I decided that RAID 10 was worth it. Thanks for the OPTIMIZE table hint. I think that we'll definitely want to go that route. In your experience how fast it the opt table process? We have a couple of medium size tables (1 million to 30 million rows) that have data inserted and removed pretty regularly. Does OPTIMIZE TABLE lock the table while it's processing? Cheers, Tripp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing a join
select g.GROUP_NAME, count(mg.MEM_ID) as NUMBER_OF_MEMBERS from GROUPS g left join MEM_GRO mg using(GRO_ID) group by g.GRO_ID John Meyer wrote: I have two tables: MEMBERS: MEM_ID ... GROUPS: GRO_ID: ... And one joiner MEM_GRO: MEM_ID, GRO_ID I want to print out a list like this GROUP_NAME, NUMBER_OF_MEMBERS Even when the number of members is 0, how do I do that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Doing a join
Yeah, I just figured it out ten minutes ago, one of those stupid little oversites on my part. -Original Message- From: Martin Jespersen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 3:40 PM To: John Meyer Cc: mysql@lists.mysql.com Subject: Re: Doing a join select g.GROUP_NAME, count(mg.MEM_ID) as NUMBER_OF_MEMBERS from GROUPS g left join MEM_GRO mg using(GRO_ID) group by g.GRO_ID John Meyer wrote: I have two tables: MEMBERS: MEM_ID ... GROUPS: GRO_ID: ... And one joiner MEM_GRO: MEM_ID, GRO_ID I want to print out a list like this GROUP_NAME, NUMBER_OF_MEMBERS Even when the number of members is 0, how do I do that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: logic/db question
Bruce tried to get to the link...saw that it's 'localhost'!! Sorry! http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html PB - bruce wrote: hi peter... tried to get to the link...saw that it's 'localhost'!! what's the real/actual url... -bruce -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 02, 2006 2:15 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: logic/db question Bruce, my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? This is an edge list tree model, ID being the child node, parentID being the parent node, the row denoting the edge between ID and parentID. Unless you know in advance how many levels there are, you need an sproc to retrieve subtrees.There is an example with discussion in Listing 7 at http://localhost/artful/mysqlbook/sampler/mysqled1ch20.html. PB - bruce wrote: hi... i have a tbl fooTBL name parentID ID so a name can have might have a parentID, as well as an ID. 'name's are associated with other 'name's via the parentID. in other words, if a name's parentID == a name's ID, name1 is the parent of name2. ie name parentID ID stanford '' 1 fall-06 1 2 spring-06 1 3 summer-06 1 4 acct 2 5 biol 2 6 math 2 7 acct 3 8 biol 3 9 math 3 10 acct 4 11 biol 4 12 math 4 13 stanford '' 14 fall-06 14 15 spring-06 14 16 summer-06 14 17 acct 15 18 biol 15 19 math 15 20 etc my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? searching google gives some insight into how to handle recursion/tree issues. i haven't as of yet come across anything that gets me to where i need to be. can this be handled with only a single table? i also need the select/delete/insert queries to be reasonably fast.. i had done this awhile ago.. but can't recall how i did it.. thanks -bruce -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULL TEXT SEARCH ALTERNATIVES...
Hi! I'm getting a lot of pushback on using mysql for full-text searching on over 30,000,000 documents. It's starting to slow down when using more than 10-15 keywords. Is there an alternative anyone is using? I don't want to replace the database, but I do need to speed up the keyword search. Any ideas? Thanks in advance! -- Avi
upgrading mysql...
hi.. i have FC3, with 4.1.13, i also have FC4 with 4.1.20. however, i can't seem to find 5.0.x RPMs for FC3/4. do i have to go ahead and build this from source for the FC3/4 boxes that i have... thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check out this Free software I found to document your IT infrastruct
Rob Munsch, I am a member of the Nabble project. This is regarding a bad post by user itguy321. Just want to explain a few things. Nabble is a free mailing list archive/gateway that works like Gmane. Users can browse, search, and post via Nabble's web interface and the post will be forwarded to the mailing list as an email. A user will need to register with Nabble first, then he will need to subscribe to the mailing list to become a subscriber, only then can he post to that mailing list. You seem to be holding Nabble responsible for this bad post. Is it fair? Is it fair to blacklist Gmail just because a bad user sent a spam? A mailing list usually has a moderation mechanism to deal with bad posts. If you are a mailing list owner, you can simply ban that user. Nabble hates spam as much as you do. We are currently monitoring this user and we will take necessary action just like a mailing list owner will do. Feel free to send me a private email if you have more concerns. Regards, Will L Nabble.com Rob Munsch wrote: itguy321 wrote: (garbage snipped) http://www.ecora.com/ecora/products/documentor.asp Full original spam source at end, for the benefit of our CCs. Reports sent, omitting the MySQL hosts obviously. Ignoring the bogus yahoo address, the actual sender and the spamvertised host have been sent spam reports, as a precursor to blacklisting. The sender, meanwhile, is using a private listing service to hide behind; this service is also now contacted about your spamming activity, sirrah. If they are a respectable service, you will get the boot; if they are not, i expect they shall shortly be blacklisted as well. Have a *great* day. Tracking link: http://www.nabble.com/check-out-this-free-software-i-found-to-document-your-it-infrastructure-tf2041675.html#a5620241 No recent reports, no history available Resolves to 72.21.53.35 Routing details for 72.21.53.35 http://www.spamcop.net/sc?action=showroute;ip=72.21.53.35;typecodes=17 [refresh/show] http://www.spamcop.net/sc?action=rcache;ip=72.21.53.35 Cached whois for 72.21.53.35 : [EMAIL PROTECTED] *Domain Name:* ECORA.COM *Administrative Contact :* Bakman, Alex [EMAIL PROTECTED] 2 International Drive Suite 150 Portsmouth, NH 03801-3149 US Phone: (603) 436-1616 Fax: (603) 436-1604 *Technical Contact :* IT, Ecora [EMAIL PROTECTED] 2 International Drive Portsmouth, NH 03801 US Phone: 603-334-3100 - Domain Name.. nabble.com Creation Date 2004-12-28 Registration Date 2004-12-28 Expiry Date.. 2006-12-28 Organisation Name Weizhen Lin Organisation Address. P O Box 99800 Organisation Address. Organisation Address. EmeryVille Organisation Address. 94662 Organisation Address. CA Organisation Address. US Admin Name... PrivateRegContact Admin Admin Address P O Box 99800 Admin Address Admin Address EmeryVille Admin Address 94662 Admin Address CA Admin Address US Admin Email.. [EMAIL PROTECTED] Admin Phone.. +1.5105952002 Admin Fax Tech Name PrivateRegContact TECH Tech Address. P O Box 99800 Tech Address. Tech Address. EmeryVille Tech Address. 94662 Tech Address. CA Tech Address. US Tech Email... [EMAIL PROTECTED] Tech Phone... +1.5105952002 Tech Fax. Name Server.. dns1.nabble.com Name Server.. dns2.nabble.com original post source: 8 - Return-path: [EMAIL PROTECTED] Envelope-to: [EMAIL PROTECTED] Received: from lists2.mysql.com ([213.136.52.31]:59977 helo=lists.mysql.com) by dirtybill.solutionsforprogress.com with smtp (Exim 4.50) id 1G8MOg-0006lJ-8Z for [EMAIL PROTECTED]; Wed, 02 Aug 2006 15:28:54 -0400 Received: (qmail 19122 invoked by uid 510); 2 Aug 2006 19:25:11 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/200528 Delivered-To: mailing list mysql@lists.mysql.com Received: (qmail 19100 invoked by uid 509); 2 Aug 2006 19:25:11 - Received-SPF: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates 72.21.53.35 as permitted sender) Message-ID: [EMAIL PROTECTED] Date: Wed, 2 Aug 2006 12:06:10 -0700 (PDT) From: itguy321 [EMAIL PROTECTED] To: mysql@lists.mysql.com MIME-Version: 1.0 Content-Type:
Re: Query Help for Loosely Couple Properties
On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: The question is, how do I query this? Say I want all records from table T whose COLOR property value is ORANGE. The only thing I can come up with (and I'm no SQL expert and this looks wrong to me) is the following: SELECT * FROM T WHERE ( T.ID NOT IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringValue.TYPE_ID = COLOR ) AND EXISTS ( SELECT * FROM StringType WHERE StringType.DEF_VAL LIKE Orange AND StringType.ID = COLOR ) ) OR ( T.ID IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringVal.VAL LIKE Orange AND StringVal.TYPE_ID = COLOR ) ) SELECT * FROM T LEFT JOIN StringVal V ON T.ID = V.REF_ID INNER JOIN StringType ST ON V.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE V.REF_ID IS NULL OR V.VAL = Orange; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing a join
On Wed, 2006-08-02 at 11:13 -0600, John Meyer wrote: I have two tables: MEMBERS: MEM_ID ... GROUPS: GRO_ID: ... And one joiner MEM_GRO: MEM_ID, GRO_ID I want to print out a list like this GROUP_NAME, NUMBER_OF_MEMBERS Even when the number of members is 0, how do I do that? SELECT G.NAME AS GROUP_NAME , COUNT(*) AS NUMBER_OF_MEMBERS FROM GROUPS G LEFT JOIN MEM_GRO AS M ON G.GRO_ID = M.GRO_ID GROUP BY G.GRO_ID; By the way, when you use ALLCAPS for everything, it makes it very difficult to pick out SQL keywords, and MAKES IT SEEM LIKE YOU ARE SHOUTING. -jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL TEXT SEARCH ALTERNATIVES...
You can tune the fulltext search in a few ways using the config, try read up on the various server variables that has to do with fulltext indexing. Other than that there is always the option of upgrading the hardware :) [EMAIL PROTECTED] wrote: Hi! I'm getting a lot of pushback on using mysql for full-text searching on over 30,000,000 documents. It's starting to slow down when using more than 10-15 keywords. Is there an alternative anyone is using? I don't want to replace the database, but I do need to speed up the keyword search. Any ideas? Thanks in advance! -- Avi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Doing a join
Sorry, but that's how I was normally trained to use SQL and to name variables. I know netiquette, it's just how I was trained on the system. -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 11:35 AM To: John Meyer Cc: mysql@lists.mysql.com Subject: Re: Doing a join On Wed, 2006-08-02 at 11:13 -0600, John Meyer wrote: I have two tables: MEMBERS: MEM_ID ... GROUPS: GRO_ID: ... And one joiner MEM_GRO: MEM_ID, GRO_ID I want to print out a list like this GROUP_NAME, NUMBER_OF_MEMBERS Even when the number of members is 0, how do I do that? SELECT G.NAME AS GROUP_NAME , COUNT(*) AS NUMBER_OF_MEMBERS FROM GROUPS G LEFT JOIN MEM_GRO AS M ON G.GRO_ID = M.GRO_ID GROUP BY G.GRO_ID; By the way, when you use ALLCAPS for everything, it makes it very difficult to pick out SQL keywords, and MAKES IT SEEM LIKE YOU ARE SHOUTING. -jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple single column indexes
Hi, up to now my idea of how MySQL uses indexes was, that when I have a WHERE clause with several fields and I have an index on each field, MySQL chooses one index (from which it thinks that it will return the fewest rows when matched again the condition) and uses this index to select a couple of rows based on that one index. Then it scans all these rows and matches every row against the conditions in the WHERE clause, without using any other index. But now I observed the following: When I ran a simple query (one table only, no joins) with a WHERE clause with several columns, it took more than one minute (6 rows, 500 MB). Then I added indexes and ran the query after adding each index. There was almost no change in execution time. But when I added the last index (so all columns that were in the WHERE clause had indexes) the query suddenly took under one second. Then I removed all the indexed, one by one, but I can't make the query take more than one second again. Even when I restart the mysql daemon, it still takes only a few milliseconds. So I have two questions: 1. What happended? 2. Does it really make sense to have single indexes per column when the columns are used together in one WHERE clause? Best regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple single column indexes
It depends what you need and how your data looks. Say you have a table with 4 columns: col1 has 1000 dictinct values col2 has 1 dictinct values col3 has 100 dictinct values col4 has 10 dictinct values In this case: select col1,col2 from tbl where col1=... and col2=... Having a single index based on col1,col2 col3 is the best option, since no actual table lookup is needed if you have that index. in this case: select * from tbl where col3=... and col1=... and col2=... it is best to have a seperate index for col1, col2 and col3 - in this case the column with the smallest amount of distinct values should be first, thus we start with col3. The result that needs to be returned (*) cover columns that are outside the where clause, so having an index on all the fields in the where clause makes no sense, since a table lookup is needed anyway. In this case having an index of col1,2,3 combined gives us an index with a much larger cardinality than the sum of the cardinalities in 3 seperate indexes (one for each column) and thus a slower index lookup. In some cases, you might find a combined index faster even in this case, but in my experience that is quite rare. But... as i said in the beginning, it really does depend on your data and the queries you use - always use explain to check how mysql optimizes your queries, and run tests like the ones you already did to see what works best... sometimes mysql surprises the heck out of me even tho i've been using it since 95 ;) André Hänsel wrote: 2. Does it really make sense to have single indexes per column when the columns are used together in one WHERE clause? Best regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join with additional table, stumped
Forgive the mess I present you, this is a strangely done site with a even
stranger structure and methodology that I am trying to work with.
The basic idea is one website has multiple websites in it. So if you place
an order with website A, orders_A is where the data is stored, if you place
an order with website B, orders_B is where the data is stored. The site
changes from A to B based on the url. Anyway...
SELECT o.id, u.industry,
u.email, u.b_first_name, u.b_last_name,
t.created
FROM orders_npfd AS o
INNER JOIN users AS u
ON (o.user_id = u.id)
INNER JOIN transactions as t
ON (t.order_id = o.id)
WHERE t.type in ('first_charge', 'recurring')
ORDER BY t.created DESC;
This basically gives me a list of all orders that I want to see from the
orders_npfd table, works as I need it to. Now, I need to add in a second
table, for the orders from the other table. Table structure is more or less
the same, at least, the data I am selecting. So the second table is
orders_npfs - note the 's'
How can I add that in so I will get results out of that table as well?
Second problem, each order table of course gets an id, or orderId, in the
above example, it is o.id and linked to t.order_id. The problem is, o.id is
autoinc PK and will/can overlap with the id's from the either of the orders
table.
However, users u.id will never overlap, nor share table data, so I think I
can use that in my condition to make sure there is no pollution of orders?
If I use a join in the above to get the second table data in there, I would
have to add in more x.foo items to my select, which really will not help me.
I am sure this is pretty confusing, if anyone has any suggestions, aside
from rethinking the design entirely, I would most appreciate it.
Maybe select the contents of both tables into a tmp table, and use that as
my join table above?
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL TEXT SEARCH ALTERNATIVES...
At 05:27 PM 8/2/2006, you wrote: Hi! I'm getting a lot of pushback on using mysql for full-text searching on over 30,000,000 documents. It's starting to slow down when using more than 10-15 keywords. Is there an alternative anyone is using? I don't want to replace the database, but I do need to speed up the keyword search. Any ideas? Thanks in advance! -- Avi Avi, Take a look at Sphinx from http://www.shodan.ru/projects/sphinx/. It's fast and it's free. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relay Log Lost on Slave
Yes, I did so... I will check the other configuration. Thanks, Kenji On 8/2/06, Dilipkumar [EMAIL PROTECTED] wrote: Hi, First reset slave and then change master to script run it. Thanks Regards Dilipkumar - Original Message - From: Kenji HIROHAMA [EMAIL PROTECTED] To: Dilipkumar [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 3:29 PM Subject: Re: Relay Log Lost on Slave Hi Dilipkumar, I checked the output of show slave status, and memorize; RELAY_MASTER_LOG_FILE, and EXEC_MASTER_LOG_POS. Then, change master to master_log_file='xx, master_log_pos=xx; However, still I get the same error message. Umm. Kenji On 8/2/06, Dilipkumar [EMAIL PROTECTED] wrote: Hi, If you relay log is lost try out the this :- Run the Change Master Position script, See the log output from where did the replication stopped. So you can start your replication. Thanks Regards Dilipkumar - Original Message - From: Kenji HIROHAMA [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 9:36 AM Subject: Relay Log Lost on Slave Hi, Under my replication environment, what should I do if I lose the current relay-log file on slave side? 1. one master and one slave replication is working 2. stop the master and the slave 3. remove the current relay log file manually 4. I can't start replication with start slave command the error message is; ERROR 29 (HY000): File 'xxx-relay-bin.25' not found (Errcode: 2) Should I sync the data manually and start replication from the beginning? Thanks, -- [EMAIL PROTECTED] Kenji Hirohama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- [EMAIL PROTECTED] Kenji Hirohama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- [EMAIL PROTECTED] Kenji Hirohama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is this query possible?
just to clarify to Brent, the songs lists the original artist and album. the CD table is for the information for a NEW mix CD. that CD contains the tracks listed in the tracks table, which point back to the individual songs. the reason the tracks are not listed in the songs table, is because they can be part of multiple CDs. one person could put that same song on tons of different mix CDs as wells as other users using it too, anyways, thanks for the help, i'll let you know how your suggestions faired. On 8/2/06, John Meyer [EMAIL PROTECTED] wrote: I've dealt with this in terms of Books and Titles. Those two are separate: one title can have many book editions published in it. Also, you can have a book with multiple titles (anthology, for instance). I suppose it is possible for album not to be the same as cd title, particularly if you have old vinyl albums around that you want to sell. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 2:13 PM To: Tanner Postert; mysql@lists.mysql.com Subject: Re: Is this query possible? I'm not sure why you split out track, track is really kind of an attribute of a song. Especially since you have artist and album with the song. Wouldn't album be the same as cd title? I'm not quite getting the logic of your schema. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Running Totals?
Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: +---+--+---+--+ | Month | Year | Registrations | Monindex | +---+--+---+--+ | October | 2004 |23 | 200410 | | December | 2004 | 5 | 200412 | | January | 2005 | 9 | 200501 | | February | 2005 |11 | 200502 | | April | 2005 | 2 | 200504 | | May | 2005 |48 | 200505 | | June | 2005 |45 | 200506 | | July | 2005 |10 | 200507 | | August| 2005 |17 | 200508 | | September | 2005 |58 | 200509 | | October | 2005 |97 | 200510 | +---+--+---+--+ The cumulative column would ideally show 23,28,37, etc. Also, if anyone has a better way to keep the different years apart than the 'monindex' column, or at least to suppress displaying it, I'll be really interested. The existing query is: Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex Union Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg where year(DatePaid)=2005 and (amount 0 or Dealer = 'Y') Group by Monindex; Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
Barry It would make life easier if I could also show a column with the cumulative count for each month. Set @cum - 0; Select Monthname(DatePaid) Month, Year(DatePaid) Year, Count(*) as Registrations, Extract(Year_Month from DatePaid) AS Monindex, @cum := @cum + Count(*) AS 'Year to date' >From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex ; PB - Barry Newton wrote: Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: +---+--+---+--+ | Month | Year | Registrations | Monindex | +---+--+---+--+ | October | 2004 | 23 | 200410 | | December | 2004 | 5 | 200412 | | January | 2005 | 9 | 200501 | | February | 2005 | 11 | 200502 | | April | 2005 | 2 | 200504 | | May | 2005 | 48 | 200505 | | June | 2005 | 45 | 200506 | | July | 2005 | 10 | 200507 | | August | 2005 | 17 | 200508 | | September | 2005 | 58 | 200509 | | October | 2005 | 97 | 200510 | +---+--+---+--+ The cumulative column would ideally show 23,28,37, etc. Also, if anyone has a better way to keep the different years apart than the 'monindex' column, or at least to suppress displaying it, I'll be really interested. The existing query is: Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex >From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex Union Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex >From capclave2005reg where year(DatePaid)=2005 and (amount 0 or Dealer = 'Y') Group by Monindex; Barry No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
At 11:10 PM 8/2/2006, Peter Brawley wrote: Barry It would make life easier if I could also show a column with the cumulative count for each month. Set @cum - 0; Select Monthname(DatePaid) Month, Year(DatePaid) Year, Count(*) as Registrations, Extract(Year_Month from DatePaid) AS Monindex, @cum := @cum + Count(*) AS 'Year to date' From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex ; PB Looked promising, but gets me the following, which isn't quite right: +---+--+---+--+--+ | Month | Year | Registrations | Monindex | Year to date | +---+--+---+--+--+ | October | 2004 |23 | 200410 | 23 | | December | 2004 | 5 | 200412 |5 | | January | 2005 | 9 | 200501 | 14 | | February | 2005 |11 | 200502 | 16 | | April | 2005 | 2 | 200504 |7 | | May | 2005 |48 | 200505 | 53 | | June | 2005 |45 | 200506 | 50 | | July | 2005 |10 | 200507 | 15 | | August| 2005 |17 | 200508 | 22 | | September | 2005 |58 | 200509 | 63 | | October | 2005 |97 | 200510 | 102 | +---+--+---+--+--+ Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fatal error
Hi ALL I have taken backup of http://mydomain.com/?q=admin/database through drupal 4.6.3 by selecting all tables and it asked me to save as backup.sql. Now when i run [EMAIL PROTECTED] root]# mysql -u kaushal -h bdc31096e.in.office.aol.com -p drupal /home/kaushal/drupal/backup.sql Enter password: ERROR 1064 at line 55817: You have an error in your SQL syntax near 'br / bFatal error/b: Maximum execution time of 30 seconds exceeded in b' at line 1 [EMAIL PROTECTED] root]# I get the above error Any clue Please let me know if you need more info on this issue Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fatal error
Looks like drupal had an error when doing the backup - mysql is complaining over the fact that your backup.sql file contains a html formated fatal error message. Kaushal Shriyan wrote: Hi ALL I have taken backup of http://mydomain.com/?q=admin/database through drupal 4.6.3 by selecting all tables and it asked me to save as backup.sql. Now when i run [EMAIL PROTECTED] root]# mysql -u kaushal -h bdc31096e.in.office.aol.com -p drupal /home/kaushal/drupal/backup.sql Enter password: ERROR 1064 at line 55817: You have an error in your SQL syntax near 'br / bFatal error/b: Maximum execution time of 30 seconds exceeded in b' at line 1 [EMAIL PROTECTED] root]# I get the above error Any clue Please let me know if you need more info on this issue Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
