Re: Where to ask a question about installation and configuration
Hello Steve, To what list should I post with a post-installation config and startup question? This list, the MySQL General Mailing List, is the right place if the question is about MySQL! Cheers -- Claudio
Re: Proxy / connected failover question
Hi Johan, I wanted to love mysql-proxy for so many years, so I understand you :) I have two main questions: * am I remembering right that MySQL Proxy provides transparent failover ? You need to use/create a lua failover script, I've never seen or tried one. What kept me from investing too much time and effort on it is that mysql-proxy has been alpha for ages, including not having been really multithreaded. * Are there other contenders in the same field, or alternate solutions ? MaxScale looks really promising: https://github.com/skysql/MaxScale I am testing it, it is not yet production ready probably, but it's developing very fast and I like that you can telnet to a debug console and monitor/manage it. Plus you can write your own plugins, I'd definitely have a look at it. 1.0 beta was recently released: http://markriddoch.blogspot.it/2014/07/maxscale-10-beta-highlights.html Ideally I'm looking for a hyper-stable tool that can run on it's own VM, so the application doesn't notice when I switch backends. All the other applications play nice, in that they simply reconnect and go on with business, so it doesn't even *have* to take improbably loads. I think it is a shared mysqlians dream :) Cheers -- Claudio
Re: About EXPLAIN: Extra column information
Hi, Can anybody explain me the difference between using index, using index condition and using where in the EXTRA column information of EXPLAIN query? using index: the columns selected are part of an index that is used to return the results, there is no need then to read the full table record and the columns will be returned using the already read index record. using index condition: The so called index condition pushdown. It is an optimization to try to avoid reading the full table record when the access method is using an index that is part of the where condition, if the index does not satisfy this partial condition the other conditions checks will be skipped, this is valid with AND conditions. Not sure if the reverse is valid with OR operator, that is if the index satsfies the condition the record should be read anyway so that the condition check is skipped. using where: the where condition is not checked at storage engine level but all records are returned and filtered by mysql Hope this helps. Best Regards Claudio
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hi | | ericomtxmacbookpro.local | *E85DC00A0137C6171923BE35EDD809573FB3AB4F | mysql DELETE FROM mysql.user WHERE user=''; mysql FLUSH PRIVILEGES; maybe helps? Cheers -- Claudio
Re: Nested WHERE
Hi Jopoy, Try this: SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username HAVING total_usage 322100 ORDER BY total_usage DESC; On values derived from group functions you have to use HAVING instead of WHERE, WHERE filters the records before the grouping, HAVING once grouping is done. Cheers Claudio 2013/11/21 Jopoy Solano m...@jopoy.com Hi! I'm not sure how to phrase this question... anyway, here it is: I'm trying to show users in DB radius who have exceeded 322100 bytes (3GB) within the current month. As of writing I can only display total usage by user with this: SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username ORDER BY total_usage DESC; I wanted to add something like a WHERE total_usage 322100 line but I don't know where to insert it. Any help would be greatly appreciated. Jopoy -- Claudio
Re: Why is mySQL not respecting foreign characters as different
Hi, I wold expect this NOT to match. This should be because the fields you are comparing are utf8_general_ci, this collation groups characters in 'classes' so that all variants of what are considered to belong to the same character type, are put in that class. Equality comparison is done comparing classes not single characters. For this reason 'e' and '' are considered equal (same class--same type of character) I didn't find anything better but here you can have an idea: http://collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html Do I have to add something to my query to tell MySQL to respect other character sets as different? If you want to distinguish between characters in the same utf8 class you have to use some workaround, from the top of my head, you might use binary() to get the utf8 code of the string which is different for each character. mysql SELECT text_id, us, de, es, fr FROM texts WHERE us = es; +-+---+---++---+ | text_id | us| de| es | fr| +-+---+---++---+ | 1 | scene | Filmszene | escena | scène | +-+---+---++---+ 1 row in set (0.00 sec) mysql SELECT text_id, us, de, es, fr FROM texts WHERE binary(us) = binary(es); Empty set (0.00 sec) There are probably other ways. Cheers Claudio 2013/9/26 Daevid Vincent dae...@daevid.com How come MySQL is not differentiating between these characters? SELECT text_id, us, de, es, fr FROM texts WHERE us = fr; Results in matching here. Notice the difference in the scene vs scène text_id us es de fr -- -- -- - all_page_scene scene escena Filmszene scène I wold expect this NOT to match. Do I have to add something to my query to tell MySQL to respect other character sets as different? CREATE TABLE `texts` ( `text_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', `us` text, `es` text, `de` text, `fr` text, PRIMARY KEY (`text_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FieldType Collation NullKey Default Extra Privileges Comment --- --- - -- -- --- -- --- - text_id varchar(50) latin1_general_ci NO PRI select,insert,update,references us text utf8_general_ciYES (NULL) select,insert,update,references es text utf8_general_ciYES (NULL) select,insert,update,references de text utf8_general_ciYES (NULL) select,insert,update,references fr text utf8_general_ciYES (NULL) select,insert,update,references -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: connection issue
Hi, # mysql -P 5045 Add -h127.0.0.1 # mysql -P5045 -h127.0.0.1 Cheers -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NOW() is stuck...
Avoid FreeBSD, Unless they did some real big magic on the scheduler of 9. Claudio On Jun 28, 2013 6:12 PM, Andy Wallace awall...@ihouseweb.com wrote: Nope, it was locked on a single value for about 36 hours, until we restarted the engine last night. Now it's running fine, and we're setting up a testbed to evaluate MySQL 5.6 and FreeBSD 9 (?) for replacing our current Solaris 10/MySQL 5.1.46 setup. On 6/28/13 12:44 AM, walter harms wrote: hi, does the value change at all like below ? mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372404355 | +---++ 1 row in set (0.00 sec) mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372404371 | +---++ 1 row in set (0.00 sec) re, wh Am 27.06.2013 20:19, schrieb Andy Wallace: Benjamin - Unfortunately: mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372238834 | +---++ 1 row in set (0.00 sec) And: mysql set global timestamp = 0; ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL This does indeed persist across sessions. Any command line connection I make to the database shows the bad value for NOW(). I also tweaked the application code to include NOW() in an existing query, and the value returned to my PHP code is also the bad value. Thanks for looking, andy On 6/27/13 11:10 AM, Stillman, Benjamin wrote: 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
Re: NOW() is stuck...
Hi, On 06/27/2013 08:19 PM, Andy Wallace wrote: Benjamin - Unfortunately: mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372238834 | +---++ 1 row in set (0.00 sec) And: mysql set global timestamp = 0; ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL Then, as Benjamin said, you have found the bug. 'GLOBAL timestamp' should not exist http://bugs.mysql.com/bug.php?id=49686 Your GLOBAL (ghost) instance of this variable sets the SESSION one at every client connection. But you are sort of trapped because there is no syntax to manipulate that GLOBAL instance. Also, sadly the manual page does not explain what happens if you set it to DEFAULT: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html Cheers -- Claudio
Re: munin MyISAM InnoDB issues
Hi, I seem to understand this is the check on the innodb main tablespace. Sounds like an overflow in munin check that builds the graph, due to the type of check (MySQL InnoDB free tablespace) I would not mind too much(There's no such critical thing like too much free space!), apart from patching the munin check to deal with numbers 2^32. To double check, look at: mysql SHOW TABLE STATUS LIKE 'any innodb table that was created in the main tablespace'\G Look at the Data_free: line, it is probably bigger than 2^31. In this case it is basically not a problem, apart from Munin not able to represent that number. For MySQL isam/myisam table-space usage broken image I have no idee on how that check is built, moreover MyISAM does not have a real tablespace. If it makes you feel better in http://demo.munin.jp/munin2/mysql-day.html that is broken too ;) Cheers Claudio On 06/16/2013 10:14 PM, Grant wrote: I have 4 out of 6 mysql graphs working in munin. MySQL isam/myisam table-space usage is a broken image and MySQL InnoDB free tablespace says: This service is in CRITICAL state because one of the values reported is outside the allowed range. Field Internal name TypeWarnCritInfo Bytes free freegauge 2147483648: 1073741824: I don't know enough about mysql (or munin) to figure out what's going on. I'm using both MyISAM and InnoDB tables. Can anyone help me out? - Grant -- Claudio
Re: severe build bug 5.5 viossl
Hi Nick,
It seems it is fixed already in MariaDB:
http://bugs.mysql.com/bug.php?id=68999
[9 Jun 9:34] Michael Widenius
This was fixed in MariaDB 5.5 in May 2013 as part of our merge of
MySQL 5.5 to MariaDB 5.5.
Cheers
Claudio
2013/6/9 Nick Edwards nick.z.edwa...@gmail.com
This was reported in 5.5.31, a patch, VERY SIMPLE was submitted.
The problem goes ignored by oracle
5.5.32 releases, same error, apply the same simple patch and builds
/tmp/mysql-5.5.32/vio/viossl.c: In function 'ssl_do':
/tmp/mysql-5.5.32/vio/viossl.c:175: error: 'SSL_OP_NO_COMPRESSION'
undeclared (first use in this
function)
/tmp/mysql-5.5.32/vio/viossl.c:175: error: (Each undeclared identifier
is reported only once
/tmp/mysql-5.5.32/vio/viossl.c:175: error: for each function it appears
in.)
make[2]: *** [vio/CMakeFiles/vio.dir/viossl.c.o] Error 1
make[1]: *** [vio/CMakeFiles/vio.dir/all] Error 2
Question, does anyone at oracle even bother with bug tracking now days?
How can something that causes a fail of building with versions of
openssl less then 1.0.0
go un fixed for so long.
Is this more proof that oracle DGAF about mysql? should I move to mariadb?
because if we have to re patch a failed build on 5.5.33, we will I
think, since it shows oracle dont give a stuff
For list archive, patch is:
--- mysql-5.5.32/vio/viossl.c 2013-05-17 01:47:14.0 +1000
+++ mysql-5.5.32a/vio/viossl.c 2013-06-09 15:38:06.0 +1000
@@ -172,8 +172,10 @@
SSL_SESSION_set_timeout(SSL_get_session(ssl), timeout);
SSL_set_fd(ssl, vio-sd);
#ifndef HAVE_YASSL
+#ifdef SSL_OP_NO_COMPRESSION
SSL_set_options(ssl, SSL_OP_NO_COMPRESSION);
#endif
+#endif
if ((r= connect_accept_func(ssl)) 1)
{
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
Re: pt-query-digest --processlist
Hi, First of all I discourage you to use --processlist, I don't think it is enough an accurate method to analyse queries. You can dynamically enable the Slow Query Log with long_query_time=0 and get a way better data. Check the syntax for SET GLOBAL variables. Remember to disable it afterwards. Now to your question, pt-query-digest version 2.2.2 I run to connect to remote node: pt-query-digest --processlist h=192.168.1.111,u=user -p PASSWORD --print --no-report --run-time 60 SERVER-`date +%Y%m%d-%H` I get: Unknown option: print Maybe because such option does not exist? Cheers Claudio
Re: SSH tunnels and non root accounts get the server service or the configuration file could not be found
Miguel, Probably your non-root user is connecting as the anonymous account. Try this: Connect as your (problematic) non-root account and do this: mysql SELECT USER(); mysql SELECT CURRENT_USER(); What do you see? If it's as I imagine you should see different values and so just delete the anonymous user from mysql.user table. Best Regards Claudio 2013/5/29 Miguel Gonzalez miguel_3_gonza...@yahoo.es In my email I state quite clearly that tunnel is working with the root account so it's not a matter of ports. The error message shows that is trying to get some configuration from the user account that is not working. Under root account I have found a file called .my.cnf with a [client] entry as I said. Regards, Miguel De: Tim Pownall pownall...@gmail.com Para: Miguel González miguel_3_gonza...@yahoo.es CC: mysql. mysql@lists.mysql.com Enviado: Miércoles 29 de Mayo de 2013 3:29 Asunto: Re: SSH tunnels and non root accounts get the server service or the configuration file could not be found If you are tunneling port 3306 to your local machine, you need to have mysql listen on the local port. what ever port is being used on your local computer to operate the tunnel to port 3306 remotely is the port you will use for mysql locally. I hope this makes sense! On Tue, May 28, 2013 at 7:05 PM, Miguel González miguel_3_gonza...@yahoo.es wrote: Dear all, Not sure if this the right mailing list address for asking this. Server running Centos and MySQL. Client is a windows xp machine. I have setup a SSH tunnel with putty and run mysql administrator. It works fine with the root account. With a non-root account I get the server service or the configuration file could not be found. I can log on but I can't see the databases that I should be allowed to see. Running a mysql -h 127.0.0.1 -u myuser -p mypassword from linux works fine I have created a .my.cnf file in the home folder with 600 permissions in the linux box and filled it with: [client] pass='mypass' user=myuser Server configuration file is under /etc/my.cnf. What am I doing wrong? Regards, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Thanks, Tim Pownall Linux Systems Performance Specialist 610-621-9712 pownall...@gmail.com -- Claudio
Re: SSH tunnels and non root accounts get the server service or the configuration file could not be found
Hi Miguel, I'm confused. Where should I issue those commands? Yes from the MySQL Administrator. From what you say it seems that you end up being authenticated as the ''@'localhost' user. Connect again with the MySQL Administrator and the non-root account and issue: mysql SHOW GRANTS; What user do you see after: 'Grants for ' ? Cheers -- Claudio
Re: Innodb innodb_buffer_pool_size?
Hi Rafal, I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My question: how to tell if my innodb_buffer_pool_size is ok? If this is a MySQL dedicated server, In your case I would set it to 2GB-3GB. You will have the whole data in RAM now and for some time. Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? No. It means it is doing fine. And if you don't need RAM for anything else why lower it? Does Free buffers 0 mean that I should make it larger? No. InnoDB will always try to allocate each block in the buffer pool. Eventually what else to check? (a) mysql SELECT engine,sum(data_length)/1024/1024 as DATA_MB,sum(INDEX_LENGTH)/1024/1024 as INDEX_MB FROM information_schema.tables GROUP BY engine; To check the real size of the dataset. (b) Note: mysql-server-5.0.58 you should upgrade to latest 5.0.96 Regards Claudio
Re: Basic SELECT help
On 11/22/2012 04:10 PM, Ben Mildren wrote: SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; Ben you were almost there ;) SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=num of params The only bad is the hardcoded parameter in the HAVING, may be it might be improved. Anyway if the query is handwritten then you just hand-modify that too, if it is built from code I can't imagine counting the parameters in the code being so hard. Cheers Claudio On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com wrote: Michael, Thanks this kind of works if I'm checking two types. But what about if I have 5 types ? On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote: response did not go to the list.. I assume that you mean the id must be associated with both type=5 AND type=2 as opposed to type=5 OR type=2; in some dialect of SQL (not mysql) you can do this: select distinct id from 'table' where type=5 intersect select distinct id from 'table' where type=2 As INTERSECT is not avilable under mysql, we will have to go the JOIN route select distinct a.id from mytable a inner join mytable b on (a.id=b.id) where a.type= 2 and b.type = 5; - michael dykman On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins neil.tompk...@googlemail.com wrote: Hi, I'm struggling with what I think is a basic select but can't think how to do it : My data is id,type 1000,5 1001,5 1002,2 1001,2 1003,2 1005,2 1006,1 From this I what to get a distinct list of id where the type equals 2 and 5 Any ideas ? Neil -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: ERROR 2013 (HY000): - Why ?
On 11/19/2012 10:27 AM, walter harms wrote: hi List, i get occasionally the following error: ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32 $ perror 32 OS error code 32: Broken pipe Just to start. If you want help you should include more details: * client used * server version * network topology * protocol used * etc -- Claudio
Re: MySQL Enterprise Monitor Testing
Hi, If things are still as before, you get MEM only if you have a support contract with Oracle, so it would make more sense to ask Oracle. They make that product, they know better how it works, they can help you better, and you are entitled to get that help. Consider that the users of the Community MySQL version do not have MEM, so you will find a very small percentage of people that used that (I am in this small percentage). Cheers Claudio 2012/11/11 Bheemsen Aitha pgb...@motorola.com Hi list, I just installed MySQL Enterprise Monitor (MEM) to monitor our MySQL NDB cluster. Per our company policies, I need to test all MEM advisors for all metrics. Did anyone do this before? Any there any tools to test the advisors in MEM? I appreciate if anyone can share their experiences with MEM. Thanks BA -- Claudio
Re: How to verify mysqldump files
Gary, It is always a good practice to test the whole solution backup/restore. So nothing is better than testing a restore, actually it should be a periodic procedure. As for the validity of the file usually is delegated to the operating system. If you want to check it yourself you may create an algorithm that analyses some patterns in the dump file to recognize that it is correct, starting may be from one that is working as 'valid' sample. Cheers Claudio 2012/11/7 Gary listgj-my...@yahoo.co.uk 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 -- Claudio
Re: Percona Backup
http://www.percona.com/doc/percona-xtrabackup/ Cheers Claudio 2012/10/23 Sabika M sabika.makhd...@gmail.com hi! I am trying to work with percona's extra backup. Has anyone found any simple instructions how to install it? We are using innodb file per table Thanks! Sabika -- Claudio
Re: Possible to copy the key field to another on INSERT?
Take a look at TRIGGERS C. PS: I am curious to know why you would do that anyway 2012/10/17 W. D. w...@us-webmasters.com When creating a record, the first field (KeyField)... KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT ...is it possible to copy this auto-generated value into another field when using the same INSERT that creates the record? Or would I have to use an UPDATE query using LAST_INSERT_ID() immediately after the INSERT statement? Thanks for any ideas you have. Start Here to Find It Fast!™ - http://www.US-Webmasters.com/best-start-page/ $8.77 Domain Names - http://domains.us-webmasters.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: user last activity and log in
Hi, 2012/10/4 Reindl Harald h.rei...@thelounge.net Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance No because MySQL does not have this facility. (5.6) Saying that a feature is not present because the hypothetical implementation would impact performance doesn't make much sense in my opinion. this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I can suggest a reading here: http://www.amazon.com/Implementing-Database-Security-Auditing-Examples/dp/183342 Regards -- Claudio
Re: group_key?
Hi, you might have hit: http://bugs.mysql.com/bug.php?id=58081 Cheers Claudio 2012/7/5 Halász Sándor h...@tbbs.net Here is something queer: select ifnull(email, round(1 * rand(), 1)) as ux, count(*) from nam group by ux; ERROR 1062 (23000): Duplicate entry '2514.0' for key 'group_key' I have a name-list, with e-mail address or not. I wanted to fill the NULL e-mail addresses with something random, and, I hope, unique. (ROUND is there only to make that surprising error liklier.) The field email is not UNIQUE, not a key, no intention of making it such. Why this error? Is RAND called more than once for each record? I tryed also UUID, but that came with its own shortcoming: if the UUID call were the whole field, it indeed was once called for every record, but if argument to IFNULL, only once for the whole query. Version 5.5.8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: GA download reverted back to 5.5.24?
+1 2012/6/30 Govinda govinda.webdnat...@gmail.com That was nice of oracle to announce this wasn't it ...(/sarcasm) I am not aligned with any side.. and I am also not known/qualified/respected in this group enough to make much of a statement... but: IMHO, In almost all matters, *appreciation* is the only approach that will serve... let alone sustain happiness... ...and especially when we consider what little we must give to have right to use MySQL. Sure, desire for better communication/usability makes total sense.. but I am just also observing/suggesting: please add (positively) to the atmosphere.. for everyones' sake. Just us humans under the hood. -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Aborted clients
2012/6/13 Johan De Meersman vegiv...@tuxera.be - Original Message - From: Claudio Nanni claudio.na...@gmail.com @Johan, you say I'm having trouble with clients aborting, but for some reason they don't get logged. Ah, it *did* start logging, now, and they come from multiple applications, too. 120612 12:19:09 [Warning] Aborted connection 13019149 to db: 'music' user: 'music' host: 'viaprod1' (Got an error reading communication packets) 120612 13:13:52 [Warning] Aborted connection 13020111 to db: 'epg' user: 'epg' host: 'viaprod1' (Got timeout reading communication packets) 120612 14:21:10 [Warning] Aborted connection 13021624 to db: 'music' user: 'music' host: 'viaprod1' (Got an error reading communication packets) Am I wrong in thinking this looks more like a hardware/network problem? Not at all. Just to close completely the code 'option', are you sure the codebase is completely different? since they still come from the same host. In this way so we can totally exclude code 'bad' habit. Then network can be a problem for sure, usually when there are firewalls in between, also when I had similar problems a network change took place, like changing switches or some configuration. Can you count the hops between MySQL and the app server? Dank Je Wel ;) Claudio -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Claudio
Re: Aborted clients
Johan, Print out warnings such as Aborted connection... to the error log. the dots are not telling if they comprise Aborted clients as well. I find the MySQL error log extremely poor, as far as I know it is one of the MySQL features (like authentication) stuck to the dawn of MySQL times. Very hard to debug non basic things like your issue. From what I have experienced usually Aborted connection means wrong credentials while Aborted clients means the client (typically PHP) did not close the connection properly. Do you have any chance to check if the code is closing the connections to the mysql database? Cheers Claudio 2012/6/12 Johan De Meersman vegiv...@tuxera.be Yo, I'm having trouble with clients aborting, but for some reason they don't get logged. The documentation at http://preview.tinyurl.com/27w9a4x clearly states If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log. The log_warnings variable has been set; originally to 1 and later to 2 because another bit of the doc says If the value is greater than 1, aborted connections are written to the error log. The error.log I'm looking at is the one that is currently opened by the MySQL daemon, as shown by lsof - and does have entries about non-replication-safe queries I'd been doing several days ago. And, yet, I see the Aborted_clients counter increase, but never find any entries in the errorlog - which is annoying, because now I don't even know which application is misbehaving. This is MySQL 5.1.50-community-log on Suse 11.1 64-bit. Does anyone have an idea why the aborted clients don't get logged, and how to fix it? thx, Johan -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Claudio
Re: i need advice on redundancy of mysql server.
Joey, I can not afford any downtime and the data is realtime. This is a very good reason for asking help to consultants. If you ask What is the best method for this setup? master-master or master-slave? then the simple answer is master-slave, for any mysql setup, that is the only safe mysql replication setup. If you mean Master-Master Active/Passive I still consider that Master-Slave. While is very well advisable to stay away from master-master active/active, unless you really know what you are doing and you have the development aware, very skilled, and listening to you*. So, go for master-slave, but I would suggest to read online mysql manuals and some great books, because if you post here to ask every single step to set it up, you are basically asking someone else to do the job for you, for free ;) Cheers Claudio 2012/6/12 Joey L mjh2...@gmail.com It sounds like you are all consultants. On Mon, Jun 11, 2012 at 4:46 PM, Baron Schwartz ba...@xaprb.com wrote: Yeah -- that was an unintentional omission. There are solo consultants like Ronald Bradford too. On Mon, Jun 11, 2012 at 3:14 PM, Andrew Moore eroomy...@gmail.com wrote: Not forgetting Pythian, Baron ;) On Mon, Jun 11, 2012 at 8:12 PM, Baron Schwartz ba...@xaprb.com wrote: Ultimately, if you intend to use MyISAM, you must keep in mind that it eliminates some of your options. One problem is that MyISAM is very slow to repair after a crash. Remember, if a crash can happen, it eventually will, it's just a question of when. And MyISAM doesn't have recovery -- it only has repair, which will not necessarily recover all of your data. If you are not aware of Percona XtraDB Cluster, it might be interesting for you. (I work for Percona.) There is also Continuent Tungsten to consider. Frankly, though, I'd step back a bit from such microscopic focus on technologies. It looks like you need advice from someone who's done this before, to get the high-level things right before you dive deeply into details. If it's really this important, I personally wouldn't trust it to a mailing list, I'd hire someone. It's well worth it. There's Percona again, of course, but there's also MySQL, SkySQL, PalominoDB, and lots more to choose from. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Baron Schwartz Author, High Performance MySQL http://www.xaprb.com/ -- 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 -- Claudio
Re: Aborted clients
Howard, a client can be blacklisted, but in that case is Aborted connection to be increased since the connection request is refused upfront. @Johan, you say I'm having trouble with clients aborting, but for some reason they don't get logged. could you please tell which exactly is the problem? 1) Aborted clients counter gets increased 2) Increasing Aborted clients has a measurable impact on the application 3) ... Thanks Claudio 2012/6/12 Howard Hart h...@ooma.com On 06/12/2012 05:10 AM, Johan De Meersman wrote: - Original Message - From: Claudio Nanniclaudio.na...@gmail.com** Print out warnings such as Aborted connection... to the error log. the dots are not telling if they comprise Aborted clients as well. Hah, how's that for selective blindness. Totally missed that :-) I find the MySQL error log extremely poor, as far as I know it is one of the MySQL features (like authentication) stuck to the dawn of MySQL times. Very hard to debug non basic things like your issue. From what I have experienced usually Aborted connection means wrong credentials while Aborted clients means the client (typically PHP) did not close the connection properly. Yep, that's it; but indeed, since aborted clients aren't logged, then, I seem to be in a ditch. Do you have any chance to check if the code is closing the connections to the mysql database? Oh, yes, millions upon billions of lines of wonderfully obscure Java stacktraces that reveal little more than Lost connection to database for every couple of thousand lines. Everything works fine most of the time, then randomly some queries will get slow, and eventually the connections will drop. Rinse and repeat. Oh well. Thanks for pointing out my reading error, I'm off to lart the devs into profiling their code to figure out *what* causes the slowness. Guess I'll have to set up some tcpdumps, too. Watch out for this one, especially if the Aborted connections are all getting charged against a single client. Per the URL below and a misbehaving application not closing connections correctly, I've seen this spontaneously blacklist a client IP. Only way to unblacklist after is to run flush-hosts on the mysql server. Also, didn't see a one-to-one correspondence between the global max_connect_errors setting and Aborted_connects (from show global status like '%abort%';), so hard to tell when you're approaching the per client blacklist limit. http://dev.mysql.com/doc/**refman/5.0/en/blocked-host.**htmlhttp://dev.mysql.com/doc/refman/5.0/en/blocked-host.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Connect to MySQL server from a c++ application
Simon, yes it is C, C++ here: http://dev.mysql.com/downloads/connector/cpp/ I did not work with this libraries and to be honest I do not know about their performances, If you have the chance it would be extremely useful for the community having some tests done with both APIs. Cheers Claudio 2012/6/8 Simon Walter si...@gikaku.com On 06/08/2012 01:55 AM, Claudio Nanni wrote: Hi, you guys don't like the official API? http://dev.mysql.com/**downloads/connector/c/http://dev.mysql.com/downloads/connector/c/ That's C isn't it? I think there is also a C++ connector. I'm interested to hear how that performs. It seems like a waste of time to write a bunch of wrappers for the C connector. -- simonsmicrophone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Connect to MySQL server from a c++ application
Hi, you guys don't like the official API? http://dev.mysql.com/downloads/connector/c/ Cheers Claudio 2012/6/7 Lars Nilsson chamael...@gmail.com On Thu, Jun 7, 2012 at 3:08 AM, Simon Walter si...@gikaku.com wrote: On 06/07/2012 12:29 PM, Lars Nilsson wrote: I've been happy using SQLAPI++ (http://www.sqlapi.com/) where I work. Commercial and not open source, but it's cross-platform and supports a dozen or so different databases. It looks nice. I'm looking for something open source. I'm fine using one of the SQL connectors. I just need to know which one works. How does SQLAPI++ connect to MySQL? Is it thread safe? It loads the libmysqlclient dll/so libraries under the hood, mapping each database client library's particular function set to its own internal function pointers. I believe it to be thread-safe (pthread mutexes on Linux/Unix, Windows relies on mutex/critical section objects). Instances of SAConnection objects should probably not be used across threads simultaneously though (usual caveats when doing multi-threaded programming apply, etc). I do like the high-level abstraction of the databases, and the use of exceptions for errors so every statement doesn't need to have a check to see if it was successful (just wrap your sequence of operations in a try/catch as makes sense for the application). I know it reduced my database-specific lines of code quite a bit when I changed a MySQL specific program to using SQLAPI++. If one need to, it is always possible to get a native database handle out that can be used with the database-specific API (at which point your program would have to be linked with the required database-specific client libraries, and so on), but it is not something I have really needed personally. If at all possible, I stay in the realm of SQLAPI++ which makes my program independent of the database libraries (implies I do not use native handles). It means I can compile my program without having Oracle installed for instance, and as long as a user has some means of configuring my program so that SA_Oracle_Client is passed to a connection object (mapping from string to the enum value or whatever else make sense), it should just work, given a proper connection string (as long as one handles the special cases properly as outlined in database specific notes for the classes and methods, etc) I'm sorry if I sound like a sales person for SQLAPI++. I have no relation to it, just a satisfied user. Lars Nilsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: multi master auto syncing when servers back on line
Joey,
from what I can see from your email you lack of a lot of basics and I
suggest you to read some documentation before proceeding.
Maatkit is now Percona Toolkit and contains some of the best tools for
MySQL.
Cheers
Claudio
2012/6/5 Joey L mjh2...@gmail.com
Hi -
I have setup mysql mult master setup on debian squeeze.
I have realized that the databases have to be initially in sync before
multi master can operate properly.
This can require a lot of down time on the one functioning server.
Is there a way to do an automatic sync from the 1 server that is still
running ??
I have found a tool dpkg package called Maakit , but having trouble
running it - get this error on the master:
mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
mailserver 192.168.1.11
DBI connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
password: YES) at /usr/bin/mk-table-sync line 1284
can anyone assist with the error ?
Or can someone tell me of a better opensource tool to use to sync the
servers without a mysql dump ? my db is rather large.
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
Re: multi master auto syncing when servers back on line
Joey,
read
thishttp://www.percona.com/doc/percona-xtrabackup/howtos/setting_up_replication.html
to
setup the second instance with no o little service disruption.
then be aware that you have basically two options for Multimaster
replication with two nodes, Active-Passive and Active-Active
Active-Passive can be used for:
-Having a stand by Master using a failover loadbalancer (using only one
active at a time)
Avoid by all means Active-Active unless you have a great team of developers
Claudio
2012/6/6 Andrew Moore eroomy...@gmail.com
Joey,
The master master replication topology comes with it's own potential
pitfalls and trials. Be sure your use case needs master master and that
you're not implementing it because you think it's 'nice to have'.
pt-table-sync should assist you getting your data in sync but how have you
arrived at this out-of-sync predicament you find yourself in?
A
On Tue, Jun 5, 2012 at 11:03 PM, Joey L mjh2...@gmail.com wrote:
with all do respect - I am new to this - i did read the docs and
having a hard time.
I also was asking if you know something easier or does the trick as
this utility does.
thanks
On Tue, Jun 5, 2012 at 5:06 PM, Claudio Nanni claudio.na...@gmail.com
wrote:
Joey,
from what I can see from your email you lack of a lot of basics and I
suggest you to read some documentation before proceeding.
Maatkit is now Percona Toolkit and contains some of the best tools for
MySQL.
Cheers
Claudio
2012/6/5 Joey L mjh2...@gmail.com
Hi -
I have setup mysql mult master setup on debian squeeze.
I have realized that the databases have to be initially in sync before
multi master can operate properly.
This can require a lot of down time on the one functioning server.
Is there a way to do an automatic sync from the 1 server that is still
running ??
I have found a tool dpkg package called Maakit , but having trouble
running it - get this error on the master:
mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
mailserver 192.168.1.11
DBI
connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
password: YES) at /usr/bin/mk-table-sync line 1284
can anyone assist with the error ?
Or can someone tell me of a better opensource tool to use to sync the
servers without a mysql dump ? my db is rather large.
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
Re: ANN: Hopper for MySQL, first public beta available!
Welcome to Hopper! Claudio 2012/5/25 Martijn Tonies m.ton...@upscene.com Dear ladies and gentlemen, Upscene Productions is happy to announce the launch of a new database-developer tool: Hopper (public beta 1) Hopper is a Stored Routine Debugger, the first beta for MySQL is now available. For more information, see http://www.upscene.com/** displaynews.php?item=20120525http://www.upscene.com/displaynews.php?item=20120525 With regards, Martijn Tonies Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Reducing ibdata1 file size
Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Changes, you have a lot of changes. You change the equivalent of 1 gb data a day. Every change is similar to an insert and every delete does not free any disk space. No way to reduce this rate unless you change the app logic. More info needed to provide a hint. Claudio On May 22, 2012 10:50 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.comwrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Jan, that's not common wisdom, Innodb datafiles ***never*** shrink, that in the blog from 22th of May is a workaround, one of the many. If you ask my my favourite is to use a stand by instance and work on that. Claudio 2012/5/22 Jan Steinman j...@bytesmiths.com From: Claudio Nanni claudio.na...@gmail.com No, as already explained, it is not possible, Innodb datafiles *never* shrink. That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Reducing ibdata1 file size
Manivannan, There is no way to reduce the InnoDB main tablespace. You can get rid (=regain disk space) of InnoDB tablespaces only if you have innodb_file_per_table setting, which allows you to get disk space back if you drop the table. The best option you have to free your current shared tablespace is to do a sql dump of the whole instance and reimport into a new one (if you need instructions on how to run multiple mysql instances on the same server just let me know) and then discard the old instance. Cheers Claudio 2012/5/21 Manivannan S. manivanna...@spanservices.com Hi , I am trying to reduce the ibdata1 data file in MySQL. In MySQL data directory the ibdata1 data file is always increasing whenever I am creating a new database and inserting some data into database. If I drop the existing database, the table structures only dropped from the server but data still exist in the ibdata1 data file. How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. Do you have any idea how to solve this problem. Thanks for any feedback. Thanks Manivannan S DISCLAIMER: This email message and all attachments are confidential and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error, please notify us immediately by return email or to mailad...@spanservices.com and destroy the original message. Opinions, conclusions and other information in this message that do not relate to the official business of SPAN, shall be understood to be neither given nor endorsed by SPAN. -- Claudio
Re: multiple instances in win 7 -- any idea
Hi Charles, it is very easy to run multiple mysql instances and of different versions as well on the same server, including windows. You just have to download the .zip version, NOT the installer. The archive you get is basically a separate independent mysql instance that can live fully in its own directory. Unzip the archive/s in separate folders and create/copy the my.ini in the root of that folder changing just two parameters: ex: c:\mysql3331 c:\mysql3332 Instance #1 - file:c:\mysql3331\my.ini [client] port = 3331 socket = /tmp/mysql3331.sock [mysqld] port = 3331 socket = /tmp/mysql3331.sock basedir = c:\mysql3331 Instance #2 - file:c:\mysql3332\my.ini [client] port = 3332 socket = /tmp/mysql3332.sock [mysqld] port = 3332 socket = /tmp/mysql3332.sock basedir = c:\mysql3332 set the right paths and enjoy. you can quickly test if the server starts normally by going into each basedir with the prompt and running bin\mysqld.exe then on different terminals bin\mysql.exe -urootto login Cheers Claudio 2012/5/14 Shawn Green shawn.l.gr...@oracle.com On 5/13/2012 6:53 PM, Brown, Charles wrote: I'm trying to install multiple instances of mysql on windows 7, 64bit. 3hrs into the job, I'm not making progress. Does anyone have an idea? 1) The installers are designed to work on single-instance installs or upgrades. 2) You only need one install to run multiple copies of the same release. The trick is to configure the necessary parts to be unique values between the instances 3) Each instance needs its own copy of unique data. No two active instances can share data. 4) The list of other items that must be unique per instance is listed here: http://dev.mysql.com/doc/**refman/5.5/en/multiple-**servers.htmlhttp://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html 5) (mailing list rule) - avoid hijacking other threads 6) (general support advice) - when having a problem, try to provide descriptive details regarding what you are trying to do, any commands you are using, and what types of failures you are encountering (including any error messages you are receiving). This usually allows anyone trying to help you to respond in a more focused and less general way. Warmest regards, -- 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 -- Claudio
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
In my experience if you have a poor designed code that run the same query for hundreds or thousands of times in a very short timespan (like some programmers do in for-loop instead of using a IN for example) you can put mysql on its knees, in some cases it may be the practical implementation of some lock mechanisms are particularly challenged by this ultra high data 'locality' which bring to very high contention on a few hotspots at different levels (mutexes, indexes, pages). Just reflections :) Claudio 2012/5/14 Baron Schwartz ba...@xaprb.com Argh. I meant to send this to the list but it doesn't have the reply-to set as I expect... the usual gripe On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz ba...@xaprb.com wrote: Johan, On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman vegiv...@tuxera.be wrote: What I fail to understand, Baron, is how there can be a deadlock here - both transactions seem to be hanging on a single-table, single-row update statement. Shouldn't the oldest transaction already have acquired the lock by the time the youngest came around; and shouldn't the youngest simply wait until the eldest finished it's update? Take a look at the output again: 8 === *** (1) TRANSACTION: TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s) update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 14 lock struct(s), heap size 3112, 27 row lock(s) MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480 90.0.0.51 mario Updating update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (1) 8 === Here is how to interpret that: Transaction 1 has locked 27 rows (not just a single row!) and is waiting for an exclusive lock on some row. Transaction 2 holds a shared lock on that same row and is trying to upgraded its shared lock to an exclusive lock. Both transactions have locked 27 rows, so this is not a single-row, single-table problem. It may be the case that it is a single-statement problem, but in that case the statement needs to be optimized somehow so that it does not access too many rows. But there is not enough information to really diagnose what is going on. -- Baron Schwartz Author, High Performance MySQL http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Andrés,
with pleasure.
Imagine a website that is used to search, just for example, hotel rooms for
booking.
It is possible that a programmer would:
1) issue a select that returns the IDs the rooms matching the criteria
2) do a loop in the code scanning each ID of the resultset and for each ID
issue the SELECT to get the details of the Hotel and/or Room (probably and,
using a join) e.g. SELECT.WHERE roomid=123
What happens is that if your search criteria return, for example, 200
rooms, you will issue 200 selects to get the details, those selects are
'twin' selects, that is they are identical with different parameters,
this in my experience is one main cause of contention, keep in mind that a
while loop in php (for example) is extremely fast.
A better approach, always in my experience, is to:
1) issue a select that returns the IDs the rooms matching the criteria
2) issue 1 select to get all the results by using something like: SELECT
,,,WHERE roomid in (1,2,6,123,239,599,...)
3) loop in the resultset and get the details of hotel/room
May be at first look it just look a choice of style, but just imagine this:
you have an application which search part (aforementioned) has 1000 hits
per second,
with the first approach (which I call auto inflicted Denial of Service :) )
you will have 1 + 1000x(200-1000) = ~200k-1M queries
with the second you will have 1+ 1000 queries, no more need to scale out :)
I hope I was enough clear, if not do not hesitate to ask, and please anyone
correct me if I am wrong.
Claudio
2012/5/14 Andrés Tello mr.crip...@gmail.com
Claudio, would you please extend the example to the use of in?
On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni
claudio.na...@gmail.comwrote:
In my experience if you have a poor designed code that run the same query
for hundreds or thousands of times in a very short timespan (like some
programmers do in for-loop instead of using a IN for example) you can put
mysql on its knees, in some cases it may be the practical implementation
of
some lock mechanisms are particularly challenged by this ultra high data
'locality' which bring to very high contention on a few hotspots at
different levels (mutexes, indexes, pages).
Just reflections :)
Claudio
2012/5/14 Baron Schwartz ba...@xaprb.com
Argh. I meant to send this to the list but it doesn't have the
reply-to set as I expect... the usual gripe
On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz ba...@xaprb.com
wrote:
Johan,
On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman
vegiv...@tuxera.be
wrote:
What I fail to understand, Baron, is how there can be a deadlock
here -
both transactions seem to be hanging on a single-table, single-row
update
statement. Shouldn't the oldest transaction already have acquired the
lock
by the time the youngest came around; and shouldn't the youngest simply
wait until the eldest finished it's update?
Take a look at the output again:
8 ===
*** (1) TRANSACTION:
TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
update `account` set `balance`= 0.00 + '-6.07' where
accountid='3235296'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but
not
gap waiting
*** (2) TRANSACTION:
TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
declared
inside InnoDB 500
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 27 row lock(s)
MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
32378480
90.0.0.51 mario Updating
update `account` set `balance`= 0.00 + '-1.37' where
accountid='3235296'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but
not
gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but
not
gap waiting
*** WE ROLL BACK TRANSACTION (1)
8 ===
Here is how to interpret that: Transaction 1 has locked 27 rows (not
just a single row!) and is waiting for an exclusive lock on some row.
Transaction 2 holds a shared lock on that same row and is trying to
upgraded its shared lock to an exclusive lock.
Both transactions have locked 27 rows, so this is not a single-row,
single-table problem. It may be the case that it is a single-statement
problem, but in that case the statement needs to be optimized somehow
so that it does not access too many rows
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Hello Andrés
did you notice that both transactions are trying to update records with
same *accountid='3235296' *
and that they lock the same index page? *space id 5806 page no 69100 n bits
176 index*
Cheers
Claudio
2012/5/11 Andrés Tello mr.crip...@gmail.com
Ok, so I had a deadlock...
But then, why a deadlock doesn't rollback all the transaccion?
On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz ba...@xaprb.com wrote:
Deadlocks and lock wait timeouts are independent of one another. A
deadlock happens when there is a cycle in the waits-for graph. Your
transactions are *active* for 132 and 33 seconds, but the deadlock
happens at the instant the conflict is detected, not after waiting. A
deadlock cannot be resolved by waiting, by definition. Hence the name,
deadlock. The only way to resolve it is to choose a victim.
On Fri, May 11, 2012 at 3:06 AM, Andrés Tello mr.crip...@gmail.com
wrote:
Ok... I have one of those pesky error, in an application not handling
deadlocks or lockwaits.
The database object can't be modified to support deadlock/lockwatis...
I can only change database parameteres
Database info: Server version: 5.5.22-log Source distribution
from show engine innodb status;
{abstract}
*** (1) TRANSACTION:
TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
update `account` set `balance`= 0.00 + '-6.07' where
accountid='3235296'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but
not
gap waiting
*** (2) TRANSACTION:
TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
declared
inside InnoDB 500
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 27 row lock(s)
MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
32378480
90.0.0.51 mario Updating
update `account` set `balance`= 0.00 + '-1.37' where
accountid='3235296'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but
not
gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but
not
gap waiting
*** WE ROLL BACK TRANSACTION (1)
The issue is that I had a lock for over 132 seconds and the other was
waiting for 33 seconds, so I get a lockwait.
accountid is locked by a
select balance from account where accountid='3235296' lock in shared
mode
How can I tell mysql to wait longer? I know the process which is doing
the
deadlock, is a long balance process... I know that it takes time,
sometives
over 15 minutes, but they always resolve...
How Can I tell mysql to wait for the lock as needed? like for over 12
minutes?
TIA
--
Baron Schwartz
Author, High Performance MySQL
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
Re: Deadlock due lockwait. How can I tell mysql to wait longer?
Andrés,
may be you can enable the general log, recreate the deadlock,
and attach the general log?
If I had to reason as InnoDB, what I see is two updates statements that
arrive and want to update the same record,
I would be confused exactly as InnoDB is because I would not know which
update is the 'good' one,
I'd close my eyes and kill one.
This is a deadlock.
Claudio
2012/5/11 Andrés Tello mr.crip...@gmail.com
Yup, but a far I understand...
I made a
select balance for update where accountid=3235296 lock in shared mode;
over the same accountid , so the second transacion just would need to
wait to the first transaccion to finish...
That is why I'm confuse if I have a Deadlock o a wait lock...
That is why I'm
On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni claudio.na...@gmail.comwrote:
Hello Andrés
did you notice that both transactions are trying to update records with
same *accountid='3235296' *
and that they lock the same index page? *space id 5806 page no 69100 n
bits 176 index*
Cheers
Claudio
2012/5/11 Andrés Tello mr.crip...@gmail.com
Ok, so I had a deadlock...
But then, why a deadlock doesn't rollback all the transaccion?
On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz ba...@xaprb.com wrote:
Deadlocks and lock wait timeouts are independent of one another. A
deadlock happens when there is a cycle in the waits-for graph. Your
transactions are *active* for 132 and 33 seconds, but the deadlock
happens at the instant the conflict is detected, not after waiting. A
deadlock cannot be resolved by waiting, by definition. Hence the name,
deadlock. The only way to resolve it is to choose a victim.
On Fri, May 11, 2012 at 3:06 AM, Andrés Tello mr.crip...@gmail.com
wrote:
Ok... I have one of those pesky error, in an application not handling
deadlocks or lockwaits.
The database object can't be modified to support
deadlock/lockwatis...
I can only change database parameteres
Database info: Server version: 5.5.22-log Source distribution
from show engine innodb status;
{abstract}
*** (1) TRANSACTION:
TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
update `account` set `balance`= 0.00 + '-6.07' where
accountid='3235296'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
of
table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec
but not
gap waiting
*** (2) TRANSACTION:
TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
declared
inside InnoDB 500
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 27 row lock(s)
MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
32378480
90.0.0.51 mario Updating
update `account` set `balance`= 0.00 + '-1.37' where
accountid='3235296'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
of
table `f_database`.`account` trx id 5F7A398 lock mode S locks rec
but not
gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
of
table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec
but not
gap waiting
*** WE ROLL BACK TRANSACTION (1)
The issue is that I had a lock for over 132 seconds and the other was
waiting for 33 seconds, so I get a lockwait.
accountid is locked by a
select balance from account where accountid='3235296' lock in shared
mode
How can I tell mysql to wait longer? I know the process which is
doing
the
deadlock, is a long balance process... I know that it takes time,
sometives
over 15 minutes, but they always resolve...
How Can I tell mysql to wait for the lock as needed? like for over 12
minutes?
TIA
--
Baron Schwartz
Author, High Performance MySQL
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
--
Claudio
Re: One table gets locked by itself
Yes indeed, but I think we are talking about MySQL level deadlocks, that can happen only with row level locking and transactions. If the deadlock is generated at application level then you can have it on anything, also blackhole :-) Claudio 2012/5/9 Johan De Meersman vegiv...@tuxera.be - Original Message - From: nixofortune nixofort...@gmail.com Few more things. You can't have a deadlock on Mylsam table. You can You *can* have deadlocks in MyISAM; the concept is not related to any specific engine - or even databases. What you can't have, is deadlock on a single table :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: 回复： Why is creating indexes faster after inserting massive data rows?
This thread is going on and on and on and on, does anyone have time to actually measure I/O? Let's make numbers talk. Claudio 2012/5/9 Rick James rja...@yahoo-inc.com A BTree that is small enough to be cached in RAM can be quickly maintained. Even the “block splits” are not too costly without the I/O. A big file that needs sorting �C bigger than can be cached in RAM �C is more efficiently done with a dedicated “sort merge” program. A “big” INDEX on a table may be big enough to fall into this category. I/O is the most costly part of any of these operations. My rule of thumb for MySQL SQL statements is: If everything is cached, the query will run ten times as fast as it would if things have to be fetched from disk. Sortmerge works this way: 1. Sort as much of the file as you can in RAM. Write that sorted piece to disk. 2. Repeat for the next chunk of the file. Repeat until the input file is broken into sorted chunks. 3. Now, “merge” those chunks together �C take the first row from each, decide which is the “smallest”, send it to the output 4. Repeat until finished with all the pieces. For a really big task, there may have to be more than on “merge” pass. Note how sort merge reads the input sequentially once, writes the output sequentially once, and has sequential I/O for each merge chunk. “Sequential” I/O is faster than “random” I/O �C no arm motion on traditional disks. (SSDs are a different matter; I won’t go into that.) The “output” from the sortmerge is fed into code that builds the BTree for the table. This building of the BTree is sequential �C fill the first block, move on to the next block, and never have to go back. BTrees (when built randomly), if they need to spill to disk, will involve random I/O. (And we are talking about an INDEX that is so big that it needs to spill to disk.) When a block “splits”, one full block becomes two half-full blocks. Randomly filling a BTree leads to, on average, the index being 69% full. This is not a big factor in the overall issue, but perhaps worth noting. How bad can it get? Here’s an example. ・ You have an INDEX on some random value, such as a GUID or MD5. ・ The INDEX will be 5 times as big as you can fit in RAM. ・ MySQL is adding to the BTree one row at a time (the non-sortmerge way) When it is nearly finished, only 1 of 5 updates to the BTree can be done immediately in RAM; 4 out of 5 updates to the BTree will have to hit disk. If you are using normal disks, that is on the order of 125 rows per second that you can insert �C Terrible! Sortmerge is likely to average over 10,000. From: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn] Sent: Tuesday, May 08, 2012 9:13 PM To: Rick James Cc: mysql@lists.mysql.com Subject: 回复： Why is creating indexes faster after inserting massive data rows? James... * By doing all the indexes after building the table (or at least all the non-UNIQUE indexes), sort merge can be used. This technique had been highly optimized over the past half-century, and is more efficient. I have a question about sort merge: Why does it do the all sort merge? In my opinion, it just maintains the B tree and inserts one key into a B tree node which has fewer sorted keys, so it is good performance. If it only does the sort merge, the B tree data structure have to been created separately. it wastes some performance. Does it? 发件人： Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com 收件人： Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be; Zhangzhigang zzgang_2...@yahoo.com.cnmailto:zzgang_2...@yahoo.com.cn 抄送： mysql@lists.mysql.commailto:mysql@lists.mysql.com mysql@lists.mysql.commailto:mysql@lists.mysql.com 发送日期： 2012年5月8日, 星期二, 上午 12:35 主题: RE: Why is creating indexes faster after inserting massive data rows? * Batch INSERTs run faster than one-row-at-a-time, but this is unrelated to INDEX updating speed. * The cache size is quite important to dealing with indexing during INSERT; see http://mysql.rjweb.org/doc.php/memory http://mysql.rjweb.org/doc.php/memory%0A * Note that mysqldump sets up for an efficient creation of indexes after loading the data. This is not practical (or necessarily efficient) when incremental INSERTing into a table. As for the original question... * Updating the index(es) for one row often involves random BTree traversals. When the index(es) are too big to be cached, this can involve disk hit(s) for each row inserted. * By doing all the indexes after building the table (or at least all the non-UNIQUE indexes), sort merge can be used. This technique had been highly optimized over the past half-century, and is more efficient. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.bemailto: vegiv...@tuxera.be] Sent: Monday, May 07, 2012 1:29 AM To: Zhangzhigang Cc:
RE: 回复： Why is creating indexes faster after inserting massive data rows?
Disagree all the way, numbers are numbers, and better than words, always. Claudio On May 9, 2012 7:22 PM, Rick James rja...@yahoo-inc.com wrote: Numbers can be misleading �C one benchmark will show no difference; another will show 10x difference. Recommend you benchmark _*your*_ case. ** ** *From:* Claudio Nanni [mailto:claudio.na...@gmail.com] *Sent:* Wednesday, May 09, 2012 8:34 AM *To:* Rick James *Cc:* Zhangzhigang; mysql@lists.mysql.com *Subject:* Re: 回复： Why is creating indexes faster after inserting massive data rows? ** ** This thread is going on and on and on and on, does anyone have time to actually measure I/O? Let's make numbers talk. ** ** Claudio ** ** 2012/5/9 Rick James rja...@yahoo-inc.com A BTree that is small enough to be cached in RAM can be quickly maintained. Even the “block splits” are not too costly without the I/O. A big file that needs sorting �C bigger than can be cached in RAM �C is more efficiently done with a dedicated “sort merge” program. A “big” INDEX on a table may be big enough to fall into this category. I/O is the most costly part of any of these operations. My rule of thumb for MySQL SQL statements is: If everything is cached, the query will run ten times as fast as it would if things have to be fetched from disk. Sortmerge works this way: 1. Sort as much of the file as you can in RAM. Write that sorted piece to disk. 2. Repeat for the next chunk of the file. Repeat until the input file is broken into sorted chunks. 3. Now, “merge” those chunks together �C take the first row from each, decide which is the “smallest”, send it to the output 4. Repeat until finished with all the pieces. For a really big task, there may have to be more than on “merge” pass. Note how sort merge reads the input sequentially once, writes the output sequentially once, and has sequential I/O for each merge chunk. “Sequential” I/O is faster than “random” I/O �C no arm motion on traditional disks. (SSDs are a different matter; I won’t go into that.) The “output” from the sortmerge is fed into code that builds the BTree for the table. This building of the BTree is sequential �C fill the first block, move on to the next block, and never have to go back. BTrees (when built randomly), if they need to spill to disk, will involve random I/O. (And we are talking about an INDEX that is so big that it needs to spill to disk.) When a block “splits”, one full block becomes two half-full blocks. Randomly filling a BTree leads to, on average, the index being 69% full. This is not a big factor in the overall issue, but perhaps worth noting. How bad can it get? Here’s an example. ・ You have an INDEX on some random value, such as a GUID or MD5. ・ The INDEX will be 5 times as big as you can fit in RAM. ・ MySQL is adding to the BTree one row at a time (the non-sortmerge way) When it is nearly finished, only 1 of 5 updates to the BTree can be done immediately in RAM; 4 out of 5 updates to the BTree will have to hit disk. If you are using normal disks, that is on the order of 125 rows per second that you can insert �C Terrible! Sortmerge is likely to average over 10,000. From: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn] Sent: Tuesday, May 08, 2012 9:13 PM To: Rick James Cc: mysql@lists.mysql.com Subject: 回复： Why is creating indexes faster after inserting massive data rows? James... * By doing all the indexes after building the table (or at least all the non-UNIQUE indexes), sort merge can be used. This technique had been highly optimized over the past half-century, and is more efficient. I have a question about sort merge: Why does it do the all sort merge? In my opinion, it just maintains the B tree and inserts one key into a B tree node which has fewer sorted keys, so it is good performance. If it only does the sort merge, the B tree data structure have to been created separately. it wastes some performance. Does it? 发件人： Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com 收件人： Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be; Zhangzhigang zzgang_2...@yahoo.com.cnmailto:zzgang_2...@yahoo.com.cn 抄送： mysql@lists.mysql.commailto:mysql@lists.mysql.com mysql@lists.mysql.commailto:mysql@lists.mysql.com 发送日期： 2012年5月8日, 星期二, 上午 12:35 主题: RE: Why is creating indexes faster after inserting massive data rows? * Batch INSERTs run faster than one-row-at-a-time, but this is unrelated to INDEX updating speed. * The cache size is quite important to dealing with indexing during INSERT; see http://mysql.rjweb.org/doc.php/memory http://mysql.rjweb.org/doc.php/memory%0A * Note that mysqldump sets up for an efficient creation of indexes after loading the data. This is not practical (or necessarily efficient) when incremental
RE: One table gets locked by itself
Not really. If its a deadlock , they all are victims. (E.g. mutex wait) Or a long running query (sending data) might be the guy. Claudio On May 8, 2012 7:31 PM, Rick James rja...@yahoo-inc.com wrote: Also,... If it happens again, do SHOW FULL PROCESSLIST while it is happening. Usually the non-Locked entry is the villain. -Original Message- From: Darryle Steplight [mailto:dstepli...@gmail.com] Sent: Tuesday, May 08, 2012 7:32 AM To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- 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: One table gets locked by itself
Right, Technically not a deadlock, Practically yes if hundreds of threads are waiting on the same mutex, Like key cache one or query cache or any other mutex. Claudio On May 8, 2012 7:51 PM, nixofortune nixofort...@gmail.com wrote: Few more things. You can't have a deadlock on Mylsam table. You can check status of your tables in use with: SHOW OPEN TABLES WHERE IN_USE !=0 you might check mysqld error log ad well On 8 May 2012 18:42, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi
Re: 回复： Why is creating indexes faster after inserting massive data rows?
Creating the index in one time is one macro-sort operation, updating the index at every row is doing the operation on and on again. If you do not understand the difference I recommend you to read some basics about sorting algorithms, very interesting read anyway. Claudio 2012/5/7 Zhangzhigang zzgang_2...@yahoo.com.cn johan Plain and simple: the indices get updated after every insert statement, whereas if you only create the index *after* the inserts, the index gets created in a single operation, which is a lot more efficient.. Ok, Creating the index *after* the inserts, the index gets created in a single operation. But the indexes has to be updating row by row after the data rows has all been inserted. Does it work in this way? So i can not find the different overhead about two ways. 发件人： Johan De Meersman vegiv...@tuxera.be 收件人： Zhangzhigang zzgang_2...@yahoo.com.cn 抄送： mysql@lists.mysql.com 发送日期： 2012年5月7日, 星期一, 下午 4:28 主题: Re: Why is creating indexes faster after inserting massive data rows? - Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn Creating indexes after inserting massive data rows is faster than before inserting data rows. Please tell me why. Plain and simple: the indices get updated after every insert statement, whereas if you only create the index *after* the inserts, the index gets created in a single operation, which is a lot more efficient. I seem to recall that inside of a transaction (thus, InnoDB or so) the difference is markedly less; I might be wrong, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Claudio
Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows?
too nice not to share it! http://www.youtube.com/watch?v=INHF_5RIxTE 2012/5/7 Zhangzhigang zzgang_2...@yahoo.com.cn Thanks, i thought about this answer in the past, and i appreciate your reply. 发件人： Alex Schaft al...@quicksoftware.co.za 收件人： mysql@lists.mysql.com 发送日期： 2012年5月7日, 星期一, 下午 4:59 主题: Re: 回复： Why is creating indexes faster after inserting massive data rows? On 2012/05/07 10:53, Zhangzhigang wrote: johan Plain and simple: the indices get updated after every insert statement, whereas if you only create the index *after* the inserts, the index gets created in a single operation, which is a lot more efficient.. Ok, Creating the index *after* the inserts, the index gets created in a single operation. But the indexes has to be updating row by row after the data rows has all been inserted. Does it work in this way? So i can not find the different overhead about two ways. My simplified 2c. When inserting rows with active indexes one by one (insert), mysql has to 1) lock the space for the data to be added, 2) write the data, 3) lock the index, 4) write the index key(s), 5) unlock the index, 6)unlock the data This happens for each row When first doing all data without index, only 1, 2, and 6 happen. When you then create an index, it can lock the index, read all the data and write all index keys in one go and then unlock the index. If you make an omelet, do you fetch your eggs from the fridge one by one, or all at the same time? :) HTH, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Free webinar about MySQL problem diagnosis
Thanks Baron! Very much valuable! Looking forward for it! Claudio 2012/5/4 Baron Schwartz ba...@xaprb.com I'll present a free webinar today about troubleshooting intermittent MySQL problems. These are often hard to pin down because they happen when you're not looking, so you can't reliably determine the symptoms or the cause. I've created some free tools (part of Percona Toolkit) to make this process much more efficient, and I'll explain how to use the tools to help gather the information needed for a good diagnosis. I'll show some real case studies from the hundreds of customer problems solved with this process and toolset. http://oreillynet.com/pub/e/2216 Friday, May 4, 2012 10AM PT, San Francisco 6pm - London | 1pm - New York | Sat, May 5th at 3am - Sydney | Sat, May 5th at 2am - Tokyo | Sat, May 5th at 1am - Beijing | 10:30pm - Mumbai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Can the mysql replication limited to one database
I recommend to use the 'wild' modifier, if you have a default db and you specify the schema in the query like update schemanotreplicated.mytable. you will miss that. Claudio 2012/5/2 Rick James rja...@yahoo-inc.com Yes, doable. In my.cnf on master: Binlog-do-database = dbname1 Binlog-do-database = dbname2 Would replicate those two dbs only. There are many other combinations using binlog/replicate-do/ignore-db/table/wild. See the manual on replication, and especially the flowchart on how those interact (sometimes in unexpected ways). -Original Message- From: Brown, Charles [mailto:cbr...@bmi.com] Sent: Wednesday, May 02, 2012 8:44 AM To: Rick James Cc: mysql@lists.mysql.com Subject: Can the mysql replication limited to one database Hi Gurus, Can the mysql replication limited to selected schema or database? I've been asked to set up mysql for only 1 out 5 databases exist in production. Please advise if this is doable Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: engine is now innoDB instead of MyISAM.
Charles, the pleasure is all mine in meeting such a kind person as you are. To reply to your question, I would recommend to read frst a basic document about MySQL Cluster, like the whitepaper (http://kae.li/iiisv) so that you have an overview of the infrastructure. Consider that only the SQL node(that is just a regular MySQL instance) and the Management console have a client command to access the node and the command is different, the regular 'mysql' client command is used to access the SQL node (which as said is just a regular MySQL instance with added NDB support), and to access the management console the command is 'ndb_mgm'. You cannot access the Data nodes with a client command, those nodes communicate with other cluster nodes but not with the user. Please feel free to ask any further question. Thanks Claudio 2012/4/29 Brown, Charles cbr...@bmi.com Hello Claudio, ** ** In all honesty, the chances of coming into contact with an individual like you who has a grounded knowledge and an ability to articulate thoughts is perhaps a once in a life time experience. However, I meant “MySQL Cluster as a product” . How can one identify these three different types of nodes. Are there any useful diagnostics command that I can issue in order to identify which node is Data, Management or SQL node. Please assist me. See below for the logon messages ** ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 374141 Server version: 5.5.19-ndb-7.2.4-gpl MySQL Cluster Community Server (GPL)* *** ** ** Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. ** ** Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. ** ** Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ** ** mysql ** ** ** ** ** ** ** ** *From:* Claudio Nanni [mailto:claudio.na...@gmail.com] *Sent:* Saturday, April 28, 2012 2:48 PM *To:* Brown, Charles *Cc:* mysql@lists.mysql.com *Subject:* Re: engine is now innoDB instead of MyISAM. ** ** Charles, ** ** first, my opinion is that if you are paying Oracle you should get answers from them about this, and then of course is good to have a second opinion here. ** ** Related to your specific question you should specify what you mean with 'clustering' which is a generic term, if with it you mean MySQL Cluster product then you have to know that this implementation of database cluster is based on a specific storage engine called Network DataBase (NDB), so you can't choose another engine for it. The default storage engine in the SQL nodes of a MySQL Cluster can be any storage engine and whether it be MyISAM, InnoDB, NDB and it's a design decision, but only NDB tables will benefit from the clustering capabilities. ** ** I hope I got your question right and that this shed a bit more light for you. ** ** Cheers ** ** Claudio ** ** 2012/4/28 Brown, Charles cbr...@bmi.com The question is, is it advisable to change the default engine of a cluster setting to Myisam? Does anyone have an opinion or experience on this issue? Do I have choice when it comes to clustering on which type engine supports clustering. Thanks -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Saturday, April 28, 2012 3:01 AM To: mysql@lists.mysql.com Subject: Re: engine is now innoDB instead of MyISAM. Am 28.04.2012 09:00, schrieb Brown: We recently switched from MySQL community to Mysql clustered using Oracle supported MySQl. The problem is, during our testing phase, we observed the default engine is now innoDB instead of MyISAM. Any thoughts on that? We're not getting the performance that we expected - any thoughts or advices out there will be greatly appreciated. my.cnf: default-storage-engine = myisam you cann not compare MyISAm and InnoDB for the same workloads that's why my.cnf exists and we usually define each known setting to prevent chnages in the behavior of applications by software-updates the same for php.ini This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql ** ** -- Claudio
RE: engine is now innoDB instead of MyISAM.
Charles, I think going back to MyISAM as default is a good idea. All you have to do is: Export all databases .Shutdown the cluster MySQL .Add one parameter to your old my.cnf , default-storage-engine=myisam .add cluster specific parameters to your my.cnf , they should be a couple .Put back your my.cnf on all SQL nodes .restart the cluster .reimport your dump , which should drop all Innodb tables I'm on a bus now I can't check better ;) Cheers Claudio On Apr 29, 2012 2:50 PM, Brown, Charles cbr...@bmi.com wrote: Hello Claudio, ** ** Thanks for quick response and the whitepaper. What precipitated my research and questions is a complain I received from one of my developer that after following our MySQL Cluster migration, tables are now defaulting to innodb ( how can he tell) and performance is poor. I’m very much concerned worried because my cnf doesn’t reflect any parameter changes or tuning opportunities for innodb – I’m using all defaults since we are not using innodb storage engine. Our MySQL implementation is very simple and limited. Ours model most large IT shops meaning it provides nothing but a nice, fancy glossary front end ( meaning placed outside the cooperate fire wall). Inside this cooperate firewall resides myriads of industry strength databases and data structures. The white paper warns not to expect much from MySQL Cluster until it’s optimized. Very little was said about Myisam. Most of the discussions were centered on innordb and NDB. Thus my objective is clearly laid out. I may have to convert all tables back to MyISAM or invest time in tuning. Your thoughts ? ** ** Lastly, can I bring over old performance parameters found in my.cnf. These are parameters that have sustain me over the years. Now, I’m doing MySql Cluster are they any more relevant. ** ** Best regards, ** ** ** ** ** ** *From:* Claudio Nanni [mailto:claudio.na...@gmail.com] *Sent:* Sunday, April 29, 2012 4:29 AM *To:* Brown, Charles *Cc:* mysql@lists.mysql.com *Subject:* Re: engine is now innoDB instead of MyISAM. ** ** Charles, ** ** the pleasure is all mine in meeting such a kind person as you are. ** ** To reply to your question, I would recommend to read frst a basic document about MySQL Cluster, like the whitepaper (http://kae.li/iiisv) so that you have an overview of the infrastructure. ** ** Consider that only the SQL node(that is just a regular MySQL instance) and the Management console have a client command to access the node and the command is different, the regular 'mysql' client command is used to access the SQL node (which as said is just a regular MySQL instance with added NDB support), and to access the management console the command is 'ndb_mgm'.** ** ** ** You cannot access the Data nodes with a client command, those nodes communicate with other cluster nodes but not with the user. ** ** Please feel free to ask any further question. ** ** Thanks ** ** Claudio ** ** 2012/4/29 Brown, Charles cbr...@bmi.com Hello Claudio, In all honesty, the chances of coming into contact with an individual like you who has a grounded knowledge and an ability to articulate thoughts is perhaps a once in a life time experience. However, I meant “MySQL Cluster as a product” . How can one identify these three different types of nodes. Are there any useful diagnostics command that I can issue in order to identify which node is Data, Management or SQL node. Please assist me. See below for the logon messages Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 374141 Server version: 5.5.19-ndb-7.2.4-gpl MySQL Cluster Community Server (GPL)* *** Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql *From:* Claudio Nanni [mailto:claudio.na...@gmail.com] *Sent:* Saturday, April 28, 2012 2:48 PM *To:* Brown, Charles *Cc:* mysql@lists.mysql.com *Subject:* Re: engine is now innoDB instead of MyISAM. Charles, first, my opinion is that if you are paying Oracle you should get answers from them about this, and then of course is good to have a second opinion here. Related to your specific question you should specify what you mean with 'clustering' which is a generic term, if with it you mean MySQL Cluster product then you have to know that this implementation of database cluster is based on a specific storage engine called Network DataBase (NDB), so you can't choose another engine
Re: engine is now innoDB instead of MyISAM.
Charles, first, my opinion is that if you are paying Oracle you should get answers from them about this, and then of course is good to have a second opinion here. Related to your specific question you should specify what you mean with 'clustering' which is a generic term, if with it you mean MySQL Cluster product then you have to know that this implementation of database cluster is based on a specific storage engine called Network DataBase (NDB), so you can't choose another engine for it. The default storage engine in the SQL nodes of a MySQL Cluster can be any storage engine and whether it be MyISAM, InnoDB, NDB and it's a design decision, but only NDB tables will benefit from the clustering capabilities. I hope I got your question right and that this shed a bit more light for you. Cheers Claudio 2012/4/28 Brown, Charles cbr...@bmi.com The question is, is it advisable to change the default engine of a cluster setting to Myisam? Does anyone have an opinion or experience on this issue? Do I have choice when it comes to clustering on which type engine supports clustering. Thanks -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Saturday, April 28, 2012 3:01 AM To: mysql@lists.mysql.com Subject: Re: engine is now innoDB instead of MyISAM. Am 28.04.2012 09:00, schrieb Brown: We recently switched from MySQL community to Mysql clustered using Oracle supported MySQl. The problem is, during our testing phase, we observed the default engine is now innoDB instead of MyISAM. Any thoughts on that? We're not getting the performance that we expected - any thoughts or advices out there will be greatly appreciated. my.cnf: default-storage-engine = myisam you cann not compare MyISAm and InnoDB for the same workloads that's why my.cnf exists and we usually define each known setting to prevent chnages in the behavior of applications by software-updates the same for php.ini This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: show master status; show binary logs
Hi Halasz, This happens quite often. It can be because more meaningful commands, or more compliant to the standard are found, or commands more consistent with the rest of the syntax. for instance look at slow query log parameter change herehttp://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html Usually not to create problems alias are introduced for quite long time and then only at a certain point (very late) made obsolete. You may read the Release Notes of the release you are using, and/or the previous ones, to know what changes have been done. In any case it shouldn't represent any problem. Cheers Claudio 2012/4/17 Halász Sándor h...@tbbs.net In the command show binary logs one may indifferently write binary or master, and it is so for some other commands associated with this function--but for the command show master status there is no such variant. Why? Is it considered obsolescent? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Shared memory protocol can not be accessed in windows
Red carpet for you Using shared memory! last one was Monty while developing MySQL 3.x!!! Sorry Siva, A bit of fun about MySQL on windows shared-memory protocol :) I have no experience on Shared Memory protocol and as me I think 99.999% of MySQL users. I think it would be way more appropriate to use TCP/IP so that (1) you can migrate to Linux/Mac anytime hassle-free (2) find a zillion people ready to help you. If you still want to be helped with this protocol please provide: MySQL Version my.ini mysql full configuration report (you may use mysql -h127.0.0.1 -P3306 -uroot -e show variablesthis will work if no root password is set, otherwise add the -p parameter) Thanks! Claudio 2012/4/14 SIVASUTHAN NADARAJAH nsivasut...@live.com I used windows 7 OS. I try to connect to the MySQL server locally using shared memory prtotocolthis error message comes. Any one, can you tell me how to figure out this? C:\mysql -h localhost -u root --protocol=memory --enable-shared-memory -p Enter password: ERROR 2038 (HY000): Can't open shared memory; client could not create request event (2)C:\ Also I am unable to use nt-pipe protocol.C:\mysql -h localhost -u root --protocol=pipe --enable-named-pipe -p mysql: unknown option '--enable-named-pipe'C:\ Thanks.Sivasuthan. -- Claudio
Re: Keynote videos from Percona Live MySQL Conference
Thanks Baron! very much appreciated! Claudio 2012/4/13 Baron Schwartz ba...@xaprb.com If you were not at the Percona Live MySQL Conference over the last few days, the keynote videos are recorded for your convenience. You can see them at http://www.percona.tv/ Presentations will be posted at http://www.percona.com/live/ as well, after the speakers submit them to us for posting. I will mention them when they're ready. - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Hi you have to see the reason in the mysql log file, that is a file either in the datadir with .err extension or in the /var/log directory. tail the last 30 lines you will see the reason why it failed to start. Claudio 2012/4/11 Prabhat Kumar aim.prab...@gmail.com did you check permission of file /var/run/mysqld/mysqld.sock On Wed, Apr 11, 2012 at 9:48 AM, Larry Martell larry.mart...@gmail.com wrote: On Wed, Apr 11, 2012 at 2:51 AM, Ganesh Kumar bugcy...@gmail.com wrote: Hi Guys, I am using debian squeeze it's working good, I am trying to install mysql-server. mysql-server installation successfully but didn't start service root@devel:/var/run# more /etc/mysql/my.cnf |grep socket # Remember to edit /etc/mysql/debian.cnf when changing the socket location. socket = /var/run/mysqld/mysqld.sock socket = /var/run/mysqld/mysqld.sock socket = /var/run/mysqld/mysqld.sock root@devel:~# /etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed! root@devel:~# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) root@devel:~# cd /var/run/mysqld/ root@devel:/var/run/mysqld# ls root@devel:/var/run/mysqld# Is selinux enabled? If so, check the logs for that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Claudio
Re: Licensing question about mysql_com.h
And in europe we cannot watch all the american TV Series online :( 2012/4/11 Paul Vallee val...@pythian.com If you own the code, you can license it under multiple licenses. Kind of like if you own a TV Show, you can license it in the US under one contract, and in other geographies under other more or less restrictive contracts. This is a painful reality to those of us in Canada, as we can't watch South Park clips online. :P On Tue, Apr 10, 2012 at 4:53 AM, James Ots my...@jamesots.com wrote: In their blog post, announcing the sharing of their work, they mention licensing it under BSD, but in the repository the COPYING file still contains the GPLv2 licence, so I'm not sure what's going on there. On 10 April 2012 02:32, Andrew Moore eroomy...@gmail.com wrote: So what's the deal with Twitter's mysql code...how can it be BSD licensed? I'm a bit unsure about the intricacies of licensing. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- Discover the latest MySQL tips and tricks from Pythian’s top talent at this year’s MySQL Conference April 10-12. Details at pythian.com/news -- Claudio
Re: Out of office replies
+1 2012/4/9 Noel Butler noel.but...@ausics.net ** People, most of you are/should be professionals. It is about time your mail servers were configured to never send out of office bullshit replies in response to mailing list messages. I realise this is difficult here and is oracles fault for running an abandonware mail server (qmail) and antiquated list server (ezmlm) that fails to send Precedence headers, but come on now, do your part since oracle have no clue, I mean you don't want your mail servers entered into DNSBL's now do you... /rant -- Claudio
Re: Questions about mysql-proxy...
Wes, Thanks for these questions about this 'ghost' of the MySQL world, it seems more a legend than a real thing! I am sorry I do not have the answers but I would love to hear some. All I can say is that MySQL Proxy is currently (still) in Alpha http://dev.mysql.com/downloads/mysql-proxy/ https://launchpad.net/mysql-proxy so it is unlikely to be used in production. Such a shame that it was not developed further, this and other features (like online backups) really miss in MySQL, according to me. Cheers Claudio 2012/4/4 Wes Modes wmo...@ucsc.edu I asked these questions in context of my clustering enquiries, but here it is more specific to mysql-proxy: 1. First, what is the best place to ask specific questions about mysql-proxy? 2. Does the proxy sit on a separate server and route all MySQL requests, or is it installed on each of the MySQL nodes and re-shuffle MySQL requests to the appropriate place? 3. Can multiple proxies be run in concert to provide redundancy and scalability as well as eliminate SPoF and bottlenecks? 4. In 2007 when RW Splitting was new, there were a few problems and limitations. What is the current status of development of this important feature? Thanks! Wes -- Wes Modes Systems Designer, Developer, and Administrator University Library ITS University of California, Santa Cruz -- Claudio
Re: Can't connect as non-root user to database
Hi, 1. FLUSH PRIVILEGES is not needed, the SQL commands to manage user and grants reload automatically the grant tables, that was used in the very past when people use to tinker directly the grant tables. 2. you did not specify the @ part of the 'someone' : GRANT ALL ON somedb.* TO 'someone'@'.' IDENTIFIED BY 'somepass'; cheers Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com Hi, All I would like to do is the create a small database with a non-root user which is allowed to access the db, however after hours of trying I gave up. I am using MySQL-5.5.20 on Fedora16 . CREATE USER 'someone'@'%' IDENTIFIED BY 'somepass'; CREATE DATABASE somedb; GRANT ALL ON somedb.* TO 'someone' IDENTIFIED BY 'somepass'; FLUSH PRIVILEGES; However, when I try to log in to somedb using someone, I always get: mysql --user=someone -p somedb Enter password: ERROR 1045 (28000): Access denied for user 'someone'@'localhost' (using password: YES) Any idea whats going wrong here? Connecting with mysql-workbench seems to work, although I don't see somedb in the list of databases. Thanks in advance, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Can't connect as non-root user to database
you probably have the anonymous user account taking over: ''@'localhost' when you specify the host with -h you are actually forcing MySQL to use TCP/IP so it will authenticate you using your ip address (127.0.0.1) login as root and: mysql drop user ''@'localhost'; and try again Cheers Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com Hi Rik, Hm, is the mysql-client library the same as the mysql-server? Yes. And does mysql --host=127.0.0.1 --user=someone -p somedb work (or it's actual IP-address, forcing the TCP/IP connect instead of possible sockets) ? This is really strange - with -h127.0.0.1 I get the same error: ERROR 1045 (28000): Access denied for user 'someone'@'localhost' (using password: YES) However with -h192.168.1.102 everything works as expected, although I used 'someone'@'%' everywhere. Does '%' not include local connections / unix domain sockets? Thanks, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Can't connect as non-root user to database
If you want to verify it is very easy: $ mysql --user=someone somedb (without -p) mysql select user(); select current_user(); cheers Claudio 2012/3/16 Claudio Nanni claudio.na...@gmail.com you probably have the anonymous user account taking over: ''@'localhost' when you specify the host with -h you are actually forcing MySQL to use TCP/IP so it will authenticate you using your ip address (127.0.0.1) login as root and: mysql drop user ''@'localhost'; and try again Cheers Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com Hi Rik, Hm, is the mysql-client library the same as the mysql-server? Yes. And does mysql --host=127.0.0.1 --user=someone -p somedb work (or it's actual IP-address, forcing the TCP/IP connect instead of possible sockets) ? This is really strange - with -h127.0.0.1 I get the same error: ERROR 1045 (28000): Access denied for user 'someone'@'localhost' (using password: YES) However with -h192.168.1.102 everything works as expected, although I used 'someone'@'%' everywhere. Does '%' not include local connections / unix domain sockets? Thanks, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio -- Claudio
Re: Can't connect as non-root user to database
Hi Clemens, my pleasure! I forgot, you had to use also -P3306, so using both -h and -P which deny the lookup for users at localhost, forcing TCP-IP. and so IPs. this is also good when the socket file is not in the standard location, you will have the same problem logging in locally, using -h and -P forces TCP-IP Cheers! Claudio 2012/3/16 Clemens Eisserer linuxhi...@gmail.com Hi Claudio, you probably have the anonymous user account taking over: ''@'localhost' login as root and: mysql drop user ''@'localhost'; Thanks a lot, that solved the problem (and saved my day :) !). when you specify the host with -h you are actually forcing MySQL to use TCP/IP so it will authenticate you using your ip address (127.0.0.1) Actually even with -h127.0.0.1 specified, I got access denied for ...@localhost. Thanks again, Clemens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Free tickets to MySQL conference
Gr! Too far, too busy! Good stuff! Claudio On Mar 15, 2012 2:52 AM, Baron Schwartz ba...@xaprb.com wrote: If you want to go to this year's MySQL conference like all the cool kids, now's your chance. Percona is giving away free tickets (and free books)! Details here: http://www.mysqlperformanceblog.com/2012/03/14/win-free-mysql-conference-tickets/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Trying to update existing prices and sizes in products table
Matthew, Baron, I am actually a bit confused,* what has the SELECT to do with the UPDATE?* SELECT ProductSku, COUNT(ProductSku) _import_products FROM _import_products GROUP BY ProductSku; I think the problem is simply that you are using two relations (tables) that are effectively the same and you have to specify an alias for each of them to let the optimizer understand the SET to which of the two refers to. *Moreover*, how can this UPDATE know about the table _import_products ??? if it is not mentioned in the JOIN? You can fix the first problem adding the aliases: UPDATE ps4_products ps4_A INNER JOIN ps4_products ps4_B ON (*_import_products*.ProductSku = ps4_products.ProductSKU) SET ps4_A.ProductPrice = _import_products.ProductPrice; SET ps4_As.ProductWeight = _import_products.ProductWeight; SET ps4_A.ProductWidth = _import_products.ProductWidth; SET ps4_A.ProductHeight = _import_products.ProductHeight; SET ps4_A.ProductLength = _import_products.ProductLength; then you will have the error: Unknown _import_products @Baron: how can an alias be carried over between two different SQL statements?? Cheers! Claudio 2012/3/14 Baron Schwartz ba...@xaprb.com Matthew, The mistake seems to be in believing that the alias from the SELECT carries over and is used in the UPDATE. You need to add an alias to the UPDATE. You are referring to _import_products in the UPDATE, but you never define it as an alias. On Tue, Mar 13, 2012 at 10:30 PM, Matthew Stuart m...@btinternet.com wrote: Hi all, I have a table of products (ps_4products), and a table of up-to-date prices and sizes (_import_products). I am trying to replace old content in the table ps4_products with up-to-date content in the _import_products table, but I am getting errors. I am trying to ask the DB to match on ProductSKU and then replace the relevant info but I am getting this error: Not unique table/alias: 'ps4_products' I have no idea what it means though. Please advise. Here's my query: SELECT ProductSku, COUNT(ProductSku) _import_products FROM _import_products GROUP BY ProductSku; UPDATE ps4_products INNER JOIN ps4_products ON (_import_products.ProductSku = ps4_products.ProductSKU) SET ps4_products.ProductPrice = _import_products.ProductPrice; SET ps4_products.ProductWeight = _import_products.ProductWeight; SET ps4_products.ProductWidth = _import_products.ProductWidth; SET ps4_products.ProductHeight = _import_products.ProductHeight; SET ps4_products.ProductLength = _import_products.ProductLength; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Baron Schwartz Percona Inc http://www.percona.com/ Consulting, Training, Support Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Design advice for hotel availability program
Hi,
Nobody answers because this is a very wide question about software
engineering,
Trust me, It seems like a simple question but it is not.
The only advice I can give you is to try to imagine all possible
scenarios/use-cases before starting the design.
Cheers
Claudio
2012/2/20 Jan Steinman j...@bytesmiths.com
Where are your domain experts? You *are* consulting with them, no?
If you don't know the answers, and don't have access to domain experts to
help you, I would design for the most general case, and factor out
exceptions as they prove to be so. Pre-optimization for exceptions almost
always turns out to be a bad choice.
From: Tompkins Neil neil.tompk...@googlemail.com
Am
I best using the following pattern
(1) Default rates/rooms stored in a generic table
(2) Any exceptions/changes/closed days to the daily rates are store in
a separate table.
(3) Any special offer exceptions are stored as a rule
All, should I consider that for any hotel, for any room, for any day I
have
a record in a huge single table ???
Everything we think we know about the world is a model... None of these is
or ever will be the real world. -- Donella H. Meadows
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
Re: Filesystem choice
Micheal, I have the feeling that no one on this planet uses raw devices with mysql, I might be wrong but I think InnoDB is kind of 'optimized' to leverage the filesystem facilities, but I would really like an InnoDB expert opinion here. Claudio 2012/2/7 Michael Dykman mdyk...@gmail.com In the case of using raw devices (which I'm not really sold on in general, but there are cases when performance is all), we ran our backups from a slave replica. On Tue, Feb 7, 2012 at 1:42 PM, Sameh Attia sat...@gmail.com wrote: Hi, Check these: http://www.enterprisestorageforum.com/storage-hardware/test-plan-for-linux-file-system-fsck-testing.html http://www.enterprisestorageforum.com/sans/features/article.php/3749926 http://www.enterprisestorageforum.com/storage-hardware/the-state-of-file-systems-technology-problem-statement.html http://www.enterprisestorageforum.com/storage-technology/the-future-of-storage-devices-and-tiering-software.html http://www.enterprisestorageforum.com/storage-hardware/linux-file-system-fsck-testingthe-results-are-in.html Regards Sameh Attia -- - Failure is not an option; it is a built-in feature in Windows. - The two basic principles of system administration: * For minor problems, reboot * For major problems, reinstall dc -e '603178305900664311156641389051003470569569613466992253686426210705237258P' On Tue, Feb 7, 2012 at 8:31 PM, List Man list@bluejeantime.com wrote: Ext4 is faster to me. LS - Original Message - From: rickytato rickytato rickyt...@r2consulting.it To: mysql@lists.mysql.com Sent: Tuesday, February 7, 2012 1:19:32 PM Subject: Filesystem choice Hi, I'm my new server I've to decided what filesystem to used. The server are dual amd six core 2.4GHz, 32GB ram, and 4x 300GB SAS 15krpm raid10 with perc700 512MB raid controller. I've to chosse between xfs and ext4; ext4 with noatime,nodiratime,data=writeback,barrier=0,nobh,commit=100,errors=remount-ro and formatted with -b 4096 -E stride=16,stripe-width=32 is right choice or nobarrier is too unsafe? Only for mysql partition, non for the root. rr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Editing/form interface
Tim, are you designing a client/server application or a web based one? Claudio 2012/1/18 Michael Dykman mdyk...@gmail.com Mysql is a backend service and has no such application-level tools. You can, however, use MS-Access (or any other such tool) and use MySL as a backend via an ODBC driver. - micael dykman On Wed, Jan 18, 2012 at 3:34 PM, Tim Johnson t...@akwebsoft.com wrote: Are there any utilities available that will enable the quick design and implementation of forms for editing and adding records? I.E. something like M$-Access or OpenOffice form designer/wizards. thanks -- Tim tim at tee jay forty nine dot com or akwebsoft dot com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Editing/form interface
Hi Tim, Excuse me but still I don't understand exactly what is the product you want to build. One thing is a tool (IDE) to work and one thing is the product you build. Some questions to understand: You ask for a tool name or a general question? What are exactly the mysql editing features ? What are your my view templates ? And again, are you building a client-server architecture application or a web based? Any interface as you surely know needs a logic and an engine that runs that logic. That logic could reside on a client application, on a browser(javascript et al), server(anything). I ask all this because your question is very vague and so the answer could be anything, Visual Basic, Jboss, Zend Studio, Adobe Flex just as an idea. Cheers Claudio 2012/1/19 Tim Johnson t...@akwebsoft.com * Claudio Nanni claudio.na...@gmail.com [120118 14:15]: Tim, are you designing a client/server application or a web based one? Hi Claudio : I am asking a general question. I have developed web-based apps with mysql editing features for over 10 years now, but I am a little lazy. Or to be more self-kind, I'm looking for tools that can speed things up a bit. For instance, I think it would be great if there were a front end to MySQL that would allow me to design forms like I used to for MS-Access and then save them as html. I could then embed them in my view templates. I looked for such a feature in MySQLWorkBench, but like that other responder said, not a 'front end'. Thanks for the inquiry. -- Tim tim at tee jay forty nine dot com or akwebsoft dot com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: delete all hosts using a wildcard
I wouldn't recommend 'playing' with the grant tables instead use the designated commands. Anyway keep in mind that if you modify the grant tables manually you have to force the reload of the privileges in memory by using the 'flush privileges'. Not needed if you use GRANT/REVOKE etc. Cheers Claudio On Jan 15, 2012 1:28 AM, Tim Dunphy bluethu...@jokefire.com wrote: Hello again list, Thanks for pointing out where I was making my mistake. I just needed to select the right field. And this is just a test environment so getting rid of those users won't have any meaningful impact. Also previewing what you will be deleting by using a select is great advice I intend to use. Best tim - Original Message - From: Paul DuBois paul.dub...@oracle.com To: Tim Dunphy bluethu...@jokefire.com Cc: mysql@lists.mysql.com Sent: Saturday, January 14, 2012 6:46:38 PM Subject: Re: delete all hosts using a wildcard On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: hello list, I have a number of hosts that I would like to delete using a wildcard (%) symbol. Here is the query I am using: mysql delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compare your pattern to the host column, not user. * To match the pattern, use LIKE, not =. So: WHERE host LIKE '%.summitnjhome.com' But to see what rows your DELETE will affect, try this first: SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; Something else to consider: What if these accounts have privileges defined in the other grant tables, such as database-level privileges in the db table? http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html Query OK, 0 rows affected (0.00 sec) And I am attempting to delete all the hosts at the domain ' summitnjhome.com'... But as you can see I am unsuccessful: mysql select user,host from mysql.user; +--+-+ | user | host| +--+-+ | root | 127.0.0.1 | | repl | virtcent10.summitnjhome.com | | admin| virtcent11.summitnjhome.com | | repl | virtcent19.summitnjhome.com | | repl | virtcent23.summitnjhome.com | | repl | virtcent30.summitnjhome.com | +--+-+ I know I can delete them individually and this is what I am going to do. But I would like to use this as a learning opportunity to help me understand how the wildcard works. Thanks in advance.. Best regards, Tim -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: trick trigger
Just adding an extra note to the already good Shawn response. Theoretically this is just as any booking system that needs to be run in transactions (db or not). What you are doing is not trivial if that makes you feel better. The problem is basically that the web interface is asynchronous so you have to 'watch' your transaction in some way. Ad Shawn says you might collect all needed data on the user, check and eventually book on server or return the conflict. @Shawn Isn't data integrity constraints part of the business logic? Cheers Claudio On Jan 11, 2012 7:40 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: Hello John, On 1/11/2012 11:16, John G. Heim wrote: I am working on an app to allow a committee to schedule classes. The members of the committee can all update the database by changing the time or the instructor for a class. I have to write an app to warn them when they've scheduled an instructor for 2 classes at the same time or if they've scheduled any of a large list of classes at the same time. For example, they shouldn't schedule Calculus 212 at the same time as Physics 302 because a student might want to take both classes. And obviously, they shouldn't schedule Professor Higgenbothom to teach both Calculus 212 and Probability 278 at 10:00 AM on Monday, Wednesday, and Friday. The problem isn't actually writing mysql to select the conflicts. The problem is when and how to run the code. I could put it in a trigger but say someone assigns Dr. Higgy to teach Calc 212 at 10 AM MWF. They need to be able to see that he is now scheduled for another class if they look at Probability 278. Get the problem? An update to one record can necessitate an update to any number of other records. I'm just looking for basic suggestions on how you'd deal with this. Should I attempt to write a trigger that updates both Calc 212 and Physics 302 when either is changed? Am I going to create an infinate loop? I am thinking of telling the committee that it can't be done and they'll have to wait for the list of conflicts to be recalculated by a background process once an hour or so. My current database structure is that there is a link table for conflicts. If Calc 212 is scheduled at the same time as Physics 302, that is shown by there being 2 records in a conflicts table. The conflicts table would contain a record with the primary key for Calc 212, the pkey for Physics 302, and a code indicating that its a course conflict. There'd also be a record for Physics 302 indicating that it has a conflict with Calc 212. If Prof. Higgy is also scheduled to tach Calc 212 and Probability 278 at the same time, that would also create 2 records in the conflicts table. Like this: calc212 | phys302 | course_conflict phys302 | calc212 | courseConflict calc212 | prob278 | instructorConflict prob278 | calc212 | instructorConflict Then my web app can do a select for conflicts when displaying Calc 212, Probabbility 278, or Physics 302. But how to get that data into the table? I'm thinking of trying to write a trigger so that wen a class record is updated, the trigger deletes the conflicts records for the class if the id appears in either column 1 or column 2, re-calculate conflicts, and re-add the conflicts records. But if anybody has basic suggestions for a completely different approach, I'd like to hear them. This is all a matter of GUI design and application logic. For example, you could force the user to wait for some kind of database error before realizing that the data they just entered was invalid or you can pre-select conflict lists from the database and block out certain times and people as 'already used' before they make their selections. This requires your application to check with the database at certain events. Let's say you want to schedule a class for Higgy to teach Calc 212, well there are at least two lists, from your description, that you need to know before allowing the user to pick a date and time: 1) the list of all classes that Higgy is already teaching 2) the list of any other classes that might interfere with Calc 212 Some additional lists may also be useful * Any other Calc 212 sections already scheduled for other professors * Any 'no classes here' schedule preferences for Higgy * The list of teaching areas that may be available/unavailable in which your Calc 212 may be taught. These all need to be added to the logic present at the time the scheduler wants to make their choices so that they can avoid many un-necessary trips to the database for every schedule they want to create. Another thing to do is to temporarily block (not with a database-level transaction) access to both Higgy and Calc 212 to minimize the chance of conflicting with the changes made to the database by someone else also trying to enter scheduling information. Summary : * Get as much data as you can get before the request leaves the user. This frees up the
Re: Common Pattern for parent-child INSERTs?
Hi Jan, I am not sure to understand what your question is, what do you mean with inserting updating 2-3 tables? I guess treat the 3-tables join as one single 'object' ? Since you have the referential integrity constraint on the [addresses] and [phones] table you need to follow this basic pattern: INSERT: 1.insert the record into [names] 2.insert the records into [addresses] and [phones] DELETE: 1.delete the records from [addresses] and [phones] 2.delete the record from [names] UPDATE: (a)no problem if you don't update the foreign keys (i.e. assigning an address and/or a phone number to another person) (b)if you need to update the foreign keys just make sure you set them to an existing names_id The problem you mention with the view is probably coming from the fact that when you insert into a view although theoretically possible if the underlying select is a simple multi-table join (updatable view) you have no assurance on the order of the inserts inside the view, it is probably depending on the specific storage engine implementation. I hope this shed a bit of light. Claudio 2012/1/5 Jan Steinman j...@bytesmiths.com: Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! Security is mostly a superstition. Security does not exist in nature, nor do the children of men as a whole experience it. Avoiding danger is no safer in the long run than outright exposure. Life is either a daring adventure, or nothing. -- Helen Keller Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Common Pattern for parent-child INSERTs?
Jan, Just thinking out loud. In relational theory views are just like any entity, in this case the referential integrity would be with the same logical entity, and in the moment of the insert the referential integrity constraint is violated because the new [names_id] will be present only after the operations is done. It might still be storage engine dependent and how it implements an insert on a updatable view. This said, at your own risk, you can disable the foreign key checks before the insert and re-enable them after. At your own risk. Cheers Claudio 2012/1/5 Jan Steinman j...@bytesmiths.com: Thanks, Claudio. What you suggested is essentially what I'm doing. I just thought if this were something common, someone would have a better way of doing it. I would LOVE to be able to simply insert into a names-addresses-phones VIEW, but I haven't been able to make that work. On 4 Jan 12, at 16:48, Claudio Nanni wrote: Hi Jan, I am not sure to understand what your question is, what do you mean with inserting updating 2-3 tables? I guess treat the 3-tables join as one single 'object' ? Since you have the referential integrity constraint on the [addresses] and [phones] table you need to follow this basic pattern: INSERT: 1.insert the record into [names] 2.insert the records into [addresses] and [phones] DELETE: 1.delete the records from [addresses] and [phones] 2.delete the record from [names] UPDATE: (a)no problem if you don't update the foreign keys (i.e. assigning an address and/or a phone number to another person) (b)if you need to update the foreign keys just make sure you set them to an existing names_id The problem you mention with the view is probably coming from the fact that when you insert into a view although theoretically possible if the underlying select is a simple multi-table join (updatable view) you have no assurance on the order of the inserts inside the view, it is probably depending on the specific storage engine implementation. I hope this shed a bit of light. Claudio 2012/1/5 Jan Steinman j...@bytesmiths.com: Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! Security is mostly a superstition. Security does not exist in nature, nor do the children of men as a whole experience it. Avoiding danger is no safer in the long run than outright exposure. Life is either a daring adventure, or nothing. -- Helen Keller Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Claudio If they can get you asking the wrong questions, they don't have to worry about the answers. -- Thomas Pynchon Jan Steinman, EcoReality Co-op -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Transactional problem
INSERT IGNORE is 'the' SQL solution, as you know and mention. I dont know Rails but this is not MySQL / SQL / Database issue but more a specific framework one so the solution depends on it. You can also make some higher level programming workaround. Claudio On Jan 4, 2012 6:46 AM, KK Everest everest5...@ymail.com wrote: Hi all, Can anyone help me with the problem explained here please: http://www.reddit.com/r/mysql/comments/o256m/a_transactional_problem/ Thanks in advance.
Re: (off topic) why PATH
my 0,02 I think I used standard path only the first installation of MySQL in 2002, after that I always I had my custom way of installing it which lead me to be able to have *any* number of instances of any independent version. After some years I saw Giuseppe Maxia's Sandbox which is a nice 'packaging' of that idea. I never use if possible standard directories which are a heritage of the very first idea of unix (multiple users use same tools on a machine), I think today is really out of date, today you need to tune and twist the system at your needs to get the best out of it. With my technique for instance you can setup a mysql cluster on one machine(for testing purposes) without the overhead of virtualization. In my opinion the OS should be an abstraction of the hardware and the software as independent as possible from it. So, software and data and anything else on /whatever mounted on some safe storage and just /etc for startup scripts, in this way you can switch server and still retain all your software with all the benefits you guys well know. Claudio 2012/1/1 Reindl Harald h.rei...@thelounge.net: Am 01.01.2012 03:51, schrieb Hal?sz S?ndor: 2011/12/29 19:35 +0100, Reindl Harald for the hadnful things on my linux-machines where such non-default locations are existing i usually set symlinks unter /usr/local/bin/ to the binarys, so they are seperated and from the user point of view in the PATh and all wroks fine The weakness of PATH: it is all right in the original Unix case, many, many little programs in few directories. Quite a few programs come with MySQL; therefore, it pays to put the MySQL directory in PATH--but Lynx, and many text-processors, comes with one program and many supporting files. In these cases a mechanism other than PATH, something like VMS or C-shell or Korn-shell alias, implemented at the depth of PATH, would be much better. and that is why you normally do ln -s /path/to/your/binary /usr/local/bin/ or do not use OSX as server because on a linux-system you have a package manager which can install the mysql-sub-folder structure directly to /usr/local/ where it is already in the path and it takes care of a clean removal uon uninstall the only reason for /usr/local/mysql/ is that you can remove this folder and is needed only on Mac OSX -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: (off topic) why PATH
yeah... I also don't understand why all these guys are bothering with virtualization just use chroot welcome to stone age if you think that everything has been done before ;) 2012/1/1 Johan De Meersman vegiv...@tuxera.be: Reinventing chroots, then? Welcome to 2012, when everything has been done before :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: (off topic) why PATH
I am a bread baker, you can relax. we are definitely going off-topic. Cheers Claudio PS: it was a joke but with a solid base: http://kae.li/iiigr 2012/1/1 Reindl Harald h.rei...@thelounge.net: chroot has it's place but if you don't understand this days what virtualization is used for [cn] jumping to conclusion here? hopefully your job has nothing to do with IT Am 01.01.2012 22:12, schrieb Claudio Nanni: yeah... I also don't understand why all these guys are bothering with virtualization just use chroot welcome to stone age if you think that everything has been done before ;) 2012/1/1 Johan De Meersman vegiv...@tuxera.be: Reinventing chroots, then? Welcome to 2012, when everything has been done before :-) -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Is ECC RAM necessary for MySQL server
Ryan, My opinion here. Any write to memory can go wrong, OS , MySQL , Storage engines, client library and so on. Innodb has some advanced mechanism for ACID compliance like the double write buffer but these are mostly to assure durability. Memory failure although not so frequent can still, in my opinion, corrupt anything included Innodb buffers. I would like the opinion of some other Innodb gurus. Happy new year. Claudio On Dec 31, 2011 2:04 PM, Ryan Chan ryanchan...@gmail.com wrote: Assume I am using InnoDB, which is ACID compliant. Do I still need to use ECC RAM, in order to make sure there is no chance of data corruption due data write? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: FULL mysqldump
Hi Jim happy holidays to you! actually you just need to add the --routines trigger mysqldump --all-databases --*routines* fulldump.sql with this you get all databases including the system one with privileges (mysql), triggers is on by default, you enable routines with the flag --* routines* * * Keep in mind that this method needs any application to be stopped from writing either by shutting it down, blocking it at network level or locking the database with something like FLUSH TABLES WITH READ LOCK; Depending on your application, your SLA, etc. Keep also in mind that for database larger than a few GB it is not recommended to use mysqldump (text dump) but any binary method, among which Percona XtraBackup in my opinion is the golden tool, derived from InnoBackup allows hot backups. Cheers Claudio 2011/12/23 Jim McNeely jmcne...@nwrads.com Hello all, happy holidays! What is the best way to run a mysqldump to get the tables, the data, the triggers, the views, the procedures, the privileges and users, everything? It seems confusing in the online documentation, or is that just me? Thanks, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
Good to know and good that you took time to read the manual, good approach. But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY UPDATE? The storage engine is a property of your table and you can set it and/or change it, it is the low-level layer (physical) of the database that takes care on how data is actually stored and retrieved. You can check your table with: SHOW TABLE STATUS LIKE 'your-table-name'; Manual page: http://kae.li/iiiga Cheers Claudio 2011/12/19 Jim McNeely j...@newcenturydata.com In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio -- Claudio
Re: UPDATE triggers with REPLACE statements
Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: delete syntax
2011/12/2 Reindl Harald h.rei...@thelounge.net well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database mysql and use flush privileges after changes The privileges should be maintained only using the designated commands. You cannot rely on the knowledge you have of the underlying implementation which can change anytime , while the privileges command are standard. *Cheers* Claudio Nanni DROP USER is the only SINGLE COMMAND as long as you do not use table/column-privileges there are exactly two relevant tables: user and db Am 02.12.2011 05:15, schrieb Stdranwl: DROP USER command is the only command to remove any user and its association from all other tables. On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald h.rei...@thelounge.net wrote: ALWAYS start with select * from mysql.user where user='mail_admin' and host like '\%'; and look what records are affected to make sure the were-statement works as expected and then use CURSOR UP and edit the last command to delete from not only doing this while unsure with escapes protects you against logical mistakes like forget a and column=1 and get 1000 rows affected with no way back -- Claudio
Re: best way to copy a innodb table
Sure you can, and you should. but in case you also update/delete rows from the first table you have to set up trigger to log changes. if you are lucky (only inserts) then its easier. Cheers Claudio 2011/12/1 Angela liu yyll2...@yahoo.com Hi, folks: I have a situation: A large innodb table t1 with 45 million rows, need to have a new table t2 exactly the same as t1, to copy the data from t1 to t2, I have the following query: create table t2 like t1; insert into t2 select * from t1; but the above insert may run long time , that can interface with performance, is there a way to chunk the insert into the new table? like breaking it down into chunks of 100,000 rows ? Thanks -- Claudio
Re: help needed restoring crashed mysql
This is not to say that MySQL could not have more of the file management features. For example, the ability to add or remove datafiles on the fly and the ability to detach tablespaces as collections of tables. That's where MySQL(read InnoDB) got stuck actually, it never introduced a powerful datafiles management system, and that is where Oracle excels (as far as being almost a O.S.) with multiple level of abstractions, just think of ASM. It is actually the part of Oracle I like most as well as the really oraclish way to get stats out of it! The 'problem' with MySQL is that it is so easy to start with it that people do not realize that is also a real RDBMS. -- Claudio Making innodb tablespaces default... well, it still would not liberate the users from thinking whether they want to run with them enabled or not. For example, if I have 1 tables of 100 bytes each, I probably do not want tablespaces. If I have 1% of tables consuming 99% of the space, I would also not want the tablespaces. As for the OP's problem, unless he changed his mind about the need to import, the same amount of space would anyway be consumed. The solution would probably be to find some bigger O Sdisk and copy that ibdata file there. Right? Cheers Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Corrupted TRG file
Halasz, Have you tried to 'browse' the information schema? Whats in there? Claudio On Nov 25, 2011 6:20 PM, Halász Sándor h...@tbbs.net wrote: The following always is the outcome of the command show create trigger: mysql show create trigger memberchange; ERROR 1602 (HY000): Corrupted TRG file for table `membership`.`address` mysqld x86 5.5.8 under Vista The trigger works, there is no problem with show triggers, and this error appears right after the trigger file was deleted (drop trigger until then) and anew made by installing a trigger--as if the code that handles show create trigger expects a format that no other does. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Data file for MyISAM engine
Also, since MySQL 5.1 MyISAM has an algorythm to detect if you are going to delete a row without ever reading it, so when you insert it, it will use the blackhole storage engine instead. :O (NB: it is a joke) Claudio 2011/11/23 Johan De Meersman vegiv...@tuxera.be - Original Message - From: Yu Watanabe yu.watan...@jp.fujitsu.com It seems that MYD is the data file but this file size seems to be not increasing after the insert sql. That's right, it's an L-space based engine; all the data that has, is and will ever be created is already in there, so storage never increases :-p Seriously though; the MYD file is the datafile and the MYI file is the index file. Both of those will increase with use, although since storage allocation happens based off pages, not records, increases will only happed when the existing pages are filling up. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: setting max_allowed_packet dynamically problem
All the previous hints are correct. I would also add: the arithmetic is only good for command line settings, something like: max_allowed_packet = 16*1024*1024 is not valid in the my.cnf max_allowed_packet = 16M is the way to go in the my.cnf Cheers Claudio 2011/11/3 Michael Dykman mdyk...@gmail.com If you changed the value in the .cnf and restarting the server did not pick up he change, I would hazard a guess that the .cnf file you edited is not the one your server is reading. Check your paths and make sure you are editing the correct file. MySL never rewrites it's own config files to reflect manually changed values. On Thu, Nov 3, 2011 at 4:37 PM, List Man list@bluejeantime.com wrote: I am running Server version: 5.1.45-log MySQL Community Server (GPL) and I attempted to change max packet with the following: SET GLOBAL max_allowed_packet=16*1024*1024; but it did not work properly. The configuration did not change by using the show variables command. I changed the configuration file (my.cnf) and restarted the server and the variable stayed the same. Does anyone have any ideas? LS -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Claudio
Re: setting max_allowed_packet dynamically problem
http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 On Nov 4, 2011 5:41 AM, Satendra stdra...@gmail.com wrote: Hi there, Could anybody suggest me to understand on which conf file (my.cnf) my server is readiing. how could I find that? On Fri, Nov 4, 2011 at 2:28 AM, Claudio Nanni claudio.na...@gmail.comwrote: All the previous hints are correct. I would also add: the arithmetic is only good for command line settings, something like: max_allowed_packet = 16*1024*1024 is not valid in the my.cnf max_allowed_packet = 16M is the way to go in the my.cnf Cheers Claudio 2011/11/3 Michael Dykman mdyk...@gmail.com If you changed the value in the .cnf and restarting the server did not pick up he change, I would hazard a guess that the .cnf file you edited is not the one your server is reading. Check your paths and make sure you are editing the correct file. MySL never rewrites it's own config files to reflect manually changed values. On Thu, Nov 3, 2011 at 4:37 PM, List Man list@bluejeantime.com wrote: I am running Server version: 5.1.45-log MySQL Community Server (GPL) and I attempted to change max packet with the following: SET GLOBAL max_allowed_packet=16*1024*1024; but it did not work properly. The configuration did not change by using the show variables command. I changed the configuration file (my.cnf) and restarted the server and the variable stayed the same. Does anyone have any ideas? LS -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Claudio
Re: Reusing ibdata1 space
Kay, There's no way to regain InnoDB space. I can suggest some techniques but no magic. 1. dump the whole database and reimport 2. setup a brand new slave ,sync and switch to it Cheers Claudio 2011/11/1 Rozeboom, Kay [DAS] kay.rozeb...@iowa.gov We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared ibdata1 file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate ibdata1 and its associated .frm files. I am wondering if we could do the following instead: 1) Let ibdata1 grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; I realize that this would not return the unused space to the operating system. But would it return it to MySQL so that it could be re-used for subsequent inserts, instead of extending ibdata1 further? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- Claudio
Re: 5.1.51 Database Replica Slows Down Suddenly, Lags For Days, and Recovers Without Intervention
Luis, Very hard to tackle. In my experience, excluding external(to mysql) bottlenecks, like hardware, o.s. etc, 'suspects' are the shared resources 'guarded' by unique mutexes, like on the query cache or key cache. Since you do not use MySQL it cannot be the key cache. Since you use percona the query cache is disabled by default. You should go a bit lower level and catch the system calls with one of the tools you surely know to see if there are waits on the semaphores. I also would like to tell that the 'seconds behind master' reported by the slave is not reliable. Good luck! Claudio 2011/10/23 Tyler Poland tpol...@engineyard.com Luis, How large is your database? Have you checked for an increase in write activity on the master leading up to this? Are you running a backup against the replica? Thank you, Tyler Sent from my Droid Bionic On Oct 23, 2011 5:40 AM, Luis Motta Campos luismottacam...@yahoo.co.uk wrote: Fellow DBAs and MySQL Users [apologies for eventual duplicates - I've posted this to percona-discuss...@googlegroups.com also] I've been hunting an issue with my database cluster for several months now without much success. Maybe I'm overlooking something here. I've been observing the database slowing down and lagging behind for thousands of seconds (sometimes over the course of several days) even without any query load besides replication itself. I am running Percona MySQL 5.1.51 (InnoDB plug-in version 1.12) on Dell R710 (6 x 3.5 inch 15K RPM disks in RAID10; 24GB RAM; 2x Quad-core Intel processors) running Debian Lenny. MySQL data, binary logs, relay logs, innodb log files are on separated partitions from each other, on a RAID system separated from the operating system disks. Default Storage Engine is InnoDB, and the usual InnoDB memory structures are stable and look healthy. I have about 500 (read) queries per second on average, and about 10% of this as writes on the master. I've been observing something that looks like between 6 and 10 pending reads per second uniformly on my cacti graphs. The issue is characterized by the server suddenly slowing down writes without any previous warning or change, and lagging behind for several thousand seconds (triggering all sorts of alerts on my monitoring system). I don't observe extra CPU activity, just a reduced disk access ratio (from about 5-6MB/s to 500KB/s) and replication lagging. I could correlate it neither InnoDB hashing activity, nor with long-running-queries, nor with background read/write thread activities. I don't have any clues of what is causing this behavior, and I'm unable to reproduce it under controlled conditions. I've observed the issue both on severs with and without workload (apart from the usual replication load). I am sure no changes were applied to the server or to the cluster. I'm looking forward for suggestions and theories on the issue - all ideas are welcome. Thank you for your time and attention, Kind regards, -- Luis Motta Campos is a DBA, Foodie, and Photographer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=tpol...@engineyard.com -- Claudio
Re: mysql server does not recognize user password
mysql -utim Then mysql SELECT USER(),CURRENT_USER(); Login as root and: delete from mysql.user where user=''; And try again with tim with password. Thanks Claudio On Oct 19, 2011 7:47 AM, Johan De Meersman vegiv...@tuxera.be wrote: Try not passing the password and typing it at the prompt. If that works, there's a problem in the parameter parsing. Random thought: could you have a .my.cnf file in your home directory? - Original Message - From: Johnny Withers joh...@pixelated.net To: Tim Johnson t...@akwebsoft.com Cc: mysql@lists.mysql.com Sent: Wednesday, 19 October, 2011 3:53:23 AM Subject: Re: mysql server does not recognize user password Why does mysql say using password: no? Seems to me the password is not being sent. On Oct 18, 2011 8:37 PM, Tim Johnson t...@akwebsoft.com wrote: linus:~ tim$ sudo mysql Password: . mysql SELECT USER(),CURRENT_USER(); +++ +++ +++ 1 row in set (0.00 sec) ...hmm... on my linux box, where all works, I see 'tim@localhost' linus:~ tim$ mysql -utim -p** -h127.0.0.1 -P3306 ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using password: NO) Thanks. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: mysql server does not recognize user password
FLUSH PRIVILEGES is not needed when you use GRANT/REVOKE/CREATE USER
etc,
Usually this problem comes when you have the anonymous user in the grant
tables (''),
MySQL has a tricky way of processing the grant tables.
Sometimes you can be surprised by what you read issuing:
SELECT USER(),CURRENT_USER();
try this:
mysql -utim -psecret -h127.0.0.1 -P3306
Regards
Claudio
2011/10/19 Tim Johnson t...@akwebsoft.com
* Reindl Harald h.rei...@thelounge.net [111018 15:14]:
Am 19.10.2011 01:03, schrieb Tim Johnson:
Now when I try to log in with host as localhost, user as tim
with 'secret' password:
linus:~ tim$ mysql --host=localhost --user=tim --password=secret
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
password: NO)
Huh!
did you make flush privileges?
Second time around, yes. Same problem
you do not specify any password from the view of the server it seems
(using password: NO)
I am using the same syntax I use on my current machine, but your
observation appears correct, mysql is _not_ seeing my password.
try mysql -u tim -p
Same error:
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
password: NO)
and enter yur password in the followed dialog
Doesn't even ask for the pwd..
passwords in command-lines are really bad bacause they are in the
processlist and history
I'm not concerned. Am closed to the outside world - these are not
servers.
If I login into the server as root again:
linus:~ tim$ sudo mysql
mysql show grants for tim@localhost;
root without a password?
jokingly
Yup. It's been years since I set up mysql. I know I've missed
something, but don't know what.
thank you
--
Tim
tim at tee jay forty nine dot com or akwebsoft dot com
http://www.akwebsoft.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
--
Claudio
Re: Is downloads.mysql.com download?
2011/10/9 Reindl Harald h.rei...@thelounge.net Am 09.10.2011 17:33, schrieb Peng Yu: Hi, I'm not able to connect to downloads.mysql.com. Is it down? what stupid question? if you have internet access, can open other websites and write mails and you fail only to connect to one server it should be clear that this server is down - what else? 1. server does not exist (iamsorude.mysql.com) 2. dns problem 3. routing problem from a specific location 4. intermittent website problem but most of all , as you have already been told, reply only if you have something useful to say and in a non rude way. just ignore all of us stupid that ask stupid questions, do you think you an make it? Thanks Claudio -- Claudio
Re: Can I Develop using Community Edition and Deploy onto Enterprise Edition??
There is no difference. It's just a marketing thing. Enterprise is mainly Support + Enterprise monitor, the source code is exactly the same, the binaries are just (as they say) with more optimized compilation, more often patches are released if you are an Enterprise subscriber. The only extra feature (it was until some time ago, not sure now) is partitioning, that it was possible to have it only in the binaries downloaded from your Enterprise account. No problems at all otherwise. Cheers Claudio 2011/9/21 Johan De Meersman vegiv...@tuxera.be - Original Message - From: Alastair Armstrong alasta...@voxorion.co.za We are in the process of upgrading from the Free Community Edition of MySQL on our Live environment to the Enterprise Edition. Do we need to do the same for my Development environment or can I continue developing on the Community Ed and then simply deploy any code, SQL script, etc to the Live Enterprise edition on our live server? Well... Enterprise edition tends to be a bit behind the community version, so it's not unthinkable that behaviour might be different. If you're just using regular queries and stuff, you should be perfectly fine, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: Triggers - Accessing all NEW data
Hi,
Just quick reading your email, forgive me if I'm mistaken
what about serializing using *concat(old.f1,'|||',old.f2,'|||',old.f3)
('|||' = any separator that works for you)*
and deserialize inside the function?
does this make any sense to you?
Cheers
Claudio
2011/9/13 Chris Tate-Davies chris.tatedav...@inflightproductions.com
Thanks,
I kinda guessed that, but I'm not sure how to pass the OLD object to it as
MySQL cannot handle a rowset datatype.
Has anyone had any experience with this? Not sure where to start or how to
proceed.
Chris
On 13/09/11 07:40, Luis Motta Campos wrote:
On 8 Sep 2011, at 16:23, Chris Tate-Davies wrote:
Hello. I want to know if there is a special way I can access all the data
in the NEW/OLD data?
I realise I can access it by referencing NEW.fieldname but I want to
serialise the NEW object so I can save as a string. Is this possible or do I
need to write a function?
Hi,
You'll have to write your own function for that.
Cheers
--
Luis Motta Campos
is a DBA, Foodie, and Photographer
--
*Chris Tate-Davies*
*Software Development*
Inflight Productions Ltd
Telephone: 01295 269 680
15 Stukeley Street | London | WC2B 5LT
*Email:*chris.tatedavies@**inflightproductions.comchris.tatedav...@inflightproductions.commailto:
chris.tatedavies@**inflightproductions.comchris.tatedav...@inflightproductions.com
*Web:*www.inflightproductions.**com http://www.inflightproductions.com
http://www.**inflightproductions.com/http://www.inflightproductions.com/
-
Registered Office: 15 Stukeley Street, London WC2B 5LT, England.
Registered in England number 1421223
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you have
received it in error, please notify the sender immediately and delete the
original. Any other use of the email by you is prohibited. Please note that
the information provided in this e-mail is in any case not legally binding;
all committing statements require legally binding signatures.
http://www.**inflightproductions.com http://www.inflightproductions.com
--
Claudio
Re: mysql
You have 2 options: use tcp/ip or find the right .sock file use this: mysql -uUSER -p -h127.0.0.1 -P3306 or check in the my.cnf where the server creates the .sock file you have to use the same with the local client. Ciao Mad! Claudio 2011/8/23 Andrew Moore eroomy...@gmail.com That's too bad. How did you configure things? What trouble shooting have you done so far? On Aug 23, 2011 9:18 AM, madu...@gmail.com madu...@gmail.com wrote: When I try to start my mysql DB I keep getting the following message: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com -- Claudio
Re: does mysql support master to master replication
Any mysql instance can replicate from any other as long as binary logging is enabled on the designated master. Two instances can replicate from one another yes, but precautions must be taken especially in the case of active-active configuration. Cheers, Claudio On Aug 3, 2011 7:19 AM, Jon Siebert jon.siebe...@gmail.com wrote: I had seen a discussion here as well, but honestly did not test it. It may be of help. http://www.linkedin.com/groupItem?view=srchtype=discussedNewsgid=72881item=60056153type=membertrk=eml-anet_dig-b_pd-ttl-cn On Wed, Aug 3, 2011 at 1:10 AM, Angela liu yyll2...@yahoo.com wrote: Hi, Folks: Does mysql support master to master replication, or master to slave replication on;y? I did not find the answer in MySQL manual, but found some discussion over the internet. Does anybody know? if so, anybody has implemented master to master replication in production? Thanks Angela
Re: granting file privileges
Hi Doug,
1.FILE is GLOBAL because it refers to the ability of the user to read/write
files on the server host filesystem (where the filesystem permissions
allow).
2.
1) user@localhost OK, not recommended
2) 'user@localhost' WRONG
3) 'user'@'localhost' OK, BEST
single quotes prevent any problem in case of special characters in the host
name.
Cheers!
Claudio
2011/7/31 d...@safeport.com
I have both a theory question and a question on implementation of
privileges. First theory. I have been using:
grant all privileges on db-name.* to user@localhost
identified by 'password';
Because I blunder about as root I never was impacted by 'file' being a
global permission. As 'load infile' seems (to me) to be equivalant to
'insert' I do not see the reason for this. If its just a historical thing,
so be it, but IMO it makes little sense that a user could create and/or
delete a table but to import data he is required to convert a csv file to
'insert value' statements.
My implementation question is about specifying the user. Apparently the
following are different:
1) user@localhost
2) 'user@localhost'
3) 'user'@'localhost'
I have not tested all this, but I did grant file privileges to #1 but could
not use them logging into with 'mysql -u doug@localhost sysadmin' ('doug'
being setup without a password).
The question is which form should be used and why are they different as all
are accepted without error and all add entries for the users and db tables.
_
Douglas Denault
http://www.safeport.com
d...@safeport.com
Voice: 301-217-9220
Fax: 301-217-9277
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?**
unsub=claudio.na...@gmail.comhttp://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
--
Claudio
