Re: help needed restoring crashed mysql
Am 30.11.2011 03:13, schrieb Karen Abgarian: The concept is not difficult to explain. Most people do not expect a gas tank to shrink once the gas is consumed...right? yes, but the hard-disk is the gas tank and the data are the gas and yes, normally everybody would expect after deleting data that the space is available for other applications signature.asc Description: OpenPGP digital signature
Re: help needed restoring crashed mysql
2011/11/29 23:19 +0100, Reindl Harald MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database And are then the table-files in the directories with frm, or in the directory where ibdata1 is? If the latter, one problem is exchanged for another. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help needed restoring crashed mysql
Am 30.11.2011 07:02, schrieb Hal?sz S?ndor: 2011/11/29 23:19 +0100, Reindl Harald MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database And are then the table-files in the directories with frm, or in the directory where ibdata1 is? If the latter, one problem is exchanged for another. they are in the db-folder but even if not it is a hughe differene if optimize table tablename free space on disk or not [root@mail:/mysql_data]$ ls insgesamt 3,0G drwx-- 2 mysql mysql 4,0K 2011-11-25 10:27 dbmail drwx-- 2 mysql mysql 4,0K 2011-11-20 17:46 mysql drwx-- 2 mysql mysql 4,0K 2011-11-20 17:46 performance_schema drwx-- 2 mysql mysql 4,0K 2011-11-30 04:00 syslog -rw-rw 1 mysql mysql 354M 2011-11-30 14:01 ibdata1 -rw-rw 1 mysql mysql 512M 2011-11-30 14:01 ib_logfile0 -rw-rw 1 mysql mysql 512M 2011-11-30 02:21 ib_logfile1 -rw-rw 1 mysql mysql6 2011-11-20 17:46 mysql_upgrade_info -rw-rw 1 mysql mysql 1,1G 2011-11-29 15:21 bin.000137 -rw-rw 1 mysql mysql 617M 2011-11-30 14:01 bin.000138 -rw-rw 1 mysql mysql 72 2011-11-29 15:21 bin.index [root@mail:/mysql_data]$ ls dbmail/ insgesamt 9,5G -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_config.frm -rw-rw 1 mysql mysql 8,7K 2011-09-18 08:18 cms1_global_cache.frm -rw-rw 1 mysql mysql 9,4K 2011-09-18 08:18 cms1_haupt.frm -rw-rw 1 mysql mysql 8,6K 2011-09-18 08:18 cms1_locks.frm -rw-rw 1 mysql mysql 9,8K 2011-09-18 08:18 cms1_meta.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_snippets.frm -rw-rw 1 mysql mysql 11K 2011-09-18 08:18 cms1_sub2.frm -rw-rw 1 mysql mysql 11K 2011-09-18 08:18 cms1_sub.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_user_group_permissions.frm -rw-rw 1 mysql mysql 8,6K 2011-09-18 08:18 cms1_user_login.frm -rw-rw 1 mysql mysql 9,6K 2011-09-18 08:18 cms1_user_modules.frm -rw-rw 1 mysql mysql 8,5K 2011-09-14 09:32 cms1_user_online.frm -rw-rw 1 mysql mysql 9,3K 2011-09-18 08:18 cms1_users.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_aliases.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_aliases_global.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_allowed_hosts.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_client_admins.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_clients.frm -rw-rw 1 mysql mysql 8,8K 2011-10-22 20:18 dbmail_acl.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_aliases.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_auto_notifications.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_auto_replies.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_ccfield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_datefield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_envelope.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_fromfield.frm -rw-rw 1 mysql mysql 8,4K 2011-10-22 20:19 dbmail_headername.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_headervalue.frm -rw-rw 1 mysql mysql 8,9K 2011-10-22 20:20 dbmail_mailboxes.frm -rw-rw 1 mysql mysql 8,6K 2011-07-24 12:17 dbmail_messageblks.frm -rw-rw 1 mysql mysql 8,9K 2011-10-22 20:20 dbmail_messages.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_pbsp.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_physmessage.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_referencesfield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_replycache.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_replytofield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_sievescripts.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_subjectfield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_subscription.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_tofield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_usermap.frm -rw-rw 1 mysql mysql 8,9K 2011-10-22 20:21 dbmail_users.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_mta.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_recipient_relay.frm -rw-rw 1 mysql mysql 1,8K 2011-11-25 10:27 dbma_recipients.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_reply_groups.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_rewrite_domains.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_rewrite_senders.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_sender_relay.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_spamfilter.frm -rw-rw 1 mysql mysql 1,7K 2011-07-24 11:49 dbma_stats.frm -rw-rw 1 mysql mysql 8,5K 2011-08-27 22:39 dbma_transports_error.frm -rw-rw 1 mysql mysql 1,5K 2011-07-24 11:49 dbma_transports.frm -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm -rw-rw 1 mysql mysql 64K 2011-09-18 08:18 cms1_config.ibd -rw-rw
Re: help needed restoring crashed mysql
Am 29.11.2011 14:08, schrieb Luis Pugoy: Hello. I have the following problem. I was importing a large database to mysql using mysqldump. Unfortunately this filled up the whole disk, and mysqldump exited with an error that the table it is currently writing to is full. Checking df -h it shows that the disk usage is at 100%. I decided to drop the database I was importing, but rechecking df -h shows that it is still at 100%. I then looked for the cause of this and found a very large file under /var/lib/mysql. I think it is ibdata1. ibdata1 does NEVER get smaller, this is normal and a hughe problem in your case, only if you are using innodb_file_per_table which is NOT default would retire the space after drop tables why is this dumb innodb_file_per_table=0 default since MOST PEOPLE have only troubles with it because they can not free space with optimize table with no real benefits? signature.asc Description: OpenPGP digital signature
Re: help needed restoring crashed mysql
On 29.11.2011, at 5:21, Reindl Harald wrote: ibdata1 does NEVER get smaller, this is normal and a hughe problem in your case, only if you are using innodb_file_per_table which is NOT default would retire the space after drop tables why is this dumb innodb_file_per_table=0 default since MOST PEOPLE have only troubles with it because they can not free space with optimize table with no real benefits? Hi... The logic behind this is probably that without innodb_file_per_table=1 and with several large ibdata files, the space IS freed up when one does optimize table or drop table. The space is freed up inside the database files and can be reused. If we think about it, the database product should only resolve problems of the database space management, not of the OS space management. Then, the user essentially asked InnoDB to keep allocating arbitrary amount of space as needed, ignoring that the OS disk is actually of the limited size. To be correct about it, the user should have stated that the ibdata file should have a firm limit and not autoextend beyond that. This is not to say that MySQL could not have more of the file management features. For example, the ability to add or remove datafiles on the fly and the ability to detach tablespaces as collections of tables. Making innodb tablespaces default... well, it still would not liberate the users from thinking whether they want to run with them enabled or not. For example, if I have 1 tables of 100 bytes each, I probably do not want tablespaces. If I have 1% of tables consuming 99% of the space, I would also not want the tablespaces. As for the OP's problem, unless he changed his mind about the need to import, the same amount of space would anyway be consumed. The solution would probably be to find some bigger O Sdisk and copy that ibdata file there. Right? Cheers Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help needed restoring crashed mysql
This is not to say that MySQL could not have more of the file management features. For example, the ability to add or remove datafiles on the fly and the ability to detach tablespaces as collections of tables. That's where MySQL(read InnoDB) got stuck actually, it never introduced a powerful datafiles management system, and that is where Oracle excels (as far as being almost a O.S.) with multiple level of abstractions, just think of ASM. It is actually the part of Oracle I like most as well as the really oraclish way to get stats out of it! The 'problem' with MySQL is that it is so easy to start with it that people do not realize that is also a real RDBMS. -- Claudio Making innodb tablespaces default... well, it still would not liberate the users from thinking whether they want to run with them enabled or not. For example, if I have 1 tables of 100 bytes each, I probably do not want tablespaces. If I have 1% of tables consuming 99% of the space, I would also not want the tablespaces. As for the OP's problem, unless he changed his mind about the need to import, the same amount of space would anyway be consumed. The solution would probably be to find some bigger O Sdisk and copy that ibdata file there. Right? Cheers Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: help needed restoring crashed mysql
Am 29.11.2011 20:25, schrieb Karen Abgarian: On 29.11.2011, at 5:21, Reindl Harald wrote: why is this dumb innodb_file_per_table=0 default since MOST PEOPLE have only troubles with it because they can not free space with optimize table with no real benefits? The logic behind this is probably that without innodb_file_per_table=1 and with several large ibdata files, the space IS freed up when one does optimize table or drop table. The space is freed up inside the database files and can be reused. well, and if you have this day 2 TB mysql-data and a year later get rid of 1 TB of it they allocated space can be REUSED for innodb but never for any other application If we think about it, the database product should only resolve problems of the database space management, not of the OS space management. the database producht with default settings is the part starting the troubles of os-space-managment and this is idiotic, no other words for this! MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database the user essentially asked InnoDB to keep allocating arbitrary amount of space as needed, ignoring that the OS disk is actually of the limited size. To be correct about it, the user should have stated that the ibdata file should have a firm limit and not autoextend beyond that. yes this case is a user-problem but the cases where ibdata1 is growing becasue ONCE bigger data was stored and never release the allocated space is a design-problem signature.asc Description: OpenPGP digital signature
Re: help needed restoring crashed mysql
Hi... there is stuff inline there. The logic behind this is probably that without innodb_file_per_table=1 and with several large ibdata files, the space IS freed up when one does optimize table or drop table. The space is freed up inside the database files and can be reused. well, and if you have this day 2 TB mysql-data and a year later get rid of 1 TB of it they allocated space can be REUSED for innodb but never for any other application I did not say it is the right thing to not have an option to shrink the database or do file management. I tried to explain the logic that is probably put into this product. Another piece of logic is that it is not really typical for the databases to lose 50% of its volume. The databases usually either grow, or can grow, or are destroyed. In that regard the product with this feature lacking probably still covers the needs of most. By comparison, Oracle did not provide ability to drop the datafiles until, eh, version 8, I believe, and it was not made easy until version 10. If we think about it, the database product should only resolve problems of the database space management, not of the OS space management. the database producht with default settings is the part starting the troubles of os-space-managment and this is idiotic, no other words for this! I would say inconvenient. As I explained above, the OS space allocation problems that way could be considered a corner case and thus be considered unimportant by MySQL development. Considering the problem of reclaiming 1 terabyte out of 2-terabyte database, one could resolve it with creating a brand new instance followed by export/import of data. It is not that there is no solution, it is inconvenient to use. MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database Well, I would not base my database design on luck and playing. There should be good awareness of what the features do and what would be the plan to deal with file allocations should the database grow, shrink or somerset. but the cases where ibdata1 is growing becasue ONCE bigger data was stored and never release the allocated space is a design-problem Not exactly. A design problem is to build a server in such a way as that adding a feature to remove datafiles would be impossible (without major rebuild). I think this one can be added. I didn't bother to check, but I would be surprised if there isn't already an enhancement request for this
Re: help needed restoring crashed mysql
On Nov 29, 2011, at 11:50 AM, Claudio Nanni wrote: This is not to say that MySQL could not have more of the file management features. For example, the ability to add or remove datafiles on the fly and the ability to detach tablespaces as collections of tables. That's where MySQL(read InnoDB) got stuck actually, it never introduced a powerful datafiles management system, and that is where Oracle excels (as far as being almost a O.S.) with multiple level of abstractions, just think of ASM. It is actually the part of Oracle I like most as well as the really oraclish way to get stats out of it! The 'problem' with MySQL is that it is so easy to start with it that people do not realize that is also a real RDBMS. -- Yes, Oracle has features of the known kind. But, if we consider the wishlist for InnoDB, then on top there would be an ability to add files without stopping database (that is without innodb tablespaces), followed by the ability to control which files are part of the database (information schema of that). It looks like at some point they have decided that the file per table thing will cover all needs. Well, it doesn't. I personally do not like dealing with 50G files should individual table grow to this size. It will be much better managed with a larger collection of smaller files, without innodb tablespaces. It is after I have those I would want the features like removing files or compacting the database.
Re: help needed restoring crashed mysql
Am 30.11.2011 01:11, schrieb Karen Abgarian: MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database Well, I would not base my database design on luck and playing. There should be good awareness of what the features do and what would be the plan to deal with file allocations should the database grow, shrink or somerset if you are working many years with mysql and myisam you normally do not expect this - and no my work depends never really on luck and that is why it look in ALL datadirs of all software i am using and missed the table files known from my isam most peopole DO NOT care about this and not expect that allocated space will not be freed nor makes it any sense to have a whole database-server to dump/import because you get rid of big databases Another piece of logic is that it is not really typical for the databases to lose 50% of its volume. well, so install http://www.dbmail.org/ with replication and offsite-backups of the slave, get rid of your biggest mail-user and think about how useful it is to waste all this space as before multiple times in the backup-storages it did never happen to me - but i heard so many people start whining because the mysql-defaults and these are most pepole which do not have our knowledge to handle this before and in this case also not able to handle dump/import in a production environment signature.asc Description: OpenPGP digital signature
Re: help needed restoring crashed mysql
Hi... and some more stuff inline. Well, I would not base my database design on luck and playing. There should be good awareness of what the features do and what would be the plan to deal with file allocations should the database grow, shrink or somerset if you are working many years with mysql and myisam you normally do not expect this - and no my work depends never really on luck and that is why it look in ALL datadirs of all software i am using and missed the table files known from my isam most peopole DO NOT care about this and not expect that allocated space will not be freed nor makes it any sense to have a whole database-server to dump/import because you get rid of big databases I am not saying there is anything wrong with your experience. I understand that people having worked with MyISAM might have a different mindset. But they at some point did make a decision to use InnoDB. I think this is the time when I would ask myself a question: where is it that my data will be stored. The concept is not difficult to explain. Most people do not expect a gas tank to shrink once the gas is consumed...right? Another piece of logic is that it is not really typical for the databases to lose 50% of its volume. well, so install http://www.dbmail.org/ with replication and offsite-backups of the slave, get rid of your biggest mail-user and think about how useful it is to waste all this space as before multiple times in the backup-storages I didn't have a pleasure to use dbmail. I presume it does something with mail users. Thinking logically, if I got rid of my biggest mail user, I might eventually get another user, even bigger one, which would consume the same space vacated by the deceased user. So why would I want to give up the space then? it did never happen to me - but i heard so many people start whining because the mysql-defaults and these are most pepole which do not have our knowledge to handle this before and in this case also not able to handle dump/import in a production environment If the people do not have the knowledge to do exports/imports, the brand new and cool file management feature will not help them either. Essentially, the instructions how to use that feature are written in the same book as the instructions how to do export/imports. If we consider it as given that people would never read, it is a dead end. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help needed with what appears to be a corrupted innodb db
Pito, can u show us the innodb parameters in the my.cnf file. regards anandkl On Sat, Jan 8, 2011 at 10:31 PM, Pito Salas r...@salas.com wrote: I am very new to trying to solve a problem like this and have searched and searched the web for a useful troubleshooting guide but I am honestly stuck. I wonder if anyone out there could take a look at this bit of mysqld log. Any help at all would be greatly appreciated!!! Pito 110107 15:07:15 mysqld started 110107 15:07:15 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 110107 15:07:15 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 35 515914826. InnoDB: Doing recovery: scanned up to log sequence number 35 515915839 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1 row operations to undo InnoDB: Trx id counter is 0 1697553664 110107 15:07:15 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 1697553198, 1 rows to undoInnoDB: Error: trying to access page number 3522914176 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 110107 15:07:15InnoDB: Assertion failure in thread 3086403264 in file fil0fil.c line 3922 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbffc55ac, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8139eec 0x83721d5 0x833d897 0x833db71 0x832aa38 0x835f025 0x835f7a3 0x830a77e 0x8326b57 0x831c825 0x8317b8d 0x82a9e66 0x8315732 0x834fc9a 0x828d7c3 0x81c29dd 0x81b5620 0x813d9fe 0x40fdf3 0x80d5ff1 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.htmlcontains information that should help you find out what is causing the crash. 110107 15:07:15 mysqld ended -- Check out http://www.salas.com and http://www.blogbridge.com/look -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
RE: Help needed on query on multiple tables
How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help needed on query on multiple tables
Thanks! That did it perfectly! Michael On Jun 3, 2010, at 11:45 AM, Steven Staples wrote: How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help needed on query on multiple tables
I am glad that I was able to help someone finally :) There may be other ways to do this, but that was what first came to mind. I would maybe run an explain on that query to ensure that it is using indexes. Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:55 AM To: Steven Staples Cc: 'MySql' Subject: Re: Help needed on query on multiple tables Thanks! That did it perfectly! Michael On Jun 3, 2010, at 11:45 AM, Steven Staples wrote: How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help needed
Hi Jerry, Thanks for the tip. What is UUID look for? is it an ID associated with the motherboard, CPU, harddisk? Thanks. Regards, Velen - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'Garris, Nicole' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, May 12, 2008 11:43 PM Subject: RE: Help needed Your best bet would be to use a UUID: SELECT UUID(); +--+ | uuid() | +--+ | c712dc72-718d-102b-b3c8-97395a1766b9 | +--+ There are equivalents in various programming languages, although not in PHP. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Garris, Nicole [mailto:[EMAIL PROTECTED] Sent: Monday, May 12, 2008 11:59 AM To: mysql@lists.mysql.com Subject: RE: Help needed Sorry, you can't prove uniqueness by running it against a hundred thousand, million, or even a billion computers. (The billionth-and-first computer could be the one with the non-unique ID.) You need a mathematical proof to prove uniqueness. -Original Message- From: Velen [mailto:[EMAIL PROTECTED] Sent: Monday, May 12, 2008 8:32 AM To: Arthur Fuller Cc: mysql@lists.mysql.com Subject: Re: Help needed Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? Thanks. Velen On Sat, 2008-05-10 at 20:59 +0400, Velen wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. Please supply a Linux version of your virus so I can test. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au - Original Message - From: Arthur Fuller To: Velen Sent: Saturday, May 10, 2008 10:44 PM Subject: Re: Help needed On my main machine the PCID is 135184-45-4-10-1513-1. Hope it helps. Arthur On Sat, May 10, 2008 at 12:59 PM, Velen [EMAIL PROTECTED] wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. It will display an ID, please mail me back the ID. If you can use it on several computers, it will be even better for me, I need to have as much results as possible. The program i'm testing is supposed to create a unique ID for each PC, this is why I need to test if it is really unique. This file is virus free and cannot do any harm to your PC. It is however a voluntary testing. Thanks for your help in advance. Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help needed
I suggest you check out http://en.wikipedia.org/wiki/UUID. It seems to have a pretty current, but not overwhelming, explanation. Originally the starting point for a UUID was the MAC (hardware) address of the network interface. Theoretically, there should only be one device with any particular MAC address in the whole world. There are exceptions, but they would be mistakes. For your purposes, you can assume that it is a unique string of 32 hexadecimal digits. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Velen [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 13, 2008 11:28 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Help needed Hi Jerry, Thanks for the tip. What is UUID look for? is it an ID associated with the motherboard, CPU, harddisk? Thanks. Regards, Velen - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'Garris, Nicole' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, May 12, 2008 11:43 PM Subject: RE: Help needed Your best bet would be to use a UUID: SELECT UUID(); +--+ | uuid() | +--+ | c712dc72-718d-102b-b3c8-97395a1766b9 | +--+ There are equivalents in various programming languages, although not in PHP. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Garris, Nicole [mailto:[EMAIL PROTECTED] Sent: Monday, May 12, 2008 11:59 AM To: mysql@lists.mysql.com Subject: RE: Help needed Sorry, you can't prove uniqueness by running it against a hundred thousand, million, or even a billion computers. (The billionth-and- first computer could be the one with the non-unique ID.) You need a mathematical proof to prove uniqueness. -Original Message- From: Velen [mailto:[EMAIL PROTECTED] Sent: Monday, May 12, 2008 8:32 AM To: Arthur Fuller Cc: mysql@lists.mysql.com Subject: Re: Help needed Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? Thanks. Velen On Sat, 2008-05-10 at 20:59 +0400, Velen wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. Please supply a Linux version of your virus so I can test. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au - Original Message - From: Arthur Fuller To: Velen Sent: Saturday, May 10, 2008 10:44 PM Subject: Re: Help needed On my main machine the PCID is 135184-45-4-10-1513-1. Hope it helps. Arthur On Sat, May 10, 2008 at 12:59 PM, Velen [EMAIL PROTECTED] wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. It will display an ID, please mail me back the ID. If you can use it on several computers, it will be even better for me, I need to have as much results as possible. The program i'm testing is supposed to create a unique ID for each PC, this is why I need to test if it is really unique. This file is virus free and cannot do any harm to your PC. It is however a voluntary testing. Thanks for your help in advance. Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed
Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? Thanks. Velen On Sat, 2008-05-10 at 20:59 +0400, Velen wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. Please supply a Linux version of your virus so I can test. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au - Original Message - From: Arthur Fuller To: Velen Sent: Saturday, May 10, 2008 10:44 PM Subject: Re: Help needed On my main machine the PCID is 135184-45-4-10-1513-1. Hope it helps. Arthur On Sat, May 10, 2008 at 12:59 PM, Velen [EMAIL PROTECTED] wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. It will display an ID, please mail me back the ID. If you can use it on several computers, it will be even better for me, I need to have as much results as possible. The program i'm testing is supposed to create a unique ID for each PC, this is why I need to test if it is really unique. This file is virus free and cannot do any harm to your PC. It is however a voluntary testing. Thanks for your help in advance. Velen
RE: Help needed
Sorry, you can't prove uniqueness by running it against a hundred thousand, million, or even a billion computers. (The billionth-and-first computer could be the one with the non-unique ID.) You need a mathematical proof to prove uniqueness. -Original Message- From: Velen [mailto:[EMAIL PROTECTED] Sent: Monday, May 12, 2008 8:32 AM To: Arthur Fuller Cc: mysql@lists.mysql.com Subject: Re: Help needed Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? Thanks. Velen On Sat, 2008-05-10 at 20:59 +0400, Velen wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. Please supply a Linux version of your virus so I can test. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au - Original Message - From: Arthur Fuller To: Velen Sent: Saturday, May 10, 2008 10:44 PM Subject: Re: Help needed On my main machine the PCID is 135184-45-4-10-1513-1. Hope it helps. Arthur On Sat, May 10, 2008 at 12:59 PM, Velen [EMAIL PROTECTED] wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. It will display an ID, please mail me back the ID. If you can use it on several computers, it will be even better for me, I need to have as much results as possible. The program i'm testing is supposed to create a unique ID for each PC, this is why I need to test if it is really unique. This file is virus free and cannot do any harm to your PC. It is however a voluntary testing. Thanks for your help in advance. Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help needed
Your best bet would be to use a UUID: SELECT UUID(); +--+ | uuid() | +--+ | c712dc72-718d-102b-b3c8-97395a1766b9 | +--+ There are equivalents in various programming languages, although not in PHP. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Garris, Nicole [mailto:[EMAIL PROTECTED] Sent: Monday, May 12, 2008 11:59 AM To: mysql@lists.mysql.com Subject: RE: Help needed Sorry, you can't prove uniqueness by running it against a hundred thousand, million, or even a billion computers. (The billionth-and-first computer could be the one with the non-unique ID.) You need a mathematical proof to prove uniqueness. -Original Message- From: Velen [mailto:[EMAIL PROTECTED] Sent: Monday, May 12, 2008 8:32 AM To: Arthur Fuller Cc: mysql@lists.mysql.com Subject: Re: Help needed Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? Thanks. Velen On Sat, 2008-05-10 at 20:59 +0400, Velen wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. Please supply a Linux version of your virus so I can test. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au - Original Message - From: Arthur Fuller To: Velen Sent: Saturday, May 10, 2008 10:44 PM Subject: Re: Help needed On my main machine the PCID is 135184-45-4-10-1513-1. Hope it helps. Arthur On Sat, May 10, 2008 at 12:59 PM, Velen [EMAIL PROTECTED] wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. It will display an ID, please mail me back the ID. If you can use it on several computers, it will be even better for me, I need to have as much results as possible. The program i'm testing is supposed to create a unique ID for each PC, this is why I need to test if it is really unique. This file is virus free and cannot do any harm to your PC. It is however a voluntary testing. Thanks for your help in advance. Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed
I tested the program before running it and it is virus-clean. I ran it through AVG first and it's clean. I ran it in Windows. I don't know whether there is a Linux version. Arthur On Mon, May 12, 2008 at 11:31 AM, Velen [EMAIL PROTECTED] wrote: Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? Thanks. Velen
Re: Help needed
On Mon, May 12, 2008 at 8:31 AM, Velen [EMAIL PROTECTED] wrote: Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? One guy that is brave/crazy/stupid enough to run a binary on his machine that didn't have anything bad happen to him does not make it safe or a good idea for the rest of us. And for all we know, you could have two email accounts just to trick the rest of us into running your trojan/keylogger/virus/botnet/whatever. Not a chance.
Re: Help needed
On Sat, 2008-05-10 at 20:59 +0400, Velen wrote: Hi, I'm testing a program and I need you assistance. Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program. Please supply a Linux version of your virus so I can test. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
Hi all, You can read this article, written by a SUN benchmarking guru (hi Dimitri :) ). Best regards. Web link : http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi [EMAIL PROTECTED] wrote: The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Is autocommit turned off? http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0; ... SQL import statements ... COMMIT; There is also a note in there about forcedirectio and certain solaris setups. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
Il Wednesday 09 April 2008 19:40:32 Rob Wultsch ha scritto: On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi [EMAIL PROTECTED] wrote: The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Is autocommit turned off? http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0; ... SQL import statements ... COMMIT; yes, autocommit is off There is also a note in there about forcedirectio and certain solaris setups. I read it, but directio seems to be unavailable on ZFS (as fas as I can tell) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP needed for speeding up a query!
Nicholas Wyatt wrote: Hello to all! has anybody got any ideas how i can speed up the following query? it's so awfully slow (about 1 second). the test_item table is the main problem. currently, it has about 108.000 entries. is it normal that it takes so long? and if it isn't, how can i optimize the query? i've got indices on all relevant columns. SELECT site.site_id, site.site_title, site.site_testtype, test.test_kt_points, MIN(test_item.ti_evaluation) as completed, IF (site.site_date site.site_enddate, site.site_enddate, site.site_date) AS sort_date FROM site LEFT JOIN test ON (site.site_id = test.site_id) LEFT JOIN test_item ON (test.test_id = test_item.test_id) WHERE site.site_testtype IN (-1, 0, 1, 2) GROUP BY site.site_id Do you have indexes on these tables/columns: site.site_id, test.site_id, test.test_id, test_item.test_id ? What sort of tables are they (innodb or myisam)? That will make a difference mainly because of the MIN() call. Try creating an index on test_item.ti_evaluation: create index ti_evaluation on test_item(ti_evaluation) or even create index ti_evaluation on test_item(test_id, ti_evaluation) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP needed for speeding up a query!
[ always cc the list so others can share the fix or make appropriate comments ] Nicholas Wyatt wrote: hi chris, thanks for answering! however, i do already have indexes on those columns. all my tables use the myisam storage engine. what are the differences you mentioned between these engines when using MIN()? myisam is a non-transactional table type, so only 1 client can update/delete/insert into it at once (other clients attempting to do this will be locked until the insert/update/delete finishes). That means the table itself can keep information about the largest value, the smallest value and the number of rows the table has. innodb is a transactional table type, which means many clients can update/delete/insert into it at once. That means the table cannot keep information about values and the number of rows because at any time it could change in another client. http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html i did find a way to speed up the query, by the way. i joined the tables the other way around. the query now takes about 0,5 seconds. SELECT site.site_id, site.site_title, site_url, site.site_testtype, site.project_id, test.test_kt_points, MIN(test_item.ti_evaluation) as completed, IF (site.site_date site.site_enddate, site.site_enddate, site.site_date) AS sort_date FROM test_item LEFT JOIN test ON (test.test_id = test_item.test_id) LEFT JOIN site ON (site.site_id = test.site_id) WHERE site.site_testtype IN (-1, 0, 1, 2) GROUP BY site.site_id Interesting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help needed to mount data from /var/lib/mysql
On 8/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need small help from you. In my Linux box i have limitation of Size in partition. I have only 5 GB space for /var. MySQL is installed in this partition only. I want to give another path like /home, where i have 120GB of space. Somebody told me that we need to change the data path of MySQL, but he is not sure of that. Can anybody knows abt this? Edit the my.cnf and change the data path. Then read this page: http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html about how to set up your new environment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help needed to mount data from /var/lib/mysql
Hello, You can change the MySQL data path in /etc/my.cnf by editing the configuration parameter datadir with new data path. Once you done the changes in my.cnf, reboot the MySQL server. Thanks, ViSolve DB Team - Original Message - From: balaraju mandala [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, August 19, 2006 12:28 PM Subject: help needed to mount data from /var/lib/mysql Hi Everybody, I need small help from you. In my Linux box i have limitation of Size in partition. I have only 5 GB space for /var. MySQL is installed in this partition only. I want to give another path like /home, where i have 120GB of space. Somebody told me that we need to change the data path of MySQL, but he is not sure of that. Can anybody knows abt this? No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.3/423 - Release Date: 8/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help needed to mount data from /var/lib/mysql
Hi All, Thank you for u r reply. But i am unable to find my.cnf, is i need to create this file.
Re: help needed to mount data from /var/lib/mysql
I am unable to start server after shifting to new location. i tried to start 'mysqld' but it was failed. A blank mysql.sock file is creating. Entries of log files are also not reporting any problem.
Re: help needed to mount data from /var/lib/mysql
if i search for any process running i am getting following thing. [EMAIL PROTECTED] mysql]# ps -ef | grep mysqld root 18389 1 0 13:09 pts/300:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid mysql18422 18389 0 13:09 pts/300:00:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/home/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/home/mysql/mysql.sock root 18725 15850 0 13:47 pts/300:00:00 grep mysqld i am new to this concept please help me.
Re: Help Needed
If you do an INSERT and you generate an new number in an AUTO_INCREMENT field the new value generated can be retrieved using: *SELECT LAST_INSERT_ID();* You are not required to retrieve into the application and then use it back in another SQL statement... you can use user variables: *SELECT @lastGenerated:=LAST_INSERT_ID();* And then use: the variable in another query: *INSERT INTO someTbl (id, someCol, somCol2) VALUES (NULL, @lastGenerated, 'OutsideValue'); * Be warned that LAST_INSERT_ID() is functional only if you did not specified the AUTO_INCREMENT field's value... only if you did not specified the AUTO_INCREMENT field at all... or in the insert statement you used NULL or 0 as a value... those values are considered magic by MySQL in case of an AUTO_INCREMENT field. Now part 2... if you didn't created an AUTO_INCREMENT field... and only want to use the last value from that table: Either you use a MAX() request: *SELECT @lastGenerated:=MAX(columnName) FROM tableName* Or: *SHOW TABLE STATUS LIKE 'tableName'* and somewhere in the result is the AUTO_INCREMENT value. Good luck ! -- Gabriel PREDA Senior Web Developer
Re: Help needed for SQL statement
Is this what you are looking for: SELECT clone_ids, COUNT(DISTINCT(gene_ids)) as count_genes FROM table_name GROUP BY clone_ids or SELECT clone_ids, COUNT(gene_ids) as count_genes FROM table_name GROUP BY clone_ids Hope this helps, Alvaro - Original Message - From: Xiaobo Chen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 24, 2005 11:43 PM Subject: Help needed for SQL statement Hi, I have such a situation: There is a table with gene_ids and clone_ids. Each gene only resides on a single clone and each clone may contain multiple genes. How do would I find how many genes are on each and every clone? Thanks in advance. X.Chen -- 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: Help needed regarding MySQL-C Libraries on Linux....
Hello. This is an output of diff user.c user.cpp. Which successfully compiles on my FC3 system using the following command: g++ user.cpp -I/usr/include/mysql -lmysqlclient I've changed the extension to 'cpp', so g++ could work with this file. Also #includeconio.h was commented, and main now returns int. [EMAIL PROTECTED] pgm]$ diff user.c user.cpp 2c2 #include conio.h --- //#include conio.h 17c17 void main() --- int main() 52a53 Ashok Kumar [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 29 lines --] Dear Friends, I'm Ashok here. first i'll tell my sys configuration. 1.Redhat-Linux 9 2.Intel Pentium chipset 3.MySQL 4.1 with Development Libraries For this i've downloaded the MySQL4.1 under the specification Linux x86 RPM downloads from the site. In my system, the MySQL Server is working well. I tried to run the C program that will make the connection to the MySQL database and create the user database and useracc table. It's finely working with Windows-VC++. But when i'm trying to run with Linux gcc, I'm getting lots of errors. I don't know what's the problem. I've attached the C-Pgm and the Error info. Pls, help me regarding this. Thanks and Regards, Ashok. Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com [-- application/zip, encoding base64, 97 lines, name: Pgm_Err.zip --] [-- Description: 3707653052-Pgm_Err.zip --] [-- text/plain, encoding 7bit, charset: us-ascii, 5 lines --] -- 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: Help needed regarding MySQL-C Libraries on Linux....
The same source that you compiled under VC++ may not work at all under linux. Especially since the header files will be different. Make sure you have the proper header files for linux installed. Also I did not see any linking flags during compilation. On 7/3/05, Ashok Kumar [EMAIL PROTECTED] wrote: Dear Friends, I'm Ashok here. first i'll tell my sys configuration. 1.Redhat-Linux 9 2.Intel Pentium chipset 3.MySQL 4.1 with Development Libraries For this i've downloaded the MySQL4.1 under the specification Linux x86 RPM downloads from the site. In my system, the MySQL Server is working well. I tried to run the C program that will make the connection to the MySQL database and create the user database and useracc table. It's finely working with Windows-VC++. But when i'm trying to run with Linux gcc, I'm getting lots of errors. I don't know what's the problem. I've attached the C-Pgm and the Error info. Pls, help me regarding this. Thanks and Regards, Ashok. Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help needed regarding MySQL-C Libraries on Linux....
Hi Prashanth, I'm happy to help u. i attached one 'C' code in the previous mail. just use that code for in vc++. Follow the procedure, 1.Create the new win32 Console App. as a empty project and add the attached 'C' file to the project. 2.goto Tools-options-Directories(tab) and in that add the corresponding MySQL Include path and as well as the lib-debug path. 3.goto Project-settings-Link(tab)-General Category-add the name 'libmysql.lib' in lib modules 4.Save the project. 5.Add the MySQL lib-debug path to the environment path variable, which is found in ctrl-panel-system-Advanced-env.var-path (for the dynamic linking of libmysql.dll file). 6.Restart the system (for effect of env path var). 7.Run the project. This c pgm will create one database called user and table named useracc under the db user. Note: Don't forget to change the username and password vars in the pgm if u set anything like that for ur MySQL. For further info on C-MySQL read the following page, some sample chapters are there, that will be useful. http://www.quepublishing.com/articles/article.asp?p=30494seqNum=2rl=1 If u have any doubts means, send that to the group, rather than sending to the seperate person and so that reply will be useful for others also. Thanks and Regards, Ashok. --- Prashanth H. Baragur [EMAIL PROTECTED] wrote: Ashok, Can u do me a favor. Am trying to update the mysql server in windows using VC++ and C code. Am very new to this domain. Can u please send the code u are using in VC++ to me? Your help would be greatly appreciate. Thanks prashanth -Original Message- From: Ashok Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 3:34 PM To: mysql mysql Subject: Help needed regarding MySQL-C Libraries on Linux Dear Friends, I'm Ashok here. first i'll tell my sys configuration. 1.Redhat-Linux 9 2.Intel Pentium chipset 3.MySQL 4.1 with Development Libraries For this i've downloaded the MySQL4.1 under the specification Linux x86 RPM downloads from the site. In my system, the MySQL Server is working well. I tried to run the C program that will make the connection to the MySQL database and create the user database and useracc table. It's finely working with Windows-VC++. But when i'm trying to run with Linux gcc, I'm getting lots of errors. I don't know what's the problem. I've attached the C-Pgm and the Error info. Pls, help me regarding this. Thanks and Regards, Ashok. Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed regarding MySQL-C Libraries on Linux....
Ashok Kumar wrote: Dear Friends, I'm Ashok here. first i'll tell my sys configuration. 1.Redhat-Linux 9 2.Intel Pentium chipset 3.MySQL 4.1 with Development Libraries For this i've downloaded the MySQL4.1 under the specification Linux x86 RPM downloads from the site. In my system, the MySQL Server is working well. I tried to run the C program that will make the connection to the MySQL database and create the user database and useracc table. It's finely working with Windows-VC++. But when i'm trying to run with Linux gcc, I'm getting lots of errors. I don't know what's the problem. I've attached the C-Pgm and the Error info. [EMAIL PROTECTED] CGI]# gcc -c -I/usr/lib/bcc/include -I/usr/include/mysql user.c is BCC the 8086 compiler ? then I'm not sure gcc can deal with those includes. To get the proper flags, I'll advise you to use the mysql_config program like : /usr/local/mysql/bin/mysql_config --cflags (you didn't attach your program, so I can't see what is going on) -- 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: Help needed with complex Query
Philip Lawatsch [EMAIL PROTECTED] wrote on 06/06/2005 01:37:37 PM: Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this as a starting point: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2; Wherever the column `number_of_parts` equals the computed value of part_count, you have a complete widget. Here is a query that returns only completed widgets: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2 HAVING `number_of_parts` = `part_count`; Here is one that returns incomplete widgets: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2 HAVING `number_of_parts` `part_count`; Here is the query that tell you that construction on these widgets hasn't even started: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2 HAVING `part_count` = 0; I think you were having two mental problems: 1) how to GROUP BY across tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer To the Fine Manual (RTFM) you can get examples and more explanations of both processes. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help needed with complex Query
Philip Lawatsch wrote: Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this: SELECT widgets.widget-id, number_of_parts, count(partid) AS cnt FROM widgets INNER JOIN part_mapping ON widgets.widget-id = part_mapping.widget-id GROUP BY widgets.widget-id, number_of_parts HAVING cnt = number_of_parts; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
Unless you are keeping track of whether a widget in complete or not, there is no hope but to scan the entire table to determine if a widget is complete. That's something you don't want to do. You should mark a widget as complete when it is completed. This would mean checking if a particular widget is completed when a modification occurs. Really what you are going to be doing is running the loop you spoke of in bits and pieces of time and only for widgets that require checking/updating. Then you only need to query on the completed field, which will be very fast. To do it in a single query, you going to need to use count and group by and then check for completion using HAVING (as opposed to where). On Jun 6, 2005, at 1:37 PM, Philip Lawatsch wrote: Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
[EMAIL PROTECTED] wrote: I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this as a starting point: snip I think you were having two mental problems: 1) how to GROUP BY across tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer To the Fine Manual (RTFM) you can get examples and more explanations of both processes. Thanks a lot, this did the trick! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help needed to create index
Asha wrote: Is there a physical limitation in the InnoDb table structure as to why it can't support FullText indexes? http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed in creating primary key ,foreign key on a varchar datatype colum
Dayakar, - Original Message - From: Dayakar [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, November 21, 2004 7:39 AM Subject: Help needed in creating primary key ,foreign key on a varchar datatype colum --=_NextPart_000_000B_01C4CFBA.91C9BA80 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hello, I am converting my database from oracle to mysql4.1 and i want the same = structure as it is oracle like primary key and foreign key references = etc..=20 In oracle i have created my primary key and foreign key references on a = varchar datatype column, so can any one help me in doing the same in = mysql. I have tried doing the same by seeing the examples given in mysql = manual but not successfull. If any one can help me by giving a example then it would be great help = for me. [EMAIL PROTECTED]:~/mysql-4.1/client ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.8-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE t(a VARCHAR(100) NOT NULL PRIMARY KEY, b INT) TYPE = INNODB; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql CREATE TABLE t1(a1 INT NOT NULL PRIMARY KEY, b1 VARCHAR(50), FOREIGN KEY (b1) REFERENCES t(a)) TYPE=INNODB; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql regards dayakar Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed in creating primary key ,foreign key on a varchar datatype colum
I just checked the definition of CREATE TABLE in the MySQL manual http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html and I didn't see anything that suggests that primary keys based on VARCHAR columns need to be treated differently than other column types in MySQL. However, when I tried to create a table based on a VARCHAR(500) column, I got a message that said: ERROR 1074: Too big column length for column 'account_number' (max = 255). Use BLOB instead Therefore, if your column is more than 255 characters long, you will have to use BLOB instead of VARCHAR. If your column is 255 or fewer characters long, change your column definition to CHAR from VARCHAR. Also, I believe you will have to include the phrase 'Type=INNODB' in your table definition; I think only the INNODB engine actually enforces FOREIGN KEY constraints. Here is an example of creating two tables, a parent and a child, that use the INNODB engine: drop table if exists dept; create table dept( deptno char(3) not null, deptname varchar(36) not null, mgrno char(6), primary key(deptno) ) Type=InnoDB; drop table if exists emp; create table emp( empno char(6) not null, firstnme char(12) not null, midinit char(1), lastname char(15) not null, workdept char(3) not null, salary dec(9,2) not null, primary key(empno), index(workdept), foreign key(workdept) references dept(deptno) on delete restrict ) Type=InnoDB; Table 'dept' is the parent table and contains one row for each department of an imaginary company. Table 'emp' is the child table and contains one row for each employee of the imaginary company. Each row in the 'emp' table has a department number in its 'workdept' column; 'workdept' is the foreign key and points to the 'deptno' column of the 'dept' table. Rhino - Original Message - From: Dayakar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 21, 2004 12:39 AM Subject: Help needed in creating primary key ,foreign key on a varchar datatype colum Hello, I am converting my database from oracle to mysql4.1 and i want the same structure as it is oracle like primary key and foreign key references etc.. In oracle i have created my primary key and foreign key references on a varchar datatype column, so can any one help me in doing the same in mysql. I have tried doing the same by seeing the examples given in mysql manual but not successfull. If any one can help me by giving a example then it would be great help for me. regards dayakar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with MySQL C API-based client (segfault)
have you tried ?
http://www.geocities.com/jahan.geo/mysql_c_by_example.html
Ruben Safir Secretary NYLXS wrote:
On Fri, Sep 19, 2003 at 09:18:22AM +0500, Vikram Vaswani wrote:
Hello,
I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?
You know, I used to read this mailing list religiously before it became
flooded with W32 questions and PHP users. And I've stay susbscribed
but I haven't posted to it in many many months. iIn fact, I didn't notice
I wasn't any longer subscribed. So I'm writing this application in C
and GTK and I was thinking, it's finally time to learn to write some MYSQL
C API stuff.
I've written a lot of Oracle C programs in years past. And now I'm looking
at the C API stuff and wow, it is not readly understandable. I open up
the mysql mail file with mutt, and bang, this is on the top!
I was going to ask the list if anyone has an exmaple of the basic needs
for a MYSQL program which makes a connection, sends a querry. Checks the
potention errors, and maps the most basic column types to C types.
The docs say to look at examples in the source directory, but those
aren't yet clear to me to understand.
Ruben
/* client.c */
#include stdio.h
#include mysql.h
int main()
{
/* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD
*field;
/* initialize MYSQL structure */
mysql_init(mysql);
/* connect to database */
if
(!(mysql_real_connect(mysql, NULL, root, , db1, 0, NULL, 0)))
{
fprintf(stderr, Error in connection: %s\n,
mysql_error(mysql));
}
for( ;; )
{
printf(query? );
gets(query);
if (strcmp(query,exit)
== 0)
{
break;
}
/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, Error in query: %s\n, mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, %s, row[j]);
j != (numFields-1) ? printf(, ) : printf(\n);
}
}
fprintf(stdout, ** Query successful, %d rows retrieved **\n,
numRecords);
}
else
{
if (mysql_field_count(mysql) == 0)
{
/* non-SELECT query */
fprintf(stdout, ** Query successful, %d
rows affected **\n, mysql_affected_rows(mysql));
}
else
{
fprintf(stderr, Error in reading result set: %s\n,
mysql_error(mysql));
}
}
}
/* clean up */
mysql_free_result(result);
}
mysql_close(mysql);
}
--
I wouldn't recommend sex, drugs, and insanity for everyone, but it works
for me.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Aftab Jahan Subedar
CEO/Software Engineer
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
http://www.SubedarTechnologies.com
http://www.DhakaStockExchangeGame.com/
http://www.CEOBangladesh.com/
http://www.NYSEGame.com
tel://+88027519050
EMail://[EMAIL PROTECTED] - Directly to my notebook
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with MySQL C API-based client (segfault)
On Fri, Sep 19, 2003 at 09:18:22AM +0500, Vikram Vaswani wrote:
Hello,
I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?
You know, I used to read this mailing list religiously before it became
flooded with W32 questions and PHP users. And I've stay susbscribed
but I haven't posted to it in many many months. iIn fact, I didn't notice
I wasn't any longer subscribed. So I'm writing this application in C
and GTK and I was thinking, it's finally time to learn to write some MYSQL
C API stuff.
I've written a lot of Oracle C programs in years past. And now I'm looking
at the C API stuff and wow, it is not readly understandable. I open up
the mysql mail file with mutt, and bang, this is on the top!
I was going to ask the list if anyone has an exmaple of the basic needs
for a MYSQL program which makes a connection, sends a querry. Checks the
potention errors, and maps the most basic column types to C types.
The docs say to look at examples in the source directory, but those
aren't yet clear to me to understand.
Ruben
/* client.c */
#include stdio.h
#include mysql.h
int main()
{
/* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD
*field;
/* initialize MYSQL structure */
mysql_init(mysql);
/* connect to database */
if
(!(mysql_real_connect(mysql, NULL, root, , db1, 0, NULL, 0)))
{
fprintf(stderr, Error in connection: %s\n,
mysql_error(mysql));
}
for( ;; )
{
printf(query? );
gets(query);
if (strcmp(query,exit)
== 0)
{
break;
}
/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, Error in query: %s\n, mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, %s, row[j]);
j != (numFields-1) ? printf(, ) : printf(\n);
}
}
fprintf(stdout, ** Query successful, %d rows retrieved **\n,
numRecords);
}
else
{
if (mysql_field_count(mysql) == 0)
{
/* non-SELECT query */
fprintf(stdout, ** Query successful, %d
rows affected **\n, mysql_affected_rows(mysql));
}
else
{
fprintf(stderr, Error in reading result set: %s\n,
mysql_error(mysql));
}
}
}
/* clean up */
mysql_free_result(result);
}
mysql_close(mysql);
}
--
I wouldn't recommend sex, drugs, and insanity for everyone, but it works
for me.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
__
Brooklyn Linux Solutions
So many immigrant groups have swept through our town
that Brooklyn, like Atlantis, reaches mythological
proportions in the mind of the world - RI Safir 1998
DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002
http://fairuse.nylxs.com
http://www.mrbrklyn.com - Consulting
http://www.inns.net -- Happy Clients
http://www.nylxs.com - Leadership Development in Free Software
http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from
around the net
http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn
1-718-382-0585
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with a select statement
What you want to do is pivot your data into a crosstab report. This has been covered several times so you should be able to find more information if you search on either pivot table, crosstab or cross tab. Here's how to do it for a week's worth of dates, I will let you extend it to fit your needs. And, before you ask, there is no generic solution to this issue in MySQL. It is a fairly simple pattern which makes dynamic solutions fairly easy to script. SELECT error , SUM(if(date='2004-07-08',1,0)) as '2004-07-08' , SUM(if(date='2004-07-09',1,0)) as '2004-07-09' , SUM(if(date='2004-07-10',1,0)) as '2004-07-10' , SUM(if(date='2004-07-11',1,0)) as '2004-07-11' , SUM(if(date='2004-07-12',1,0)) as '2004-07-12' , SUM(if(date='2004-07-13',1,0)) as '2004-07-13' , SUM(if(date='2004-07-14',1,0)) as '2004-07-14' FROM tablename GROUP BY error; I know it seems clumsy to need to define every column but it actually processes rather quickly. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jonas Fischer [EMAIL PROTECTED] wrote on 08/03/2004 03:39:39 PM: Hello right now I have a table which looks like this (including duplicate entries) ErrorDate A0012004-07-08 A001 2004-07-08 A222 2004-07-08 A001 2004-07-09 Now I query for the quantity one error occurs during a day (select Date, Error, Count(*) FROM table1 GROUP BY Date and Error). The result looks like this: Date Error Quantity 2004-07-08 A001 2 2004-07-08 A222 1 2004-07-09 A001 1 What I would like to have right now is a result which looks like this: Date 2004-07-08 2004-07-08 Error A001 1 1 A222 2 0 As a header I would like to have the dates and than display the error on the left side and the quantity of how often the error occures beneath under each date. My problem is, that I do not always have an entry for every error per day. Anybody knows how to do that? Thank you very much for your help. Jonas ___ Gesendet von Yahoo! Mail - Jetzt mit 100MB Speicher kostenlos - Hier anmelden: http://mail.yahoo.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help needed!
Hi, I need to add a new auto field to my db, but I need to make sure the records are listed in order of one of the other fields first which is a date field. Any ideas how I do this would be greatly appreciated! listing records is in no particular order, unless you ask for an order. SELECT ... column names ... FROM ... table name(s) ... WHERE ... where clause ... ORDER BY MyDateColumn With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help needed!
Hi, (please reply to the list only) Yes thanks but how do I then add a new auto number field and still keep the order? What order? What has an auto-number have to do with what order? Please explain your problem in detail - it might be completely clear to you, but we're outsiders :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help needed!
Hi Dominic, I think everyone is misunderstanding my problem as perhaps I didnt phrase it very well. I need to add a new autonumber field to my db, but I need to make sure when the numbers go into the field they are in the order of one of my other fields that is a date field. i.e. I have a field which shows the date someone became a member - but my current ID field doesnt match in order. So I need to add a new ID field so that numbers 1 - say 100 are say 1st Jan 00 then numbers 101 - say 201 are say 2nd Jan 00 etc. etc. So that the ID fields order matches the dates order. Now I can write SELECT * FROM table ORDER BY STARTDATE which orders the db correctly, but then how do I add an autonumber field to it and keep the records in that order? Right - now things _are_ more clear :-) Nevertheless, I have to ask you: why is this a requirement? What is the auto-inc column supposed to be: if it's only a unique identifier, what does the order matter? As for a solution, I think the best thing you can do is rename this table, create a new table with the same strucutre, but WITH an auto-inc column and then do a INSERT INTO... SELECT ... FROM oldtable ORDER BY datecolumn. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed
* Unnar I have a problem with this query Version MySQL 4.0.15-nt The query gets all articles written by author but excludes those articles which are related to products SELECT DISTINCT post.post_id, post.title FROM c_posts post, c_post_product prod, authors_relation rel, c_post_properties prop WHERE rel.post_id != '$nPostId' // This is the post_id of the post which is currently being viewed ( works ) AND rel.author_id = '$nAuthorId' // The author_id of the Author AND post.post_id = rel.post_id // To connect the two tables AND post.post_id = prop.post_id // The properties table AND post.post_id prod.post_id // This is where the problem is. I've also tried != . Details below** AND prop.state != 3 ** I don't get any errors but the query just doesn't Exclude those post_id's which are in the c_post_product table which should be the case. Try a LEFT JOIN, and check if prod.post_id is NULL: SELECT post.post_id, post.title FROM c_posts post authors_relation rel, c_post_properties prop LEFT JOIN c_post_product prod ON post.post_id = prod.post_id WHERE rel.post_id != '$nPostId' // This is the post_id of the post which is currently being viewed ( works ) AND rel.author_id = '$nAuthorId' // The author_id of the Author AND post.post_id = rel.post_id // To connect the two tables AND post.post_id = prop.post_id // The properties table AND prod.post_id IS NULL AND prop.state != 3 URL: http://www.mysql.com/doc/en/JOIN.html ** I've also tried the NOT IN function, but I get errors when trying that. Seems like it's not supported by my version ?? Well... the IN and NOT IN operators are available, but the operand is a list, you can't use sub-queries in 4.0: URL: http://www.mysql.com/doc/en/Comparison_Operators.html#IDX1177 URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with MySQL C API-based client (segfault)
Here is the corrected version:)
Take a look at Paul DuBois book. Don't use gets. Instead use fgets. That's
safe. I didn't change your gets call:)
hth,
Ganbold
#include stdio.h
#include stdlib.h
#include string.h
#if defined(_WIN32) || defined(_WIN64)
#include windows.h
#endif
#ifdef WIN32
#pragma comment(lib,ws2_32)
#include d:\mysql\include\mysql.h
#else
#include /usr/local/include/mysql/mysql.h
#endif
int main(void)
{
char query[255];
int i, j, count;
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *field;
/* initialize MYSQL structure */
mysql = mysql_init(NULL);
if(mysql==NULL){
fprintf(stderr, mysql_init failed! Error %u
(%s)\n,mysql_errno(mysql),mysql_error(mysql));
exit(1);
}
if(mysql_real_connect(mysql,localhost,root,test,test,3306,NULL,0)==NULL){
fprintf(stderr, mysql_real_connect failed! Error %u
(%s)\n,mysql_errno(mysql),mysql_error(mysql));
exit(1);
}
for( ;; )
{
printf(query? );
gets(query);
if (strcmp(query,exit)== 0)
{
break;
}
/* execute query */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, Error in query: %s\n, mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT query */
/* retrieve result set */
int numRecords = (int)mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i numRecords; i++)
{
row = mysql_fetch_row(result);
for (j = 0; j numFields; j++)
{
fprintf(stdout, %s, row[j]);
j != (numFields-1) ? printf(, ) : printf(\n);
}
}
fprintf(stdout, ** Query successful, %d
rows retrieved **\n,numRecords);
}
else
{
if (mysql_field_count(mysql) == 0)
{
/* non-SELECT query */
fprintf(stdout, ** Query successful, %d rows affected
**\n, mysql_affected_rows(mysql));
}
else
{
fprintf(stderr, Error in reading result
set: %s\n,mysql_error(mysql));
}
}
}
/* clean up */
mysql_free_result(result);
}
mysql_close(mysql);
return 0;
}
At 09:18 AM 9/19/2003 +0500, you wrote:
Hello,
I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?
/* client.c */
#include stdio.h
#include mysql.h
int main()
{
/* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD
*field;
/* initialize MYSQL structure */
mysql_init(mysql);
/* connect to database */
if
(!(mysql_real_connect(mysql, NULL, root, , db1, 0, NULL, 0)))
{
fprintf(stderr, Error in connection: %s\n,
mysql_error(mysql));
}
for( ;; )
{
printf(query? );
gets(query);
if (strcmp(query,exit)
== 0)
{
break;
}
/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, Error in query: %s\n,
mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, %s,
row[j]);
j != (numFields-1) ? printf(, ) : printf(\n);
}
}
fprintf(stdout, ** Query
RE: Help needed with SQL...
On 26-Feb-2003 M Wells wrote: Hi All, I'm having problems creating a query to return a recordset from the following situation: I have two tables, one which contains member details, and the other which contains details re: the groups to which the member belongs. When a member logs into a particular part of my site, I want to show them the details of the other people who belong to the same groups they do. snip So, if I passed the query the memberid value of '1', I'd like to return a a unique records recordset of: 1, john doe, [EMAIL PROTECTED] 2, jane doe, [EMAIL PROTECTED] 4, inigo montoya, [EMAIL PROTECTED] Can anyone help me work out how to achieve this? mysql select * from members; ++---+--+ | id | name | email| ++---+--+ | 1 | john doe | [EMAIL PROTECTED] | | 2 | jane doe | [EMAIL PROTECTED] | | 3 | joe bloggs| [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+--+ mysql select * from groups; +---+--+ | groupname | memberid | +---+--+ | group1|1 | | group1|2 | | group2|1 | | group2|4 | | group3|3 | +---+--+ 5 rows in set (0.00 sec) mysql SELECT DISTINCT members.* FROM members, groups as a, groups as b - WHERE a.groupname=b.groupname AND members.id=b.memberid - AND a.memberid=1; ++---+-+ | id | name | email | ++---+-+ | 1 | john doe | [EMAIL PROTECTED]| | 2 | jane doe | [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+-+ 3 rows in set (0.01 sec) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed with SQL...
Yes , I had use this query statement in my MySQL server, But I have a query about it. Why are you use WHERE a.groupname=b.groupname AND members.id=b.memberid AND a.memberid=1? Can you give me a explain or give me a advise! Thanks in advantage for help! -Original Message- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 4:05 PM To: M Wells Cc: [EMAIL PROTECTED] Subject: RE: Help needed with SQL... On 26-Feb-2003 M Wells wrote: Hi All, I'm having problems creating a query to return a recordset from the following situation: I have two tables, one which contains member details, and the other which contains details re: the groups to which the member belongs. When a member logs into a particular part of my site, I want to show them the details of the other people who belong to the same groups they do. snip So, if I passed the query the memberid value of '1', I'd like to return a a unique records recordset of: 1, john doe, [EMAIL PROTECTED] 2, jane doe, [EMAIL PROTECTED] 4, inigo montoya, [EMAIL PROTECTED] Can anyone help me work out how to achieve this? mysql select * from members; ++---+--+ | id | name | email| ++---+--+ | 1 | john doe | [EMAIL PROTECTED] | | 2 | jane doe | [EMAIL PROTECTED] | | 3 | joe bloggs| [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+--+ mysql select * from groups; +---+--+ | groupname | memberid | +---+--+ | group1|1 | | group1|2 | | group2|1 | | group2|4 | | group3|3 | +---+--+ 5 rows in set (0.00 sec) mysql SELECT DISTINCT members.* FROM members, groups as a, groups as b - WHERE a.groupname=b.groupname AND members.id=b.memberid - AND a.memberid=1; ++---+-+ | id | name | email | ++---+-+ | 1 | john doe | [EMAIL PROTECTED]| | 2 | jane doe | [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+-+ 3 rows in set (0.01 sec) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed with SQL...
On 26-Feb-2003 [EMAIL PROTECTED] wrote: Yes , I had use this query statement in my MySQL server, But I have a query about it. Why are you use WHERE a.groupname=b.groupname AND members.id=b.memberid AND a.memberid=1? Can you give me a explain or give me a advise! Sure. mysql SELECT DISTINCT members.* FROM members, groups as a, groups as b - WHERE a.groupname=b.groupname AND members.id=b.memberid - AND a.memberid=1; The 'a.memberid=1' clause looks up the groups that member 1 belongs to in the groups table (as a), giving 'group1' 'group2'. Then it joins back against the groups table (as b) with the 'a.groupname=b.groupname' clause to get the folks that belong to these group(s). Finally the 'members.id=b.memberid' bit selects the records out of the members table, with the DISTINCT function suppressing any duplicates. Clear as mud? (I could've be a little more obvious if I'd put the 'a.memberid=1' clause first. Sorry ...) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with SQL...
I've never seen opening a table twice and then doing a join back with itself. Am I wording this correctly? Is that how to describe what you're doing? Thanks for posting this to the full list. - Sheryl - Original Message - From: Don Read [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 5:11 AM Subject: RE: Help needed with SQL... On 26-Feb-2003 [EMAIL PROTECTED] wrote: Yes , I had use this query statement in my MySQL server, But I have a query about it. Why are you use WHERE a.groupname=b.groupname AND members.id=b.memberid AND a.memberid=1? Can you give me a explain or give me a advise! Sure. mysql SELECT DISTINCT members.* FROM members, groups as a, groups as b - WHERE a.groupname=b.groupname AND members.id=b.memberid - AND a.memberid=1; The 'a.memberid=1' clause looks up the groups that member 1 belongs to in the groups table (as a), giving 'group1' 'group2'. Then it joins back against the groups table (as b) with the 'a.groupname=b.groupname' clause to get the folks that belong to these group(s). Finally the 'members.id=b.memberid' bit selects the records out of the members table, with the DISTINCT function suppressing any duplicates. Clear as mud? (I could've be a little more obvious if I'd put the 'a.memberid=1' clause first. Sorry ...) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with SQL...
It is usually called a self-join, and it is a very useful technique. You have to do this when you need to obtain or access two separate subsets from a single table and somehow use those in a join. In this case, you want the groupids a specific member belongs to (set one) as well as all members belonging to the same group (set two). And the two instances of the table have to be identified through use of table aliases. Regards, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: Don Read [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:56 AM Subject: Re: Help needed with SQL... I've never seen opening a table twice and then doing a join back with itself. Am I wording this correctly? Is that how to describe what you're doing? Thanks for posting this to the full list. - Sheryl - Original Message - From: Don Read [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 5:11 AM Subject: RE: Help needed with SQL... On 26-Feb-2003 [EMAIL PROTECTED] wrote: Yes , I had use this query statement in my MySQL server, But I have a query about it. Why are you use WHERE a.groupname=b.groupname AND members.id=b.memberid AND a.memberid=1? Can you give me a explain or give me a advise! Sure. mysql SELECT DISTINCT members.* FROM members, groups as a, groups as b - WHERE a.groupname=b.groupname AND members.id=b.memberid - AND a.memberid=1; The 'a.memberid=1' clause looks up the groups that member 1 belongs to in the groups table (as a), giving 'group1' 'group2'. Then it joins back against the groups table (as b) with the 'a.groupname=b.groupname' clause to get the folks that belong to these group(s). Finally the 'members.id=b.memberid' bit selects the records out of the members table, with the DISTINCT function suppressing any duplicates. Clear as mud? (I could've be a little more obvious if I'd put the 'a.memberid=1' clause first. Sorry ...) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed in mysql
Hello Inamullah, Thursday, October 24, 2002, 12:50:27 PM, you wrote: First of all don't send me your questions, send them to the mailing list. I don't privide private support. IKWell, I have just installed mysql on both wndows n IK Linux on the same machine and i wanted to ask how i IK can access a database in mysql on windows to be able IK to import it to mysql in Linux and also how i can IK import a database from SQL server or Access into IK mysql. Make a dump file using mysqldump program, copy dump file to the Linux box, and then restore tables. or you can do mysqldump -hyour_windows_box -uuser_name -ppassword database_name | mysql -hyour_linux_box -uuser_name -ppassword database_name For more info about mysqldump look at: http://www.mysql.com/doc/en/mysqldump.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help needed
Hi Sheela, At 01:49 2002.10.11. -0700, you wrote: hi, I am using mysql while creating a table I want to give column descriptioneither by using a query or GUI ex create table t1( a bigint not null ' here i wamt to tell what for the a is ) pl help me thanks sheela You can add comments to your sql statements like this: /* comment here */ For example: create table t1 (a bigint not null, /*comment1*/ b int, /*comment1*/ c char /*comment1*/ ) Good luck, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help Needed
Pushpinder, Looks like a file permission problem. Which user do you have that runs the MySQL daemon (server)? Does this user have the ability to write to the directory you've specified as the data directory for MySQL? Kelly --- Pushpinder Garcha [EMAIL PROTECTED] wrote: Hi I have set up the MySQL database to run locally on my Mac. I have set the privilege for the root user. However, when I log in as the root user I am still unable to create a new database mysql CREATE DATABASE master; ERROR 1006: Can't create database 'master'. (errno: 13) Please tell why this keeps on happening. I am running the following version of MySQl for MAC mysql select version(); ++ | version() | ++ | 3.23.51-entropy.ch | ++ 1 row in set (0.00 sec) This is the privilages that I have granted uses on the system mysql select * from user; +---+--+--+-+-+-+ -+-+---+-+---+--+ ---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +---+--+--+-+-+-+ -+-+---+-+---+--+ ---++-+++ | localhost | root | 04bd6ac2298e4ebd | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | | localhost | | | N | N | N | N | N | N | N | N | N| N | N | N | N | N | | % | pgarcha | | N | N | N | N | N | N | N | N | N| N | N | N | N | N | | localhost | psgarcha | 6a309f0f0ad4b60d | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | | % | psgarcha | 6a309f0f0ad4b60d | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | | localhost | admin| | N | N | N | N | N | N | Y | N | Y| N | N | N | N | N | | localhost | dummy| dummy| N | N | N | N | N | N | N | N | N| N | N | N | N | N | +---+--+--+-+-+-+ -+-+---+-+---+--+ ---++-+++ 7 rows in set (0.00 sec) Thanks in advance Pushpinder Garcha - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php = -- Kelly Firkins Computer Junkie Information Systems guy email: [EMAIL PROTECTED] __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with fixed size table database design!
One mistake that I see happen a lot in database design is that people get stuck in visualizing the data in physical terms along with the labels we attach to them. You should try to think more generically. Freezer, box, building, tube, etc. are all just labels assigned to locations. In your case, everything appears to fall together quite nicely. Here is how I would create your main Inventory table: Inventory InvSeqID ParentID TypeID Quantity LocVertical LocHorizontal MiscAttribute (i.e. Temp) Types --- TypeSeqID Label Capacity Description That should take you most of the way to designing a system that can expand easier without changing table structures or adding tables when you get a new location Type, like floor, building, complex, etc. Your Types table will hold a list of the various Types of locations. This would have an entry for each size tube you use, each size box, freezer, etc. Very easy to had new box sizes, freezer types, etc. The Inventory table will have many self relating (ParentID-InvSeqID) records that you can drill down (or up) to build a view of your inventory. This table structure allows an unlimited amount of levels without the need for you need change or add tables. Programming this may seem daunting at first, but most of your programming will just be loops on the ParentID, continuing until there is no more parent ID. I know may not have gone into enough detail for you, but as you said, this has been done 650,000 some odd times. Although usually to create a message board that can have unlimited replies to replies. The attributes you are tracking are just different than a message boards, the concept is the same. And there are a bunch of message board tutorials out there. On Thursday, September 19, 2002, at 06:30 PM, Csaba Kiss wrote: this is my first mail to the mailing last, since I am new to the mysql world. I would like to create a cell-bank mysql database. We freeze down cells in small tubes. These tubes are stored in boxes and the boxes are in big freezers. Sometimes we freeze 10-20 new tubes with new samples (i.e. 5 of each). -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with fixed size table database design!
Hi, On Fri, 2002-09-20 at 08:30, Csaba Kiss wrote: this is my first mail to the mailing last, since I am new to the mysql world. I would like to create a cell-bank mysql database. We freeze down cells in small tubes. These tubes are stored in boxes and the boxes are in big freezers. Sometimes we freeze 10-20 new tubes with new samples (i.e. 5 of each). When we pick them up we do it usually one by one. Since the size of the boxes and the freezers are constant, the tables that I should use should also be constant (right?). I am not sure how I should design my database. That's why I would like to ask for help. I thought of using the following tables: Samples (contains the data about the samples) Boxes (I am not sure about this) It should contain how many rows and columns are in the box? Freezers (Freezers have racks that holds the boxes and these racks are arranged in rows and columns again)I guess this table should contain the number of racks, rows, and columns, location, temperature) I am stuck somewhere here. I am not too sure how I should proceed. Should I create a freezing table that contains Sample_id, Box_id, Freezer_id...? Rule #1: a database is not a spreadsheet. Particularly with a RELATIONAL database. Also, you want to prevent duplicating information (in relational terms: normalise). You could create a freezer table, a box table and a sample table. Each box (=row in box table) has a reference to the freezer_id, and each sample has a reference to its box_id. So you build relations between the tables. If you need to store the location of a box within a freezer, you can add its row/col coordinates as extra columns in the box table. That way each box has its own coordinates with it as well as its freezer_id. Easy! Temperature... per freezer or per indivual box? Put in the appropriate table (i.e. freezer if per freezer. That way you only have to store/update it in one place). The database should automatically put the tubes in the boxes until they are full and then move to another box by itself. That is something for your application to do. You can find out how many samples each indivual box has, so where to add new samples, and you can also decide when to start a new box. If is thaw one samples it should remove those samples from the box and mark it empty. In the above schema, you would simply delete the sample from the sample table. In the end, a box would have no samples referring to it, which implicitly makes it empty. So, start thinking in relational terms. Your project actually makes this quite easy because it itself already has clear relations (samples in a box, boxes in a freezer, etc). Good luck! You may also wish to consider MySQL training, to quickly get to grips with these concepts. See http://www.mysql.com/training/ for details on currently scheduled training courses. Regards, Arjen. -- MySQL Training, Support, Licenses @ https://order.mysql.com/?marl __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with JOIN on 3 tables
Hiho hiho! bob wrote: Hi, I'm very new to mysql and I'm having a problem getting my joins to work. I have 3 tables: builder category builder_category_link The builder table has a an id field and then several other fields (name, address etc) The category table has an id field and then one other field (type of business... electrician, plumber etc) The builder_category_link table has 3 fields, an id and then a builderid and a categoryid (links the other two tables together) So, I can have a builder that can be a plumber, an electrician etc, etc. I've tried every way I can think to get the join to work and I've read and re-read the mysql docs and I just can't figure out how to make it go. I want to be able to search on both builder name and category (and get the builders that are in that category). I'm assuming the ID in builder table is named builderID in builder_category. Furthermore the ID in category is categoryID in builder_category_table. Then you should join like this: SELECT b.name, c.business FROM builder b LEFT JOIN builder_category bc ON ( b.id = bc.builderid) LEFT JOIN category c ON ( c.id = bc.builderid ) ; That should give out all names from builder which have any business given. Greetings Ralf -- Ralf Narozny Splendid Internet GmbH Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed with JOIN on 3 tables
Thanks for all the input: My code was correct when I compared it to the responses I received. I'm pretty new to mysql, so I was totally blaming my coding for the errors I was getting: This is what I found I will stop using Microsoft for development of mysql. I was running the queries locally on my Win2k machine using the win32 version of mysql. After screwing with it for several days I finally gave up and posted to the list. My code works perfectly on my live Linux and FreeBSD servers. Now after several wasted days, I have another reason to hate Bill Gates! :-) Thanks again. -Original Message- From: Ralf Narozny [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 13, 2002 12:28 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Help needed with JOIN on 3 tables Hiho hiho! bob wrote: Hi, I'm very new to mysql and I'm having a problem getting my joins to work. I have 3 tables: builder category builder_category_link The builder table has a an id field and then several other fields (name, address etc) The category table has an id field and then one other field (type of business... electrician, plumber etc) The builder_category_link table has 3 fields, an id and then a builderid and a categoryid (links the other two tables together) So, I can have a builder that can be a plumber, an electrician etc, etc. I've tried every way I can think to get the join to work and I've read and re-read the mysql docs and I just can't figure out how to make it go. I want to be able to search on both builder name and category (and get the builders that are in that category). I'm assuming the ID in builder table is named builderID in builder_category. Furthermore the ID in category is categoryID in builder_category_table. Then you should join like this: SELECT b.name, c.business FROM builder b LEFT JOIN builder_category bc ON ( b.id = bc.builderid) LEFT JOIN category c ON ( c.id = bc.builderid ) ; That should give out all names from builder which have any business given. Greetings Ralf -- Ralf Narozny Splendid Internet GmbH Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed with JOIN on 3 tables
Hi SELECT b.* //all fields from builder FROM builder b //define tables and aliases , builder_category_link l , category c WHERE (b.builderID = l.builderID) //linking builder to link table AND (l.categoryID = c.categoryID) //linking category to link table which in //turn builds the link to the builder table AND (l.category_description = 'plumber'); //where category is plumber should return all builders who do plumbing I think the id field in the link table is redundant, unless you have another use for it. Hope I understood what you were aiming to do :) Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: bob [mailto:[EMAIL PROTECTED]] Sent: 13 June 2002 17:09 To: [EMAIL PROTECTED] Subject: Help needed with JOIN on 3 tables Hi, I'm very new to mysql and I'm having a problem getting my joins to work. I have 3 tables: builder category builder_category_link The builder table has a an id field and then several other fields (name, address etc) The category table has an id field and then one other field (type of business... electrician, plumber etc) The builder_category_link table has 3 fields, an id and then a builderid and a categoryid (links the other two tables together) So, I can have a builder that can be a plumber, an electrician etc, etc. I've tried every way I can think to get the join to work and I've read and re-read the mysql docs and I just can't figure out how to make it go. I want to be able to search on both builder name and category (and get the builders that are in that category). Thanks for your help. Rob. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with JOIN on 3 tables
Strange, there shouldn't be any difference between MySQL query behaviour under Win2K or Linux or BSD or Win98. It could be more to do with the version of MySQL you are using. - Original Message - From: bob [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 14, 2002 3:18 AM Subject: RE: Help needed with JOIN on 3 tables This is what I found I will stop using Microsoft for development of mysql. I was running the queries locally on my Win2k machine using the win32 version of mysql. After screwing with it for several days I finally gave up and posted to the list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed on query
Hi, Now I need a query which will take the values from column 'query', treat them as patterns for matching and return row(s) which match the given string 'blahblah'. In this example, the matched data is obviously in second row. (bla% matches blahblah) Do I make any sense? Is this possible? Any help will be greatly appreciated. To be honest I didn't belive it would work, but... here it is :) mysql select * from blah; +--+--+---+ | uid | username | query | +--+--+---+ |1 | someuser | %qu1% | |2 | anotheru | bla% | +--+--+---+ 2 rows in set (0.00 sec) mysql select * from blah where 'blahblah' like query; +--+--+---+ | uid | username | query | +--+--+---+ |2 | anotheru | bla% | +--+--+---+ 1 row in set (0.00 sec) Regards, Sasa mysql, select, database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed on query
You should be able to just use something like this: Select uid, username From tablename Where '$externalstringvar' like query; That's PHP syntax. You'll have to use whatever makes sense for your scripting or programming language in place of the $externalstringvar variable. The single-quotes are required. Note this will always do a full table scan, so if your table is big, it will be slow. ..chris - Original Message - From: thor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 4:22 AM Subject: Help needed on query I have a table like this one: +-+---+---+ | uid | username | query | +-+---+---+ | 1 | someuser | %qu1% | | 2 | anotheru | bla% | +-+---+---+ I get a string from external source, let's say the string is 'blahblah'. Now I need a query which will take the values from column 'query', treat them as patterns for matching and return row(s) which match the given string 'blahblah'. In this example, the matched data is obviously in second row. (bla% matches blahblah) Do I make any sense? Is this possible? Any help will be greatly appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with C program + Mysql
Hi. The char* to the field value will be NULL (in the C meaning), if the value is NULL (in the SQL meaning). The reason for your core dump is probably a missing check before you treat the NULL pointer as string. Bye, Benjamin. On Thu, Feb 28, 2002 at 03:23:01PM -0300, [EMAIL PROTECTED] wrote: I have defined a Mysql table with some integer variable, I can put NULL values in these elementes, but when I user mysql_fetch_row, if there's some integer field with NULL value, the program fails with core dump. Where's the problem? Thanks in advance. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed !!!
hi, Yes, I was really thinking about the InnoDB Tables, Thank you very much for such a systematic and accurate reply, Thanks and regards, Chetan -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 1:10 PM To: Chetan Lavti Cc: [EMAIL PROTECTED] Subject: Re: Help needed !!! On Fri, Jan 25, 2002 at 01:05:12PM +0530, Chetan Lavti wrote: I am going to use the MySQL version 3.23.47 for as our database. The issue is which table type to use. I want that the database should be (memory-resident). I have tried with the MyISAM tables which doesn't solves my purpose as in this case the data are stored in the files. Right. I have also tried with the HEAP tables that uses a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes I am loosing all data stored in that. As, I have created one table as a heap type but when I make my server down and start it again all rows created nowhere exists. Correct. It sounds like you might want to look at InnoDB tables. They are disk based, but the InnoDB table handler can use a significant amount of RAM (if you allow it to) to cache index *and* record data. MyISAM only caches index data in memory. The InnoDB buffer pool is where this cached data is stored. So, if anybody can suggest me any process by which I can populated HEAP table(es) from a duplicate table(es) (which is on secondary storage) at the time of startup. Also, any runtime modifications in the tables will need to be updated in both the tables (one in memory and another one on disk). If any other method by which I can achieve the same. There was talk last year of implementing a hybrid HEAP/MyISAM table type (originally motived by the DBA at Slashdot). Upon startup, the MyISAM table would be loaded into a RAM-based HEAP-table. Any changes to the HEAP table would get written to the underlying MyISAM table as well. Read-only queries, of course, would be run against the RAM-based HEAP table. I suspect that will never be implemented, since InnoDB and it's buffer pool go a long way toward solving the same problem. Hope that helps, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 513,100,530 queries (265/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed !!!
On Fri, Jan 25, 2002 at 01:05:12PM +0530, Chetan Lavti wrote: I am going to use the MySQL version 3.23.47 for as our database. The issue is which table type to use. I want that the database should be (memory-resident). I have tried with the MyISAM tables which doesn't solves my purpose as in this case the data are stored in the files. Right. I have also tried with the HEAP tables that uses a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes I am loosing all data stored in that. As, I have created one table as a heap type but when I make my server down and start it again all rows created nowhere exists. Correct. It sounds like you might want to look at InnoDB tables. They are disk based, but the InnoDB table handler can use a significant amount of RAM (if you allow it to) to cache index *and* record data. MyISAM only caches index data in memory. The InnoDB buffer pool is where this cached data is stored. So, if anybody can suggest me any process by which I can populated HEAP table(es) from a duplicate table(es) (which is on secondary storage) at the time of startup. Also, any runtime modifications in the tables will need to be updated in both the tables (one in memory and another one on disk). If any other method by which I can achieve the same. There was talk last year of implementing a hybrid HEAP/MyISAM table type (originally motived by the DBA at Slashdot). Upon startup, the MyISAM table would be loaded into a RAM-based HEAP-table. Any changes to the HEAP table would get written to the underlying MyISAM table as well. Read-only queries, of course, would be run against the RAM-based HEAP table. I suspect that will never be implemented, since InnoDB and it's buffer pool go a long way toward solving the same problem. Hope that helps, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 513,100,530 queries (265/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help needed on datetime functions
On Wednesday 05 December 2001 16:40, you wrote: hi everybody i want to take diff of datetime format columns. Is it possible using direct '-' arithmatic.. It gives result as: 2001-11-03 15:43:47 - 2001-11-03 15:42:21 = 126 2001-11-03 16:11:50 - 2001-11-03 15:48:00 = 6350 First row is clear but i'm not getting second row's diff.. **((63)50)** how it comes. Is there any diff way to find diff of datetime columns. (any mysql func.) OR It is not possible by using any standard mysql funcions and one has to write his own program to do this. You can use something like this: SEC_TO_TIME(UNIX_TIMESTAMP(2001-11-03 16:11:50) - UNIX_TIMESTAMP(2001-11-03 15:48:00)) which would return 00:23:50 hth -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* Universe, n.: The problem. */ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed getting table headings into an outfile
Sorry, I should have given you more information about the problem.
We have a Java program, with a J2EE backend which is connected to a MySQL
database.
The database is running on a windows 2000 machine, and we want to output the
contents of the tables in text format(CSV). I can get the formatting ok,
but it just needs the table headings.
Ideally we want to use one or more queries to do this, rather than using
mysqldump.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 10 September 2001 11:38 am
To: Andrew Murphy
Subject: Re: Help needed getting table headings into an outfile
I do this in perl to get the headers. Perhaps you can adjust to fit into
your output script.
my $statement = SELECT * FROM $table;
my $sth = $dbh-prepare( $statement ) ;
$sth-execute();
my $fields = $sth-{NUM_OF_FIELDS};
for ( my $i = 0 ; $i $fields ; $i++ ) {
$name = $sth-{NAME}-[$i] ;
if ($i == 0) {
$fldnames = $name ;
} else {
$fldnames = $fldnames . | . $name ;
}
} # end of for loop
$sth-finish();
Dirk Van Wie
Andrew Murphy [EMAIL PROTECTED] on 09/10/2001 12:33:50 PM
To: 'mysql Mailing List' [EMAIL PROTECTED]
cc:
Subject: Help needed getting table headings into an outfile
Hi,
I keep sending this to the list, but im not sure if anyone else is getting
it.
I hope you can help me with this.
I am using the INTO OUTFILE 'filename' function, to create .CSV output
files
from my queries.
Whenever I do this, the table headings are removed from the output. Is
there a way to get the table headings displayed in the output file?
Thanks in advance
Andrew
database, sql, query, table
database, sql, query, table
database, sql, query, table
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help Needed Please
Hi, use "mysqladmin variables" command to see current settings. I guess your mysqld does not read the config file you have edited. ;-) It happened to me me also few days ago. Remember mysqld looks for /etc/my.cnf and $DATADIR/var/my.cnf if I remember well. Maybe put the path to config file just on the command line into safe_mysqld script. Martin Hi I need to set the max_allowed_packet to 16M. below is my 01mysql-server.sh file. I use DBI and I know that I have to start this before the DBI program and I did but if I use ./mysqld --help it shows that my max_allowed_packet is still 1M. Any help would be really great since I've been working on this all day :( - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help Needed Please
On 26-Aug-2001 [EMAIL PROTECTED] wrote: Hi I need to set the max_allowed_packet to 16M. below is my 01mysql-server.sh file. I use DBI and I know that I have to start this before the DBI program and I did but if I use ./mysqld --help it shows that my max_allowed_packet is still 1M. Any help would be really great since I've been working on this all day :( case $1 in start) if [ -x /usr/local/bin/safe_mysqld ]; then /usr/local/bin/safe_mysqld --user=mysql --set-variable=max_allowed_packet=16M /dev/null echo -n ' mysqld' try it in your /etc/my.cnf: [mysqld] socket=/tmp/mysql.sock set-variable = max_allowed_packet=16M Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help needed for replication
Already tried ... Not successfully. Another idea ? -Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED]] Sent: Friday, July 20, 2001 7:51 PM To: BALU Frédéric; [EMAIL PROTECTED] Subject: Re: help needed for replication Dear Frederic, localhost | jkasas | 1896f443280395b3 And what I do and get : mysql GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; ERROR 1133: Can't find any matching row in the user table Try GRANT SELECT ON MEMBERS.users TO jkasas@localhost (w/out quotes)! :) Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: BALU Frédéric [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 20, 2001 5:54 PM Subject: help needed for replication Hi everybody, I use Replication on a windows NT4 Server. There are 1 master and 2 slaves, all on the same machine. One slave is started with the option skip-name-resolve, not the other. For the 2 slaves : master-host=localhost When adding a user (MYSQL.User), the replication is OK but, when I use GRANT, I get a 1133 error. Let's have a look on a slave : MYSQL.User table : localhost | jkasas | 1896f443280395b3 And what I do and get : mysql GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; ERROR 1133: Can't find any matching row in the user table Personnally, I don't understand why. Does anyone understand ? Thx for the answers. -- Frederic BALU - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help needed for replication : EUREKA
The solution is ... FLUSH PRIVILEGES; on the slaves. As it's not an UPDATE, this command is not sent to slaves with the binlog. -Original Message- From: BALU Frédéric [mailto:[EMAIL PROTECTED]] Sent: Monday, July 23, 2001 8:47 AM To: 'Stefan Hinz'; [EMAIL PROTECTED] Subject: RE: help needed for replication Already tried ... Not successfully. Another idea ? -Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED]] Sent: Friday, July 20, 2001 7:51 PM To: BALU Frédéric; [EMAIL PROTECTED] Subject: Re: help needed for replication Dear Frederic, localhost | jkasas | 1896f443280395b3 And what I do and get : mysql GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; ERROR 1133: Can't find any matching row in the user table Try GRANT SELECT ON MEMBERS.users TO jkasas@localhost (w/out quotes)! :) Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: BALU Frédéric [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 20, 2001 5:54 PM Subject: help needed for replication Hi everybody, I use Replication on a windows NT4 Server. There are 1 master and 2 slaves, all on the same machine. One slave is started with the option skip-name-resolve, not the other. For the 2 slaves : master-host=localhost When adding a user (MYSQL.User), the replication is OK but, when I use GRANT, I get a 1133 error. Let's have a look on a slave : MYSQL.User table : localhost | jkasas | 1896f443280395b3 And what I do and get : mysql GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; ERROR 1133: Can't find any matching row in the user table Personnally, I don't understand why. Does anyone understand ? Thx for the answers. -- Frederic BALU - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help needed for replication
Dear Frederic, localhost | jkasas | 1896f443280395b3 And what I do and get : mysql GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; ERROR 1133: Can't find any matching row in the user table Try GRANT SELECT ON MEMBERS.users TO jkasas@localhost (w/out quotes)! :) Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: BALU Frédéric [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 20, 2001 5:54 PM Subject: help needed for replication Hi everybody, I use Replication on a windows NT4 Server. There are 1 master and 2 slaves, all on the same machine. One slave is started with the option skip-name-resolve, not the other. For the 2 slaves : master-host=localhost When adding a user (MYSQL.User), the replication is OK but, when I use GRANT, I get a 1133 error. Let's have a look on a slave : MYSQL.User table : localhost | jkasas | 1896f443280395b3 And what I do and get : mysql GRANT SELECT ON MEMBERS.users TO jkasas@'localhost'; ERROR 1133: Can't find any matching row in the user table Personnally, I don't understand why. Does anyone understand ? Thx for the answers. -- Frederic BALU - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed
You need to GRANT PRIVILEGES to the host and user that PHP connects from. Kim White wrote: Hi Could anybody tell me how to get rid of the following error on a php3 request. Warning: MySQL Connection Failed: Host 'hostname.co.za' is not allowed to connect to this MySQL server in /var/local/irm/irm.inc on line 83. Thanks Kim White - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed
Kim White wrote: Could anybody tell me how to get rid of the following error on a php3 request. Warning: MySQL Connection Failed: Host 'hostname.co.za' is not allowed to connect to this MySQL server in /var/local/irm/irm.inc on line 83. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) Go to the manual on http://www.mysql.com and search for GRANT command syntax. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Hong Kong, China ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: HELP NEEDED: Problems with SELECT in combination with HAVING
Hey Hans - it didn't work (damn) but thank you for your reply and sorry about the accidental mail you got. The result that was produced had the correct time but it was not attatched to the correct row. It was instead attached to the first row that MySql found in the RobotRun table. I have found another way around it, which goes like this: SELECT max(runId), robotId FROM RobotRun WHERE endTime IS NOT NULL GROUP BY RobotId This builds on the assumption that runId's are assigned in a strictly ascending order, so it only works for my domain ... It also seems that I have made a small typo in my initial request (sorry). The purpose of the SQL is to find the runId of the newest run for each robotId. Thus I reformed your SQL to: SELECT runId, max(startTime) I can solve my problem by using GROUP BY and HAVING when running against SyBase like this: SELECT * FROM RobotRun WHERE endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Alas, this does not work with MySql. Is there a general understanding within the MySql community that GROUP BY and HAVING doesn't conform to the standard ? Regards Thomas -Original Message- From: Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: 26. juni 2001 17:42 To: [EMAIL PROTECTED] Subject: Re: HELP NEEDED: Problems with SELECT in combination with HAVING Try SELECT robotId, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId This should return the robotId and the most recent startTime labeled 'crit' for all records specified in the where clause. And have a look in the manual for the group by statement - since this statement works like a enhanced 'select distinct' query it will produce ill results applied to non unique combinations in the select and group by statement. I suppose that¹s the case for your 'robotId, startTime' pair. If I got this wrong forgive me it was first thing in the morning. Hannes On 6/26/01 4:05 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hello there - I have this weird problem using MySql (version 3.23.39). It seems that MySql interpretation of a SELECT statment using HAVING is completely random. I have a table called RobotRun which stores information about when a robot has been running (start, stop and the id of the robot). This translates to columns: runId (primary key), robotId (the id of the robot), startTime and endTime. A robot can run several times during its life time, thus several records will appear in RobotRun for the same robotId, however, each row will always have a unique runId. The purpose of the SQL is to produce one RobotRun row for each robot, with the added restriction that it is only the row with the newest startTime value that gets selected. Now if I do the following SQL things start to get strange: SELECT robotId, startTime, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = crit This query produces 321 rows. Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is correct and expected since the input set is larger. NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows What is going on. I cannot see how this is possible. The input set is only getting larger. The number of rows involved in the table is in the vicinity of 5. The theoretical maximum number of rows produced from the SQL is in the neighbourhood of 5000 rows. That shouldn't be a problem. I have also tried the following statement with the same result (problem): SELECT robotId, startTime FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Any help would be appreciated. Regards Thomas Koch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: HELP NEEDED: Problems with SELECT in combination with HAVING 2
Hey Hannes - it didn't work (damn) but thank you for your reply and sorry about the accidental mail you got. The result that was produced had the correct time but it was not attatched to the correct row. It was instead attached to the first row that MySql found in the RobotRun table. I have found another way around it, which goes like this: SELECT max(runId), robotId FROM RobotRun WHERE endTime IS NOT NULL GROUP BY RobotId This builds on the assumption that runId's are assigned in a strictly ascending order, so it only works for my domain ... It also seems that I have made a small typo in my initial request (sorry). The purpose of the SQL is to find the runId of the newest run for each robotId. Thus I reformed your SQL to: SELECT runId, max(startTime) I can solve my problem by using GROUP BY and HAVING when running against SyBase like this: SELECT * FROM RobotRun WHERE endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Alas, this does not work with MySql. Is there a general understanding within the MySql community that GROUP BY and HAVING doesn't conform to the standard ? Regards Thomas -Original Message- From: Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: 26. juni 2001 17:42 To: [EMAIL PROTECTED] Subject: Re: HELP NEEDED: Problems with SELECT in combination with HAVING Try SELECT robotId, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId This should return the robotId and the most recent startTime labeled 'crit' for all records specified in the where clause. And have a look in the manual for the group by statement - since this statement works like a enhanced 'select distinct' query it will produce ill results applied to non unique combinations in the select and group by statement. I suppose that¹s the case for your 'robotId, startTime' pair. If I got this wrong forgive me it was first thing in the morning. Hannes On 6/26/01 4:05 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hello there - I have this weird problem using MySql (version 3.23.39). It seems that MySql interpretation of a SELECT statment using HAVING is completely random. I have a table called RobotRun which stores information about when a robot has been running (start, stop and the id of the robot). This translates to columns: runId (primary key), robotId (the id of the robot), startTime and endTime. A robot can run several times during its life time, thus several records will appear in RobotRun for the same robotId, however, each row will always have a unique runId. The purpose of the SQL is to produce one RobotRun row for each robot, with the added restriction that it is only the row with the newest startTime value that gets selected. Now if I do the following SQL things start to get strange: SELECT robotId, startTime, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = crit This query produces 321 rows. Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is correct and expected since the input set is larger. NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows What is going on. I cannot see how this is possible. The input set is only getting larger. The number of rows involved in the table is in the vicinity of 5. The theoretical maximum number of rows produced from the SQL is in the neighbourhood of 5000 rows. That shouldn't be a problem. I have also tried the following statement with the same result (problem): SELECT robotId, startTime FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Any help would be appreciated. Regards Thomas Koch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP NEEDED: Problems with SELECT in combination with HAVING
Thomas You might want to consider to post a sample select from the table, some things become apparent just then. Hannes On 6/27/01 3:35 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hey Hans - it didn't work (damn) but thank you for your reply and sorry about the accidental mail you got. The result that was produced had the correct time but it was not attatched to the correct row. It was instead attached to the first row that MySql found in the RobotRun table. I have found another way around it, which goes like this: SELECT max(runId), robotId FROM RobotRun WHERE endTime IS NOT NULL GROUP BY RobotId This builds on the assumption that runId's are assigned in a strictly ascending order, so it only works for my domain ... It also seems that I have made a small typo in my initial request (sorry). The purpose of the SQL is to find the runId of the newest run for each robotId. Thus I reformed your SQL to: SELECT runId, max(startTime) I can solve my problem by using GROUP BY and HAVING when running against SyBase like this: SELECT * FROM RobotRun WHERE endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Alas, this does not work with MySql. Is there a general understanding within the MySql community that GROUP BY and HAVING doesn't conform to the standard ? Regards Thomas -Original Message- From: Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: 26. juni 2001 17:42 To: [EMAIL PROTECTED] Subject: Re: HELP NEEDED: Problems with SELECT in combination with HAVING Try SELECT robotId, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId This should return the robotId and the most recent startTime labeled 'crit' for all records specified in the where clause. And have a look in the manual for the group by statement - since this statement works like a enhanced 'select distinct' query it will produce ill results applied to non unique combinations in the select and group by statement. I suppose that¹s the case for your 'robotId, startTime' pair. If I got this wrong forgive me it was first thing in the morning. Hannes On 6/26/01 4:05 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hello there - I have this weird problem using MySql (version 3.23.39). It seems that MySql interpretation of a SELECT statment using HAVING is completely random. I have a table called RobotRun which stores information about when a robot has been running (start, stop and the id of the robot). This translates to columns: runId (primary key), robotId (the id of the robot), startTime and endTime. A robot can run several times during its life time, thus several records will appear in RobotRun for the same robotId, however, each row will always have a unique runId. The purpose of the SQL is to produce one RobotRun row for each robot, with the added restriction that it is only the row with the newest startTime value that gets selected. Now if I do the following SQL things start to get strange: SELECT robotId, startTime, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = crit This query produces 321 rows. Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is correct and expected since the input set is larger. NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows What is going on. I cannot see how this is possible. The input set is only getting larger. The number of rows involved in the table is in the vicinity of 5. The theoretical maximum number of rows produced from the SQL is in the neighbourhood of 5000 rows. That shouldn't be a problem. I have also tried the following statement with the same result (problem): SELECT robotId, startTime FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Any help would be appreciated. Regards Thomas Koch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http
Re: HELP NEEDED: Problems with SELECT in combination with HAVING
Try SELECT robotId, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId This should return the robotId and the most recent startTime labeled 'crit' for all records specified in the where clause. And have a look in the manual for the group by statement - since this statement works like a enhanced 'select distinct' query it will produce ill results applied to non unique combinations in the select and group by statement. I suppose that¹s the case for your 'robotId, startTime' pair. If I got this wrong forgive me it was first thing in the morning. Hannes On 6/26/01 4:05 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hello there - I have this weird problem using MySql (version 3.23.39). It seems that MySql interpretation of a SELECT statment using HAVING is completely random. I have a table called RobotRun which stores information about when a robot has been running (start, stop and the id of the robot). This translates to columns: runId (primary key), robotId (the id of the robot), startTime and endTime. A robot can run several times during its life time, thus several records will appear in RobotRun for the same robotId, however, each row will always have a unique runId. The purpose of the SQL is to produce one RobotRun row for each robot, with the added restriction that it is only the row with the newest startTime value that gets selected. Now if I do the following SQL things start to get strange: SELECT robotId, startTime, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = crit This query produces 321 rows. Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is correct and expected since the input set is larger. NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows What is going on. I cannot see how this is possible. The input set is only getting larger. The number of rows involved in the table is in the vicinity of 5. The theoretical maximum number of rows produced from the SQL is in the neighbourhood of 5000 rows. That shouldn't be a problem. I have also tried the following statement with the same result (problem): SELECT robotId, startTime FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Any help would be appreciated. Regards Thomas Koch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with Query
Please do a SHOW FIELDS FROM and SHOW KEYS FROM on all tables involved so we can see what's going on here. Also, a warning, if desacription is indexed, by doing LIKE '%GABA%' with wildcard at beginning, it won't use index's. Would also help if you did an EXPLAIN on that query and sent that. - Original Message - From: Bryan Coon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 27, 2001 1:20 PM Subject: Help needed with Query I am working on a mysql database with many large tables (1.5 million rows on some) and came across a beeg problem. I have need for a query like this: mysql select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as panel, null as first_pcrp, null as second_pcrp, null as prb_seq, null as term, null as validation, null as freq, null as population from locuslink l, subsnp left outer join chromosome_position c on subsnp_pk = c.snp_fk left outer join locus_annotation a on subsnp_pk = a.snp_fk where a.locusid = substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk; Which seems insane. This 4 table join in another situation would become a 6 table join. As it is, it takes 1min 10.60 seconds for this query to return one result. Clearly I am not a MySQL expert, and even though this query works, there must be a better way to do this. Is it a matter of indexing? A matter of poor schema design? Poor query design? Is it unavoidable? As far as I know, none of the tables are indexed (I did not set up the schema). Please let me know if more info is required to help with this. Thanks, Bryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed with Query UPDATE INFO
Okay, here is the full monty from the database regarding the query in question, any suggestions on how to improve any of it are greatly appreciated! Also, if any more info is needed, just let me know. Thanks! Bryan QUERY: mysql select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as panel, null as first_pcrp, null as second_pcrp, null as prb_seq, null as term, null as validation, null as freq, null as population from locuslink l, subsnp left outer join chromosome_position c on subsnp_pk = c.snp_fk left outer join locus_annotation a on subsnp_pk = a.snp_fk where a.locusid = substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk; EXPLAIN: ++--+---+--+-+--+---+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+---+--- --+ | subsnp | ALL | NULL | NULL |NULL | NULL | 1201 | Using temporary; Using filesort | | l | ALL | NULL | NULL |NULL | NULL | 21294 | where used | | c | ALL | NULL | NULL |NULL | NULL | 579 | | | a | ALL | NULL | NULL |NULL | NULL | 704 | where used; Distinct| ++--+---+--+-+--+---+--- --+ 4 rows in set (0.00 sec) DESCRIBE: mysql describe locuslink; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | LOCUSID| varchar(12) | | | | | | SYMBOL | varchar(15) | YES | | NULL| | | INTERIM_SYMBOL | varchar(15) | YES | | NULL| | | MIM_NUMBER | varchar(15) | YES | | NULL| | | CHROM | varchar(6) | YES | | NULL| | | BAND | varchar(20) | YES | | NULL| | | DESCRIPTION| varchar(150) | YES | | NULL| | | SPECIES| varchar(10) | YES | | NULL| | | SOURCESEQ | varchar(15) | YES | | NULL| | ++--+--+-+-+---+ 9 rows in set (0.01 sec) mysql describe subsnp; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | SUBSNP_PK | int(11) | | PRI | 0 | | | SOURCE | varchar(40) | | | | | | SOURCEID | varchar(20) | | | | | | SNPPOSITION| int(11) | | | 0 | | | TOTALSEQLENGTH | int(11) | | | 0 | | | VARIATION | varchar(20) | YES | | NULL| | | NUM_ALLELES| int(11) | YES | | NULL| | ++-+--+-+-+---+ 7 rows in set (0.00 sec) mysql describe chromosome_position; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | SNP_FK | int(11)| | | 0 | | | CHROM| varchar(5) | YES | | NULL| | | CHROMPOS | int(11)| YES | | NULL| | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe locus_annotation; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | SNP_FK | int(11) | | | 0 | | | LOCUS | varchar(20) | YES | | NULL| | | LOCUSID | int(11) | YES | | NULL| | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql KEYS: mysql show keys from locuslink; Empty set (0.00 sec) mysql show keys from subsnp; +++--+--+-+---+- +--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+-+---+- +--++-+ | subsnp | 0 | PRIMARY |1 | SUBSNP_PK | A | 1201 | NULL | NULL | | +++--+--+-+---+- +--++-+ 1 row in set (0.00 sec) mysql show keys from chromosome_position; Empty set (0.00 sec) mysql show keys from locus_annotation; Empty set (0.00 sec) mysql - Before posting, please check:
Re: Help needed with Query UPDATE INFO
Ok, a few things I see...first off, slap some keys into those tables on the join portions. Secondly, upon examining your query a second time, your never joining subsnp and locuslink. I see your trying to use a full join, but you didn't put the criteria for it in the where clause. That would cause some VERY weird join results, and may be your problem. Try adding in the join clause in the where clause. - Original Message - From: Bryan Coon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 27, 2001 2:52 PM Subject: RE: Help needed with Query UPDATE INFO Okay, here is the full monty from the database regarding the query in question, any suggestions on how to improve any of it are greatly appreciated! Also, if any more info is needed, just let me know. Thanks! Bryan QUERY: mysql select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as panel, null as first_pcrp, null as second_pcrp, null as prb_seq, null as term, null as validation, null as freq, null as population from locuslink l, subsnp left outer join chromosome_position c on subsnp_pk = c.snp_fk left outer join locus_annotation a on subsnp_pk = a.snp_fk where a.locusid = substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk; EXPLAIN: ++--+---+--+-+--+---+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+---+--- --+ | subsnp | ALL | NULL | NULL |NULL | NULL | 1201 | Using temporary; Using filesort | | l | ALL | NULL | NULL |NULL | NULL | 21294 | where used | | c | ALL | NULL | NULL |NULL | NULL | 579 | | | a | ALL | NULL | NULL |NULL | NULL | 704 | where used; Distinct| ++--+---+--+-+--+---+--- --+ 4 rows in set (0.00 sec) DESCRIBE: mysql describe locuslink; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | LOCUSID| varchar(12) | | | | | | SYMBOL | varchar(15) | YES | | NULL| | | INTERIM_SYMBOL | varchar(15) | YES | | NULL| | | MIM_NUMBER | varchar(15) | YES | | NULL| | | CHROM | varchar(6) | YES | | NULL| | | BAND | varchar(20) | YES | | NULL| | | DESCRIPTION| varchar(150) | YES | | NULL| | | SPECIES| varchar(10) | YES | | NULL| | | SOURCESEQ | varchar(15) | YES | | NULL| | ++--+--+-+-+---+ 9 rows in set (0.01 sec) mysql describe subsnp; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | SUBSNP_PK | int(11) | | PRI | 0 | | | SOURCE | varchar(40) | | | | | | SOURCEID | varchar(20) | | | | | | SNPPOSITION| int(11) | | | 0 | | | TOTALSEQLENGTH | int(11) | | | 0 | | | VARIATION | varchar(20) | YES | | NULL| | | NUM_ALLELES| int(11) | YES | | NULL| | ++-+--+-+-+---+ 7 rows in set (0.00 sec) mysql describe chromosome_position; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | SNP_FK | int(11)| | | 0 | | | CHROM| varchar(5) | YES | | NULL| | | CHROMPOS | int(11)| YES | | NULL| | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe locus_annotation; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | SNP_FK | int(11) | | | 0 | | | LOCUS | varchar(20) | YES | | NULL| | | LOCUSID | int(11) | YES | | NULL| | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql KEYS: mysql show keys from locuslink; Empty set (0.00 sec) mysql show keys from subsnp; +++--+--+-+---+- +--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part
Re: Help needed on mysql!!
What do you mean by "better" ? Next time it hangs, check your disk freespace. When MySQL runs out of tmp workspace, it waits until some becomes available. [EMAIL PROTECTED] wrote: Help! I have set up a web server with PHP4, RH7 and MySQL 3.23.32. After surfing for sometime, the browser will be in a forever loading mode. I use mysqladmin processlist to take a look and I saw processes with the command "sleep" (it could be just 1 "sleep" process). Everytime I encounter this, I have to restart httpd server. Qn: How can I optimize apache, or php or mysql so that it won't "sleep"? Where should I modify to have no such problem again? With a better machine config with same os/softwareconfiguration, it seems to have no such problem...does that mean it is due to hardware? Please help. TIA!! Regards - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help Needed
Hello Pat Well, I really feel stupid! Under the gun with this any I really don't have a clue. I also need to install some type of DBI foe windows2000 advanced server too and I'm now downloading a perl file off of mysql.com. I really have no idea working with windows. My main OS that I have been working with was FreeBSD server. Again thanks for the time that you took out to help me out! Joe Pat Sherrill wrote: Expected behaviour, look in your Tray on the Taskbar for a stoplight icon. Pat... - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, March 17, 2001 6:12 PM Subject: Help Needed Hi I just downloaded MYsql for windows NT. I'm running it on Windows2000 Advanced Server. Everything loads fine and it is running ok but, when I try to run Winmysqladmin the program loads up for only about 2 seconds and then closes. Any ideas on what I need to do? Thanks Joe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (+) Help needed
Don't you think this has nothing specific to MySQL? Maybe you should read some books on SQL and the manual for MySQL? SELECT * FROM tablename WHERE datefield'2001-01-11' AND datefied'2001-02-12' Don;t ask how to correctly call the query in perl, read perldoc DBI perldoc DBD::mysql Does ANYBODY read books manuals these days? Bye Artem - Original Message - From: "Alexei Sh." [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 27, 2001 4:04 PM Subject: (+) Help needed Ok I need help with this.. I have to values like $dat1="2001-01-11"; $dat2="2001-02-12"; so and i have Mysql table with IP | Size | Date| -- 213.32.44.11 | 3|2001-01-11-12| -- 213.32.44.11 | 3|2001-01-11-13| -- 213.32.44.12 | 3|2001-01-12-12| -- 213.32.44.14 | 3|2001-01-13-17| -- 213.32.44.16 | 3|2001-01-14-11| -- 213.32.44.111 | 3|2001-02-15-10| -- 213.32.44.121 | 3|2001-01-13-10| -- 213.32.44.131 | 3|2001-01-15-10| -- So i need SELECT from table only record between values $dat1 and $dat2 how to do this ?! Thx __ ___ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: (+) Help needed
Slight correction, for some reason the query below does NOT work on my sample DB unless I quote the date values. This should work now: SELECT IP, Size, Date FROM your_table WHERE Date BETWEEN '2001-02-01' AND '2001-05-01'; so and i have Mysql table with IP | Size | Date| -- 213.32.44.11 | 3|2001-01-11-12| -- 213.32.44.11 | 3|2001-01-11-13| -- 213.32.44.12 | 3|2001-01-12-12| -- 213.32.44.14 | 3|2001-01-13-17| -- 213.32.44.16 | 3|2001-01-14-11| -- 213.32.44.111 | 3|2001-02-15-10| -- 213.32.44.121 | 3|2001-01-13-10| -- 213.32.44.131 | 3|2001-01-15-10| -- So i need SELECT from table only record between values $dat1 and $dat2 how to do this ?! Thx _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
