RE: Calling function, that operates on another database
Chris, take a look at Federated tables https://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html No, it is not as easy as Oracle's dblinks. David. David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158 Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.net | www.univision.net -Original Message- From: bars0.bars0.bars0 [mailto:bars0.bars0.ba...@gmail.com] Sent: Tuesday, April 08, 2014 4:16 PM To: mysql@lists.mysql.com Subject: Calling function, that operates on another database Hi all. I have standard select statement and on one column I want to run function, that will connect to another database (same server). Is this possible? High level example: SELECT db1.clients.client_id, getTurnover(db1.clients.client_id) FROM db1.clients; AND getTurnover($id) body would be something like: SELECT SUM(db2.turnover.amount) FROM db2.turnover WHERE db2.turnover.client_id = $id; So for some data, I need to make lookup to another database table. Is this even possible? Cheers, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
RE: Help with cleaning up data
Bill, here is one approach: The following query will return the id's that should NOT be deleted: Select min (id) from icd9x10 group by icd9, icd10 Once you run it and happy with the results then you subquery it in a DELETE statement. Something like: Delete from icd9x10 A where A.id not in (Select min (B.id) from icd9x10 B group by B.icd9, B.icd10). I have not tested it (sorry it is a weekend here...), but I hope it will lead you into the right direction. David. David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158 Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.net | www.univision.net -Original Message- From: william drescher [mailto:will...@techservsys.com] Sent: Saturday, March 29, 2014 2:26 PM To: mysql@lists.mysql.com Subject: Help with cleaning up data I am given a table: ICD9X10 which is a maping of ICD9 codes to ICD10 codes. Unfortunately the table contains duplicate entries that I need to remove. CREATE TABLE `ICD9X10` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `icd9` char(8) NOT NULL, `icd10` char(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `icd9` (`icd9`,`id`), UNIQUE KEY `icd10` (`icd10`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=671 DEFAULT CHARSET=ascii id icd9 icd10 25 29182 F10182 26 29182 F10282 27 29182 F10982 I just can't think of a way to write a querey to delete the duplicates. Does anyone have a suggestion ? bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
RE: Locking a Database (not tables) x
Thanks Shawn, This may work for us with some script changes. We'll take a look. By the way, too bad we cannot rename a database, or can we? See http://dev.mysql.com/doc/refman/5.1/en/rename-database.html about removal of a dangerous RENMAE DATABASE statement... David. David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158 Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.net | www.univision.net -Original Message- From: shawn l.green [mailto:shawn.l.gr...@oracle.com] Sent: Friday, March 21, 2014 3:34 PM To: mysql@lists.mysql.com Subject: Re: Locking a Database (not tables) x Hi David. On 3/21/2014 1:42 PM, David Lerer wrote: Frequently, we import a production dump that contains only 1 or 2 databases into one of our QA instances that contains many more databases. (i.e. database being a schema or a catalogue). At the beginning of the import script, we first drop all objects in the QA database so that it will be a perfect match (object wise) to production. Is there an easy way to lock the whole database for the duration of the import - so that no developers can update the database? Obviously, I can revoke permissions, but I was wondering whether there is a better approach. If you start with a DROP DATABASE that will pretty much ensure that nobody gets back into it. Then re-create your tables in a new DB (yyy) As a last set of steps do CREATE DATABASE RENAME TABLE yyy.table1 to .table1, yyy.table2 to .table2, (repeat for all your tables). DROP DATABASE yyy Because this is essentially a metadata flip, the RENAME will be quite speedy. -- Shawn Green MySQL Senior 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 The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Locking a Database (not tables) x
Frequently, we import a production dump that contains only 1 or 2 databases into one of our QA instances that contains many more databases. (i.e. database being a schema or a catalogue). At the beginning of the import script, we first drop all objects in the QA database so that it will be a perfect match (object wise) to production. Is there an easy way to lock the whole database for the duration of the import - so that no developers can update the database? Obviously, I can revoke permissions, but I was wondering whether there is a better approach. Thanks, David. David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158 Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.net | www.univision.net The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Locking a Database (not tables)
Frequently, we import a production dump that contains only 1 or 2 databases into one of our QA instances that contains many more databases. (i.e. database being a schema or a catalogue). At the beginning of the import script, we first drop all objects in the QA database so that it will be a perfect match (object wise) to production. Is there an easy way to lock the whole database for the duration of the import - so that no developers can update the database? Obviously, I can revoke permissions, but I was wondering whether there is a better approach. Thanks, David. David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158 Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.net | www.univision.net The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
RE: Locking a Database (not tables) x
Thanks Wayne. This a great idea to prevent user activity on the server. I’ll use it in the future. But I’m looking for a way to prevent user activity on a database ((i.e. database being a schema or a catalogue). David. David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158 Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.netmailto:dle...@univision.net | http://www.univision.net [cid:1e909b.png@efba91b0.48b65711]http://www.univision.net From: Wayne Leutwyler [mailto:wleut...@columbus.rr.com] Sent: Friday, March 21, 2014 2:12 PM To: David Lerer Subject: Re: Locking a Database (not tables) x You could set max_connections = 0; then kill off any remaining connections. Do your data load and then set you max_connections back to what it was prior. show variables like ‘max_connections’; (note this number) set global max_connections = 0 This will leave 1 connection open for a superuser, I dont know what ID you use for that a lot of people use root. Now import your data. Once the import is done set global max_connections back to what it was. On Mar 21, 2014, at 1:42 PM, David Lerer dle...@univision.netmailto:dle...@univision.net wrote: Frequently, we import a production dump that contains only 1 or 2 databases into one of our QA instances that contains many more databases. (i.e. database being a schema or a catalogue). At the beginning of the import script, we first drop all objects in the QA database so that it will be a perfect match (object wise) to production. Is there an easy way to lock the whole database for the duration of the import - so that no developers can update the database? Obviously, I can revoke permissions, but I was wondering whether there is a better approach. Thanks, David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Walter Wayne Leutwyler, RHCT Sr. MySQL Database Administrator Mobile: 614 519 5672 Office: 614 889 4956 E-mail: wayne.leutwy...@gmail.commailto:wayne.leutwy...@gmail.com E-mail: wleut...@columbus.rr.commailto:wleut...@columbus.rr.com Website: http://penguin-workshop.dyndns.org Courage is being scared to death, but saddling up anyway. --John Wayne The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
Re: Monitoring Sessions
We have tried Oracle tool (MySQL Enterprise Monitor) which allows you to capture and analyze queries submitted from selected hosts, for a specific time window. The tool and its user interface were very useful in identifying the volume and heavy queries. Licensing and (cost) may be an issue. I have not tried the Percona tool. David. - Original Message - From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] Sent: Wednesday, October 10, 2012 08:02 AM To: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Re: Monitoring Sessions Thanks Johan for info, We already tried with tcpdump and wireshark it was helpfull. Percona tool kit i need to try. From: Johan De Meersman vegiv...@tuxera.be To: Anupam Karmarkar sb_akarmar...@yahoo.com Cc: mysql@lists.mysql.com Sent: Wednesday, 10 October 2012 5:16 PM Subject: Re: Monitoring Sessions - Original Message - From: Anupam Karmarkar sb_akarmar...@yahoo.com How to monitor individual session number of rows selected or updated by sessions, number of bytes sent and reviewed by session in a given time period, sessions connects runs command and then disconnects, SHOW GLOBAL STATUS is not helping me in this case as i want low level session details, there are nearly 50's application server requesting to 1 databases server, which server is generating more traffic need to monitor and what kind of queries it is firing, binlog file are genrating nearly 7-8 GB daily. Data trafic we can also get on network level but can we get more details as mention. Well, you can look at the local (session) status, but that would require each session to actually store those, as you can't access them outside of the session. Alternatively, you can use tcpdump and wireshark to capture traffic as it goes through the wire and look at what's happening. Percona Toolkit's pt-query-digest tool can also work with tcpdump logs. The general log file will also save you entire sessions, but will do so for EVERY session that happens - which is going to make for a huge overhead on the busy machine you seem to be describing, so is definitely not recommended. Someone also recently posted links towards an init-sql based approach which might be adapted, and towards a McAfee Auditing module for MyQSL that also seemed to hold some promise. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How often the slave to pull data from master?
Thanks Shawn. Very informative and useful. David. The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How often the slave to pull data from master?
Thanks Shawn. This is very useful. Could you shed some light on how rolled-back transactions (or not-yet-committed transactions for that matter) are treated as far as the binary logs? Are these updates actually go to the binary logs, thus trigger replication to the salve? Thanks, David. -Original Message- From: Shawn Green [mailto:shawn.l.gr...@oracle.com] Sent: Monday, July 30, 2012 11:22 AM To: Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: How often the slave to pull data from master? On 7/29/2012 12:52 AM, Zhigang Zhang wrote: Hi If there are additional events beyond that, it retrieves those in sequence after it writes the current statement into the relay logs. I have a question about this: Whether the slave is sent a signal whenever the master generates each event? Yes, the slave receives a signal but it is not a TCP (networked) signal that the slave receives. Let me add a few more details to the 'retrieval of events from the Binary log' part of the replication process. Each slave that connects to a master will open a 'dump thread' process that keeps track of the end of the binary log. This is a mini-daemon that runs in the context of the overall MySQL server. The slave holds and maintains an open connection with the dump thread and when there are no new events to transmit to the slave, the thread goes to sleep. This does not break the TCP/IP connection to the slave. The 'signal' to the 'slave' is not sent via TCP to the remote server. It is sent to it's proxy, the dump thread, using a pthread_cond_signal event. This wakes the thread and initiates the streaming of new data to the slave. The dump thread is initiated by the slave when it makes its connection. This is considered a 'slave process' as it is the dedicated local listener for the slave process. Each slave gets its own dump thread. When the slave disconnects, the dump thread is destroyed. Using a low-level kernel signal is much more efficient (in terms of network usage and CPU cycles) than to continuously ping one server from the other. This is why we chose this design. For any additional lower-level details than this, I encourage you to review the source as it can tell you much more than most would like to be shared in a general forum like this list. Also, there is a more technical discussion on the internal mechanics of MySQL already in place. The proper place to ask for more details would be the Internals mailing list. -- 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 The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Subquery taking too much time on 5.5.18?
Cabbar, try to replace the IN subquery with an EXISTS. Something like: SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name like 'X%'); Does it help? David. -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Friday, July 06, 2012 11:46 AM To: mysql@lists.mysql.com Subject: Subquery taking too much time on 5.5.18? Hi Everyone, I have been trying to understand why subqueries are taking tooo much time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. In a nutshell, I have 2 tables: A and B. And, I do something like this: SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%'); Table A has 460,000 rows and Table B has 5000 rows, and A.id is my primary key, and B.name is indexed. Also, the sub-query here (B.name starts with X%) returns about 300 rows. For some weird reason, this query takes a ton of time (I cancelled it after 750 seconds). I looked at the query plan with EXPLAIN and it could not find an index to use for table A and looks like it is doing a table scan (even though A.id is the primary key)... To understand it better, I divided it up, and sent two queries separately as follows:: SELECT A_ID FROM B WHERE B.name like 'X%' takes 0.002 second. For testing purposes, I concatenated all ids from this query and send a hard-coded query on A like: SELECT * FROM A WHERE A.id in (1,2,3,4,5.) and this takes 0.002 second. Basically, both queries are super fast, but when I combine them via IN w/sub-query, the thing spends a lot more time? As an alternative, I tried using JOIN as follows: SELECT A.* FROM A INNER JOIN B ON (A.id = B.A_id) WHERE B.name like 'X%'; and this takes 0.04 seconds JOIN is also fast, but there are cases where I really need IN subqueries. I would really really appreciate it if you can shed some light on this issue and tell me what I am doing wrong and/or how I can fix this? Thanks a ton. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: create alias for columns bound to database?
Dante, consider using views that are defined with your alternate column names and present the application with these views rather than underlying table names. David. -Original Message- From: D. Dante Lorenso [mailto:da...@lorenso.com] Sent: Friday, May 18, 2012 5:22 PM To: mysql@lists.mysql.com Subject: create alias for columns bound to database? All, I'd like to be able to create column aliases which are bound to the database itself. I have an assortment of columns without naming standards which i'd like to migrate to a better naming scheme. Is there a feature in MySQL that would allow me to give a database column multiple names? I'm thinking that for SELECT * statements, you would use the default column name, but for insert, delete, update, etc, it would be fine to use the aliased name or default. Doing this would *really* help to allow me to migrate the database to the new naming convention without breaking existing code. I would then be able to refactor at a more leisurely pace. Does the feature exist, or can it be created? -- Dante D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: MySQL slowlog - only in file?
Alternatively, you could use MySQL Enterprise Monitor (Oracle) and capture the queries for very easy analysis. I have found it very effective, especially when you have very high number of queries per second. I have not noticed any impact on database performance. David. -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Monday, May 14, 2012 6:16 AM To: P.R.Karthik Cc: Nilnandan Joshi; Rafał Radecki; mysql@lists.mysql.com Subject: Re: MySQL slowlog - only in file? Alternatively, you can copy the data into another table easily: http://www.bitbybit.dk/carsten/blog/?p=115 Best, / Carsten On 14.05.2012 09:34, P.R.Karthik wrote: Hi Rafal, If there are more slow queries in your server and logging them into a table will increase the IO of the server. It is better to be in a file. The slow query log file can be processed easily by pt-query-digesthttp://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html . Regards, KarthiK.P.R MySQL DBA On Fri, May 11, 2012 at 2:47 PM, Nilnandan Joshinilnan...@gmail.comwrote: Hi Rafal, If you are using MySQL 5.1 and later version than you can enable the log tables and you can see slow queries in the log tables. Please check this post: http://nilinfobin.com/2012/03/slow_log-and-general_log-tables-in-mysql-5-1/ regards, Nilnandan On Fri, May 11, 2012 at 2:40 PM, Rafał Radeckiradecki.ra...@gmail.com wrote: Hi all. Is there a possibility to see the info from slowlog somewhere in database? I would like to see slow queries using mysql and not by watching the log file. I've searched on google and mysql website but hasn't found the solution. Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Deadlock due lockwait. How can I tell mysql to wait longer?
Going on a limb here...: I believe I have occurred similar issue (i.e. two transactions go into an indefinite wait).Though, very infrequent occurrence. My only explanation at that time was that there is some loophole when the deletes/inserts had some impact also on the table indexes. In our case, the deletes/inserts statements were invoked by a stored procedure. David. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, May 14, 2012 9:28 AM To: Baron Schwartz Cc: MySql Subject: Re: Deadlock due lockwait. How can I tell mysql to wait longer? - Original Message - From: Baron Schwartz ba...@xaprb.com Because it can be resolved by rolling back just one of them. Why destroy ALL the work people are trying to accomplish, if you could just throw away some of it? 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? Or is this a problem with the consistent view that I'm not seeing? -- 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 The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: MySQL slowlog - only in file?
Alternatively, you could use MySQL Enterprise Monitor (Oracle) and capture the queries for very easy analysis. I have found it very effective, especially when you have very high number of queries per second. In turning on this capture, I have not noticed any impact on database performance. David. -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Monday, May 14, 2012 6:16 AM To: P.R.Karthik Cc: Nilnandan Joshi; Rafał Radecki; mysql@lists.mysql.com Subject: Re: MySQL slowlog - only in file? Alternatively, you can copy the data into another table easily: http://www.bitbybit.dk/carsten/blog/?p=115 Best, / Carsten On 14.05.2012 09:34, P.R.Karthik wrote: Hi Rafal, If there are more slow queries in your server and logging them into a table will increase the IO of the server. It is better to be in a file. The slow query log file can be processed easily by pt-query-digesthttp://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html . Regards, KarthiK.P.R MySQL DBA On Fri, May 11, 2012 at 2:47 PM, Nilnandan Joshinilnan...@gmail.comwrote: Hi Rafal, If you are using MySQL 5.1 and later version than you can enable the log tables and you can see slow queries in the log tables. Please check this post: http://nilinfobin.com/2012/03/slow_log-and-general_log-tables-in-mysql-5-1/ regards, Nilnandan On Fri, May 11, 2012 at 2:40 PM, Rafał Radeckiradecki.ra...@gmail.com wrote: Hi all. Is there a possibility to see the info from slowlog somewhere in database? I would like to see slow queries using mysql and not by watching the log file. I've searched on google and mysql website but hasn't found the solution. Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to sync mysql.user table between to two mysql instances
As a follow up question, will it be ok to do the following: 1. mysqldump -hmysql-inst2 mysql 2. Backup mysql-inst1 and use the backup to restore to mysql-inst2 3. mysql -hmysql-inst2 mysql This way I hope to be able to refresh a DEV instance from a PROD database, but preserve the permissions, users, passwords, etc (Assuming of course that the schemas are identical on the two instances). Thanks, David. -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Friday, March 16, 2012 2:20 PM To: MySql Subject: Re: how to sync mysql.user table between to two mysql instances Charles, 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql 2. With pt-table-sync from Percona Toolkit if you need something more sophisticated. On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles cbr...@bmi.com wrote: Can someone instruct me on how to sync mysql.user table between to two mysql instances. In other words, I wouild like to copy mysql.user from mysql-inst1 to mysql-inst2 + -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to sync mysql.user table between to two mysql instances
I was trying to get a confirmation too. In any event, Charles, I'd try these three steps below and see what happens. I assume that information_schema is populated on the fly. David. -Original Message- From: Brown, Charles [mailto:cbr...@bmi.com] Sent: Friday, March 16, 2012 2:59 PM To: David Lerer; Baron Schwartz; MySql Subject: RE: how to sync mysql.user table between to two mysql instances Hello David, Precisely, that's what my problem is. The users found in mysqlinst1 are not in mysqlinst2. There are about 30 users defined in inst1 and only 4 in inst2. I would like to sync these tables. How do you do it in short of creating 26 accounts in mysqlinst2 one at a time - too tedious. Help me I'm running out of time. -Original Message- From: David Lerer [mailto:dle...@us.univision.com] Sent: Friday, March 16, 2012 1:36 PM To: Baron Schwartz; MySql; Brown, Charles Subject: RE: how to sync mysql.user table between to two mysql instances As a follow up question, will it be ok to do the following: 1. mysqldump -hmysql-inst2 mysql 2. Backup mysql-inst1 and use the backup to restore to mysql-inst2 3. mysql -hmysql-inst2 mysql This way I hope to be able to refresh a DEV instance from a PROD database, but preserve the permissions, users, passwords, etc (Assuming of course that the schemas are identical on the two instances). Thanks, David. -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Friday, March 16, 2012 2:20 PM To: MySql Subject: Re: how to sync mysql.user table between to two mysql instances Charles, 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql 2. With pt-table-sync from Percona Toolkit if you need something more sophisticated. On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles cbr...@bmi.com wrote: Can someone instruct me on how to sync mysql.user table between to two mysql instances. In other words, I wouild like to copy mysql.user from mysql-inst1 to mysql-inst2 + -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql 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. The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: query problem with null
Have you tried to set city = null (i.e. without the quotes)? David. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Friday, March 09, 2012 4:24 PM To: mysql@lists.mysql.com Subject: query problem with null When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help? The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: preg_replace in update statement
Try with a combination of functions LOCATE and SUBSTR. Locate will return the positions for WordA and WordB within the original text, and, SUBSTR will allow you to string what you you need all together. David. On Thu, Mar 8, 2012 at 4:11 PM, Hank hes...@gmail.com wrote: I have a simple problem: I have a varchar field in the database, and I want to remove all text between WordA and WordB, including WordA and WordB, leaving all text before WordA and after WordB intact. Possible with just SQL? I know I can write a PHP program to do it, but it's not that important to spend that much time on. I'd like one SQL statement to do it. Thanks! -Hank -- 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 The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Read_only and InnoDB transactions
What version do you use? David. -Original Message- From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.com] Sent: Monday, November 28, 2011 7:09 AM To: mysql@lists.mysql.com Subject: Read_only and InnoDB transactions Hi all. From the Mysql Documentation: If you attempt to enable read_only while other clients hold explicit table locks or have pending transactions, the attempt blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set. But when I try to set SET GLOBAL read_only = ON; It brake running transaction. -- Viacheslav Biriukov BR The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Read_only and InnoDB transactions
It may not have an impact on you, but be aware of this severe (imho) bug that caused read_pnly to be ignored regardless of running transactions in version 5.5.8. See bug#58669 and others. We upgraded to 5.5.17 where the bug was fixed. David. From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.com] Sent: Monday, November 28, 2011 9:20 AM To: David Lerer Cc: mysql@lists.mysql.com Subject: Re: Read_only and InnoDB transactions MySQL Community Server 5.1.59 on the Centos 5.7 2011/11/28 David Lerer dle...@us.univision.commailto:dle...@us.univision.com What version do you use? David. -Original Message- From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.commailto:v.v.biriu...@gmail.com] Sent: Monday, November 28, 2011 7:09 AM To: mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Read_only and InnoDB transactions Hi all. From the Mysql Documentation: If you attempt to enable read_only while other clients hold explicit table locks or have pending transactions, the attempt blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set. But when I try to set SET GLOBAL read_only = ON; It brake running transaction. -- Viacheslav Biriukov BR The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Viacheslav Biriukov BR http://biriukov.com The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
RE: Concerned : Developer getting There is no 'root'@'%' registered error message
Thanks Bier. I see what you mean. (As a rule we always use SQL SECURITY INVOKER) David. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, August 18, 2011 4:20 AM To: David Lerer Cc: mysql@lists.mysql.com Subject: Re: Concerned : Developer getting There is no 'root'@'%' registered error message - Original Message - From: David Lerer dle...@us.univision.com Correct me if I am wrong, but my impression is that usage of DEFINER=user in stored procedures has no impact on runtime and is actually optional. (not to be confused with the clause SQL SECURITY INVOKER which is crucial.) You can also say SQL SECURITY DEFINER, which makes the procedure run with the privileges of the user who defined it. At that point, the definer becomes very relevant indeed :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: Concerned : Developer getting There is no 'root'@'%' registered error message
Correct me if I am wrong, but my impression is that usage of DEFINER=user in stored procedures has no impact on runtime and is actually optional. (not to be confused with the clause SQL SECURITY INVOKER which is crucial.) Note: I use 5.1.32-enterprise-gpl-advanced-log. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Hungarian Notation [Was Re: Too many aliases]
I join you Arthur. That Hungarian notation is despicable (though I love listening to that language, it is different). I don't find it necessary for a column name to tell me its type. But I do like the ability to have all database objects (table, column, trigger, index, fk, views, procedures, etc.) sortable and searchable. I use a prefix though. My prefix is a number for one reason: Ease of communication with stuff. A schema is assigned to a range of numbers. Sounds old fashioned? Cobolish? So? My 2c. David. - Original Message - From: Arthur Fuller fuller.art...@gmail.com To: Martin Gainty mgai...@hotmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Sun Aug 07 19:03:43 2011 Subject: Re: Hungarian Notation [Was Re: Too many aliases] I despise this sort of notation, and have instead adopted what have cheerfully named Hungarian Suffix notation, the reason being Signal-To-Noise ratio. Instead of prefacing everything with some form of prefix, just do the opposite: Customer_tbl Customer_Dead_boo Customer_DOB_date Customer_qs (that means Query Select) Customer_qu (that means Query Update) Customer_qd (that means Query Delete) CustomerOrders_tbl Customer_frm (a form that opens the Customer table; could involve subforms, but in that case they are named Customer_Orders_fsub, Customer_Payments_fsub, and so on. Easy to read, obvious the intent, and easily sortable. Just my opinion. Arthur
RE: Too many aliases
I agree. I use the same column name in all tables where it has the same function - but I consistently add a suffix or prefix. And yes, it is the old fashion way David. -Original Message- From: h...@tbbs.net [mailto:h...@tbbs.net] Sent: Thursday, August 04, 2011 8:26 AM To: r...@grib.nl Cc: mysql@lists.mysql.com Subject: Re: Too many aliases 2011/08/03 12:46 +0200, Rik Wasmus But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id WHERE second.id IS NULL Well, yes, here it is needful. But it seems to me from most of the examples that people here post, that they have the idea that it is the style always to use one-letter aliases, whether it is helpful or not. Now I do not do this, but I often see examples where a field for one purpose has in one table one name, and in another table another, slightly different, name, and then, too, I see alias used, although, in this case, no table name at all is needed. (I like to use the same field name in all tables where it has the same function.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dle...@us.univision.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Too many aliases
I rarely use aliases (unless rarely required in self-join queries). When I have that option, I create unique columns by prefixing every table (and its objects) with a number. Something like: Create table T1234_Employee (C1234_Employee_id number(5), C1234_employee_status char(1)...) Index X1234_Employee_Id on Etc. Yes, the column names may be longer this way, but easy to refer to and easy to communicate (by specifying a table number). I wonder what others think about it. David. -Original Message- From: Rik Wasmus [mailto:r...@grib.nl] Sent: Wednesday, August 03, 2011 6:47 AM To: mysql@lists.mysql.com Subject: Re: Too many aliases 2011/08/02 12:11 +0530, Adarsh Sharma select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Why do people constantly change table names for queries, although, as here, it gain them nothing? It often makes for less clarity (for which table is this an alias???). Depens on your table names. I rather like being able to give a short description rather then long table names if someone decided that as a tablename. I doubt your example with already short tablenames is one from real life, but if you saw someone doing it would indeed be a waste of time. But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id WHERE second.id IS NULL -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dle...@us.univision.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Which is better
Hard to tell. It depends on the cardinality of tables' id (I assume the IDs are not unique in each of the tables). David. -Original Message- From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, August 02, 2011 2:47 PM To: Adarsh Sharma Cc: mysql@lists.mysql.com Subject: Re: Which is better On 8/2/2011 02:41, Adarsh Sharma wrote: Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) B ( 7 MB ) A has 10 columns B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks There is no difference in performance. The optimizer will change the sequence it uses to read the tables according to its own rules. If you had used STRAIGHT JOIN to force a particular execution path, the it would normally be faster to read the smaller table first. -- 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?unsub=dle...@us.univision.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
