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
help needed restoring crashed mysql
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. I deleted it and rechecked df -h, but it is stll at 100%. I then stopped mysql and finally the output of df -h was correct. However, when I try to restart mysql it outputs the following errors in the error log; it is in the attached file. Thanks for anyone that could help. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 29 12:51:23 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... InnoDB: Page directory corruption: infimum not pointed to 29 12:51:23 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex (LOTS OF ZEROES...) ;InnoDB: End of page dump 29 12:56:28 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 0, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be a freshly allocated page 29 12:56:28InnoDB: Error: trying to access a stray pointer 0x33987ff8 InnoDB: buf pool start is at 0xb3978000, end at 0xb4178000 InnoDB: Probable reason is database corruption or memory InnoDB: corruption. If this happens in an InnoDB database recovery, see InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: how to force recovery. 29 12:56:28 InnoDB: Assertion failure in thread 3067451088 in file ../../../storage/innobase/include/buf0buf.ic line 264 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/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. 29 12:56:28 - mysqld got signal 6 ; 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=16777216 read_buffer_size=131072 max_used_connections=0 max_threads=151 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345919 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 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... stack_bottom = (nil) thread_stack 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb763ecbd] /usr/sbin/mysqld(handle_segfault+0x494) [0xb730a854] [0xb7085400] /lib/tls/i686/nosegneg/libc.so.6(abort+0x182) [0xb6d88d42] /usr/sbin/mysqld(page_cur_search_with_match+0x9e3) [0xb755cdb3] /usr/sbin/mysqld(btr_cur_search_to_nth_level+0x5ae) [0xb74f2c8e] /usr/sbin/mysqld(btr_pcur_open_on_user_rec+0x73) [0xb75be6c3] /usr/sbin/mysqld(+0x46fc55) [0xb7512c55] /usr/sbin/mysqld(dict_load_sys_table+0x75) [0xb75195c5] /usr/sbin/mysqld(dict_boot+0xd27) [0xb75bf927] /usr/sbin/mysqld(innobase_start_or_create_for_mysql+0x1299) [0xb7595459] /usr/sbin/mysqld(+0x43e7e6) [0xb74e17e6] /usr/sbin/mysqld(ha_initialize_handlerton(st_plugin_int*)+0x3f) [0xb741611f] /usr/sbin/mysqld(+0x411f2a) [0xb74b4f2a] /usr/sbin/mysqld(plugin_init(int*, char**, int)+0x8a7) [0xb74b8827] /usr/sbin/mysqld(+0x2698d2) [0xb730c8d2] /usr/sbin/mysqld(main+0x1fa) [0xb730ff9a] /lib/tls/i686/nosegneg/libc.so.6(__libc_start_main+0xe6) [0xb6d71bd6] /usr/sbin/mysqld(+0x182d81) [0xb7225d81] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. -- 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 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
Help needed with what appears to be a corrupted innodb db
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.html contains 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=arch...@jab.org
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=arch...@jab.org
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: recovery help needed
I actually get the feeling you are not connecting as root. Try mysql -uroot -p test instead of just mysql test Have a nice day, - Martijn On Wed, Aug 26, 2009 at 03:02, Joemysql@bluepolka.net wrote: OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
Step # 1 : Stop mysql service # /etc/init.d/mysql stop Step # 2: Start to MySQL server w/o password: # mysqld_safe --skip-grant-tables Step # 3: Connect to mysql server using mysql client: # mysql -u root Step # 4: Setup new MySQL root user password mysql use mysql; mysql update user set password=PASSWORD(NEW-ROOT-PASSWORD) where User='root'; mysql flush privileges; mysql quit Step # 5: Stop MySQL Server: # /etc/init.d/mysql stop Step # 6: Start MySQL server and test it # /etc/init.d/mysql start # mysql -u root -p Note: You can check structure of table 'user' mysql SHOW CREATE TABLE 'user'; And update other required fields also. Enjoy ;-) On Wed, Aug 26, 2009 at 2:36 PM, Martijn Engler mart...@crystal-labs.nlwrote: I actually get the feeling you are not connecting as root. Try mysql -uroot -p test instead of just mysql test Have a nice day, - Martijn On Wed, Aug 26, 2009 at 03:02, Joemysql@bluepolka.net wrote: OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat
Basic SQL Query Help Needed
I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
recovery help needed
We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
You have to reset the permissions. http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Carlos On 8/25/2009 7:12 PM, Joe wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Basic SQL Query Help Needed
SELECT * FROM ORDER o INNER JOIN ORDER_LINE_ITEMS o_l ON (o.id=o_l.id) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. To: mysql@lists.mysql.com From: c...@hosting4days.com Subject: Basic SQL Query Help Needed Date: Tue, 25 Aug 2009 16:21:45 -0700 I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ With Windows Live, you can organize, edit, and share your photos. http://www.windowslive.com/Desktop/PhotoGallery
Re: recovery help needed
OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' So connect to it from somewhere other than localhost. :-) Specifically from the bogus IP you set it to. Let's say you used the bogus IP of 10.200.100.20. 1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0. 2. On some other machine on the same LAN, give it the bogus IP with the same netmask. 3. No need to worry about routes, it's on the same LAN. 4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot -p'. When you enter the correct password, it should let you in. * I don't know if you'll need to restart mysql for it to bind to the new IP. I don't think so, but then again I have not tested it. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
R: Re: recovery help needed
When you are in without the flag , issue the following: Select current_user(); It should return root. Then do this: Grant all privileges on *.* 'root'@'%' identified by 'letmein' It should work If you did not mess too much with grant tables. Claudio Il giorno 26 ago, 2009 4:36 m., Todd Lyons tly...@ivenue.com ha scritto: On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible... So connect to it from somewhere other than localhost. :-) Specifically from the bogus IP you set it to. Let's say you used the bogus IP of 10.200.100.20. 1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0. 2. On some other machine on the same LAN, give it the bogus IP with the same netmask. 3. No need to worry about routes, it's on the same LAN. 4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot -p'. When you enter the correct password, it should let you in. * I don't know if you'll need to restart mysql for it to bind to the new IP. I don't think so, but then again I have not tested it. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: h...
[MySQL] Table structure help needed
I am creating surveys for our website and want store questions and answers in a database. I found a tutorial that was useful, however it only gave structure for a single question with one answer. My surveys need to be more comprehensive than a poll question, and so I need some help with the structure of my tables. Currently, my database has these tables: -user (for admin login, unrelated currently) -survey_question -survey_option -survey_answer Here is a breakdown of the fields for each: -survey_question ---QuestionID - INT - primary key - auto_increment ---QuestionText - TEXT -survey_option ---OptionID - INT - primary key ---QuestionID - INT ---OptionText - TEXT ---OptionValue - INT -survey_answer ---AnswerID - INT - primary key ---AnswerValue - INT ---QuestionID - INT I don't see how this can be sufficient, for example if I had a question with multiple choices. I want to be able to insert all surveys into these tables, taking into consideration that there are many form input types. Can anyone let me know if my structure is sufficient? This is all new and I'm learning as much as I can. Thanks!
newbie config help needed
Hello all, I have set up a little server and am pitching to an organization to host their upcoming new site. The box is Centos 5.2 up to date and mysql 5.0.45. The requirements below must be met but I don't know how to verify their status. Can someone point me in the right direction. Will read directions if necessary Dave * Your server must have the following MySQL grant privileges (for tables): SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE * Your MySQL server must not be running in Strict mode. -- Canada must refuse to be entangled in any more wars fought to make the world safe for capitalism. -- The Regina Manifesto, 1933 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie config help needed
Dave, Log in as the user you intend to set up for them. Issue the following: show grants; select @@sql_mode; If you aren't sure how to interpret the information I will direct you to the manual: http://dev.mysql.com/doc/refman/5.0/en/ Side note: I would upgrade. 5.0.45 has some pretty serious bugs. I'd upgrade to the latest 5.0 release. Baron On Sat, Jan 31, 2009 at 4:13 PM, Dave Stevens g...@uniserve.com wrote: Hello all, I have set up a little server and am pitching to an organization to host their upcoming new site. The box is Centos 5.2 up to date and mysql 5.0.45. The requirements below must be met but I don't know how to verify their status. Can someone point me in the right direction. Will read directions if necessary Dave * Your server must have the following MySQL grant privileges (for tables): SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE * Your MySQL server must not be running in Strict mode. -- Canada must refuse to be entangled in any more wars fought to make the world safe for capitalism. -- The Regina Manifesto, 1933 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie config help needed
Hi Dave, that's easy. Either install http://www.phpmyadmin.net/home_page/index.php on the server, the best tool to manage a mysql server. Or use XAMPP, a complete package from http://www.apachefriends.org/en/index.html Guenther Dave Stevens wrote: Hello all, I have set up a little server and am pitching to an organization to host their upcoming new site. The box is Centos 5.2 up to date and mysql 5.0.45. The requirements below must be met but I don't know how to verify their status. Can someone point me in the right direction. Will read directions if necessary Dave * Your server must have the following MySQL grant privileges (for tables): SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE * Your MySQL server must not be running in Strict mode. -- 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]
Help needed
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 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]
Help needed to tune Innodb on ZFS (on Solaris)
Hi, I installed Mysql 5.0.45 on Solaris 10 01/08 configured to run the datadir on a ZFS filesystem on a Raid1 pool. Both myisam and innodb tables are on the same filesystem. Innodb is configured to run with a buffer_pool_size=256M, with doublewrite set to OFF and with file_per_table to Off. The log files are 2 x 48 MB each. 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. Following some advice found in various bloggers I created the zfs filesystem I created with a recordsize of 16K, and set flush_log_at_trx_commit to 0. In some way the benchmark have improved, but I still find Innodb much slower than Myisam. Does anyone have any experience on this particular configuration? Is there any other trick to follow to improve Innodb's performance on ZFS? Three more things that I noticed: - setting innodb_flush_method=O_DIRECT causes mysql to complain that the directio() is not implemented. - dropping the db and reimporting it from the dump is slower at every iteration. Is there any way to keep the next reimports as fast as the first one without recreating from scratch the ibdata files? - why using innodb_file_per_tables is so much slower than running innodb with a single table space? Thanks in advance for any help, Nico -- 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: stored procedure, parameter type help needed
Just use the type varchar.I always use it in my regular life.
On Feb 11, 2008 4:44 PM, Magne Westlie [EMAIL PROTECTED] wrote:
Jerry Schwartz wrote:
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here,
wouldn't
that be more efficient? Or am I (as usual) missing something?
I may be the one missing something :-). I have tried to think out of the
box and use other solutions, but haven't come up with a way that works
without using IN.
The query are to be used in a calendar-ish application, for finding when
people are free to attend meetings. The ids I send as parameter is the
ids of users that I want to check availability for. The id-list may
contain between 1 and 50 user ids.
Maybe I could use
JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...) (?)
but as far as I understand, I'd still have to generate this list as a
string because I do not know how many users to check for, and then
CONCAT the query, PREPARE etc. as described in Peter Brawley's email.
Then I think I prefere using IN.
As for the optimization of IN, I've read the following in the manual:
The search for the item then is done using a binary search. This means
IN is very quick if the IN value list consists entirely of constants.
(
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
)
, so if I am to rely on the doc, it should be very quick the way I use
it.
Thanks for your comment Jerry.
Magne
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
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]
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: stored procedure, parameter type help needed
Jerry Schwartz wrote:
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here, wouldn't
that be more efficient? Or am I (as usual) missing something?
I may be the one missing something :-). I have tried to think out of the
box and use other solutions, but haven't come up with a way that works
without using IN.
The query are to be used in a calendar-ish application, for finding when
people are free to attend meetings. The ids I send as parameter is the
ids of users that I want to check availability for. The id-list may
contain between 1 and 50 user ids.
Maybe I could use
JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...) (?)
but as far as I understand, I'd still have to generate this list as a
string because I do not know how many users to check for, and then
CONCAT the query, PREPARE etc. as described in Peter Brawley's email.
Then I think I prefere using IN.
As for the optimization of IN, I've read the following in the manual:
The search for the item then is done using a binary search. This means
IN is very quick if the IN value list consists entirely of constants.
(http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in;)
, so if I am to rely on the doc, it should be very quick the way I use it.
Thanks for your comment Jerry.
Magne
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
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: stored procedure, parameter type help needed
Hi again,
I found a way that works for the query I wanted in my sproc. It uses
your solution to insert into a temporary table, and then uses a SELECT
in the IN-part.
I don't know yet if this solution may have side-effects. I need to read
more about how temporary tables is handled by MySQL, as when it comes to
speed of execution in this solution.
Thanks for your help Peter.
My solution (with an argument that may look weird, but I found out
adding the extra paranthesis while generating the string in Python was
so much easier that doing it in the sproc) (working test):
---
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test (
user_id INT,
user_name VARCHAR(100),
PRIMARY KEY (user_id)
) ENGINE=MyIsam;
INSERT INTO user_test VALUES(1,'Bob'), (2,'Ann'), (3,'Bill'), (4,'Jim');
DROP PROCEDURE IF EXISTS get_users;
DELIMITER ::
CREATE PROCEDURE get_users(IN param VARCHAR(1000))
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_uids;
CREATE TEMPORARY TABLE temp_uids (
uid INT NOT NULL
);
SET @qry = CONCAT('INSERT INTO temp_uids VALUES ', param);
PREPARE stmt FROM @qry;
EXECUTE stmt;
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT uid
FROM temp_uids);
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I wanted
to make
the request fast, and the concatenating and string handling takes some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure, parameter type help needed
-Original Message-
From: Magne Westlie [mailto:[EMAIL PROTECTED]
Sent: Friday, February 08, 2008 5:37 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: stored procedure, parameter type help needed
Hi again,
I found a way that works for the query I wanted in my sproc. It uses
your solution to insert into a temporary table, and then uses a SELECT
in the IN-part.
I don't know yet if this solution may have side-effects. I need to read
more about how temporary tables is handled by MySQL, as when it comes
to
speed of execution in this solution.
Thanks for your help Peter.
My solution (with an argument that may look weird, but I found out
adding the extra paranthesis while generating the string in Python was
so much easier that doing it in the sproc) (working test):
---
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test (
user_id INT,
user_name VARCHAR(100),
PRIMARY KEY (user_id)
) ENGINE=MyIsam;
INSERT INTO user_test VALUES(1,'Bob'), (2,'Ann'), (3,'Bill'),
(4,'Jim');
DROP PROCEDURE IF EXISTS get_users;
DELIMITER ::
CREATE PROCEDURE get_users(IN param VARCHAR(1000))
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_uids;
CREATE TEMPORARY TABLE temp_uids (
uid INT NOT NULL
);
SET @qry = CONCAT('INSERT INTO temp_uids VALUES ', param);
PREPARE stmt FROM @qry;
EXECUTE stmt;
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here, wouldn't
that be more efficient? Or am I (as usual) missing something?
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
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]
stored procedure, parameter type help needed
Dear List, I want to create a stored procedure that runs a query using the IN operator (or is IN a function???) on values sent as argument. That is, my procedure should be called with something like: CALL get_users((1,2)); and I was hoping to implement something like this: CREATE PROCEDURE get_users(uids LIST) -- what type to use here? BEGIN SELECT * FROM user_test WHERE user_id IN uids; END:: Thanks, Magne Westlie Working test code for getting one user only: -- DROP TABLE IF EXISTS user_test; CREATE TABLE user_test ( user_id INT, user_name VARCHAR(100), PRIMARY KEY (user_id) ) ENGINE=MyIsam; INSERT INTO user_test VALUES(1, 'Bob'); INSERT INTO user_test VALUES(2, 'Ann'); INSERT INTO user_test VALUES(3, 'Bill'); DROP PROCEDURE IF EXISTS get_users; DELIMITER :: CREATE PROCEDURE get_users(uid INT) BEGIN SELECT * FROM user_test WHERE user_id = uid; END:: DELIMITER ; CALL get_users(3); -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only problem
is that the query I actually want to use this in, is a 100 line query
with lots of arguments. I don't feel to good about creating it into a
bunch of strings (16) that I have to concatenate with the variables
inbetween.
Also, I was moving the query into a stored procedure because I wanted to
make the request fast, and the concatenating and string handling takes
some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you like.
When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Magne, I want to create a stored procedure that runs a query using the IN operator ... See 'Variable-length argument for query IN() clause' at http://www.artfulsoftware.com/queries.php PB - Magne Westlie wrote: Dear List, I want to create a stored procedure that runs a query using the IN operator (or is IN a function???) on values sent as argument. That is, my procedure should be called with something like: CALL get_users((1,2)); and I was hoping to implement something like this: CREATE PROCEDURE get_users(uids LIST) -- what type to use here? BEGIN SELECT * FROM user_test WHERE user_id IN uids; END:: Thanks, Magne Westlie Working test code for getting one user only: -- DROP TABLE IF EXISTS user_test; CREATE TABLE user_test ( user_id INT, user_name VARCHAR(100), PRIMARY KEY (user_id) ) ENGINE=MyIsam; INSERT INTO user_test VALUES(1, 'Bob'); INSERT INTO user_test VALUES(2, 'Ann'); INSERT INTO user_test VALUES(3, 'Bill'); DROP PROCEDURE IF EXISTS get_users; DELIMITER :: CREATE PROCEDURE get_users(uid INT) BEGIN SELECT * FROM user_test WHERE user_id = uid; END:: DELIMITER ; CALL get_users(3); -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Hi Magne
...the query I actually want to use this in, is a 100 line query with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I wanted
to make
the request fast, and the concatenating and string handling takes some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN (
|
but so long as it has those elements, it can be as complex as you like.
When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (', ('''abc'',''def'',''ghi''' );
|
||
PB
Re: Design Help Needed
HI Sudheer, THIS DESIGN IS BASED ON MY MANY-TO-ONE DIRECTION PATTERN OF DATABASE NORMALIZATION DESIGN... PLEASE MODIFY/CORRECT IT ACCORDING TO YOUR TASTE. AS A JAVA DEVELOPER I'M USING HIBERNATE FOR MY CREATE-UPDATE-DELETE(CUD) AND DIRECT JDBC FOR MY QUERIES(R). YOU MAY VARY. HERE, HOPE THIS WILL GIVE YOU AN IDEA. LEGEND: X-TABLE, L-LONG, S-STRING/CHAR(?), T-DATETIME _ID-PRIMARY_KEY, REF-FOREIGN_KEY X:CONTACTS_AND_ADDRESSES L:CONTACT_AND_ADDRESS_ID S:WEBSITE S:EMAIL S:MESSAGING S:TELEPHONE S:MOBILE S:LAND_ADDRESS T:CREATED/MODIFIED X:INDIVIDUALS L:INDIVIDUAL_ID S:USERNAME S:PASSWORD S:DISPLAY_NAME S:PICTURE_URI S:SECURITY_QUESTION S:ANSWER T:CREATED/MODIFIED X:INDIVIDUALS_CONTACTS_AND_ADDRESSES L:INDIVIDUAL_CONTACT_AND_ADDRESS_ID L:REF_INDIVIDUAL_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS L:ESTABLISHMENT_ID S:BUSINESS_NAME T:CREATED/MODIFIED X:ESTABLISHMENTS_CONTACT_AND_ADDRESS L:ESTABLISHMENT_CONTACT_AND_ADDRESS_ID L:REF_ESTABLISHMENT_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS_OF_INDIVIDUALS L:ESTABLISHMENT_OF_INDIVIDUAL_ID L:REF_ESTABLISHMENT_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:CUSTOMERS L:CUSTOMER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:PARTNERS L:PARTNER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:EMPLOYEES_POSITIONS L:EMPLOYEE_POSITION_ID S:ROLE S:SPECIFIC_RULE S:HOWTODO T:CREATED/MODIFIED X:TIME_SCHEDULES L:TIME_SCHEDULE_ID S:APPLIED_TASK T:APPLIED_TIME L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED X:EMPLOYEES L:EMPLOYEE_ID L:REF_INDIVIDUAL_ID L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED GOOD LUCK, HOPE THIS HELPS. -Melvin Sudheer Satyanarayana [EMAIL PROTECTED] wrote: Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
RE: Design Help Needed
Hi Sudheer, First of all there a number of ways to design this database. You will need to choose the one that you feel suites your needs best. Here one possible design. Because you have different type of users/accounts, it looks like ACCOUNT_TYPE table is needed ACCOUNT_TYPE table Account_type_id - autoincrement, PK Account_type - varchar(25). ( Values in this field are: Individual,Business,Partner,Internal) Because you have user information to store: USER_INFO table User_id - autoincrement, PK Account_type_id - integer, FK (foreign key to ACCOUNT_TYPE) Fields of personal/Business information to follow - (First, Last Name, .) Because you have web site security info to store: WEB_SITE_SECURITY_INFO table User_id - integer, FK (foreign key to USER_INFO) Security_question Security_question_answer Fields of security information to follow You can grow the database as you add module, but as much as you can foresee and design ahead it would be better Mikhail Berman -Original Message- From: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 1:50 AM To: mysql@lists.mysql.com Subject: Design Help Needed Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- 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: Design Help Needed
Because you are a novice to data base design, you have fallen into a common trap. If you think about an array, you don't want to store multiple users in a row, you want to store them in a column. In other words, you want to have one table that stores businesses (once per business, probably) and another table that stores the associated users. You use a unique business ID to find all of the users for a particular business. Once you start thinking that way, you'll start to get the hang of it. 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: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 1:50 AM To: mysql@lists.mysql.com Subject: Design Help Needed Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- 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]
Design Help Needed
Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb corruption help needed!
On 3/11/07, Jean-Sebastien Pilon [EMAIL PROTECTED] wrote: I am running a mysql database server and we experienced a power failure. The mysql server does not want to restart because innodb is corrupted. Version info: Mysql version 4.1.11-Debian_4sarge7-log Debian sarge Reiserfs filesystem What I have tried: - Ran reiserfsck to fix corrupted file system. It fixed some problems, now says no corruption. - Restarted mysql with no success - Restarted mysql with innodb_force_recovery = 4 option, with no success - Restarted mysql with innodb_force_recovery = 6 option, mysql starts, but cannot access any data in my different schemas/tables See below for mysql startup log What should I do next ? Unfortuantely, it looks like your InnoDB tablespace is beyond recovery. If this data is critical and you don't have a backup, it might be worth getting MySQL Enterprise Support for this server and have them try and recover it -- see https://shop.mysql.com/enterprise/ for details. Assuming you have a backup of your data and you want to get the server back up and running so you can restore from this backup, you need to shut down the server, delete the corrupted InnoDB tablespace and associated .frm files, and start it up again (which will cause the server to re-create the tablespace from scratch): 1. Shut down mysql (/etc/init.d/mysql stop on Debian, iirc) 2. Go into your data directory, and run: a. rm -f ib* b. for f in `find -name \*.ibd`; do b=`echo $f | sed 's/.ibd//g'`; rm -f $b.ibd $b.frm ; done 3. Start the server again (/etc/init.d/mysql start on Debian, iirc) 4. Restore your backup (I'm assuming this is in a .sql file) by running the relevant sections back through the mysql program. By the way, a power outage shouldn't cause the InnoDB tablespace to become corrupted like this, it's ACID-compliant. It's possible that your disk system is doing some caching you don't know about, resulting in MySQL thinking data is on disk (or guaranteed to be put on disk) when it actually isn't. See http://brad.livejournal.com/2116715.html for details on how to test (and fix) this. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed with setting size of the table
Hi All: As the GUI tools archive doesn't help, I wanted to seek some help from here. I was migrating the database from oracle to mysql. I was having an issue migrating a table from oracle to mysql figured out that the table size on the MySQL has to be set. I'm using the migration kit which has an administrator tool also included in it. But I'm not sure as of how can I set the size of the table in it. If we just manually create a table with same columns and rows, we cannot migrate the contents of it right? So that's a bad idea. I see that only by setting the size is only the option. Can anyone help? Any suggestions? Best, Sri -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Monday, March 12, 2007 3:09 PM To: Ananda Kumar Cc: mysql@lists.mysql.com Subject: Re: tool to migrate data from oracle to mysql SQLWays also works, and it does stored procedures, etc, but it's thousands of dollars (depending on the # of objects in your database). http://www.ispirer.com/products/ David Arun Kumar PG wrote: http://www.mysql.com/products/tools/migration-toolkit/ There is a video presentation as well here for ORacle to MySQL migration. Thanks, - Arun On 3/12/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, Does any body know of any tool available for migrating data from oracle to mysql. Can you please pass on the url. regards anandkl -- 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]
Innodb corruption help needed!
Hello, I am running a mysql database server and we experienced a power failure. The mysql server does not want to restart because innodb is corrupted. Version info: Mysql version 4.1.11-Debian_4sarge7-log Debian sarge Reiserfs filesystem What I have tried: - Ran reiserfsck to fix corrupted file system. It fixed some problems, now says no corruption. - Restarted mysql with no success - Restarted mysql with innodb_force_recovery = 4 option, with no success - Restarted mysql with innodb_force_recovery = 6 option, mysql starts, but cannot access any data in my different schemas/tables See below for mysql startup log What should I do next ? Please let me know if you need any additionnal information, such as log files etc... Thanks, Jean-Sebastien Pilon *** LOG *** Mar 10 17:57:21 db01-mtl mysqld_safe[3579]: started Mar 10 17:57:21 db01-mtl mysqld[3582]: 070310 17:57:21 InnoDB: Database was not shut down normally! Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: Starting crash recovery. Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: Reading tablespace information from the .ibd files... Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: Restoring possible half-written data pages from the doublewrite Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: buffer... Mar 10 17:57:21 db01-mtl mysqld[3582]: 070310 17:57:21 InnoDB: Starting log scan based on checkpoint at Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: log sequence number 9 4253873524. Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: Doing recovery: scanned up to log sequence number 9 4253910595 Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: Probable data corruption on page 4 Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: Original record PHYSICAL RECORD: n_fields 7; 1-byte offs TRUE; info bits 0 Mar 10 17:57:21 db01-mtl mysqld[3582]: 0: len 4; hex ; asc ;; 1: len 1; hex 00; asc ;; 2: len 4; hex 000953be; asc S ;; 3: len 18; hex 860b00038603000486030004; asc ;; 4: len 3; hex 8164b7; asc d ;; 5: len 4; hex 8000; asc ;; 6: len 4; hex 00d7306e; asc 0n;; Mar 10 17:57:21 db01-mtl mysqld[3582]: Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: on that page. Steps 255. Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: Cannot find the dir slot for record PHYSICAL RECORD: n_fields 7; 1-byte offs TRUE; info bits 0 Mar 10 17:57:21 db01-mtl mysqld[3582]: 0: len 4; hex ; asc ;; 1: len 1; hex 00; asc ;; 2: len 4; hex 0009fbbe; asc ;; 3: len 18; hex 860b00038603000486030004; asc ;; 4: len 3; hex 81624d; asc bM;; 5: len 4; hex 8000; asc ;; 6: len 4; hex 00d7166f; asco;; Mar 10 17:57:21 db01-mtl mysqld[3582]: Mar 10 17:57:21 db01-mtl mysqld[3582]: InnoDB: on that page! Mar 10 17:57:21 db01-mtl mysqld[3582]: 070310 17:57:21 InnoDB: Page dump in ascii and hex (16384 bytes): Mar 10 17:57:21 db01-mtl mysqld[3582]: removed dump Mar 10 17:57:22 db01-mtl mysqld[3582]: t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t e;InnoDB: End of page dump Mar 10 17:57:22 db01-mtl mysqld[3582]: 070310 17:57:22 InnoDB: Page checksum 3805026483, prior-to-4.0.14-form checksum 706858147 Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: stored checksum 3606072843, prior-to-4.0.14-form stored checksum 9 Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: Page lsn 9 4253873884, low 4 bytes of lsn at page end 4253873884 Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: Page number (if stored to page already) 4, Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: Page may be an update undo log page Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: Page may be an index page where index id is 4294967295 0 Mar 10 17:57:22 db01-mtl mysqld[3582]: 070310 17:57:22InnoDB: Assertion failure in thread 3083643104 in file page0page.c line 112 Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: We intentionally generate a memory trap. Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: Submit a detailed bug report to http://bugs.mysql.com. Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: If you get repeated assertion failures or crashes, even Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: immediately after the mysqld startup, there may be Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: corruption in the InnoDB tablespace. Please refer to Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html Mar 10 17:57:22 db01-mtl mysqld[3582]: InnoDB: about forcing recovery. Mar 10 17:57:22 db01-mtl mysqld[3582]: mysqld got signal 11; Mar 10 17:57:22 db01-mtl mysqld[3582]: This could be because you hit a bug. It is also possible that this binary Mar 10 17:57:22 db01-mtl mysqld[3582]: or one of the libraries it was linked against is corrupt, improperly built, Mar 10 17:57:22 db01-mtl mysqld[3582]: or misconfigured. This error can also be caused
Help needed
Hi I'm having a problem compiling store procedure getting errors that don't make much sense. The problem is that I need to filter any value that is either NULL or an empty string an set to '0' character. SP is below CREATE PROCEDURE test () BEGIN DECLARE code VARCHAR(10) DEFAULT '0'; update table_name set filed_name = CASE when ISNULL(filed_name) = 1 OR CHAR_LENGTH(filed_name) = 0 then code END CASE; END; Thanks for any help.
HELP needed for speeding up a query!
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 thanks a lot in advance for any suggestions! cheers, nick -- 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]
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?
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.
Help Needed
Hi, How can I get the current value of AUTO_INCREMENT field, as the AUTO_INCREMENT field is a foreign key in some other table.please do mail it to my mailed, its very urgent! Thanks, bhanu The pessimist may be right in the long run, but the optimist has a better time during the trip. G Bhanu Prakash (Senior Member Technical Staff) Airtight Networks Pvt. Ltd., Airtight House, SN 149/1A, off ITI Road,Aundh, Pune 411007. Mobile:9850764135 THE INFORMATION IN THIS EMAIL AND ANY ATTACHMENTS IS CONFIDENTIAL AND INTENDED SOLELY FOR THE USE OF THE PERSON NAMED ABOVE. IF YOU ARE NOT THE INTENDED RECIPIENT, OR HAVE OTHERWISE RECEIVED THIS EMAIL IN ERROR, DO NOT READ, DISTRIBUTE, COPY OR OTHERWISE USE IT. IF YOU HAVE RECEIVED THIS COMMUNICATION IN ERROR, PLEASE IMMEDIATELY NOTIFY THE SENDER BY TELEPHONE OR EMAIL, AND DESTROY THIS MESSAGE AND ANY ATTACHMENTS. THANK YOU.
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: INstalling DBD for mysql--Help needed
Hello Guys, I was able to connect to mysql database using perl DBI. I was using the wrong version of Perl on HP-UX to install the DBI and DBD. There were two versions , 32 bit and 64 bit perl. by default the 32 bit perl was picked up. I changed the path to 64 bit perl interpreter and the DBI created 64 bit objects and I had to use right 64-bit flags for DBD::mysql installation. Thanks for all who helped Vinay - Original Message - From: Jake Peavy [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, March 03, 2006 5:38 PM Subject: Re: INstalling DBD for mysql--Help needed On 3/3/06, Vinay [EMAIL PROTECTED] wrote: Hello, I am trying to install DBI and mysql DBD for perl on HP-UX 11.23i. I have installed DBI successfully but having a hard time installing DBD for mysql on HP-UX. I am getting make errors while installing. I want to use perl to connect to mysql database. Any help appreciated, Thank you, vinay how do you think we're going to help? should I try to perform a Vulcan mind meld with your server? Or did you just want me to come over and fix it for you? I tell ya what - just give us your server IP, open up telnet, and provide the root password. we'll get that nasty DBD! -jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INstalling DBD for mysql--Help needed
Hello, I am trying to install DBI and mysql DBD for perl on HP-UX 11.23i. I have installed DBI successfully but having a hard time installing DBD for mysql on HP-UX. I am getting make errors while installing. I want to use perl to connect to mysql database. Any help appreciated, Thank you, vinay
Re: INstalling DBD for mysql--Help needed
On 3/3/06, Vinay [EMAIL PROTECTED] wrote: Hello, I am trying to install DBI and mysql DBD for perl on HP-UX 11.23i. I have installed DBI successfully but having a hard time installing DBD for mysql on HP-UX. I am getting make errors while installing. I want to use perl to connect to mysql database. Any help appreciated, Thank you, vinay how do you think we're going to help? should I try to perform a Vulcan mind meld with your server? Or did you just want me to come over and fix it for you? I tell ya what - just give us your server IP, open up telnet, and provide the root password. we'll get that nasty DBD! -jp
(Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED
(OT) (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED -- The Scenario: ~~ We out locked out of our door lock database. The database is Sybase Sql Anywhere version 7.0 The software uses a hard coded DBA password that is *not* the word 'sql' A Maybe Solution: ~~ The software does *not* do a check on it's ODBC DSN to see what type of driver is being used. I can successfully have the software use the MyODBC debug dll which, because it's password is unknown to the MySQL server fails with an authentication error. The debug version version of the shipped MyODBC plugin does not log the password. I need a copy of the MyODBC debug plugin which *WILL* log the password. *** PLEASE HELP US FIX OUR DOOR LOCK SYSTEM! *** ~ Michael Joyner System Administrator / 904-470-8170 Edward Waters College 1658 Kings Road Jacksonville, FL 32209 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED
Michael Joyner wrote: (OT) (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED -- The Scenario: ~~ We out locked out of our door lock database. The database is Sybase Sql Anywhere version 7.0 The software uses a hard coded DBA password that is *not* the word 'sql' A Maybe Solution: ~~ The software does *not* do a check on it's ODBC DSN to see what type of driver is being used. I can successfully have the software use the MyODBC debug dll which, because it's password is unknown to the MySQL server fails with an authentication error. The debug version version of the shipped MyODBC plugin does not log the password. I need a copy of the MyODBC debug plugin which *WILL* log the password. *** PLEASE HELP US FIX OUR DOOR LOCK SYSTEM! *** ~ Michael Joyner System Administrator / 904-470-8170 Edward Waters College 1658 Kings Road Jacksonville, FL 32209 MyODBC does not connect to Sybase. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED
gerald_clark wrote: Michael Joyner wrote: (OT) (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED -- MyODBC does not connect to Sybase. Correct. Merely trying to use it's debug features to snatch the dba password so that I can get into the db and do a hash replacement on the software's system user so that I can undo the damage caused by a vendor locking us out of the software. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Special Character translation with export help needed
Is this the wrong list to ask this on? If so can someone suggest a better list? Am I missing something in my analysis of my problem? Did I not include enough info? Could it be a problem with perl scripts? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 8, 2005, at 3:24 AM, Boysenberry Payne wrote: I tried using `mysqldump $log -v -Q -c --set-charset --default-character-set=utf8 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick $db_name -r $bk_file 21` and I still get -- MySQL dump 10.9 -- -- Host: localhostDatabase: -- -- -- Server version 4.1.14-standard /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `access` -- DROP TABLE IF EXISTS `access`; CREATE TABLE `access` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(32) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I need it to output to utf8 as I've said in my previous post. How come --default-character-set=utf8 didn't change it? Internally it's stored as utf8 and I've read mysqldump outputs to utf8 as default. Yet I'm seeing latin1. What gives? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 7, 2005, at 9:33 PM, Boysenberry Payne wrote: I think the problem is mysqldump uses this: DEFAULT CHARSET=latin1 with --set-charset How can I get it to use utf8? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 7, 2005, at 8:46 PM, Boysenberry Payne wrote: I'm having difficulty getting certain special characters to stay the same when I export databases. Here are some of the examples: View of data from phpMyAdmin: Habitat is a “what you see is what you getâ€? or “WYSIWYGâ€? View from table dump via phpMyAdmin export: Habitat is a √¢‚Ǩ≈ìwhat you see is what you get√¢‚Ǩ? or √¢‚Ǩ≈ìWYSIWYG√¢‚Ǩ? View from table dump via mysqldump: Habitat is a √ɬ¢√¢‚Äö¬¨√Ö‚Äúwhat you see is what you get√ɬ¢√¢‚Äö¬¨? or √ɬ¢√¢‚Äö¬¨√Ö‚ÄúWYSIWYG√ɬ¢√¢‚Äö¬¨? The view of the data via phpMyAdmin seems to be the closest to what I need. The special characters are created by flash then saved in the mysql ( 4.1.13 ) database. Now I'm moving them to a different mysql ( 4.1.14 ) database. Is there a better way to swap databases that will prevent the special characters from being translated? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.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] -- 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: Special Character translation with export help needed
I figured out that it wasn't really the special characters that were the issue, but the addslashes function in php with the html tags. I just got rid of the back slashes and all is good. Sorry for the noise... Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 8, 2005, at 4:57 PM, Boysenberry Payne wrote: Is this the wrong list to ask this on? If so can someone suggest a better list? Am I missing something in my analysis of my problem? Did I not include enough info? Could it be a problem with perl scripts? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 8, 2005, at 3:24 AM, Boysenberry Payne wrote: I tried using `mysqldump $log -v -Q -c --set-charset --default-character-set=utf8 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick $db_name -r $bk_file 21` and I still get -- MySQL dump 10.9 -- -- Host: localhostDatabase: -- -- -- Server version 4.1.14-standard /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `access` -- DROP TABLE IF EXISTS `access`; CREATE TABLE `access` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(32) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I need it to output to utf8 as I've said in my previous post. How come --default-character-set=utf8 didn't change it? Internally it's stored as utf8 and I've read mysqldump outputs to utf8 as default. Yet I'm seeing latin1. What gives? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 7, 2005, at 9:33 PM, Boysenberry Payne wrote: I think the problem is mysqldump uses this: DEFAULT CHARSET=latin1 with --set-charset How can I get it to use utf8? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 7, 2005, at 8:46 PM, Boysenberry Payne wrote: I'm having difficulty getting certain special characters to stay the same when I export databases. Here are some of the examples: View of data from phpMyAdmin: Habitat is a “what you see is what you getâ€? or “WYSIWYGâ€? View from table dump via phpMyAdmin export: Habitat is a √¢‚Ǩ≈ìwhat you see is what you get√¢‚Ǩ? or √¢‚Ǩ≈ìWYSIWYG√¢‚Ǩ? View from table dump via mysqldump: Habitat is a √ɬ¢√¢‚Äö¬¨√Ö‚Äúwhat you see is what you get√ɬ¢√¢‚Äö¬¨? or √ɬ¢√¢‚Äö¬¨√Ö‚ÄúWYSIWYG√ɬ¢√¢‚Äö¬¨? The view of the data via phpMyAdmin seems to be the closest to what I need. The special characters are created by flash then saved in the mysql ( 4.1.13 ) database. Now I'm moving them to a different mysql ( 4.1.14 ) database. Is there a better way to swap databases that will prevent the special characters from being translated? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Special Character translation with export help needed
I'm having difficulty getting certain special characters to stay the same when I export databases. Here are some of the examples: View of data from phpMyAdmin: Habitat is a “what you see is what you getâ€? or “WYSIWYGâ€? View from table dump via phpMyAdmin export: Habitat is a √¢‚Ǩ≈ìwhat you see is what you get√¢‚Ǩ? or √¢‚Ǩ≈ìWYSIWYG√¢‚Ǩ? View from table dump via mysqldump: Habitat is a √ɬ¢√¢‚Äö¬¨√Ö‚Äúwhat you see is what you get√ɬ¢√¢‚Äö¬¨? or √ɬ¢√¢‚Äö¬¨√Ö‚ÄúWYSIWYG√ɬ¢√¢‚Äö¬¨? The view of the data via phpMyAdmin seems to be the closest to what I need. The special characters are created by flash then saved in the mysql ( 4.1.13 ) database. Now I'm moving them to a different mysql ( 4.1.14 ) database. Is there a better way to swap databases that will prevent the special characters from being translated? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Special Character translation with export help needed
I think the problem is mysqldump uses this: DEFAULT CHARSET=latin1 with --set-charset How can I get it to use utf8? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Nov 7, 2005, at 8:46 PM, Boysenberry Payne wrote: I'm having difficulty getting certain special characters to stay the same when I export databases. Here are some of the examples: View of data from phpMyAdmin: Habitat is a “what you see is what you getâ€? or “WYSIWYGâ€? View from table dump via phpMyAdmin export: Habitat is a √¢‚Ǩ≈ìwhat you see is what you get√¢‚Ǩ? or √¢‚Ǩ≈ìWYSIWYG√¢‚Ǩ? View from table dump via mysqldump: Habitat is a √ɬ¢√¢‚Äö¬¨√Ö‚Äúwhat you see is what you get√ɬ¢√¢‚Äö¬¨? or √ɬ¢√¢‚Äö¬¨√Ö‚ÄúWYSIWYG√ɬ¢√¢‚Äö¬¨? The view of the data via phpMyAdmin seems to be the closest to what I need. The special characters are created by flash then saved in the mysql ( 4.1.13 ) database. Now I'm moving them to a different mysql ( 4.1.14 ) database. Is there a better way to swap databases that will prevent the special characters from being translated? Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.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 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]
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]
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]
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[EMAIL PROTECTED] CGI]# gcc -c -I/usr/lib/bcc/include -I/usr/include/mysql user.c In file included from /usr/include/pthread.h:20, from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/sched.h:41: parse error before __THROW /usr/include/sched.h:44: parse error before __THROW /usr/include/sched.h:48: parse error before __THROW /usr/include/sched.h:51: parse error before __THROW /usr/include/sched.h:54: parse error before __THROW /usr/include/sched.h:57: parse error before __THROW /usr/include/sched.h:60: parse error before __THROW /usr/include/sched.h:63: parse error before __THROW In file included from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/pthread.h:166: parse error before __THROW /usr/include/pthread.h:169: parse error before __THROW /usr/include/pthread.h:172: parse error before __THROW /usr/include/pthread.h:176: parse error before __THROW /usr/include/pthread.h:181: parse error before __THROW /usr/include/pthread.h:187: parse error before __THROW /usr/include/pthread.h:195: parse error before __THROW /usr/include/pthread.h:198: parse error before __THROW /usr/include/pthread.h:202: parse error before __THROW /usr/include/pthread.h:206: parse error before __THROW /usr/include/pthread.h:211: parse error before __THROW /usr/include/pthread.h:217: parse error before __THROW /usr/include/pthread.h:221: parse error before __THROW /usr/include/pthread.h:226: parse error before __THROW /usr/include/pthread.h:230: parse error before __THROW /usr/include/pthread.h:235: parse error before __THROW /usr/include/pthread.h:239: parse error before __THROW /usr/include/pthread.h:243: parse error before __THROW /usr/include/pthread.h:261: parse error before __THROW /usr/include/pthread.h:266: parse error before __THROW /usr/include/pthread.h:285: parse error before __THROW /usr/include/pthread.h:290: parse error before __THROW /usr/include/pthread.h:303: parse error before __THROW /usr/include/pthread.h:309: parse error before __THROW /usr/include/pthread.h:333: parse error before __THROW /usr/include/pthread.h:336: parse error before __THROW /usr/include/pthread.h:339: parse error before __THROW /usr/include/pthread.h:342: parse error before __THROW /usr/include/pthread.h:352: parse error before __THROW /usr/include/pthread.h:359: parse error before __THROW /usr/include/pthread.h:362: parse error before __THROW /usr/include/pthread.h:367: parse error before __THROW /usr/include/pthread.h:371: parse error before __THROW /usr/include/pthread.h:392: parse error before __THROW /usr/include/pthread.h:395: parse error before __THROW /usr/include/pthread.h:398: parse error before __THROW /usr/include/pthread.h:401: parse error before __THROW /usr/include/pthread.h:406: parse error before __THROW /usr/include/pthread.h:415: parse error before __THROW /usr/include/pthread.h:420: parse error before __THROW /usr/include/pthread.h:423: parse error before __THROW /usr/include/pthread.h:428: parse error before __THROW /usr/include/pthread.h:432: parse error before __THROW /usr/include/pthread.h:557: parse error before __THROW /usr/include/pthread.h:560: parse error before __THROW /usr/include/pthread.h:564: parse error before __THROW /usr/include/pthread.h:567: parse error before __THROW /usr/include/pthread.h:577: parse error before __THROW /usr/include/pthread.h:584: parse error before __THROW /usr/include/pthread.h:588: parse error before __THROW /usr/include/pthread.h:591: parse error before __THROW /usr/include/pthread.h:596: parse error before __THROW /usr/include/pthread.h:612: parse error before __THROW /usr/include/pthread.h:621: parse error before __THROW In file included from /usr/include/pthread.h:655, from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/bits/sigthread.h:32: parse error before '*' token /usr/include/bits/sigthread.h:36: parse error before __THROW In file included from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/pthread.h:673: parse error before __THROW /usr/include/pthread.h:678:
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]
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[EMAIL PROTECTED] CGI]# gcc -c -I/usr/lib/bcc/include -I/usr/include/mysql user.c In file included from /usr/include/pthread.h:20, from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/sched.h:41: parse error before __THROW /usr/include/sched.h:44: parse error before __THROW /usr/include/sched.h:48: parse error before __THROW /usr/include/sched.h:51: parse error before __THROW /usr/include/sched.h:54: parse error before __THROW /usr/include/sched.h:57: parse error before __THROW /usr/include/sched.h:60: parse error before __THROW /usr/include/sched.h:63: parse error before __THROW In file included from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/pthread.h:166: parse error before __THROW /usr/include/pthread.h:169: parse error before __THROW /usr/include/pthread.h:172: parse error before __THROW /usr/include/pthread.h:176: parse error before __THROW /usr/include/pthread.h:181: parse error before __THROW /usr/include/pthread.h:187: parse error before __THROW /usr/include/pthread.h:195: parse error before __THROW /usr/include/pthread.h:198: parse error before __THROW /usr/include/pthread.h:202: parse error before __THROW /usr/include/pthread.h:206: parse error before __THROW /usr/include/pthread.h:211: parse error before __THROW /usr/include/pthread.h:217: parse error before __THROW /usr/include/pthread.h:221: parse error before __THROW /usr/include/pthread.h:226: parse error before __THROW /usr/include/pthread.h:230: parse error before __THROW /usr/include/pthread.h:235: parse error before __THROW /usr/include/pthread.h:239: parse error before __THROW /usr/include/pthread.h:243: parse error before __THROW /usr/include/pthread.h:261: parse error before __THROW /usr/include/pthread.h:266: parse error before __THROW /usr/include/pthread.h:285: parse error before __THROW /usr/include/pthread.h:290: parse error before __THROW /usr/include/pthread.h:303: parse error before __THROW /usr/include/pthread.h:309: parse error before __THROW /usr/include/pthread.h:333: parse error before __THROW /usr/include/pthread.h:336: parse error before __THROW /usr/include/pthread.h:339: parse error before __THROW /usr/include/pthread.h:342: parse error before __THROW /usr/include/pthread.h:352: parse error before __THROW /usr/include/pthread.h:359: parse error before __THROW /usr/include/pthread.h:362: parse error before __THROW /usr/include/pthread.h:367: parse error before __THROW /usr/include/pthread.h:371: parse error before __THROW /usr/include/pthread.h:392: parse error before __THROW /usr/include/pthread.h:395: parse error before __THROW /usr/include/pthread.h:398: parse error before __THROW /usr/include/pthread.h:401: parse error before __THROW /usr/include/pthread.h:406: parse error before __THROW /usr/include/pthread.h:415: parse error before __THROW /usr/include/pthread.h:420: parse error before __THROW /usr/include/pthread.h:423: parse error before __THROW /usr/include/pthread.h:428: parse error before __THROW /usr/include/pthread.h:432: parse error before __THROW /usr/include/pthread.h:557: parse error before __THROW /usr/include/pthread.h:560: parse error before __THROW /usr/include/pthread.h:564: parse error before __THROW /usr/include/pthread.h:567: parse error before __THROW /usr/include/pthread.h:577: parse error before __THROW /usr/include/pthread.h:584: parse error before __THROW /usr/include/pthread.h:588: parse error before __THROW /usr/include/pthread.h:591: parse error before __THROW /usr/include/pthread.h:596: parse error before __THROW /usr/include/pthread.h:612: parse error before __THROW /usr/include/pthread.h:621: parse error before __THROW In file included from /usr/include/pthread.h:655, from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/bits/sigthread.h:32: parse error before '*' token /usr/include/bits/sigthread.h:36: parse error before __THROW In file included from /usr/include/mysql/my_global.h:149, from user.c:3: /usr/include/pthread.h:673: parse error before __THROW /usr/include/pthread.h:678:
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]
Help needed with complex Query
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]
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]
help needed to create index
creating an index is necessary for fulltext search. and when i say create fulltext index search_index on sometable(column1,column2) i get an error messg like Error: The used table type doesn't support FULLTEXT indexes Why doesn't InnoDb support FullText indexes? Is there a physical limitation in the InnoDb table structure as to why it can't support FullText indexes? Can anyone help me to find solution ? Tia, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PArtial help needed
I was able to set up a table no problem. On one of the blobs, it is coming up on the web page like a mess and not like an interpreted object as one would expect. Using Mysql 3.23.58 with php 4.3.9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help needed
I asked for help with a version of this query a few months
ago, and subsequently made some improvements to it, and also
fooled around with other parts of my app that were in need of
tuning. I've since done some more extensive benchmarking and
realized that this query really is somewhat slow. Even though
the data set is rather small and everything is (I think)
properly indexed and the joins are sensible, I can't seem to
get rid of the using temporary and using filesort in my
EXPLAIN. I'd be grateful for any suggestions for improving
this.
Here's the query (obviously I run it with different values for
subject.name and different LIMIT values, but this is
representative):
SELECT citation.*, DATE_FORMAT(citation.last_modified, '%e %M, %Y') AS
last_modified
FROM citation, subject, citation_subject
WHERE subject.name = 'History'
AND citation_subject.subject_id = subject.id
AND citation_subject.citation_id = citation.id
AND citation.deleted = 0
ORDER BY citation.stripped_word, FIELD(citation.part_of_speech, 'NOUN',
'ADJECTIVE', 'ADVERB', 'VERB'), citation.id
LIMIT 150, 50
and EXPLAIN gives me this:
*** 1. row ***
table: subject
type: ref
possible_keys: PRIMARY,name
key: name
key_len: 50
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
table: citation_subject
type: ref
possible_keys: citation_id,subject_id
key: subject_id
key_len: 4
ref: subject.id
rows: 169
Extra: Using index
*** 3. row ***
table: citation
type: eq_ref
possible_keys: PRIMARY,deleted
key: PRIMARY
key_len: 4
ref: citation_subject.citation_id
rows: 1
Extra: Using where
Finally, here are the three tables involved. I've trimmed out the
irrelevant columns:
CREATE TABLE `citation` (
`id` int(11) NOT NULL auto_increment,
`word` varchar(50) NOT NULL default '',
`stripped_word` varchar(50) default NULL,
`part_of_speech` enum('NOUN','VERB','ADJECTIVE','ADVERB') NOT NULL default
'NOUN',
`last_modified` timestamp(14) NOT NULL,
`deleted` datetime default '-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `deleted` (`deleted`),
KEY `word` (`word`),
KEY `stripped_word` (`stripped_word`)
) TYPE=MyISAM
CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `citation_id` (`citation_id`,`subject_id`),
KEY `subject_id` (`subject_id`,`citation_id`)
) TYPE=MyISAM
CREATE TABLE `subject` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`deleted` datetime default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) TYPE=MyISAM
Thank you for any suggestions.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
