Re: where is the stored functions STORED?
Hi, Dan Nelson Rolando Edwards It works, thank you. :-) 2009/3/26 Rolando Edwards redwa...@logicworks.net: The table mysql.proc always contains the hard code of stored procedures and functions. Triggers in 5.0 are stored in a .TRG file Make sure, if you used mysqldump, to include --triggers as a dump option Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Cui Shijun [mailto:rancp...@gmail.com] Sent: Thursday, March 26, 2009 7:44 AM To: mysql@lists.mysql.com Subject: where is the stored functions STORED? hi, It seems that the stored functions isn't stored in a seperated file like trigger does. Where are them? If I copy the database directory from a server to another, can I get the same functions on the both servers? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Master-master replication configuration...how?
Hi I have 2 servers and I am trying to set up (for testing purposes) a master-master replication environment. I read the instructions in the manual for master-slave, but am not able to find any information on how to set up a master-master system. Can someone on this list point me to any documentation on this topic? Also, I am new to replication. Could someone briefly tell me typical problems with this kind of setup, or why it is/is not advisable? TIA -- V
Re: Master-master replication configuration...how?
blue.trapez...@gmail.com wrote: Hi I have 2 servers and I am trying to set up (for testing purposes) a master-master replication environment. I read the instructions in the manual for master-slave, but am not able to find any information on how to set up a master-master system. Can someone on this list point me to any documentation on this topic? What you are probably trying to achieve is bi-directional replication (arguably different from multi master but as close as you can get) - take a look at auto_increment_increment and auto_increment_offset, the idea is that all your primary keys use auto_increment integers and one master will be offset from the other (ie: server A uses 1,3,5,7,9...etc and server B uses 2,4,6,8...etc) so they never clash Also, I am new to replication. Could someone briefly tell me typical problems with this kind of setup, or why it is/is not advisable? What's your primary reason for wanting multi master? It's worth noting that this approach shouldnt be used if your primary concern is master load... since using traditional statement based replication, each master is going to process all write queries. We're currently implementing a passive/active multi master set up using MMM (http://code.google.com/p/mysql-master-master/) to handle failover, it's a lot safer. Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication Issues
That appear to have been it, thanks to all for the assist and have a great weekend! Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Thursday, March 26, 2009 18:00 To: mysql@lists.mysql.com Subject: Re: Replication Issues I notice in the startup messages there is an underscore in the file names, but there are dashes in the dir list you provided. ??? On 3/26/09, Dirk Bremer dirk.bre...@nisc.coop wrote: On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General
Data structure for matching for company data
I'm a complete newbie, so please feel free to help me here! I simplified my problem as below: 1. Thousands of companies (i.e. company names) 2. 200 industries. Each company has tree-type(?) industry info. (Industry, Sub-industry. Say, each industry has 10 sub-industry information. (10X20)) The problem is one company can have 1,2,3... or 15 industries and/or sub-industries... (as in a real-life case) What would be a smart way create tables for this situation? (both company and industry must be searchable) (One rough thought (after having studying MySQL for 2 weeks now) is create 3 tables of 1. company (3 cols) - company id(pk), company name 2. industry (3 cols) - industry id(pk), industry, sub-industry 3. matching table (3 cols?) - match id(pk), company id(fk), industry id(fk)...? But, one company can have more than 1 industry info! Ah... Then, if that's the case, different match id same company id different industry id??) I'm stuck here! :( Any general advice would be appreciated... :) _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com
Re: Data structure for matching for company data
Choi 1. company (3 cols) - company id(pk), company name 2. industry (3 cols) - industry id(pk), industry, sub-industry 3. matching table (3 cols?) - match id(pk), company id(fk), industry id(fk)...? Yes, you've got it. In the matching (usually called bridging) table, any company or industry can appear multiple times, but no combo can appear more than once. PB http://www.artfulsoftware.com - ChoiSaehoon wrote: I'm a complete newbie, so please feel free to help me here! I simplified my problem as below: 1. Thousands of companies (i.e. company names) 2. 200 industries. Each company has tree-type(?) industry info. (Industry, Sub-industry. Say, each industry has 10 sub-industry information. (10X20)) The problem is one company can have 1,2,3... or 15 industries and/or sub-industries... (as in a real-life case) What would be a smart way create tables for this situation? (both company and industry must be searchable) (One rough thought (after having studying MySQL for 2 weeks now) is create 3 tables of 1. company (3 cols) - company id(pk), company name 2. industry (3 cols) - industry id(pk), industry, sub-industry 3. matching table (3 cols?) - match id(pk), company id(fk), industry id(fk)...? But, one company can have more than 1 industry info! Ah... Then, if that's the case, different match id same company id different industry id??) I'm stuck here! :( Any general advice would be appreciated... :) _ ???, ?? ??? Windows Live Hotmail! ?? ???! http://www.hotmail.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.238 / Virus Database: 270.11.30/2026 - Release Date: 03/27/09 07:13:00
Search based where claused and stored proc
I'm working on a small project of re-implementing all of the sql for a web site. The task is pretty trivial but overall there are some minor things that I'm trying to code through. We've moved much of the logic over to stored procs and call them with parameterized queries. This works well since there isn't much inject attack possibility on these. Now I have one query left, which allows for an arbitrary number of search parameters, all using AND. Has anyone accomplished coverting something like this to a stored proc in mysql? Logically I could pass in the parameters in as an array of words, or a wordlist to be broken up inside the proc, but I don't want to spend a bunch of time either reinventing the wheel or working to a goal that can't be accomplished. We could build the base query dynamically in the code using standard sql and bind the parameters to it that way but since we've moved everything else to procs I figured I'd look into this as well. BTW, this is a project I brought onto after they found they had a sql injection bug in there code that was exploited... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Search based where claused and stored proc
Ben Wiechman Network Administrator Wisper High Speed Internet Office: 866.394.7737 Direct: 320.256.0184 Cell: 320.247.3224 b...@wisper-wireless.com -Original Message- From: Gary Smith [mailto:g...@primeexalia.com] Sent: Friday, March 27, 2009 12:59 PM To: mysql@lists.mysql.com Subject: [MySQL] Search based where claused and stored proc I'm working on a small project of re-implementing all of the sql for a web site. The task is pretty trivial but overall there are some minor things that I'm trying to code through. We've moved much of the logic over to stored procs and call them with parameterized queries. This works well since there isn't much inject attack possibility on these. Now I have one query left, which allows for an arbitrary number of search parameters, all using AND. Has anyone accomplished coverting something like this to a stored proc in mysql? Logically I could pass in the parameters in as an array of words, or a wordlist to be broken up inside the proc, but I don't want to spend a bunch of time either reinventing the wheel or working to a goal that can't be accomplished. We could build the base query dynamically in the code using standard sql and bind the parameters to it that way but since we've moved everything else to procs I figured I'd look into this as well. BTW, this is a project I brought onto after they found they had a sql injection bug in there code that was exploited... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=b...@meltel.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: Search based where claused and stored proc
Ben- did'nt see your solution? Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. From: b...@wisper-wireless.com To: mysql@lists.mysql.com Subject: RE: Search based where claused and stored proc Date: Fri, 27 Mar 2009 13:43:51 -0500 Ben Wiechman Network Administrator Wisper High Speed Internet Office: 866.394.7737 Direct: 320.256.0184 Cell: 320.247.3224 b...@wisper-wireless.com -Original Message- From: Gary Smith [mailto:g...@primeexalia.com] Sent: Friday, March 27, 2009 12:59 PM To: mysql@lists.mysql.com Subject: [MySQL] Search based where claused and stored proc I'm working on a small project of re-implementing all of the sql for a web site. The task is pretty trivial but overall there are some minor things that I'm trying to code through. We've moved much of the logic over to stored procs and call them with parameterized queries. This works well since there isn't much inject attack possibility on these. Now I have one query left, which allows for an arbitrary number of search parameters, all using AND. Has anyone accomplished coverting something like this to a stored proc in mysql? Logically I could pass in the parameters in as an array of words, or a wordlist to be broken up inside the proc, but I don't want to spend a bunch of time either reinventing the wheel or working to a goal that can't be accomplished. We could build the base query dynamically in the code using standard sql and bind the parameters to it that way but since we've moved everything else to procs I figured I'd look into this as well. BTW, this is a project I brought onto after they found they had a sql injection bug in there code that was exploited... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=b...@meltel.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Express your personality in color! Preview and select themes for Hotmail®. http://www.windowslive-hotmail.com/LearnMore/personalize.aspx?ocid=TXT_MSGTX_WL_HM_express_032009#colortheme
RE: Search based where claused and stored proc
There was an article on forums.mysql.com (save the content, not the link) that takes, as the in parameter, a comma delimited list of values and then breaks them down and inserts them into a temp table. The article uses and innodb table, which I find sort of odd as a memory table would probably be faster. It uses this to join it to the table/columns that the search is being conducted on. This seems to be the most common approach I've found. The problem with this approach is that I don't know how to join a table against a column when you are looking for like data instead of equality. This gets me closer, but not exactly there yet. From: Martin Gainty [mgai...@hotmail.com] Sent: Friday, March 27, 2009 1:58 PM To: b...@wisper-wireless.com; mysql@lists.mysql.com Subject: RE: Search based where claused and stored proc Ben- did'nt see your solution? Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. From: b...@wisper-wireless.com To: mysql@lists.mysql.com Subject: RE: Search based where claused and stored proc Date: Fri, 27 Mar 2009 13:43:51 -0500 Ben Wiechman Network Administrator Wisper High Speed Internet Office: 866.394.7737 Direct: 320.256.0184 Cell: 320.247.3224 b...@wisper-wireless.com -Original Message- From: Gary Smith [mailto:g...@primeexalia.com] Sent: Friday, March 27, 2009 12:59 PM To: mysql@lists.mysql.com Subject: [MySQL] Search based where claused and stored proc I'm working on a small project of re-implementing all of the sql for a web site. The task is pretty trivial but overall there are some minor things that I'm trying to code through. We've moved much of the logic over to stored procs and call them with parameterized queries. This works well since there isn't much inject attack possibility on these. Now I have one query left, which allows for an arbitrary number of search parameters, all using AND. Has anyone accomplished coverting something like this to a stored proc in mysql? Logically I could pass in the parameters in as an array of words, or a wordlist to be broken up inside the proc, but I don't want to spend a bunch of time either reinventing the wheel or working to a goal that can't be accomplished. We could build the base query dynamically in the code using standard sql and bind the parameters to it that way but since we've moved everything else to procs I figured I'd look into this as well. BTW, this is a project I brought onto after they found they had a sql injection bug in there code that was exploited... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=b...@meltel.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Express your personality in color! Preview and select themes for Hotmail®. http://www.windowslive-hotmail.com/LearnMore/personalize.aspx?ocid=TXT_MSGTX_WL_HM_express_032009#colortheme -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Data structure for matching for company data
My esteemed friend, partner and co-author has laid it out perfectly for you. Just follow the instructions table-wise. One thing that may not be obvious from Peter's prescription is that you need to enter a bunch of rows into the industry table first, so that the foreign keys will make sense in the bridge table. But presumably this is not onerous. Over here we commonly use a table called SIC (standard industry codes) that has sub-industries etc. Whenever possible I try to use such standards rather than make up a new one. For example, there is the ISO country codes standard, which provides unique two and three letter codes for every country. Yes, thanks to politics, envy, revenge instinct etc. such country codes from time to time require updating, but I choose to stick with the ISO standards rather than rely on my self-invented one and all attendant hassles whenever a country commits seppukku. You might consider looking into the SIC code scheme before bothering to invent your own. You can download this from a number of sources and in a number of formats. Just Google SIC and you should get there. Hope this helps, Arthur
