Qcache - how it works?
let's say that we have the following settings: Variable_name: query_cache_limit Value: 64kB Variable_name: query_cache_size Value: 4MB and status: | Qcache_queries_in_cache| 1679 | | Qcache_inserts| 2242534| | Qcache_hits | 1058592| | Qcache_lowmem_prunes | 135641 | | Qcache_not_cached| 2558808| | Qcache_free_memory | 1717208| | Qcache_free_blocks | 485| | Qcache_total_blocks | 3885 | I wonder why Qcache_lowmem_prunes grows, even though Qcache_free_memory is almost 2MB. I thought that queries are thrown from cache only in situation, when: 1. there were some changes in data of that query 2. there is no memory for new query and Qcache_free_memory should be close to 0 to achieve the state when queries are thrown away. Anybody has an explaination for such behaviour? Thanks in advance Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locks on Heap tables
Hello everyone, We are using MySQL as the database backend on quite a big portal page with about 50.000 users and 3 mio. PIs per day. MySQL is as well the backend for the (php) session management. We are using a heap for that case as well as for instance phpbb does. Lately we are experiencing long lasting table locks due to deletes or updates on the session table. I know that heap tables only support table wide locking, but shouldn't those locks be gone quite fast? I have already checked the obvious reasons for this kind of behaviour like swapping but I couldn't find anything. Even googling didn't bring anything useful up. Hopefully someone got some ideas to solve this problem :-) Thank you in advance Hannes Rohde ¯ incoWEB.de - agentur für neue medien Stapenhorststr. 10 D-45329 Essen [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.incoWEB.de Phone Fax 0700-0-4626932 0700-0-INCOWEB Diese E-Mail enthält vertrauliche Informationen, die nur für den o.g. Empfänger bestimmt sind! Jede Kenntnisnahme, Verteilung oder Vervielfältigung durch andere Personen ist nicht zulässig. Sollten Sie diese E-Mail irrtümlich erhalten haben, melden Sie uns dies bitte unverzüglich. This email, its content and any files transmitted with it are intended solely for the addressee(s). Access, distribution or copying by any other party is not permitted. If you are not the intended recipient, then please notify us immediately by returning it to the originator. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
telnet localhost 3306 Connection refused
What is causing me to have this problem in mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686). I am running shorewall, but that shouldn't affect localhost should it? My firewall, web, and mySQL server are the same machine. # telnet localhost 3306 Trying 127.0.0.1... telnet: Unable to connect to remote host: Connection refused I've tried to comment, uncomment and change to * this 'bind-address' line in /etc/mysql/my.cnf # keep secure by default! #bind-address= 127.0.0.1 #bind-address= * port= 3306 Of course, I can't connect from any of the other IP addresses that my mySQL server is assigned either: # telnet 192.168.1.1 3306 Trying 192.168.1.1... telnet: Unable to connect to remote host: Connection refused # telnet 10.10.10.1 3306 Trying 10.10.10.1... telnet: Unable to connect to remote host: Connection refused # ifconfig eth1 Link encap:Ethernet inet addr:192.168.1.1 Bcast:192.168.1.255 Mask:255.255.255.0 loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 wlan0 Link encap:Ethernet inet addr:10.10.10.1 Bcast:10.255.255.255 Mask:255.255.255.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: telnet localhost 3306 Connection refused
ps ax|grep sql if mysql is running. and if you telnet to that port (3306) and still you can't connect. check if you have blocked your port on your firewall. usually, firewall for linux are iptables and ipchains flush them first and try. HTH On 7/7/05, Daevid Vincent [EMAIL PROTECTED] wrote: What is causing me to have this problem in mysql Ver 12.22 Distrib 4.0.24 , for pc-linux-gnu (i686). I am running shorewall, but that shouldn't affect localhost should it? My firewall, web, and mySQL server are the same machine. # telnet localhost 3306 Trying 127.0.0.1... telnet: Unable to connect to remote host: Connection refused I've tried to comment, uncomment and change to * this 'bind-address' line in /etc/mysql/my.cnf # keep secure by default! #bind-address = 127.0.0.1 http://127.0.0.1 #bind-address = * port = 3306 Of course, I can't connect from any of the other IP addresses that my mySQL server is assigned either: # telnet 192.168.1.1 http://192.168.1.1 3306 Trying 192.168.1.1... telnet: Unable to connect to remote host: Connection refused # telnet 10.10.10.1 http://10.10.10.1 3306 Trying 10.10.10.1... telnet: Unable to connect to remote host: Connection refused # ifconfig eth1 Link encap:Ethernet inet addr:192.168.1.1 http://192.168.1.1 Bcast:192.168.1.255http://192.168.1.255Mask: 255.255.255.0 http://255.255.255.0 lo Link encap:Local Loopback inet addr:127.0.0.1 http://127.0.0.1 Mask:255.0.0.0 http://255.0.0.0 wlan0 Link encap:Ethernet inet addr:10.10.10.1 http://10.10.10.1 Bcast:10.255.255.255http://10.255.255.255Mask: 255.255.255.0 http://255.255.255.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Louie Miranda http://www.axishift.com -- under development
Re: telnet localhost 3306 Connection refused
Daevid Vincent wrote: What is causing me to have this problem in mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686). I am running shorewall, but that shouldn't affect localhost should it? My firewall, web, and mySQL server are the same machine. Shorewall can affect localhost as well Try adding : ACCEPT fw fw udp 3306 ACCEPT fw fw tcp 3306 in your /etc/shorewall/rules (where fw is the zone represented by your machine, it depends on your settings). You should also add the settings for http ( port 80 ). And eventually change the logging mode for dropped paquets, that should give you a clue. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: telnet localhost 3306 Connection refused
Try /sbin/iptables -L -n make sure there's no rule that block connection to/from port 3306 (TCP) Daevid Vincent wrote: What is causing me to have this problem in mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686). I am running shorewall, but that shouldn't affect localhost should it? My firewall, web, and mySQL server are the same machine. # telnet localhost 3306 Trying 127.0.0.1... telnet: Unable to connect to remote host: Connection refused I've tried to comment, uncomment and change to * this 'bind-address' line in /etc/mysql/my.cnf # keep secure by default! #bind-address= 127.0.0.1 #bind-address= * port= 3306 Of course, I can't connect from any of the other IP addresses that my mySQL server is assigned either: # telnet 192.168.1.1 3306 Trying 192.168.1.1... telnet: Unable to connect to remote host: Connection refused # telnet 10.10.10.1 3306 Trying 10.10.10.1... telnet: Unable to connect to remote host: Connection refused # ifconfig eth1 Link encap:Ethernet inet addr:192.168.1.1 Bcast:192.168.1.255 Mask:255.255.255.0 loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 wlan0 Link encap:Ethernet inet addr:10.10.10.1 Bcast:10.255.255.255 Mask:255.255.255.0 -- Regards, Ady Wicaksono HP: +628562208680 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change collation on all existing databases, tables and columns
Hello there, I run MySQL 4.1.12 and have some databases with the default collation of latin1_swedish_ci. I have edited the my.cnf file to read default-collation = latin1_german1_ci Now I would like to change the collation on all existing databases, tables and columns to have the latin1_german1_ci collation, as the my.cnf entry only works for new entries. Is there an easy command or tool to achieve that? I don't want to send the appropriate command mentioning every single database, table and column, but rather look for a FOR loop or a wildcard. Thanks Florian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Misconfigured master - server id was not set
Gleb Paharenko wrote: Hello. Setting master to 127.0.0.1 could produce a problem. MASTER_HOST and MASTER_PORT are the hostname (or IP address) of the master host and its TCP/IP port. Note that if MASTER_HOST is equal to localhost, then, like in other parts of MySQL, the port may be ignored (if Unix socket files can be used, for example). I got hit by that already when I tried to use localhost for the master host, and the slave tried to connect to itself. This was fixed by using 127.0.0.1, and the authentication as the replication user that I created on the master works fine since then. Jan Schneider [EMAIL PROTECTED] wrote: Hi, I have slave-master-setup that is special in two ways: 1) The slave connects through an stunnel 2) The slave replicates only one db As soon as I start the slave process, the server connects but fails with the following log messages: Jul 6 14:58:11 ijssel1 mysqld[11755]: 050706 14:58:11 Slave SQL thread initialized, starting replication in log 'mysql-bin.001' at position 227973, relay log './ijssel1-relay-bin.001' position: 4 Jul 6 14:58:11 ijssel1 mysqld[11755]: 050706 14:58:11 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3307', replication started in log 'mysql-bin.001' at position 227973 Jul 6 14:58:11 ijssel1 mysqld[11755]: 050706 14:58:11 Error reading packet from server: Misconfigured master - server id was not set (server_errno=1236) Jul 6 14:58:11 ijssel1 mysqld[11755]: 050706 14:58:11 Got fatal error 1236: 'Misconfigured master - server id was not set' from master when reading data from binary log Jul 6 14:58:11 ijssel1 mysqld[11755]: 050706 14:58:11 Slave I/O thread exiting, read up to log 'mysql-bin.001', position 227973 Jul 6 14:58:18 ijssel1 mysqld[11755]: 050706 14:58:18 Error reading relay log event: slave SQL thread was killed On the master side I see the stunnel connecting. To rule out corrupted binary logs, I did a RESET MASTER on the master. I verified with SHOW VARIABLES and SHOW BINLOG EVENTS that the master has server-id 1, the slave 2, and the server-id 1 is correctly used in the binlogs. Any ideas? Jan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hierarchical relations / innodb
Hi, I want to buil a hierarchical database, with different kind of relations. I have differents elements which are linked between them by different kinds of relations. Ex: element 1 IS A element 72 element 22 IS PART OF element 36 I want to have a table elements,for several reasons: I want to keep each element unique and indexed, if the definition of element 72 has to been modified is relation to element 1 wouldn't be modified as I'm working with indexes only to express the relationship and if element 72 has an other relation with something else it is updated at once. Then I think about having a table relations, something like, id_child, id_parent, kind of relation. that would give for example 1,72,IS A 22,36, PART OF ... Until here I think this is the right way to proceed, because it's the more flexible approach and will allow all the possible interactions. For the final depending application, we want to output a graphical tree of the relations/elements. I think this is possible with that design with performances ok as we won't have huge degrees of depth and we won't have a huge number of elements. Now the problem: One user want to delete element 72 for example. 2 options: it's impossible because element 72 as a child or we decide to warn the user and delete the childs at the same time. We haven't made the final decision yet, but the mechanism is still the same:deleting an element should check for existing children in the table relations. I'm used to work with myIsam, I could easily do somthing like select * from relations where id_parent=72, but I would like to know if it is possible to implement that with innoDB? I also would like to delete the element (or store it somewhere else) if it is orphaned, eg if element 1 was only the child of 72 and 72 is deleted the record in the relations table as to be deleted but the record in the elements table should be flaged or moved in an orphan table. In addition, if a user is working on element 72 we want to lock all the children. Sorry for the long explanation, I hope it is clear enough 2 questions: - do you see any problem with the design I've choosen?efficiency in building the tree for example,problem to establish relations? - do you think it is possible to use innodb in an efficient way for that (constraints and cascaded delete and locks) and do you have any good pointer on how to do it?or is it better to keep myIsam and manually do the checks? Thank you very much for taking time to read this, hope you will have any idea/comment! melanie _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change collation on all existing databases, tables and columns
Hello. If you don't want to write a script (I think it shouldn't be difficult anyway) for this purposes, you could use mysqldump with --skip-create-options or --compatible=no_table_options,no_field_options. Using this you could get the dump of you database and table definitions without information about collation. Then just import the dump and all tables will have the same collation. However, you can loose some important table properties. Florian Effenberger [EMAIL PROTECTED] wrote: Hello there, I run MySQL 4.1.12 and have some databases with the default collation of latin1_swedish_ci. I have edited the my.cnf file to read default-collation = latin1_german1_ci Now I would like to change the collation on all existing databases, tables and columns to have the latin1_german1_ci collation, as the my.cnf entry only works for new entries. Is there an easy command or tool to achieve that? I don't want to send the appropriate command mentioning every single database, table and column, but rather look for a FOR loop or a wildcard. Thanks Florian -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locks on Heap tables
Hello. Are you sure that your server doesn't swap? Providing output of 'SHOW STATUS', 'SHOW VARIABLES' and your table definition could give more information for suggestions. Also, if you have a hash index on a MEMORY table that has a high degree of key duplication (many index entries containing the same value), updates to the table that affect key values and all deletes are significantly slower. The degree of slowdown is proportional to the degree of duplication (or, inversely proportional to the index cardinality). You can use a BTREE index to avoid this problem. Hannes Rohde [EMAIL PROTECTED] wrote: Hello everyone, We are using MySQL as the database backend on quite a big portal page with about 50.000 users and 3 mio. PIs per day. MySQL is as well = the backend for the (php) session management. We are using a heap for that = case as well as for instance phpbb does.=20 Lately we are experiencing long lasting table locks due to deletes or updates on the session table. I know that heap tables only support table wide locking, but shouldn't those locks be gone quite fast? I have = already checked the obvious reasons for this kind of behaviour like swapping but = I couldn't find anything. Even googling didn't bring anything useful up. Hopefully someone got some ideas to solve this problem :-) Thank you in advance Hannes Rohde =AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF= =AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF incoWEB.de - agentur f=FCr neue medien Stapenhorststr. 10 D-45329 Essen [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.incoWEB.de Phone Fax 0700-0-4626932 0700-0-INCOWEB Diese E-Mail enth=E4lt vertrauliche Informationen, die nur f=FCr den = o.g. Empf=E4nger bestimmt sind! Jede Kenntnisnahme, Verteilung oder Vervielf=E4ltigung durch andere Personen ist nicht zul=E4ssig. Sollten = Sie diese E-Mail irrt=FCmlich erhalten haben, melden Sie uns dies bitte = unverz=FCglich. This email, its content and any files transmitted with it are intended solely for the addressee(s). Access, distribution or copying by any = other party is not permitted. If you are not the intended recipient, then = please notify us immediately by returning it to the originator.=20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: telnet localhost 3306 Connection refused
Hello. Are you sure that mysql is running? Is it possible that you have skip_networking in your configuration file? See: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Daevid Vincent [EMAIL PROTECTED] wrote: What is causing me to have this problem in mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686). I am running shorewall, but that shouldn't affect localhost should it? My firewall, web, and mySQL server are the same machine. # telnet localhost 3306 Trying 127.0.0.1... telnet: Unable to connect to remote host: Connection refused I've tried to comment, uncomment and change to * this 'bind-address' line in /etc/mysql/my.cnf # keep secure by default! #bind-address= 127.0.0.1 #bind-address= * port= 3306 Of course, I can't connect from any of the other IP addresses that my mySQL server is assigned either: # telnet 192.168.1.1 3306 Trying 192.168.1.1... telnet: Unable to connect to remote host: Connection refused # telnet 10.10.10.1 3306 Trying 10.10.10.1... telnet: Unable to connect to remote host: Connection refused # ifconfig eth1 Link encap:Ethernet inet addr:192.168.1.1 Bcast:192.168.1.255 Mask:255.255.255.0 loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 wlan0 Link encap:Ethernet inet addr:10.10.10.1 Bcast:10.255.255.255 Mask:255.255.255.0 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Qcache - how it works?
Hello. I think you have a big fragmentation of your query cache. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. See: http://dev.mysql.com/doc/mysql/en/query-cache-configuration.html Remigiusz Soko$owski [EMAIL PROTECTED] wrote: let's say that we have the following settings: Variable_name: query_cache_limit Value: 64kB Variable_name: query_cache_size Value: 4MB and status: | Qcache_queries_in_cache| 1679 | | Qcache_inserts| 2242534| | Qcache_hits | 1058592| | Qcache_lowmem_prunes | 135641 | | Qcache_not_cached| 2558808| | Qcache_free_memory | 1717208| | Qcache_free_blocks | 485| | Qcache_total_blocks | 3885 | I wonder why Qcache_lowmem_prunes grows, even though Qcache_free_memory is almost 2MB. I thought that queries are thrown from cache only in situation, when: 1. there were some changes in data of that query 2. there is no memory for new query and Qcache_free_memory should be close to 0 to achieve the state when queries are thrown away. Anybody has an explaination for such behaviour? Thanks in advance Remigiusz -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: noob : advice on design?
Hello. In archives at: http://lists.mysql.com/mysql you could find a lot of questions about database design and good answers with explanations. Monty Harris [EMAIL PROTECTED] wrote: Dear Group, I have just completed the Sams MySQL in 24 hours and feel like I now have a reasonable understanding of creatinf queries, etc. However, the one area that seemed sadly lacking was that of database design. I recently purchased a book named Database design for mere mortals, which seems to be very slow going, and is going to take me forever to get through the 550 pages. I'm not looking for a quick fix, but there has to be some middle ground. Is there anywhere I can go to get a reasonable working knowledge of database design just so I can get started with the task I have been given. In the meantime I can make my way through this book. So, where do I go to learn about the initial design, seeing as it is so crucial? -- 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]
Prepared grant statement?
Hi list: I don't know if this the right forum to ask the following questions: Will MySQL production version 5.0 support grant in prepared statements? The yet part is encouraging in ERROR 1295 (HY000) at line 17: This command is not supported in the prepared statement protocol yet Will prepared statements in stored procedures be supported? (I read that it is disabled right now) Regards, Adolfo __ Renovamos el Correo Yahoo! Nuevos servicios, más seguridad http://correo.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Locks on Heap tables
Hello yet again, Thanks for the quick answer, Gleb! I am quite sure that the system doesn't swap. I'll give the BTREE index a shot and I will let you know what effect it had. Here's a the free Output from one of the systems: total used free sharedbuffers cached Mem: 20688042018276 50528 0 41644 902912 -/+ buffers/cache:1073720 995084 Swap: 2096472 606522035820 'Show status' outputs the following: +++ | Variable_name | Value | +++ | Aborted_clients| 5071 | | Aborted_connects | 48869 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 339| | Bytes_received | 1378529900 | | Bytes_sent | 632139443 | | Com_admin_commands | 7 | | Com_alter_db | 0 | | Com_alter_table| 1 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 339| | Com_change_db | 849180 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 339| | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 1 | | Com_dealloc_sql| 0 | | Com_delete | 792523 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 1 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 2285560| | Com_insert_select | 291| | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 3 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 1 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 4527361| | Com_set_option | 729| | Com_show_binlog_events | 0 | | Com_show_binlogs | 83 | | Com_show_charsets | 159| | Com_show_collations| 159| | Com_show_column_types | 0 | | Com_show_create_db | 6 | | Com_show_create_table | 200| | Com_show_databases | 71 | | Com_show_errors| 0 | | Com_show_fields| 768| | Com_show_grants| 20 | | Com_show_innodb_status | 0 | | Com_show_keys | 19 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_privileges| 0 | | Com_show_processlist | 76 | | Com_show_slave_hosts | 3 | | Com_show_slave_status | 0 | | Com_show_status| 1 | | Com_show_storage_engines | 0 | | Com_show_tables| 356| | Com_show_variables | 270| | Com_show_warnings | 0 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_truncate | 64 | | Com_unlock_tables
Re: Network drive
I would like to thank you all for the help and the explanations. I think I'll stick to the option of having the database behind the firewall. The problem is I don't control that part of the company, as you can imagine so I'll have to ask the network administrators for that. Just want to resume what was said here: - Even if it was possible, RDBMS over a network is not recommended - if you're working behind a firewall, you'd better open a port for your db server - adding SSL can help making it more secure --- [EMAIL PROTECTED] wrote: Ruben Carvalho [EMAIL PROTECTED] wrote on 07/06/2005 12:54:24 PM: Well, thank you very much for your explanation. My problem is I would like to have the data files being saved in a machine behind a proxy but the server running in a machine outside the proxy (the clients don't have access to the machine behind the proxy). Any ideas? Thank you --- [EMAIL PROTECTED] wrote: Ruben Carvalho [EMAIL PROTECTED] wrote on 07/06/2005 11:06:10 AM: I think I haven't understood your question. I guess that in case of a network failure you can have the same behavior as a power shutdown. About the networked drives? Anyone? --- Martijn Tonies [EMAIL PROTECTED] wrote: Hi Ruben, I would like to make a short, quick and simple question. Is it possible to have the following line: innodb_data_home_dir=X:/data/ in a my.ini config file? I'm using windows XP, mysql 4.1.12, X: is a mapped network drive to a Linux folder using samba, all the permissions are set and tested. I have seen this posted many times but without many replies. I want to use a folder in a mapped drive to save my InnoDB data. Is this possible? I don't know this particular answer for MySQL, but I do have 1 question: If the database engine doesn't have control over the files and/or drive, what should it do in the case of a network failure? Let alone the latency of a networked file... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Rúben Carvalho RDBMS over a network: NOT recommended. Not only can you not enforce OS-level locking on your files (maybe you can, I guess it may depend on your device and inteface protocols) but the MOST COMMON bottleneck to database performance is disk I/O. If you went with networked storage, you are not only going to suffer through disk lag (seek time + rotational positioning before the operation can start) but you are incurring network overhead on top of it IN BOTH DIRECTIONS. Unless your network device is flash-only (all memory, no disks), you just cut your throughput by at least 75%. And even if your device is flash-only you will reduce your data throughput by 25-50% (all performance numbers are rough estimates pulled out of my a** but based on the number of extra network hops necessary to get at and read your files). I don't care how fast your network is, networked storage can't be as fast as local disks. Again, it is highly discouraged to use networked storage for anything but the most trivial database uses (small file sizes, low traffic, etc). For any application that requires even modest performance, spend your money on a fast RAID configuration. You will be much happier in the long run. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rúben Carvalho There are different kinds of secure setups. As a first idea, your database server and your web server DO NOT need to be on the same machine. There are MANY ways to setup a secure web system. How many of each type of networking component are at your disposal (proxy servers, firewalls, web servers, network interface cards, routers, etc.)? Different types of security are available with different hardware/software configurations. Basically, it all boils down to keeping the users only where you want the users to be (outside of your network) and allowing only certain servers (or even just particular processes on those servers) to access your internal resources. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rúben Carvalho ___ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com -- MySQL General Mailing
Re: Datediff
Scott Haneda [EMAIL PROTECTED] wrote on 07/06/2005 08:16:41 PM:
I am using 4.0.18-standard
So I do not have `DATEDIFF`, but I need to ability to do so, anyone know
some other simple trick to get days between two dates?
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
Use UNIX_TIMESTAMP() to convert your dates into integers (seconds) and
convert
Here's how I would compute the # of days between 2005-01-01 06:00:00 and
2005-02-01 18:00:00 (it should be 31 days 12 hours or 31.5)
localhost.(none)select (UNIX_TIMESTAMP('2005-02-01
18:00:00')-UNIX_TIMESTAMP('2005-01-01 06:00:00'))/(60*60*24);
+--+
| (UNIX_TIMESTAMP('2005-02-01 18:00:00')-UNIX_TIMESTAMP('2005-01-01
06:00:00'))/(60*60*24) |
+--+
| 31.50 |
+--+
See how that works? (Difference in seconds)/(seconds in a day) =
difference in days
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Connections and open files weirdness?
Hi, I'm running out of resources even though I think I shouldn't. The open_files_limit=256, max_connections=246, and table_cache=64 and I'm trying to actively create 'Too many open file errors' with this configuration, to be able to quantify the effect of raising the filesystem's ulimit, and the variables mentioned above. The first problem is this: I can only make 236 connections and not 246 (Threads_connected). Further, with the 236 connections opened: as soon as I do the first table join (just two tables), I get the 'Too many open files' error. But... Open_files is only 5 and Open_tables is 1. So even though it appears that my resources aren't spent, I still have an unuseable system. Why? MySQL 4.1.10, Solaris 8, 1 GB RAM, key_buffer_size=264MB. Tables are flushed before I start making the connections. Thanks for your time. -- Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical relations / innodb
Hello. I think such kind of logic could be implemented on database layer, because the main task for you is to keep data integrity, and this was one of the reason of database creation. However, without triggers your task becomes difficult. Unfortunately they're available only in MySQL 5.xx, which is not production ready yet. mel list_php [EMAIL PROTECTED] wrote: Hi, I want to buil a hierarchical database, with different kind of relations. I have differents elements which are linked between them by different kinds of relations. Ex: element 1 IS A element 72 element 22 IS PART OF element 36 I want to have a table elements,for several reasons: I want to keep each element unique and indexed, if the definition of element 72 has to been modified is relation to element 1 wouldn't be modified as I'm working with indexes only to express the relationship and if element 72 has an other relation with something else it is updated at once. Then I think about having a table relations, something like, id_child, id_parent, kind of relation. that would give for example 1,72,IS A 22,36, PART OF ... Until here I think this is the right way to proceed, because it's the more flexible approach and will allow all the possible interactions. For the final depending application, we want to output a graphical tree of the relations/elements. I think this is possible with that design with performances ok as we won't have huge degrees of depth and we won't have a huge number of elements. Now the problem: One user want to delete element 72 for example. 2 options: it's impossible because element 72 as a child or we decide to warn the user and delete the childs at the same time. We haven't made the final decision yet, but the mechanism is still the same:deleting an element should check for existing children in the table relations. I'm used to work with myIsam, I could easily do somthing like select * from relations where id_parent=72, but I would like to know if it is possible to implement that with innoDB? I also would like to delete the element (or store it somewhere else) if it is orphaned, eg if element 1 was only the child of 72 and 72 is deleted the record in the relations table as to be deleted but the record in the elements table should be flaged or moved in an orphan table. In addition, if a user is working on element 72 we want to lock all the children. Sorry for the long explanation, I hope it is clear enough 2 questions: - do you see any problem with the design I've choosen?efficiency in building the tree for example,problem to establish relations? - do you think it is possible to use innodb in an efficient way for that (constraints and cascaded delete and locks) and do you have any good pointer on how to do it?or is it better to keep myIsam and manually do the checks? Thank you very much for taking time to read this, hope you will have any idea/comment! melanie _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- 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]
Another generalization hierarchy problem
Dear list: I am preparing an entity relationship diagram and encountered the following problem: The entity individual has the attributes firstname, lastname. The entity company has the attributes name, companytype. Now I wish to include the subtype customer with the attributes taxid, billingaddress. However, a customer may be either an individual or a company, and I understand that a subtype can only have one supertype. (I cannot put customer as the supertype because there are many individuals and companies that are not customers.) How can I implement the subentity customer in my ERD? Can I do it at all? Thank you Alberto Brea -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
[EMAIL PROTECTED] wrote on 07/07/2005 11:22:52 AM: Dear list: I am preparing an entity relationship diagram and encountered the following problem: The entity individual has the attributes firstname, lastname. The entity company has the attributes name, companytype. Now I wish to include the subtype customer with the attributes taxid, billingaddress. However, a customer may be either an individual or a company, and I understand that a subtype can only have one supertype. (I cannot put customer as the supertype because there are many individuals and companies that are not customers.) How can I implement the subentity customer in my ERD? Can I do it at all? Thank you Alberto Brea What would be wrong with using the same information for company as you have for individual (a company of one). Technically (even though they are the same physical being) they are two different logical entities in relationship to your system. If you define a customer as a business entity, company, with which you have some relationship then people need to impersonate companies in order to form that relationship. Individuals are distinguished by the fact that they are members of a company and you do not have direct business with them but rather with their parent entity, the company by way of the customer object. For the case of direct consumer purchases, the company and individual records would be identical. Another option is to have customer become the supertype and you derive two subtypes company and individual. The qualification would be at the customer level of whether they are a current, future (contact), or past customer... It's a change of perspective and focuses on the business relationship and the entities that share that relationship rather than the entities and what relationship you have with them. Last idea: Expand your idea of company and individual so that they fit on the same table entity (or whatever works in your naming model. Then you could use an attribute on the table to distinguish between public and corporate customers. I like this idea least as it muddles two distinct entities into one. So my suggestions summed up: a) Make individuals also their own companies b) Modify your entity diagram so that both companies and individuals become subtypes of customer c) Change your design so that companies and individuals are subtypes of a common entity. Create your customer relationships to that common parent. I am sure there will be dozens of other suggestions coming in from others on the list...(hint hint!) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
IGNORE: test only
IGNORE: test only since I did not get my last posting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb crashes during heavy usage with exceeded memory error
Kasthuri, Maybe it's time to re-think your application architecture? A 200-meg BLOB is quite large for a highly-concurrent system, considering that MySQL will have to read/save it in its entirety _and_ allocate network buffers for it, so essentially you're allocating _400_ megs or so _per_ client. Thanks a lot for all who responded. Yes, I'm fighting that battle with developers right now. Until I can convince developers to redesign their application, I'm working on ways to keep mysql from not crashing. I think our option is to move to 64 bit machine or store session data on local disk instead of in the database. Thanks again. Kasthuri (not to mention that many of your web sessions are sending 200 megs of data around your network between your appserver(s) and your database, which is a performance issue as well) -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD4DBQFCzBgMtvXNTca6JD8RAhPYAKDDqEMlqXKM1q+cEkj2DTUcR795EQCY4h8J xIIf3/Uyktd0PO5M6573qw== =gWXC -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A problem with privileges
Hi everyone, My environment: - Linux 2.6.7-1.7asp #1 Thu Jul 15 17:36:07 YEKST 2004 i686 i386 GNU/Linux - server version: 4.0.13 I wrote a script-SQL like this: #Begin of script-SQL ... DATA_BEGIN=$1 DATA_END=$2 TIME_BEGIN=$3 TIME_END=$4 USER_NAME=$5 PRICE=$6 CUR_TABLE=acc_cur TMP_TABLE=acc_tmp mysql -h 198.168.68.1 -u info blg TTT2 DROP TABLE IF EXISTS $TMP_TABLE; CREATE TABLE $TMP_TABLE SELECT cur_date,cur_time,traffic FROM $CUR_TABLE LIMIT 1; DELETE FROM $TMP_TABLE; INSERT INTO $TMP_TABLE SELECT cur_date,cur_time,count(*) FROM $CUR_TABLE WHERE user_name = '$USER_NAME' AND cur_date = '$DATA_BEGIN' AND cur_date '$DATA_END' AND cur_time = '$TIME_BEGIN' AND cur_time = '$TIME_END' GROUP BY cur_date,cur_time; SELECT (count(*)*($PRICE)/60) FROM $TMP_TABLE; DROP TABLE $TMP_TABLE; TTT2 #End of script-SQL When I grant privileges for user 'info' like this: +--+ | Grants for [EMAIL PROTECTED]/255.255.255.0 | +--+ | GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'info'@'198.168.68.0/255.255.255.0' | | GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `blg`.* TO 'info'@'198.168.68.0/255.255.255.0' | +--+ The script-SQL, printed above, execute Ok. When I grant privileges for user 'info' like this: +--+ | Grants for [EMAIL PROTECTED]/255.255.255.0 | +--+ | GRANT USAGE ON *.* TO 'info'@'198.168.68.0/255.255.255.0'| | GRANT SELECT ON `blg`.* TO 'info'@'198.168.68.0/255.255.255.0' | | GRANT INSERT, DELETE, CREATE, DROP ON `blg`.`acc_tmp` TO 'info'@'198.168.68.0/255.255.255.0' | +--+ When I tried to execute the script-SQL, I get error: ERROR 1142 (0) at line 2: drop command denied to user: '[EMAIL PROTECTED]' for table 'acc_tmp' Help me, pls. Many thanks Vitalij -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
Dear Shawn, Thanks for your reply. I find that individuals and companies each have attributes that are completely irrelevant to the other. E.g. individuals have sex and language (so e-mail can be sent to them as Dear Sir or Dear Madam in both English and Spanish). The only time they have information in common is when they are customers, where they have a tax id, billing address, sales, etc. By company I mean any juridical entity (I didn't use the word entity in order not to confuse it with ERD entities). So a company can have many individuals and an individual can also have multiple companies (e.g. the firm he works for, a club, a professional association). An individual can be of the subtype personal_relation or customer but for some persons both types overlap (e.g. a personal friend with whom I also do business). I think that I cannot put 'customer' as the supertype because many 'individuals' and 'companies' are not customers (I wish they were :-)) so they wouldn't share the 'customer' attributes. ATTEMPT: I thought of having an individual_customer and an company_customer as subtypes of individual and company, respectively. But in your experience, wouldn't it be a mess to have half of the customers in one entity and half in the other? Best regards, Alberto Brea [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
Dear Shawn, Thanks for your reply. I find that individuals and companies each have attributes that are completely irrelevant to the other. E.g. individuals have sex and language (so e-mail can be sent to them as Dear Sir or Dear Madam in both English and Spanish). The only time they have information in common is when they are customers, where they have a tax id, billing address, sales, etc. By company I mean any juridical entity (I didn't use the word entity in order not to confuse it with ERD entities). So a company can have many individuals and an individual can also have multiple companies (e.g. the firm he works for, a club, a professional association). An individual can be of the subtype personal_relation or customer but for some persons both types overlap (e.g. a personal friend with whom I also do business). I think that I cannot put 'customer' as the supertype because many 'individuals' and 'companies' are not customers (I wish they were :-)) so they wouldn't share the 'customer' attributes. ATTEMPT: I thought of having an individual_customer and an company_customer as subtypes of individual and company, respectively. But in your experience, wouldn't it be a mess to have half of the customers in one entity and half in the other? Best regards, Alberto Brea [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?
Can you help set me straight? I recently upgraded from coldfusion 5 to coldfusion mx 7. In the process I also switched from odbc to jdbc connections to mysql (4.0.21-nt-max-log). From looking at the query log, I'm now getting... SHOW VARIABLES ...once per connection followed by... SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED - followed by one or more... ^ SELECT ^ ^ followed by... ^ SET autocommit=1 ^ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ^ - this pattern repeats until the final Quit for the connection. I can include actual entries from the query log if you would like to see them. Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables? I am not using transactions and InnoDB or BDB tables. Since I am not using transactions is this statement irrelevant (and unneeded)? I'm also a little surprised by the SET autocommit=1. The manual says... The other non-transactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called ``atomic operations.'' In transactional terms, MyISAM tables effectively always operate in AUTOCOMMIT=1 mode. Atomic operations often offer comparable integrity with higher performance. and... By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk. and... Note that if you are not using transaction-safe tables, any changes are stored at once, regardless of the status of autocommit mode. So it would seem that the SET autocommit=1 commands being seen in the query log are not needed. I checked and autocommit is enabled (not that it would seem to matter since I'm using MyISAM tables)... mysql SELECT @@AUTOCOMMIT; +--+ | @@autocommit | +--+ |1 | +--+ Am I right that neither SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED SET autocommit=1 are appropriate/needed for MyISAM tables? If that is true, then I guess my next step is to try and find out why coldfusion mx 7 is issuing them to MySQL when using MyISAM tables. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connections and open files weirdness?
Hello. Your value 256 for open_files_limit is too low. Set it at least to several thousands. See: http://dev.mysql.com/doc/mysql/en/not-enough-file-handles.html Martijn van den Burg [EMAIL PROTECTED] wrote: Hi, I'm running out of resources even though I think I shouldn't. The open_files_limit=3D256, max_connections=3D246, and table_cache=3D64 a= nd I'm trying to actively create 'Too many open file errors' with this configuration, to be able to quantify the effect of raising the filesystem's ulimit, and the variables mentioned above. The first problem is this: I can only make 236 connections and not 246 (Threads_connected). Further, with the 236 connections opened: as soon as I do the first table join (just two tables), I get the 'Too many open files' error. But... Open_files is only 5 and Open_tables is 1. So even though it appears that my resources aren't spent, I still have an unuseable system. Why? MySQL 4.1.10, Solaris 8, 1 GB RAM, key_buffer_size=3D264MB. Tables are flushed before I start making the connections. Thanks for your time. -- Martijn -- =0D The information contained in this communication and any attachments is co= nfidential and may be privileged, and is for the sole use of the intended= recipient(s). Any unauthorized review, use, disclosure or distribution i= s prohibited. If you are not the intended recipient, please notify the se= nder immediately by replying to this message and destroy all copies of th= is message and any attachments. ASML is neither liable for the proper and= complete transmission of the information contained in this communication= , nor for any delay in its receipt. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Daniel Gaddis wrote: Can you help set me straight? I recently upgraded from coldfusion 5 to coldfusion mx 7. In the process I also switched from odbc to jdbc connections to mysql (4.0.21-nt-max-log). From looking at the query log, I'm now getting... SHOW VARIABLES ...once per connection followed by... SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED - followed by one or more... ^ SELECT ^ ^ followed by... ^ SET autocommit=1 ^ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ^ - this pattern repeats until the final Quit for the connection. I can include actual entries from the query log if you would like to see them. Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables? I am not using transactions and InnoDB or BDB tables. Since I am not using transactions is this statement irrelevant (and unneeded)? I'm also a little surprised by the SET autocommit=1. The manual says... The other non-transactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called ``atomic operations.'' In transactional terms, MyISAM tables effectively always operate in AUTOCOMMIT=1 mode. Atomic operations often offer comparable integrity with higher performance. and... By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk. and... Note that if you are not using transaction-safe tables, any changes are stored at once, regardless of the status of autocommit mode. So it would seem that the SET autocommit=1 commands being seen in the query log are not needed. I checked and autocommit is enabled (not that it would seem to matter since I'm using MyISAM tables)... mysql SELECT @@AUTOCOMMIT; +--+ | @@autocommit | +--+ |1 | +--+ Am I right that neither SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED SET autocommit=1 are appropriate/needed for MyISAM tables? If that is true, then I guess my next step is to try and find out why coldfusion mx 7 is issuing them to MySQL when using MyISAM tables. Daniel, All of those items are _required_ by the JDBC specification. Some of them are being set by the JDBC driver when you create a new connection (i.e. SET autocommit=1), others by your connection pool (most likely, for things like SET SESSION TRANSACTION ISOLATION LEVEL, as the JDBC spec requires connections newly created or being taken from a pool to be in a certain state). Also, notice that unless your application is under _extreme_ load, none of these SET queries will are likely to have an impact on the performance of your application. There's not an easy way to _not_ do them, as software built on top of JDBC expects those values to be set correctly. If you're using a newer version of our JDBC driver (3.1.x), you can always add useLocalSessionState=true to avoid having to do _some_ of these queries to the database. You'll also want to have your connection pool hold on to connections for some short amount of time so there's a potential for re-use, instead of creating a new connection every time. The show variables query is used by the JDBC driver to configure various internal things based on what version of MySQL the driver is connected to, and how you've chosen to configure the server. It can't be avoided. Once again, if you're using version 3.1.x of the driver, you can put cacheServerConfiguration=true in your JDBC URL, and the values from this query will be cached, however if you reconfigure your database server, you'll need to restart your appserver to pick up the new values. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCzXXntvXNTca6JD8RAq/IAKCQUdX3XxdWGt4232QL1DIGHDwXUACfSzr1 +W6uZKDy+35vYvPBDmrBsJw= =ZtjH -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored function problems (Was: UDF failure)
Hi,
Still having difficulty with stored functions. I downloaded and installed
MySQL 5.0.7 and
I *still* cannot get stored functions to work.
I used the example for hello from section 19.2.1 of the online documentation
for MySQL and the example code doesnt work for me either.
Below are the versions from, respectively, the MySQL cli, the MySQL server
and my Linux and following that is a transcript from the mysql cli trying to
create the hello function.
mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline
4.3
mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community
Edition - Standard (GPL)
Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386
GNU/Linux
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 5.0.7-beta-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql DELIMITER //
mysql
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
-
- DELIMITER ;
- //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'RETURN CONCAT('Hello, ',s,'!');
DELIMITER' at line 2
--
Nic Stevens - [EMAIL PROTECTED]
Re: Another generalization hierarchy problem
[EMAIL PROTECTED] wrote on 07/07/2005 01:57:33 PM: Dear Shawn, Thanks for your reply. I find that individuals and companies each have attributes that are completely irrelevant to the other. E.g. individuals have sex and language (so e-mail can be sent to them as Dear Sir or Dear Madam in both English and Spanish). The only time they have information in common is when they are customers, where they have a tax id, billing address, sales, etc. By company I mean any juridical entity (I didn't use the word entity in order not to confuse it with ERD entities). So a company can have many individuals and an individual can also have multiple companies (e.g. the firm he works for, a club, a professional association). An individual can be of the subtype personal_relation or customer but for some persons both types overlap (e.g. a personal friend with whom I also do business). I think that I cannot put 'customer' as the supertype because many 'individuals' and 'companies' are not customers (I wish they were :-)) so they wouldn't share the 'customer' attributes. ATTEMPT: I thought of having an individual_customer and an company_customer as subtypes of individual and company, respectively. But in your experience, wouldn't it be a mess to have half of the customers in one entity and half in the other? Best regards, Alberto Brea [EMAIL PROTECTED] Not really... You have your actual customer information in two places (individual and company) already. Creating two kinds of customer relationships actually makes some sense. Sure you have two customer tables but you can make them seem like one if you can create a view (v 5.0+) or use a merge table (MyISAM tables only) or use a UNION query (v4.0+) Having them split into two tables will simplify certain queries (show me all corporate customers). You could also add additional customer relation information to the company_customer table so that you can keep more details about them than you do individual_customers (most businesses want to turn big customers into bigger customers. This extra information could assist with that) Just so that we are on the same page...I think this is roughly where you are headed. All properties are merely ideas and not suggestions: --Objects-- Customer - the purchaser of at least one Order of goods or services Company - an organization composed of one or more Individuals Individual - a person that may or may not be part of an Company Customer_Company - the details of the business relationship between you and a Company that is also a Customer Customer_Individual - the details of the business relationship between you and an Individual that is also a Customer Order - The sale of one or more goods or services to a customer --Property lists-- Customer: ID, date of first order, date of last order, # of orders placed, total value ordered Company: ID, name, billing address, shipping address Individual: ID, name(s), billing address, shipping address Customer_Company (details about the relationship of a company AS a customer): ID, Customer_id, Company_id, Contact Histories (list), Status,... Customer_Individual (details about the relationship of an individual AS a customer): ID, Customer_id, Individual_id, Contact Histories(list), Status,... The Contact Histories (I couldn't think of a better term right off the top of my head) would be the records of correspondence (sales letters, billing, faxes, ...) and phone calls between you and your customers. I guess they should probably attach to the Company and Individual objects that way you can record pre-sales and post-departure contact information, too. However, I think you can see that I treat the relationship *itself* as an object that has a life of it's own. So far, I really like your design. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Stored function problems (Was: UDF failure)
At 11:41 -0700 7/7/05, Nic Stevens wrote:
Hi,
Still having difficulty with stored functions. I downloaded and installed
MySQL 5.0.7 and
I *still* cannot get stored functions to work.
I used the example for hello from section 19.2.1 of the online documentation
for MySQL and the example code doesnt work for me either.
Below are the versions from, respectively, the MySQL cli, the MySQL server
and my Linux and following that is a transcript from the mysql cli trying to
create the hello function.
mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline
4.3
mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community
Edition - Standard (GPL)
Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386
GNU/Linux
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 5.0.7-beta-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql DELIMITER //
mysql
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
-
- DELIMITER ;
- //
The example in the manual has the delimiter ; line following the //
line, not preceding it.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'RETURN CONCAT('Hello, ',s,'!');
DELIMITER' at line 2
--
Nic Stevens - [EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
Alberto,
Persons and companies are distinctive entities, yes. Treating either as
if it were a subspecies of the other leads one into absurdities. Either
may be a customer, but neither need be. You haven't described the
problem context. Conceivably one or the other could also be a
contractor, a subcontractor, a supplier. Here's a common solution, one
we've often used to a client's satisfaction.
Let a 'party' be any entity with which your firm does any sort of
transaction--customers who buy something from you, contractors who do
something for you, suppliers who sell you something, c. Thus you likely
need a transaction_types table eg 'customer', 'contractor', 'supplier',
'regulator' c).
So far, you recognise two party types, persons and companies, but others
are easy to think of (government departments. NGOs c). A party has a
row in a parties table: partyID (int auto_increment), a name (char(50)
eg 'Buffo Blair', 'ABC Cleaners', 'Inland Revenue'), and a partytype
attribute (char(10) eg 'person', 'company', 'govt dept'c) which refers
to a partytypes lookup table (partytype char(10) PK).
Parties have addresses, possibly several of them, so an address table is
the container for all address info including address type (eg
'business', 'home', 'vacation', 'temporary place of incarceration' c).
Every address row has a partyID value which points at a row in parties
to indicate whose address it is.
Persons have their special attributes ('language', 'credit card number',
c), so you have a persons table for all that including a partyID column
pointing at a row in the parties table.
Likewise companies have their own special attributes, so you have a
companies table for all that, again including a column for partyID and
of course a column for personal contact (pointing at a persons row of
course).
Then a customer is merely a party that buys something, so the customers
table has columns for customer-specific info plus a column which points
at a parties row, where it finds the customer's name, type, c. When it
comes time to write the app or web customer form, you hide the details
of how to display and edit customer party info in a Customer View.
Likewise for Address Views, Invoices and so on
PB
-
[EMAIL PROTECTED] wrote:
Dear Shawn,
Thanks for your reply.
I find that individuals and companies each have attributes that
are completely irrelevant to the other. E.g. individuals have sex
and language (so e-mail can be sent to them as Dear Sir or Dear
Madam in both English and Spanish). The only time they have
information in common is when they are customers, where they have a
tax id, billing address, sales, etc.
By company I mean any juridical entity (I didn't use the
word entity in order not to confuse it with ERD entities). So
a company can have many individuals and an individual can also
have multiple companies (e.g. the firm he works for, a club, a
professional association).
An individual can be of the subtype personal_relation
or customer but for some persons both types overlap (e.g. a
personal friend with whom I also do business).
I think that I cannot put 'customer' as the supertype because
many 'individuals' and 'companies' are not customers (I wish they
were :-)) so they wouldn't share the 'customer' attributes.
ATTEMPT: I thought of having an individual_customer and
an company_customer as subtypes of individual and company,
respectively.
But in your experience, wouldn't it be a mess to have half of the
customers in one entity and half in the other?
Best regards,
Alberto Brea
[EMAIL PROTECTED]
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored function problems (Was: UDF failure)
Nic,
At the end of the func, your sproc delimiter // needs to come before
restoration of the semi-colon as delimiter, thus:
DELIMITER //
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
//
DELIMITER ;
PB
-
Nic Stevens wrote:
Hi,
Still having difficulty with stored functions. I downloaded and installed
MySQL 5.0.7 and
I *still* cannot get stored functions to work.
I used the example for hello from section 19.2.1 of the online documentation
for MySQL and the example code doesnt work for me either.
Below are the versions from, respectively, the MySQL cli, the MySQL server
and my Linux and following that is a transcript from the mysql cli trying to
create the hello function.
mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline
4.3
mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community
Edition - Standard (GPL)
Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386
GNU/Linux
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 5.0.7-beta-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql DELIMITER //
mysql
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
-
- DELIMITER ;
- //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'RETURN CONCAT('Hello, ',s,'!');
DELIMITER' at line 2
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count(*)
select count(*) from store group by orederId. For the above sql, I am not getting the count of unique order ids.. help!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count(*)
[snip] select count(*) from store group by orederId. For the above sql, I am not getting the count of unique order ids.. [/snip] select orderID, count(*) from store group by orderID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count(*)
select count(distinct ordr_ID) from store -Original Message- From: Gana [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 3:01 PM To: mysql@lists.mysql.com Subject: Count(*) select count(*) from store group by orederId. For the above sql, I am not getting the count of unique order ids.. help!! -- 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: Another generalization hierarchy problem
Thanks to all for you help and time. After reading to replies, I think that perhaps a clean way of dealing with individual and company customers in a single table could be to leave them out of the parties tree altogether and relate them to the parties table without a generalization hierarchy, but one-to- one from a customers table. For instance: 1) PARTIES TREE: Parent: PARTIES Attr: id, type Subtypes of Parties (complete and disjoint): INDIVIDUALS Attr: id, firstname, lastname, sex, language JURENTITIES Attr: id, name, type Subtypes of Individuals (complete and overlapping): PERSONAL Attr: id, birthday BUSINESS Attr: id, title, specialization 2) CUSTOMERS TREE: Parent: CUSTOMERS Attr: partyid (references parties), taxid, billingaddress Subtypes of customers (complete and overlapping): SUBSCRIBERS Attr: partyid (references parties), startdate, enddate SERVICE1USERS Attr: partyid (references parties), xxx SERVICE2USERS Attr: partyid (references parties), xxx 3) OUTSIDE BOTH TREES: PHONES Attr: id, number, areacode (relates M-M with parties) ADDRESSES Attr: id, street,etc (relates M-M with parties) EMAILS Attr: id, email (relates M-M with parties) URLS Attr: id, url (relates M-M with parties) The three would need an associative table separating them from the parties table, with a composite key Am I going wrong somewhere? Alberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
Thanks to all for you help and time. After reading to replies, I think that perhaps a clean way of dealing with individual and company customers in a single table could be to leave them out of the parties tree altogether and relate them to the parties table without a generalization hierarchy, but one-to- one from a customers table. For instance: 1) PARTIES TREE: Parent: PARTIES Attr: id, type Subtypes of Parties (complete and disjoint): INDIVIDUALS Attr: id, firstname, lastname, sex, language JURENTITIES Attr: id, name, type Subtypes of Individuals (complete and overlapping): PERSONAL Attr: id, birthday BUSINESS Attr: id, title, specialization 2) CUSTOMERS TREE: Parent: CUSTOMERS Attr: partyid (references parties), taxid, billingaddress Subtypes of customers (complete and overlapping): SUBSCRIBERS Attr: partyid (references parties), startdate, enddate SERVICE1USERS Attr: partyid (references parties), xxx SERVICE2USERS Attr: partyid (references parties), xxx 3) OUTSIDE BOTH TREES: PHONES Attr: id, number, areacode (relates M-M with parties) ADDRESSES Attr: id, street,etc (relates M-M with parties) EMAILS Attr: id, email (relates M-M with parties) URLS Attr: id, url (relates M-M with parties) The three would need an associative table separating them from the parties table, with a composite key Am I going wrong somewhere? Alberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: telnet localhost 3306 Connection refused
Have you checked that the user failing to connect has the right privileges? Check the user table of mysql database to see if that user has a select privilege in that table. Laurie At 01:01 AM 7/7/2005, Daevid Vincent wrote: What is causing me to have this problem in mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686). I am running shorewall, but that shouldn't affect localhost should it? My firewall, web, and mySQL server are the same machine. # telnet localhost 3306 Trying 127.0.0.1... telnet: Unable to connect to remote host: Connection refused I've tried to comment, uncomment and change to * this 'bind-address' line in /etc/mysql/my.cnf # keep secure by default! #bind-address= 127.0.0.1 #bind-address= * port= 3306 Of course, I can't connect from any of the other IP addresses that my mySQL server is assigned either: # telnet 192.168.1.1 3306 Trying 192.168.1.1... telnet: Unable to connect to remote host: Connection refused # telnet 10.10.10.1 3306 Trying 10.10.10.1... telnet: Unable to connect to remote host: Connection refused # ifconfig eth1 Link encap:Ethernet inet addr:192.168.1.1 Bcast:192.168.1.255 Mask:255.255.255.0 loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 wlan0 Link encap:Ethernet inet addr:10.10.10.1 Bcast:10.255.255.255 Mask:255.255.255.0 -- 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: A problem with privileges
Privet! User with similar privileges successfully drops table on my MySQL 5.0.7: mysql drop table acc_tmp; Query OK, 0 rows affected (0.01 sec) mysql show grants for current_user; +-+ | Grants for [EMAIL PROTECTED] | +-+ | GRANT USAGE ON *.* TO 'info'@'localhost' | | GRANT SELECT ON `blg`.* TO 'info'@'localhost' | | GRANT INSERT, DELETE, CREATE, DROP ON `blg`.`acc_tmp` TO 'info'@'localhost' | +-+ 3 rows in set (0.00 sec) mysql select database(); ++ | database() | ++ | blg| ++ 1 row in set (0.00 sec) Your 4.0.13 version is very old and could contain bugs. Check if problem exists on the latest release (4.1.12 or if you unable to use 4.1 - on 4.0.25). Kaplenko Vitalij [EMAIL PROTECTED] wrote: Hi everyone, My environment: - Linux 2.6.7-1.7asp #1 Thu Jul 15 17:36:07 YEKST 2004 i686 i386 GNU/Linux - server version: 4.0.13 I wrote a script-SQL like this: #Begin of script-SQL ... DATA_BEGIN=$1 DATA_END=$2 TIME_BEGIN=$3 TIME_END=$4 USER_NAME=$5 PRICE=$6 CUR_TABLE=acc_cur TMP_TABLE=acc_tmp mysql -h 198.168.68.1 -u info blg TTT2 DROP TABLE IF EXISTS $TMP_TABLE; CREATE TABLE $TMP_TABLE SELECT cur_date,cur_time,traffic FROM $CUR_TABLE LIMIT 1; DELETE FROM $TMP_TABLE; INSERT INTO $TMP_TABLE SELECT cur_date,cur_time,count(*) FROM $CUR_TABLE WHERE user_name = '$USER_NAME' AND cur_date = '$DATA_BEGIN' AND cur_date '$DATA_END' AND cur_time = '$TIME_BEGIN' AND cur_time = '$TIME_END' GROUP BY cur_date,cur_time; SELECT (count(*)*($PRICE)/60) FROM $TMP_TABLE; DROP TABLE $TMP_TABLE; TTT2 #End of script-SQL When I grant privileges for user 'info' like this: +--+ | Grants for [EMAIL PROTECTED]/255.255.255.0 | +--+ | GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'info'@'198.168.68.0/255.255.255.0' | | GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `blg`.* TO 'info'@'198.168.68.0/255.255.255.0' | +--+ The script-SQL, printed above, execute Ok. When I grant privileges for user 'info' like this: +--+ | Grants for [EMAIL PROTECTED]/255.255.255.0 | +--+ | GRANT USAGE ON *.* TO 'info'@'198.168.68.0/255.255.255.0'| | GRANT SELECT ON `blg`.* TO 'info'@'198.168.68.0/255.255.255.0' | | GRANT INSERT, DELETE, CREATE, DROP ON `blg`.`acc_tmp` TO 'info'@'198.168.68.0/255.255.255.0' | +--+ When I tried to execute the script-SQL, I get error: ERROR 1142 (0) at line 2: drop command denied to user: '[EMAIL PROTECTED]' for table 'acc_tmp' Help me, pls. Many thanks Vitalij -- 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]
database structure question...
hi... i'm considering an app where i'm going to parse a lot of colleges (~1000) faculty information. would it be better to have all the faculty information in one large table or would it be better/faster to essentially place each college in it's own separate table, and reference each table by a college_ID, that's unique and assigned to each college, and maintained in a master_collegeTBL... thoughts/comments/etc i'm leaning towards the side that keeps each college information separate, although this means that i essentially have to deal with 1000s of tables/files... -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure question...
bruce wrote: hi... i'm considering an app where i'm going to parse a lot of colleges (~1000) faculty information. would it be better to have all the faculty information in one large table or would it be better/faster to essentially place each college in it's own separate table, and reference each table by a college_ID, that's unique and assigned to each college, and maintained in a master_collegeTBL... thoughts/comments/etc i'm leaning towards the side that keeps each college information separate, although this means that i essentially have to deal with 1000s of tables/files... -bruce [EMAIL PROTECTED] You're *far* better off putting everything in one table and using a field in the table, for example CollegeID, to identify which column you're dealing with. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database structure question...
even though this might mean i get a table with 5 million records??? as opposed to say a 1000 different tables, each with 50,000 records? -bruce -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 5:34 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: database structure question... bruce wrote: hi... i'm considering an app where i'm going to parse a lot of colleges (~1000) faculty information. would it be better to have all the faculty information in one large table or would it be better/faster to essentially place each college in it's own separate table, and reference each table by a college_ID, that's unique and assigned to each college, and maintained in a master_collegeTBL... thoughts/comments/etc i'm leaning towards the side that keeps each college information separate, although this means that i essentially have to deal with 1000s of tables/files... -bruce [EMAIL PROTECTED] You're *far* better off putting everything in one table and using a field in the table, for example CollegeID, to identify which column you're dealing with. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure question...
I would strongly recommend creating one table, with a column that stores the college_ID for each faculty member, and a separate table to correlate college name and college_id. For example... Create table faculty ( last_name varchar(50), first_name varchar(50), college_id int, primary key (last_name, first_name), key c_id (college_id) ); Create table colleges ( college_name varchar(50), college_id int, primary key (college_id) ); This sort of structure will allow you to easily and quickly retrieve all faculty for a given college (select last_name, first_name from faculty where college_id=$id). Also, if a faculty member were to be transferred to another college w/in your system, it is easy to update (update faculty set college_id=$new_college where last_name=Smith and first_name=John). Or, to find what college a given faculty member is at, (select college_id from faculty where last_name=Smith and first_name=John). Finding a faculty member from ~1,000 tables would be very, very painful, not to mention slow. Another reason not to store each college in its own table is that on many file systems, there is a limit to the number of files allowed within one directory, regardless of how small the files are. I believe that on most linux's, it is in the tens of thousands. Not likely to be reached, but if your application grew to encompass tens of thousands of colleges, you would eventually run out of room. (See http://answers.google.com/answers/threadview?id=122241 for an explanation.) ~ Devananda bruce wrote: hi... i'm considering an app where i'm going to parse a lot of colleges (~1000) faculty information. would it be better to have all the faculty information in one large table or would it be better/faster to essentially place each college in it's own separate table, and reference each table by a college_ID, that's unique and assigned to each college, and maintained in a master_collegeTBL... thoughts/comments/etc i'm leaning towards the side that keeps each college information separate, although this means that i essentially have to deal with 1000s of tables/files... -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database structure question...
as of now.. i've heard that there might be a file limit.. but given that i'm using linux, i doubt it.. and if there is a limiit.. i'm sure it's a kernel option that i can tweek... in my app, i'm not worried about profs transferring between schools... that data's going to be ptreety static, and separate between schools.. but i haven't heard anyone talk to the issue of timing, with regards to doing queries/selects/etc... although, i can imagine the kind of query that might stretch across multiple tables (10) might get to be painful... on the other hand, if i have all the information in a single table and need to make a change to the table, i'd have to move around/modify/deal with a serious number of records, whereas, if the college data is in separate tables, it would make changes alot easier or, i could do a hybrid solution if performing actual queries makes sense.. i could have a 'temp' master collegeTBL that contains all the information, and this table is comprised of the smaller separate collegeTBLS, and i could simply make any changes to the smaller tbls, and rebuild the master table from time to time... hmmm -bruce -Original Message- From: Devananda [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 6:07 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: database structure question... I would strongly recommend creating one table, with a column that stores the college_ID for each faculty member, and a separate table to correlate college name and college_id. For example... Create table faculty ( last_name varchar(50), first_name varchar(50), college_id int, primary key (last_name, first_name), key c_id (college_id) ); Create table colleges ( college_name varchar(50), college_id int, primary key (college_id) ); This sort of structure will allow you to easily and quickly retrieve all faculty for a given college (select last_name, first_name from faculty where college_id=$id). Also, if a faculty member were to be transferred to another college w/in your system, it is easy to update (update faculty set college_id=$new_college where last_name=Smith and first_name=John). Or, to find what college a given faculty member is at, (select college_id from faculty where last_name=Smith and first_name=John). Finding a faculty member from ~1,000 tables would be very, very painful, not to mention slow. Another reason not to store each college in its own table is that on many file systems, there is a limit to the number of files allowed within one directory, regardless of how small the files are. I believe that on most linux's, it is in the tens of thousands. Not likely to be reached, but if your application grew to encompass tens of thousands of colleges, you would eventually run out of room. (See http://answers.google.com/answers/threadview?id=122241 for an explanation.) ~ Devananda bruce wrote: hi... i'm considering an app where i'm going to parse a lot of colleges (~1000) faculty information. would it be better to have all the faculty information in one large table or would it be better/faster to essentially place each college in it's own separate table, and reference each table by a college_ID, that's unique and assigned to each college, and maintained in a master_collegeTBL... thoughts/comments/etc i'm leaning towards the side that keeps each college information separate, although this means that i essentially have to deal with 1000s of tables/files... -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure question...
bruce wrote: even though this might mean i get a table with 5 million records??? as opposed to say a 1000 different tables, each with 50,000 records? -bruce That's right. Databases are made for this sort of thing. If you have a separate table for each location, constructing queries to pull data from a number of them at once will be an absolute nightmare, not to mention what will happen if you have to modify the table structure. For example, what do you do if you want to see all records that were entered yesterday? You run 1000 separate queries! You can bet that this will be slower than if everything was in 1 table. Seriously, put everything in 1 table. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure question...
You have not said what type of information you will be storing in this database. Is it going to be just faculty information? Even if it is just faculty information, you do realize that each school treats departments a bit dfferently. The faculyt maybe under different school, may specialize in certain field(s). What about all those data? Or are you going strictly for First, Last, College type simple scenario? On 7/7/05, Daniel Kasak [EMAIL PROTECTED] wrote: bruce wrote: even though this might mean i get a table with 5 million records??? as opposed to say a 1000 different tables, each with 50,000 records? -bruce That's right. Databases are made for this sort of thing. If you have a separate table for each location, constructing queries to pull data from a number of them at once will be an absolute nightmare, not to mention what will happen if you have to modify the table structure. For example, what do you do if you want to see all records that were entered yesterday? You run 1000 separate queries! You can bet that this will be slower than if everything was in 1 table. Seriously, put everything in 1 table. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Issue with AS and sub queries
Hi there somehow my AS field alias of a sub query is adding a dot at the start therefore I cant use it in my application. (SELECT SUM(feed_usage.bandwidth) AS bandwidth FROM feed_usage WHERE customerID IN (57) AND DATE_FORMAT(feed_usage.stats_date,'%m%Y')=DATE_FORMAT(NOW(),'%m%Y') ) AS total_bandwidth , comes up as .total_bandwidth in my resultset any ideas ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Prepared grant statement?
Yes even I have problems working with stored procs without prepared statements support. It would be great if someone from MySQL team can tell if they have plans to include prepared statements in stored procs in the production release of MySQL5.0. Regards sujay -Original Message- From: Adolfo Bello [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 4:28 PM To: Mysql Lists Subject: Prepared grant statement? Hi list: I don't know if this the right forum to ask the following questions: Will MySQL production version 5.0 support grant in prepared statements? The yet part is encouraging in ERROR 1295 (HY000) at line 17: This command is not supported in the prepared statement protocol yet Will prepared statements in stored procedures be supported? (I read that it is disabled right now) Regards, Adolfo __ Renovamos el Correo Yahoo! Nuevos servicios, más seguridad http://correo.yahoo.es -- 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]
