Re: Strange Replication Behavior
I believe the master.info and relay-log.info are only on the slaves and are specific to each slave (as each slave could be at a different point in the bin-log.) Thanks, Carl On Jul 25, 2012, at 2:22 PM, Richard Reina wrote: I am trying to setup a new slave server and when I go to the master to copy over master master.info and relay-log.info they seem to be missing? Not in /var/lib/mysql and could not find it with #: find / -name master.info The master server has been setup for years and already has three slaves replicating from it so I cannot understand how this is possible. When I do the query show master status I get mysql_master_log.000123 | 755522343 Anyone have any idea as to what could be wrong? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Looking for consultant
We are actually facing both capacity and availability issues at the same time. Our current primary server is a Dell T410 (single processor, 32 GB memory) with a Dell T310 (single processor, 16GB memory) as backup. Normally, the backup server is running as a slave to the primary server and we manually switch it over when the primary server fails (which it did last Saturday morning at 2:00AM.) The switch over process takes 10-15 minutes although I am reducing that to about five minutes with some scripting (the changeover is a little more complex than you might think because we have a middle piece, also MySQL, that we use to determine where the real data is.) Until six months ago, the time delay was not a problem because the customer processes could tolerate such a delay. However, we now have a couple of water parks using our system at their gate, in their gift shops and in their concessions so we need to now move the changeover time to a short enough period that they really don't notice. Hence, the need I have described as 'high availability'. The T410 is normally reasonably capable of processing our transactions, i.e., the customers are comfortable with the latency. However, we have been on the T310 since last Saturday and it is awful, basically barely able to keep up and producing unacceptable latency. Further, our load will double in the next six months and double again the the following six months. So, my thought was that since we have to deal with the issue change over time which will cause us to restructure the servers, that we should also deal with the capacity issue. I think a couple of Dell T620's will provide the capacity we need (the servers we have spec'ed should be around 8X faster than the T410) but I have no experience evaluating or setting up HA systems (I have worked with MySQL for 12 years and am reasonably comfortable with it and I have read everything I can find about HA options and their implementations.) Hence, my post asking for help (which we are willing to pay for.) The web app is primarily JSP's for the administration side and Flash for the operators and other people doing transactions. The server side code is about 1.25 million lines of code and there are about 750 JSP's. The data is 950 tables with heavy use of foreign key constraints. The container is Tomcat which runs on separate servers (the data servers only run MySQL.) Any ideas or help in any way are always welcome. Thanks, Carl On Jul 18, 2012, at 9:42 AM, Shawn Green wrote: On 7/17/2012 8:22 PM, Carl Kabbe wrote: On Monday, I asked if there were consultants out there who could help set up an NDB high availability system. As I compared our needs to NDB, it became obvious that NDB was not the answer and more obvious that simply adding high availability processes to our existing Innodb system was. So, I am back asking if there are consultants lurking on this list that could help with this project. As has been discussed on this list many times before, there are many ways to measure 'high availability'. Most of them deal with what kind of disaster you want to survive or return to service from. If all you are looking for is additional production capacity then the terms you may want to investigate are 'scale out', 'partitioning', and 'replication'. All high-availability solutions require at least some level of hardware redundancy. Sometimes they require multiple layers in multiple locations. Several of those features of MySQL also help with meeting some high-availability goals. Are you willing to discuss your specific desired availability thresholds in public? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Looking for consultant
On Monday, I asked if there were consultants out there who could help set up an NDB high availability system. As I compared our needs to NDB, it became obvious that NDB was not the answer and more obvious that simply adding high availability processes to our existing Innodb system was. So, I am back asking if there are consultants lurking on this list that could help with this project. Thanks, Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Looking for consultant
We are looking at installing an NDB cluster and are looking for someone to assist us in setting it up. Thanks, Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Converting Microsoft SQL database to MySQL
I do this quite frequently. In our case, we are converting competitors data so the process is to use Navicat (premium) to bring the data from MSSQL to MySQL (in the same fields, etc.) and then use a program to convert it into our format so it will run on our system. The only thing I have had to do is add some indexes manually. Thanks, Carl On Jan 30, 2012, at 2:06 AM, James wrote: Hi All, I am involved in a project to migrate our entire database from Microsoft SQL to MySQL. I would appreciate the help if anyone could share what tools will you recommend of converting SQL database to MySQL. Cheers. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
OFF TOPIC - Looking for Consultant
We are looking for a consultant to set up Master - Master replication between two sites (both in US.) Both sites run MySQL version 5.5 (Innodb) in Slackware Linux. Local traffic at each site is on the low side of moderate and is from a Java based web application. There is a VPN between the sites and the remote site is currently running as a slave. We are looking for a consultant to do this as our staff simply does not have the time. Reply directly to me c...@etrak-plus.com. Thanks, Carl
Replication issue
I am running master - master replication between two locations using MySQL version 5.1.41 on Slackware Linux 13 (64bit). The problem from show slave status is: Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_Counter: 1 Exec_Master_Log_Pos: 552321409 Relay_Log_Space: 165412833 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I have tried telling it to skip that transaction (set global sql_slave_skip_counter = 1) to no avail. From what I have been able to determine from searching the Internet, it appears that the replication is failing replicating blobs ahich are basically jpg's of members. If I understand the problem, it is caused by blob containing a character which is the same character that is used to mark the end of a transaction in the bin log. My questions: 1) Is this a reasonable/correct analysis and 2) how do I work around the issue? Thanks, Carl
Replication, log info
One more bit of information... this is from the error log: 110215 8:19:32 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error 110215 8:19:32 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible r easons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log) , the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network p roblem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594 110215 8:19:32 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQ L thread with SLAVE START. We stopped at log 'mysql-bin.000717' position 552321409 110215 8:19:32 [Note] Slave I/O thread: connected to master 'xxx@mysql3:3306',replication started in log 'm ysql-bin.000717' at position 643066423 110215 8:19:33 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) 110215 8:19:33 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'log even t entry exceeded max_allowed_packet; Increase max_allowed_packet on master', Error_code: 1236 110215 8:19:33 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000717', position 643066423 110216 5:15:20 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000717' at position 552321409, relay log './mysqly_liv-relay.000127' position: 74666916 110216 5:15:20 [ERROR] Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 40173568, event_t ype: 87 110216 5:15:20 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error 110216 5:15:20 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible r easons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log) , the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network p roblem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594 110216 5:15:20 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQ L thread with SLAVE START. We stopped at log 'mysql-bin.000717' position 552321409 110216 5:15:20 [Note] Slave I/O thread: connected to master 'xxx@mysql3:3306',replication started in log 'm ysql-bin.000717' at position 643066423 110216 5:15:20 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) 110216 5:15:20 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'log even t entry exceeded max_allowed_packet; Increase max_allowed_packet on master', Error_code: 1236 110216 5:15:20 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000717', position 643066423 ~ Thanks, Carl
Re: Replication issue
The max_allowed_packet setting is the same on both. I have tried restarting the slave... didn't work. I can bounce the master. Thanks, Carl - Original Message - From: Elizabeth Mattijsen l...@dijkmat.nl To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, February 16, 2011 6:23 AM Subject: Re: Replication issue First make sure that the max_allowed_packet setting is the same on both masters. Make sure that setting is active on the slave in question. Then start replication or bounce the master (not sure which I did to fix this the last time I ran into this). Elizabeth Mattijsen = On Feb 16, 2011, at 12:20 PM, Carl wrote: I am running master - master replication between two locations using MySQL version 5.1.41 on Slackware Linux 13 (64bit). The problem from show slave status is: Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_Counter: 1 Exec_Master_Log_Pos: 552321409 Relay_Log_Space: 165412833 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I have tried telling it to skip that transaction (set global sql_slave_skip_counter = 1) to no avail. From what I have been able to determine from searching the Internet, it appears that the replication is failing replicating blobs ahich are basically jpg's of members. If I understand the problem, it is caused by blob containing a character which is the same character that is used to mark the end of a transaction in the bin log. My questions: 1) Is this a reasonable/correct analysis and 2) how do I work around the issue? Thanks, Carl -- 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 issue
I am not quite certain I understand your suggestion: Forget workarounds to solve replication errors and re-init you replication if you will be sure it is really consistent When you say re-init the replication, are you saying to restart the slave in question from a good copy of the master that I know to be good? Just trying to be really careful. Thanks, Carl - Original Message - From: Reindl Harald h.rei...@thelounge.net To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, February 16, 2011 6:24 AM Subject: Re: Replication issue -- 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 issue
are you saying to restart the slave in question from a good copy of the master that I know to be good? Reindl Harald replied: yes! there is a reason why the salve stops to work and in my opinion the only save way to get a 100% clean slave is clone it again from the stopped master Carl: I was hoping to avoid that because it approximately 24 hours to move the master data to the slave. I know that is the only way to be certain they are sync'd but is there any other way? Thanks, Carl - Original Message - From: Reindl Harald h.rei...@thelounge.net To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, February 16, 2011 6:40 AM Subject: Re: Replication issue -- 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 issue
I was describing how long it takes to do a mysqldump, move the data, load the data in the slave and then restart the slave. I have never used the rsync process... I will try it out in the in the middle of the night when I have time to recover from a screwup. Who says systems people need sleep! Thanks, Carl - Original Message - From: Reindl Harald h.rei...@thelounge.net To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, February 16, 2011 7:02 AM Subject: Re: Replication issue -- 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, log info
It won't hurt anything to change the mac_allowed_packet size so I will increase it (it is set to 80MB, I will double it and see what happens it the future.) Does anyone know if there are issues replicating blobs (I read yesterday that these sometimes cause problems)? I am just trying to see if I am setting myself up for future problems. Thanks, Carl - Original Message - From: Johan De Meersman To: Carl Cc: mysql@lists.mysql.com Sent: Wednesday, February 16, 2011 6:55 AM Subject: Re: Replication, log info On Wed, Feb 16, 2011 at 12:23 PM, Carl c...@etrak-plus.com wrote: 110216 5:15:20 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) This seems to be the major player, here. I would make sure to increase the setting identically on both sides, though. I'm not entirely sure how this would happen - maybe due to making a borderline-sized query deterministic? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Replication issue
Thank you for the information and script. I will try it out tonight when traffic stops. Thanks, Carl - Original Message - From: Reindl Harald h.rei...@thelounge.net To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, February 16, 2011 7:47 AM Subject: Re: Replication issue -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
5.5 versus 5.1
innodb_flush_log_at_trx_commit = 0 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout My config file for the 5.1 install is essentially the same (minor variations.) Does anyone know why the performance would be so different? Thanks, Carl
Re: Master Master Replication ... do a fail over and a week agos data is revealed.
Johan, You state that master - master is not reliable in dual active environments. I am in the process of setting up just such an environment (moderate active on the primary server, lighter activity on the other server.) Do you know where I can get some information on the risks? Thanks, Carl - Original Message - From: Johan De Meersman vegiv...@tuxera.be To: short cutter shortcut...@126.com Cc: Brent Clark brentgclarkl...@gmail.com; mysql@lists.mysql.com Sent: Tuesday, October 19, 2010 5:10 AM Subject: Re: Master Master Replication ... do a fail over and a week agos data is revealed. On Tue, Oct 19, 2010 at 9:48 AM, short cutter shortcut...@126.com wrote: 2010/10/18 Brent Clark brentgclarkl...@gmail.com: Hiya I run MySQL Master - Master Replication. Ive had an interesting situation whereby I failed over using heartbeat but whats is interesting is that via the application (vbulletin), I see that the forums was showing that a weeks ago data. Why using M-M replication? The book of High performance Mysql says it is not a reliable mechanism. There's various reasons why - almost all my setups also use it. It's not a reliable mechanism for dual-active setups, but as a hot standby there's nothing wrong with it whatsoever. Read the book again :-) I don't have a straight explanation about why the secondary master offers data from a week ago, though. If replication is running, maybe there's something going on with the binlogging on the primary ? Check the primary's master status and the secondary's slave status; check what's in the primary's binlogs and in the secondary's relay logs; if need be check the traffic that goes over the replication interface. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Transfer database to MySQL.Huge effort?
Luis, I regularly move data from MSSQL to MySQL using Navicat. Start MSSQL (the lite version is free if you don't have it) and load your MSSQL data. From Navicat, create a database to receive the data and import it using ODBC. Very slick, reasonably quick, complete transfer including creating the tables. Thanks, Carl - Original Message - From: Luis Suzuki luissuz...@live.com To: mysql@lists.mysql.com Sent: Saturday, September 18, 2010 6:59 PM Subject: Transfer database to MySQL.Huge effort? I want to transfer a database(not MySQL) to MySQL(only the tables,not stored procedures,views etc.).Using SQLWays I gotseveral problems(It needs some expertise).Now using SQLMaestro DataWizard I exported some tables to csv format forimporting with mysqlimport,I previously created the database with CREATE DATABASE (database in simpliest form).Now,when I use mysqlimport to import the csv formatted tables it tells that I do not have the table xyz.I thought themysqlimport command could automatically read the imported file and create the table with adequate structure and name according to the read/imported file.The number of tables to import are almost two hundred and previously creating in MySQL almost two hundred tableswith structure compatible to the imported database tables is a huge effort.Are there any easier method to follow?I am a relatively new user of database software. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fixture List generation using MySQL
I have written this in both C and Java. It is very complex as, in real life, you want to balance home and away, sequence the games so that the home or away games are spread throughout the schedule, accomodate partial rounds (10 team league where each team is to play 13 games), accomodate odd numbers of teams (7,9,etc.) and create games for teams with short schedules and a lot more. In addition, this is only the beginning as, once you have a playing schedule, you need to assign the games to space which is much more complicated than creating the schedule. Reporting the games is rather trivial except for situations where games have been moved, teams have dropped out or been forfeited out, etc. Thanks, Carl Gavin - Sorry, didn't mean to send it to you privately... itchy trigger finger. - Original Message - From: Gavin Towey gto...@ffn.com To: Tompkins Neil neil.tompk...@googlemail.com; [MySQL] mysql@lists.mysql.com Sent: Thursday, August 19, 2010 1:50 PM Subject: RE: Fixture List generation using MySQL That's almost a cartesean product; except you just want to eliminate results where a team would be paired up with itself. create table teams ( id serial ); Query OK, 0 rows affected (0.02 sec) insert into teams values (), (), (), (); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 [ff] test select * from teams; ++ | id | ++ | 1 | | 2 | | 3 | | 4 | ++ 4 rows in set (0.00 sec) select * from locations; +--+ | name | +--+ | home | | away | +--+ 2 rows in set (0.00 sec) select * from teams t1 JOIN teams t2; +++ | id | id | +++ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 2 | | 1 | 3 | | 2 | 3 | | 3 | 3 | | 4 | 3 | | 1 | 4 | | 2 | 4 | | 3 | 4 | | 4 | 4 | +++ 16 rows in set (0.00 sec) With no join condition, we every possible combination of t1 paired with t2; however, this leads to the undesireable result that we have combinations like team 4 vs team 4. So you just need to add a condition to prevent those rows from showing up: select * from teams t1 JOIN teams t2 ON t1.id!=t2.id; +++ | id | id | +++ | 2 | 1 | | 3 | 1 | | 4 | 1 | | 1 | 2 | | 3 | 2 | | 4 | 2 | | 1 | 3 | | 2 | 3 | | 4 | 3 | | 1 | 4 | | 2 | 4 | | 3 | 4 | +++ 12 rows in set (0.10 sec) Notice you get both combinations of 2 vs 1 and 1 vs 2, so you could just call whichever team is in the first column as the home team. Regards, Gavin Towey -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, August 19, 2010 10:07 AM To: [MySQL] Subject: Re: Fixture List generation using MySQL I'm looking at a routine / script to create the fixtures like team 1 vs team 2 team 3 vs team 4 team 5 vs team 6 etc On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley peter.braw...@earthlink.net wrote: I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Basically ... select a.id,b.id from tbl a join tbl b on a.idb.id; union select a.id,b.id from tbl a join tbl b on a.idb.id; PB - On 8/19/2010 9:12 AM, Tompkins Neil wrote: Hi, I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Thanks for any input. Regards Neil This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com -- 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
I am setting up master - master replication between two sites that are in separate facilities that are hundreds of miles apart. The operating systems are Slackware 13, the MySql version is 5.1.41 (will probably upgrade to latest.) I have two questions: 1. Is the data visible during transmission? 2. Is there a way to encrypt the data during transmission? Thanks, Carl
Re: Master - master replication
Walter, Don't know how I missed that but it exactly what I needed. Thanks, Carl - Original Message - From: Walter Heck wal...@openquery.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Monday, May 24, 2010 5:49 AM Subject: Re: Master - master replication Hi Carl, On Mon, May 24, 2010 at 13:42, Carl c...@etrak-plus.com wrote: 1. Is the data visible during transmission? Not sure what you mean there? 2. Is there a way to encrypt the data during transmission? MySQL supports SSL encryption of replication. Here's a good starting point: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html cheers, Walter Heck Engineer @ Open Query (http://openquery.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: Master - master replication
Interesting. How is the best way to protect the information while using master - master replication on remote sites? (The data contains the information of children, credit cards and bank accounts.) Thanks, Carl - Original Message - From: John Daisley To: Carl Cc: Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 6:47 AM Subject: Re: Master - master replication also consider that it is much more likely that remote slaves will start falling behind particularly if you throw encryption into the equation. Regards John On 24 May 2010 13:24, Carl c...@etrak-plus.com wrote: Walter, Don't know how I missed that but it exactly what I needed. Thanks, Carl - Original Message - From: Walter Heck wal...@openquery.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Monday, May 24, 2010 5:49 AM Subject: Re: Master - master replication Hi Carl, On Mon, May 24, 2010 at 13:42, Carl c...@etrak-plus.com wrote: 1. Is the data visible during transmission? Not sure what you mean there? 2. Is there a way to encrypt the data during transmission? MySQL supports SSL encryption of replication. Here's a good starting point: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html cheers, Walter Heck Engineer @ Open Query (http://openquery.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Master - master replication
John, I am familiar with the PCI regs and am trying to accomodate them. Our process requires that the card information is available on both servers so it is more a question of how than if. Thanks, Carl - Original Message - From: John Daisley To: Carl Cc: Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 7:20 AM Subject: Re: Master - master replication You need to check pci compliance rules before you go replicating and transmitting credit card data. On 24 May 2010 14:15, Carl c...@etrak-plus.com wrote: Interesting. How is the best way to protect the information while using master - master replication on remote sites? (The data contains the information of children, credit cards and bank accounts.) Thanks, Carl - Original Message - From: John Daisley To: Carl Cc: Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 6:47 AM Subject: Re: Master - master replication also consider that it is much more likely that remote slaves will start falling behind particularly if you throw encryption into the equation. Regards John On 24 May 2010 13:24, Carl c...@etrak-plus.com wrote: Walter, Don't know how I missed that but it exactly what I needed. Thanks, Carl - Original Message - From: Walter Heck wal...@openquery.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Monday, May 24, 2010 5:49 AM Subject: Re: Master - master replication Hi Carl, On Mon, May 24, 2010 at 13:42, Carl c...@etrak-plus.com wrote: 1. Is the data visible during transmission? Not sure what you mean there? 2. Is there a way to encrypt the data during transmission? MySQL supports SSL encryption of replication. Here's a good starting point: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html cheers, Walter Heck Engineer @ Open Query (http://openquery.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Master - master replication
This is both interesting and puzzling. The only way credit card information can be aquired is through SSL communication with the user (user enters credit card information which is used to authorize the transactions, whatever.) Yet, that same process is not sufficient to comply with PCI DSS requirements to move the card information from one server to another. Seems illogical since both transmissions are exposed in the same way. Thanks, Carl - Original Message - From: John Daisley To: Prabhat Kumar Cc: Carl ; Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 7:39 AM Subject: Re: Master - master replication ssl is not enough for pci dss compliance. If you store credit card information and are not pci compliant you can be heavily fined and have your ability to process/accept credit card payments permanently removed. The storage and transmission of credit card details demands end-to-end encryption and tokenization. MySQL replication with ssl is not going to meet the requirements. Probably be easier to write the data to both servers directly rather than writing to one and then trying to secure replication to a level demanded by the pci regs. regards John On 24 May 2010 13:23, Prabhat Kumar aim.prab...@gmail.com wrote: I think setting up few more configuration variable in replication will secure the data in plain text transmission . #--master-ssl #--master-ssl-ca #--master-ssl-capath #--master-ssl-cert #--master-ssl-cipher #--master-ssl-key http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html Thanks, On Mon, May 24, 2010 at 6:45 PM, Carl c...@etrak-plus.com wrote: Interesting. How is the best way to protect the information while using master - master replication on remote sites? (The data contains the information of children, credit cards and bank accounts.) Thanks, Carl - Original Message - From: John Daisley To: Carl Cc: Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 6:47 AM Subject: Re: Master - master replication also consider that it is much more likely that remote slaves will start falling behind particularly if you throw encryption into the equation. Regards John On 24 May 2010 13:24, Carl c...@etrak-plus.com wrote: Walter, Don't know how I missed that but it exactly what I needed. Thanks, Carl - Original Message - From: Walter Heck wal...@openquery.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Monday, May 24, 2010 5:49 AM Subject: Re: Master - master replication Hi Carl, On Mon, May 24, 2010 at 13:42, Carl c...@etrak-plus.com wrote: 1. Is the data visible during transmission? Not sure what you mean there? 2. Is there a way to encrypt the data during transmission? MySQL supports SSL encryption of replication. Here's a good starting point: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html cheers, Walter Heck Engineer @ Open Query (http://openquery.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Master - master replication
Mark and Patrick, The data is encrypted on the servers (wouldn't want it any other way.) So, I believe we would be transmitting encrypted data over a secure line (SSL, SSH, VPN, whatever.) Doesn't sound to me that there is much of a chance a bad person could ever see anything. Can anyone see how the data could be acquired by a bad person? (I understand both servers have to be secured.) Thanks, Carl - Original Message - From: Patrick Sherrill patr...@michael-clarke.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Monday, May 24, 2010 8:09 AM Subject: Re: Master - master replication I beleive the issue is more storage related than anything else. Multiple servers exponentially increased risk of compromise. Carl wrote: This is both interesting and puzzling. The only way credit card information can be aquired is through SSL communication with the user (user enters credit card information which is used to authorize the transactions, whatever.) Yet, that same process is not sufficient to comply with PCI DSS requirements to move the card information from one server to another. Seems illogical since both transmissions are exposed in the same way. Thanks, Carl - Original Message - From: John Daisley To: Prabhat Kumar Cc: Carl ; Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 7:39 AM Subject: Re: Master - master replication ssl is not enough for pci dss compliance. If you store credit card information and are not pci compliant you can be heavily fined and have your ability to process/accept credit card payments permanently removed. The storage and transmission of credit card details demands end-to-end encryption and tokenization. MySQL replication with ssl is not going to meet the requirements. Probably be easier to write the data to both servers directly rather than writing to one and then trying to secure replication to a level demanded by the pci regs. regards John On 24 May 2010 13:23, Prabhat Kumar aim.prab...@gmail.com wrote: I think setting up few more configuration variable in replication will secure the data in plain text transmission . #--master-ssl #--master-ssl-ca #--master-ssl-capath #--master-ssl-cert #--master-ssl-cipher #--master-ssl-key http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html Thanks, On Mon, May 24, 2010 at 6:45 PM, Carl c...@etrak-plus.com wrote: Interesting. How is the best way to protect the information while using master - master replication on remote sites? (The data contains the information of children, credit cards and bank accounts.) Thanks, Carl - Original Message - From: John Daisley To: Carl Cc: Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 6:47 AM Subject: Re: Master - master replication also consider that it is much more likely that remote slaves will start falling behind particularly if you throw encryption into the equation. Regards John On 24 May 2010 13:24, Carl c...@etrak-plus.com wrote: Walter, Don't know how I missed that but it exactly what I needed. Thanks, Carl - Original Message - From: Walter Heck wal...@openquery.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Monday, May 24, 2010 5:49 AM Subject: Re: Master - master replication Hi Carl, On Mon, May 24, 2010 at 13:42, Carl c...@etrak-plus.com wrote: 1. Is the data visible during transmission? Not sure what you mean there? 2. Is there a way to encrypt the data during transmission? MySQL supports SSL encryption of replication. Here's a good starting point: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html cheers, Walter Heck Engineer @ Open Query (http://openquery.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621
Re: 50 things to know before migrating from Oracle to MySQL
I once was selling a system to an organization. I recommended an IBM AIX box for about $30,000. A competitor was charging $30,000 for the software and said it had to run on an AS/400 that would cost in excess of $200,000. I lost the sale because the IBM salesman said, quite candidly, 'I make more commission on the AS/400 so that's the one I am selling.' Oracle is very similar. They are managed to make money. I suspect we will see licensing fees and required support contracts because they can now charge them. And, an Oracle consultant to write a join with 100-200 joins? Oracle will sell it if they can convince the customer. Just some thoughts. - Original Message - From: Tom Worster f...@thefsb.org To: mos mo...@fastmail.fm; mysql@lists.mysql.com Sent: Sunday, January 31, 2010 7:39 PM Subject: Re: 50 things to know before migrating from Oracle to MySQL On 1/29/10 5:03 PM, mos mo...@fastmail.fm wrote: I noticed the article didn't say how much money you'll save by not paying through the nose for Oracle per server licensing, the cost of upgrading your hardware to get some speed out of Oracle, or the cost of having to hire one or more Oracle administrators to manage and tweak the database. how much does an oracle programmer who can maintain your queries with more than 61 joins cost, in, say, usd/hr? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.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: 50 things to know before migrating from Oracle to MySQL
A quick Google turned up http://www.xaprb.com/blog/2009/03/13/50-things-to-know-before-migrating-oracle-to-mysql/ Man, I love Google. Thanks, Carl - Original Message - From: Daevid Vincent dae...@daevid.com To: mysql@lists.mysql.com Cc: 'changuno ' chang...@rediffmail.com Sent: Thursday, January 28, 2010 5:49 PM Subject: RE: 50 things to know before migrating from Oracle to MySQL -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Thursday, January 28, 2010 2:16 PM To: mysql@lists.mysql.com On 1/28/2010 3:21 AM, changuno wrote: Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? would it have been too much to just link to it? Thought the same thing. Not only that, it would have been PREFERRED, so I can BOOKMARK it and SHARE it with my other colleagues. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
errno: 13
Fresh install of 5.1.41 on a brand new (Slackware 13 - 64 bit) machine. Installed from tar. Directory structure is: basedir=/usr/local/mysql and datadir=/storage/mysql/data. I am currently running as root. The permissions on the directories in /storage/mysql/data are 766 (I have double and triple checked this.) I have created the mysql data tables by running mysql_install_db... it seemed to complete without error: r...@mysql3:/usr/local/mysql/scripts# ./mysql_install_db --datadir=/storage/mysql/data --basedir=/usr/local/mysql -uroot Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h 10.10.10.31 password 'new-password' Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe You can test the MySQL daemon with mysql-test-run.pl cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/local/mysql/scripts/mysqlbug script! The latest information about MySQL is available at http://www.mysql.com/ Support MySQL by buying support/licenses from http://shop.mysql.com/ I then ran /usr/local/mysql/bin/mysqld_safe -uroot which produced the following error report: 091211 13:19:18 mysqld_safe Starting mysqld daemon with databases from /storage/mysql/data 091211 13:19:18 [Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line 091211 13:19:18 [Note] Plugin 'FEDERATED' is disabled. 091211 13:19:18 [Warning] /usr/local/mysql/bin/mysqld: ignoring option '--innodb-use-sys-malloc' due to invalid value 'ON' ^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 091211 13:19:18 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins 091211 13:19:19 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name /storage/mysql/data/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 091211 13:19:19 mysqld_safe mysqld from pid file /storage/mysql/data/mysql3.pid ended ~ The relevant portions of the /storage/mysql/data/mysql directory are (for the first error): -rw-rw 1 root root 0 2009-12-11 13:17 plugin.MYD -rw-rw 1 root root 1024 2009-12-11 13:17 plugin.MYI -rw-rw 1 root root 8586 2009-12-11 13:17 plugin.frm - It appears to me that mysqld is looking for the plugin.frm in the /usr/local/mysql/mysql directory which doesn't exist because my data directory is /storage/mysql/data. The second error, InnoDB: File name /storage/mysql/data/ibdata1 InnoDB: File operation call: 'create' is probably the show stopper. The relevant portions of my.cnf are: # The MySQL server [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 20M max_sp_recursion_depth = 100 table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 basedir=/usr/local/mysql datadir=/storage/mysql/data wait_timeout = 10800 max_connections = 600 and # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /storage/mysql/data innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ ignore_builtin_innodb plugin-load=innodb=ha_innodb.so;innodb_trx=ha_innodb.so;innodb_locks=ha_innodb.so;innodb_lock_waits=ha_innodb.so;innodb_cmp=ha_innodb.so;innodb_cmp_reset=ha_ innodb.so;innodb_cmpmem=ha_innodb.so;innodb_cmpmem_reset=ha_innodb.so # Note: ha_innodb.so is in the 'plugins' directory. This error makes no sense to me. Can anyone kick me in the right direction? Thanks, Carl
Thoroughbred data to MySQL
I have a customer that has a five year old system that runs on Thoroughbred (Thoroughbred Software International) data. Has anyone ever been successful converting Thoroughbred data to MySQL? If so, how did you do it? Thanks, Carl
Re: Solved Select query locks tables in Innodb
Just to close this off. Baron was correct in that the core problem was a bug in MySQL (I was using version 5.0.37.) There are some references to this bug in the MySQL bug stuff but they claim to have eliminated it in 5.0.30... apparently not. I ungraded to version 5.1.32 and the original problem disappeared and the selects behave as one would expect. Many thanks to all who offered advice. Carl - Original Message - From: Perrin Harkins per...@elem.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Friday, March 13, 2009 1:40 PM Subject: Re: Select query locks tables in Innodb 2009/3/12 Carl c...@etrak-plus.com: I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. What's the relationship you're implying between memory and locking? Multi-version concurrency doesn't necessarily mean the older versions that are being read from have to be entirely in memory. InnoDB will lock on a query that doesn't use an index. It shouldn't lock on a SELECT query, regardless of the indexes involved. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Upgrading and screwed up
Trying to upgrade from 5.0.37 to 5.1.32, These are the steps I have taken: 1. Took a dump of the our production database using mysqldump. 2. Downloaded the binary version for i86 Linux and placed it on a Slackware 12 server. 3. Foolishly ran mysql_upgrade against the data (/storage/data/mysql... these are all Inodb) thinking I had started mysqld for the new version. However, it was probably the prior version (5.0.37.) 4. Moved the data directory (/storage/data) to .bak (/storage/data.bak). 5. Unpacked the .gz and changed the linked directory to the new installed directory. 6. Ran the install script (scripts/mysql_install_db --user=root). Yes, I know this is out of sequence, no excuses. 7. Realized I could not make my time deadline for the install, so I started reverting to the previous setup... moved the new data directory to data.new, moved the data.bak to data, moved the new link to mysql.new and the old link (now named mysql.old) back to mysql. 8. Started mysqld (really mysqld_safe) under 5.0.37 and it looked good (I could see the data tables, etc.) When I attempted to start our app, the system complained that it a stored procedure did not exist. Oh-oh. At this point, I can edit the stored procedure (there are a couple of hundred that behave the same way) but not save it. I can see that the stored procedure is in the mysql proc table and it is complete (when I bring the stored procedure up in Navicat, it is missing the paramters which indicates to me that Navicat is using something it has stored because I can see the praramters when I look in the table.) To summarize the problem, mysql does not seem to be able to find/see the stored procedures. Anyone have any ideas? TIA, Carl
Re: Select query locks tables in Innodb
Brent, After a delay while I was busy killing alligators, I did as you suggested (added a composite index of date and organization_serial on journal_entry_master... in the spirit of your suggestion, anyway.) The results were interesting: 1. In my test environment, I could not force a locked file even though I opened the dates up to cover 2+ years and changed to an organization that had more records. The 'Explain' is attached as temp1.txt. You will note that it starts with 100,000+ records while the eventual set of records for the report is 60,000 because the 100,000+ number includes some journmal entries for refund/void/etc. transactions which we have no interest in. 2. I tried various combinations of indexes but couldn't seem to get any better than the composite one on the journal_entry_master. I did not check whether the other options would produce locked files. I am now going to put this into production and see if it will actually fly. I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. Thanks for all your help and Baron's suggestions also. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not very unique, on average it will only narrow down the table to 1/687 of it's full size. This is probably the source of your locking problem and where you want to focus. InnoDB will lock on a query that doesn't use an index. It would have to lock every record anyway, so why not lock the table? 36,000 records still may be too large of a result set to do record versioning. But, optimizing your query is the only way to go. Your date_effective is a lot more granular, so you may want to focus on that. If you do a lot of these types of searches, you can try creating a compound index on organization_serial+date_effective. CREATE INDEX (org_date) ON journal_entry_master(organization_serial,date_effective) MySQL would/should then use that query, which will narrow things down quicker and better. It shouldn't have to try to do versioning on 56,000 records while it tries to get the subset of that (36,000). Brent On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote: Brent, The query returns about 36,000 rows. The 56,000 rows from the journal_entry_master table is all the entries for organization 16 (they span more than the dates I have asked for.) SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary index (auto-increment), 687 for the organization_serial (the one I am using), 18 for the organization_shift_start (I tried this before, i.e., starting with the organization_shift, but it quickly got mired down) and 777,000+ for the date_effective. If I understand correctly, you have suggested using the date index. The difficulty is the data contains many organizations and so the date range query returns 163,000+ rows. Also, I would expect scaling a query where I had to programatically cut it up would 1) be difficult and 2) wouldn't really solve the problem but would rather just shorten the time of the locks. I am not suggesting that I might not end up there, only hoping for a better solution. Thanks for all your insight and feel free to suggest away. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Wednesday, March 04, 2009 4:23 PM Subject: Re: Select query locks tables in Innodb Is the result of the query returning 56,000+ rows? How many rows are you expecting to be returned once the query is finished running? Your date range is over a year. You may actually get much better performance (and avoid locking) by running more queries with a narrower date range and linking them through a UNION. It's using the organization index rather than the date index. I don't know your dataset, but typically you want your query to use the date index since that narrows down the data set better. You can run SHOW INDEX FROM journal_entry_master to see the distribution of your data in the index. The cardinality column will indicate the uniqueness of your data. The higher the number, the more unique values. Brent 2009/3/4 Carl c...@etrak-plus.com: Under stress (having transaction entered), the query shows that it is still locking the tables. I rewrote the query and tested it step by step but could not tell whether tyhe partially complete query was locking tables because it ran so fast. However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. Does anyone
Re: Select query locks tables in Innodb
I really appreciate the time you have taken to help me with this problem.
I will be out of the office until around 1:00PM and will try your
suggestions.
I did attach a copy of the query but it may have been stripped somewhere
along the line so I have placed it in line below.
select *
from payment_to_fee_link_budget_account_detail_link, journal_entry_master,
journal_entry_type,
payment_to_fee_link_event, payment_to_fee_link, fees, fees_event,
fees_budget_account_detail_link, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial,
transaction_event, receipt_master, budget_account_detail,
budget_account_detail as ptfl_budget_account_detail, budget_account_master
where journal_entry_master.organization_serial = 16
and journal_entry_master.date_effective = '2008-01-01'
and journal_entry_master.date_effective '2009-03-31'
and journal_entry_type.journal_entry_type_serial =
journal_entry_master.journal_entry_type_serial
and
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial =
journal_entry_master.journal_entry_master_serial
and payment_to_fee_link_budget_account_detail_link.date_effective =
'2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective
'2009-03-31'
and payment_to_fee_link_event.payment_to_fee_link_event_serial =
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and transaction_event.transaction_event_serial =
payment_to_fee_link_event.transaction_event_serial
and fees.fees_serial = payment_to_fee_link.fees_serial
and transactions.transactions_serial = fees.transactions_serial
and person.person_serial = transactions.person_serial
and receipt_master.receipt_serial = transaction_event.receipt_serial
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial =
fees_event.fees_event_serial
and budget_account_detail.budget_account_detail_serial =
fees_budget_account_detail_link.budget_account_detail_serial
and ptfl_budget_account_detail.budget_account_detail_serial =
payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
and budget_account_master.budget_account_serial =
budget_account_detail.budget_account_serial
and budget_account_master.budget_account_type_serial = 5001
TIA,
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STATUS while this is going on. And use mysqladmin debug and
check the error log. Then put those in some pastebin and send us the
link. And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running. There's not a lot I can do
to really help you with this other than assume that you are wrong :)
Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.
5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release. You might be surprised at how
much that changes things.
Baron
On Wed, Mar 4, 2009 at 3:33 PM, Carl c...@etrak-plus.com wrote:
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten
out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message - From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you
Re: Select query locks tables in Innodb
Thanks to all of you.
The key was the 107488 rows. I restructured the query so that it started
with something smaller and it 1) runs faster (I'm guessing the reduced use
of temp space) and 2) did not seem to cause any locking problems (I will
test this under load today.)
I have attached a copy of the query which has been simplified in a couple of
ways (I don't really want every field from every row selected from every
table.) Also, the constants like organization_serial (16) and dates are
variables in the real version.
The explain now shows:
idtable typepossible_keys
keylenref rows
1organization_shiftrefPRIMARY, organizationorganization
4const5
1organization_shift_start ref PRIMARY, organization_shift
organization_shift4 organization_shift_serial295
1journal_entry_masterrefPRIMARY, organization_shift_start
organization_shift_start 5 organization_shift_start_serial 52
Note that it now starts with 5 row, expands to 295 rows, etc. not the
100,000+ from before.
Again, thanks for all your help.
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
wrote:
A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just locking the table.
InnoDB does not do lock escalation a la SQL Server etc. I'd look at
Perrin's suggestions, I think they are likely to be the problem.
More importantly, what is the query? :-)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com
# sales from collections
(select *
from organization_shift, organization_shift_start, transaction_event,payment_to_fee_link_event,payment_to_fee_link, payment_to_fee_link_budget_account_detail_link,fees_budget_account_detail_link, fees_event, budget_account_detail,
payments, budget_account_detail as ptfl_budget_account_detail, budget_account_master, journal_entry_master, journal_entry_type, receipt_master, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial
where organization_shift.organization_serial = 16
and organization_shift_start.organization_shift_serial =
organization_shift.organization_shift_serial
and organization_shift_start.date_effective = '2008-01-01'
and organization_shift_start.date_effective '2009-03-31'
#$P!{organizationShiftStartQuery}
and journal_entry_master.organization_shift_start_serial =
organization_shift_start.organization_shift_start_serial
and receipt_master.receipt_serial =
transaction_event.receipt_serial
and transactions.transactions_serial =
transaction_event.transactions_serial
and transactions.organization_serial = organization_shift.organization_serial
#$P!{itemSerials}
and person.person_serial = transactions.person_serial
and payment_to_fee_link_event.transaction_event_serial =
transaction_event.transaction_event_serial
and
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
= payment_to_fee_link_event.payment_to_fee_link_event_serial
and
payment_to_fee_link_budget_account_detail_link.cash_basis_reporting_flag = 'Y'
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and payments.payments_serial =
payment_to_fee_link.payments_serial
and payment_to_fee_link_budget_account_detail_link.date_effective
= '2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective
'2009-03-31'
and (payments.payment_type_code_serial in (
1,2,3,4,5,8,24,6,7,12,13,23,25 )# 1,2,3,4,5,8,24,6,7,12,13,23,25
or
payment_to_fee_link_budget_account_detail_link.description='Apply available
credit to customer accounts receivable')
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial =
fees_event.fees_event_serial
and
fees_budget_account_detail_link.budget_account_detail_serial
Re: Select query locks tables in Innodb
Under stress (having transaction entered), the query shows that it is still locking the tables. I rewrote the query and tested it step by step but could not tell whether tyhe partially complete query was locking tables because it ran so fast. However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. Does anyone have any ideas? TIA, Carl - Original Message - From: Baron Schwartz ba...@xaprb.com To: Brent Baisley brentt...@gmail.com Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com select * from payment_to_fee_link_budget_account_detail_link, journal_entry_master, journal_entry_type, payment_to_fee_link_event, payment_to_fee_link, fees, fees_event, fees_budget_account_detail_link, person, transactions left join regs on regs.transactions_serial = transactions.transactions_serial, transaction_event, receipt_master, budget_account_detail, budget_account_detail as ptfl_budget_account_detail, budget_account_master where journal_entry_master.organization_serial = 16 and journal_entry_master.date_effective = '2008-01-01' and journal_entry_master.date_effective '2009-03-31' and journal_entry_type.journal_entry_type_serial = journal_entry_master.journal_entry_type_serial and payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial = journal_entry_master.journal_entry_master_serial and payment_to_fee_link_budget_account_detail_link.date_effective = '2008-01-01' and payment_to_fee_link_budget_account_detail_link.date_effective '2009-03-31' and payment_to_fee_link_event.payment_to_fee_link_event_serial = payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial and payment_to_fee_link.payment_to_fee_link_serial = payment_to_fee_link_event.payment_to_fee_link_serial and transaction_event.transaction_event_serial = payment_to_fee_link_event.transaction_event_serial and fees.fees_serial = payment_to_fee_link.fees_serial and transactions.transactions_serial = fees.transactions_serial and person.person_serial = transactions.person_serial and receipt_master.receipt_serial = transaction_event.receipt_serial and fees_event.fees_serial = payment_to_fee_link.fees_serial and ( fees_event.transaction_event_description_serial = 13 or fees_event.transaction_event_description_serial = 2 ) and fees_budget_account_detail_link.fees_event_serial = fees_event.fees_event_serial and budget_account_detail.budget_account_detail_serial = fees_budget_account_detail_link.budget_account_detail_serial and ptfl_budget_account_detail.budget_account_detail_serial = payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial and budget_account_master.budget_account_serial = budget_account_detail.budget_account_serial and budget_account_master.budget_account_type_serial = 5001 temp.XLS Description: MS-Excel spreadsheet 1 SIMPLE journal_entry_masterref PRIMARY,organization,journal_entry_type_serial,date_effective organization 4 const 56926 Using where 1 SIMPLE journal_entry_type eq_ref PRIMARY PRIMARY 4 PRODUCTION.journal_entry_master.journal_entry_type_serial 1 1 SIMPLE payment_to_fee_link_budget_account_detail_link ref journal_entry,budget_account_detail_serial,event,date_effective journal_entry 4 PRODUCTION.journal_entry_master.journal_entry_master_serial 1 Using where 1 SIMPLE ptfl_budget_account_detail eq_ref PRIMARY PRIMARY 4 PRODUCTION.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial 1 1 SIMPLE
Re: Select query locks tables in Innodb
One more note. Perrin asked if I was using any select... for update. The answer is no, neither in the select query that seems to be locking the tables nor in the queries that are processing transactions. Surprisingly, one of the tables that reports being locked is never accessed in the report query. It is a foreign key on one of the files that is used. TIA, Carl - Original Message - From: Baron Schwartz ba...@xaprb.com To: Brent Baisley brentt...@gmail.com Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.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: Select query locks tables in Innodb
I did check that all tables are Innodb. I was using the Navicat Server Monitor because I know that when I see the monitor reporting a status of locked during an attempted query, that user comes to a complete halt until the lock is cleared (usually by the bad query finishing.) I will check the isolation level but I believe it is whatever was set out of the box (five years ago.) Thanks, Carl - Original Message - From: Perrin Harkins per...@elem.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, March 04, 2009 1:49 PM Subject: Re: Select query locks tables in Innodb 2009/3/4 Carl c...@etrak-plus.com: However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. I don't know what that is, but I think you'd better look at something closer to the bone, like SHOW INNODB STATUS. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. EXPLAIN isn't really relevant to table locking. InnoDB tables should never let readers block writers for a simple SELECT. Does anyone have any ideas? Did you check that your tables are InnoDB? Are you running some kind of crazy isolation level? - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.
Baron
On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:
I did check that all tables are Innodb.
I was using the Navicat Server Monitor because I know that when I see the
monitor reporting a status of locked during an attempted query, that user
comes to a complete halt until the lock is cleared (usually by the bad
query
finishing.)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select query locks tables in Innodb
I have been wrestling with this problem for a couple of weeks and have been unable to find a solution. The MySQL version is 5.0.37 and it is running on a Slackware Linux 11 box. The problem: A query that is selecting data for a report locks the files that it accesses forcing users who are attempting to enter transactions to wait until the select query is finished. The query is sizable so I have not included it here (I can if that would be helpful.) Explain shows (abbreviated): id select_typetabletypepossible keys key_len refrows Extra 1SIMPLE transactions ref PRIMARY,person,organization 4const107448 * 1SIMPLE person eq_ref PRIMARY 4person_serial1 1SIMPLE regs ref transaction 4transactions_serial 1 1SIMPLE transaction_event refPRIMARY, transaction, receipt 4transactions_serial1 1SIMPLE receipt_masterref PRIMARY 4receipt_serial1 The 107448 rows are the transactions for the organization I am reporting. The person is linked directly to the transaction. During the select query, the person table is locked thereby stopping updates to any person in the table. I have always thought a select is only a read and would, therefore, not lock any tables. Anyone have any ideas? TIA, Carl
Master-master setup
I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the edits and deletes would work (primary key is autoincrement): (Serial) (Serial) TransactionServer A Server B Add to server A1 Replicated 1 Add to server A2 Add to server B (before record 2 2 is replicated) Replicate to server B ? Replicate to server A? Does replication control the order in which transactions are applied so that somehow the replication from server A to server B is applied before the insert to server B? TIA, Carl
Really bad situation
The setup: Slackware 11 server running MySQL version 5.0.24a. Data tables are InnoDB. Mostly used by a Tomcat server on the same 10.10.10.x network. Firewall and port forwarding are managed by GuardDog and GuideDog. The outside world has no direct access to the server and can not present queries to, everything goes through Tomcat and another simple Java POJO server. Ran great for over a year. Then, two weeks ago, it suddenly had a query (unidentified) that caused it to lock any query that tried to insert anything and the server was running at 100% CPU utilization. Probably 50 users were on the system. We use Navicat. The Server monitor showed some queries from an IP that is internal (192.168.0.106.) Yet, that computer did not have anything that would hit MySQL. I eventually got the whole process back up by rebooting the Tomcat server and the MySQL server. I suspected a long running query so I started monitoring slow queries. Identified one that I thought my be the culprit (it was a query from a report that I was trying to fix.) Today, the scenario repeated. This time the queries were coming from 192.168.0.107. CPU was 100% and all inserts were locked. Navicat showed the query as one of our stored procedures which would normally run in a fraction of a second but there was just a flood of them. I shut down 192.168.0.107 but, surprisingly, the queries continued. I stopped Tomcat (very difficult) and stopped and started MySQL through mysqladmin. Ran OK for about five minutes and the flood of queries started again from 192.168.0.107 (which, of course, was completely powered down.) Thinking there was a stack of queries someplace, I rebooted the MySQL server and once again started Tomcat, etc. Within five minutes more queries appeared from 192.168.0.107 and the CPU went to 100%. After about 10 minutes, they all disappeared and everything returned to normal. The data seems intact, no damage that I can discern. All the transactions seemed to have processed or aborted. I am befuddled: 1. Queries coming from a powered down computer would seem to be either a) stacked up someplace or b) from a hacker/intruder. Rebooting the server should have cleared any stack so it looks like I am left with an intruder (which doesn't sound very good.) 2. If it is an intruder, why wouldn't he/she keep going until the server just died. If anybody has any ideas, I sure would like to hear them. TIA, Carl
Re: MySQL Application Builder
I have been doing a lot of development in Flex over the last six months. While it is a terrific presentation layer, connecting to a data manager (we use MySQL) is time consuming (we use Java based openAMF) and a little tempermental. We use Flex 2 Builder to help with the presentation layer and it is decent. However, in order to do the things that make a presentation smooth and easy to understand, you have to understand how Flex works which is as difficult and obtuse as Java Swing. You can do simple things quickly but to do nice things is quite time consuming. HTH. Carl - Original Message - From: Keith Spiller [EMAIL PROTECTED] To: [MySQL] mysql@lists.mysql.com Sent: Wednesday, April 16, 2008 2:05 AM Subject: MySQL Application Builder Hi, I've been building my PHP/MySQL applications by hand for years. Now I am wondering after seeing a Flex demo if some sort of instant or super easy mysql application builder existings. I want something for rapid development with a graphical user interface. Maybe drag and drop table query creation etc. Something to dramatically reduce the amount of time it takes to build a simple web application that reads from MySQL tables. Any recommendations? Thank you for your help... Keith No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.0/1379 - Release Date: 4/15/2008 6:10 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple DB copies with periodic synchronization
Chris, Interesting problem. We just went through a similar scenario. Our setup is: 1. A central server that has all data for everyone. 2. Remote databases (could be on either a single workstation or a server.) Requirements: 1. All administration (price changes, etc.) is done (web interface) on the central server. 2. Each remote site must be able to process transactions even if communications with central site are lost. 3. Only the data of interest to each remote site is replicated in that site's database. How we implemented: 1. A separate Java application runs on each remote computer that has a copy of a database. Periodically (timer), this application looks for data that has changed since it last checked. It knows the data that has changed because, for those tables that we want replicated, the primary key and table ID are put into a special table using triggers. 2. The changed remote data is sent to the central server (we have a Java application listening on a specific port) where it is stored and the primary key is returned to the remote (that way we always know the serial of the other side.) 3. When the remote has sent all the data it has accumulated, it asks the central server for any changed data (uses same process to determine what constitutes changed data) and that data is sent to the remote. In our case, we may have many remotes that are interested in the same or different data (several organizations may be running on the same central server.) Of course, there are the usual processes to make certain that data gets from one side to the other and that, once the data gets to the other side, it never comes over again (unless it is changed again.) Just our way of doing this (after three false starts.) Thanks, Carl - Original Message - From: Chris Cowen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 20, 2006 10:34 AM Subject: multiple DB copies with periodic synchronization Hi We have a mySQL database which is being used by a restaurant ordering system, in which many of the tables are being used to store menu item information, pricing etc. The restaurant started off as a single outlet, but is now about to open some more new premises. We would like to have ordering systems in the new premises which can use the information from the database on the original machines. For operational reasons, we want to synchronise the tables that hold all the menu information once a day, and then use the local copies throughout the day. Synchronisation will be over a VPN. We would prefer to do it this way, so that it the VPN goes down (e.g. WAN or phone line is out), the restaurant can still operate using the last synchronised copy of the menu. (as opposed to simply sending the SQL commands over the VPN). There will be one master machine will be where the restaurant managers make changes to their menus, which will get picked up in the morning by the remote machines. The master will also be used to store transactions from all the other branches (where it can be backed up). We'd like to synchronise the menu information in the morning, before the restaurant opens. Then after they close, the transaction tables for the days sales to be synchronised back to the master machine. What is the usual approach in this sort of case? Do we: 1) write our own perl or php script to run the sql commands we need to synchronise? There's not a lot of tables. I don't know much about MySQL commands for synchronisation, or even if there are any. 2) use a third party synchronisation tool ? I looked at SQLyog, but we don't need a GUI. 3) is there another way? - for example a built-in mechanism in mysql to allow duplication with regular synchronisation. Sorry if this is a dumb question - but I'm sure this type of scenario must be fairly common, for example when implementing redundant or distributed databases, so I would be very interested in hearing about people experiences and opinions. Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 3/17/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 3/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installing jTDS for WorkBench
http://sourceforge.net/projects/jtds docs say jTDS does not need any special installation. Just drop the jar file into your application's classpath How do I figure out where that is on a win box? I am trying to connect it to MsSql using http://java.com/getjava and http://sourceforge.net/projects/jtds and can't find a look here, put there, that is the classpath. help help... ^C -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
removing ibdata1 if some/all tables are not InnoDB?
Hello, I'm stuck with a rapidly decreasing amount of available disk space and a requirement to keep a lid on the size of our databases. We're using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of transactions keeping short term track of webserver sessions, which we don't need to keep logs of for very long. I have a number of databases, almost all of which are using MyISAM or HEAP, and one database using InnoDB. As such (or at least, as I understand it) we have a ibdata1 file that will grow forever and AFAIK there's no way to stop it growing forever for as long as we have that InnoDB database. Am I correct? I'm no MySQL guru, my parsing of TFM and googling around and finding bug and feature requests for ibdata1 purging suggests that this is the case. If so, if I drop the InnoDB database, stop mysqld, delete (UNIX filesystem) the imdata1 file, restart mysqld and import a (modified to be MyISAM) dumped copy of the InnoDB database, will that work without damaging anything and then not leave me with another infinatly growing imdata1 file? Am I correct in assuming that InnoDB databases are meant for sites where disk space is not ever likely to be an issue, and MyISAM is a more suitable database engine for our much tighter disk space situation? I may have missed a section of the doco that discusses why one would choose an engine over another? Thanks for any advice, Carl -- === Vivitec Pty. Ltd. Suite 6, 51-55 City Rd. Southbank, 3006. Ph. +61 3 8626 5626 Fax +61 3 9682 1000 === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple data GUI editor?
We use MySQL-Front from Star-Tools GmbH (www.mysqlfront.de)... works pretty much like you have asked. Thanks, Car - Original Message - From: Berman, Mikhail [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: D_C [EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:36 PM Subject: RE: simple data GUI editor? Well, Actually MS-Access through ODBC should work for you -Original Message- From: D_C [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:28 PM To: mysql@lists.mysql.com Subject: simple data GUI editor? i was wondering if people can recommend a simple Excel like tool for editing data? MySql control center - seems to have limitations (unicode, not in dev anymore) Query browser - have to type raw sql to show/hide columns... ideally i want something with a few more features than either of these, eg list data in a vertical table rather than just horizontal... lookups to other tables but more oriented to lots of interactive editing of the DB data than DB admin. I guess more like an Access GUI... (puts on flame pants) + ideally not very expensive :-) thanks! -- 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] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.5/18 - Release Date: 6/15/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.5/18 - Release Date: 6/15/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: sleeping processes
Ronny, Oops, no I didn't. Odd that my code (which looks very similar to your code) has no problems but your code does. Has to be something in your code (always work from something that works to something that doesn't.) Is there any way you can subset your code to just a few lines to get it to work and then expand it to what you really want to accomplish? Can you run a debugger on it to make certain what you think is happening is really what is happening? Thanks, Carl - Original Message - From: Ronny Melz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 06, 2005 3:58 AM Subject: Re: Re: sleeping processes Hi Carl, thank you for your reply. did you have a look at my original posting where I included the code? your code (omitting the error routines) is essentially like this: sock=mysql_init(0)) mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0) mysql_select_db(sock,gvDatabase) // possibly looped mysql_real_query(sock, query, strlen(query)) mysql_free_result(tableRes); // eoloop // At the end of the program, I close the socket. 1. I don't open a connection for each query, using the already open socket instead (good for some things, not good for other ones.) where in your code are you closing the connection? I tried both: to embrace the query/free_result into mysql_init/mysql_close commands within the loop as well as doing the mysql_init/mysql_close at the beginning and at the end of the program. (just as you do and as I posted in my first message) 2. You have to free the result set after every select. I free the result set after every select until NULL. hmmm... anyway many thanks, Ronny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sleeping processes
Ronny,
I think this is actually quite simple. All of your statistic/information
says you are not closing the connection... so where in your code are you
closing the connection?
In one of my projects, I open the data manager as:
// open a MySql database
if (!(sock=mysql_init(0)))
{
_lclose(hfile);
MessageBox(hDlg,Couldn't initialize mysql struct,Convert SportsLog
Data,MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}
// mysql_options(sock,MYSQL_READ_DEFAULT_GROUP,connect);
if
(!mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0)
)
{
_lclose(hfile);
sprintf(tstuff,Couldn't connect to engine!\n%s\n,mysql_error(sock));
MessageBox(hDlg,tstuff,Convert SportsLog Data,MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}
if (mysql_select_db(sock,gvDatabase))
{
_lclose(hfile);
sprintf(tstuff,Couldn't select database test: Error: %s\n,
mysql_error(sock));
MessageBox(hDlg,tstuff,Convert SportsLog Data,MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}
Then, I use the following code to select, etc.:
if (mysql_real_query(sock, query, strlen(query))) {
queryError(query);
}
After I pull the information I want out of the result set, I close the
result set with:
mysql_free_result(tableRes);
At the end of the program, I close the socket.
Note two things:
1. I don't open a connection for each query, using the already open socket
instead (good for some things, not good for other ones.) But, for the
appropriate applications, it is very fast.
2. You have to free the result set after every select.
By the way, this code came right out of Googling.
Thanks and good luck,
Carl
- Original Message -
From: Ronny Melz [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, June 05, 2005 4:57 PM
Subject: Re: sleeping processes
Thanks so far for your advice,
Is it possible that your application doesn't close connection properly?
that is exactly what also I think is the problem's cause, but I am unable
to
locate the place where it actually does happen. My code seems
straightforward
and I had looked over it some other more experienced people which were
unable
to find the bug as well... weird
Check with netstat the states of connections between your application
and server.
'$ netstat | grep mysql' dumps a list increasing proportionally to 'mysql
show full processlist' up to the point where max_connections are reached:
then the mysql processlist reports max_connections+1 pids (including the
terminal I use to get the processlist) whereas a '$ netstat | grep mysql |
wc
-l' does never return due to an ever increasing number of open
connections.
Each of them is in state TIME_WAIT.
Do you see some sleeping processes
with ps utility or 'mysqladmin processlist' command?
AFAIK, 'mysqladmin processlist' prints the same as a 'mysql show full
processlist', right? It's max_connections sleeping processes plus the
processlist query.
Your hint to watch out for sleeping processes with ps was interesting.
Actually, I have some 14 processes ('ps lax | grep mysql') without running
my
program but max_connections+14 if it is running. Each of the processes is
sleeping. I still don't have any idea, do you?
Any suggestions appreciated.
Ronny
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Charsets in console
Hi there, Question: I have a database with names that may contain accented characters like é or è. My charset collation are the defaults latin1 latin1_swedish_ci. In my browser all characters come out all right (both in phpMyAdmin and in my application), but when I open a command console (mysql.exe) all characters look messed up.Manually typing accented characters works, but they do not match the ones in the database. E.g. The name Céline is in the database, I see Céline in the browser, but I see C8line in the console. Manually requesting the record of Céline (select * from individuals where name='céline') fails. The funny thing is that the C8line record *is* found when I type the request without accents (select * from individuals where name='celine')? Does anybody know how to make the console show and accept the accented characters? Thanks Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting to InnoDB?
We are running MySQL on Red Hat Enterprise Linux 3, using the Red Hat- supplied RPM file mysql-server-3.23.58-2.3. Our current MySQL configuration has MyISAM as the default database file type. I would like to change this such that InnoDB is the default. My understanding of the documentation says that, after changing the configuration file and restarting the MySQL server, there will be no problems using the existing MyISAM databases. Is that correct? Thanks, Carl G. Riches Software Engineer Department of Mathematics Box 354350 voice: 206-543-5082 or 206-616-3636 University of Washingtonfax: 206-543-0397 Seattle, WA 98195-4350 internet: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Converting to InnoDB?
On Mon, 25 Apr 2005, mathias fatene wrote: Sorry, Alter table toto ENGINE=innodb. You don't must, you can. You can also have differents storage ENGINES in the same mysql database. With innodb, you will earn ROW level locking. Best Regards Mathias FATENE Thanks, Mathias! Carl Carl G. Riches Software Engineer Department of Mathematics Box 354350 voice: 206-543-5082 or 206-616-3636 University of Washingtonfax: 206-543-0397 Seattle, WA 98195-4350 internet: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where is MySQL Workbench? (DBDesigner's successor)
Ok, so I have been loving DbDesigner, but bumpted into a few bug-a-boos. http://www.fabforce.net/forum says We will continue to host the DBD4 download till the release of the MySQL Workbench, its successor application that will be an official MySQL product. Then this project will rest in peace. So I figure I would check it out. MySql.com search gave me nohthing even close, and google gave me http://dev.mysql.com/doc/mysql/en/String_functions.html which says February 15, 2004 ... Originally called DB Designer, MySQL Workbench was developed as a college project by Michael Zinner, who is now among the newest MySQL employees. ... Set for release in May, MySQL Workbench will be available under a GNU GPL; commercial pricing was not determined at press time. That was over a year ago, which is a long time in this arena. So... any DBD fans know whats going on? Carl Karsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
links to MySql tools
What does it take to get something like http://dabodev.com listed on the MySql site? (This not a request to have it listed, just wondering on behalf of the dabo team.) http://solutions.mysql.com/program/faq.html#b2 B3. What is the entry level membership fee in the Partner Program? The entry level fee is only US$595/EUR495 per year and includes a MySQL Network development subscription among other benefits! I can understand that something needs to filter out all the wizbang submissions that arn't upto some standard, so I am not complaining, just wondering. Carl K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select previous group
Seems like there should be a simple solution to my problem but I have been unable to find it. Suppose you have a phone book of names, addresses, etc. You are looking at page 100 and want to now see page 99. (The person data is, of course, dynamic so a specific name is not tagged to a specific page.) How can I select the names for page 99 (in this case) knowing only the first name on page 100 and the number of names I want to display? Thanks, Carl No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auth and ssh tunnel
I am trying to connect to MySql with an SSH tunnel that terminates on the same box MySqld is running on. I would expect that I do L3306:localhost:3306 and grant access to [EMAIL PROTECTED] This doesn't seem to work. I got this to work on a test box: L3306:my.sql.IP:3306 grant access to [EMAIL PROTECTED] but I don't really want to expose the box like that. I realize that a firewall could block any real exposure, but I don't have that kind of authority over the box. Is there a howto for this kind of setup? Carl Karsten http://www.personnelware.com/carl/resume.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subject: ~~//~~ Greetings from sunny Queensland, Australia ~~//~~ ~~//~~ Adv
Hi, My name is Carl Millen, and I am writing you this email from my home here in sunny Queensland, Australia. The reason for this email is that you and I share something in common... At some point in our lives we contemplated or tried to start a home business. Don't worry; I'm not trying to sell you anything. I just want to ask you a simple question. Is it possible that at this time in your life, you are open to taking a look at a winning opportunity? If your answer is Yes, reply to this email saying Send More Info (and let me know your name and what country you're from) and I will send out some information to you right away. If your answer is no please delete this email because I will not be contacting you again. Thank you so much for your time. I hope you will at least take a free look. Regards, Carl Millen Outgoing mail is certified Virus Free: Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/04 : 35 Tansey Street, Beenleigh, Qld 4207, Australia E-Mail sent using the Free Trial Version of WorldMerge, the fastest and easiest way to send personalized e-mail messages. For more information visit http://www.worldmerge.com 491266 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle 2 MySQL updates/replication?
Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Thanks for any ideas, -Carl Edwards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disconnecting from server
exit; ? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 21, 2004 10:12 AM Subject: disconnecting from server Hi, I am using the command ./mysqld_safe --user=mysql to connect to the server but how does one disconnect? Thanks in advance -- 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: C Client compil error
try...
#include windows.h
#include mysql.h
int main(void)
{
MYSQL *database;
database = mysql_init(database);
return(0);
}
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, April 26, 2004 8:25 AM
Subject: C Client compil error
Hello all,
I try to compil a very simple client, but i get multiple error from my
compiler
Windows XP, DevC++, MySql installed : mysql --version = mysql Ver 14.3
Distrib 4.1.1a-alpha, for Win95/Win98 (i32)
The prgram :
#include mysql.h
int main(void)
{
MYSQL *database;
database = mysql_init(database);
return(0);
}
The error from the compiler :
Compilateur: Default compiler
Exécution de gcc.exe...
gcc.exe D:\al\Working_code\connection.c -o
D:\al\Working_code\connection.exe -Wall -pedantic -g3
-IC:\Dev-Cpp\include -Id:\al -ID:\MySql -LC:\Dev-Cpp\lib
-Ld:\MySql
In file included from C:/Dev-Cpp/include/sys/types.h:38,
from D:/MySql/mysql.h:33,
from D:/al/Working_code/connection.c:1:
C:/Dev-Cpp/include/stddef.h:6:2: warning: #include_next is a GCC extension
In file included from D:/MySql/mysql.h:57,
from D:/al/Working_code/connection.c:1:
D:/MySql/mysql_com.h:145: parse error before SOCKET
D:/MySql/mysql_com.h:145: warning: no semicolon at end of struct or union
D:/MySql/mysql_com.h:172: parse error before '}' token
D:/MySql/mysql_com.h:172: warning: type defaults to `int' in declaration
of
`NET'
D:/MySql/mysql_com.h:172: ISO C forbids data definition with no type or
storage class
D:/MySql/mysql_com.h:249: parse error before '*' token
D:/MySql/mysql_com.h:250: parse error before '*' token
D:/MySql/mysql_com.h:251: parse error before '*' token
...
In file included from D:/al/Working_code/connection.c:1:
D:/MySql/mysql.h:113: warning: ISO C89 does not support `long long'
In file included from D:/al/Working_code/connection.c:1:
D:/MySql/mysql.h:215: parse error before NET
D:/MySql/mysql.h:215: warning: no semicolon at end of struct or union
...
And so on.
Could anybody point me where i can search/solve the problem?
Thank's a lot
--
Vincent
--
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]
mySqlDump grant command
I am sure that i used mysqldump to create a script that had both CREATE TABLE and GRANT commands, but now I can't figure out how. Carl K http://www.personnelware.com/carl/resume.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: msvcr70.dll was not found error when double click on desktop icon
hi, msvcr70.dll is part of the Microsoft(r) C Runtime Library v7. You can download this file free of charge from: http://www.dll-files.com/cgi-bin/cgiwrap/dll-files/topdown/download.pl?file=msvcr70.zip== (Copy and paste the above URI into your browsers address bar) Carl - Original Message - From: Joe Audette [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 8:54 AM Subject: Re: msvcr70.dll was not found error when double click on desktop icon d r wrote: I am a personal user who just downloaded the version 4.0 and mysql administrator. The first time I clicked on the Administrator icon on my desktop I get an error that says failed to start because msvcr70.dll was not found. Try to reinstall. I did remove the program and then reinstalled it and got the same error. I just purchased a lern mysql book and need to download the program so I can learn it. I had this same error and i searched my hard drive and found that file and put it in the bin folder where MySqlAdministrator is installed and it fixed it. I found it in a folder related to MS Outlook. Hope that helps, if you don't find it let me know and I'll send you mine. Best Regards, Joe Audette -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL Row Size
Hi, Does anyone know of the fastest way to get the size of a single table row in bytes? Im currently using the following query: (this query takes 7 seconds which is not efficient). SELECT SUM(LENGTH(CONTACT(view_stat_id,account_id,project_id,contact_id,timestamp)) ) as size FROM table1 WHERE account_id='10043'; THIS IS TO DETERMINE HOW MUCH DISK SPACE THE ROW IS TAKING UP! Any help will be appreciated. Thanks --- Carl Fretwell - Web Developer - Doctor Net t. 0870 770 4990 - f. 0870 770 4991 Visit www.doc-net.com - let us be your key to the web Visit www.eMailCampaigner.com - discover the power of permission based marketing --- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data + odbc
The first test i've done was on, lets say, BoxA and it worked just fine. Than, i said,'ok, lets install the client on another WKS' that is BoxB. And from BoxB it's not working. I've got 2 workstations and a server on which i have Mysql 3.23.52 - nt. Lets call the workstations BoxA and BoxB, and the server BoxC. The clients are build in Visual FoxPro and use MyODBC for connecting to server. From both WKS statements like select, insert, update, delete work just fine, but when i want to 'LOAD DATA local INFILE' it works OK only from one WKS. Here's my code open database opreluare CREATE CONNECTION transfer ; DATASOURCE MYSQLSERVER ; USERID incarc PASSWORD incarc ; DATABASE OCUPAT vQuery=LOAD DATA local INFILE 'c:/ocupat/preluare/baza.txt' into table baza FIELDS TERMINATED BY '' r=sqlexec(sqlconnect(transfer),vQuery) and r is -1 after that. h = SqlConnect(transfer) r=sqlexec(h,vQuery) if r0 ? aError( laErrors ) _cliptext = laErrors[1,2] endif Paste the result here. Carl K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems connecting to MySQL with WLS
Hello! I'm using a WLS server and MySQL. Where am I supposed to put the mysql-connector-java-3.0.11-stable-bin.jar to make sure WLS will find it? I've tried several places ie. under ttk and right under classes. Still WLS doesn't find my mysql.jar file. It works when I'm compiling it locally but when I'm trying to deploy it on the server everything goes wrong. My hierarchy looks like this. WEB-INF | classes | web | java | se | ttk | Test.class
SQL-help needed
Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58.
SQL-HELP
Hi! I got a table, champions, looking like this: id class winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. Here's some help for starters: SELECT DISTINCT CASE WHEN c1.winner_1=c1.winner_1 THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1,champions c2 /Carl
SQL-HELP
Hello! I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). /Carl
SV: SQL-HELP
Now UNION is implemented in MySQL 4.0.0. and as I stated earlier I run 3.23.58. -Ursprungligt meddelande- Från: Rodolphe Toots [mailto:[EMAIL PROTECTED] Skickat: den 17 februari 2004 16:19 Till: Jonas Lindén; Carl Schéle; [EMAIL PROTECTED] Ämne: SV: SQL-HELP yeah but that wont really do it since the names are in two columns so, there must also be a UNION included do a union and then select distinct on the result from the union that should do it (eller hur?) -Ursprungligt meddelande- Från: Jonas Lindén [mailto:[EMAIL PROTECTED] Skickat: den 17 februari 2004 16:01 Till: Carl Schéle; IT; Posten; [EMAIL PROTECTED] Ämne: Re: SQL-HELP Hello, you might want to try select DISTINCT ? http://www.mysqlfreaks.com/statements/18.php /Jonas - Original Message - From: Carl Schéle, IT, Posten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 3:49 PM Subject: SQL-HELP Hello! I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). /Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: SQL-HELP
It works fine (with a little tweak). SELECT DISTINCT CASE WHEN c1.winner_1 = c1.winner_2 THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1,champions c2 ORDER BY winner ASC is what I wanted. Thank you very much! Btw, I can't help my webhotel is rotten and only uses old versions. But it's cheap :) /Carl -Ursprungligt meddelande- Från: Jochem van Dieten [mailto:[EMAIL PROTECTED] Skickat: den 17 februari 2004 16:44 Till: Carl Schéle, IT, Posten Kopia: [EMAIL PROTECTED] Ämne: Re: SQL-HELP Carl Schéle, IT, Posten wrote: I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). The smart way: get a database that understands UNION. The other way: SELECT DISTINCT CASE WHEN c1.id = c1.id THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1, champions c2 Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE NOT EXISTS
I use php 4.1.1. mysql 3.23.49 and phpmyadmin 2.5.4 I have two tables classes - class_id (int) - class_name (text) HS HD DD DS MD champions - champions_id (int) - year (text) - class_name (text) - winner (text) 1981 HS PETER 1981 DS ANNE What I want to do is to list all class_name from classes that aren't represented a specific year. So what I want as result is HD, DD, MD I'm trying to use the following query: SELECT class_name FROM classes WHERE NOT EXISTS (SELECT * FROM champions WHERE champions.class_name=classes.class_name AND champions.year='1981') ERRORREPORT: #1064 - 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 'EXISTS ( SELECT * FROM champions WHERE champions.class_name Someday please help me. Am I not using the right version of php? Or is it mysql?
Re: backup
* Rick ([EMAIL PROTECTED]) wrote: i guys!! im newbie, how can i backup a mysql database? what do you recommend me? man mysqldump -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom Claiming that your operating system is the best in the world because more people use it is like saying McDonalds makes the best food in the world. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql parser
I am looking for code that will take an SQL command and break it into it's parts. I have found some attempts, but none that use the code from an actual SQL engine, and big surprise, they don't work 100%. I have a few goals: 1) developer tool: cut/paste the SQL command and get a pretty format display - color, each component on a separate line, sub selects indented, etc. How often have you done this by hand and missed a paren? 2) part of an app: user is given a form with a bunch of textbox's. The form has a 'basic query' and each textbox has properties defining how to augment the basic query. For each input the user gives, augment the query. This would be much easier if I had something to break the basic query up into it's parts. I am guessing that such a thing does exist, so before I go spelunking through the source code, perhaps someone can point me somewhere. It looks like sql_yacc.y is what is used to define the syntax that MySql uses. It has been a while sense I took a compiler class, so forgive my ignorance. is sql_yacc.y used to generate the parser which is then compiled, or is it used at runtime? The goal is to be able to hook into the MySql code without having to copy it. That way as MySql evolves, so will my tool. http://www.personnelware.com/carl/resume.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API first row not being returned from a query
* Santino ([EMAIL PROTECTED]) wrote:
I use :
numRows = mysql_num_rows( Result);
numFields = mysql_num_fields( Result);
for( j=0; j numRows; j++) {
mysql_data_seek( Result, j);
CurrentRow = mysql_fetch_row( Result);
for( k = 0; k numFields; k++)
printf( %s\t, CurrentRow[ k]);
printf( \n);
}
and it works
Try to add
mysql_data_seek( Result, 0);
I'll remember the mysql_data_seek call. However, I did find my problem.
It seems I was calling mysql_fetch_row( Result) once BEFORE returning to
my calling routing to fetch the rows out, thus I was only getting the
last 6 rows. DOH!
Thanks for the help.
--
.''`. Carl B. Constantine
: :' : [EMAIL PROTECTED]
`. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8
`- Debian GNU/Linux -- The power of freedom
Claiming that your operating system is the best in the world because more
people use it is like saying McDonalds makes the best food in the world.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API first row not being returned from a query
I'm writing an application and have the following SQL Query written in
C/GTK+ code:
select customer_id, phone, last_name, first_name,
company, account_code from customers;
OK, I then issue the following C commands:
results = mysql_store_result(conx);
numRows = mysql_num_rows(results);
g_print(There are %d rows returned\n, numRows);
return(results);
The print shows 7 rows returned, which is correct. I then have a while
loop to step through each row like so:
i = 0;
while (db_row = mysql_fetch_row(results))
{
g_print(getting data...\n);
id = db_row[0];
phone = db_row[1];
last = db_row[2];
first = db_row[3];
company = db_row[4];
account = db_row[5];
row =
g_strconcat(db_row[0],,,db_row[1],,,db_row[2],,,db_row[3],,,db_row[4],
,,db_row[5],0L);
g_print(Row %d is: %s\n,i,row);
i++;
}
OK, the problem is, I don't get the very first row, I only get the last
6 rows. Can anyone tell me WHY this is? It doesn't really make sense.
This code supposedly prints rows 1-7 but really only prints 2-7.
Your help is greatly appreciated.
--
.''`. Carl B. Constantine
: :' : [EMAIL PROTECTED]
`. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8
`- Debian GNU/Linux -- The power of freedom
Claiming that your operating system is the best in the world because more
people use it is like saying McDonalds makes the best food in the world.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populating a Table with Data by Inserting a Textfile
Stephen Tiano wrote: What I'd like to do is create a textfile and then import the whole thing into a table. So, naturally, I've a few questions. Do I need to include the column heads as a line in my textfile, or can I simply go straight to the data? No ... you can simply go straight to the data .. The command to use for this is mysqlimport from the command line, or load data infile from a mysql session. Both commands would accept an option to specify the columns (according to the ordering of the columns in the text file) to be loaded. Look up mysql docs on both methods ... Assuming, no column heads are necessary--because they're already there in the table I've previously created--I'm looking at something like this (the double quotes at the start of each line are deliberately empty because the first column is an auto_increment column): No need for the double quotes to denote the auto_increment field, IMHO. You should just exclude the column, and use the parameter that tells mysqlimport or load data infile what columns you are loading ... the system will handle the auto_increment seemlessly as usual. , 'jack', 'sprat', '1 Main St', 'apt 2L', 'Anytown', 'NY', '1', '20030912154545' , 'john', 'doe', '45 Hoover St', 'apt 8B', 'Anytown', 'NY', '1', '20030912154555' , 'jane', 'smyth', '9 Fifth Ave', 'apt 2L', 'Anytown', 'NY', '1', '20030912154615' Now, do I put items in the single quotes, or is that only for when I'm typing stuff directly into the table via a command line situation? Do I separate entries using \t--no quotes, of course--without any spaces, commas, or tabs? And end each line with \r? (Or \n? Or both?) Like so: \t'jack'\t'sprat'\t'1 Main St'\t'apt 2L'\t'Anytown'\t'NY'\t'1'\t'20030912154545'\r , 'john'\t'doe'\t'45 Hoover St'\t'apt 8B'\t'Anytown'\t'NY'\t'1'\t'20030912154555'\r , 'jane'\t'smyth'\t'9 Fifth Ave'\t'apt 2L'\t'Anytown'\t'NY'\t'1'\t'20030912154615'\r The key thing is the field separation character. There is a parameter to tell the system what you have chosen as a field separation character, like this: --fields-separated-by=char ... After using this, u need not worry about embedded spaces within the individual fields -- ie, no need to quote each field. For lines, as long as you have one row corresponding to one line, you need not bother. But there is also an option to tell the system the character(s) that mark the end of each line . Look up the docs for load data infile and mysqlimport ... hth --carl Steve Tiano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
non-root users cannot run mysqlimport
Hi, I need to periodically load a dumpfile from another DB into mysql. Now, since this is a cronjob, and for which I would need to avoid interactivity, I DO NEED to run this as a non-root user. I have setup such a user, and have granted the user all on the destination database. The user also has the important 'file' privilege. But it seems only the user root can load these files. Any ideas on whats going on here and what I may be missing/overlooking ... All posts appreciated ... --carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Loading a flat file
MyWeeklyLeads.com wrote: I have a flat file that I am trying to load to a MySQL database (fixed-length fields). When I try: LOAD DATA '/mypath/flatfile.txt' INTO TABLE mytable It is generating a ton of errors, but the end result is that it's populating only the first field for all records and all subsequent fields are NULL check the field separator .. perhaps, u are using a non-default separator, and have not specified what the field separation character should be. Any suggestions? You could also try using mysqlimport ... eg: mysqlimport -u userid -p pw -r --fields-terminated-by='field-separation-char' database-name Look up the command syntax .. -r above would replace duplicates, and there is a -i to ignore duplicates. If the ordering of the fields within the flatfile does not correspond with the fields in the DB, then you will also need to specify the columnts to be loaded .. The only problem is that you may need to run mysqlimport as root ... have not yet found a way to run it as a non-root user .. yet. hth --carl Tried these too: LOAD DATA '/mypath/flatfile.txt' INTO TABLE mytable lines terminated by '\n\' LOAD DATA '/mypath/flatfile.txt' INTO TABLE mytable lines terminated by '\r\n\' No go :-( Thanks! Rw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 1044 on database restore
I'm trying to move a database from one machine to another. I dumped the database and successfully loaded it on one machine, but I can't do it on another machine. I have the database set up. I created the user to access the database, and gave all privs with grant option for that database. But when I go to run the command to restore the database, I get the following error: $ mysql -u user -p database database.sql Enter password: ERROR 1044 at line 26: Access denied for user: '[EMAIL PROTECTED]' to database 'database' I don't understand. Looking at the sql file, the line is: /*!4 ALTER TABLE accidents DISABLE KEYS */; LOCK TABLES accidents WRITE; UNLOCK TABLES; /*!4 ALTER TABLE accidents ENABLE KEYS */; Why can I do this on one machine but get an error on the other? The users are set up the same. Actually, using mysqlcc, I can't even get the user to have the lock tables priv, even though I'm root granting the priv, it just refuses to take. Yet, the machine it worked on seems to be set up the same way and I didn't have a problem. Can someone point me in the right direction to a solution? -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom Claiming that your operating system is the best in the world because more people use it is like saying McDonalds makes the best food in the world. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1044 on database restore
* Victoria Reznichenko ([EMAIL PROTECTED]) wrote: It means that user doesn't have ALTER privilege. BUT, I've tried granting the user all privs in mysqlcc and it still doesn't work. Looking at my other setup, the users look like they are set up the same. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom Claiming that your operating system is the best in the world because more people use it is like saying McDonalds makes the best food in the world. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database replication
* Jeremy Zawodny ([EMAIL PROTECTED]) wrote: On Tue, Jun 24, 2003 at 09:14:18PM -0700, Carl B. Constantine wrote: I want to be able to take a database running on a primary server and duplicate/mirror it on a secondary server. I want to be able to update the secondary server on a selectable interval (every 15 mins, every hour, etc). What is the best way to accomplish this task? You'd need to start and stop replication on the slave using a bit of custom code. See the the replication related commands in the manual. Will do. thank you. Here is another question, is it possible for some people to work on the master and some on the slave, and then merge the data back and forth so that they are both the same or does it only work one way thus the DB's are out-of-sync? Additionally, where does MySQL write it's transactions? To a transaction log. :-) ha ha. I meant physically. If the power to the box dies and the system crashes, What does MySQL do to recover? What about tables that are not InnoDB? InnoDB tables scan the log for transactions that must be committed or rolled back. MyISAM tables are not transactional. Ok, That confirms what I thought. Thanks. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database replication
I want to be able to take a database running on a primary server and duplicate/mirror it on a secondary server. I want to be able to update the secondary server on a selectable interval (every 15 mins, every hour, etc). What is the best way to accomplish this task? Consider that at least one table is InnoDB and does use transactions. There may be others as I develop the database further. Additionally, where does MySQL write it's transactions? If the power to the box dies and the system crashes, What does MySQL do to recover? What about tables that are not InnoDB? Thanks. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
segfault on startup after kernel change?
I recently changed my kernel (as noted in another message) to one that supports ACPI (for my laptop running RH9) and a couple other patches (I don't know all of them, one may have been the preemptive kernel patch). I've installed MySQL 4.0.12 from RPM's on the myslq.com site. When I start mysql using /etc/init.d/mysql start, I now get a 1024 segmentation fault: # /etc/init.d/mysql start # Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 320: 1024 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 030618 08:00:57 mysqld ended I've checked the mysql log /var/lib/mysql/hostname.err but all it shows is this: 030618 08:00:57 mysqld started 030618 08:00:57 mysqld ended no realy information at all. If I switch back to the stock RH9 kernel, mysql starts just fine. So what in the kernel would cause this sudden segfault on startup? I don't get it. It shouldn't happen. Please answer. I've searched the mysql and mysql-bugs archives at AIMS and no answer. I did a google search on this error and come up with hits from many different lists (PHP, LDAP, and so forth) but nothing that pertains to what I'm seeing. Can someone provide some insight? -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
sudden 1476 segfault starting mysql
I've been using MySQL 4.12 on my RH9 laptop for quite some time just fine. I used the RPM's from the msql website. Today, I went to start mysql and I get the following error: [EMAIL PROTECTED] root]# /etc/init.d/mysql start [EMAIL PROTECTED] root]# Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 320: 1476 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 030616 21:50:26 mysqld ended anyone have ideas on this? I can't think of anything I've done to the system that would suddenly cause this. That said, I did change the kernel in it recently (someone compiled one up that has ACPI in it) and added a line to /etc/sysctl.conf: # turn off exec-shield kernel.exec-shield = 0 But that shouldn't have anything to do with it, should it? -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
Re: mySQL GUIs
* Rodolphe Toots ([EMAIL PROTECTED]) wrote: hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed Try MySQL's own MySQL Control Center (mysqlcc): http://www.mysql.com/downloads/mysqlcc.html You need Qt, but Qt is available for many platforms, *nix, Win32, and Mac OS X too I believe. MySQLcc is a pretty nice product, reminds me a bit of pgAdmin II on Win32 (for the postgresql db). The other requirement is you need MySQL 4 if you want to compile it yourself, but binaries are available from the website that still work with 3.23.x -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
Re: Open-Source/Freeware Tool To Generate Entity Relationship Diagram s From Text File Containing SQL Scripts ?
* Eldrid Rensburg ([EMAIL PROTECTED]) wrote: I have exported tables from MySQL 3.23 to a text file script containing all its 'create table', indexing, etc, statements. Is there an open-source / freeware tool to generate Entity Relationship Diagrams from this script file ? If not what other tools exist ? The problem with this approach is that SQL Tables do NOT translate directly to ER. In some cases yes, but the ER is not necessarily the DB Schema and visa versa. Total relationships are not there in the schema but are in the ER for example. The same with aggregates and so forth. As to doing ER, you can use Dia http://www.lysator.liu.se/~alla/dia/ which will also do UML. Now, it shouldn't be hard to go from your SQL script file to a UML type diagram (ala Absess and Oracle) but I'm not aware of utils that do that either. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
Re: Open-Source/Freeware Tool To Generate ER Diagrams From SQL Sc ripts ? - ImportER (ER Tool: Dezign)
* Eldrid Rensburg ([EMAIL PROTECTED]) wrote: Apparently, the reverse engineering add-on utility, ImportER, for the ER Tool, Dezign, see Free evaluation versions: www.datanamic.com/download/index.html can import MySQL tables directly. Having looked at that, it only runs on Windows. It also looks more like class diagrams (from the screenshots) than true ER, but I haven't looked at it in detail. Just my $0.02 worth. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
RE: Question about SLAVE STOP
I know that the slave will honor transactions but how does that effect the STOP SLAVE command? Based on your note, I'm assuming (Oh, that nasty word!) that if the stop slave is issued in the middle of a transaction it will roll back. I'm also assuming that the pointer for the bin-log will now point to the begining of the transaction that was rolled back. If this is incorrect please let me know. Thanks! Carl -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 07, 2002 8:56 AM To: '[EMAIL PROTECTED]' Cc: Carl McNamee Subject: Re: Question about SLAVE STOP Hello. On Sat 2002-12-07 at 14:26:00 +0100, [EMAIL PROTECTED] wrote: Hello. On Fri 2002-12-06 at 16:13:54 -0600, [EMAIL PROTECTED] wrote: First off, we are using Innodb tables on version 3.23.53a-max. If the command STOP SLAVE is issued in the middle of the following type of transaction: set autocommit = 0; update...; update...; update...; commit; does it roll back the current transaction or wait for it to complete? [...] I decided to dig in the source a bit (v3.23.46). [...] In short, it seems the slave thread effectively runs in auto-commit mode which is not what you want. I found a somewhat old post by Heikki (the InnoDB deveoper) which basically says the same: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:79650 I just happened to see in the changelog, that this is out of date. As of 3.23.52, it wraps BEGIN/COMMIT: D.3.3 Changes in release 3.23.52 (14 Aug 2002) * Wrap BEGIN/COMMIT around transaction in the binary log. This makes replication honour transactions. Sorry about that. Teaches me to not presume that there were no important changes in the last few stable releases. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Question about SLAVE STOP
sql,query First off, we are using Innodb tables on version 3.23.53a-max. If the command STOP SLAVE is issued in the middle of the following type of transaction: set autocommit = 0; update...; update...; update...; commit; does it roll back the current transaction or wait for it to complete? Or does it, heaven forbid, just stop in the middle of the transaction? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question about slave server
sql,query In a replicated environment, are slave servers read only or can a user attach to one and perform updates? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOINing unequal-sized tables (grouped columns)
I've seen this kind of question asked, but have not found any useful answers. So, at the risk of being redundant and repeating what has already been covered: I want to join the groupings of table columns. The tables are potentially different lengths. A toy example (I figure bankers/accountants have been using SQL longer than anyone :-) ): mysql select * from deposits; ++--+-+ | date | acct | damount | ++--+-+ | 2002-09-21 |1 | 100.00 | | 2002-09-21 |2 | 20.00 | | 2002-09-21 |1 | 75.00 | ++--+-+ 3 rows in set (4.73 sec) mysql select * from withdrawals; ++--+-+ | date | acct | wamount | ++--+-+ | 2002-09-21 |2 |5.00 | | 2002-09-21 |1 | 50.00 | ++--+-+ 2 rows in set (0.45 sec) Ideally, I would like to end up with: ++--+-+-+ | date | acct | damount | wamount | ++--+-+-+ | 2002-09-21 |1 | 175.00 | 50.00 | | 2002-09-21 |2 | 20.00 |5.00 | ++--+-+-+ Of course, doing: mysql select w.date,w.acct,sum(damount),sum(wamount) from withdrawals as w left outer join deposits as d on (w.acct = d.acct) group by w.date,w.acct; ++--+--+--+ | date | acct | sum(damount) | sum(wamount) | ++--+--+--+ | 2002-09-21 |1 | 175.00 | 100.00 | | 2002-09-21 |2 |20.00 | 5.00 | ++--+--+--+ 2 rows in set (2.49 sec) is wrong. Doing multiple queries and/or creating a permanent or temporary transaction table works, but I was hoping to get something in one select, without having to transform tables being created from separate sources. I almost thought this would work (mySQL 4.0): mysql select date,acct,sum(damount) from deposits group by date,acct union select date,acct,sum(wamount) from withdrawals group by date,acct; ++--+--+ | date | acct | sum(damount) | ++--+--+ | 2002-09-21 |1 | 175.00 | | 2002-09-21 |2 |20.00 | | 2002-09-21 |1 |50.00 | | 2002-09-21 |2 | 5.00 | ++--+--+ 4 rows in set (0.73 sec) Not quite. Any thoughts? I would think this is a well-known and well-solved problem. My SQL skills are slap-dash at best, learned in fits and starts, so forgive me if I've missed something obvious here. Thanks! Carl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb locking behavior question
sql,query While working with record level locking on innodb tables we noted some behavior that has us a bit befuddled. The problem, in its simple form, is that I have two processes accessing the same table. Process A does the following: begin; select * from table1 for update limit 5; Now process B comes along and does: begin; select * from table1 for update limit 5; The behavior we are seeing, on both 3.23.49a and 3.23.52, is that process B gets blocked and eventually times out if process A doesn't commit or rollback. In other words it appears that process A has the whole table locked. What we were expecting was that process B would skip over the records locked by process A and get the next 5 records. Any thoughts on what's really going on here? Is there a (better?) way to do what we want? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL select rows conditional on same select
Thanks Chuck! The last example you gave looked just right, but when I ran it, it returned an error. A quick look at the mysql manual revealed that UNION SELECTs are on the TO DO list for mysql :( With the examples you provided though, I was able to create the following statement which returns exactly what I wanted. select distinct X.id, X.title from X, Y where X.id=Z or (X.id=Y.assoc and Y.id=Z); when Z = 1 ++---+ | id | title | ++---+ | 1 | one | | 3 | three | ++---+ when Z = 2 ++---+ | id | title | ++---+ | 2 | two | | 3 | three | | 4 | four | ++---+ and when Z = 3 or 4 or 5, it just returns the single row that I would expect. Again, thanks. Carl Carl Franks wrote: Hi, I have 2 tables, X and Y. table X table Y ++---+ ++---+ | id | title | | id | assoc | ++---+ ++---+ | 1 | one | | 1 | 3 | | 2 | two | | 2 | 3 | | 3 | three | | 2 | 4 | | 4 | four | ++---+ | 5 | five | ++---+ I wish to retrieve the X.id and X.title where X.id=Z select X.id, X.title from X where X.id = Z and also the X.id and X.title where (X.id=Y.assoc) when (Y.id=X.id) select X.id, X.title from X, Y where X.id = Y.assoc and X.id = Y.id So... I think you want the OR of those two statements. The obvious way to do this might be: select X.id, X.title from X where X.id = Z union select X.id, X.title from X, Y where X.id = Y.assoc and X.id = Y.id or: select X.id, X.title from X, Y where ( X.id = Z and not exists (select * from X, Y where X.id = Y.assoc and X.id = Y.id) or X.id = Y.assoc and X.id = Y.id) The above won't work in current versions of mysql, and probably can't make very good use of indices. It can be simulated in mysql via: select distinct X.id, X.title from X, Y where ( X.id = Z or X.id = Y.assoc and X.id = Y.id) but I'm trouble trying to express this as a single SQL statement. Could someone help me out with this, or at least give me a clue as to how to think this one through? In case I haven't explained it very well, I'll describe exactly what I'm doing. Z is a product number, and I want to retrieve the (table X) data for product Z and also the (table X) data for any other products associated with it, represented by table Y. The above paragraph seems different than what you said above. To get the data for products associated with product Z, I think you want Y.id = Z and X.id = Y.assoc select X.id, X.title from X where X.id = Z union select X.id, X.title from X, Y where X.id = Y.assoc and Y.id = Z Chuck I know I could do it using 2 select statements, firstly just selecting X.id, X.title, Y.id, Y.assoc where X.id=Z (I'm using perl DBI) and then within the perl program create another SQL statement selecting X.id, X.title WHERE Y.assoc IS NOT NULL in the results from the first select. However, I would like to do this with one SELECT if it's possible, for efficiency. Thanks, Carl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL select rows conditional on same select
Hi, I have 2 tables, X and Y. table X table Y ++---+ ++---+ | id | title | | id | assoc | ++---+ ++---+ | 1 | one | | 1 | 3 | | 2 | two | | 2 | 3 | | 3 | three | | 2 | 4 | | 4 | four | ++---+ | 5 | five | ++---+ I wish to retrieve the X.id and X.title where X.id=Z and also the X.id and X.title where (X.id=Y.assoc) when (Y.id=X.id) but I'm trouble trying to express this as a single SQL statement. Could someone help me out with this, or at least give me a clue as to how to think this one through? In case I haven't explained it very well, I'll describe exactly what I'm doing. Z is a product number, and I want to retrieve the (table X) data for product Z and also the (table X) data for any other products associated with it, represented by table Y. I know I could do it using 2 select statements, firstly just selecting X.id, X.title, Y.id, Y.assoc where X.id=Z (I'm using perl DBI) and then within the perl program create another SQL statement selecting X.id, X.title WHERE Y.assoc IS NOT NULL in the results from the first select. However, I would like to do this with one SELECT if it's possible, for efficiency. Thanks, Carl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Interesting problem with Alter table and foreign keys on 3.23.51
Below is an example of a problem I'm having when issuing an alter table command to create a foreign key in mysql version 3.23.51. I am running the max version and the tables exist in the innodb table space. Thoughts? Comments? Criticism? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 mysql alter table Table2 add constraint foreign key (par_id) references Table1 (id); ERROR 1005: Can't create table './test/#sql-6b2e_f.frm' (errno: 150) mysql show create table Table1\G *** 1. row *** Table: Table1 Create Table: CREATE TABLE `Table1` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=InnoDB 1 row in set (0.00 sec) mysql show create table Table2\G *** 1. row *** Table: Table2 Create Table: CREATE TABLE `Table2` ( `name` char(10) NOT NULL default '', `par_id` int(11) NOT NULL default '0', PRIMARY KEY (`name`) ) TYPE=InnoDB 1 row in set (0.00 sec) mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Location of header files - RPM
Description: Installed MySQL-3.23.49a-1.i386.rpm and the client. Then tried to install PHP which needs path to header files. From what I can determine from your website documentation, the header files should be in an include directory. There is no include directories under mysql. How-To-Repeat: From the php-4.2.1 directory, enter: ./configure --with-mysql=/usr/share/mysql --with-xml --with-apache=/usr/loca l/src/apache_1.3.23/ --enable-track-vars --enable-ftp Fix: Submitter-Id: submitter ID Originator:Tech Support Organization: Hill Country Community MHMR Center MySQL support: [none | licence | email support | extended email support ] Synopsis: Where are header files? Severity: critical Priority: medium Category: mysql Class: support Release: mysql-3.23.49a (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49a Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 19 hours 52 min 29 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries \ per second avg: 0.000 Environment: machine, os, target, libraries (multiple lines) System: Linux geronimo 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLA\ GS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-excep tions -fno-rtti -\ mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jan 29 06:58 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-clie\ nt-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-be rkeley-db --witho\ ut-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socke\ t-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex -- exec-prefix=/usr \ --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedi r=/var/lib/mysql \ --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQ\ L RPM' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-po\ inter -felide-constructors -fno-exceptions -fno-rtti -mpent ium' CXX=gcc Carl Carpenter IT Manager Hill Country Community MHMR Center --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.363 / Virus Database: 201 - Release Date: 5/21/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
server restart updating NULL values in fulltext indexed table
Description: When performing an UPDATE on a field which is fulltext indexed and currently contains the NULL value, the MySQL server restarts and the update fails. How-To-Repeat: SQL code to reproduce problem: create table test (id int, data text, fulltext(data)); insert into test (id) values (1); update test set data='Some Data' where id=1; ERROR 2013: Lost connection to MySQL server during query Fix: Work-around: set all fulltext-indexed fields to NOT NULL, as the problem does not seem to affect text fields with the empty string, only text fields with the NULL value. Submitter-Id: submitter ID Originator:Carl J Meyer Organization: Mennonite.net MySQL support: none Synopsis: server restarts when updating NULL-valued fulltext-indexed fields Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.1-alpha (Official MySQL RPM) Environment: System: Linux mennonet1 2.4.17 #2 SMP Tue Jan 29 12:37:22 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 10 17:12 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x2 root root 1285788 Apr 2 11:58 /lib/libc-2.2.4.so -rw-r--r--1 root root 27332668 Apr 2 11:42 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 2 11:42 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server --enable-thread-safe-client '--with-comment=Official MySQL RPM' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
double-quotes do not perform as documented in fulltext boolean mode searches
Description:
According to the documentation at
http://www.mysql.com/doc/F/u/Fulltext_Search.html, placing double-quotes
around a phrase within the AGAINST clause of a full-text boolean mode
search should match only rows which contain this phrase exactly as
typed. Instead, it appears that the server returns all rows
containing those words in any order, as if the double-quotes
were not present.
How-To-Repeat:
create table test (id int, data text not null, fulltext(data));
insert into test (data) values ('This is a phrase to search for');
insert into test (data) values ('We will search for this phrase');
insert into test (data) values ('Another bit of text');
insert into test (data) values ('Yet more text');
insert into test (data) values ('etc etc');
select data, match(data) against('phrase to search for' in boolean mode)
as rank from test where match(data) against('phrase to search for'
in boolean mode);
This search should only return the row with data 'This is a phrase to
search for', or should at least give that row higher relevance -
instead, the first two rows are given identical relevance, exactly as
if the double-quotes had been omitted.
Fix:
Haven't found a way to duplicate the missing functionality.
Submitter-Id: submitter ID
Originator:Carl J Meyer
Organization:
Mennonite.net
MySQL support: none
Synopsis: fulltext boolean mode search, double-quotes do not work as documented
Severity: non-critical
Priority: low
Category: mysql
Class: sw-bug | doc-bug
Release: mysql-4.0.1-alpha (Official MySQL RPM)
Server: /usr/bin/mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.1-alpha
Protocol version10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 4 min 53 sec
Threads: 2 Questions: 5 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1
Queries per second avg: 0.017
Environment:
System: Linux mennonet1 2.4.17 #2 SMP Tue Jan 29 12:37:22 EST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc'
CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Apr 10 17:12 /lib/libc.so.6 - libc-2.2.4.so
-rwxr-xr-x2 root root 1285788 Apr 2 11:58 /lib/libc-2.2.4.so
-rw-r--r--1 root root 27332668 Apr 2 11:42 /usr/lib/libc.a
-rw-r--r--1 root root 178 Apr 2 11:42 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --without-berkeley-db --with-innodb
--enable-assembler --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server
--enable-thread-safe-client '--with-comment=Official MySQL RPM'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
boolean-mode fulltext search: problems with +searchterm*
Description:
It appears that MySQL can have trouble with a searchterm
in a boolean-mode fulltext index search which is both prefixed
with + and suffixed with *, when that searchterm is paired
with another required term. The empty set is returned when there are rows
which should match.
How-To-Repeat:
I have not been able to duplicate this bug in a test case, but it
is clearly occurring in my actual table, as the following demonstrates:
mysql SELECT article_id as id, title FROM search WHERE
MATCH(title) AGAINST ('+Anabaptists +Reist' IN BOOLEAN MODE);
+--+-+
| id | title |
+--+-+
| 2538 | Hans Reist House and the Vale of Anabaptists. |
+--+-+
1 row in set (0.01 sec)
mysql SELECT article_id as id, title FROM search WHERE
MATCH(title) AGAINST ('+Anabapt* +Reist' IN BOOLEAN MODE);
Empty set (0.01 sec)
But this is odd - if I replace Reist
with Hans, I now get a different row entirely:
mysql SELECT article_id as id, title FROM search WHERE
MATCH(title) AGAINST ('+Anabapt* +Hans' IN BOOLEAN MODE);
+-++
| id | title |
+-++
| 462 | Hans Landis of Zurich (d. 1614): the last Swiss Anabaptist martyr. |
+-++
1 row in set (0.01 sec)
The only possible key that I've come up with is that Anabaptist or
Anabaptists is a fairly common word in this field - appears in 296 out
of 3249 rows - but not nearly half or anything like that.
Unfortunately, I have not been able to find another word that causes
this bug.
If it matters, I have dropped and recreated the table several times. I
have tried creating the fulltext index both before and after inserting the
data.
Fix:
As I haven't been able to isolate the exact nature of the bug, I
don't know of a fix or workaround.
Submitter-Id: submitter ID
Originator:Carl J Meyer
Organization:
Mennonite.net
MySQL support: none
Synopsis: boolean-mode fulltext search: problems with +searchterm*
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-4.0.1-alpha (Official MySQL RPM)
Environment:
System: Linux mennonet1 2.4.17 #2 SMP Tue Jan 29 12:37:22 EST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc'
CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Apr 10 17:12 /lib/libc.so.6 - libc-2.2.4.so
-rwxr-xr-x2 root root 1285788 Apr 2 11:58 /lib/libc-2.2.4.so
-rw-r--r--1 root root 27332668 Apr 2 11:42 /usr/lib/libc.a
-rw-r--r--1 root root 178 Apr 2 11:42 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --without-berkeley-db --with-innodb
--enable-assembler --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server
--enable-thread-safe-client '--with-comment=Official MySQL RPM'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Performance issues between two servers
Backgroud: We have a process that runs on a server (APPDEV1) that writes records to a mysql server (SQLDEV0). We attempted to move the databases from SQLDEV0 to SQLDEV1 and have run into some sort of performance bottleneck. The server SQLDEV0 is a Compac server with ~2GB of ram and two processors. The server SQLDEV1 is also a Compac server. It has 4GB of ram and two processors that are a bit faster than the ones in SQLDEV0. One big difference between SQLDEV0 and SQLDEV1 is the version of RedHat. SQLDEV0 is running RedHat 7.2. SQLDEV1 is running an enterprise version of RedHat 7.2 so that it can take advantage of the 4GB of ram. All the table spaces are using Innodb. Problem: The process on APPDEV1 can write records to the box SQLDEV0 about eight time faster than to SQLDEV1. We've looked over the my.sql configurations and they seem to be ok. In fact we adjusted the my.cnf file on SQLDEV1 so that it was identicle to SQLDEV0 but it did not help. The systems are running ~70-95% cpu idle so cpu is not a bottle neck. In testing, raw disk I/O rates are about 50% faster on SQLDEV1 as SQLDEV0. We don't see a bottle neck on I/O. This is the only process using mysql on SQLDEV1. On SQLDEV0 it shares access with several other programs but the box is not very busy. Thoughts? Comments? Criticism? Carl McNamee Systems Administrator/DBA Billing Concepts (210) 949-7282 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Feature? (Not null and timestamps)
sql,query One of our developers pointed out something to me today and I can't explain. When we created a table with timestamp as one of the column types and not null the describe table command shows that nulls are allowed for the timestamp column. However, a show create table command shows the not null for the timestamp column. Which is correct? How do I make the column not null? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Timestamp and Load Data command
We are attempting to put records into a table using the load data or mysqlimport commands. One quirk is with columns that include a timestamp type. When we import the records we get zeros in the timestamp column. How can we get the current time inserted when using the load data or mysqlimport commands? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
