Re: NOW() is stuck...
Timestamp is a session variable, so it must have been set to something other than 0 (1372228034 epoch is the date you're showing) in your current session. mysql> set timestamp = 1372228034; Query OK, 0 rows affected (0.00 sec) mysql> select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-26 02:27:14 | 2013-06-27 13:20:48 | +-+-+ 1 row in set (0.00 sec) mysql> set timestamp = 0; Query OK, 0 rows affected (0.00 sec) mysql> select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 | +-+-+ 1 row in set (0.00 sec) Cliff's notes: set timestamp = 0; On 6/26/13 6:10 PM, "Andy Wallace" wrote: >We've been having some issues with one of our MySQL servers lately, and >currently >the dang thing is "stuck". For at least the last hour, NOW() is returning >the same >value: > >mysql> select now(); >+-+ >| now() | >+-+ >| 2013-06-26 02:27:14 | >+-+ > >The system variable "timestamp" also has that same time value stored in >it. How >can we kick this loose so that the values are more current with real >time? (it is >currently 3:08PM here, despite our MySQL instance thinking it's 2am. The >system >time on the machine is correct: > >$ date >Wed Jun 26 15:08:56 PDT 2013 > > >This is MySQL 5.1.46 running on solaris2.10. > >Any ideas short of restarting the MySQL engine? I'm willing to do that, >but would much >rather wait and not do it in the middle of the day. > >Thanks, >Andy > > >-- >Andy Wallace >iHOUSEweb, Inc. >awall...@ihouseweb.com >(866) 645-7700 ext 219 >-- >"Sometimes it pays to stay in bed on Monday, rather than spending the >rest of the week debugging Monday's code." >- Christopher Thompson > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql > Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NOW() is stuck...
It persists across sessions? Does this return anything: show global variables like 'timestamp'; Hopefully it returns: Empty set (0.00 sec) I vaguely remember reading about a bug in 5.1.4x with something to do with a global timestamp. I thought it only showed one though, and that you couldn't set it. If the above returned a timestamp and not an empty set, try: set global timestamp = 0; That should return something like this: ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL But if it returns: Query OK, 0 rows affected (0.00 sec) And then your queries return correct timestamps, you've found a bug. I'd hope that it would fail, but the only thing I can think of is if it's being set as a global variable. If this does fix your problem, and if you're using replication, you may have an issue with your replicated data. Replication uses timestamp extensively. On 6/27/13 1:44 PM, "Andy Wallace" wrote: >But the question is how. I have nothing in the code that does it, or this >would have been true for months instead of just the last 24 hours. In >addition, this is currently set globally - no matter what connection to >the database, it all comes up with this value. Which means that all my >time-based queries no longer work correctly. > >Does your message suggest that setting it to 0 might clear the problem? > > > >On 6/27/13 10:31 AM, Stillman, Benjamin wrote: >> Timestamp is a session variable, so it must have been set to something >> other than 0 (1372228034 epoch is the date you're showing) in your >>current >> session. >> >> >> mysql> set timestamp = 1372228034; >> Query OK, 0 rows affected (0.00 sec) >> >> >> mysql> select now(), sysdate(); >> +-+-+ >> | now() | sysdate() | >> +-+-+ >> | 2013-06-26 02:27:14 | 2013-06-27 13:20:48 | >> +-+-+ >> 1 row in set (0.00 sec) >> >> >> mysql> set timestamp = 0; >> Query OK, 0 rows affected (0.00 sec) >> >> >> mysql> select now(), sysdate(); >> +-+-+ >> | now() | sysdate() | >> +-+-+ >> | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 | >> +-+-+ >> 1 row in set (0.00 sec) >> >> >> >> Cliff's notes: set timestamp = 0; >> >> >> >> >> >> >> >> >> On 6/26/13 6:10 PM, "Andy Wallace" wrote: >> >>> We've been having some issues with one of our MySQL servers lately, and >>> currently >>> the dang thing is "stuck". For at least the last hour, NOW() is >>>returning >>> the same >>> value: >>> >>> mysql> select now(); >>> +-+ >>> | now() | >>> +-+ >>> | 2013-06-26 02:27:14 | >>> +-+ >>> >>> The system variable "timestamp" also has that same time value stored in >>> it. How >>> can we kick this loose so that the values are more current with real >>> time? (it is >>> currently 3:08PM here, despite our MySQL instance thinking it's 2am. >>>The >>> system >>> time on the machine is correct: >>> >>> $ date >>> Wed Jun 26 15:08:56 PDT 2013 >>> >>> >>> This is MySQL 5.1.46 running on solaris2.10. >>> >>> Any ideas short of restarting the MySQL engine? I'm willing to do that, >>> but would much >>> rather wait and not do it in the middle of the day. >>> >>> Thanks, >>> Andy >>> >>> >>> -- >>> Andy Wallace >>> iHOUSEweb, Inc. >>> awall...@ihouseweb.com >>> (866) 645-7700 ext 219 >>> -- >>> "Sometimes it pays to stay in bed on Monday, rather than spending the >>> rest of the week debugging Monday's code." >>> - Christopher Thompson >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe:http://lists.mysql.com/mysql >>> >> >> >> >> >> Notice: This communication may contain privileged and/or confidential >>information. If you are not the intended recipient,
Re: Cannot connect to MySql Database
On 4/9/13 1:54 PM, "Joe Kosinski" wrote: >Hi, > >I am trying to connect to a MySql database and I keep getting the >following: > > Cannot find (ping) database host Joseph-Kosinskis-MacBook on the network > Failed to connect to Unix socket '/var/run/lirc/lircd' > No such file or directory (2) > >The database is located on a MacBook running OS X 10.8. The MySql Server >is running. > >Joe >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql > The hostname can't be found. Try localhost instead of Joseph-Kosinskis-MacBook. As for the next error, that's not a database error, that's a MythTV error. lircd is the daemon for remotes (Linux Infrared Remote Control Daemon). Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Please Help. selectcol_arrayref problem
Nope. That's just granting replication privileges so it can read updates
on all tables on all databases. It cannot select anything.
Why are you trying to connect with a replication slave user?
On 4/2/13 1:47 PM, "Richard Reina" wrote:
>I did a "GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23'
>IDENTIFIED
>BY 'psswd';
>
>on the master. Doesn't *.* mean everything? Why would it just show me to
>databases?
>
>
>
>
>
>
>2013/4/2 Larry Martell
>
>> On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina
>> wrote:
>> > use DBI;
>> > my $dbh = DBI->connect( "DBI:mysql:rushload;192.168.0.1", $usrr,
>>$passw,
>> {
>> > RaiseError => 3 } );
>> > my $dbs = $dbh->selectcol_arrayref("show databases");
>> >
>> > #my $dsn = "dbi:mysql:information_schema:192.168.0.1:3306";
>> > #my $dbh = DBI->connect($dsn, $usrr, $passw);
>> >
>> > my $dbs = $dbh->selectcol_arrayref('show databases');
>> >
>> > print "@$dbs\n";
>> >
>> > When I query the server for a list of databases with the code above it
>> > returns the name of just two and there are over 10.
>> >
>> > Any ideas?
>>
>> Permissions - the user you're logging in as probably only has
>> permission to see the 2 that are being returned.
>>
Notice: This communication may contain privileged and/or confidential
information. If you are not the intended recipient, please notify the sender by
email, and immediately delete the message and any attachments without copying
or disclosing them. LBI may, for any reason, intercept, access, use, and
disclose any information that is communicated by or through, or which is stored
on, its networks, applications, services, and devices.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: MySQL Error#: 2002
Assuming you copied and pasted the error, it looks like the host made a typo in the config file: '/var/lib/myswl/mysql.sock' Should probably be mysql, not myswl. -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, March 18, 2013 3:32 PM To: Patrice Olivier-Wilson; mysql@lists.mysql.com Subject: RE: MySQL Error#: 2002 Check directory permissions, and check out the 'answers' in here: http://forums.mysql.com/read.php?10,284776,284936 > -Original Message- > From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] > Sent: Monday, March 18, 2013 12:05 PM > To: mysql@lists.mysql.com > Subject: MySQL Error#: 2002 > > I have about 60 websites based on mysql and php. Suddenly they have > all gone blank, just white pages. The files are still on the server > and I can see the tables in all the databases via myphpadmin > interfact. I'm not getting any response from hosting gods yet. When I > try to connect to server via Dreamweaver, the error message is: MySQL > Error#: 2002 Can't connect to local MySQL server through socket > '/var/lib/myswl/mysql.sock'(111). > > I have one site that uses a different IP number that that site is ok. > My static sites, ie, no database inclusion, are ok. > > Any ideas what to look for, most appreciated. > > Regards, > > Patrice Olivier-Wilson > 828-628-0500 > http://Biz-comm.com > b...@biz-comm.com > > > "Everything will be alright in the end, so if it is not alright, it is > not yet the end." - Quote from movie: The Best Exotic Marigold Hotel > > > -- > 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 Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: mysql Ver 14.12 Distrib 5.0.27 user privileges question
OP's first question: " I am not able to create a table on my own. what privileges I need to create and modify tables in this database?" The answer to that question is that he/she needs CREATE to create tables and ALTER to alter them. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 27, 2013 12:55 PM To: mysql@lists.mysql.com Subject: Re: mysql Ver 14.12 Distrib 5.0.27 user privileges question says who? you MAY need CREATE privileges but it not uncommon have a defined scheme and not allow the user to create or drop tables, the user below is able to do anything for a common web-app to anser the OP's question % in mysql is the same as * for the bash so yes, % means "any host" Am 27.02.2013 18:38, schrieb Prabhat Kumar: > you need CREATE Privileges. > http://dev.mysql.com/doc/refman/5.0/en/grant.html#grant-privileges > > On Wed, Feb 27, 2013 at 10:42 PM, Rajeev Prasad wrote: >> currently on this version of MySQL a database has been built for me >> to use. and following privileges are given: I am not able to create a >> table on my own. what privileges I need to create and modify tables in this >> database? >> >> mysql Ver 14.12 Distrib 5.0.27, for sun-solaris2.9 (sparc) using >> EditLine wrapper >> >> mysql> show grants; >> >> +---+ >> | Grants for myuserid@% >> | >> >> +---+ >> | GRANT USAGE ON *.* TO 'myuserid'@'%' IDENTIFIED BY PASSWORD >> '*4EF5..6' | >> | GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuserid'@'%' >> +---+ >> 2 rows in set (0.00 sec) >> >> at % means I can do the operations from other hosts too? using ssh Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: data loss due to misconfiguration
This might help with that task: mysqlbinlog binary_log_file_name.01 --start-datetime="2013-02-25 16:45:00" --stop-datetime="2013-02-25 16:50:00" | grep 'DELETE\|ALTER TABLE\|PARTITION' -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, February 26, 2013 5:11 PM To: mysql@lists.mysql.com Subject: Re: data loss due to misconfiguration Hello Zachary, On 2/26/2013 4:42 PM, Zachary Stern wrote: > Any idea what can cause this? Can it be misconfiguration? Could it be > because I'm messing with MySQL's memory usage so much, or the thread > settings? > > My config is linked at the bottom, for reference. I might be doing > something terribly dumb. > > The stuff I've done under "# * Fine Tuning" worries me the most, but I'm > just not sure how I might be causing this. > > I've seen things like this on other servers before but never been able to > nail down the issue. So my config options being the common denominator here > - I suspect that it's something I'm doing. > > However - if there were missing commits, the data is gone as you said - so > wouldn't I never see it to begin with? > > https://gist.github.com/zacharyalexstern/5042483 > If binary logging is enabled, review your logs to ensure that the changes you think are going into your database, really are. If they are, look for anything that may remove data from a table like DELETE commands or ALTER TABLE ... PARTITION ... commands (dropping or resizing your partitions) If you haven't already, verify in your error log that your MySQL isn't being randomly killed by your OS for exhausting its RAM. If those are both a bust, enable the General Query Log and see if any other weird commands you don't recognize are being sent to your database. http://dev.mysql.com/doc/refman/5.6/en/server-logs.html -- 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 Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: data loss due to misconfiguration
Are you actually querying the table (select count(*) from table_name), or just the stats (show table status)? Is the table Innodb? If you're using Innodb and aren't doing a select count (or other select query) on the table, then yes you'll have varying results. This is because unlike MyISAM, Innodb does not keep a count of the records. Using show table status gives just an estimation. This would be my first path of investigation. http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html "The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count." -Original Message- From: Zachary Stern [mailto:z...@enternewmedia.com] Sent: Tuesday, February 26, 2013 12:42 PM To: mysql@lists.mysql.com Subject: data loss due to misconfiguration Is such a thing possible? There are no errors or issues, but we can query a table, get X number of rows, query it later, and all of the sudden be missing a thousand rows. I know this isn't much to go on, but I'm not even sure what information to provide. Will be happy to give anything you guys might be able to think of. TIA. -Zachary Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: log sequence number InnoDB: is in the future!?
I definitely agree with using replication. As for delayed replication, this is actually a built in feature of MySQL 5.6 (coming soon). 5.6 has numerous improvements to replication. Definitely worth checking out: http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html Scroll down to Replication Improvements. Lastly, I've heard good things about Percona's Data Recovery Tool for InnoDB: https://launchpad.net/percona-data-recovery-tool-for-innodb. It might be worth a try. -Original Message- From: Manuel Arostegui [mailto:man...@tuenti.com] Sent: Monday, February 04, 2013 4:35 AM To: Larry Martell Cc: wha...@bfs.de; mysql Subject: Re: log sequence number InnoDB: is in the future!? 2013/2/3 Larry Martell > > > We also ended up dropping the database and restoring from dumps. > However all recent dumps ended up having a similar corruption and we > were still getting the same errors. We had to go back to an October > dump before it would come up cleanly. And our db is fairly large, and > it takes around 4 hours to load a dump. We were working on this Friday > from 8:30 AM until 4AM Saturday before we got it going. And now we're > trying to recall all the alters we did since then, and reload all the > data since then, most of which is in files we can import. The only > thing we don't have is the manual updates done. All in all a total > disaster and something that will make us rethink our procedures. > Perhaps we'll look at replication, although I don't know if that would > have helped in this case. Hi Larry, I am sorry to read this. I hope you guys recovered everything already. I would like to suggest something though. >From my point of view it is always good to backup just schemas (without data) aside from regular data backups, that's to say, combine both. If something like this happens, you can always do a diff and get the schemas recovered in a matter of minutes. Generally, schemas are pretty light and they won't use any significant disk space. About the replication solutionI would strongly recommend to use it if possible in your scenario. Clearly it won't prevent any data-loss generated by a bad statement (UPDATE without where, DELETE * from etc). Albeit, if you're thinking to have a dedicated slave for backups you might want to use pt-delay-slave ( http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html) so you can have your slave delayed XX minutes/hours and you can prevent disasters coming from bad statements such as the ones I described earlier. Anyways, as I was saying, if it's possible to have a server just acting as a slave as a backup, that would help you to recover faster in corruption due to HW problems. It would be a matter of setting it up as a master, which generally takes minutes. Hope you guys fixed everything already! Manuel. Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to verify mysqldump files
In the past when I used mysqldump, I used a slave database for backups and periodically testing restores. My process for testing: - Stop the slave process (so the db doesn't get updated). - Run the backup. - Create restore_test database. - Restore the backup to the restore_test database. - Use mysqldbcompare to compare the two databases. - Drop restore_test database. - Start the slave process. I have this scripted so it just runs and emails me the results. Useful link: http://dev.mysql.com/doc/workbench//en/mysqldbcompare.html -Original Message- From: Gary [mailto:listgj-my...@yahoo.co.uk] Sent: Wednesday, November 07, 2012 7:52 AM To: mysql@lists.mysql.com Subject: How to verify mysqldump files Can anyone suggest how I could verify that the files created by mysqldump are "okay"? They are being created for backup purposes, and the last thing I want to do is find out that the backups themselves are in some way corrupt. I know I can check the output of the command itself, but what if.. I don't know... if there are problems with the disc it writes to, or something like that. Is there any way to check whether the output file is "valid" in the sense that it is complete and syntactically correct? -- GaryPlease do NOT send me 'courtesy' replies off-list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: ndb_restore is not restoring users and grants
By default, the mysql users/privileges tables are MyISAM, not ndbcluster. ndb_mgm backup won't backup anything other than ndbcluster tables. The --restore-privilege-tables only works if you've converted those user and privileges tables to ndbcluster. >From the manual: >http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-programs-ndb-restore.html#option_ndb_restore_restore-privilege-tables "--restore-privilege-tables ndb_restore does not by default restore distributed MySQL privilege tables (MySQL Cluster NDB 7.2.0 and later). This option causes ndb_restore to restore the privilege tables. This works only if the privilege tables were converted to NDB before the backup was taken." I have been using this setup and it works well: http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-privilege-distribution.html It basically creates backups of the user and privileges tables, then converts them to ndbcluster. This allows you to back them up through ndb_mgm and also allows you to use the same users/privileges across all the SQL nodes in the cluster. Hope this helps. -Original Message- From: Bheemsen Aitha [mailto:pgb...@motorola.com] Sent: Tuesday, October 30, 2012 12:09 AM To: mysql@lists.mysql.com Subject: ndb_restore is not restoring users and grants Hi, I was able to backup my cluster using ndb_mgm and also restore using ndb_restore. However, when everything looks fine after restore, I see that users and grants are not restored. So, does ndb_restore restore all databases including information_schema and mysql databases, plus users and grants? Am I missing something? Do I need to do anything after restore step? here are the commands I used for restore. ndb_restore -c hostname -s -n 1 -m -b 3 -r --restore-privilege-tables=true --backup_path=/opt/app/mysql_data01/ndb_backup/BACKUP/BACKUP-3/ ndb_restore -c hostname -s -n 2 -b 3 -r --restore-privilege-tables=true --backup_path=/opt/app/mysql_data02/ndb_backup/BACKUP/BACKUP-3/ I really appreciate any help on this. Thanks -bheem Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Doubt Regd. Circular Replication In Mysql
I stand corrected and apologize. Numerous multi-master setup descriptions I've read have said to set this (including the one linked in the original question). However, as you said, the entry in the manual clearly says it defaults to 0. Learn something new every day. Thanks Shawn. On Sep 24, 2012, at 2:05 PM, "Shawn Green" wrote: > replicate-same-server-id = 0 Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Doubt Regd. Circular Replication In Mysql
replicate-same-server-id = 0 keeps MySQL from replicating binary log entries from itself. For instance, here's a rough overview: You write to Server A. Server A writes that to its binary log. Server B reads Server A's binary log and completes the same thing. Because log-slave-updates is enabled, Server B writes it to its own binary log. Server C reads Server B's binary log and completes the same thing. Again, with log-slave-updates enabled, Server C writes it to its own binary log. Server A reads Server C's binary log. Here's where the issue starts. Without replicate-same-server-id = 0, Server A will complete the insert/update/delete as it reads it from Server C's binary log. However, this query originated from Server A, so it's just going to do it again. Then it's again replicated to Server B, Server C, and so on. This can create a loop and/or break replication. For instance, if you drop a table on A. It replicates across, and back to A. Replication will error out because when it tries to drop the same table again, it already doesn't exist. You need replicate-same-server-id = 0 set so that it knows not to execute any binary log entries with its own server ID. From: Adarsh Sharma [mailto:eddy.ada...@gmail.com] Sent: Monday, September 24, 2012 10:39 AM To: Stillman, Benjamin Subject: Re: Doubt Regd. Circular Replication In Mysql Yes I fixed , but i solve the issue by enabling log-slave-updates only Why we use the below parameter :- replicate-same-server-id = 0 Ya i configured auto-increment settings properly. Thanks Thanks On Mon, Sep 24, 2012 at 8:03 PM, Stillman, Benjamin mailto:bstill...@limitedbrands.com>> wrote: Sounds like you're missing the following in your my.cnf on server B (probably all of them): replicate-same-server-id = 0 log-slave-updates While you're checking, might as well as make sure your auto-increment settings are in there and correct also. -Original Message- From: Adarsh Sharma [mailto:eddy.ada...@gmail.com<mailto:eddy.ada...@gmail.com>] Sent: Monday, September 24, 2012 10:23 AM To: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com> Subject: Doubt Regd. Circular Replication In Mysql Hi all, Today i set up a circular replication between three nodes A,B & C successfully. I expect whatever writes on A will propagated to B & then Propagated to C because the structure is like below :- A -> B - > C -> A I created a sample table stag in test database in A and insert few records that are also replicated to B but not to C. Now when i created the same table in C , it shows errors in show slave status\G output in A node. I needed this setup because all these servers are in different colos so that whatever writes to any node would replicated to others also for one database. I followed the below link for setting this circular replication :- http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch-p2 Is it possible to achieve whatever i needed or i need to create Multi Master set up 2 nodes only. Thanks Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices.
RE: Doubt Regd. Circular Replication In Mysql
Sounds like you're missing the following in your my.cnf on server B (probably all of them): replicate-same-server-id = 0 log-slave-updates While you're checking, might as well as make sure your auto-increment settings are in there and correct also. -Original Message- From: Adarsh Sharma [mailto:eddy.ada...@gmail.com] Sent: Monday, September 24, 2012 10:23 AM To: mysql@lists.mysql.com Subject: Doubt Regd. Circular Replication In Mysql Hi all, Today i set up a circular replication between three nodes A,B & C successfully. I expect whatever writes on A will propagated to B & then Propagated to C because the structure is like below :- A -> B - > C -> A I created a sample table stag in test database in A and insert few records that are also replicated to B but not to C. Now when i created the same table in C , it shows errors in show slave status\G output in A node. I needed this setup because all these servers are in different colos so that whatever writes to any node would replicated to others also for one database. I followed the below link for setting this circular replication :- http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch-p2 Is it possible to achieve whatever i needed or i need to create Multi Master set up 2 nodes only. Thanks Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: query help
I think this will get you there: SELECT LEAD, COUNT(*) FROM ORDERS GROUP BY LEAD; It'll give you something more like: | LEAD | COUNT(*) | | F | 44 | | S | 122 | | R | 32 | -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Thursday, September 13, 2012 9:51 AM To: mysql@lists.mysql.com Subject: query help I have a table like this: |ORDERS| |ID| DATE | QNT | LEAD | |342 | 8-12-12 | 32 | F| |345 | 8-15-12 | 12 | S| |349 | 8-16-12 | 9 | R| I am looking for a way to query it with counts by the LEAD column in order to tell what the number of each type lead is, so that I get something like this: F_LEADS S_LEADS R_LEADS 44 122 32 Is this possible? If so can anyone help with syntax? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Error starting data node
I just realized I shouldn't be reading these first thing in the morning before my first cup of coffee.. That says node id 10, then the IP. Sorry about that. -Original Message- From: Stillman, Benjamin [mailto:bstill...@limitedbrands.com] Sent: Wednesday, August 08, 2012 8:56 AM To: 'Aastha'; mysql@lists.mysql.com Subject: RE: Error starting data node Unable to connect with connect string: nodeid=10,172.17.3.66:1186 There's a comma between 10 and 172 instead of a period. -Original Message- From: Aastha [mailto:aast...@gmail.com] Sent: Tuesday, August 07, 2012 6:20 PM To: mysql@lists.mysql.com Subject: Error starting data node Helo, I am receiving error when trying to start the data node. The configuration seems ok. Kindly help. *C:\mysql\bin>ndbd* *Unable to connect with connect string: nodeid=10,172.17.3.66:1186* *Retrying every 5 seconds. Attempts left: 12 11 10 9 8 7 6 5 4 3 2 1, failed.* *2012-08-07 18:19:20 [ndbd] ERROR-- Could not connect to management server, e**rror: ''* *Config.ini on management node :* [ndbd default] # Options affecting ndbd processes on all data nodes: NoOfReplicas=1# Number of replicas DataDir=C:/mysql/bin/cluster-data # Directory for each data node's data files # Forward slashes used in directory path, # rather than backslashes. This is correct; # see Important note in text DataMemory=80M# Memory allocated to data storage IndexMemory=18M # Memory allocated to index storage # For DataMemory and IndexMemory, we have used the # default values. Since the "world" database takes up # only about 500KB, this should be more than enough for # this example Cluster setup. [ndb_mgmd] # Management process options: HostName=172.17.3.66 # Hostname or IP address of management node DataDir=C:/mysql/bin/cluster-logs # Directory for management node log files NodeId=1 [ndbd] # Options for data node "A": HostName=172.17.3.69 # Hostname or IP address NodeId=10 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.7.20 [mysqld] # SQL node A options: HostName=172.17.3.68 # Hostname or IP address NodeId=60 [mysqld] # SQL node B options: HostName=172.17.3.67 # Hostname or IP address NodeId=61 *my.ini on management server* [mysql_cluster] # Options for management node process config-file=c:/mysql/bin/config.ini configdir=c:/mysql/bin/cluster-cache/ *my.ini on data node * [mysql_cluster] # Options for data nod process: ndb-connectstring=172.17.3.66 # location of the management server ndb-nodeid=10 Kindly help. I have looked at the configuration and things fine to me Regards, Aastha Gupta Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- 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: Error starting data node
Unable to connect with connect string: nodeid=10,172.17.3.66:1186 There's a comma between 10 and 172 instead of a period. -Original Message- From: Aastha [mailto:aast...@gmail.com] Sent: Tuesday, August 07, 2012 6:20 PM To: mysql@lists.mysql.com Subject: Error starting data node Helo, I am receiving error when trying to start the data node. The configuration seems ok. Kindly help. *C:\mysql\bin>ndbd* *Unable to connect with connect string: nodeid=10,172.17.3.66:1186* *Retrying every 5 seconds. Attempts left: 12 11 10 9 8 7 6 5 4 3 2 1, failed.* *2012-08-07 18:19:20 [ndbd] ERROR-- Could not connect to management server, e**rror: ''* *Config.ini on management node :* [ndbd default] # Options affecting ndbd processes on all data nodes: NoOfReplicas=1# Number of replicas DataDir=C:/mysql/bin/cluster-data # Directory for each data node's data files # Forward slashes used in directory path, # rather than backslashes. This is correct; # see Important note in text DataMemory=80M# Memory allocated to data storage IndexMemory=18M # Memory allocated to index storage # For DataMemory and IndexMemory, we have used the # default values. Since the "world" database takes up # only about 500KB, this should be more than enough for # this example Cluster setup. [ndb_mgmd] # Management process options: HostName=172.17.3.66 # Hostname or IP address of management node DataDir=C:/mysql/bin/cluster-logs # Directory for management node log files NodeId=1 [ndbd] # Options for data node "A": HostName=172.17.3.69 # Hostname or IP address NodeId=10 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.7.20 [mysqld] # SQL node A options: HostName=172.17.3.68 # Hostname or IP address NodeId=60 [mysqld] # SQL node B options: HostName=172.17.3.67 # Hostname or IP address NodeId=61 *my.ini on management server* [mysql_cluster] # Options for management node process config-file=c:/mysql/bin/config.ini configdir=c:/mysql/bin/cluster-cache/ *my.ini on data node * [mysql_cluster] # Options for data nod process: ndb-connectstring=172.17.3.66 # location of the management server ndb-nodeid=10 Kindly help. I have looked at the configuration and things fine to me Regards, Aastha Gupta Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: trouble with perl
http://bixsolutions.net/forum/thread-18.html -Original Message- From: Elim Qiu [mailto:elim@gmail.com] Sent: Monday, August 06, 2012 5:42 PM To: mysql@lists.mysql.com Subject: trouble with perl To populate table for a perl forum, I got the following error: aMac:mwf elim$ perl install.pl dyld: lazy symbol binding failed: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace dyld: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace My OS is Mac OS X 10.6.8 64 bit, Apple built-in perl is 5.10.0 (64bit) and MySQL is newly upgraded to 5.5.27 MySQL Community Server (GPL) (64bit) I don't know how to make DBD:mysql work. Any idea is appreciated Maybe I have to get mysql source and compile/install it with custom options? Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: DECIMAL datatype automatically makes blank become 0
Meant to add: My workaround was to set the column as varchar then convert it to decimal after the import. When adding to the table, I use a temp table to import to as varchar, convert the column, the just select from temp table into prod table. -Original Message- From: Stillman, Benjamin [mailto:bstill...@limitedbrands.com] Sent: Monday, August 06, 2012 2:05 PM To: 'Fred G' Cc: mysql@lists.mysql.com Subject: RE: DECIMAL datatype automatically makes blank become 0 I don't believe this is limited to Workbench. I saw the same behavior using LOAD INFILE and a pipe-delimited file. There's a bug report that's been open since November of 2006 about this: http://bugs.mysql.com/bug.php?id=23212 -Original Message- From: Fred G [mailto:bayespoker...@gmail.com] Sent: Monday, August 06, 2012 9:48 AM To: Hassan Schroeder Cc: mysql@lists.mysql.com Subject: Re: DECIMAL datatype automatically makes blank become 0 Thanks for the response. I'm not sure that is exactly what is happening for me. I tried an example where I created a dummy database and a dummy table. If I create a column with decimal datatype and insert 2 different rows, one blank and one null, they both are treated as nulls. This is what I would like, but it does not work this way when I try to import a csv. When I try to import a csv file with either null or blank values for a decimal datatype, they both get treated as 0.00 (if we are using DECIMAL(12,2)). I think the issue has to do with the way MySQL 5.2 Workbench imports csv values. I experimented with a few different tests, but I haven't found a way to successfully treat null decimal values being imported from a csv as nulls in the database without just making the whole column a VARCHAR(255) datatype, which does seem to allow for nulls, but just seems like the wrong way to solve the problem. On Sun, Aug 5, 2012 at 9:53 AM, Hassan Schroeder wrote: > On Sat, Aug 4, 2012 at 8:14 PM, Fred G wrote: > > > But I'm sure that I must be missing something here. Is there a way > > to > use a > > DECIMAL-like operator that treats blanks as blanks? > > A DECIMAL column is either going to contain a decimal number or NULL; > 'blank' isn't a term that even makes sense in this context. > > If you want NULL rather than 0 for a non-specified value, insert it > that way. > > -- > Hassan Schroeder hassan.schroe...@gmail.com > http://about.me/hassanschroeder > twitter: @hassan > Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- 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: DECIMAL datatype automatically makes blank become 0
I don't believe this is limited to Workbench. I saw the same behavior using LOAD INFILE and a pipe-delimited file. There's a bug report that's been open since November of 2006 about this: http://bugs.mysql.com/bug.php?id=23212 -Original Message- From: Fred G [mailto:bayespoker...@gmail.com] Sent: Monday, August 06, 2012 9:48 AM To: Hassan Schroeder Cc: mysql@lists.mysql.com Subject: Re: DECIMAL datatype automatically makes blank become 0 Thanks for the response. I'm not sure that is exactly what is happening for me. I tried an example where I created a dummy database and a dummy table. If I create a column with decimal datatype and insert 2 different rows, one blank and one null, they both are treated as nulls. This is what I would like, but it does not work this way when I try to import a csv. When I try to import a csv file with either null or blank values for a decimal datatype, they both get treated as 0.00 (if we are using DECIMAL(12,2)). I think the issue has to do with the way MySQL 5.2 Workbench imports csv values. I experimented with a few different tests, but I haven't found a way to successfully treat null decimal values being imported from a csv as nulls in the database without just making the whole column a VARCHAR(255) datatype, which does seem to allow for nulls, but just seems like the wrong way to solve the problem. On Sun, Aug 5, 2012 at 9:53 AM, Hassan Schroeder wrote: > On Sat, Aug 4, 2012 at 8:14 PM, Fred G wrote: > > > But I'm sure that I must be missing something here. Is there a way > > to > use a > > DECIMAL-like operator that treats blanks as blanks? > > A DECIMAL column is either going to contain a decimal number or NULL; > 'blank' isn't a term that even makes sense in this context. > > If you want NULL rather than 0 for a non-specified value, insert it > that way. > > -- > Hassan Schroeder hassan.schroe...@gmail.com > http://about.me/hassanschroeder > twitter: @hassan > Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Looking for consultant
7.2 introduces geographic clustering: https://blogs.oracle.com/MySQL/entry/synchronously_replicating_databases_across_data http://dev.mysql.com/tech-resources/articles/mysql-cluster-7.2.html (section titled: Enhancing Cross Data Center Scalability: Multi-Site Clustering) Data nodes can be located at multiple data centers. They've had geographic replication for a while, but this makes it even easier. Obviously performance depends on your network setup. I believe they suggest latency under 20ms and bandwidth between the datacenters of 1Gbit or faster. Redundant management and SQL nodes can be split across the datacenters also. -Original Message- From: Howard Hart [mailto:h...@ooma.com] Sent: Wednesday, July 18, 2012 8:26 PM To: mysql@lists.mysql.com Subject: Re: Looking for consultant You could write to an InnoDB frontend with master/master replication at each site, and slave off the local InnoDB server to your local cluster at each site. Would make your writes limited by your InnoDB server performance and remote replication speed, but reads would run at cluster speeds and be a bit more bulletproof. That could also potentially cover the foreign key constraints limitation in cluster since last I checked, it doesn't support these--may have changed recently--don't know. The foreign key constraint checks in this case would be covered by the InnoDB frontend prior to pushing to cluster. Also looks like the latest MySQL cluster solution supports asynchronous binlog style replication per link below, so guess that's a possibility too now. http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html On 07/18/2012 04:45 PM, Rick James wrote: > Keep in mind that all "cluster" solutions are vulnerable to a single power > failure, earthquake, flood, tornado, etc. > > To "protect" from such, you need a hot backup located remotely from the > "live" setup. This introduces latency that will kill performance -- all > cluster solutions depend on syncing, heartbeats, etc, that cannot afford long > latencies. > > You may choose to ignore that issue. But, before going forward you need to > make that decision. > >> -Original Message- >> From: Antonis Kopsaftis [mailto:ak...@edu.teiath.gr] >> Sent: Wednesday, July 18, 2012 9:09 AM >> To: Carl Kabbe >> Cc: mysql@lists.mysql.com >> Subject: Re: Looking for consultant >> >> Hello, >> >> As far i can understand by your post, you need a high availability >> mysql cluster with large capacity. >> For having high availability you need something that can give you >> multi-master replication between two or more mysql servers. >> >> In my knowledge there are three solutions that can give you multi- >> master >> replication: >> >> 1. "Official" mysql cluster >> It's an Enterprise class solution, very complicated, but 'it fully >> multi-master. I was using one for about two year, but i dont >> recommend it because (at least in my setup) it did not have very good >> performance. >> It's use it's own storage engine(NDB) which has a number of >> limitations. >> >> 2. Tungsten replicator. >> It 's relative new product. It support multi-master replication >> between different type of databases, and it seems very promising. >> It's java based. I haven't tested it but you can read a lot about on: >> http://datacharmer.blogspot.com >> >> 3. Percona xtraDB cluster >> It's also a relative new product. It's also support multi-master >> replication, and it seems to have very good performance. The last 3 >> weeks i have installed a 3 node cluster of percona software and i'm >> testing it. It seems to works ok, and after some optimization it has >> better performance than my production mysql setup(simple >> primary-slave >> replication) on same hardware (virtual machines). If i dont find any >> serious problem till September i will use it for production. >> >> >> Now,for you application to communicate with the two mysql master >> nodes there several solutions: >> 1. Desing your app to use both mysql servers. With this solution you >> can ever split writes in the one server, and reads in the other. It's >> up to you to do whatever you want. >> >> 2. Setup a simple heartbeat solution and setup a floating virtual ip >> between you mysql servers. If one of the mysql server( i mean the >> whole >> OS) crash, the floating ip will be attached to the second server. >> >> 3. In each app server, install a tcp load balancer software like >> "haproxy" and balance the mysql tcp connections between your app >> servers and the mysql servers. >> >> Regards, >> akops >> >> >> On 18/7/2012 6:11 μμ, Carl Kabbe wrote: >>> 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 fa
RE: Subquery taking too much time on 5.5.18?
As far as I know, a B-tree index can be used by LIKE as long as the string doesn't begin with a wildcard. " LIKE 'X%' " should be fine to use an index on the name column. The index only includes results in the search base which start with X. That said, I probably wouldn't use a subquery, either. But since the OP says they'd prefer to use subqueries, try this and tell me what happens: SELECT * FROM A WHERE A.id IN ( SELECT A_ID FROM ( SELECT A_ID FROM B WHERE B.name LIKE 'X%' ) AS x ); It's just wrapping the subquery within another subquery, forcing MySQL to run from the inside out. I don't have a running instance nearby to test on, but I hope it helps. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, July 06, 2012 11:58 AM To: mysql@lists.mysql.com Subject: Re: Subquery taking too much time on 5.5.18? Am 06.07.2012 17:46, schrieb Cabbar Duzayak: > Hi Everyone, > > I have been trying to understand why subqueries are taking tooo much > time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. > > In a nutshell, I have 2 tables: A and B. And, I do something like this: > > SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like > 'X%'); > > Table A has 460,000 rows and Table B has 5000 rows, and A.id is my > primary key, and B.name is indexed. Also, the sub-query here (B.name > starts with X%) returns about 300 rows. query B can not used any key because 'like' never can use any key i try to avoid subqueries wherever i can becasue the mysql query-optmizer is really weak in most cases (it appears 5.6 will be much better in many of them) such things i would always do with two queries in the application * first the sub-query * genearte the query above with the results in the app * fire up the final query Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: alternative to slow query
Not sure why it wouldn't show primary as a possible key then... >From your first email: *** 1. row *** id: 1 select_type: SIMPLE table: e type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 95127 Extra: I'd be curious to see the explain from this: select id, lang, term from expression where id = (insert a random, valid id value here); Does it use a key then? Or at least show primary as a possible key? -Original Message- From: brian [mailto:mysql-l...@logi.ca] Sent: Tuesday, July 03, 2012 1:47 PM To: mysql@lists.mysql.com Subject: Re: alternative to slow query On 12-07-03 01:13 PM, Stillman, Benjamin wrote: > I don't see an index for expression.id. > mysql db_lexi > show index from expression\G *** 1. row *** Table: expression Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 96111 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: ... and 10 other keys, 2 of which are also being used in the WHERE part. I left them out for clarity because while they help to narrow things down a bit including them doesn't speed up the query all that much. mysql db_lexi > SHOW TABLE STATUS WHERE NAME = 'expression'\G *** 1. row *** Name: expression Engine: InnoDB Version: 10 Row_format: Compact Rows: 100747 Avg_row_length: 193 Data_length: 19447808 Max_data_length: 0 Index_length: 31621120 Data_free: 48234496 Auto_increment: 240840 Create_time: 2012-06-27 14:18:57 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.77 sec) mysql db_lexi > SHOW TABLE STATUS WHERE NAME = 'expression_expression'\G *** 1. row *** Name: expression_expression Engine: InnoDB Version: 10 Row_format: Compact Rows: 106191 Avg_row_length: 103 Data_length: 11026432 Max_data_length: 0 Index_length: 14204928 Data_free: 48234496 Auto_increment: 218884 Create_time: 2012-06-27 14:19:31 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.13 sec) I realise that I should have posted all this in the original msg. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: alternative to slow query
I don't see an index for expression.id. -Original Message- From: brian [mailto:mysql-l...@logi.ca] Sent: Tuesday, July 03, 2012 12:28 PM To: mysql@lists.mysql.com Subject: Re: alternative to slow query On 12-07-02 09:33 PM, yoku ts wrote: > Hello, > > add index to expression1_id and expression2_id on expression_expression. > > it doesn't use index,following, > >> WHERE >> ee2.expression1_id = $ID >> OR >> ee1.expression2_id = $ID > Thank you for your reply. The table already has indexes on these columns, however: mysql db_lexi > show index from expression_expression\G *** 1. row *** Table: expression_expression Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 106191 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *** 2. row *** Table: expression_expression Non_unique: 1 Key_name: expression2_id_idx Seq_in_index: 1 Column_name: expression2_id Collation: A Cardinality: 106191 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *** 3. row *** Table: expression_expression Non_unique: 1 Key_name: type_lien_id_idx Seq_in_index: 1 Column_name: type_lien_id Collation: A Cardinality: 43 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *** 4. row *** Table: expression_expression Non_unique: 1 Key_name: expression1_id_idx Seq_in_index: 1 Column_name: expression1_id Collation: A Cardinality: 106191 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 4 rows in set (0.23 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Question about testing memcmp()
By recent security breach, do you mean the issue with passwords? If so: http://www.dbasquare.com/2012/06/11/a-security-flaw-in-mysql-authentication-is-your-system-vulnerable/ for i in `seq 1 2000`; do mysql -u USERNAME --password=INCORRECTPASSWORD -h HOSTNAME ; done If you get in using that, you're vulnerable. But as Harald said, why not just update? Always better to be patched and up-to-date. Ben Stillman bstill...@limitedbrands.com -Original Message- From: Sabika Makhdoom [mailto:sabika.makhd...@gmail.com] Sent: Monday, June 25, 2012 12:17 AM To: mysql@lists.mysql.com Subject: Question about testing memcmp() I want to test our memcmp() binaries to see if we have the mysql binaries that are impacted by the recent security breach. How do I test it? Thanks Life was meant to be mobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: mysql binlogs and their expiry times
You shouldn't have to flush the logs manually. I have been flushing binary logs using this expire_logs_days for some time with no other intervention. One thing to keep in mind is that if you have limited disk space, and an usual spike in updates your binary logs can still grow and consume all available disk space if you are not keeping tabs on available disk space when using the expire_logs_days directive or a cron job. Depending on your volume of traffic or the size of the volume this may not be an issue. Ben Wiechman Network Admin Wisper High Speed Internet > -Original Message- > From: Jim Lyons [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 08, 2008 10:51 AM > To: Zbigniew Szalbot > Cc: mysql@lists.mysql.com > Subject: Re: mysql binlogs and their expiry times > > According to the doc for this parameter, "Possible removals happen at > startup and at binary log rotation." This implies that removal is not > automatic - you have to either flush logs or bounce the server. We use > "Purge Master" periodically. > > On Wed, Oct 8, 2008 at 3:10 AM, Zbigniew Szalbot <[EMAIL PROTECTED]> > wrote: > > > Hi there, > > > > I hope someone can help. Due to they way my HD has been sliced I had > > to move mysql database to /usr/local/mysql. All works fine. Last week > > I added this entry: > > > > #expire bin logs > > expire_logs_days = 7 > > > > to /usr/local/mysql/my.cnf > > > > I restarted the MySQL server and now I have been waiting for the > > binlogs to automatically expire but this is not happening: > > > > $ ls -l /usr/local/mysql > > > > -r--r--r-- 1 mysql mysql4954 Oct 1 07:30 my.cnf > > drwx-- 2 mysql mysql1536 Sep 27 07:10 mysql > > -rw-rw 1 mysql mysql 1073745213 Sep 2 04:07 mysql-bin.47 > > -rw-rw 1 mysql mysql 1073746878 Sep 7 03:48 mysql-bin.48 > > -rw-rw 1 mysql mysql 1073745707 Sep 11 20:07 mysql-bin.49 > > -rw-rw 1 mysql mysql 175527890 Sep 12 08:32 mysql-bin.50 > > -rw-rw 1 mysql mysql 128272 Sep 12 08:40 mysql-bin.51 > > -rw-rw 1 mysql mysql 1073745119 Sep 17 04:35 mysql-bin.52 > > -rw-rw 1 mysql mysql 1073747657 Sep 22 04:26 mysql-bin.53 > > -rw-rw 1 mysql mysql 1073744456 Sep 27 03:28 mysql-bin.54 > > -rw-rw 1 mysql mysql 986782722 Oct 1 07:32 mysql-bin.55 > > -rw-rw 1 mysql mysql 1073742442 Oct 6 04:18 mysql-bin.56 > > -rw-rw 1 mysql mysql 536487381 Oct 8 07:45 mysql-bin.57 > > -rw-r- 1 mysql mysql 209 Oct 6 04:18 mysql-bin.index > > > > Do you have any idea why? Or if /usr/local/mysql/ is a correct > > location for my.cnf file? Perhaphs it should go to /usr/local/etc/ ? > > > > If it matters, I use mysql-server-5.0.67 on FreeBSD 7.0-Release machine. > > > > Many thanks! > > > > -- > > Zbigniew Szalbot > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > Jim Lyons > Web developer / Database administrator > http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DRBD Setup & Replication
I currently have a MySQL server in production and am considering protecting it with DRBD. The kicker is that it is a production database and so I can't take it offline, or can take it offline for only a very (several minutes max) short period of time. Is it feasible to get this working without much/any downtime on the primary server? How gracefully does replication behave off a DRBD pair when a failover occurs? It depends partially on just how the active server fails I suppose more than anything else. Does anyone have any experience with this and care to comment? Ben Wiechman
RE: MySQL server statistics
With 5.1 you have more control over general query log and the slow query log - enable or disable at runtime, output to file or DB table. As of MySQL 5.1.6, the server can write general query and slow query entries to log tables, log files, or both. For details, see Section 5.2.1, "Selecting General Query and Slow Query Log Output Destinations". As of MySQL 5.1.12, additional runtime control of the general query and slow query logs is available: You can enable or disable logging, or change the name of the log file. See Section 5.2.3, "The General Query Log", and Section 5.2.5, "The Slow Query Log". See http://dev.mysql.com/doc/refman/5.1/en/log-files.html If you can either use non-GA or wait until 5.1 is GA. Or - looking at your original question it may be able to narrow down the source of the queries if you can graph your data more often - maybe every 10-15 seconds instead of a longer interval to help you profile which applications are hammering your db server. Ben Wiechman > -Original Message- > From: Yves Goergen [mailto:[EMAIL PROTECTED] > Sent: Monday, July 21, 2008 11:19 AM > To: Rob Wultsch > Cc: mysql@lists.mysql.com > Subject: Re: MySQL server statistics > > On 20.07.2008 23:49 CE(S)T, Rob Wultsch wrote: > > On Sun, Jul 20, 2008 at 1:33 PM, Yves Goergen > > <[EMAIL PROTECTED]> wrote: > >> Hello, > >> > >> I've installed MySQL server 5.0 and have written a small statistics > script > >> that regularly checks the number of connections and queries to the > server, > >> which I can then view in a diagram. But sometimes it just says that at > a > >> time, unusually many connections or queries have been made to the > server. I > >> cannot see what causes them. Neither the user nor the actual queries. > >> > >> At work I got in touch with the Oracle Enterprise Manager recently. I > >> haven't looked at it too closely yet, but I think it could give useful > >> information about each session, what it does and more importantly what > it > >> did. > >> > >> I have no idea what to search for to get this information from the > MySQL > >> server. So I had to ask here first. Is there any method to get those > >> statistics? I don't mean the SHOW PROCESSES list, it only contains a > >> snapshot of the very moment when MySQL got to execute my command. I > mean > >> information about recent activity, like 15 minutes, 2 hours or so. > > > > Check out the general query log: > > http://dev.mysql.com/doc/refman/5.0/en/query-log.html > > As far as I have understood that, there is only one log that can be > turned on or off. When turned on, it logs every single query sent to the > server. I hope it also logs some other data, like the username and maybe > the client address (local/remote). But this has one major drawback: To > enable or disable the log (which, I can imagine, takes quite a bit > performance) the whole server must be stopped and restarted. This isn't > really an option for a production database server, just to know what's > going on from time to time. > > -- > Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> > Visit my web laboratory at http://beta.unclassified.de > > -- > 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: mysqldump of huge innodb database
Unfortunately the additional parameters didn't solve my problem. But thanks for your response! ssh [EMAIL PROTECTED] \ "mysqldump -u XYZ --verbose --password=XYZ --quick --single-transaction --net_buffer_length=1G --max_allowed_packet=1G dbmail | /bin/gzip" \ > /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp I don't thinks the problem and also following command didn't work mysqldump -h XYZ -u XYZ --verbose --password=XYZ --quick --single-transaction --net_buffer_length=1G --max_allowed_packet=1G dbmail | gzip > /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp Always get the result: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 177912 Script ended at: Sat Sep 22 06:32:16 CEST 2007 (1190435536) Execution Time: Hours: 4 Minutes: 269 Seconds: 16155 OR mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 189738 Script ended at: Sun Sep 23 06:30:30 CEST 2007 (1190521830) Execution Time: Hours: 4 Minutes: 267 Seconds: 16048 OR mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 137554 Script ended at: Mon Sep 24 06:30:01 CEST 2007 (1190608201) Execution Time: Hours: 4 Minutes: 267 Seconds: 16020 I know these two other solutions: - Setting up a replication service - Stopping mysql, copying db-files, and restart mysql Doing replication is not possible cause of the huge size of the database. Hard-core copy of db-files causes a downtime of up to 8 hours so it would be possible. Or does somebody has another (hope better) solution? With best regards, Benjamin Schmidt Hartleigh Burton wrote: Hiya, I was backing up a 95GB InnoDB database and forever had problems. It ended up working and I never really worked out exactly what the cause was... but try using the following: --opt (does --quick + extended-insert + others) --net_buffer_length=1G (set this to whatever you want, 1G is the largest it will support. I was backing up uncompressed audio so had it at 1G. When --opt is set it also uses --extended-insert, the net_buffer_length tells mysqldump when to break the extended insert and create a new insert. Useful when dealing with large packets) --max_allowed_packet=1G (or whatever you expect your largest packet to be, in my case was up to 1G) Example: mysqldump -u mysqldump --password= --opt --verbose --net_buffer_length=1G --max_allowed_packet=1G --single-transaction dbname > dbname.sql If this still fails... try running the backup from a remote computer either by using MySQL Administrator or mysqldump. Occasionally I would get the same error you received when running mysqldump on localhost, however it would complete when run from either my workstation or on another server. I can't really explain why this would happen, but now I just run all of my backups straight to a mirrored server. Example: mysqldump -h 192.168.x.x -u mysqldump --password= --opt --verbose --net_buffer_length=1G --max_allowed_packet=1G --single-transaction dbname > dbname.sql Good luck, hope this helps. Hartz. -Original Message- From: Benjamin Schmidt [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 September 2007 7:05 PM To: mysql@lists.mysql.com Subject: mysqldump of huge innodb database Hello list members Since a few days I get this error message when making a backup of my database: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 174955 Script ended at: Tue Sep 4 06:45:37 CEST 2007 (111137) Execution Time: Hours: 4 Minutes: 282 Seconds: 16956 The ibdata1 file now has a size of 42GB (I use the innodb engine). The command to backup is following: ssh [EMAIL PROTECTED] \ "mysqldump -u mysqldump --password= --quick --single-transaction dbmail | /bin/gzip" > /Backup/mysqldump.tar.gz And this is my config-file (default values from the debian package): ... innodb_data_file_path=ibdata1:10M:autoextend:max:183G key_buffer = 16MB max_allowed_packet = 64M thread_stack = 128K query_cache_limit = 1048576 query_cache_size = 16777216 query_cache_type = 1 set-variable = max_connections=1000 max_allowed_packet = 64M ... As I wrote above, it worked this way a very long time. And it should work again ;) Does anyone know this problem or has an idea? Many thanks in advance, Benjamin Schmidt smime.p7s Description: S/MIME Cryptographic Signature
mysqldump of huge innodb database
Hello list members Since a few days I get this error message when making a backup of my database: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 174955 Script ended at: Tue Sep 4 06:45:37 CEST 2007 (111137) Execution Time: Hours: 4 Minutes: 282 Seconds: 16956 The ibdata1 file now has a size of 42GB (I use the innodb engine). The command to backup is following: ssh [EMAIL PROTECTED] \ "mysqldump -u mysqldump --password= --quick --single-transaction dbmail | /bin/gzip" > /Backup/mysqldump.tar.gz And this is my config-file (default values from the debian package): ... innodb_data_file_path=ibdata1:10M:autoextend:max:183G key_buffer = 16MB max_allowed_packet = 64M thread_stack = 128K query_cache_limit = 1048576 query_cache_size = 16777216 query_cache_type = 1 set-variable = max_connections=1000 max_allowed_packet = 64M ... As I wrote above, it worked this way a very long time. And it should work again ;) Does anyone know this problem or has an idea? Many thanks in advance, Benjamin Schmidt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column type suggestions
I recall there is a command that will display suggested optimal columns types for a given table based on the data stored in the table, but I haven't been able to figure out what it is. Help! Thanks. Ben Wiechman Wisper High Speed Internet [EMAIL PROTECTED]
James Benjamin - Seriously Disruptive MySQL DBA
hi, if anybody wants to reach me please try this account - [EMAIL PROTECTED] or call me on 0044 750 408 9783 = Weather Almanac, Weather Folklore Books Buy Eric Sloane weather almanac, books on weather vanes & more today. http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=5e9d5c58760ae9512db1d2881a701fe8 -- Powered By Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seriously Disruptive DBA Needed!!!!!!!!! - London, United Kingdom
Hi, I work for a mobile technology company in London. We are looking to recruit a MySQL DBA. The world of the enterprise DBA is changing, be part of it. Like it or not, open source technology is making serious inroads into the enterprise environment. Justifying the purchase of a heavyweight RDBMS with it's associated overhead, when credible Open Source alternatives exist, is getting harder and harder. Our client positively welcomes this change. Without it, they wouldn't exist.. Having said this, business models may be changing, but operational requirements are not. Although they are committed to Open Source, they are more passionately committed to their service, their subscribers, and not being woken up at unsociable hours of the morning. Our client is hiring a DBA to be part of it's Engineering team. This is both a production support and development role that requires architect grade thinking. They are a small team of open source and telephony experts, many with a track record of contribution to Open Source projects - That's the disruptive part. The serious part is their focus on quality of service, sustainability and our global aspirations. Your CV will tell a credible story that involves successful implementations at an enterprise scale. Your focus will be on service delivery and sustainability, not be a simple "shopping list" of products and acronyms (our client are smart people, and their attention span is short). You will bring to our client mature DBA skills to bear on a rapidly growing environment. If you agree that the world is changing, and want to be right at the forefront of these changes, we need to talk. Whether you're a seasoned Oracle DBA who doesn't know their INNODB from their MyISAM, or a hardcore MySQL DBA with contributions to the code base, our client wants to hear from you. Our client is in a position to offer an exceptional basic - they want quality people and that´s why I am working with them - to source them quality and exceptional candidates not only for this role but several others that they are recruiting for. Perhaps we could speak? Thanks James = -- Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Database Administrator/ Data Architect - LONDON
hi everyone, i am looking to find a MySQL guru to come work full time at a passionate mobile technology/ communications company in the heart of London. This truly is the place to be! They have global footprint - everyone in the company are technically competent and extremely passionate about what the do. Their product has launched recently in the US (in addition to initially launched in the UK, it has launched in Germany and other European countries). More territories are planed for late 07 and 08. We need to find a DBA to take control of the MySQL RDBMS and the data held therein. The role will actually combine elements of Data Architect too. The role is going to involve liaising with the application developers and the network operations team. The dress style of the company is informal and they also have a Nintendo Wii in the office that anybody can play on at any time of the day(work load permitting of course). Feel free to contact me using jamesbenjamin AT linuxmail DOT org thanks James = Measure Network Availability & Security Carrier, MSO and Vendor Triple Play product testing reduces downtime. http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=d446202f87d73543d468e6891d79072d -- Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOB: MySQL DBA - London, UK
Hi, I am looking to hire a permanent MySQL Database Administrator to take control of our companyÅ› database architecture and overall data held within. We are a passionate company at the forefront of our marketing - innovative mobile communication. We have a superb office location overlooking London - near HMS Belfast. Please e-mail me to discuss further. My e-mail address is [EMAIL PROTECTED] Many thanks JB = -- Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query
Brilliant, this one works perfect! Thank you for the quick reply! Ben Rolando Edwards wrote: select distinct email_address from people P,registered_products A,registered_products B where P.person_id = A.person_id and A.product_type = "Product A" and P.person_id = B.person_id and B.product_type = "Product B" ; Give it a try !!! - Original Message - From: "Benjamin Ventura" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Thursday, August 2, 2007 1:22:00 PM (GMT-0500) America/New_York Subject: Complex query I have a database tracking registrations of software products with two related tables, "registered_products" and "people". I need to pull a list of email addresses from the people table BUT I only want to pull people records who own very specific combinations of products from the registered_products table. Let's say I want people who own both Product A AND Product B... My first guess is to write a query like this: select distinct email_address from people join registered_products on people.person_id = registered_products.person_id where registered_products.product_type = "Product A" and registered_products.product_type = "Product B" However, this does not work, no rows are returned. When I think about it, that makes sense, because rows are evaluated one at a time, and no single product row can simultaneously have two values for the column "product_type". I need to only get results from a person record that has two related registered_product rows, one with a value of "Product A" and one with "Product B". What is the best way to write a query to do this? Thanks, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex query
I have a database tracking registrations of software products with two related tables, "registered_products" and "people". I need to pull a list of email addresses from the people table BUT I only want to pull people records who own very specific combinations of products from the registered_products table. Let's say I want people who own both Product A AND Product B... My first guess is to write a query like this: select distinct email_address from people join registered_products on people.person_id = registered_products.person_id where registered_products.product_type = "Product A" and registered_products.product_type = "Product B" However, this does not work, no rows are returned. When I think about it, that makes sense, because rows are evaluated one at a time, and no single product row can simultaneously have two values for the column "product_type". I need to only get results from a person record that has two related registered_product rows, one with a value of "Product A" and one with "Product B". What is the best way to write a query to do this? Thanks, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql dump help!
Not to ask a perhaps silly question, but are you specifically trying to use mysqldump, or just get a backup copy of your databases? If you just want to back them up download MySQL Administrator -> GUI = fun & easy... :) http://dev.mysql.com/downloads/ (Download MySQL GUI Tools - about half way down the page) Ben Wiechman -Original Message- From: Gary Josack [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 24, 2007 6:50 AM To: Red Hope Cc: mysql@lists.mysql.com Subject: Re: mysql dump help! Red Hope wrote: > I'm curious about one thing. When I go into MySQL > folder on the hard drive. I go into the 'bin' folder, > should there be an .exe program called mysqldump? or > not? > > Lillian > > > --- Carlos Proal <[EMAIL PROTECTED]> wrote: > > >> Yep, good for you, welcome to the "real" world >> You are changing the prompt but are still inside the >> dbms, you need to >> get out, because mysqldump is an application (.exe >> file) not a sql >> command, ie >> >> > > >> Welcome to the MySQL monitor. Commands end with ; >> or \g. >> Your MySQL connection id is 9 >> Server version: 5.0.41-community-nt-log MySQL >> Community Edition (GPL) >> >> Type 'help;' or '\h' for help. Type '\c' to clear >> the buffer. >> >> mysql> >> mysql> quit >> Bye >> >> D:\lillian>mysqldump -u root -ppassword test > >> test.sql; >> >> > -- > >> but maybe after the quit, the window will close, if >> that happens, open a >> new command prompt from "start -> programs -> >> accesories --> command >> prompt" and then run mysqldump >> >> Carlos >> >> >> Red Hope wrote: >> >>> Hey y'all, >>> I use charming Windows XP on here. I've taken >>> >> database >> >>> classes but lucky for me we never used *real* >>> >> MySQL. >> >>> Below I typed up exactly what I put into the MySQL >>> prompt and this is what it kicks back to me. >>> >>> Lillian >>> >>> >>> >>> mysql> >>> >>> mysql> \R shell> >>> PROMPT set to 'shell>' >>> >>> shell> >>> >>> shell>mysqldump -u root -ppassword test > >>> >> test.sql; >> >>> ERROR 1064 <4200>: You have an error in your SQL >>> syntax; checkthe manual that corresponds to your >>> >> MySQL >> >>> server version for the right syntax to use near >>> 'mysqldump -u root -ppassword test > test.sql' at >>> >> line >> >>> 1 >>> >>> >>> >>> --- Carlos Proal <[EMAIL PROTECTED]> wrote: >>> >>> >>> Can you email us the complete command and the >> error >> ? Carlos Red Hope wrote: > Well, that went over my head. :) I understand > > what > you're telling me, how to get there, but not how > > to do > it. bleh. > > When I start up MySQL Command Line Client, I'm > > always > prompted at "mysql>". So I told it to switch > >> from >> > > that > prompt to "shell>" prompt. It always starts up > >> in >> > "mysql>" prompt. Once I'm in shell, I tried the > > dump > procedure and it kept saying it couldn't > >> connect. >> > > So I > checked what databases it had, it shows them. I > > can't > even switch to a database because of no > > connection. > I'm not exactly sure why there's no 'connection' > > at > all. > > Thanks for trying so hard, Carlos! > Lillian > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >>> >>> >>> >>> >>> >>> > > >>> Got a little couch potato? >>> Check out fun summer activities for kids. >>> >>> > http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&c s=bz > >>> >>> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> >> > http://lists.mysql.com/[EMAIL PROTECTED] > >> > > > > > Luggage? GPS? Comic books? > Check out fitting gifts for grads at Yahoo! Search > http://search.yahoo.com/sear
validating, filtering price value for a decimal column from various strings
Hi list subscribers, i am inserting millions of product rows from csv files via LOAD DATA INFILE. Every product has a price, but this "price-strings" vary heavily. The main difference between them, is the decimal format. Sometimes it is european like this: 1.000.000,00 sometimes its american like this: 1,000,000.00 so some examples for these strings are: EUR 1,00 (meaning decimal 1.00) 1.00 € (meaning decimal 1.00) 1.000,00 EUR (meaning decimal 1000.00) EUR 1.000,00 (meaning decimal 1000.00) 1.000 EUR (meaning decimal 1000.00) 1,000 EUR (meaning decimal 1000.00) and now, i want to filter/validate that directly within the query. I think some RegEx could do the trick, but this is to much for me. Ive searched for some RegEx for validating decimals, but they allways use just one notation of a decimal. I think a good strategy for that, to filter all chars but [0-9\.,], and than to do some logic like to check how man chars after the last dot or comma (if its two you know its something like 0.00). No my problem is, i don't know where to start. Maybe with a stored procedure or something like that? I don't wanna use another language for this, because i would have to do some comprehensive update work then (selecting every row, checking the price, updating the price), and these rows get written every day, so i would have to do these updates once a day on a couple of million rows. Anyone get me in the right direction? Thanks in advance Regards Benjamin Bittner - Was ist Glück? Schlafen Fische überhaupt? Die Antworten gibt’s auf Yahoo! Clever.
patch for 5.1.11 to use openssl
i don't know the proper place to send this and i didn't see any mention of it in the list archives. there is a really small problem with the configure script in 5.1.11 (and other versions, perhaps, but i haven't looked) that prevents building with openssl support. patch below. thanks, ben -- --- configure.orig Sat Jun 24 08:45:34 2006 +++ configure Sat Jun 24 08:45:59 2006 @@ -39099,7 +39099,7 @@ # compiler warnings when using gcc 3.x if test "$openssl_include" != "/usr/include" then -openssl_includes="-I$ssl_include" +openssl_includes="-I$openssl_include" fi # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
impact of --without-server on include files installed
The source build of 5.0.18, on linux, when using --without-server, leaves out the following 3 headers that are installed when --without-server is not given: > include/mysql/mysqld_ername.h > include/mysql/mysqld_error.h > include/mysql/sql_state.h Is this intentional? This breaks the python MySQLdb module if built against the client-only install of mysql. I'm not sure which party is to blame, so I have to start somewhere. :) Thanks, -- Benjamin Rutt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration 4.0 to 4.1 performance problem
Hi, I host a big french website including forum (phpbb), search engine (mnogo), album (smartor), blogs... around 780Mo in MySQL Everything work fine with my old configuration : - One SQL/mail server bi-Xeon, 2.6Ghz, 3Go : FC4, MySQL 4.0, Postfix-Mysql, Courier-imap Mysql - One front server vi-Xeon, 2.2Gh, 2Go : ...FC2, Apache 1.33, php 4.4.0 Yersterday, I decided to update from mysql 4.0 to mysql 4.1. I simply use "rpm -Uv MySQL-serverrpm" Since this update, I see many mysql_too_many_connection. So I deciede to increase this setting in my my.cnf from 500 to 1000. But I don't know why Mysql is so slow to return query. The server keep a low load average around 1-2 and CPU's usage around 20%. I also try to use Fedora RPM, But problem is still present. Please help me, my visitors become crazy ;-) Below, I paste my different data : Thanks. Ben ### /etc/my.cnf [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 60 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #log=/home/logs/mysql_log max_connections = 1200 log_error = /var/lib/mysql/mysql_error_log old_passwords=1 ### mysqlreport MySQL 4.1.12 uptime 0 0:47:27 Tue Sep 13 00:06:40 2005 __ Key _ Buffer usage 18.51M of 384.00M %Used: 4.82 Write ratio 0.42 Read ratio 0.00 __ Questions ___ Total 2.39M 838.96/s Slow 0 0.00/s %Total: 0.00 %DMS: 0.00 DMS 381.82k 134.11/s 15.99 __ Table Locks _ Waited 18.69k 6.57/s %Total: 3.88 Immediate 462.66k 162.51/s __ Tables __ Open 512 of 512 %Cache: 100.00 Opened 895 0.31/s __ Connections _ Max used 704 of 1200 %Max: 58.67 Total 275.89k 96.91/s __ Created Temp Disk table 5.98k 2.10/s Table 17.80k 6.25/s File 7 0.00/s ### ./mysqlreport -dms -com 5 MySQL 4.1.12 uptime 0 9:29:17 Tue Sep 13 08:48:30 2005 __ Key _ Buffer usage 29.91M of 384.00M %Used: 7.79 Write ratio 0.33 Read ratio 0.00 __ Questions ___ Total 11.72M 343.26/s Slow 0 0.00/s %Total: 0.00 %DMS: 0.00 DMS 1.80M 52.80/s 15.38 UPDATE 997.17k 29.19/s 8.50 55.29 SELECT 750.53k 21.97/s 6.40 41.61 INSERT 33.51k 0.98/s 0.29 1.86 DELETE 22.35k 0.65/s 0.19 1.24 REPLACE 0 0.00/s 0.00 0.00 Com_ 1.37M 39.98/s 11.65 change_db 1.36M 39.92/s 11.63 set_option 437 0.01/s 0.00 show_status 280 0.01/s 0.00 begin 274 0.01/s 0.00 commit 274 0.01/s 0.00 __ Table Locks _ Waited 36.09k 1.06/s %Total: 1.58 Immediate 2.25M 65.85/s __ Tables __ Open 512 of 512 %Cache: 100.00 Opened 1.06k 0.03/s __ Connections _ Max used 704 of 1200 %Max: 58.67 Total 1.28M 37.46/s __ Created Temp Disk table 28.27k 0.83/s Table 87.37k 2.56/s File 19 0.00/s ### top top - 00:02:46 up 5:38, 3 users, load average: 1.84, 1.71, 1.50 Tasks: 165 total, 1 running, 164 sleeping, 0 stopped, 0 zombie Cpu(s): 9.8% us, 2.4% sy, 0.0% ni, 87.0% id, 0.4% wa, 0.1% hi, 0.3% si Mem: 3115044k total, 3036040k used, 79004k free, 545064k buffers Swap: 2031608k total, 0k used, 2031608k free, 620516k cached -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
Here is another question. Can you achieve the same performance having to different kinds of databases as though you were only using one? I am assuming that you are going to run into problems because you cannot set both types of databases to have a lot of memory allocated to them. Right? On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey <[EMAIL PROTECTED]> wrote: > As far as I know memory usage between the two table types is roughly the > same. The way memory is setup/used is somewhat different however. For > myisam the primary memoy buffer to accelerate queries is the key_buffer > which caches data for keys. In innodb you have more options to set with > the main one being the innodb_buffer_pool_size which is used for caching > keys and data, you want to set this as large as possible. You also have > several other adjustable buffers inlcuing an 'additonal_mem_pool' which > I'm not quite sure what it is used for, and the log_buffer which is used > for transaction related memory I believe. > > So, if you are going to be using both MyISAM and InnoDB you will need > seperate buffers, which will of course increase total memory usage, or > leave a smaller size for both. But if you switch completely to InnoDB > you can drop the MyISAM buffers down to almost nothing (still need them > as the mysql table with user data etc uses them, but say 8megs would be > plenty). > > John > > On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: > > > > > > Hello John, > > > > Interesting post, quite useful, > > Question about performance with InnoDB ? > > say you have a hosting server with 256 Mb of ram, would you know if > > that will make a difference if the major database is converted from > > MyIsam to InnoDb ? > > > > Although, InnoDB is not a requirement, just luxury, but I would love > > to enjoy foreign keys and transactions > > > > Please advise, > > Thanks > > > > > > Sunday, October 10, 2004, 8:39:15 AM, you wrote: > > > > JM> I meant 'No transaction support', which is you can't use > > JM> begin work; ... ; commit; etc to perform transactions, each query > > JM> takes effect immeiately and is visible to all other > > JM> threads/clients immediately. > > ... > > > > JM> Concurrency refers to multiple seperate connections (threads) > > JM> trying to read/write to/from the same table at the same time. > > JM> Imagine you have 100 different connections to the database all > > JM> trying to write to the same table. With MyISAM each one will lock > > JM> the entire table, and only one will execute at a time, making it > > JM> very slow. In InnoDB each one will only lock the rows it is > > JM> modifying and they can all execute at once (if they are not > > JM> modifying the same rows), and it will be very fast. > > > > > > > > > > Best regards, > > Jacques Jocelyn > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on RedHat ES 3.0
Yup. On Mon, 11 Oct 2004 11:33:50 -0400, Ferguson, Michael <[EMAIL PROTECTED]> wrote: > Thanks. > Here is what it got after I ran your command. It seems that mysql and > php is already installed. Right??? > > [EMAIL PROTECTED] root]# rpm -qa | grep -i mysql > libdbi-dbd-mysql-0.6.5-5 > mysql-3.23.58-1 > perl-DBD-MySQL-2.1021-3 > mysql-devel-3.23.58-1 > mod_auth_mysql-20030510-1.ent > php-mysql-4.3.2-8.ent > MySQL-python-0.9.1-6 > > > > -Original Message- > From: Benjamin Arai [mailto:[EMAIL PROTECTED] > Sent: Monday, October 11, 2004 11:29 AM > To: Ferguson, Michael > Cc: [EMAIL PROTECTED] > Subject: Re: MySQL on RedHat ES 3.0 > > rpm -qa | grep -i mysql > > On Mon, 11 Oct 2004 09:12:54 -0400, Ferguson, Michael > <[EMAIL PROTECTED]> wrote: > > G'Day All, > > > > I successfully installed RedHat ES 3.0 and would like to get MySQL > > installed on it. After reading the online manual at > > dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit > > unsure of my next move. Can some please help me out with directions on > > > how to check the system to verify whether or not MySQL is installed, > > and how to download and install MySQL on this server. > > > > Many thanks and best wishes. > > > > Ferg. > > > > > > -- > Benjamin Arai > http://www.cs.ucr.edu/~barai > [EMAIL PROTECTED] > -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read-Only DB User
Run "SELECT * FROM user;" in the mysql database. All of the options are obvious. On Mon, 11 Oct 2004 19:28:49 +0530, Anil Doppalapudi <[EMAIL PROTECTED]> wrote: > First connect to mysql as root user and issue the following command > > grant select on .* to @ identified by > ''; > flush privileges; > > it will grant only select privilege to the newly created user on database > and he can only connect from the ipaddress specified in command > > Anil > DBA > > -Original Message- > From: Lee Zelyck [mailto:[EMAIL PROTECTED] > Sent: Monday, October 11, 2004 7:30 AM > To: [EMAIL PROTECTED] > Subject: Read-Only DB User > > Hi All, >I'm sorry to access such a basic question, but I > couldn't find a specific answer to it in the mysql > manual pages. > >The question is, how would someone create a basic > read-only user for a single db? I just intend for it > to be used by a script to validate data in the db > itself. > >Anyway, if anyone can provide a lean and concise > statement that will provide this, it would be very > much appreciated. > > Thanks! > Lee > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on RedHat ES 3.0
rpm -qa | grep -i mysql On Mon, 11 Oct 2004 09:12:54 -0400, Ferguson, Michael <[EMAIL PROTECTED]> wrote: > G'Day All, > > I successfully installed RedHat ES 3.0 and would like to get MySQL > installed on it. After reading the online manual at > dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit > unsure of my next move. > Can some please help me out with directions on how to check the system > to verify whether or not MySQL is installed, and how to download and > install MySQL on this server. > > Many thanks and best wishes. > > Ferg. > > -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Determine the Server's Version on Old Server?
mysqladmin version On Mon, 11 Oct 2004 10:36:54 -0400, Michael Stassen <[EMAIL PROTECTED]> wrote: > You should keep threads on the list. That way, more people can help, and > more can benefit from the answers. > > I've not looked at the code behind mysql_get_server_info(), but every > version of mysql I've seen has 3 parts to the version number. It seems > clear that the mysql version numbering scheme is release.version, where > release is 3.23, 4.0, 4.1, or 5.0, and version is sequential. Assuming > you'll always get a 3 part version seems safe to me. > > Michael > > Matthew Boehm wrote: > > >>Will mysql_get_server_info() give you what you need? > >><http://dev.mysql.com/doc/mysql/en/mysql_get_server_info.html> > > > > > > I guess I could use that and parse out on the . separator. > > > > Will I always get a 3 . separated string? ie: X.XX.XX ? Or could I sometimes > > get X.XX? > > > > Thanks, > > Matthew > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock tables
You only need to lock whene you are going to run a query that contains a series of actions and they all have to happen at the same time. As for single queries, they are already atomic, so you don't need to put and locks around them. On Mon, 11 Oct 2004 11:14:36 +0100, Melanie Courtot <[EMAIL PROTECTED]> wrote: > Hi, > I'm a bit confused by the lock mechanism under mysql. > When user A does an update on table 1, the table is automatically locked > by mysql?that means at the same time user B won't be able to modify the > same row? > Or do I have to specify the lock for each query? > And what about temporary tables? > If anybody has a simple explanation or a link on a doc > thanks, > Melanie > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Website site Database (Project)
What do you mean? On Sun, 10 Oct 2004 23:07:59 -0700 (PDT), John <[EMAIL PROTECTED]> wrote: > > > I wanted to know if this is a good place to post > for a project I needed done, If not can someone direct > me to a better place to post it. > > Thanks > > ___ > Do you Yahoo!? > Declare Yourself - Register online to vote today! > http://vote.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup
Don't use rsync. Try rdiff-backup, its much more reliable and offers rolling restoration. On Sat, 2004-04-10 at 02:08, Matt W wrote: > Hi Steve, > > You might want to look at FLUSH TABLES WITH READ LOCK. That's a query > to run from mysql, but I'm sure you can get it to work in your shell > script (you need to maintain the MySQL connection while doing the > backup). I don't know much about that, though. I think you just run > UNLOCK TABLES when you're finished. > > > Matt > > > - Original Message - > From: "Steve Sills" > Sent: Tuesday, April 06, 2004 8:17 PM > Subject: backup > > > I want to use rsync to backup my db server, how do i lock all the tables > for all the db's to read only so i cando my backup, then unlock them > again. It needs to be done from the command line, not the mysql > program. Anyone have any ideas? I have looked and couldn't find the > answer i was looking before. Its running from a shell script, from my > backup machine. Its currently setup to shut down the server, however i > don't want to have to do this. Thanks in advance. > > Steve Sills > Platnum Computers, President > http://www.platnum.com [EMAIL PROTECTED] Benjamin Arai Araisoft Email: [EMAIL PROTECTED] Website: http://www.araisoft.com
Re: MySQL on Linux
Just to be complete, linux does have limitations depending upon limitations of the file-system, and the kernel. All modern filesystems (XFS, EXT3, ...) all allow files over a terabyte is size. On Tue, 2004-04-06 at 13:39, Ronan Lucio wrote: > > Uhm, > > what are you talking about?!? > > When I put our site on a Linux system, apache stop working when > it´s logfile get major than 2 Gb. > > I was afraid of it´d happen with MySQL, too. > > > Linux has no such limitation. > > you can grow files as large as you like. > > right now I have an InnoDB dbase with Mysql on a linux > > system and the file is over 60 GIGS in size! > > Great!!! So, I don´t need to worry about it... :-) > > Thanks Dan, > Ronan Benjamin Arai Araisoft Email: [EMAIL PROTECTED] Website: http://www.araisoft.com
Simple question : Find older CHILD for each PARENT
Hi, I have a simple problem that I don't know how to solve with mysql. I have 2 tables, a parent table and a child table. parent: --- | p_id | name | --- | 1| A | | 2| B | | ... |... | | 112 | C | | 113 | D | --- child: -- | c_id | p_id | name | age | -- | 1|1 | BP | 15 | | 2|56 | AW | 12 | | ... | ... | GH | 19 | |299|2 | RT | 14 | |300|56 | FG | 18 | -- I want to get a list of all the parents with the age and the name of the older child they've got. Let's say that a parent can't have 2 children with the same age. I can solve my problem by using multiple queries but that's not very clean and a bit heavy (especially if there are lots of parents). Any idea? Thank you Benjamin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN 10 times quicker than LEFT JOIN on big tables and simple queries?
Here is my problem: I have 2 tables, a parent table and a child table. The parent table has got 113 rows, the child table has got 3 000 000 rows. parent: --- | p_id | name | --- | 1| A | | 2| B | | ... |... | | 112 | C | | 113 | D | --- child: -- | c_id | p_id | -- | 1|1 | | 2|56 | | ... | ... | |299|2 | |300|56 | -- I want to get a list of all the parents (even the parents without child) with the number of children they've got. I use a LEFT JOIN in order to retrieve all the parents without exception : SELECT parent.p_id, COUNT(child.c_id) FROM parent LEFT JOIN child ON (parent. p_id = child.p_id) GROUP BY parent.p_id; This query takes 140 seconds to be executed and I got 70 results. Now if I use a basic JOIN like that: SELECT parent.p_id, COUNT(child.c_id) FROM parent JOIN child ON (parent.p_id = child.p_id) GROUP BY parent.p_id; The query takes now 13 seconds to finish!! But now I got only 67 results because the basic JOIN does not include the parents without children. What I don't understand is why the JOIN is far much quicker than the LEFT JOIN whereas the only difference is that the LEFT JOIN includes the parents without children? Any explanations? Here are the EXPLAIN for the 2 cases : LEFT JOIN case : --- table type possible_keys key key_len ref rows Extra parent index NULL PRIMARY 4 NULL113 Using index child refp_id p_id 5 parent.p_id 40694 --- JOIN case: --- table type possible_keys key key_len refrowsExtra child ALLp_id NULLNULL NULL 300 Using temporary; Using filesort parent eq_ref PRIMARYPRIMARY 4child.p_id 1 Using index --- I'm using MySQL 4.0.13 and MyISAM tables. I'm using keys and Indexes. Thank you very much. Benjamin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows to Linux
I do this all the time at my work for really large tables. Just scp, ftp the entire data directory over to the linux box and restart the MySQL service. The only addition thing you might want to do is compress the file before sending them just so you have some way to check them and tables usually compress very well. On Tue, 2003-11-04 at 11:21, Matt Babineau wrote: > Well, he could do that but being inexperienced with Linux, I figured it > would be more beneficial to use a familiar WYSIWYG so he doesn't blow > and hour playing the with CLI like I did :) > > On Tue, 2003-11-04 at 16:58, Big Brother wrote: > > err why not do a mysqldump then just import that? > > > > --- > > > > > > Quoting Matt Babineau <[EMAIL PROTECTED]>: > > > > > Check out SQLYog, could can connect and copy databases...pretty much > > > like MSSQL Enterprise manager. They have a trial version on their site: > > > http://www.webyog.com/sqlyog > > > > > > > > > > > > On Mon, 2003-11-03 at 19:51, Matt Fletcher wrote: > > > > Hi there, > > > > I have taken the plunge and dropped windows in favour of linux. My > > > > question is what is the best way to get the data from my windows mysql > > > > databases into linux? Can I just copy some files from one partition to > > > > another or what? > > > > > > > > Thanks, > > > > > > > > Matt > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > -- Benjamin Arai <[EMAIL PROTECTED]> Araisoft Corp.
mysql memory usage
hi everyone. i'd like to know if mysql always uses all the key_buffer size it has been given in my.cnf especially, on my server with : set-variable= thread_stack=128K set-variable= key_buffer=200M set-variable= max_allowed_packet=1M set-variable= table_cache=128 set-variable= sort_buffer=4M set-variable= net_buffer_length=8K top shows this : 21:39:49 up 134 days, 3:00, 1 user, load average: 21.82, 24.32, 21.84 Tasks: 375 total, 20 running, 355 sleeping, 0 stopped, 0 zombie Cpu(s): 83.3% user, 16.7% system, 0.0% nice, 0.0% idle Mem:901156k total, 823388k used,77768k free, 6360k buffers Swap: 1951888k total, 3376k used, 1948512k free, 711876k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 18598 mysql 20 0 54524 53m 2300 R 10.0 6.1 1:35.66 mysqld (329 lines like this one, except for the %CPU column) obviously , my server (dual pIII 1Ghz) is a bit exhausted. but my point is : mysql is threaded, so i guess the whole mysql size is 54524 (swap+physical). why doesn't mysql use the key_buffer size ? mysqladmin variables show the good key_buffer_size value. mytop says my key efficiency is 99.54%. maybe it has something to do with the database files size? bye. * Benjamin KRIEF * Directeur Technique * IGUANE Studio * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication redirector
hi Jeremy , thanks (again, tell me if it's to much=) for helping me. i've got to admit here that i'm reaching the maximum of my linux skills. i've used top,free,iostat and sar , without finding the answers i need. for instance , on the master (dual P3 1Ghz , 1G ram, running only mysql, with 353Mo of data): uptime : -- 18:46:44 up 123 days, 23:07, 1 user, load average: 21.20, 14.22, 13.93 free : totalusedfree shared buffers cached Mem:901156 890080 11076 05860 731076 -/+ buffers/cache: 153144 748012 Swap: 1951888 226356 1725532 top : -- 19:02:26 up 24 days, 17:54, 1 user, load average: 2.12, 3.19, 2.84 418 processes: 416 sleeping, 2 running, 0 zombie, 0 stopped CPU states: 58.1% user, 7.0% system, 0.0% nice, 34.9% idle Mem:900084K total, 884928K used,15156K free,70076K buffers Swap: 979956K total, 243256K used, 736700K free, 341272K cached mytop (thanks again =) : -- MySQL on localhost (3.23.49-log) up 29+10:33:22 [18:48:43] Queries Total: 13,593,786 Avg/Sec: 5.34 Now/Sec: 8.54 Slow: 189,308 Threads Total: 356 Active: 27Cached: 0 Key Efficiency: 99.93% Bytes in: 1,328,945,210 Bytes out: 940,236,299 my.cnf extract: -- set-variable= thread_stack=128K set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=128 set-variable= sort_buffer=1M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_connections=500 thanks to top , i can presume that the cpu's are ok :34.9% idle (there's a "bug" in the debian procps , top show only one proc , but cat /proc/cpuinfo show me the 2 cpu , so they are both effectively used by the system) i'm asking myself : why is the kernel using so much cache (730M). shouldn't he (AFAIK) gives this space to mysql instead of swapping (226M) ? then i think maybe he uses the cache to manage the swap space. but heh , 730M for cache , and only 153 for the applications ! where am i wrong? (let's stay humble : i'm wrong , not the linux kernel :) do you think there is some simple things i could tune to improve the system performance ? (the slave is now ok , thanks to the horrible regexps system i use to redirect part of the queries to the master =) bye, Ben. ***** Benjamin KRIEF * Directeur Technique * IGUANE Studio Tel:01.56.55.54.20 * 5-7-9 passage des Cloys Fax:01.56.55.54.24* 75018 PARIS Gsm:06.12.56.50.41 * mailto:[EMAIL PROTECTED] ***** > On Fri, Oct 24, 2003 at 04:00:07AM +0200, Benjamin KRIEF wrote: >> > >> > Is your bottleneck CPU, I/O, or lock contention? >> > >> > Is your slave running on worse hardware than your master? >> >> my bottleneck is CPU , definitely. the load can go up to 35 in peak >> hours. >> no swapping occurs , my key efficiency is at 99,89 . > > The load average often has little relationship to the actual CPU > utilization. Is your CPU at 100% most of the time? Or are your procs > blocked on disk I/O. > > Jeremy > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,530,693,767 queries > (439/sec. avg) > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication redirector
hi , and thanks for your answer, > On Mon, Oct 20, 2003 at 07:03:37PM +0200, Benjamin KRIEF wrote: >> the problem is that it's quite impossible to fine tune the load between >> the servers , and my slave is a bit too busy , so the replication thread >> is becoming very slow , and sometimes , i can see that the slave is more >> than 2 hours behind the master in terms of replication , and this really >> is a pb for me. > > Two hours?!? Wow. > as you say =) >> i looked everywhere for a "high-priority-updates" option, but i can't >> find >> it.tell me if i'm wrong but i deduced that i can't ask the slave to wait >> until it reached the master before answering selects. > > Is your bottleneck CPU, I/O, or lock contention? > > Is your slave running on worse hardware than your master? my bottleneck is CPU , definitely. the load can go up to 35 in peak hours. no swapping occurs , my key efficiency is at 99,89 . and yes , my slave hardware is worse than my master : master= 2xPIII 1Ghz , 1 Go ram slave = 1 PIV 2,4Ghz, 1Go ram i also have to admit that the webserver is on the slave :( >> so i've started using more regexps to redirect the queries , but >> it's quite awful to my eyes. i would really like to input the ratio >> of "select" queries i'd like to send on the slave . > > Ouch. That's clearly not a good solution. > >> what about , instead of round robin dns , writing a small perl script , >> including a socket listening on the port 3306 , this small script would >> have to redirect the sql requests on the slave or on the master , but >> this >> time , i could fine tune it , and input for instance 0,3 for the slave >> , >> so that 3 requests out of 10 would go to the slave. > > Have you looked at SQL Relay? thanks for that one . it appears to me that it's exactly what i was looking for. bye * Benjamin KRIEF * Directeur Technique * IGUANE Studio Tel:01.56.55.54.20 * 5-7-9 passage des Cloys Fax:01.56.55.54.24* 75018 PARIS Gsm:06.12.56.50.41 * mailto:[EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication redirector
hi guys, i've just finished implementing replication on 2 production servers. i'm using round robin dns to balance the load between the slave & the master. as you would presume , the master handle all the updates, and 1/2 of the selects , the slave handle the other 1/2 of the selects. the selection of the server is made in the php code using regexp matching "^SELECT" , if one request start by "SELECT" , it is redirected to select.mydomain.com. in my dns zone file , i've got two entries for select.mydomain.com , one for each server. the problem is that it's quite impossible to fine tune the load between the servers , and my slave is a bit too busy , so the replication thread is becoming very slow , and sometimes , i can see that the slave is more than 2 hours behind the master in terms of replication , and this really is a pb for me. i looked everywhere for a "high-priority-updates" option, but i can't find it.tell me if i'm wrong but i deduced that i can't ask the slave to wait until it reached the master before answering selects. so i've started using more regexps to redirect the queries , but it's quite awful to my eyes. i would really like to input the ratio of "select" queries i'd like to send on the slave . what about , instead of round robin dns , writing a small perl script , including a socket listening on the port 3306 , this small script would have to redirect the sql requests on the slave or on the master , but this time , i could fine tune it , and input for instance 0,3 for the slave , so that 3 requests out of 10 would go to the slave. maybe stg similar already exists ? thanks for your attention, * Benjamin KRIEF * Directeur Technique * IGUANE Studio Tel:01.56.55.54.20 * 5-7-9 passage des Cloys Fax:01.56.55.54.24* 75018 PARIS Gsm:06.12.56.50.41 * mailto:[EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimization headaches.
hi all , and thanx for your help on my weird_indexes problem. no that i've created the indexes , the things are going a bit better but it's still not so fast : here's some output from mytop : - Queries Total: 442,666Avg/Sec: 7.74 Now/Sec: 16.27 Slow:36,449 Threads Total: 212 Active: 65Cached: 0 Key Efficiency: 99.99% Bytes in: 53,284,729 Bytes out: 178,102,128 - after this, mytop lists 65 requests , from which 30 are here for more than 20 seconds !! the longer one is at 64 seconds. you've guessed it : it's hard to stand. the load of the server (dual pIV , with 1Go ram and scsi disks (no raid) is now 20,23,30. my.cnf includes this : - set-variable= thread_stack=128K set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=128 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_connections=500 - i take the risk to say that it's not a disk pb , iostat looks good , and my key efficiency is near 100% wich AFAIK means that my keys are read from memory , and not from disk. mysqldumpslow gave me the top 3 hard queries , and all those queries are using the indexes created yesterday (i analyzed and optimized the tables , and "explained" the selects) : - Count: 596 Time=28.77s (17145s) Lock=13.57s (8085s) Rows=8.3 (4959), [EMAIL PROTECTED] SELECT more, pays, date, nbvote, id, sexe, login, age, poid, taille, region, v_ann FROM v2easy1_users WHERE nblogin>'S' AND moderation='S' AND sexe='S' AND age>='S' AND age<='S' AND pays='S' AND region='S' AND pix='S' ORDER BY lastlogin DESC LIMIT N,N Count: 592 Time=28.98s (17155s) Lock=13.32s (7885s) Rows=9.3 (5534), [EMAIL PROTECTED] SELECT more, pays, date, nbvote, id, sexe, login, age, poid, taille, region, v_ann FROM v2easy1_users WHERE nblogin>'S' AND moderation='S' AND sexe='S' AND age>='S' AND age<='S' AND pays='S' AND region='S' ORDER BY lastlogin DESC LIMIT N,N Count: 566 Time=32.72s (18521s) Lock=12.50s (7073s) Rows=10.0 (5660), [EMAIL PROTECTED] SELECT id, login, nbvoteweek, age, region, sexe FROM v2easy0_users WHERE nblogin>'S' AND sexe='S' AND pix='S' AND moderation='S' ORDER by nbvoteweek DESC, date LIMIT N, N - i'm almost ready to assume that whatever i could do in optimization , i'll never be able to drastically reduce the amount of slow query , and that my only ways now are php-caching , or a new server =( if someone have got a clue on something i could try to improve this , i'd be grateful !! bye. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spaces in index name
thanks for all your answers ! i'd never imagine that mailing-list to be so active and efficient! matt : does mysql locks the table during the whole alter table execution? Matt W wrote: Hi Ben, When using column/index names with odd characters (such as spaces), you need to use backticks (SHIFT + the ~ key) around the name: `index id` This ALTER TABLE query should make all the changes you want: ALTER TABLE v2easy0_users DROP INDEX `login connexion`, DROP INDEX `index id`, -- Drop the PRIMARY KEY with 2 id columns DROP PRIMARY KEY, -- And re-add it with just one column ADD PRIMARY KEY (id); Hope that helps. Matt - Original Message - From: "Benjamin KRIEF" Sent: Sunday, August 31, 2003 7:40 PM Subject: spaces in index name hi all , i'm trying to improve performance of a quite big and heavily used mysql set of tables. i want to create some indexes on this table , but before this , i'd like to remove the ones created by my predecessor , which are sometimes duplicate , and somtimes useless. here is an output from show index : -+---++---+--+ -+ C| Table | Non_unique | Key_name | Seq_in_index | Column_name | -+---++---+--+ -+ A| v2easy0_users | 0 | PRIMARY |1 | id | A| v2easy0_users | 0 | PRIMARY |2 | id | A| v2easy0_users | 0 | login |1 | login | A| v2easy0_users | 0 | id|1 | date| A| v2easy0_users | 0 | id|2 | id | A| v2easy0_users | 0 | id|3 | login | A| v2easy0_users | 1 | login connexion |1 | login | A| v2easy0_users | 1 | index id |1 | id | first of all , i'm asking myself : why is there 2 primary keys on the same column ? i'd want to remove the second index , but since it has the same name than the first , how would i do something like that? then , i'd like to remove the index "login connexion" , and the index "index id" , but the problem is : mysql> drop index login connexion on v2easy0_users; ERROR 1064: You have an error in your SQL syntax near 'connexion on v2easy0_users' at line 1 (the same goes with ' and " around the index name) ! of course , mysql doesn't accept spaces in index names , so why did he accepted it on creation ? it seems that i can't remove those index , which are never used by the optimizer (i used explain) , and which slow down my insert and updates... should i dump then recreate this table? i'm a bit stuck here , thanks for your help , and sorry for my english ... ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spaces in index name
hi all , i'm trying to improve performance of a quite big and heavily used mysql set of tables. i want to create some indexes on this table , but before this , i'd like to remove the ones created by my predecessor , which are sometimes duplicate , and somtimes useless. here is an output from show index : -+---++---+--+-+ C| Table | Non_unique | Key_name | Seq_in_index | Column_name | -+---++---+--+-+ A| v2easy0_users | 0 | PRIMARY |1 | id | A| v2easy0_users | 0 | PRIMARY |2 | id | A| v2easy0_users | 0 | login |1 | login | A| v2easy0_users | 0 | id|1 | date| A| v2easy0_users | 0 | id|2 | id | A| v2easy0_users | 0 | id|3 | login | A| v2easy0_users | 1 | login connexion |1 | login | A| v2easy0_users | 1 | index id |1 | id | first of all , i'm asking myself : why is there 2 primary keys on the same column ? i'd want to remove the second index , but since it has the same name than the first , how would i do something like that? then , i'd like to remove the index "login connexion" , and the index "index id" , but the problem is : mysql> drop index login connexion on v2easy0_users; ERROR 1064: You have an error in your SQL syntax near 'connexion on v2easy0_users' at line 1 (the same goes with ' and " around the index name) ! of course , mysql doesn't accept spaces in index names , so why did he accepted it on creation ? it seems that i can't remove those index , which are never used by the optimizer (i used explain) , and which slow down my insert and updates... should i dump then recreate this table? i'm a bit stuck here , thanks for your help , and sorry for my english ... ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: inserting images to mysql - is it possible?
check out http://www.phpbuilder.com thay have a lot of things and that is one off them. -- eschew obfuscation. -Original Message- From: Digital Directory USA [mailto:[EMAIL PROTECTED] Sent: 29 June 2003 19:28 To: [EMAIL PROTECTED] Subject: inserting images to mysql - is it possible? I am new to php and mysql, I have searched looked over and cannot find a way to do this... can it be done? I have set up the following in mysql. How do I upload the product images to the pPhoto column. Any suggestions? ds_produc Field Type Attributes Null Default Extra pID int(11)Noauto_increment catID int(11)No 0 pName varchar(70)Yes NULL pNum varchar(40)Yes NULL pTeaser textNo pDesc textYes NULL pPrice float(10,2)Yes NULL pSalesPrice float(10,2)No 0.00 pSale char(1)No pPhoto varchar(70)Yes NULL pPhotoWidth int(11)No 0 pPhotoHeight int(11)No 0 pSize varchar(35)Yes NULL pAvail char(1)No Y George Guebely Digital Directory USA, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to do multipul Joins
Hello all I am having trouble with joining tables i will explain. I have four tables Cat - CatFile - File - User. thay are related in the order. I need to join them around the file table. i tryed 'SELECT cat.name, catfile.catid, file.fileid, user.name FROM cat RIGHT JOIN catfile ON catfile.catid=cat.catid RIGHT JOIN file ON file.fileid=catfile.fileid LEFT JOIN user ON file.userid=user.userid' i get a list as long as my arm most of which are not what i am trying to get -- eschew obfuscation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting !?!
On Wed 2003-04-02 at 13:32:22 +0300, [EMAIL PROTECTED] wrote: > Hello mysql, > We make updade of database from 3.23.49 to 4.0.12 > Before update we can see host of connections /see Example1/. > After update every connections looks like they made from local host > /see Example2/, but they did not. > Any explanation of effect?!? A bug. It is listed as fixed in the change history for the next (not yet released) version 4.0.13 in the online manual. HTH, Benjamin. -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why don't ISPs use v4
Hi.
On Mon 2003-03-31 at 21:20:25 +0100, [EMAIL PROTECTED] wrote:
> Sorry everybody. I assumed that as it had been out for a long time (2
> years?) it was stable.
It's been 1 1/2 years (Oct 2001). But that was an alpha release. The
open development model of MySQL screws numbers a bit. The first beta
release was Aug 2002. So in real, it was about 7 month since feature
freeze. The release dates are part of
http://www.mysql.com/doc/en/News.html
For more info on the release designation (alpha, beta, gamme,
production), have a look at
http://www.mysql.com/doc/en/Which_version.html
(especially the second half)
HTH,
Benjamin.
--
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible Bug: Dropping Trailing White Space
On Thu 2003-03-20 at 14:01:52 -0500, [EMAIL PROTECTED] wrote: > I have a table with a column defined as the following. > > hash CHAR(16) BINARY NOT NULL > > Most data inserts fine. However, if data has trailing white space > (ASCII character 32), it seems to be getting truncated by MySQL during > the insert, such that subsequent queries to find the values fail. Full > example below. > > Any Ideas? That is the documented behaviour (http://www.mysql.com/doc/en/CHAR.html) and is an (implementation dependend) feature of SQL. If you don't want it, you had to use VARCHAR instead. But there is a known bug with MySQL (see above and http://www.mysql.com/doc/en/Open_bugs.html). As the bug description implies, use a TEXT type like TINYTEXT instead. HTH, Benjamin. PS: Btw, the BINARY keyword only influences sort behaviour, nothing else. -- [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: 4.0.12 startup problem InnoDB related
On Thu 2003-03-20 at 15:59:29 +0100, [EMAIL PROTECTED] wrote: > Hi, > > I was upgrading from 4.0.10 to 4.0.12 when a strange thing happened. I > compiled from source on SuSE-8.0 (gcc version 2.95.3, kernel 2.4.18-4GB) > and installed and everything was fine. However I noticed that I had > debugging compiled in. > > I went back, took out --with-debug from my configure options and > recompiled. The resulting mysqld did no longer start up but quit with the > following message in error.log: > > 030320 09:02:00 mysqld started > InnoDB: Error: trx_t size is 416 in ha_innodb.cc but 456 in srv0start.c > InnoDB: Check that pthread_mutex_t is defined in the same way in these > InnoDB: compilation modules. Cannot continue. > 030320 9:02:00 Can't init databases > 030320 9:02:00 Aborting > > How strange; I deleted all InnoDB files in the data directory. Same > result. Then I recompiled again adding the option --with-debug and it > started ok creating all necessary files. > > What is wrong here? Such a config change requires to do a full recompile (make distclean or whatever), which you apparently did not. HTH, 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: Index on a (homemade) SET?
Hi. On Thu 2003-03-20 at 15:52:25 +0100, [EMAIL PROTECTED] wrote: > > I use a lot of SET-alike fields in my tables. The fields are used to store > properties and such; every bit stands for a specific value. The fields are > queries like 'where property_field & 1<<4' or 'where property_field & 1025' > if you look for more than one property. > > Just your average SET behavoir and very convenient to use. > > > The only problem is these queries are slow. You need to do a tablescan to > find the matching records. Making a seperate table where you store an entry per > property is another option but even slower (makes a big difference after > even a few tens of thousend of rows). > > Any hint / tips / ideas how to index a set-like field? Maybe you should start by explaining why you don't use SETs? Whatever the reason is, I strongly suspect that it will influence the answer. All what you described works fine with SETs and there is a chance that MySQL will optimize accesses to them better. 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: Embedded MySQL?
Hi. On Thu 2003-03-20 at 08:25:08 +0200, [EMAIL PROTECTED] wrote: > Hi, I have a question I hope you can help me with. > > I try to develop a database in Borlands Delphi or with C++ and Visual Studio > 6 (Windows 2000). Probably I choose Delphi. > Now I want to use MySQL as a platform for my program. According to > http://www.mysql.com/doc/en/libmysqld_overview.html > I can run a standalone server in my program. Now I wonder how this works in > practise? > > Ideally I want to call an API in a dll-file to create and maintain the > database. http://www.mysql.com/doc/en/libmysqld.html should answer most of your concern. Basically it boils down to an additional call to mysql_server_init() and mysql_server_end() and compiling with libmysqld instead of libmysqlclient. The rest is the same with the client library. HTH, 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: converting an existing column to auto increment
Hello. On Mon 2003-03-17 at 18:08:08 -0600, [EMAIL PROTECTED] wrote: > I have an table with a column > > numberINT NOT NULL PRIMARY KEY > > This table contains many records and there are gaps in the number > sequence. I would like to modify this column to use the AUTO INCREMENT > feature but I need to preserve the present numbering sequence (which is > monotonically increasing) including the holes. It is not clear from the > manual or my MySQL book exactly how to do this. > > Is this possible? if so what is the correct ALTER TABLE command? Yes. First, make a backup of your tables. Although I don't expect any problems: Better safe than sorry. AUTO_INCREMENT columns won't reuse gaps. If you add a new value, it will be at least MAX(value)+1. Could be higher, if you deleted some rows in-between. The command is ALTER TABLE your_table MODIFY number INT NOT NULL AUTO_INCREMENT PRIMARY KEY Done. Note that if your column wouldn't be NOT NULL already and indeed contain some NULL values, I would expect them to be handled like a NULL for the AUTO_INCREMENT in a normal INSERT, i.e. it would get MAX(value)+1 (and so on for all other rows where number<=>NULL). As I said, this is not an issue in your current case. HTH, 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: automatically incrementing an int value
On Wed 2003-03-12 at 11:16:09 -0600, [EMAIL PROTECTED] wrote: > At 11:41 -0500 3/12/03, Douglas B. Jones wrote: > >Hi, > > > >I understood replace to only increment n when it matches the > >name value. In that case, you want a primary key on (name,n) with n being auto_increment. > There are 122,111 statements, but when you add > >up the numbers in the n column, they exceed 122,111. They should > > I don't know what you're trying to say here. If you *add up* the > numbers in the column, of course they will exceed the number of records. I think he expects one row for each name, with the n being the (original) number of rows which had that name. If I am correct, another way to reach the same goal is to simply insert all rows as (without unique key on name, only a normal key) and then do a SELECT name, COUNT(*) FROM virus GROUP BY name; This has the advantage that you, Douglas, can keep timestamps and so on which enables better analysis afterwards. If you are not interested in other data, but only the one-time analysis you presented, why (mis-)use a database at all? Simple do something like $ sort < file_with_names | uniq -c in the shell (I presume you have shell access because you used grep before). HTH, 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: When is MySQL 4.1 going to have its binary download available ?
Hi.
On Wed 2003-03-12 at 14:39:26 +0200, [EMAIL PROTECTED] wrote:
> Hi,
> I do not know how to compile, but I sure want to try the new features of
> 4.1.
There is no date yet for the first binary release of 4.1. They will
publish such an alpha release when they consider it mature enough for
general public testing. So, in a way, just wait with testing until
they invite by publishing it.
HTH,
Benjamin.
PS: In case you are not aware of it: There is an own section how to
get and compile the 4.1 source version:
http://www.mysql.com/doc/en/Installing_source_tree.html
--
[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: problems with GRANT, user, databases
Hi!
On Mon 2003-03-10 at 16:44:40 -0500, [EMAIL PROTECTED] wrote:
[...]
> - Given a system user 'junk' I would like to create a "master" user
> 'junk' that can have all permissions (including grant?) on all 'junk_*'
> databases so that he can create and manage his own databases
>
> - Given a "master" user 'junk' I would like for that user to be able to
> create other users 'junkNN' or or 'junk-*' or whatever so that my good
> friend junk is not always acting as "root".
>
> I have tried
>
> mysql> grant all privileges on junk_* to [EMAIL PROTECTED] identified by 'junk';
> ERROR 1064: You have an error in your SQL syntax near '* to [EMAIL PROTECTED]
> identified by 'junk'' at line 1
> mysql> grant all privileges on junk_.* to [EMAIL PROTECTED] identified by 'junk';
> Query OK, 0 rows affected (0.24 sec)
>
> and so I guess the way to specify database matching is with pure regexp
> notation of '.*' for 'any character any number of times',
No, it does not use REGEXPs at all. The second statement was
interpreted as 'databasename_'.'*', i.e. '.' is the seperator between
database name and table name. Here, '*' is not really a joker
(although it has some of its effects), but simply a placeholder
specific to the GRANT command. That means you have granted user junk
access to all tables within the database 'junk_' (where '_' is a joker
meaning any single char, like '.' in REGEXP).
I am not sure from my mind whether SQL jokers ('%') work within GRANT,
but if they do, the first statement should have looked something like
that:
GRANT ALL PRIVILEGES ON 'junk\\_%'.* TO [EMAIL PROTECTED] IDENTIFIED BY 'junk';
(maybe more or less backslashes needed)
> mysql> select * from mysql.db where user = 'junk' ;
> | localhost | junk_ | junk | Y | Y | Y | Y
> | Y | Y | N | Y | Y | Y |
> mysql> select * from mysql.user where user = 'junk' ;
> | localhost | junk | 372b3ff6545565e4 | N | N | N |
> N | N | N | N | N | N|
> N | N | N | N | N |
[...]
> but I can't create a database
>
> mysql> create database junk_testing ;
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_testing'
Aside from the name mismatch, creating databases is a global
privilege, i.e. database privileges are not evaluated for a
not-yet-existing database. Someone correct me, if I am wrong.
> bash-2.05a$ mysql -ujunk -pjunk
> Welcome to the MySQL monitor. Commands end with ; or \g.
> mysql> use junk_test ;
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_test'
Jupp, see above.
> Maybe I have to re-grant, too. Grrr...
No, grants are evaluated when they are needed, not before. I.e. a
database/table whatever has not to exist at GRANT-time for the
privileges to work later.
> OK, now I'm just mad :-) Let's try granting to this specific database
> just to get things going.
>
> bash-2.05a$ mysql -u droot -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> mysql> grant all privileges on junk_test to junk ;
> ERROR 1046: No Database Selected
> mysql> use mysql ;
> Database changed
That should have got you thinking. :-)
Of course, the grant command implicitly changes the mysql tables, but
you don't have to use them in order to use GRANT. GRANT is an
abstract, generic interace to whatever privilege system an RDBMS has.
I bet the error you get is due to 'junk_test' having no database
specifier ("junk_test" instead of "junk_test.*" and therefore MySQL is
trying to use junk_test as table name with the default database
currently used. I.e. I guess you just changed access rights for
"mysql.junk_test". Try to see what mysql.db contains now...
> mysql> grant all privileges on junk_test to junk ;
> Query OK, 0 rows affected (0.02 sec)
> mysql> quit
> Bye
> bash-2.05a$ mysql -ujunk -pjunk
> Welcome to the MySQL monitor. Commands end with ; or \g.
> mysql> use junk_test ;
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_test'
... and it would explain that error perfectly. Btw, there is a nice
tool, mysqlaccess, which tells you a bit about the privileges a user
has.
> Not onl
Re: Searching on indexed char field...
On Mon 2003-03-10 at 15:06:05 -0500, [EMAIL PROTECTED] wrote:
> >
> > If you're indexing all 50 characters, index fewer of them.
>
> Not that I think you're wrong, but help me understand, please:
>
> It seems to me that this would cause the index creation to go faster,
> but the execution of the SELECT query to, if anything, go slower...?
>
> I'm probably wrong, though, but just wondered why. :)
A simple example of why it can be faster ("can", not "is") for selects
is obvious, if you have a look at caches for a moment: If you have
10.000.000 rows and an index on a char[50], you have an index of about
(50+4)*10.000.000 ~= 540MB. If you limit the index to 10 chars, you'll
need 140MB. If you have 256MB, the one may fit into memory, the other
may not and therefore requires additional disk reads.
Another example would be regarding disk reads: MySQL reads always a
whole block (1KB) from the index. With 14 bytes (plus some
adminstrative overhead) far more index entries fit into one block than
with 54 bytes. I.e. one disk read has a higher chance to fetch the
index entry you need next and make the next disk read unnecessary.
The situation is actually far more complex - I only wanted to show an
example why the seemingly slower configuration can be faster: due to
limited system resources, configurations which use available resources
wisely can be faster.
HTH,
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: Transactions
Hi. On Sun 2003-03-09 at 11:34:33 -0500, [EMAIL PROTECTED] wrote: > From what I understand, "transactions" are a kind of protection that > prevents certain commands from executing if certain other conditions > haven't been met. Not completely. They can do much more. Another way to look at transactions is to think about them as a possibility to execute several commands with the advantages (and guarantees) of a single statement, that includes as main points: - all or none of the statements are submitted (if an error occurs, all statements are rolled back) - other threads can only see the effects of any of the statements, when the transaction is committed. > And this is particularly important for ecommerce, among other > things. Do I more or less have that right? What is important for ecommerce are the ACID criteria (that are guarantees: Atomicity, Consistency, Integrity, Durability - you may want to look that term up), which require transactions to be implemented. Some people use the terms "transactions" in a wrong way by implying that an RDBMS with transaction support automatically complies with the ACID criteria, which is wrong. It just happens that most RDBMS which support the one also comply to the other. The most abused example is that a bank that wants to transfer money from one account to another. To update the balances you would do something like: UPDATE account SET balance=balance-100 WHERE id=2 UPDATE account SET balance=balance+100 WHERE id=1 Without transactions it may happen that you end up with changing only one of the two accounts. And there are a lot of possibilities why this could happen (power outage, error in the update, network problems, whatever). Some of them can be easily checked and worked-around in application code, some of them can only be handled by changing the database design and application logic a lot to accomodate the risks. > My main question, tho', is: Does the latest version of MySQL > available in production mode, not a beta or less, have this > transactions capability yet? MySQL 3.23 is declared stable since Jan 2001, i.e. more than two years, and had transactions support even longer. HTH, 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: License
Hi. On Thu 2003-03-06 at 12:45:19 +, [EMAIL PROTECTED] wrote: > > If I develop a software for my company that works with MySQL and this > software will be used only inside the company, do I have to purchase > commercial license? No, the GPL license will do: The GPL focuses on _distributing_ between _entities_ and puts conditions on that. Anything else you are free to do. As long as you not distributing (one company counts as one entity), you have to do nothing in order to comply with the GPL. Of course, if you find MySQL useful, it would be sensible to consider buying support to give back a bit. HTH, 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: Syntax confusion
On Wed 2003-03-05 at 17:12:23 -0600, [EMAIL PROTECTED] wrote: [...] > BA_DATE >= '2003-02-25 00:00:00' AND BA_DATE <= '2003-02-25 23:59:59' > > or > > TO_DAYS(FROM_DAYS(BA_DATE)) = '2003-02-25' Hm. You mean FROM_DAYS(TO_DAYS(BA_DATE)), don't you? BA_DATE is a timestamp, not a number of days. Aside from that, it prevents use of indexes, which is always bad. :-) How about BA_DATE LIKE '2003-02-25%' instead? HTH, 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: HAVING vs. WHERE
On Wed 2003-03-05 at 11:17:37 -0500, [EMAIL PROTECTED] wrote: > In the MySQL reference, it warns against using HAVING for items > that "should" be in a WHERE clause. I'm not sure what items > "should" be in a WHERE clause. Everything except stuff that only works when it's in the HAVING clause. The HAVING clause is applied only after all rows matching the WHERE clause have been fetched, i.e. if you put a condition from the WHERE clause into the HAVING clause, you take away all possibilities for optimizing. [...] > This does it: > > SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,"."))< 224 > OR left(inetAdr,instr(inetAdr,".")) > 239 > > and this works as well: > > SELECT *,left(inetAdr,instr(inetAdr,".")) as ia FROM Client HAVING ia < 224 > OR ia > 239 In this case you won't notice a big difference, because the first query uses an expression on inetAdr and therefore cannot use indexes either. Try hard to have a pure column on one side of the operator, like this: SELECT * FROM Client WHERE inetAdr NOT BETWEEN "224." AND "239." which will happily use an index on inetAdr. > and it is a little cleaner, although as I'm going to be doing this in a > PHP script, cleanliness isn't all that important. > > So I guess I have 2 questions: > > 1] Which should I use? My version. ;-) > 2] Is this the easiest way to check for the multicast address? You may want to have a look at the functions INET_NTOA() and INET_ATON(). HTH, 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: speeding up simple SELECT statements with the C api?
Hello. On Thu 2003-02-27 at 14:52:56 -0800, [EMAIL PROTECTED] wrote: [...] > Anyways, I'm running into a little bit of a performance issue as the old > database report writer had direct access to the database through a c library > (no sql interface). On some reports there can be as many as 100,000 select > statements. With mysql this is ending up with a performance penalty of > about 3x the existing time that the current report writer takes. > > Running Intel's vtune I can see that the select statements (through > mysql_query)are taking up around 90% of the run time. [...] > Anyways, I'm not sure if there is any kind of change I can make to reduce > this sql statement penalty and was hoping someone here could possibly help > reduce it. First, let me clarify, that the perceived speed loss is less with the SQL statements per se, but with: build query -> send -> task switching to server process -> read query -> parse -> optimizer -> read data and build result -> send result -> task switching to client process -> read result. That's of course not complete, but you get the idea. (You can avoid some of the latency by running queries in parallel.) IMHO, there is not much you can do about it. You switched from a specialized database interface to one that is intended for general, rational storage. A general approach is always slower than a specialized one, if both are of the same quality. There are some things you can try to get more speed, but when have implemented so much of them that you are at the old speed, you will have a similar specialized solution as you had before. If you say you are fine with the specialized solution, I wonder why you changed to an RDBMS to begin with (you could have taken, e.g. BDB). If you are not, I fear you have to live with some speed loss. That said, I suggest you take a look at (and benchmark for your application): - UNIX sockets, make sure to use them if you can, - your queries: can you combine some of the 100.000 statements? (oh, see you answered that below), - Using MySQL 4.0 to take advantage of the new query cache (you have to enable it explicitly), - threading (client-side), maybe you can run stuff in parallel, - the HANDLER commands which bypass some abstraction layers and - libmysqld, the embedded library, which bypasses the connection overhead. I am sure there is more, but that is what came to mind currently. [...] > I have a feeling it's the overhead with every query that's really > the problem here and that there really is no fix. That's right. > I also can't really combine the sql statements and save the data for > later due to the unique format of the reports. But perhaps there > are some optimizations I can make to help. HTH, 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: MySQL 4.0.11 is released
Hi. On Wed 2003-02-26 at 18:36:07 +0100, [EMAIL PROTECTED] wrote: [...] > As Mark has already stated, this is a typo - it should have read "GAMMA" > instead. Sorry for the confusion. Would you mind to elaborate a bit on the current state of the 4.0 cycle? Since Monty suggested in the 4.0.8 release announcement that 4.0.9 will be declared stable if no major problems arise with 4.0.8, I am a little bit suprised that we are still at gamma with 4.0.11. I understand that 4.0.9 was a quick after-release in order to fix the problem with hostname resolving, which affected a lot of people. I am not aware of a major bug fix which led to 4.0.10, but there were so much small changes. For 4.0.11, I could guess that the change in NULL sorting behaviour is the major thing. So, well, I think my question is, is that really the way I described and the stable release is only a little step away (and only delayed by what could be called bad luck) or is there a more general issue I am missing? As I said, a summary of the current state and your thoughts on next releases would be fine; I don't expect any binding statements. :) TIA, 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: How to query an entire row?
Hello. On Mon 2003-02-24 at 11:28:05 +, [EMAIL PROTECTED] wrote: > Hello, > > In a table like this: > > ID > Item1 char(100) > Item2 char(100) > . > . > ItemN char(100) > > What's the cleanest way to do this mysql query: > > SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' > > Only way I can think to do it is: > > SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE > '%mysearch%' OR ) Yes, that's it. If you think that's unpretty, you are right. With a normalized design, you usually shouldn't need such a query. In other words, if you find yourself needing to do such queries regularly, you may want to re-evaluate your database design. Depending on the context, a look at FULLTEXT indexes may be helpful, too. HTH, 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: InterBase vs. Mysql
On Tue 2003-02-18 at 17:06:17 +, [EMAIL PROTECTED] wrote:
> This will be my last posting. I don't belive I am being constructive
> and have no wish to instantly be hated by the whole of MySQL.
[...]
Oh, I do not have anything at all against you. I just tried to correct
what I saw as a misconception of yours. If anything of that sounded
offensive to you, I apologize, as that was not my intention. Blame it
on me not being a native speaker, if you want.
Hope you have a nice day,
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: Quick License Question...
Hi. First, I am not a lawyer. On Tue 2003-02-18 at 15:48:00 -0500, [EMAIL PROTECTED] wrote: > Quick question about the license issue that I thought of while reading > through the Interbase Vs. MySQL threads. > If I develop a program that uses MySQL for my company and it is only used > for internal use, never repacked and sold/distributed outside the company > what type of license aggrement is that under? If you mean whether that complies with the GPL, the answer is yes. The GPL is about distributing and therefore doesn't care about your use case (a company as a single entity with regard to this). In effect, you are using MySQL without license (but only under the "fair use" clauses of the copyright law, which allow you that). The reason you may do so with MySQL, but not with some other commercial software is simply that MySQL AB gave you the software without asking for money by making the download available (without attaching any conditions). > This project would have code that would obviously be 'sensitive' > information for the company so Open Source would be out the question, but > as this would never be re-distributed am I right in thinking we do not > need to buy a license aggrement from MySQL? Yes. > I was reading throuhg the manual in the license section and noticed > they said it would be 'nice' that if MySQL was helping your > enterprise then you should at least buy some support from > them. However, I am comfortable enough with MySQL and its use is > VERY light weight that it would be pretty silly to buy support from > them. > Not saying anything against the MySQL team, but they did make > the product fairly easy to work with :) Yeah, the idea in that sentence behind buying support is not about having support, but about given some money in order to "pay back", if you think that would be the right thing to do. That you also have official support this way is just an added benefit. In other words: it would be just a gift in order to say thanks. (With the thought that you already got a gift from them: free use of MySQL.) HTH, 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: InterBase vs. Mysql
On Tue 2003-02-18 at 16:11:11 +, [EMAIL PROTECTED] wrote: > Benjamin Pflugmann wrote: [...] > There is also some middle ground here. Which is the overlap of the two. No. Either the way you distribute your software is GPL-compliant or not. If it is not, you need a commercial license, if it is, you are fine. Forking the source requires it to be still under the GPL, so the license requirements have not changed. > MySQL say that this is an extension of the application, and therefore > breaks the GPL, and therefore a licence is needed. Okay, you can argue, if MySQL AB's interpretation of the GPL is correct, but this changes nothing. Even if you fork, they will still own the copyright on a major part of the code and can still sue you, if they like (I do not mean to imply that they would be fast to sue). > They are however, the only big GPL user who thinks this way. I note > for example the number of companies selling commercial CGI software > designed to run on Apache, Wrong example. Apache is not licensed under GPL (it's "Apache License"). > to which no licence is mandatory. Also Sendmail, Neither is sendmail.(it's BSD license). > GCC, Have not seen many packages that add on GCC. And derived output of GCC is explicitly excluded from being GPL. > other DBMS's, Examples? PostgreSQL is not GPL'ed, InterBase neither (I am not sure if that is still current, but last time I looked it was not). > and indeed GNU/Linux it's self. That's a valid reference in that Linus Torvald has indeed said that he does not think kernel modules have to be GPL'ed, but then, Linus is not someone who much cares about such issues. [...] > Therefore, I can see no reason why not somebody could fork MySQL into > FreeSQL. It would take a few hours at SorceForge, a 'sed' of MySQL into > FreeSQL', and a good posting to Slashdot. Keep it 100% GPL without > breaking either the wording or the spirit of the document. Remove all > reference to copyright material belonging to MySQL. It's either GPL or > cpryright, not both. No offense meant, but you seem to have a lack of understanding of how the GPL works. It cannot work without copyright. It is based on it. It sounds as if you base your opinion on hearsay. I suggest to read the GPL FAQ http://www.gnu.org/licenses/gpl-faq.html. Or talk to an lawyer if you need. > Then use this without commercial licence... > > BTW, as to another posting. 'Either accept the GPL or purchase a > licence'. I do note another option (apart from forking): Use something > else. Is MySQL really that good? I do worry that with arrogant > statements like this, this is exactly what people will do, in droves. What's the problem? Do use whatever fits best with your need. The point of the statement is that there is no "right" to have MySQL without cost. You can have it with without cost, if you abide by the GPL. Or else you can buy it. Or you can use something else. Freedom of choice. Although that may sound arrogant, it is not meant this way. It is meant as being realitistic: The people who put a lot of hard work into making MySQL have chosen the GPL. So you should respect that. One could also see it the other way: it sounds kind of arrogant of people trying to tell MySQL AB how they have to license their software. You are free to choose the product of your choice. MySQL AB is free to choose the license(s) of their choice. 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: InterBase vs. Mysql
On Tue 2003-02-18 at 12:57:04 +, [EMAIL PROTECTED] wrote: [...] > Most benchmark published actually give credit to mysql when it comes to > Mysql Vs Interbase, but as an end user, the best tests are the one i > conduct myself in my premises. > Therefor when i say that mysql Vs interbase definitely goes to interbase, > understand that it's the result of my own experience! Hm. So you take the limited experience of your use case and make general statements from that. Don't get me wrong. I absolutely believe you that InterBase is faster for you in your use cases. But that doesn't mean that it is in general. As you can see from the reactions, it would help if you stated your personal experience as such. > Bear in mind that all the benchmark published can give you all the credit > possible, if my experience as an enduser proove me otherwise, i'll tend to > believe what i can see and proove, not what i can read in an article! I don't see how this is less biased than a benchmark might be. Of course, it is the relevant part for you personally, in accordance with the next statement. > So my word to any enduser would be : conduct your own experiences... and > see for yourself. Absolutely. Seconded. 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: InterBase vs. Mysql
Hi. On Tue 2003-02-18 at 11:48:53 +, [EMAIL PROTECTED] wrote: > hi Maciej, > > the only arguments you can get in favour of mysql is that it's free, and > some of its SQL statement are faster than interbase. Well, and outstanding support, IMHO. > Interbase has the advantage of having views, stored procedures,triggers,and > it's crazily fast. > Don't forget that interbase is also written by professionnal on borland > campus, guyz who completed a university degree and were assessed and were > judged good enough to work with borland; whilst the average developpers for > mysql are volunteers who are not paid for what they are doing! You seem to have some greater misconception about the development of MySQL. Please don't make such statements when you don't know the background. Although MySQL had some great contributions (in source code or otherwise) from volunteers, it is mainly developed by the staff of professionals of MySQL AB. So the speak about university degrees is pointless (aside from that, why do you think volunteers wouldn't have a degree...). > I think you should insist on the free aspect of mysql and it's simplicity > of use, because for somebody who knows the 2 databases, features wise, > robustness wise and speed wise, interbase is far ahead of mysql. I give you features wise. The other two, robustness and speed wise, I see no indication for. HTH, 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: InterBase vs. Mysql
On Tue 2003-02-18 at 12:02:59 +, [EMAIL PROTECTED] wrote: [...] > This is the main reason why I am looking at PostgreSQL at the moment, > although I've not looked much at Interbase, any good? > > Either that, or fork MySQL into FreeMySQL, as we can do under the GPL, > and not charge our selves :) Aehem. There seems to be some misconception here. Either your program is fine with MySQL being GPL or not. If it is (and your forking example would work for you) either by using MySQL in a way that your program is not required to be GPL'ed or by GPL'ing your program, you need no commercial license from MySQL AB either, and you can already distribute your program with MySQL without the need of a fork or whatever. Or your program needs a commercial license, than forking MySQL would not help, because you still have to adhere to the GPL. The only reason MySQL AB can hand out a commercial license is because they are also the Copyright holders, which you aren't even after forking. HTH, 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: InterBase vs. Mysql
Hi. Just a little correction. On Tue 2003-02-18 at 12:44:39 +0100, [EMAIL PROTECTED] wrote: > Hi Maciej, > > I don't know much about Interbase, but MySQL is for some cases a very good > choice! > Some database engineers would say MySQL isn't a database because it has no > transactions by know (in a stable version). MySQL 3.23 has InnoDB support and therefore transactions and is the stable version since for two years now. The only change with 4.0 is that InnoDB is going to be part of the "MySQL" package instead of "MySQL-Max", as it was with 3.23. > But think about, if you really need this!!! > In versions 4.x (coming soon in a stable version) transactions, subselects > etc. would be available! Soon coming as stable is 4.0 (it is already good enough for production use, IMHO), but sub-selects are in 4.1, which is still declared alpha. The main features of 4.0 as listed on http://www.mysql.com/doc/en/News-4.0.x.html are: - a query cache (may vastly improve performance for many apps) - improved FULLTEXT indexing - MERGE table improvements - support for UNION in select - libmysqld, a embedded version of MySQL - more fine grained privileges (with GRANT) - dynamic server variables (change the configuration on the fly) - rewrite of replication with new features - and some other stuff HTH, 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: Why is MySQL 4 standard binary 4mb larger than max?
On Tue 2003-02-18 at 01:17:16 -0800, [EMAIL PROTECTED] wrote:
> Just curious since max has more featured compiled in yet it's quite
> smaller for download at mysql.com.
Because the MySQL-Max package only contains mysqld-max, and you are
supposed to install it alongside of the MySQL package, which contains
everything else needed, not only the standard mysqld.
HTH,
Benjamin.
PS: Please start a new thread when you start a new subject (i.e. do
not use a unrelated message and reply to that). Else, most mail
readers will show it belonging to the post you replied to.
--
[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: REPOST: MySQL 3.23.54 is a buggy, crashing piece of crap.
On Mon 2003-02-17 at 12:14:12 +0100, [EMAIL PROTECTED] wrote: [...] Please disregard this mail. It was not supposed to go to the list. (forgot to remove the CC that my mailer is set up to automatically append for mails going to my -mysql address). My apologies about that, Jason. 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: REPOST: MySQL 3.23.54 is a buggy, crashing piece of crap.
On Mon 2003-02-17 at 03:38:07 -0600, [EMAIL PROTECTED] wrote: > Reply when you have a job, dumbass. > I have a system that makes more in a week than you make in a year > crashing because of this buggy piece of shit. If a system supposedly making millions a year is unstable you are a fool that you haven't solved it already using your advanced support contract (http://www.mysql.com/support/), that you surely have. > I don't give a rats ass what your worthless opinion on my post is. > Now fuck off, dumbass. Yeah. That lack of respect is exactly why I refused to read your original post about the problem to the end. As I said, that is a list of volunteers and your attitude surely doesn't help you to get answers. Bye, Benjamin. > - Original Message - > From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> > To: "Jason Maskell" <[EMAIL PROTECTED]> > Sent: Monday, February 17, 2003 3:35 AM > Subject: Re: REPOST: MySQL 3.23.54 is a buggy, crashing piece of crap. > > > > On Mon 2003-02-17 at 02:40:57 -0600, [EMAIL PROTECTED] wrote: > > > Oh grow up. > > > > Exactly the kind of reply I expected from you. > > -- [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: How to build Relay Replication system ?
Hello. On Sat 2003-02-08 at 07:24:17 -0700, [EMAIL PROTECTED] wrote: [...] > Do you think the following links may help? > > * http://www.mysql.com/doc/en/Replication_HOWTO.html > * http://www.mysql.com/doc/en/Replication.html > * http://www.mysql.com/doc/en/Replication_FAQ.html > * http://www.mysql.com/doc/en/Replication_Options.html > * http://www.mysql.com/doc/en/Replication_SQL.html > > This was an automated response to your email 'How to build Relay Replication system >?'. > Final search keyword used to query the manual was 'How to build Relay Replication >system ?'. > > Feedbacks, suggestions and complaints about me should be > directed to [EMAIL PROTECTED] Is this bot somehow endorsed by the list admin / MySQL AB? Anyhow, could you please stop CC'ing the replies to the list? There is enough mail already without getting a "copy" triggered by every new question. I am not pleased. Or in other words: There is no additional benefit for subscribers in getting all these automatically generated search results. I am sure that most subscribers know where to look if they want to search the manual. So please, if you must, keep the reply to the original author only. Regards, 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: Mail Problems
Hi. On Sat 2003-02-08 at 16:06:03 +, [EMAIL PROTECTED] wrote: [...] > Re: InnoDB foreign keys bug Yesterday 12:30:56 am > BUG: InnoDB ORDER BY DESC may hang in 4.0.10 Yesterday 12:40:40 am > > and they arrived 24 hours after being sent! Yes, the list server is lagging behind now for a while (AFAICT it started End of January and got worse since then). 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
java.sql.Exception: Communication link failure on FreeBSD
Hi, I've seen there's a problem with the mysql binaries on linux, does anyone know if this also affects FreeBSD? I cannot get access to the database via jdbc. I am using the mysql-connector-java-2.0.14-bin.jar, mysql version 3.29.49, on freebsd 4.5, and get the following stacktrace: java.sql.SQLException: Communication link failure: java.io.IOException at at at com.mysql.jdbc.Driver.connect(Unknown Source) at at java.sql.DriverManager.getConnection(DriverManager.java, Compiled Code) at yo.main(yo.java, Compiled Code) Cheers, Ben - 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: Opposite selection...
Hi. On Tue 2003-02-04 at 12:25:08 -0500, [EMAIL PROTECTED] wrote: > Ok I'm stumped on what I think should be a somewhat simple query. What I > have so far is a list of names that is in a list of projects AND in a the > main contact list by doing the following query: > SELECT p.name, p.company FROM contacts c, projects p WHERE > CONCAT(c.firstName, " ", c.lastName) = p.name AND c.company = p.company > > This is good and works correctly, what I need now is the opposite of this. > The names that are in the project list but NOT in the contact list. If I > had some subqueries this would be a simple NOT IN :) but as I dont (mysql > 3.23.55) I'm not sure how to attack this. Well, manual explicitly explains how to cope with the lack of sub-selects: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html So something like SELECT p.name, p.company FROM project p LEFT JOIN contacts c ON c.company = p.company AND CONCAT(c.firstName, " ", c.lastName) = p.name WHERE c.lastName IS NULL should do the job. This won't be able to use indexes due to the expression (CONCAT) over the right-table columns (this was not true for your original example, because a normal joins allows exchanges the order, a LEFT JOIN doesn't - a sub-select wouldn't help with this, btw). If you are sure that no spaces are in firstname resp. lastname, you can rewrite the condition to enable use of indexes: ON c.company = p.company AND c.firstName = SUBSTRING_INDEX( p.name, ' ', 1 ) AND c.lastName = SUBSTRING_INDEX( p.name, ' ', -1 ) HTH, 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: zerofill fields
Hi. On Tue 2003-02-04 at 17:04:39 -, [EMAIL PROTECTED] wrote: > > I noticed that when you return a zerofill field from a select statement into > a server-side language, say PHP or Perl, it will store the number with the > zeros included, great no problem. > > However, if I insert into that table which has a primary key which is set as > a zerofill field, when I use the PHP command mysql_insert_id() it returns > the primary key value, but without the zeros, is there any way round this > apart from writing some code to add the zeros? No, as mysql_insert_id operates on a number, not a string, so there is no way to pass the leading zeros trough. Regards, 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: creating update files
Hi. On Mon 2003-02-03 at 10:19:59 +0100, [EMAIL PROTECTED] wrote: [...] > >That can be made to work with several versions. In other words, it > >would not check a version number saved in the database, but determine > >the version by the database structure itself. > > > >That would give #2 with the assurance that an update script is only > >run, if the database structure is as expected, no matter happened to > >the database in-between (it is fascinating what clients can do to > >files they are not supposed to even know about ;-). > > Sound like a good idea, ie that I should use a md5 hash to recognise the > structure. and use that as a sort of versioning of the tables... > > I think the md5 would have to be calculated ona per table basis, since > if we introduce some bug-fixes in one module only and it requires a > table-update, this would be recognised in a future update... Of course it is up to you, but I wouldn't do it this way. Changing one table but not another could break your application. I would call your bug-fixes a new version and handle it accordingly. > the question is then, do "select create table TEST" work exactly the > same on different os:s ie if I have the same table on a windows > machine and on a linux machine, does the above query return the > exact same result? even eith line endings? or could they return 2 > different queries and therefore making the md5 calculating prove > worthless? Interesting point. I did not think about this before (have no cross-platform issues here). Well, if you use mysqldump, you get different versions anyhow (because it contains info about server, etc.). On UNIX at least, it is quite easy to accomodate for lines ending. Hm. There is a another problem. Newer versions of mysqldump use SHOW CREATE TABLE, AFAIK, and therefore the dump will look server-dependend (comments for 4.0 features or such). In short, it is not as stable as needed regarding md5sums across either, platforms or MySQL versions. Missed that, as I concentrated on what you asked for, changes in your database. The cross-platform part can be solved relatively easily by either normalizing the dumps before building the checksum (which is not too hard), or by simply having two checksums (the MS Windows and the UNIX one) pointing to the same update-script. For the MySQL versions issue, it should be enough to keep a older 3.23 mysqldump around, and use always the same. This may break somewhen (MySQL 5.0?), but it should give you mid-term stability for the process. > or are there other ways to get to the database structure that work the > same on different platforms...? Hm. Write your on mysqldump? (That's not as hard as it sounds, because you don't have to start from scratch, but only modify the existing one.) HTH, 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: check doesn't seem to work
Hi. First of all, do all of us, including yourself, a favor and upgrade to 4.0.9 (or 4.0.10 which should come out this week). Reporting problems against a 7 months old alpha version is not very, well, productive, if the current release is gamma quality. On Mon 2003-02-03 at 10:04:58 +0100, [EMAIL PROTECTED] wrote: > > I have a create table command in which the check statement does > not seem to do anything. I know it was just there for compatibility > in older mySQL versions, but I thought it should be working in > version 4.0.2. Isn't it? Where did you get that from? The only 4.0 change entry mentioned CHECK is for 4.0.6 (which you don't have) and explictly tells that it is still not implemented: http://www.mysql.com/doc/en/News-4.0.6.html And the CREATE TABLE section explains the same: http://www.mysql.com/doc/en/CREATE_TABLE.html Regards, 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: SQL Syntax
On Sat 2003-02-01 at 10:35:46 -, [EMAIL PROTECTED] wrote: > Hi Benjamin, > > Wow, that sure sorted that problem out... I had to rejig it slightly to > get it to work, Oops... too much copy&paste by me :-) > but this is the final working version: Glad it worked out. Bye, Benjamin. > > SELECT > b.id, > p.part_code, p.product_type, p.description, > po.options, > b.price, b.quantity, > b.price*b.quantity AS total > FROM basket_header bh > INNER JOIN basket b ON b.basket_id = bh.basket_id > LEFT JOIN products p ON p.prod_id = b.prod_id > LEFT JOIN product_options po ON po.po_id = b.op_id > WHERE bh.basket_id = 4 > GROUP BY b.id, p.part_code, p.product_type, p.description, po.options, > b.price, b.quantity, total, bh.basket_id, p.options > > Many thanks, now I'll work out why I couldn't do that so I can better > understand it. [...] -- [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: mysqldump issue (table named group)
Hello. On Fri 2003-01-31 at 13:45:26 -0600, [EMAIL PROTECTED] wrote: > >Description: > One of our customers had a table named group, admitedly a bad design > but still not something that should cause mysqldump to fail hard! Please be more specific. Did mysqldump error out or the re-import of the data? > >How-To-Repeat: > Name a table group and run mysqldump, even with --opt Have you tried --quote-names ? > >Fix: > Quick workaround is to rename the table, but actually, mysqldump should > surround columns and tables with `` marks when creating sql code See above, --quote-names is supposed to do that. If it doesn't for you, please provide a repeatable example. Bye, Benjamin. > Release: mysql-3.23.54 (Source distribution) > System: Linux x.com 2.4.18-19.7.x #1 Thu Dec 12 09:00:42 EST 2002 >i686 unknown [...] -- [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
