Re: too many connections
Joeffrey Betita wrote: hello i just installed mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz, httpd-2.0.54.tar.gz, php-5.0.4.tar.gz etc. on a Intel(R) Pentium(R) 4 CPU 2.40GHz with 1GB RAM this is just a temporary until we buy a new high end server. my-large.cnf is the configuration in the /etc/my.cnf CentOS release 4.0 (Final) is the linux distribution. what is the ideal configuration so that 20,000 user does not encounter the too many connections error when they browse our website. thank you very much. Do you work for Intel? Or do you just like putting (R) after every Registered Trademark(R)? Anyway, on to your problem... Do you expect 20,000 users to all hit your website within a second or so of each other? Somehow I doubt that. The too many connections error refers to how many connections can be open *to the MySQL server* at once. When someone is just viewing one of your pages, they don't have a connection open to Apache, and therefore your PHP scripts aren't holding a MySQL connection open (unless you use persistent connections to MySQL, which isn't the default for PHP). You will only experience a problem if the number of connections at once is greater than the number set up in my.cnf. For example, if MySQL is set up to handle 1000 connections, then it can handle 1000 people all hitting refresh or entering the URL of your website, all at once. There might be 100,000 people looking at your website, but as long as 1000 of them don't all click a link at once, you're sweet. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Any thoughts/ideas ? There are pros and cons to both ways.(As you pointed out with moving the records to another table) I allways prefer flagging the records. The draw back with flagging the records is that you might sacrifice some speed(depends on the number of records in the table.) If the table does not grow that fast most def just flag the records as deleted. my2c worth -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT EXECUTE in MySQL 4.1
Hi there, For some reason, GRANT EXECUTE is possible on the global level, but not on the database level: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES Has anyone got any idea what EXECUTE should do on a global level in MySQL 4.1? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql database characterset
Hi there, Is the mysql database always in UTF8 characterset for MySQL 4.1 and up? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT EXECUTE in MySQL 4.1
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 09:40 To: mysql@lists.mysql.com Subject: GRANT EXECUTE in MySQL 4.1 Hi there, For some reason, GRANT EXECUTE is possible on the global level, but not on the database level: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES Has anyone got any idea what EXECUTE should do on a global level in MySQL 4.1? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Martin, EXECUTE is specifically for stored procedures.. Whilst the privilege is available in = 4.0.2, it has no effect at all until 5.0.something. This is all documented in the Privileges provided by MySQL manual page I believe.. EXECUTE on a GLOBAL level would, of course, allow the grantee to call any stored procedure. HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database characterset
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 10:43 To: mysql@lists.mysql.com Subject: mysql database characterset Hi there, Is the mysql database always in UTF8 characterset for MySQL 4.1 and up? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Yep.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.7/70 - Release Date: 11/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT EXECUTE in MySQL 4.1
For some reason, GRANT EXECUTE is possible on the global level, but not on the database level: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES Has anyone got any idea what EXECUTE should do on a global level in MySQL 4.1? Martin, EXECUTE is specifically for stored procedures.. Whilst the privilege is available in = 4.0.2, it has no effect at all until 5.0.something. This is all documented in the Privileges provided by MySQL manual page I believe.. Right. I thought it was strange :-) EXECUTE on a GLOBAL level would, of course, allow the grantee to call any stored procedure. Figures. Thanks. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FYI: Background info on visibility of command line arguments
Hi! Some days ago, there was a debate on this list about the visibility of passwords using ps if they were given on the command line. I have just come across this text. While it talks about the process name, the info still applies to command line arguments as well: | 1.13 How do I change the name of my program (as seen by `ps')? | | On BSDish systems, the ps program actually looks into the address | space of the running process to find the current argv[], and displays | that. That enables a program to change its `name' simply by modifying | argv[]. | | On SysVish systems, the command name and usually the first 80 bytes of | the parameters are stored in the process' u-area, and so can't be | directly modified. There may be a system call to change this | (unlikely), but otherwise the only way is to perform an exec(), or | write into kernel memory (dangerous, and only possible if running as | root). | | Some systems (notably Solaris) may have two separate versions of ps, | one in `/usr/bin/ps' with SysV behaviour, and one in `/usr/ucb/ps' | with BSD behaviour. On these systems, if you change argv[], then the | BSD version of ps will reflect the change, and the SysV version won't. | | Check to see if your system has a function setproctitle(). Quote taken from here: http://www.erlenstar.demon.co.uk/unix/faq_2.html#SEC22 So the client code that overwrites the password argument will be executed on all systems, but take effect only on some. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Set Question
Hello, We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to change ft_max_word_len value beyond 254
Hello, I want to index a table using a TEXT value, with length 255. I tried changing ft_max_word_len but each time I check the status of variables, I notice the changes are not taken. It defaults to 254. I am able to change this value to anything below 254. Is there any other way to enforce this ft_max_word_len value to some arbitrary value above 254? The point is, because my index length is being limited to only 254, I am having false misses in my SELECT queries, based on the TEXT index. Bendick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to change ft_max_word_len value beyond 254
-Original Message- From: Bendick Mahleko [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 12:22 To: mysql@lists.mysql.com Subject: how to change ft_max_word_len value beyond 254 Hello, I want to index a table using a TEXT value, with length 255. I tried changing ft_max_word_len but each time I check the status of variables, I notice the changes are not taken. It defaults to 254. I am able to change this value to anything below 254. Is there any other way to enforce this ft_max_word_len value to some arbitrary value above 254? The point is, because my index length is being limited to only 254, I am having false misses in my SELECT queries, based on the TEXT index. Bendick Hi Bendick, Am I missing something here? The ft_max_word_len variable sets the maximum length of any word that fulltext will index, *not* the maximum length of the field that you are indexing. Now, unless you are indexing some scientific data, with for instance some strange, long virus name - I don't know of any word, in the English language at least, that is longer than 254 characters. I recently built a dictionary table for fun, with ~500,000 words from the English language in the table, so I can verify this for you if you want ;) Perhaps your false misses are due to something else, such as ft_min_word_len, or the values being in more than 50% of the rows etc. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.7/70 - Release Date: 11/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Sorry I don't understud this, why it's impossible ? If the PK is auto-generated from MySQL it will have progressive numbers, and it's always possible to force a lower, non-existant number in the PK. Any thoughts/ideas ? We need to track the modification to the records too so the route has been to keep them all in a different, specular databases. If the real table look like this: CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`id`) ); The backup one look like this: CREATE TABLE `users` ( `del__id` int(11) NOT NULL auto_increment, `del__ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `del__flag` char(1) default 'D', `del__note` mediumtext, `id` int(11) NOT NULL auto_increment, `ts` datetime NOT NULL default '-00-00 00:00:00', `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`del__id`) ); That is the first one whit del__* fields added but all indexed removed. Having the same name and similar schema for the two tables make easier have a photo of the database in a defined time slice. Usefull with small, not very often changing databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change ft_max_word_len value beyond 254
Hello Mark, I am indexing scientific data, where each word is potentially more than 255 in length. So the point is, there doesn't seem to be a way to change the maximum word length (via 'ft_max_word_len' - the parameter defining the maximum length of any word as you pointed out) beyond 255. What are my alternatives? Thanks in advance. Bendick Mark Leith wrote: -Original Message- From: Bendick Mahleko [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 12:22 To: mysql@lists.mysql.com Subject: how to change ft_max_word_len value beyond 254 Hello, I want to index a table using a TEXT value, with length 255. I tried changing ft_max_word_len but each time I check the status of variables, I notice the changes are not taken. It defaults to 254. I am able to change this value to anything below 254. Is there any other way to enforce this ft_max_word_len value to some arbitrary value above 254? The point is, because my index length is being limited to only 254, I am having false misses in my SELECT queries, based on the TEXT index. Bendick Hi Bendick, Am I missing something here? The ft_max_word_len variable sets the maximum length of any word that fulltext will index, *not* the maximum length of the field that you are indexing. Now, unless you are indexing some scientific data, with for instance some strange, long virus name - I don't know of any word, in the English language at least, that is longer than 254 characters. I recently built a dictionary table for fun, with ~500,000 words from the English language in the table, so I can verify this for you if you want ;) Perhaps your false misses are due to something else, such as ft_min_word_len, or the values being in more than 50% of the rows etc. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Bastian Balthazar Bux wrote: We need to track the modification to the records too so the route has been to keep them all in a different, specular databases. If the real table look like this: CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`id`) ); The backup one look like this: CREATE TABLE `users` ( `del__id` int(11) NOT NULL auto_increment, `del__ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `del__flag` char(1) default 'D', `del__note` mediumtext, `id` int(11) NOT NULL auto_increment, `ts` datetime NOT NULL default '-00-00 00:00:00', `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`del__id`) ); That is the first one whit del__* fields added but all indexed removed. Having the same name and similar schema for the two tables make easier have a photo of the database in a defined time slice. Usefull with small, not very often changing databases. hi bastian just a thought. rather stay away from auto_increment PK's and rather generate your own PK. i have run into trouble a couple of times using auto_increment when i made backups and restored the data again. The PK changed and i had records in other tables referencing the old PK , but then the PK changed. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
I know it's bad form to reply to yourself but I just found a major mental mistake in my response. See embedded: [EMAIL PROTECTED] wrote on 08/12/2005 12:18:21 AM: Sebastian [EMAIL PROTECTED] wrote on 08/11/2005 01:19:30 PM: well i managed to solve the problem myself, and im no sql genius... i thought i had an index on maps_rating.map which i didn't.. adding an index on it improved the query. i think that is about all the improvement i can get.. but if there is still room for more speed i'd like to know.. Sebastian wrote: Jigal van Hemert wrote: Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query sorry for the lack of info. there are a couple of indexes on maps table, but i am not using them unless i use a where clause. not sure if the format is going to appear correctly on mailing list email, here is the info: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ /NULL/ 626 Using temporary; Using filesort 1 SIMPLE maps_rating ALL /NULL/ /NULL/ /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 site.maps.userid 1 CREATE TABLE `maps` ( `id` int(10) unsigned NOT NULL auto_increment, `mip` smallint(6) NOT NULL default '0', `map` varchar(50) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `filename` varchar(50) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `mip` (`mip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; CREATE TABLE `maps_rating` ( `id` int(10) unsigned NOT NULL auto_increment, `map` int(10) NOT NULL default '0', `rating` smallint(6) NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `ipaddress` varchar(15) NOT NULL default '', `dateline` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; the query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage This is not to solve your problem (you already did that) but to respond to your request for possible additional optimizations. One of the basic principles I try to use when optimizing is to JOIN as little data as possible, even if it takes more than one step. What your original query does is to JOIN three tables then GROUP BY on the resulting combinations of records. If you eliminated all of the duplication from your secondary tables (maps_rating and user) by performing your AVG and COUNT in separate steps, you reduce the amount of data you need to reprocess through the GROUP BY by an order of magnitude. Less data = less time. Here is how I would approach your problem. I would create a temp table (or a static table if you run this often enough) that contains whatever statistics you want (your COUNTs, AVGs, etc.) then join that to the `maps` table to fill in the rest of the columns you wanted in your report. The whole query would resemble something like this: CREATE TEMPORARY TABLE tmpRatings(KEY(map)) SELECT map , avg(rating) as rating , count(id) as votes FROM maps_rating GROUP BY map; This query is wrong, I cut and pasted but forgot to edit...(I must have been WAY too tired to be online) SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN user ON (user.userid = maps.userid) LEFT JOIN tmpRatings ON tmpRatings.map = maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage; What I meant to give was this: SELECT maps.* , tr.rating , tr.votes , user.username FROM maps LEFT JOIN user ON (user.userid = maps.userid) LEFT JOIN tmpRatings ON tmpRatings.map = maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage; Assuming an average of 10 ratings per map, you save at least 10x the processing time in your final query as compared to your original. We did add a little processing to create the statistics table, however the additional overhead is not nearly as much as we saved so the net gain will still be quite noticeable. Please give it a shot and let me know how it compares to your original. Thanks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Again,
Re: how to change ft_max_word_len value beyond 254
Bendick Mahleko [EMAIL PROTECTED] wrote on 08/12/2005 07:48:27 AM: Hello Mark, I am indexing scientific data, where each word is potentially more than 255 in length. So the point is, there doesn't seem to be a way to change the maximum word length (via 'ft_max_word_len' - the parameter defining the maximum length of any word as you pointed out) beyond 255. What are my alternatives? Thanks in advance. Bendick Mark Leith wrote: -Original Message- From: Bendick Mahleko [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 12:22 To: mysql@lists.mysql.com Subject: how to change ft_max_word_len value beyond 254 Hello, I want to index a table using a TEXT value, with length 255. I tried changing ft_max_word_len but each time I check the status of variables, I notice the changes are not taken. It defaults to 254. I am able to change this value to anything below 254. Is there any other way to enforce this ft_max_word_len value to some arbitrary value above 254? The point is, because my index length is being limited to only 254, I am having false misses in my SELECT queries, based on the TEXT index. Bendick Hi Bendick, Am I missing something here? The ft_max_word_len variable sets the maximum length of any word that fulltext will index, *not* the maximum length of the field that you are indexing. Now, unless you are indexing some scientific data, with for instance some strange, long virus name - I don't know of any word, in the English language at least, that is longer than 254 characters. I recently built a dictionary table for fun, with ~500,000 words from the English language in the table, so I can verify this for you if you want ;) Perhaps your false misses are due to something else, such as ft_min_word_len, or the values being in more than 50% of the rows etc. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk With bioinformatics being such a hot topic today, and because you didn't say exactly what kind of long, scientific data you are trying to index an idea occurred to me that you may be storing gene sequences. DNA sequences can be represented as LONG strings of A, C, T, and G but this doesn't leave any word breaks for the index to pick up on. With that in mind, you may be able to substitute any one of those letters with one of the stop letters and enable full-text indexing. Here is a visual example: AGACATATACCCGCGTA A.ACATATACCC.C.TA I substituted a period for all G's in this sequence. I could have used any other punctuation or whitespace character. So long as you never exceed 255 base pair combinations between any two occurrences of the delimiter nucleotide, the FT Index should be able to properly capture the entire sequence. When searching just convert your target nucleotide to your stop character and continue as usual. Could this technique help to reduce the number of false negatives in your application? For instance, you might replace all occurrences of the extremely common amino or methyl in chemical names with a % or $ character. Not only could it help to compress the data but it introduces artificial word breaks into extremely long words without losing any information from the actual data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Locking Issue
Hello. Use SHOW PROCESSLIST and slow-query log to catch the query which locks tables for a long time. Upgrade to 4.1.13. If you use MyISAM, think about moving towards InnoDB. Aaron [EMAIL PROTECTED] wrote: Hi all , I have been experiencing intermittent locking issues with MYSQL. It appears that sometimes a query will lock reliease its lock, and is causing other queries to wait and wait until the connection limit is reached and i am locked out of the database. Has anyone ever had anything like this happen? The setup: Redhat 9.0 , Kernel 2,4,20-8smp mysql-standard-4.1.7-pc-linux-i686-icc-glibc23 MyISAM Tables (And unless InnoDB can support fulltext or some other equivalent , migrating isnt an option at present) ext2fs Our Datbase Activity: We have a somewhat active website. Things run fairly smoothly for the most part , although we do have some slow queries from time to time. We have far more selects than updates , but updates are still reasonably active. Frequently , an update will get locked while a slower query is running. Sometimes we can experience a large backup waiting for a slow query , but typically everything sorts out once the slow query finishes. Rarely , however , a query will be in a locked state and will not let go of its lock. Subsequent updates lock , and subsequent selects lock. Eventually , if the above has happened , the connection table will fill up. We dont have any scripts that explicitly LOCK TABLES , aside from our backup script which uses mysqlhotcopy. Is it possible that the mysqlhotcopy LOCK TABLES could interfere with the locking from the website activity? I apologise for the vagueness of this request , I really dont know what direction would be best to further diagnose this. If you have any advice , it would be greatly appreciated. thanks for your time! Aaron -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT EXECUTE in MySQL 4.1
Hello. EXECUTE is not operational until MySQL 5.0.3. Don't use in 4.1. See: http://dev.mysql.com/doc/mysql/en/grant.html Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, For some reason, GRANT EXECUTE is possible on the global level, but not on the database level: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES Has anyone got any idea what EXECUTE should do on a global level in MySQL 4.1? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a question in SERIALIZABLE transaction isolation level
Hello. SERIALIZABLE is like REPEATABLE READ, but all plain SELECT statements are implicitly converted to SELECT ... LOCK IN SHARE MODE. In REPEATABLE READ statements like SELECT ... LOCK IN SHARE MODE that use a unique index with a unique search condition lock only the index record found, not the gap before it. So, parallel INSERTs are possible and they cause a 'duplicate' error. In the transaction isolation level,Database send some duplicate entry message to me. Why? _ $$ MSN Messenger: http://messenger.msn.com/cn -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Minimial settings on Windows XP AND Linux for MySQL server
I would like to install MySQL 5.0.x beta into two computers and test them. They will run Windows XP Home Edition (of course with massive updates and whatsnot) and Linux (SlackWare 10.0 vanilla). I am familiar with Registry Editor and bash shell scripting for /etc start up directory in technical level. My question here is, how can I set both of them to run ONLY essential utilities necessary to support MySQL with good security settings? And, no I do not mean computer inside a locked room without network support :) I would like to know if anyone had experience and have a resource for it on line? I would expect various opinions about them, and would like to hear 'em. -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Query Help
I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
I am using mysql 4.0.25 with no subquery support : ( Jason Chan [EMAIL PROTECTED] ¦b¶l¥ó news:[EMAIL PROTECTED] ¤¤¼¶¼g... I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? -- 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: Query Help
Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem' and sg.Grade = 'A' The key here is using the aliases for the tables. The linking of the tables comes in the s.StudentID = sg.StudentID statement. I am using mysql 4.0.25 with no subquery support : ( Jason Chan [EMAIL PROTECTED] �b�l�� news:[EMAIL PROTECTED] �g... I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Have a great day, Dan Julson Application Software Engineer API Outsourcing 1355 Mendota Heights Rd. Mendota Heights, MN 55120 651-675-2628 (work) 952-456-2067 (mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA
I want to find out students who have got A in both Maths and Chem
How the SQL look like?
Use a self-join on SubjectGrade, requiring an A in Maths on the left and an A
in Chem on the right, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg1 ON s.StudentID = sg1.StudentID
JOIN SubjectGrade sg2 ON s.StudentID = sg2.StudentID
WHERE sg1.Subject = 'Maths' AND sg1.Grade = 'A'
AND sg2.Subject = 'Chem' AND sg2.Grade = 'A';
Alternatively, use an IN list for the subject, then use a HAVING clause to
limit the results to students with 2 matching rows, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
GROUP BY s.StudentID
HAVING COUNT(*) = 2;
Change GROUP BY s.StudentID to GROUP BY s.StudentName if you want the
results in alphabetical order by name instead of in ID order.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem' and sg.Grade = 'A' Take a close look at the WHERE clause: sg.Subject = 'Maths' and sg.Subject = 'Chem' will never return a result - how could the subject be two different things at the same time? Here's one way that should work: SELECT s.StudentID, StudentName FROM Student s JOIN StudentGrade sg1 USING (StudentID) JOIN StudentGrade sg2 USING (StudentID) WHERE sg1.Subject = 'Maths' AND sg1.Grade = 'A' AND sg2.Subject = 'Chen' AND sg2.Grade = 'A' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote: I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? select s.StudentID , s.StudentName from Student as s , SubjectGrade as sj where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 'Maths' or sj.Subject = 'Chem') -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote: I am using mysql 4.0.25 with no subquery support : ( Jason Chan [EMAIL PROTECTED] ¦b¶l¥ó news:[EMAIL PROTECTED] ¤¤¼¶¼g... I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? SELECT StudentName FROM Student INNER JOIN SubjectGrade sg1 ON Student.StudentID=sg1.StudentID INNER JOIN SubjectGrade sg2 ON sg1.StudentID=sg2.StudentID WHERE sg1.Subject='Maths' AND sg1.Grade='A' AND sg2.Subject='Chem' AND sg2.Grade='A'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linux vs. Windows?
Same machine, any performance difference? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux vs. Windows?
Same machine, any performance difference? creakcreakcreak - the sound of a rusty old can of worms being opened...;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Alternatively, use an IN list for the subject, then use a HAVING clause
to limit the results to students with 2 matching rows, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
GROUP BY s.StudentID
HAVING COUNT(*) = 2;
What if the student has 2 result in maths (or 2 in chemistry) ??? ;-)
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux vs. Windows?
[snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux vs. Windows?
On Fri, 12 Aug 2005, Jay Blanchard wrote: Yes. Linux will consistently outperform Windows in many areas. Except Dilbert's boss's approval queue. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote: I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? select s.StudentID , s.StudentName from Student as s , SubjectGrade as sj where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 'Maths' or sj.Subject = 'Chem') I think your query will return student 3 as well -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Arno Coetzee wrote: Jason Chan wrote: I have a student Table and a SubjectGrade table snip I want to find out students who have got A in both Maths and Chem How the SQL look like? select s.StudentID , s.StudentName from Student as s , SubjectGrade as sj where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 'Maths' or sj.Subject = 'Chem') This will work, but you'll get 2 rows per student, one for each subject. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux vs. Windows?
Il giorno 12/ago/05, alle ore 16:46, Brian Dunning ha scritto: Same machine, any performance difference? go to http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/information/ benchmark-results/result-mysql-platform-relative.html anyway the difference isn't only in performance what did you think about crashless :-) Good job Paolo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
I havn't write my schema clearly , (StudentID, Subject) is the key of
SubjectGrade
Philippe Poelvoorde [EMAIL PROTECTED] ???
news:[EMAIL PROTECTED] ???...
Alternatively, use an IN list for the subject, then use a HAVING clause
to limit the results to students with 2 matching rows, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
GROUP BY s.StudentID
HAVING COUNT(*) = 2;
What if the student has 2 result in maths (or 2 in chemistry) ??? ;-)
--
Philippe Poelvoorde
COS Trading Ltd.
--
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: Linux vs. Windows?
I am willing to bet you I can write a more scalable higher performing socket server using NT I/O Completion Ports than you can write using Linux's epoll. It you're running a 32 cpu system, Windows will consistently outperform Linux in many areas. My point is, blanket statements like this aren't wise. - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Brian Dunning [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, August 12, 2005 7:55 AM Subject: RE: Linux vs. Windows? [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. -- 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]
Complex query. (It's killing me)
Hello list, I got a sql query that's is just beating me (5-0). Have have
read here and there, and MySQL Query Browser is just fed up with all the
tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me):
[shipments]
ID int
DateShipped DATE
[returns]
ID INT
DateReturned DATE
Type ENUM('undelivered','customer')
Status ENUM('open','close')
Second, the expected result:
Shipped Undelivered Returned Open
12/8/2005 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 6 7
Explanation of field columns:
Shipped: rows from [shipments] that have the field 'DateShipped'
populated.
Undelivered: rows from [returns] that have Type = 'undelivered'
Returnded: rows from [returns] that have Type = 'customer'
Open: rows from [returns] that have Status = 'open'
Third, the sql query I have came out with so far is (my non-working best
solution)
SELECT
shipments.DateShipped,
SUM(CASE
WHEN shipments.DateShipped IS NOT NULL THEN 1
ELSE 0
END) AS shipped
SUM(CASE
WHEN returns.DateReturned='undelivered' THEN 1
ELSE 0
END) AS undelivered,
SUM(CASE
WHEN returns.DateReturned='customer' THEN 1
ELSE 0
END) AS returned,
SUM(CASE
WHEN returns.Status='open' THEN 1
ELSE 0
END) AS open
FROM shipments, returns
GROUP BY shipments.DateShipped
ORDER BY shipments.DateShipped DESC
LIMIT 5
That's it. Im SO stuck with this query. I would really really appreciate
any help, hints, links, or ideas about it.
James G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
So i have to write 3 join if I have 3 conditions and so on, right? Scott Noyes [EMAIL PROTECTED] ??? news:[EMAIL PROTECTED] ???... Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem' and sg.Grade = 'A' Take a close look at the WHERE clause: sg.Subject = 'Maths' and sg.Subject = 'Chem' will never return a result - how could the subject be two different things at the same time? Here's one way that should work: SELECT s.StudentID, StudentName FROM Student s JOIN StudentGrade sg1 USING (StudentID) JOIN StudentGrade sg2 USING (StudentID) WHERE sg1.Subject = 'Maths' AND sg1.Grade = 'A' AND sg2.Subject = 'Chen' AND sg2.Grade = '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: Linux vs. Windows?
On Fri, 2005-08-12 at 09:55 -0500, Jay Blanchard wrote: [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. Except for those areas that it doesn't. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
RE: Linux vs. Windows?
[snip] On Fri, 2005-08-12 at 09:55 -0500, Jay Blanchard wrote: [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. Except for those areas that it doesn't. [/snip] True. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux vs. Windows?
[snip] I am willing to bet you I can write a more scalable higher performing socket server using NT I/O Completion Ports than you can write using Linux's epoll. It you're running a 32 cpu system, Windows will consistently outperform Linux in many areas. My point is, blanket statements like this aren't wise. [/snip] Except when they are. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux vs. Windows?
On 8/12/05, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] I am willing to bet you I can write a more scalable higher performing socket server using NT I/O Completion Ports than you can write using Linux's epoll. It you're running a 32 cpu system, Windows will consistently outperform Linux in many areas. My point is, blanket statements like this aren't wise. [/snip] Except when they are. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] It really depend on administrator/developer's ability to configure each. -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: Linux vs. Windows?
[snip] creakcreakcreak - the sound of a rusty old can of worms being opened...;) [/snip] Except when it's already open. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
SELECT s.StudentID, StudentName FROM Student s JOIN StudentGrade sg1 USING (StudentID) JOIN StudentGrade sg2 USING (StudentID) WHERE sg1.Subject = 'Maths' AND sg1.Grade = 'A' AND sg2.Subject = 'Chem' AND sg2.Grade = 'A' So i have to write 3 join if I have 3 conditions and so on, right? Yes, if you use this approach, you'll need another JOIN and another two conditions in the WHERE clause. If you use the IN()...HAVING approach, then you just need to add the new subject to the IN() function and change the HAVING COUNT(*) = 2 to = 3. (As was pointed out, this approach won't work if you have two Maths grades and no Chem grades.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux vs. Windows?
Like crashing, auto-rebooting, memory leaking, program cost, etc... :) Brian Duke Level(3) Communication ==-=-=-=-=--==--==¬ -Original Message- From: Pat Adams [mailto:[EMAIL PROTECTED] Sent: Friday, August 12, 2005 9:21 AM To: mysql@lists.mysql.com Subject: RE: Linux vs. Windows? On Fri, 2005-08-12 at 09:55 -0500, Jay Blanchard wrote: [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. Except for those areas that it doesn't. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA
I want to find out students who have got A in both Maths and Chem
How the SQL look like?
Philippe Poelvoorde wrote:
Michael Stassen wrote:
Alternatively, use an IN list for the subject, then use a HAVING
clause to limit the results to students with 2 matching rows, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
GROUP BY s.StudentID
HAVING COUNT(*) = 2;
What if the student has 2 result in maths (or 2 in chemistry) ??? ;-)
Then the table has redundant rows!
But seriously, that's a different question -- one which would require more
information to answer. In other words, based on the table description, I
assume the combination of StudentID and Subject is unique in table
SubjectGrade. While 'Maths', 'Phys', and 'Chem' sound categorical, I expect
they were simplifications of actual course names, and the question amounts to
finding students with 'A's in 2 specific courses. If the Subject is in fact
categorical, so that a given student may have several grades (rows) for the
same Subject, then I think we need more details. What, exactly, are thee
requirements? Do we want students with at least one 'A' in each category?
Students with all 'A's in each? Students with an 'A' average in each?
Something else?
The advantage of the GROUP BY version of the query (if my assumptions are
correct) is that it generalizes better than the self-join. For example, to
find students with an 'A' in 'Bio', 'Maths', 'Phys', and 'Chem', you'd have to
join the SubjectGrade table 2 more times in the self-join version, which gets
unwieldy and inefficient. The GROUP BY version is easy to change, however:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem', 'Bio', 'Phys')
AND sg.Grade = 'A';
GROUP BY s.StudentID
HAVING COUNT(*) = 4;
You could also find students who have gotten 'A's in exactly 3, or at least 3,
of those 4 subjects simply by modifying the HAVING clause to COUNT(*)=3, or
COUNT(*)=3, respectively.
On the other hand, to find a student with an 'A' in 'Chem' and a 'B' in
'Phys', for example, I think you'd probably use the self-join version, as you
need to tie the grade to the subject (though you could tie them with CONCAT in
the GROUP BY version, I suppose).
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query. (It's killing me)
Shipped Undelivered Returned Open 12/8/2005 143 3 3 Does this mean of the 14 shipped on 12/8/2005, 3 were returned at some later date, or does it mean that you shipped 14 on 12/8/2005, and on that same day 3 unrelated shipments came back, each of which could have been shipped any time before 12/8/2005? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query. (It's killing me)
Just in the spirit of refining my own skills, here is how I would tackle
the problem. It parses, but I haven't populated the tables so I don't know
if it works:
SELECT s.dateshipped, COUNT(r.type=undelivered), COUNT(r.type =
customer), COUNT(r.status=open)
FROM shipments s JOIN returns r ON s.id = r.id
GROUP BY s.dateshipped
ORDER BY s.dateshipped DESC ;
Does this do anything worthwhile?
James M. Gonzalez [EMAIL PROTECTED]
12/08/2005 16:16
To
mysql@lists.mysql.com
cc
Subject
Complex query. (It's killing me)
Hello list, I got a sql query that's is just beating me (5-0). Have have
read here and there, and MySQL Query Browser is just fed up with all the
tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me):
[shipments]
ID int
DateShipped DATE
[returns]
ID INT
DateReturned DATE
Type ENUM('undelivered','customer')
Status ENUM('open','close')
Second, the expected result:
Shipped Undelivered Returned Open
12/8/2005 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 6 7
Explanation of field columns:
Shipped: rows from [shipments] that have the field 'DateShipped'
populated.
Undelivered: rows from [returns] that have Type = 'undelivered'
Returnded: rows from [returns] that have Type = 'customer'
Open: rows from [returns] that have Status = 'open'
Third, the sql query I have came out with so far is (my non-working best
solution)
SELECT
shipments.DateShipped,
SUM(CASE
WHEN shipments.DateShipped IS NOT NULL THEN 1
ELSE 0
END) AS shipped
SUM(CASE
WHEN returns.DateReturned='undelivered' THEN 1
ELSE 0
END) AS undelivered,
SUM(CASE
WHEN returns.DateReturned='customer' THEN 1
ELSE 0
END) AS returned,
SUM(CASE
WHEN returns.Status='open' THEN 1
ELSE 0
END) AS open
FROM shipments, returns
GROUP BY shipments.DateShipped
ORDER BY shipments.DateShipped DESC
LIMIT 5
That's it. Im SO stuck with this query. I would really really appreciate
any help, hints, links, or ideas about it.
James G.
--
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: Complex query. (It's killing me)
Sorry - I think you need a LEFT JOIN or it won't count shipments which are
not returned.
Alec
[EMAIL PROTECTED]
12/08/2005 16:38
To
[EMAIL PROTECTED]
cc
mysql@lists.mysql.com
Subject
Re: Complex query. (It's killing me)
Just in the spirit of refining my own skills, here is how I would tackle
the problem. It parses, but I haven't populated the tables so I don't know
if it works:
SELECT s.dateshipped, COUNT(r.type=undelivered), COUNT(r.type =
customer), COUNT(r.status=open)
FROM shipments s JOIN returns r ON s.id = r.id
GROUP BY s.dateshipped
ORDER BY s.dateshipped DESC ;
Does this do anything worthwhile?
James M. Gonzalez [EMAIL PROTECTED]
12/08/2005 16:16
To
mysql@lists.mysql.com
cc
Subject
Complex query. (It's killing me)
Hello list, I got a sql query that's is just beating me (5-0). Have have
read here and there, and MySQL Query Browser is just fed up with all the
tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me):
[shipments]
ID int
DateShipped DATE
[returns]
ID INT
DateReturned DATE
Type ENUM('undelivered','customer')
Status ENUM('open','close')
Second, the expected result:
Shipped Undelivered Returned Open
12/8/2005 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 6 7
Explanation of field columns:
Shipped: rows from [shipments] that have the field 'DateShipped'
populated.
Undelivered: rows from [returns] that have Type = 'undelivered'
Returnded: rows from [returns] that have Type = 'customer'
Open: rows from [returns] that have Status = 'open'
Third, the sql query I have came out with so far is (my non-working best
solution)
SELECT
shipments.DateShipped,
SUM(CASE
WHEN shipments.DateShipped IS NOT NULL THEN 1
ELSE 0
END) AS shipped
SUM(CASE
WHEN returns.DateReturned='undelivered' THEN 1
ELSE 0
END) AS undelivered,
SUM(CASE
WHEN returns.DateReturned='customer' THEN 1
ELSE 0
END) AS returned,
SUM(CASE
WHEN returns.Status='open' THEN 1
ELSE 0
END) AS open
FROM shipments, returns
GROUP BY shipments.DateShipped
ORDER BY shipments.DateShipped DESC
LIMIT 5
That's it. Im SO stuck with this query. I would really really appreciate
any help, hints, links, or ideas about it.
James G.
--
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]
RE: Linux vs. Windows?
On Fri, 2005-08-12 at 09:30 -0600, Duke, Brian wrote: Like crashing, auto-rebooting, memory leaking, program cost, etc... -Original Message- From: Pat Adams [mailto:[EMAIL PROTECTED] Sent: Friday, August 12, 2005 9:21 AM To: mysql@lists.mysql.com Subject: RE: Linux vs. Windows? On Fri, 2005-08-12 at 09:55 -0500, Jay Blanchard wrote: [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. Except for those areas that it doesn't. In all seriousness, what operating system you use should not be based on expected performance. It should be based on your ability to administer it. If I were to test the performance of MySQL on two identical machines, one running Windows and one running Debian, I can almost guarantee that the Linux box would blow the Windows box away. On the flip side, my company would be better off running MySQL on a Windows box rather then one of our Solaris or AIX boxen, since none of us know much about them (they're maintained by our corporate office). Even though Linux more or less acts like its big UNIX cousins, the nitty gritty details of system administration, security, and patching are much difference. So put your database on whatever platform you're comfortable running. If you can secure a Windows box and make it stable, use Windows. If you can secure a Debian or RedHat or insert flavor of UNIX/Linux here, use it. You'll get a much higher return on investment from making sure that the server itself is set up correctly and the tables and queries that run on it are set up correctly then you will tuning e2fs parameters on a Linux box, or whatever it is that Windows admins do to make their boxen faster. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
Re: Locking Issue
The first thing I would do is to upgrade the Kernel, as per you r mail u said u were running 2.4.20-8, get the latest one for RH9 that is 2.4.20-31.9 SMP, and you might see a huge difference, if it doesn't work, then make sure you have properly indexed the colums, mytop is a great tool for diagnosis, also see the slow query log, play around with top and other OS tools, this should work if not switch to INNODB Kishore Jalleda On 8/11/05, Aaron [EMAIL PROTECTED] wrote: Hi all , I have been experiencing intermittent locking issues with MYSQL. It appears that sometimes a query will lock reliease its lock, and is causing other queries to wait and wait until the connection limit is reached and i am locked out of the database. Has anyone ever had anything like this happen? The setup: Redhat 9.0 , Kernel 2,4,20-8smp mysql-standard-4.1.7-pc-linux-i686-icc-glibc23 MyISAM Tables (And unless InnoDB can support fulltext or some other equivalent , migrating isnt an option at present) ext2fs Our Datbase Activity: We have a somewhat active website. Things run fairly smoothly for the most part , although we do have some slow queries from time to time. We have far more selects than updates , but updates are still reasonably active. Frequently , an update will get locked while a slower query is running. Sometimes we can experience a large backup waiting for a slow query , but typically everything sorts out once the slow query finishes. Rarely , however , a query will be in a locked state and will not let go of its lock. Subsequent updates lock , and subsequent selects lock. Eventually , if the above has happened , the connection table will fill up. We dont have any scripts that explicitly LOCK TABLES , aside from our backup script which uses mysqlhotcopy. Is it possible that the mysqlhotcopy LOCK TABLES could interfere with the locking from the website activity? I apologise for the vagueness of this request , I really dont know what direction would be best to further diagnose this. If you have any advice , it would be greatly appreciated. thanks for your time! Aaron -- 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: Linux vs. Windows?
* Jay Blanchard [EMAIL PROTECTED] [050812 07:32]: [snip] On Fri, 2005-08-12 at 09:55 -0500, Jay Blanchard wrote: [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. Except for those areas that it doesn't. [/snip] The best situation might be a combination of both. But wait! That's what I have. Boot up linux, then start windows in it's own desktop. Best of both worlds! And lately, I've been using MS-Access to help design and think about MySQL databases. MTCW tj -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Thanks Scott! Scott Noyes [EMAIL PROTECTED] ??? news:[EMAIL PROTECTED] ???... SELECT s.StudentID, StudentName FROM Student s JOIN StudentGrade sg1 USING (StudentID) JOIN StudentGrade sg2 USING (StudentID) WHERE sg1.Subject = 'Maths' AND sg1.Grade = 'A' AND sg2.Subject = 'Chem' AND sg2.Grade = 'A' So i have to write 3 join if I have 3 conditions and so on, right? Yes, if you use this approach, you'll need another JOIN and another two conditions in the WHERE clause. If you use the IN()...HAVING approach, then you just need to add the new subject to the IN() function and change the HAVING COUNT(*) = 2 to = 3. (As was pointed out, this approach won't work if you have two Maths grades and no Chem grades.) -- 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: Linux vs. Windows?
Please note that this answer is not meant to support one OS or another, but the information on that page is not useful. The information is seriously out of date. The comparison is on Windows NT not Server 2K3 or XP. The hardware is Pentium Pro 400 or AMD K6II-350 with old versions of software. Server 2K3 has been much more stable than Windows NT and its security is better, but still not great. -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com go to http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/information/ benchmark-results/result-mysql-platform-relative.html anyway the difference isn't only in performance what did you think about crashless :-) Good job Paolo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query. (It's killing me)
-Original Message- From: James M. Gonzalez Sent: 12 August 2005 16:58 To: 'Scott Noyes' Subject: RE: Complex query. (It's killing me) Sorry, I will explain myself more clearly: Everyday, we ship packages, and we also receive some packages. The one we receive, has been shipped by us some days before. So no package can be shipped AND received in the same day. The return reasons are two: it can not be delivered ( type='undelivered') or the customer send it to us god knows why (type='customer') I hope that makes sense. Im still very stuck with this. -Original Message- From: Scott Noyes [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 16:35 To: James M. Gonzalez Cc: mysql@lists.mysql.com Subject: Re: Complex query. (It's killing me) Shipped Undelivered Returned Open 12/8/2005 143 3 3 Does this mean of the 14 shipped on 12/8/2005, 3 were returned at some later date, or does it mean that you shipped 14 on 12/8/2005, and on that same day 3 unrelated shipments came back, each of which could have been shipped any time before 12/8/2005? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex query. (It's killing me)
The original sql query returned nothing. 0 rows.
The second (with a left join) returned a record for every date avaiable,
but a 0 in all others fields. (0 shipments, 0 undelivered, 0 returned, 0
open, etc)
Im not sure if we can solve this with a join, since each colum does have
a different set of conditions (like where clausules)
James
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 12 August 2005 16:41
To: [EMAIL PROTECTED]
Cc: James M. Gonzalez; mysql@lists.mysql.com
Subject: Re: Complex query. (It's killing me)
Sorry - I think you need a LEFT JOIN or it won't count shipments which
are
not returned.
Alec
[EMAIL PROTECTED]
12/08/2005 16:38
To
[EMAIL PROTECTED]
cc
mysql@lists.mysql.com
Subject
Re: Complex query. (It's killing me)
Just in the spirit of refining my own skills, here is how I would tackle
the problem. It parses, but I haven't populated the tables so I don't
know
if it works:
SELECT s.dateshipped, COUNT(r.type=undelivered), COUNT(r.type =
customer), COUNT(r.status=open)
FROM shipments s JOIN returns r ON s.id = r.id
GROUP BY s.dateshipped
ORDER BY s.dateshipped DESC ;
Does this do anything worthwhile?
James M. Gonzalez [EMAIL PROTECTED]
12/08/2005 16:16
To
mysql@lists.mysql.com
cc
Subject
Complex query. (It's killing me)
Hello list, I got a sql query that's is just beating me (5-0). Have have
read here and there, and MySQL Query Browser is just fed up with all the
tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me):
[shipments]
ID int
DateShipped DATE
[returns]
ID INT
DateReturned DATE
Type ENUM('undelivered','customer')
Status ENUM('open','close')
Second, the expected result:
Shipped Undelivered Returned Open
12/8/2005 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 6 7
Explanation of field columns:
Shipped: rows from [shipments] that have the field 'DateShipped'
populated.
Undelivered: rows from [returns] that have Type = 'undelivered'
Returnded: rows from [returns] that have Type = 'customer'
Open: rows from [returns] that have Status = 'open'
Third, the sql query I have came out with so far is (my non-working best
solution)
SELECT
shipments.DateShipped,
SUM(CASE
WHEN shipments.DateShipped IS NOT NULL THEN 1
ELSE 0
END) AS shipped
SUM(CASE
WHEN returns.DateReturned='undelivered' THEN 1
ELSE 0
END) AS undelivered,
SUM(CASE
WHEN returns.DateReturned='customer' THEN 1
ELSE 0
END) AS returned,
SUM(CASE
WHEN returns.Status='open' THEN 1
ELSE 0
END) AS open
FROM shipments, returns
GROUP BY shipments.DateShipped
ORDER BY shipments.DateShipped DESC
LIMIT 5
That's it. Im SO stuck with this query. I would really really appreciate
any help, hints, links, or ideas about it.
James G.
--
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]
RE: Linux vs. Windows?
Pat Adams wrote: Even though Linux more or less acts like its big UNIX cousins, the nitty gritty details of system administration, security, and patching are much difference. Ah, not really. In the *nix world, I first encountered Linux, then HPUX, then AIX, then Linux again, then Solaris. A sysadmin worth his pay won't have problem working any of them. /Per Jessen, Zürich -- http://www.spamchek.com/freetrial - managed anti-spam and anti-virus solution. Sign up for your free 30-day trial now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.11-beta has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 5.0.11-beta, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the seventh published Beta release in the 5.0 series. All attention will continue to be focused on fixing bugs and stabilizing 5.0 for later production release. NOTE: This Beta release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. Functionality added or changed: * Security improvement: Applied a patch that addresses a potential zlib data vulnerability that could result in an application crash. (CAN-2005-1849 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-1849)) This only affects the binaries for platforms that are linked statically against the bundled zlib (most notably Microsoft Windows and HP-UX). * SHOW CHARACTER SET and INFORMATION_SCHEMA now properly report the Latin1 character set as cp1252. (Bug #11216 (http://bugs.mysql.com/11216)) * mysqldump now dumps triggers for each dumped table. This can be suppressed with the --skip-triggers option. (Bug #10431 (http://bugs.mysql.com/10431)) * Added new ER_STACK_OVERRUN_NEED_MORE error message to indicate that, while the stack is not completely full, more stack space is required. (Bug #11213 (http://bugs.mysql.com/11213)) * NDB: Improved handling of the configuration variables NoOfPagesToDiskDuringRestartACC, NoOfPagesToDiskAfterRestartACC, NoOfPagesToDiskDuringRestartTUP, and NoOfPagesToDiskAfterRestartTUP should result in noticeably faster startup times for MySQL Cluster. (Bug #12149 (http://bugs.mysql.com/12149)) * Added support of where clause for queries with FROM DUAL. (Bug #11745 (http://bugs.mysql.com/11745)) * Added an optimization that avoids key access with NULL keys for the ref method when used in outer joins. (Bug #12144 (http://bugs.mysql.com/12144)) * Maximum size of stored procedures increased from 64k to 4Gb. (Bug #11602 (http://bugs.mysql.com/11602)) * Added error message for users who attempt CREATE TABLE ... LIKE and specify a non-table in the LIKE clause. (Bug #6859 (http://bugs.mysql.com/6859)) Bugs fixed: * Creation of the mysql group account failed during the RPM installation. (Bug #12348 (http://bugs.mysql.com/12348)) * big5 strings were not being stored in FULLTEXT index. (Bug #12075 (http://bugs.mysql.com/12075)) * When DROP DATABASE was called concurrently with a DROP TABLE of any table the MySQL Server crashed. (Bug #12212 (http://bugs.mysql.com/12212)) * max_connections_per_hour setting was being capped by unrelated max_user_connections setting. (Bug #9947 (http://bugs.mysql.com/9947)) * SELECT @@local... returned @@session... in the column header. (Bug #10724 (http://bugs.mysql.com/10724)) * Multiplying ABS() output by a negative number would return incorrect results. (Bug #11402 (http://bugs.mysql.com/11402)) * Updated dependency list for RPM builds to include missing dependencies such as useradd and groupadd. (Bug #12233 (http://bugs.mysql.com/12233)) * mysql_install_db used static localhost value in GRANT tables even when server hostname is not localhost, such as localhost.localdomain. This change is applied to version 5.0.10b on Windows. (Bug #11822 (http://bugs.mysql.com/11822)) * Multiple SELECT SQL_CACHE queries in a stored procedure causes error and client hang. (Bug #6897 (http://bugs.mysql.com/6897)) * Added checks to prevent error when allocating memory when there was insufficient memory available. (Bug #7003 (http://bugs.mysql.com/7003)) * Character data truncated when GBK characters 0xA3A0 and 0xA1 are present. (Bug #11987 (http://bugs.mysql.com/11987)) * Comparisons like SELECT A\\ LIKE A\\; fail when using SET NAMES utf8;. (Bug #11754 (http://bugs.mysql.com/11754)) * Corrected inaccurate error message when inserting out of range data under TRADITIONAL SQL mode. (Bug #11546 (http://bugs.mysql.com/11546)) * When used in a SELECT query against a view, the GROUP_CONCAT() function returned only a single row. (Bug #11412 (http://bugs.mysql.com/11412)) * Calling the C API function mysql_stmt_fetch() after all rows of a result set were
Re: BLOB FILES ERRORS
Hi, I've made it work with DUMPFILE but its a BIG workaround for my php aplication: To insert I have to read the file received from the browser $_FILES[tmp_name] placed it in a directory for MySQL to read it, and them insert it into MySQL table with LOAD_FILE To extract I have to use DUMPFILE and the read the file back in to a php var and them send it to the browser It works but it does not ugly.. On Thursday 11 August 2005 22:47, Kemin Zhou wrote: ::: Hi, ::: I remembered something when playing wiht BLOB. It looks that the select ::: statement does not ::: work well with BLOB. It always add or modify the BLOB a little bit. ::: Later I found out that ::: the client programming interfaces, perl,C++ etc tend to do well on BLOB ::: and files. ::: ::: Kemin ::: ::: Gleb Paharenko wrote: ::: Hello. ::: ::: Use DUMPFILE instead of OUTFILE. See: ::: http://dev.mysql.com/doc/mysql/en/select.html ::: ::: jose nuno neto [EMAIL PROTECTED] wrote: ::: Hi, ::: ::: I'm trying to use MySQL Blob columns to store files ::: (docs,gifs,pdf.) but I'm getting corrupted files from the DB. ::: ::: the applications runs php, loading file content with addslashes but in ::: order to spot the error i've tried it with mysql only like this: ::: column file is a MEDIUMBLOB ::: INSERT INTO XX SET file=LOAD_FILE(/tmp/teste.pdf); ::: and them ::: SELECT file INTO OUTFILE /tmp/teste_frommysql.pdf FROM XX WHERE ::: id=12; ::: ::: and teste_frommysql.pdf cant be loaded ::: ls -l shoes that the sizes are different: ::: 270713 Aug 2 11:28 /tmp/teste.pdf (ORIGINAL) ::: 277405 Aug 10 17:21 /tmp/teste_frommysql.pdf ::: ::: I've made a test with a text file and noticed that the file from MySQL ::: has a added LineBreak at the end.Is thta the problem? how can I fix ::: this? ::: ::: This is my config: ::: Linux 2.6.11-1.1369_FC4 ::: mysql-server-4.1.11-2 ::: mysql-4.1.11-2 ::: ::: cat /etc/my.cnf ::: [mysqld] ::: datadir=/var/lib/mysql ::: socket=/var/lib/mysql/mysql.sock ::: ::: [mysql.server] ::: user=mysql ::: basedir=/var/lib ::: ::: [safe_mysqld] ::: err-log=/var/log/mysqld.log ::: pid-file=/var/run/mysqld/mysqld.pid ::: ::: key_buffer_size=64M ::: table_cache=256 ::: sort_buffer_size=4M ::: read_buffer_size=1M -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index - max key length is 1024 bytes
I am trying to create an index with multiple fields. The sixe of each of the column is listed and thier sum is 560bytes. But when I try to create an index with the colums, it complains on the size exceeded 1024 bytes. Below is the query and the size of each.. create index selectTechnologyClubsThread_idx on content (club_id, date_update, subject, message_id, id, date_published, comment_count_d, display_usr_name_d, short_content) COLUMN_NAME TYPESIZE club_id Bigint 8 date_update Bigint 8 Subject Varchar(120)121 message_id varchar(120)121 Id Bigint 8 date_published Datetime8 comment_count_d Int 4 short_content Varchar(250)251 display_usr_name_d Varchar(30) 31 TOTAL : 560 Any sort of help would be great - javabuddy People are conversing... without posting their email or filling up their mail box. ~~1123867827435~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade 3.23 to 4.12 slowness
lack of a primary key was the problem. I am surprised the problem didnt show up in 3.23 but did in 4.12 Thanks for the suggestions! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux vs. Windows?
Folks, Go with what you know best. If you are a good Windows admin etc go with windows. If you are a good Linux/Unix admin go with Linux. What little performance gain from one or the other will be lost if you do not run a tight ship all around. Performance and stability goes way beyond what OS is better. Don't let anyone push you in one direction if you are more comfortable with the other OS. If you have little exposure with both I would say Linux. Not because it may or may not outperform windows just because once you learn it it's under your belt. Same machine, any performance difference? -- 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: Index - max key length is 1024 bytes
If you are on a version prior to 4.1.2 the max index size is 500 bytes
{not sure why the error mentions 1024}
From section 14.1 of documention
The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can
be changed by recompiling. For the case of a key longer than 250 bytes,
a larger key block size than the default of 1024 bytes is used.
-Original Message-
From: javabuddy [mailto:[EMAIL PROTECTED]
Sent: Friday, August 12, 2005 12:30 PM
To: mysql@lists.mysql.com
Subject: Index - max key length is 1024 bytes
I am trying to create an index with multiple fields.
The sixe of each of the column is listed and thier sum is 560bytes. But
when I try to create an index with the colums, it complains on the size
exceeded 1024 bytes. Below is the query and the size of each..
create index selectTechnologyClubsThread_idx on content
(club_id, date_update, subject, message_id, id, date_published,
comment_count_d, display_usr_name_d, short_content)
COLUMN_NAME TYPESIZE
club_id Bigint 8
date_update Bigint 8
Subject Varchar(120)121
message_id varchar(120)121
Id Bigint 8
date_published Datetime8
comment_count_d Int 4
short_content Varchar(250)251
display_usr_name_d Varchar(30) 31
TOTAL : 560
Any sort of help would be great
- javabuddy
People are conversing... without posting their email or filling up their
mail box. ~~1123867827435~~
roomity.com http://roomity.com/launch.jsp No sign up to read or search
this Rich Internet App
--
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]
Index - max key length is 1024 bytes
Thanks But still I can't get the part where my column size totals to 560, but MySql complaining that I have exceeded 1024. Did I went anywhere wrong??? - Gana. People are conversing... without posting their email or filling up their mail box. ~~1123871242499~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index - max key length is 1024 bytes
javabuddy wrote: Thanks But still I can't get the part where my column size totals to 560, but MySql complaining that I have exceeded 1024. Did I went anywhere wrong??? - Gana. People are conversing... without posting their email or filling up their mail box. ~~1123871242499~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App What character set are you using? The default (latin1) is 1 byte per char, but some different character sets are 2 or 3 bytes per char (for example, unicode / UTF8 is 3 bytes). I can't find where this is documented in the manual, but it is discussed slightly here: http://dev.mysql.com/doc/mysql/en/char.html http://dev.mysql.com/doc/mysql/en/charset-unicode.html HTH, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index - max key length is 1024 bytes
javabuddy wrote: But still I can't get the part where my column size totals to 560, but MySql complaining that I have exceeded 1024. Did I went anywhere wrong??? Are you sure you've thought your index through correctly and considered how MySQL will use it? What sort of query would such an index be useful for? In most circumstance it makes little sense to index more than a prefix of a long VARCHAR or TEXT field, and indexing nine fields at once will only make sense if you use all nine in your query. Maybe you want a FULLTEXT index? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index - max key length is 1024 bytes
Thanks Devananda.. it helped. - javabuddy. People are conversing... without posting their email or filling up their mail box. ~~1123878070408~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spatial extensions - SRID
To the powers that be: Can we get a MySql GIS/spatial list?? This is going to be a very popular area (actually it is already). I am trying to use the Spatial extensions to MySql. To be honest PostGIS has many more features, but MySql is my favorite, and on top of that, the applications I am using only work with MySql. My problem is this. From the manual, all Geometry contains: Its SRID, or Spatial Reference Identifier. This value identifies the geometry's associated Spatial Reference System that describes the coordinate space in which the geometry object is defined. In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry. This is a big issue. Will this be the same in version 5? Or will the SRIDs actually be used? I would like to just represent everything in lat/lon, not on a planar surface. Now, if the SRIDs are used in MySql 5, where is the list of acceptable values? Also, to developers on the list: perhaps it would be better if these were text values like 'NAD83'?? -- http://www.douglassdavis.com
Re: Complex query. (It's killing me)
mysql Ver 12.22 Distrib 4.0.20, for mandrake-linux-gnu (i586) With 4.1, it might have been a little easier using some subqueries. But with 4.0, I don't think we can get the results you're looking for in a single query, without some really nasty setup. Part of the issue is that we need to join the tables using the date column, and if you have dates where you didn't ship anything but got returns, and others where you didn't get returns but you did ship, we'll be missing rows. However, we can get the results you want in three queries, using temporary tables. Or you could do it in two, if you want to join them together using your favorite client-side language. CREATE TEMPORARY TABLE myTemp SELECT dateShipped AS date, COUNT(*) AS shipped, 0 AS undelivered, 0 AS returned, 0 AS open FROM shipped GROUP BY dateShipped; INSERT INTO myTemp SELECT dateReturned AS date, 0 AS shipped, SUM(type='undelivered') AS undelivered, SUM(type='customer') AS returned, SUM(status='open') AS open FROM returns GROUP BY dateReturned; SELECT date, SUM(shipped) AS shipped, SUM(undelivered) AS undelivered, SUM(returned) AS returned, SUM(open) AS open FROM myTemp GROUP BY date; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux vs. Windows?
MySQL is written with UNIX-like operating systems in mind. MySQL for Windows does not currently take advantage of many of the powerful scalability features of Windows NT based operating systems (2000, XP, 2003), as stated in the documentation. I happen to be one of the people working to make MySQL take advantage of the NT functionality. When my new Win32 disk I/O code goes into MySQL, and all disk I/O isn't serialized (in other words SELECT + SELECT on all engines, and SELECT + INSERT in MyISAM will no longer be serialized simply because Windows doesn't have pread/pwrite), I think you're going to see some of the artificial performance differences between MySQL for Windows and MySQL for Linux start to level off. Not that I'd suggest benchmarking those two programs against each other as some kind of valid comparison of performance until a lot of MySQL for Windows functionality is rewritten to take advantage of NT kernel features. For an example of how much different a server based solely on UNIX-like emulation of syscalls versus that same code properly ported to Windows, take a look at Apache2 for Windows. They take advantage of asynchronous accepts and I/O completion ports in the new MPM. What many non-programmers fail to realize is that the NT kernel provides methods of high performance I/O and scalability that are not only competitive with but in some ways superior to Linux's epoll or BSD's kqueue. The problem is, the only experience you have to base your judgement on is either Microsoft software like IIS which has had plenty of bumps along the way, or UNIX programs that are ported to Windows by using compatibility functions and hacking certain functionality to fit into the UNIX model these programs are written for. The first great example of how a well written port to Windows can turn out high performance and scalability, take a look at the WinNT port of Apache2, and then tell us how slow Windows is. - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Jeremiah Gowdy [EMAIL PROTECTED]; Brian Dunning [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, August 12, 2005 8:26 AM Subject: RE: Linux vs. Windows? [snip] I am willing to bet you I can write a more scalable higher performing socket server using NT I/O Completion Ports than you can write using Linux's epoll. It you're running a 32 cpu system, Windows will consistently outperform Linux in many areas. My point is, blanket statements like this aren't wise. [/snip] Except when they are. -- 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]
Child-Parent Relationships with 2 Parents
Suppose I want to create an animal kingdom database
that gives me the option of displaying the following
taxonomic hierarchies:
1) A bread crumbs navigation string, including ALL
taxons; e.g. Animalia (kingdom) Chordata (phylum)
Vertebrata (a SUBphylum) Mammalia (class) Eutheria
(a SUBclass)
2) A bread crumbs nav string that excludes taxons
beginning with SUB; e.g. Animalia Chordata
Mammalia
3) Lists of various taxons' children, including ALL
taxons; e.g.
a. CHORDATA (phylum)
b. Vertebrata (subphylum)
b. Urochordata (subphylum)
4) Lists of taxons' children, excluding SUBorders,
SUBfamilies, etc.; e.g.
a. CHORDATA (phylum)
b. Mammalia (class)
b. Aves (class)
b. Reptilia (class), etc.
5. A combination that looks like this:
a. CHORDATA (phylum)
b. VERTEBRATA
c. Mammalia
c. Aves
c. Reptilia
c. Amphibia
b. UROCHORDATA
And if you clicked on Vertebrata, you'd see only its
children, like this:
Mammalia
Aves
Reptilia
Amphibia
What's the best way to organize a database to achieve
this flexibility? My current child-parent relationship
generally works, but I think I erred in treating
SUBtaxons a little differently, assigning them parents
but not children. For example, the data below
illustrates how I assigned the phylum Chordata as the
parent of both the subphylum Vertebrata and the class
Mammalia.
Chordata | Animalia
Vertebrata | Chordata
Mammalia | Chordata
So clicking Chordata displays...
Vertebrata
Mammalia
...and clicking Mammalia displays mammal orders, but
clicking Vertebrata displays nothing.
Do you think I'll be able to write PHP scripts that do
what I want, or does it make more sense to reorganize
my database, assigning parents and children to every
taxon, then writing PHP scripts that somehow weed out
SUBtaxons when I don't want to display them?
A third option is to use a recursive array, something
I haven't learned how to do yet. If I try a recursive
array, I assume I'd have to modify my database so that
every taxon does indeed have a parent and a child.
If it helps, I posted the code for the two PHP scripts
I'm using to make my bread crumbs navigation and to
display the children of various taxons. Eventually,
I'll have to write a third script that will join
tables with additional information on distribution,
diet, etc.
But I think what I really need now is confirmation
that I should assign EVERY taxon a parent and child
(or advice to the contrary) and whether I'd be better
off sticking with a child-parent relationship or a
recursive array.
Thanks.
[PHP]
//
http://www.sitepoint.com/article/hierarchical-data-database/2
function display_children($parent, $level)
{
$result = mysql_query('SELECT Name FROM gzanimals as A
WHERE Parent=' . $parent . ';');
while ($row = mysql_fetch_array($result)) {
echo str_repeat(' ',$level).$row['Name'].\n;
display_children($row['Name'], $level+1);
}
}
function get_path($node) {
$result = mysql_query('SELECT Parent FROM gzanimals
'. 'WHERE Name='.$node.';');
$row = mysql_fetch_array($result);
$path = array();
if ($row['Parent']!='') {
$path[] = $row['Parent'];
$path = array_merge(get_path($row['Parent']),
$path);
}
return $path;
}
$mypath = get_path($mycode);
for($i=0;$icount($mypath);$i++){
echo a href=\index.php?taxon=.$mypath[$i].\
.$mypath[$i]. /a gt; ;
}
[/PHP]
* * * * * * * * * *
This is the script I'm using to display children of
various taxons:
[PHP]
?php
$result = mysql_query('select count(*) from
gzanimals');
if (($result) (mysql_result ($result , 0) 0)) {
} else {
die('Invalid query: ' . mysql_error());
}
{
$taxon = mysql_query (SELECT Name, Parent FROM
gzanimals AS A
WHERE Parent = '$_GET[taxon]');
echo 'div class=' . $_GET['taxon'] . '';
echo 'table';
//!-- BeginDynamicTable --
// while ($row = mysql_fetch_array ($taxon)) {
while ($row = mysql_fetch_array($taxon, MYSQL_ASSOC))
{
// mysql_fetch_array($taxons, MYSQL_ASSOC)
// tda
href=SitePointA.php?taxon=$taxon{$row[Name]}/a/td
echo EOD
tr
tda
href=index.php?taxon=$row[Name]{$row[Name]}/a/td
/tr
EOD;
}
}
echo '/table/div';
?
/td
/tr
/table
[/PHP]
__
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: Child-Parent Relationships with 2 Parents
I just thought of a possible solution. Instead of listing all the phyla, orders, classes, families, genera and species in one big table, create separate tables for each taxonomic level. Each taxon would then have two parents. the genus Sus' (pigs) parents would be both the subfamily Suinae and family Suidae. But the genus Panthera doesn't belong to a subfamily, so its family (Felidae) would be listed as both Parent and Parent2. NAME | PARENT | PARENT2 Sus | Suinae | Suidae Panthera | Felidae | Felidae Then I could write PHP scripts focusing on either Parent or Parent2, depending on what I want to do. Does this sound sensible? Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database equivalent to NorthWind for M$ SQL
Hi Scott, Is there any database file similiar to M$'s Northwind that I can use to play with? It would be nice if there is one inside MySQL by default for newbies to start out with. Microsoft's document often used Northwind as an example to teach functions. How about FlightStats: http://dl.flightstats.us/ You can try out a front-end to the data: http://flightstats.us/ It's all public domain. Read about where it comes from: http://flightstats.us/about.php Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux vs. Windows?
I can't resist... neither Mac OS X! lol Dan T On Aug 12, 2005, at 8:46 AM, Brian Dunning wrote: Same machine, any performance difference? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
