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 awall...@ihouseweb.com 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 awall...@ihouseweb.com 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. -- 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
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 awall...@ihouseweb.com 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: Cannot connect to MySql Database
On 4/9/13 1:54 PM, Joe Kosinski joj...@gmail.com 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 gatorre...@gmail.com 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 larry.mart...@gmail.com
On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com
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 rp.ne...@yahoo.com 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
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: 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: 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 larry.mart...@gmail.com 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
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: 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 bstill...@limitedbrands.commailto: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.commailto:eddy.ada...@gmail.com] Sent: Monday, September 24, 2012 10:23 AM To: mysql@lists.mysql.commailto: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
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 shawn.l.gr...@oracle.com 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: 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
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\binndbd* *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: 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\binndbd* *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: 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
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 hassan.schroe...@gmail.com wrote: On Sat, Aug 4, 2012 at 8:14 PM, Fred G bayespoker...@gmail.com 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: 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 hassan.schroe...@gmail.com wrote: On Sat, Aug 4, 2012 at 8:14 PM, Fred G bayespoker...@gmail.com 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: 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 fails (which it did last Saturday morning at 2:00AM.) The switch over process takes 10-15 minutes although I am reducing that to about five minutes with some
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
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: 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: 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
