Re: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap?
Hello, On 4/17/2019 10:29 AM, Turritopsis Dohrnii Teo En Ming wrote: Subject/Topic: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap? Good evening from Singapore, Our customer (company name is Confidential/not disclosed) reported that their MySQL database has been found missing or was deleted a few times. While it is bad form to explain how to break into anyone's software (including our own), there are places you can look to get a better idea about what might have happened: 1 - the database may have been removed by a DROP DATABASE command. General Query Log - this will show you which session issued the command and the command itself. Audit log (only for commercial releases) - same thing Binary Log - Should have a record of the command executing. But, depending on which account was used or if Binary Log filtering is in place, it may not. This presumes that the Binary Log is even enabled on this system. Many people mistakenly believe it is only for Replication when its other primary use is for point-in-time recovery. If your customer has a recent backup and all of the Binary Log files created since that backup, they could return the system to the point it was at just before that database went missing, skip that DROP command, then continue rolling forward the changes to the other tables to return to a "current" state of their data. 2 - The database was "dropped" by either changing privileges to the folder or by removing it from disk or some other file-level or system-level operation. Either of those would cause errors to start appearing in the MySQL Error Log because a resource that mysqld thinks should exist is no longer available. While the Error Log can't tell you which operation made those files "no longer available" it will have a fingerprint that such an action happened outside of mysqld. Have you determined which method was used to make that database/schema disappear? A normal DROP command (which could happen through an SQL injection attack) would not leave messages in the Error Log about "unable to access ..." or something similar. The server (mysqld) would know that the database was gone (because it removed it) and it wouldn't be trying to find it or the tables within it for your clients to use it. ... snip ... No matter how many commands I try, sqlmap always report that phpMyAdmin 4.8.4 is *NOT* SQL injectable. Perhaps I was using the wrong sqlmap commands all the time? The following is one of the many sqlmap commands I have used. $ python sqlmap.py -u "https://www.EXAMPLE.com/phymyadmin/index.php?id=1; --level=1 --dbms=mysql --sql-query="drop database" Privately asking phpMyAdmin may be a better source of information about how to hack their system to do things it was not intended to do. This list is not about phpMyAdmin and it is very public. They may also have a way of showing you some kind of trace or log that serves as a fingerprint for that happening. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap?
Subject/Topic: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap? Good evening from Singapore, Our customer (company name is Confidential/not disclosed) reported that their MySQL database has been found missing or was deleted a few times. They are using Ubuntu 16.04 LTS Linux server with Apache2 Web Server, MySQL and PHP (LAMP). We responded to these security incidents by changing the passwords of the regular user, root user, and MySQL database user root. We have also examined /var/log/auth.log and think that the hacker could not have come in through ssh or sftp over ssh. From /var/log/mysql/error.log, we can ascertain that the MySQL database has been deleted at certain timings. We have also found nothing abnormal after examining /var/log/apache2/access.log. Even though we have secured the Ubuntu Linux server by changing passwords, the hacker was still able to delete our customer's MySQL database again and again. I have already proposed to install ModSecurity Open Source Web Application Firewall (WAF) to defend against web application attacks but my boss has told me to put that on hold at the moment. In fact, I have already deployed ModSecurity 2.9.0 on a Ubuntu 16.04 LTS *Testing* server and found that it actively detects and logs Nessus and sqlmap vulnerability scans in blocking mode. Since we did not find any evidence that the hacker had breached our customer's Ubuntu 16.04 LTS production server through ssh or Teamviewer, we suspect that the hacker could have achieved it by SQL injection. I took the initiative of downloading and installing Nessus Professional 8.3.1 Trial version for Windows 64-bit. The vulnerability scan report generated by Nessus Web Application Tests shows that our customer is using a version of phpMyAdmin prior to 4.8.5 which could be vulnerable to SQL injection using the designer feature. Further research shows that I can use sqlmap to determine if phpMyAdmin is SQL injectable. I already have a Testing Ubuntu 16.04 LTS Linux server with a Testing MySQL database and a Testing phpMyAdmin 4.8.4. I have purposely installed phpMyAdmin 4.8.4 because this version was reported to be vulnerable to SQL injection using the designer feature, and our customer is using a vulnerable version, according to CVE-2019-6798 ( https://nvd.nist.gov/vuln/detail/CVE-2019-6798 ). Then I proceeded to download and execute sqlmap on our Ubuntu Linux desktop against our Testing server. No matter how many commands I try, sqlmap always report that phpMyAdmin 4.8.4 is *NOT* SQL injectable. Perhaps I was using the wrong sqlmap commands all the time? The following is one of the many sqlmap commands I have used. $ python sqlmap.py -u "https://www.EXAMPLE.com/phymyadmin/index.php?id=1; --level=1 --dbms=mysql --sql-query="drop database" Replace database by database name. May I know what is the correct sqlmap command that I should use to determine that my Testing phpMyAdmin 4.8.4 is SQL injectable? I would like to know if I can successfully drop/delete the Testing database on our Testing server. If I can successfully drop/delete the Testing MySQL database using sqlmap, I would be able to conclude that the hacker must have carried out SQL injection to drop/delete the customer's database. I have already turned off the Testing ModSecurity Web Application Firewall on our Testing server to allow sqlmap to go through. Please point me to any good tutorial on SQL injection using sqlmap. Maybe I do not understand SQL injection well enough. Our customer is also using a customised in-house inventory management system that relies on PHP application and MySQL database. Would open source Snort Intrusion Detection System (IDS) and Intrusion Prevention System (IPS) be able to detect and block SQL injection as well? Please advise. Thank you very much. -BEGIN EMAIL SIGNATURE- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html Singaporean Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -END EMAIL SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
In MySQL 8, how do you distinguish between Roles and Users in table mysql.user?
Hi there, In MySQL 8, how can you figure out if an entry in the mysql.user table is a role or a user? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.
Step-by-Step Tutorial: How to Setup Your Own e-Commerce Online Store using WooCommerce 3.4.5, Wordpress 4.9.8, and CentOS 1805 (LAMP) in Amazon AWS Cloud
Good morning from Singapore, You can read my step-by-step tutorial on How to Setup Your Own e-Commerce Online Store using WooCommerce 3.4.5, Wordpress 4.9.8, and CentOS 1805 (LAMP) in Amazon AWS Cloud at any one of my two redundant blogs. My blogs were configured in RAID 1 mirroring array. https://tdtemcerts.wordpress.com/2018/09/29/step-by-step-tutorial-how-to-setup-your-own-e-commerce-online-store-using-woocommerce-3-4-5-wordpress-4-9-8-and-centos-1805-lamp-in-amazon-aws-cloud/ https://tdtemcerts.blogspot.com/2018/09/step-by-step-tutorial-how-to-setup-your.html Thanks for reading! If there are any mistakes, please do let me know! ===BEGIN SIGNATURE=== Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 30 Oct 2017 [1] https://tdtemcerts.wordpress.com/ <https://tdtemcerts.wordpress.com/>[2] http://tdtemcerts.blogspot.sg/ <http://tdtemcerts.blogspot.sg/>[3] https://www.scribd.com/user/270125049/Teo-En-Ming <https://www.scribd.com/user/270125049/Teo-En-Ming>===END SIGNATURE===
Re: how to select the record with one sql statement?
Hello sea, On 8/13/2018 7:01 PM, sea wrote: helle, I have a table, like this: pigId dayweigt pig1 2018-1-121 pig2 2018-1-131 pig3 2018-1-141 pig1 2018-1-222 pig2 2018-1-231 pig3 2018-1-240 pig1 2018-1-323 pig2 2018-1-330 pig3 2018-1-341 . only the pig1'weight increase continuously for 3 days. Giving the input: num_of_day(weight increasing continuously for num_of_day); expecting the output: certain_day, pigId;from certain_day, pigId'weight increasing continuously for num_of_day. How to select the records in one sql statement? thanks I've thought about this a bit (since your question appeared on the list) and I break down the tasks you need to perform in my head like this. (Others on the list may have different ways to approach the same problem) task 1 - For each bucket, a pigId value, assemble an ordered list (not a set) of each weight sorted by time. (not hard) task 2 - Within each ordered list, compare the values of every consecutive pair. (several ways to do this) task 3 - Iterate over those "consecutive value differences" generated in task 2 looking for the longest sequence of positive non-zero values for each pigId. (this is not really a set-oriented process so normal SELECT or GROUP BY command patterns will not handle it with any efficency) I'm afraid that attempting all of that sequencing and iteration using just a single set-based SQL command is not going to be practical. Using one or more cursors within a stored procedure is your best bet for this type of sequential trend analysis. I could easily imagine the first step as a INSERT...SELECT...ORDER BY... command going to a new table with an autoincrement column on it (to provide a global sequence number across all of your individual pigId values) . The second step could do a self join to that table where the ON clause could look like a.pigId = b.pigID AND a.seq-1 = b.seq But at that point, counting the length of sequences (and remembering when each trend became positive) needs a loop. That's where even complicated set-wise SQL fails you and you need to shift into using the SQL of stored programs. Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
how to select the record with one sql statement?
helle, I have a table, like this: pigId dayweigt pig1 2018-1-121 pig2 2018-1-131 pig3 2018-1-141 pig1 2018-1-222 pig2 2018-1-231 pig3 2018-1-240 pig1 2018-1-323 pig2 2018-1-330 pig3 2018-1-341 . only the pig1'weight increase continuously for 3 days. Giving the input: num_of_day(weight increasing continuously for num_of_day); expecting the output: certain_day, pigId;from certain_day, pigId'weight increasing continuously for num_of_day. How to select the records in one sql statement? thanks
Re: How to get the MySQL Command-Line Tool to display Unicode properly?
Probably the dumbest suggestion yet, but have you tried "set names utf8" in the client? On 20 March 2018 20:50:08 CET, Roger House <rho...@sonic.net> wrote: > > >On 03/15/2018 02:06 PM, Roger House wrote: >> >> >> On 03/15/2018 11:30 AM, shawn l.green wrote: >>> Hi Roger, >>> >>> (please note, this is a bottom-post forum) >>> >>> On 3/13/2018 7:54 PM, Roger House wrote: >>> > >>> > On 03/13/2018 03:11 PM, Reindl Harald wrote: >>> >> >>> >> >>> >> Am 13.03.2018 um 22:59 schrieb Roger House: >>> >>> In all respects except one, the treatment of Unicode works just >>> fine. >>> >>> I can write Unicode to database tables, read it, display it, >etc., >>> >>> with no problems. The exception is mysql, the MySQL Command-Line >>> >>> Tool. When I execute a SELECT statement to see rows in a table >>> >>> containing the Venus and Mars Unicode characters, here is what I >see >>> >>> on the screen: >>> >>> >>> >>> || Venus | ♀ | | Mars | ♂ | | >>> >>> >>> >>> What I should see in the right column are the standard glyphs >for >>> >>> Venus and Mars. >>> >>> >>> >>> Any ideas about how to get the MySQL Command-Line Tool to >display >>> >>> Unicode properly? >>> >> what operating system >>> >> what terminal >>> >> >>> >> all recent Linux systems have UTF8 as default >>> >> >>> > >>> > >>>> I am running Ubuntu MATE 16.04. I have the problem also on Windows >7 >>>> and on Mac OS Version 10.11.6. I do not think that the problem has >to >>>> do with the operating system nor the terminal. Everything about >the >>>> Unicode text works fine in all tools such as editors, the cat >command, >>>> etc. It is only when I am running mysql and I issue a SELECT >>>> command to >>>> see what is in a row. Then the UTF-8 is not rendered properly. I >>>> believe the problem is with mysql. >>>> >>>> Roger >>>> >>> >>> If I presume that your terminal has a code page that is >>> utf8-compatible (you say that cat command renders the multibyte >>> characters just fine) then it could be your client-side mysql >>> settings that are rendering those multibyte characters into >>> individual glyphs based on their individual byte values. >>> >>> The next time you are in mysql and have a chance to look at some >utf8 >>> data, please collect and share these two reports: >>> >>> status >>> SHOW GLOBAL VARIABLES like '%haracter%'; >>> >>> (you can obfuscate any sensitive details like server names or >addresses) >>> >>> Yours, >> Here is the requested info: >> >> mysql> status >> -- >> mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine >> wrapper >> >> Connection id: 5 >> Current database: ephemeris >> Current user: root@localhost >> SSL: Not in use >> Current pager: stdout >> Using outfile: '' >> Using delimiter: ; >> Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu) >> Protocol version: 10 >> Connection: Localhost via UNIX socket >> Server characterset: utf8mb4 >> Db characterset: latin1 >> Client characterset: utf8mb4 >> Conn. characterset: utf8mb4 >> UNIX socket: /var/run/mysqld/mysqld.sock >> Uptime: 6 hours 17 min 8 sec >> >> Threads: 1 Questions: 28 Slow queries: 0 Opens: 122 Flush tables: >> 1 Open tables: 41 Queries per second avg: 0.001 >> -- >> >> mysql> show global variables like '%haracter%'; >> +--++ >> | Variable_name | Value | >> +--++ >> | character_set_client | utf8mb4 | >> | character_set_connection | utf8mb4 | >> | character_set_database | utf8mb4 | >> | character_set_filesystem | binary | >> | character_set_results | utf8mb4 | >> | character_set_server | utf8mb4 | >> | cha
Re: How to get the MySQL Command-Line Tool to display Unicode properly?
On 03/15/2018 02:06 PM, Roger House wrote: On 03/15/2018 11:30 AM, shawn l.green wrote: Hi Roger, (please note, this is a bottom-post forum) On 3/13/2018 7:54 PM, Roger House wrote: > > On 03/13/2018 03:11 PM, Reindl Harald wrote: >> >> >> Am 13.03.2018 um 22:59 schrieb Roger House: >>> In all respects except one, the treatment of Unicode works just fine. >>> I can write Unicode to database tables, read it, display it, etc., >>> with no problems. The exception is mysql, the MySQL Command-Line >>> Tool. When I execute a SELECT statement to see rows in a table >>> containing the Venus and Mars Unicode characters, here is what I see >>> on the screen: >>> >>> || Venus | ♀ | | Mars | ♂ | | >>> >>> What I should see in the right column are the standard glyphs for >>> Venus and Mars. >>> >>> Any ideas about how to get the MySQL Command-Line Tool to display >>> Unicode properly? >> what operating system >> what terminal >> >> all recent Linux systems have UTF8 as default >> > > I am running Ubuntu MATE 16.04. I have the problem also on Windows 7 and on Mac OS Version 10.11.6. I do not think that the problem has to do with the operating system nor the terminal. Everything about the Unicode text works fine in all tools such as editors, the cat command, etc. It is only when I am running mysql and I issue a SELECT command to see what is in a row. Then the UTF-8 is not rendered properly. I believe the problem is with mysql. Roger If I presume that your terminal has a code page that is utf8-compatible (you say that cat command renders the multibyte characters just fine) then it could be your client-side mysql settings that are rendering those multibyte characters into individual glyphs based on their individual byte values. The next time you are in mysql and have a chance to look at some utf8 data, please collect and share these two reports: status SHOW GLOBAL VARIABLES like '%haracter%'; (you can obfuscate any sensitive details like server names or addresses) Yours, Here is the requested info: mysql> status -- mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper Connection id: 5 Current database: ephemeris Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: latin1 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 6 hours 17 min 8 sec Threads: 1 Questions: 28 Slow queries: 0 Opens: 122 Flush tables: 1 Open tables: 41 Queries per second avg: 0.001 -- mysql> show global variables like '%haracter%'; +--++ | Variable_name | Value | +--++ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--+----+ 8 rows in set (0.01 sec) Update I noticed that the status command shows Db characterset: latin1 whereas all the other charactersets are utf8mb4. So I looked around to see how to change the Db characterset, and came up with this: ALTER DATABASE ephemeris CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; After which, status shows Db characterset: utf8mb4 So the database characterset has now been changed. But select * from planet_desc; still shows Venus | ♀ Mars | ♂ I shut down the mysql service and restarted it, but this did not change anything. I'm beginning to think that the command line tool mysql does not pay any attention to character sets when the SELECT command displays on the terminal. Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to get the MySQL Command-Line Tool to display Unicode properly?
On 03/15/2018 11:30 AM, shawn l.green wrote: Hi Roger, (please note, this is a bottom-post forum) On 3/13/2018 7:54 PM, Roger House wrote: > > On 03/13/2018 03:11 PM, Reindl Harald wrote: >> >> >> Am 13.03.2018 um 22:59 schrieb Roger House: >>> In all respects except one, the treatment of Unicode works just fine. >>> I can write Unicode to database tables, read it, display it, etc., >>> with no problems. The exception is mysql, the MySQL Command-Line >>> Tool. When I execute a SELECT statement to see rows in a table >>> containing the Venus and Mars Unicode characters, here is what I see >>> on the screen: >>> >>> || Venus | ♀ | | Mars | ♂ | | >>> >>> What I should see in the right column are the standard glyphs for >>> Venus and Mars. >>> >>> Any ideas about how to get the MySQL Command-Line Tool to display >>> Unicode properly? >> what operating system >> what terminal >> >> all recent Linux systems have UTF8 as default >> > > I am running Ubuntu MATE 16.04. I have the problem also on Windows 7 and on Mac OS Version 10.11.6. I do not think that the problem has to do with the operating system nor the terminal. Everything about the Unicode text works fine in all tools such as editors, the cat command, etc. It is only when I am running mysql and I issue a SELECT command to see what is in a row. Then the UTF-8 is not rendered properly. I believe the problem is with mysql. Roger If I presume that your terminal has a code page that is utf8-compatible (you say that cat command renders the multibyte characters just fine) then it could be your client-side mysql settings that are rendering those multibyte characters into individual glyphs based on their individual byte values. The next time you are in mysql and have a chance to look at some utf8 data, please collect and share these two reports: status SHOW GLOBAL VARIABLES like '%haracter%'; (you can obfuscate any sensitive details like server names or addresses) Yours, Here is the requested info: mysql> status -- mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper Connection id: 5 Current database: ephemeris Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: latin1 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 6 hours 17 min 8 sec Threads: 1 Questions: 28 Slow queries: 0 Opens: 122 Flush tables: 1 Open tables: 41 Queries per second avg: 0.001 -- mysql> show global variables like '%haracter%'; +--++ | Variable_name | Value | +--++ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to get the MySQL Command-Line Tool to display Unicode properly?
Hi Roger, (please note, this is a bottom-post forum) On 3/13/2018 7:54 PM, Roger House wrote: > > On 03/13/2018 03:11 PM, Reindl Harald wrote: >> >> >> Am 13.03.2018 um 22:59 schrieb Roger House: >>> In all respects except one, the treatment of Unicode works just fine. >>> I can write Unicode to database tables, read it, display it, etc., >>> with no problems. The exception is mysql, the MySQL Command-Line >>> Tool. When I execute a SELECT statement to see rows in a table >>> containing the Venus and Mars Unicode characters, here is what I see >>> on the screen: >>> >>> || Venus | ♀ | | Mars | ♂ | | >>> >>> What I should see in the right column are the standard glyphs for >>> Venus and Mars. >>> >>> Any ideas about how to get the MySQL Command-Line Tool to display >>> Unicode properly? >> what operating system >> what terminal >> >> all recent Linux systems have UTF8 as default >> > > I am running Ubuntu MATE 16.04. I have the problem also on Windows 7 and on Mac OS Version 10.11.6. I do not think that the problem has to do with the operating system nor the terminal. Everything about the Unicode text works fine in all tools such as editors, the cat command, etc. It is only when I am running mysql and I issue a SELECT command to see what is in a row. Then the UTF-8 is not rendered properly. I believe the problem is with mysql. Roger If I presume that your terminal has a code page that is utf8-compatible (you say that cat command renders the multibyte characters just fine) then it could be your client-side mysql settings that are rendering those multibyte characters into individual glyphs based on their individual byte values. The next time you are in mysql and have a chance to look at some utf8 data, please collect and share these two reports: status SHOW GLOBAL VARIABLES like '%haracter%'; (you can obfuscate any sensitive details like server names or addresses) Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to get the MySQL Command-Line Tool to display Unicode properly?
I am running Ubuntu MATE 16.04. I have the problem also on Windows 7 and on Mac OS Version 10.11.6. I do not think that the problem has to do with the operating system nor the terminal. Everything about the Unicode text works fine in all tools such as editors, the cat command, etc. It is only when I am running mysql and I issue a SELECT command to see what is in a row. Then the UTF-8 is not rendered properly. I believe the problem is with mysql. Roger On 03/13/2018 03:11 PM, Reindl Harald wrote: Am 13.03.2018 um 22:59 schrieb Roger House: In all respects except one, the treatment of Unicode works just fine. I can write Unicode to database tables, read it, display it, etc., with no problems. The exception is mysql, the MySQL Command-Line Tool. When I execute a SELECT statement to see rows in a table containing the Venus and Mars Unicode characters, here is what I see on the screen: || Venus | ♀ | | Mars | ♂ | | What I should see in the right column are the standard glyphs for Venus and Mars. Any ideas about how to get the MySQL Command-Line Tool to display Unicode properly? what operating system what terminal all recent Linux systems have UTF8 as default -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to get the MySQL Command-Line Tool to display Unicode properly?
Am 13.03.2018 um 22:59 schrieb Roger House: In all respects except one, the treatment of Unicode works just fine. I can write Unicode to database tables, read it, display it, etc., with no problems. The exception is mysql, the MySQL Command-Line Tool. When I execute a SELECT statement to see rows in a table containing the Venus and Mars Unicode characters, here is what I see on the screen: || Venus | ♀ | | Mars | ♂ | | What I should see in the right column are the standard glyphs for Venus and Mars. Any ideas about how to get the MySQL Command-Line Tool to display Unicode properly? what operating system what terminal all recent Linux systems have UTF8 as default -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to get the MySQL Command-Line Tool to display Unicode properly?
Five months ago I posted the query shown below on StackOverflow. I got one reply which was not of much help. So I am trying again, hoping a more MySQL-centric forum might be able to solve my problem. Roger House How to get the MySQL Command-Line Tool to display Unicode properly? I use a Python program to write text containing Unicode characters to a MySQL database. As an example, two of the characters are |u'\u2640' a symbol for Venus or female u'\u2642' a symbol for Mars or male | I use utf8mb4 for virtually all character sets involved with MySQL. Here is an excerpt from /etc/mysql/my.cnf |[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] default-character-set=utf8mb4 character-set-server =utf8mb4 character_set_system =utf8mb4 | In addition, all tables are created with these parameters: |ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | In all respects except one, the treatment of Unicode works just fine. I can write Unicode to database tables, read it, display it, etc., with no problems. The exception is mysql, the MySQL Command-Line Tool. When I execute a SELECT statement to see rows in a table containing the Venus and Mars Unicode characters, here is what I see on the screen: || Venus | ♀ | | Mars | ♂ | | What I should see in the right column are the standard glyphs for Venus and Mars. Any ideas about how to get the MySQL Command-Line Tool to display Unicode properly? Edit: I have done a fair amount of research into the various MySQL system variables, etc., and I now realize that the my.cnf settings shown above have some serious issues. In fact, the server, mysqld, would not launch with the settings shown. To correct things, remove these from [mysqld]: |default-character-set=utf8mb4 character-set-system=utf8mb4 | I'm not sure that the [client] option does anything, but it doesn't seem to hurt. In Python u'\u2640' represents a single Unicode character, namely "♀". This compiles down to three bytes containing the hex value E29980. I am having no problems at all encoding and decoding Unicode. The correct values are being stored in a MySQL table; they are correctly read from the table, and when displayed by a Python program they show up like this: |♀ Venus ♂ Mars | The program output can be redirected to a file, processed by a text editor, etc., and in all cases the correct Unicode symbol is displayed. There is only one place where the correct Unicode symbol is not displayed, and that is when I am using the MySQL Command Line Tool. When I issue a SELECT statement on the table containing the Unicode symbols I get the junk shown above. This is not a Windows specific issue. I have exactly the same problem with the MySQL Command Line Tool when I run it on Windows, Mac OS X, and Ubuntu.
In MySQL 8.0, how does one recognize roles?
Hi, In MySQL 8.0, if you use CREATE ROLE, it seems to create an entry in mysql.users But how does one distinguish between roles and users? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.
Re: How to get the MySQL Command-Line Tool to display Unicode properly
2017/10/18 18:32 ... Roger House: I get the same behavior with the MySQL Command Line Tool when I run it on Windows, Mac OS X, and Ubuntu, so I'm pretty sure the problem has to do with mysql itself. What do you know about the displays to which the client is writing? In the case of "cmd" under Windows, see what others had to do for other programs: https://stackoverflow.com/questions/388490/unicode-characters-in-windows-command-line-how -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to get the MySQL Command-Line Tool to display Unicode properly
I use a Python program to write text containing Unicode characters to a MySQL database. As an example, two of the characters are u'\u2640' a symbol for Venus or female u'\u2642' a symbol for Mars or male I use utf8mb4 for virtually all character sets involved with MySQL. Here is an excerpt from /etc/mysql/my.cnf [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] character-set-server =utf8mb4 I'm not sure that the [client] option does anything, but it doesn't seem to hurt. In addition, all tables are created with these parameters: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci In all respects except one, the treatment of Unicode works just fine. I can write Unicode to database tables, read it, display it, etc., with no problems. The exception is mysql, the MySQL Command-Line Tool. When I execute a SELECT statement to see rows in a table containing the Venus and Mars Unicode characters, here is what I see on the screen: | Venus | ♀ | | Mars | ♂ | What I want to see is this | Venus | ♀ | | Mars | ♂ | I get the same behavior with the MySQL Command Line Tool when I run it on Windows, Mac OS X, and Ubuntu, so I'm pretty sure the problem has to do with mysql itself. Any ideas about how to get the MySQL Command-Line Tool to display Unicode properly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to get auto Increment ID of INSERT?
LAST_INSERT_ID() returns the latest ID for the current connection. As long as you yourself can guarantee that no other queries are executed using that connection, you're fine. If another record others is inserted using another connection, that connection will return a different LAST_INSERT_ID(). Best, / Carsten On 08-10-2015 15:48, Richard Reina wrote: If I insert a record into a table with an auto increment ID how can I get that records ID value? I have read about SELECT LAST_INSERT_ID() statement, however, do not two statements introduce the risk that another insert may occur in the interum? Is there a full proof way of getting the ID of the record that you have just inserted? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to get auto Increment ID of INSERT?
If I insert a record into a table with an auto increment ID how can I get that records ID value? I have read about SELECT LAST_INSERT_ID() statement, however, do not two statements introduce the risk that another insert may occur in the interum? Is there a full proof way of getting the ID of the record that you have just inserted? Thanks
Re: how can i login without a password although in the user-table is a password ?
Bernd wrote: a password ? Hi, this is my system: mysql status -- mysql Ver 14.12 Distrib 5.0.26, for suse-linux (x86_64) using readline 5.1 Connection id: 142883 Current database: mysql Current user: root@localhost SSL:Not in use Current pager: less Using outfile: '' Using delimiter:; Server version: 5.0.26-Max-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 223 days 22 hours 38 min 49 sec Threads: 1 Questions: 9178423 Slow queries: 99 Opens: 607644 Flush tables: 3 Open tables: 64 Queries per second avg: 0.474 my user-table looks like this: mysql select host,user,password from user; ++--+---+ | host | user | password | ++--+---+ | localhost | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | localhost | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | % | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | 127.0.0.1 | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | ++--+---+ I know that this is not the best solution, I will change it. Using mysql on a Linux-system I can login with mysql -u root without supplying a password. How is this possible although there is a password for each user and for root in the user-table ? I found it out by myself: I have a .my.cnf in my home-directory. I forgot. Bernd Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671
how can i login without a password although in the user-table is a password ?
Hi, this is my system: mysql status -- mysql Ver 14.12 Distrib 5.0.26, for suse-linux (x86_64) using readline 5.1 Connection id: 142883 Current database: mysql Current user: root@localhost SSL:Not in use Current pager: less Using outfile: '' Using delimiter:; Server version: 5.0.26-Max-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 223 days 22 hours 38 min 49 sec Threads: 1 Questions: 9178423 Slow queries: 99 Opens: 607644 Flush tables: 3 Open tables: 64 Queries per second avg: 0.474 my user-table looks like this: mysql select host,user,password from user; ++--+---+ | host | user | password | ++--+---+ | localhost | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | localhost | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | % | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | 127.0.0.1 | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | ++--+---+ I know that this is not the best solution, I will change it. Using mysql on a Linux-system I can login with mysql -u root without supplying a password. How is this possible although there is a password for each user and for root in the user-table ? Thanks in advance. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 2294 http://www.helmholtz-muenchen.de/idg Je suis Charlie Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change character sets in InnoDB as fast as possible
Yes, normally convert from latin1 to binary, and from binary to utf8 (which would also be the recipe to convert actual utf8 data which accidentally ended up in latin1 columns to the proper definition without changing the content). I would not know why that would take overly long for you. An alternative if you have the disk space, and the table has no triggers, is using a tool like pt-online-schema change to avoid locking during the change (it creates a shadow table with the proper data and renames the tables once it is done). I am looking for a way to convert about 40GB of InnoDB tables from latin1 character set to utf8. As true conversion will take ages, I had the idea of just changing the character sets (and preferably collation, too) of the tables without actually converting the data. Conversion could be done manually later. From my side it is ok that the data is wrongly encoded in the tables for the time of manual conversion. The goal is to have the tables up and running as soon as possible. I tried to set the character sets with CHANGE, MODIFY, ALTER, but mysql always seems to convert the tables’ data. I read that character sets will be ignored if you convert to binary character sets, but still changing to this set takes ages. So my question is: Is there a way of changing an InnoDB table's character set and collation without letting mysql converting the data? What is the fastest way of changing the character sets, regardless of the method? If converting the data is the only way to go, I thought about converting several tables at the same time, but still this would require a down time of several hours, which is basically unacceptable. Is there no faster way to go? Thanks a lot for your ideas!
How to change character sets in InnoDB as fast as possible
Hi there, I am looking for a way to convert about 40GB of InnoDB tables from latin1 character set to utf8. As true conversion will take ages, I had the idea of just changing the character sets (and preferably collation, too) of the tables without actually converting the data. Conversion could be done manually later. From my side it is ok that the data is wrongly encoded in the tables for the time of manual conversion. The goal is to have the tables up and running as soon as possible. I tried to set the character sets with CHANGE, MODIFY, ALTER, but mysql always seems to convert the tables’ data. I read that character sets will be ignored if you convert to binary character sets, but still changing to this set takes ages. So my question is: Is there a way of changing an InnoDB table's character set and collation without letting mysql converting the data? What is the fastest way of changing the character sets, regardless of the method? If converting the data is the only way to go, I thought about converting several tables at the same time, but still this would require a down time of several hours, which is basically unacceptable. Is there no faster way to go? Thanks a lot for your ideas! Best, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Upgrading How To
Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading How To
Hi Grant, On 12/26/2014 11:18 AM, Grant Peel wrote: Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant There are a few file-level storage changes between 5.x (where x 6) and 5.6 that you may need to resolve before the upgrade. Examples: * 5.6 will not read any tables that were physically created in a version older than 5.0 and never rebuilt using a newer version. * the YEAR(2) data type is no longer supported. * pre 4.1 passwords - If you are upgrading from version 5.1 or older, you will need to update their hashes or configure 5.6 to recognize the older hashes as valid. The user authentication system in 5.6 is more advanced than in earlier versions. Several features are removed as of 5.6 http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html#mysql-nutshell-removals Many defaults were changed starting with 5.6. These and other things to consider before a move to 5.6 (like the SQL Mode and timestamp behaviors) are all listed here: http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html mysql_upgrade will update the system tables in the `mysql` database and run a CHECK TABLE ... FOR UPGRADE on all your tables but it cannot handle all of the possible upgrade issues you may encounter due to the other things about the server that may have changed. Reindl's technique with the rsync is just like what you are doing with your full-image save/restore. His is just optimized for operating between two live machines. You are also very strongly encouraged to test the upgrade to 5.6 on a lab box long before you push it into production. This will give you the chance to find any of those new 5.6 changes that your clients may not be ready to handle. Yours, -- 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
Re: Upgrading How To
Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. Any other comments are welcome. -G
Re: Upgrading How To
Am 26.12.2014 um 20:52 schrieb Grant Peel: Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. that should be in general fine, i would recommend a scipted optimize table for any tables on the old machine before starting for two reasons * the data to transfer will be smaller * all old tables will be for sure rebuilt and not in 5.0 format the rsync works also fine with a USB stick, ext4 format preferred because owner / permissions, but that can be fixed easily in any case on the new machine per chmod / chown the only difference in your case is that you have a larger downtime if the hot rsync followed by a cold one with a USB stick as destination is noticeable faster needs to be tested, maybe have a empty datadir on the destination and transfer all data is faster then the checksumming signature.asc Description: OpenPGP digital signature
Upgrading How To
Hi all, I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Example: Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. 1) How does one correctly move the users and all the permissions (grant tables), 2) How does one move the data. 3) Assume lots of backups have been done and there is no risk of permanently loosing data. Also, move/copy to be done using files (to memstick or external disk or NFS), not using ssh directly (rcopy etc). 4) Basically, dumpt the data and users and perms and put it on a new box J I suspect this question is trivial to a lot of admins out there, but, I suspect it would be helpful to many out there. Happy Holidays! -Grant
Re: Upgrading How To
Am 25.12.2014 um 16:01 schrieb Grant Peel: I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. * setup the new box * stop mysqld on the old * rsync /var/lib/mysqld to the new one * start mysql on the new one * mysql_upgrade -u root -p * enter root pwd * done to keep downtime as low as possible rsync one or two times *hot* without stop mysqld on the old machine so the last rsync only transfer diffs - doing that for 12 years now from MySQl 3.x to 5.5 between Windows, MacOSX and Linux systems in all directions rsync params: --force --delete-after -tPrlpogEAX signature.asc Description: OpenPGP digital signature
Re: How to retrieve next record?
Thanks Martin,
But loading ALL the records sums up to (now) 10 mio records and further
on in future up to 100 mio records.
H, don't think that's wise.
Nevertheless, thanks for thinking alog.
BR, Hans.
Martin Gainty schreef op 11-12-14 om 23:33:
If you *dont* mind implementing your fetch in PHP:
Get ALL of the records into a resultset
then fetch each record one a time from the resultset e.g.
if($con != null)
{ //start $con!=null
$res = mysqli_query($con, SELECT *FROM Table); //we have all
records in $res
if ($res != null)
{ //check to make sure resultset != null
$rowcount = 0;
$row=mysqli_fetch_array($res,MYSQLI_ASSOC); //get the first
record from the resultset $res
while($rowcount $res-num_rows)
{ // start while
$row = mysqli_fetch_array($res); //get the NEXT
record from the resultset $res
$rowcount = $rowcount + 1;
}
} //end res!=null
} //end $con!=null
Martin Gainty
Date: Thu, 11 Dec 2014 14:07:33 -0800
Subject: Re: How to retrieve next record?
From: mussa...@csz.com
To: larry.mart...@gmail.com
CC: mysql@lists.mysql.com
On Thu, December 11, 2014 13:43, Larry Martell wrote:
On Thu, Dec 11, 2014 at 4:34 PM, Trianon 33 triano...@gmail.com wrote:
Hi all,
It seems like a simple question, however I'm not able to find the
answer.
Let me explain. I have a DB with lots of columns, one is labeled as
'kenteken' and it is also the primary key in the DB. I poke into this DB
with the command
$sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken =
'$sActueelkenteken';
This is ok and gives me the correct result. However, then I want the
next
row (according to the primary key), but with what command?
That's were I'm stuck.
Didn't find anything like NEXT, so... what to look for?
What do you mean by 'next' row? Since you're querying by primary key
you will only get one row, right?
Related what is the form of the prmary key. If its numeric something like
$sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken
'$sActueelkenteken' limit 1
might work.
--
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154
--
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: How to retrieve next record?
Thanks Johan, You're partly right in your interpretation, but not in total :-) Later on in time I'll lift a tip. I need some time to process your answer and do my homework. Nevertheless I've got already something that works, but maybe isn't the most efficient. Nevertheless a good start, thanks. BR, Hans. Johan De Meersman schreef op 11-12-14 om 23:44: - Original Message - From: Wm Mussatto mussa...@csz.com Subject: Re: How to retrieve next record? Related what is the form of the prmary key. If its numeric something like $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken '$sActueelkenteken' limit 1 might work. No, kenteken is dutch for license plate. If so, not numeric, although greater/less comparisons do work on strings, too. My guess, from the sample queries, would be that this is processing for some form of automated number plate recognition system :-) Now, Hans, besides pointing you in the right direction, I'm going to be whining a bit about some pet peeves of mine. I'm waiting for the start of a midnight intervention, anyway :-p That query, as pointed out already, is only asking for a single kenteken. I'll stick to the dutch column names for clarity for other readers, btw - although one of the aforementioned pet peeves is nonenglish variable names. Makes code an absolute bitch to maintain for someone who doesn't speak that language. That's from experience; I've had to debug crap in french and spanish, among other languages. Your code (or, more precisely, the DB driver) is only going to make those records available to your program that you have explicitly asked for, so that query will only ever make the one record available. You will need to build a query that returns all the records you want to access, or, alternatively, make repeated queries. The former is more efficient by far; the latter is useful if the next set depends on what you find in the previous set. Another pet peeve: don't use select *. Explicitly select the columns you're looking for. It a) saves network bandwith; b) guards against later table structure changes; c) potentially allows the use of covering indexes and d) reduces the server memory footprint required for sorting etc. Once you built the correct query, you'll need to have a cursor to loop through it. Your DB driver will probably refer to it as a resultset or a similar denomination. The typical buildup for a database connection (bar advanced abstraction layers) is db_connect (returns a database handle); dbh-execute(sql) (returns a resultset handle); loop using rs-fetch_next (probably returns an array or hash with the data). See your language's db class documentation for the gritty details there. You may also find a fetch_all or similar which returns you the entire resultset in a single call. Can be useful, but remember that that means allocating memory clientside for the entire dataset in one go, instead of reusing the same variables row for row. A further pet peeve: don't just dump variables into your sql string, use bind variables. The easy method opens you up for little Bobby Tables. Google that, if you're unfamiliar with it. Then weep in despair :-p The idea of bind variables is fairly simple: you stick placeholders in your sql string where you would otherwise use string interpolation; then tell the statement handle the variables that should go in there. The database is actually aware of this method, so there is no chance that the variables might get interpreted as part of the SQL - it KNOWS they're variables, not keywords. Additionally, if you're going to be executing the same statement repeatedly, use prepared statements instead of regular executes. On MySQL the benefit is marginal (but still noticeable), on other databases it might be considerable - sometimes orders of magnitude faster. Oracle, for instance, has an execution plan cache; so if you use prepared statements, it can skip the whole parse - analyze - pick plan bit and skip straight to the next execution round with the new values you provided. On fast statements (like primary key lookups) that can sometimes save 80% and more of the roundtrip time. The abovementioned where-clause with limit is probably also going to work; but then you'll need to re-query time after time; and limit does not always work quite intuitively - although in this simple case, it does. If you *must* re-query time after time, do a speed comparison with and without prepared statements; otherwise do go for the fetch_next loop. Now, you've got documentation to read, I believe. Off you go :-) /johan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to retrieve next record?
Hi all, It seems like a simple question, however I'm not able to find the answer. Let me explain. I have a DB with lots of columns, one is labeled as 'kenteken' and it is also the primary key in the DB. I poke into this DB with the command $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken = '$sActueelkenteken'; This is ok and gives me the correct result. However, then I want the next row (according to the primary key), but with what command? That's were I'm stuck. Didn't find anything like NEXT, so... what to look for? Thanks for any hints, best regards, Hans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to retrieve next record?
On Thu, Dec 11, 2014 at 4:34 PM, Trianon 33 triano...@gmail.com wrote: Hi all, It seems like a simple question, however I'm not able to find the answer. Let me explain. I have a DB with lots of columns, one is labeled as 'kenteken' and it is also the primary key in the DB. I poke into this DB with the command $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken = '$sActueelkenteken'; This is ok and gives me the correct result. However, then I want the next row (according to the primary key), but with what command? That's were I'm stuck. Didn't find anything like NEXT, so... what to look for? What do you mean by 'next' row? Since you're querying by primary key you will only get one row, right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to retrieve next record?
On Thu, December 11, 2014 13:43, Larry Martell wrote: On Thu, Dec 11, 2014 at 4:34 PM, Trianon 33 triano...@gmail.com wrote: Hi all, It seems like a simple question, however I'm not able to find the answer. Let me explain. I have a DB with lots of columns, one is labeled as 'kenteken' and it is also the primary key in the DB. I poke into this DB with the command $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken = '$sActueelkenteken'; This is ok and gives me the correct result. However, then I want the next row (according to the primary key), but with what command? That's were I'm stuck. Didn't find anything like NEXT, so... what to look for? What do you mean by 'next' row? Since you're querying by primary key you will only get one row, right? Related what is the form of the prmary key. If its numeric something like $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken '$sActueelkenteken' limit 1 might work. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to retrieve next record?
Please reply to the list and not to me directly On Thu, Dec 11, 2014 at 5:10 PM, Trianon 33 triano...@gmail.com wrote: Larry Martell schreef op 11-12-14 om 23:04: On Thu, Dec 11, 2014 at 4:58 PM, Trianon 33 triano...@gmail.com wrote: Larry Martell schreef op 11-12-14 om 22:43: On Thu, Dec 11, 2014 at 4:34 PM, Trianon 33 triano...@gmail.com wrote: Hi all, It seems like a simple question, however I'm not able to find the answer. Let me explain. I have a DB with lots of columns, one is labeled as 'kenteken' and it is also the primary key in the DB. I poke into this DB with the command $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken = '$sActueelkenteken'; This is ok and gives me the correct result. However, then I want the next row (according to the primary key), but with what command? That's were I'm stuck. Didn't find anything like NEXT, so... what to look for? What do you mean by 'next' row? Since you're querying by primary key you will only get one row, right? Sorry for not expressing me right. Let's say may DB contains the following rowd: 0001ES bla bla bla 0002ES bla bla bla 0010DD bla bla bla 0012FF bla bla bla The first row I retrieve with my statement where my variable $sActueelkenteken = 0001ES. This is succesfully. after that I want the next record which happens to be 0002ES etc. That's the next record I would like to retrieve. Does this help for the question? The rows in the database are not ordered. What makes 0002ES the row you want? You have to change your query to select the rows you want and control the order with a sort or within your program. Hello Larry, hm. My rows are ordered. No, the data in the database is not ordered. 0001ES, comes before 0002ES, etc. But so far that doesn't really matter. My DB consists of approx 10 mio records, all with this 6-digit field kenteken which is 6 positions. All records are sorted by primary key which is on the field kenteken. All I want to do is select a record (not neccesarily the first in the DB) and from there I want simply the next record based on the order of the primary key. Somewhere in the middle I would see record FF, followed by ZZ for example and somewhere to the end I would see ZZ. Makes this explanation it somewhat more understandable? You would have to know the range of the items you want, e.g.: SELECT * FROM kentekenlogtest WHERE kenteken = $firstOneYouWant and kenteken = $lastOneYouWant Or if you know how many you want you could do: SELECT * FROM kentekenlogtest WHERE kenteken = $firstOneYouWant limit $howManyYouWant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to retrieve next record?
Trianon 33 schreef op 11-12-14 om 23:20: Wm Mussatto schreef op 11-12-14 om 23:07: On Thu, December 11, 2014 13:43, Larry Martell wrote: On Thu, Dec 11, 2014 at 4:34 PM, Trianon 33 triano...@gmail.com wrote: Hi all, It seems like a simple question, however I'm not able to find the answer. Let me explain. I have a DB with lots of columns, one is labeled as 'kenteken' and it is also the primary key in the DB. I poke into this DB with the command $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken = '$sActueelkenteken'; This is ok and gives me the correct result. However, then I want the next row (according to the primary key), but with what command? That's were I'm stuck. Didn't find anything like NEXT, so... what to look for? What do you mean by 'next' row? Since you're querying by primary key you will only get one row, right? Related what is the form of the prmary key. If its numeric something like $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken '$sActueelkenteken' limit 1 might work. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 Thanks for the reply William, But should the sign not be a sign (in order to get the next record)? Since the next record is than the actual record? BR, Hans. Actually this did the trick. Thanks for thinking along with me. BR, Hans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to retrieve next record?
If you *dont* mind implementing your fetch in PHP:
Get ALL of the records into a resultset
then fetch each record one a time from the resultset e.g.
if($con != null)
{ //start $con!=null
$res = mysqli_query($con, SELECT *FROM Table); //we have all
records in $res
if ($res != null)
{ //check to make sure resultset != null
$rowcount = 0;
$row=mysqli_fetch_array($res,MYSQLI_ASSOC); //get the first
record from the resultset $res
while($rowcount $res-num_rows)
{ // start while
$row = mysqli_fetch_array($res); //get the NEXT
record from the resultset $res
$rowcount = $rowcount + 1;
}
} //end res!=null
} //end $con!=null
Martin Gainty
Date: Thu, 11 Dec 2014 14:07:33 -0800
Subject: Re: How to retrieve next record?
From: mussa...@csz.com
To: larry.mart...@gmail.com
CC: mysql@lists.mysql.com
On Thu, December 11, 2014 13:43, Larry Martell wrote:
On Thu, Dec 11, 2014 at 4:34 PM, Trianon 33 triano...@gmail.com wrote:
Hi all,
It seems like a simple question, however I'm not able to find the
answer.
Let me explain. I have a DB with lots of columns, one is labeled as
'kenteken' and it is also the primary key in the DB. I poke into this DB
with the command
$sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken =
'$sActueelkenteken';
This is ok and gives me the correct result. However, then I want the
next
row (according to the primary key), but with what command?
That's were I'm stuck.
Didn't find anything like NEXT, so... what to look for?
What do you mean by 'next' row? Since you're querying by primary key
you will only get one row, right?
Related what is the form of the prmary key. If its numeric something like
$sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken
'$sActueelkenteken' limit 1
might work.
--
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: How to retrieve next record?
- Original Message - From: Wm Mussatto mussa...@csz.com Subject: Re: How to retrieve next record? Related what is the form of the prmary key. If its numeric something like $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken '$sActueelkenteken' limit 1 might work. No, kenteken is dutch for license plate. If so, not numeric, although greater/less comparisons do work on strings, too. My guess, from the sample queries, would be that this is processing for some form of automated number plate recognition system :-) Now, Hans, besides pointing you in the right direction, I'm going to be whining a bit about some pet peeves of mine. I'm waiting for the start of a midnight intervention, anyway :-p That query, as pointed out already, is only asking for a single kenteken. I'll stick to the dutch column names for clarity for other readers, btw - although one of the aforementioned pet peeves is nonenglish variable names. Makes code an absolute bitch to maintain for someone who doesn't speak that language. That's from experience; I've had to debug crap in french and spanish, among other languages. Your code (or, more precisely, the DB driver) is only going to make those records available to your program that you have explicitly asked for, so that query will only ever make the one record available. You will need to build a query that returns all the records you want to access, or, alternatively, make repeated queries. The former is more efficient by far; the latter is useful if the next set depends on what you find in the previous set. Another pet peeve: don't use select *. Explicitly select the columns you're looking for. It a) saves network bandwith; b) guards against later table structure changes; c) potentially allows the use of covering indexes and d) reduces the server memory footprint required for sorting etc. Once you built the correct query, you'll need to have a cursor to loop through it. Your DB driver will probably refer to it as a resultset or a similar denomination. The typical buildup for a database connection (bar advanced abstraction layers) is db_connect (returns a database handle); dbh-execute(sql) (returns a resultset handle); loop using rs-fetch_next (probably returns an array or hash with the data). See your language's db class documentation for the gritty details there. You may also find a fetch_all or similar which returns you the entire resultset in a single call. Can be useful, but remember that that means allocating memory clientside for the entire dataset in one go, instead of reusing the same variables row for row. A further pet peeve: don't just dump variables into your sql string, use bind variables. The easy method opens you up for little Bobby Tables. Google that, if you're unfamiliar with it. Then weep in despair :-p The idea of bind variables is fairly simple: you stick placeholders in your sql string where you would otherwise use string interpolation; then tell the statement handle the variables that should go in there. The database is actually aware of this method, so there is no chance that the variables might get interpreted as part of the SQL - it KNOWS they're variables, not keywords. Additionally, if you're going to be executing the same statement repeatedly, use prepared statements instead of regular executes. On MySQL the benefit is marginal (but still noticeable), on other databases it might be considerable - sometimes orders of magnitude faster. Oracle, for instance, has an execution plan cache; so if you use prepared statements, it can skip the whole parse - analyze - pick plan bit and skip straight to the next execution round with the new values you provided. On fast statements (like primary key lookups) that can sometimes save 80% and more of the roundtrip time. The abovementioned where-clause with limit is probably also going to work; but then you'll need to re-query time after time; and limit does not always work quite intuitively - although in this simple case, it does. If you *must* re-query time after time, do a speed comparison with and without prepared statements; otherwise do go for the fetch_next loop. Now, you've got documentation to read, I believe. Off you go :-) /johan -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
how to access Synology's mysql (mariadb) on the command line
My Synology station is on 192.168.178.27, the database listens to port 3306, on my FritzBox I forwarded port 3306 to 192.168.178.27, I /can/ connect to the database on http://192.168.178.27/phpMyAdmin/ But when I try: mysql --host=192.168.178.27 --password=* --user=wybo I get: ERROR 1045 (28000): Access denied for user 'wybo'@'wybo.fritz.box' (using password: YES) What am I doing wrong? -- Wybo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to access Synology's mysql (mariadb) on the command line
Hi Wybo, On 8/20/2014 3:47 PM, Wybo wrote: My Synology station is on 192.168.178.27, the database listens to port 3306, on my FritzBox I forwarded port 3306 to 192.168.178.27, I /can/ connect to the database on http://192.168.178.27/phpMyAdmin/ But when I try: mysql --host=192.168.178.27 --password=* --user=wybo I get: ERROR 1045 (28000): Access denied for user 'wybo'@'wybo.fritz.box' (using password: YES) What am I doing wrong? Access is granted only if three parts are correct: 1) the login you are using (wybo) 2) the password for the login 3) the host you are connecting from (wybo.fritz.box) is allows to use that account. It's #3 that most people forget about. Run this query SELECT host FROM mysql.user WHERE user='wybo'; If you see a pattern in the results that would match your host's name, then you need to compare your password hashes. If you don't know if you have a matching host pattern, post the list of host patterns you got from the query to the list. We can tell you. Regards, -- 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
Re: how to access Synology's mysql (mariadb) on the command line
Hello Wybo, I cleansed your reply and cc:'ed the list again to share the answer. On 8/20/2014 4:24 PM, Wybo wrote: Hi Shawn, Thanks for your prompt reply - I suppose I'll have to do that query via phpMysqlAdmin. When I do that, the only host that appears is localhost. However, when I browse the user table, I also see %edited%, which is the hostname of the synology station, see the attached screenshot (%also edited%). Does this mean that I have to add a new entry in this table? If so, can I do that via phpMysqlAdmin? Yes, you will need to use your phpMysqlAdmin session to issue an appropriate GRANT command so that the 'wybo' user can login from 'wybo.fritz.box'. Example - GRANT the permissions you want to give on *.* to 'wybo'@'wybo.fritz.box' IDENTIFIED BY 'password goes here in plain text' Research the GRANT command itself (and the other account management commands) to see what else you can do while creating an account or adjusting permissions. http://dev.mysql.com/doc/refman/5.6/en/account-management-sql.html Examples of the types of host patterns you can use are also in the manual, here: http://dev.mysql.com/doc/refman/5.6/en/account-names.html Yours, -- 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
Re: how to access Synology's mysql (mariadb) on the command line
Yes, that worked - thank you very much! On 2014-08-20 22:51, shawn l.green wrote: Hello Wybo, I cleansed your reply and cc:'ed the list again to share the answer. On 8/20/2014 4:24 PM, Wybo wrote: Hi Shawn, Thanks for your prompt reply - I suppose I'll have to do that query via phpMysqlAdmin. When I do that, the only host that appears is localhost. However, when I browse the user table, I also see %edited%, which is the hostname of the synology station, see the attached screenshot (%also edited%). Does this mean that I have to add a new entry in this table? If so, can I do that via phpMysqlAdmin? Yes, you will need to use your phpMysqlAdmin session to issue an appropriate GRANT command so that the 'wybo' user can login from 'wybo.fritz.box'. Example - GRANT the permissions you want to give on *.* to 'wybo'@'wybo.fritz.box' IDENTIFIED BY 'password goes here in plain text' -- Wybo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
how to improve mysql's query speed in table 'token' of keystone,when using in openstack with lots of VMs?
when i used mysql as the keystone's backend in openstack ,i found that the
'token' table saved 29 millions record (using myisam as engine,the size of
token.MYD is 100G) and have 4 new token save per second. That result to the
slow query of a token .since of inserting new token frequently,how could i set
the configure to speed up the query operation.
the token's struct is id,expires,extra,valid,user_id with index {expires,valid}
and the select sql is select id,expires,extra,valid,user_id from token where
valid=1 and expires ='-XX-XX XX:XX:XX' and user_id
='XXX';with often return 2 results.
Here is some db status data in a real openstack environment with 381 active
VMs:
+---+-+
| Variable_name | Value |
+---+-+
| Handler_read_first | 259573419 |
| Handler_read_key | 1344821219 |
| Handler_read_next | 3908969530 |
| Handler_read_prev | 1235 |
| Handler_read_rnd | 1951101 |
| Handler_read_rnd_next | 48777237518 |
+---+-+
and
+-++
| Variable_name | Value |
+-++
| Qcache_free_blocks | 498 |
| Qcache_free_memory | 1192512 |
| Qcache_hits | 1122242834 |
| Qcache_inserts | 352700155 |
| Qcache_lowmem_prunes | 34145019 |
| Qcache_not_cached | 1529123943 |
| Qcache_queries_in_cache | 1681 |
| Qcache_total_blocks | 4949 |
+-++
it seems that the 'insert' operation of saving new token affects the query
buffer,and result of a low-level of query-hit's rate.
please give me some help,thanks.
Re: How to write a multi query in mysqltest framework?
On Thu, Jul 10, 2014 at 10:33:04AM +0800, 娄帅 wrote: In the C API, we can call mysql_query(select 1; select 2); which just send the command once to the server, and server return two result sets, So i want to know if there is a command in the mysqltest framework to do the job? I want to write a test case like that. The client knows about statement bounds from query delimiter. By default the delimiter is semicolon. You can change it to something else with 'delimiter' command: delimiter |; select 1; select 2;| BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Cannot Update, at least not how I'm telling it to!
Hi. mysql describe unions_data; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | NO | PRI | NULL| auto_increment | | Title | varchar(70) | NO | | NULL|| | Description | text| YES | | NULL|| | UsersID | int(11) | NO | MUL | NULL|| +-+-+--+-+-++ 4 rows in set (0.02 sec) mysql select * from unions_data; ++---+-+-+ | ID | Title | Description | UsersID | ++---+-+-+ | 4 | Union One | descr one | 2 | | 5 | Union Two | descr two | 2 | ++---+-+-+ 2 rows in set (0.00 sec) mysql update unions_data set Title='Dos' and Description='dos' where ID=5; ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Dos' mysql update unions_data set Title=2.2 and Description='dos' where ID=5; Query OK, 1 row affected (0.33 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from unions_data; ++---+-+-+ | ID | Title | Description | UsersID | ++---+-+-+ | 4 | Union One | descr one | 2 | | 5 | 0 | descr two | 2 | ++---+-+-+ 2 rows in set (0.00 sec) Notice that it threw an error about an incorrect DOUBLE value for a field that is a varchar. Notice that when it did decide to update, it updated incorrectly and it updated Title but _not_ Description, yet issued no Warnings. Am I missing something?? TIA, Savi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Cannot Update, at least not how I'm telling it to!
The AND in your UPDATE clause makes this a logical operator instead of being a SQL keyword. Your syntax is wrong: UPDATE ... SET mycol = value, mycol2 = value [WHERE ... ] With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -Original Message- From: Surya Savarika Sent: Wednesday, July 09, 2014 3:56 PM To: mysql@lists.mysql.com Subject: Cannot Update, at least not how I'm telling it to! Hi. mysql describe unions_data; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | NO | PRI | NULL| auto_increment | | Title | varchar(70) | NO | | NULL|| | Description | text| YES | | NULL|| | UsersID | int(11) | NO | MUL | NULL|| +-+-+--+-+-++ 4 rows in set (0.02 sec) mysql select * from unions_data; ++---+-+-+ | ID | Title | Description | UsersID | ++---+-+-+ | 4 | Union One | descr one | 2 | | 5 | Union Two | descr two | 2 | ++---+-+-+ 2 rows in set (0.00 sec) mysql update unions_data set Title='Dos' and Description='dos' where ID=5; ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Dos' mysql update unions_data set Title=2.2 and Description='dos' where ID=5; Query OK, 1 row affected (0.33 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from unions_data; ++---+-+-+ | ID | Title | Description | UsersID | ++---+-+-+ | 4 | Union One | descr one | 2 | | 5 | 0 | descr two | 2 | ++---+-+-+ 2 rows in set (0.00 sec) Notice that it threw an error about an incorrect DOUBLE value for a field that is a varchar. Notice that when it did decide to update, it updated incorrectly and it updated Title but _not_ Description, yet issued no Warnings. Am I missing something?? TIA, Savi -- 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
How to write a multi query in mysqltest framework?
Hi, all, In the C API, we can call mysql_query(select 1; select 2); which just send the command once to the server, and server return two result sets, So i want to know if there is a command in the mysqltest framework to do the job? I want to write a test case like that. Thank you for your reply!
Re: How to get all known bugs on specified mysql version?
don't use GTID is my suggestion :) 2014-07-07 7:42 GMT+08:00 娄帅 louis.hust...@gmail.com: Hi, all, I want to use MySQL 5.6.18 in production, so i want to get all known bugs on this version. Now i check the bugfix section in MySQL 5.6.19 and above version. Am i doing the right thing? Or Do you have a better method? Any input will be appreciated! -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
Re: How to get all known bugs on specified mysql version?
Why do not use GTID? Just curious...WB 2014-07-07 3:18 GMT-03:00 louis liu yloui...@gmail.com: don't use GTID is my suggestion :) 2014-07-07 7:42 GMT+08:00 娄帅 louis.hust...@gmail.com: Hi, all, I want to use MySQL 5.6.18 in production, so i want to get all known bugs on this version. Now i check the bugfix section in MySQL 5.6.19 and above version. Am i doing the right thing? Or Do you have a better method? Any input will be appreciated! -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
Re: How to get all known bugs on specified mysql version?
Am 07.07.2014 12:37, schrieb wagnerbianchi.com: Why do not use GTID? Just curious...WB just read the changelogs and endless count of bugreports it's a new feature and it works as buggy as replication years ago where you had the rebuild the slave all the time and maybe still if temporary tables and memory tables are part of the game - i had a reason to switch to normal tables with hash-names and remove them finally since that didn't break replication all the time 2014-07-07 3:18 GMT-03:00 louis liu yloui...@gmail.com: don't use GTID is my suggestion :) 2014-07-07 7:42 GMT+08:00 娄帅 louis.hust...@gmail.com: Hi, all, I want to use MySQL 5.6.18 in production, so i want to get all known bugs on this version. Now i check the bugfix section in MySQL 5.6.19 and above version. Am i doing the right thing? Or Do you have a better method? Any input will be appreciated! signature.asc Description: OpenPGP digital signature
Re: How to get all known bugs on specified mysql version?
Hello, On 7/6/2014 7:42 PM, 娄帅 wrote: Hi, all, I want to use MySQL 5.6.18 in production, so i want to get all known bugs on this version. Now i check the bugfix section in MySQL 5.6.19 and above version. Am i doing the right thing? Or Do you have a better method? Any input will be appreciated! The easiest way to see which bugs are pending is to actually search the bugs database, http://bugs.mysql.com Here is one example search you can perform. Note, many of these bugs (such as bug #49728) are low-impact edge-cases that are prioritized lower than other more important high-impact bugs. Our development teams have limited resources. We triage and prioritize what they can work on based on how severe the bug is, how often is may be encountered, and how easy it may be to work around. http://bugs.mysql.com/search.php?search_for=status[]=Activeseverity=limit=Allorder_by=idcmd=displayphpver=5.5os=0os_details=bug_age=0tags=similar=target=last_updated=0defect_class=allworkaround_viability=allimpact=allfix_risk=allfix_effort=alltriageneeded= Anyone can join the fight! Start by submitting an OCA (Oracle Contributor's Agreement). http://www.oracle.com/technetwork/community/oca-486395.html If you have any questions about the OCA, please contact the MySQL community team. http://www.mysql.com/about/contact/?topic=community Then, any patches you provide can be analyzed, possibly improved, and potentially merged into the actual source code. For some recent examples, see: http://www.tocker.ca/2014/06/09/mysql-5-6-19-community-release-notes.html -- 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
How to get all known bugs on specified mysql version?
Hi, all, I want to use MySQL 5.6.18 in production, so i want to get all known bugs on this version. Now i check the bugfix section in MySQL 5.6.19 and above version. Am i doing the right thing? Or Do you have a better method? Any input will be appreciated!
How to get a S-lock in a Transaction
Hi: I tried to get a S-lock using following SQL: 1. start transaction; SELECT * FROM test.t1 where id=1; But I found this way does not work. I changed it as following: 2. start transaction; SELECT * FROM test.t1 where id=1 lock in share mode; I am wondering why the first way does not get a S lock?
Re: Big innodb tables, how can I work with them?
2014-05-15 14:26 GMT+02:00 Antonio Fernández Pérez antoniofernan...@fabergroup.es: Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM). Adding more RAM will only save you for a few weeks/months until the data isn't able to fit in memory any longer. You will face the same problem soon (if your data is and will be still growing). There will be a point where you just can't buy more and better hardware (actually you kinda can, but you will spend load of money and might end up with nice servers just doing nothing because they support more memory in their motherboard so you need to upgrade it too). You should give your application a thought and start considering noSQL/table sharding/partitioning/archiving. Maybe it is too late, but before needing another hardware upgrade, yo should've thought about a solution that would allow you keep growing without needing to spend all in hardware (unless you have unlimited money). Good luck! Manuel.
Re: Big innodb tables, how can I work with them?
- Original Message - From: Manuel Arostegui man...@tuenti.com Subject: Re: Big innodb tables, how can I work with them? noSQL/table sharding/partitioning/archiving. I keep wondering how people believe that NoSQL solutions magically don't need RAM to work. Nearly all of them slow down to a crawl, many even worse than an SQL database, as soon as the full or working set no longer fits in memory, too. Don't get me wrong - they have certain benefits and definite usecases, but it's time people stop presenting them as a magic bullet. They require understanding and work, just like any other technology. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Big innodb tables, how can I work with them?
2014-05-19 11:49 GMT+02:00 Johan De Meersman vegiv...@tuxera.be: - Original Message - From: Manuel Arostegui man...@tuenti.com Subject: Re: Big innodb tables, how can I work with them? noSQL/table sharding/partitioning/archiving. I keep wondering how people believe that NoSQL solutions magically don't need RAM to work. Nearly all of them slow down to a crawl, many even worse than an SQL database, as soon as the full or working set no longer fits in memory, too. Don't get me wrong - they have certain benefits and definite usecases, but it's time people stop presenting them as a magic bullet. They require understanding and work, just like any other technology. I was thinking about its distributed system as it might speed up reads :-) We do have a huge noSQL cluster here at work and it certainly needs lot of RAM. Manuel
Re: Big innodb tables, how can I work with them?
What kind of queries is this table serving? 8GB is not a huge amount of data at all and IMO it's not enough to warrant sharding. On Thu, May 15, 2014 at 1:26 PM, Antonio Fernández Pérez antoniofernan...@fabergroup.es wrote: Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM). Any ideas? Thanks in advance. Regards, Antonio.
Re: Big innodb tables, how can I work with them?
Hi, Thanks for your replies. In our case, we can't implement NOSQL solution. Thats requires modify/check all our application and all services (Including FreeRADIUS that I'm not sure if it's compatible). Andrew, I have heard about people that has a lot of data, more than me. I know that MySQL support this amount but in this case and thinking in the future, I have this problem with my architecture; how can I grow in database servers without delete rows in the tables. I have checked slow queries and now there aren't. These tables are serving queries from FreeRADIUS service. For example, SUMs, COUNTS, nomal SELECTs ... Always with a where condition. Excuse me, what is the meaning of IMO? Thanks. Regards, Antonio.
Big innodb tables, how can I work with them?
Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM). Any ideas? Thanks in advance. Regards, Antonio.
Re: Big innodb tables, how can I work with them?
Am 15.05.2014 14:26, schrieb Antonio Fernández Pérez: I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM) rule of thumbs is innodb_buffer_pool = database-size or at least as much RAM that frequently accessed data stays always in the pool signature.asc Description: OpenPGP digital signature
how to set SET SQL_BIG_SELECTS=1 ?
Dear all,
After upgrading from mysql 5.0 to mysql 5.1, i am getting the follwing
error:
how to set SET SQL_BIG_SELECTS=1 ?
Error is
*Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check
your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the
SELECT is okay query: drupal_get_filename /* Guest : drupal_get_filename */
SELECT filename FROM system WHERE name = 'user' AND type = 'module' in
*/home/User/public_html/includes/database.mysql.inc* on line *136*
*Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check
your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the
SELECT is okay query: drupal_lookup_path /* Guest : drupal_lookup_path */
SELECT src FROM url_alias WHERE dst = 'welcome' AND language IN('en', '')
ORDER BY language DESC, pid DESC in
*/home/User/public_html/includes/database.mysql.inc* on line *136*
*Fatal error*: Call to undefined function filter_xss_admin() in
*/home/User/public_html/includes/common.inc* on line
*369*##
Thank you
Re: how to set SET SQL_BIG_SELECTS=1 ?
Am 28.02.2014 12:17, schrieb Madan Thapa: After upgrading from mysql 5.0 to mysql 5.1, i am getting the follwing error: how to set SET SQL_BIG_SELECTS=1? just type it before your query in question? as you can see below if you try such things and they are not supported or you have a typo you get a crear error, otherwise OK MariaDB [(none)] SET SQL_BIG_SELECTS=1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] SET SQL_BIG_SELEtCTS=1; ERROR 1193 (HY000): Unknown system variable 'SQL_BIG_SELEtCTS' *Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay query: drupal_get_filename /* Guest : drupal_get_filename */ SELECT filename FROM system WHERE name = 'user' AND type = 'module' in */home/User/public_html/includes/database.mysql.inc* on line *136* signature.asc Description: OpenPGP digital signature
Re: how to set SET SQL_BIG_SELECTS=1 ?
Thank you , however i am newbie to mysql and not sure where to put that (
SET SQL_BIG_SELECTS=1;) :
when i grep , i see following files for example that has query code:
[root@server public_html]# grep -R SELECT filename . | grep -v error_lo
./sites/all/modules/views/includes/admin.inc:$filename =
db_result(db_query(SELECT filename FROM {system} WHERE type = 'module' AND
name = 'advanced_help'));
./sites/all/modules/ubercart/docs/hooks.php: $filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d,
$file_id));
./sites/all/modules/ubercart/uc_file/uc_file.module: $files =
db_query(SELECT filename FROM {uc_files} WHERE filename LIKE LOWER('%s'),
'%'. strtolower($url[1]) .'%');
./sites/all/modules/ubercart/uc_file/uc_file.module:$filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $fid));
./sites/all/ubercart/docs/hooks.php: $filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d,
$file_id));
./sites/all/ubercart/uc_file/uc_file.module: $files = db_query(SELECT
filename FROM {uc_files} WHERE filename LIKE LOWER('%s'), '%'.
strtolower($url[1]) .'%');
./sites/all/ubercart/uc_file/uc_file.module:$filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $fid));
./modules/system/system.module: $result = db_query(SELECT filename, name,
type, status, throttle, schema_version FROM {system} WHERE type = '%s',
$type);
./includes/bootstrap.inc: elseif (db_is_active() (($file =
db_result(db_query(SELECT filename FROM {system} WHERE name = '%s' AND
type = '%s', $name, $type))) file_exists($file))) {
[root@server public_html]#
please advise.
thanks
On Fri, Feb 28, 2014 at 4:57 PM, Reindl Harald h.rei...@thelounge.netwrote:
Am 28.02.2014 12:17, schrieb Madan Thapa:
After upgrading from mysql 5.0 to mysql 5.1, i am getting the follwing
error:
how to set SET SQL_BIG_SELECTS=1?
just type it before your query in question?
as you can see below if you try such things
and they are not supported or you have a typo
you get a crear error, otherwise OK
MariaDB [(none)] SET SQL_BIG_SELECTS=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)] SET SQL_BIG_SELEtCTS=1;
ERROR 1193 (HY000): Unknown system variable 'SQL_BIG_SELEtCTS'
*Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check
your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the
SELECT is okay query: drupal_get_filename /* Guest : drupal_get_filename
*/
SELECT filename FROM system WHERE name = 'user' AND type = 'module' in
*/home/User/public_html/includes/database.mysql.inc* on line *136*
Re: how to set SET SQL_BIG_SELECTS=1 ?
than that is no longer a MySQL question and you should consult
the guy who wrote the code, there are reasons for such limits
and blindly disable them may reuslt in the server going down
because you disable a barrier for crap code
Am 28.02.2014 12:53, schrieb Madan Thapa:
Thank you , however i am newbie to mysql and not sure where to put that (
SET SQL_BIG_SELECTS=1;) :
when i grep , i see following files for example that has query code:
[root@server public_html]# grep -R SELECT filename . | grep -v error_lo
./sites/all/modules/views/includes/admin.inc:$filename =
db_result(db_query(SELECT filename FROM {system} WHERE type = 'module' AND
name = 'advanced_help'));
./sites/all/modules/ubercart/docs/hooks.php: $filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d,
$file_id));
./sites/all/modules/ubercart/uc_file/uc_file.module: $files =
db_query(SELECT filename FROM {uc_files} WHERE filename LIKE LOWER('%s'),
'%'. strtolower($url[1]) .'%');
./sites/all/modules/ubercart/uc_file/uc_file.module:$filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $fid));
./sites/all/ubercart/docs/hooks.php: $filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d,
$file_id));
./sites/all/ubercart/uc_file/uc_file.module: $files = db_query(SELECT
filename FROM {uc_files} WHERE filename LIKE LOWER('%s'), '%'.
strtolower($url[1]) .'%');
./sites/all/ubercart/uc_file/uc_file.module:$filename =
db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $fid));
./modules/system/system.module: $result = db_query(SELECT filename, name,
type, status, throttle, schema_version FROM {system} WHERE type = '%s',
$type);
./includes/bootstrap.inc: elseif (db_is_active() (($file =
db_result(db_query(SELECT filename FROM {system} WHERE name = '%s' AND
type = '%s', $name, $type))) file_exists($file))) {
On Fri, Feb 28, 2014 at 4:57 PM, Reindl Harald h.rei...@thelounge.netwrote:
Am 28.02.2014 12:17, schrieb Madan Thapa:
After upgrading from mysql 5.0 to mysql 5.1, i am getting the follwing
error:
how to set SET SQL_BIG_SELECTS=1?
just type it before your query in question?
as you can see below if you try such things
and they are not supported or you have a typo
you get a crear error, otherwise OK
MariaDB [(none)] SET SQL_BIG_SELECTS=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)] SET SQL_BIG_SELEtCTS=1;
ERROR 1193 (HY000): Unknown system variable 'SQL_BIG_SELEtCTS'
*Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check
your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the
SELECT is okay query: drupal_get_filename /* Guest : drupal_get_filename
*/
SELECT filename FROM system WHERE name = 'user' AND type = 'module' in
*/home/User/public_html/includes/database.mysql.inc* on line *136*
signature.asc
Description: OpenPGP digital signature
How to make multiple master to single slave in mysql?
How to make multiple master replicate to single slave in mysql? Tell me some approaches? Thanks Zhigang
Re: How to make multiple master to single slave in mysql?
On 2/25/2014 9:55 PM, Zhigang Zhang wrote: How to make multiple master replicate to single slave in mysql? Tell me some approaches? Put your masters in a replication ring, hang a slave from one of them (as suggested by Mr. Van der Westhuizen) Use an external product, such as GoldenGate, that can collect data from multiple sources and perform the steps necessary to keep the MySQL instance you are calling the slave updated. (as mentioned by xiangdong...@gmail.com) Write your own daemon or script to poll each master, in turn, by adjusting the slave's replication configuration in some kind of round-robin technique (several of these scripts are on the web). Using the native replication process, no MySQL versions (5.7 or earlier) support one slave replicating data from multiple masters. It is possible to have multiple slaves replicating from a single master but you cannot have one slave repicating from multiple masters. Why are you not reading the responses you are getting? Regards, -- 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
RE: How to make multiple master to single slave in mysql?
OK! Thank you very much! Zhigang. -Original Message- From: shawn l.green [mailto:shawn.l.gr...@oracle.com] Sent: Wednesday, February 26, 2014 11:57 AM To: mysql@lists.mysql.com Subject: Re: How to make multiple master to single slave in mysql? On 2/25/2014 9:55 PM, Zhigang Zhang wrote: How to make multiple master replicate to single slave in mysql? Tell me some approaches? Put your masters in a replication ring, hang a slave from one of them (as suggested by Mr. Van der Westhuizen) Use an external product, such as GoldenGate, that can collect data from multiple sources and perform the steps necessary to keep the MySQL instance you are calling the slave updated. (as mentioned by xiangdong...@gmail.com) Write your own daemon or script to poll each master, in turn, by adjusting the slave's replication configuration in some kind of round-robin technique (several of these scripts are on the web). Using the native replication process, no MySQL versions (5.7 or earlier) support one slave replicating data from multiple masters. It is possible to have multiple slaves replicating from a single master but you cannot have one slave repicating from multiple masters. Why are you not reading the responses you are getting? Regards, -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I mysqldump different database tables to the same .sql file?
--databases, methinks. - Original Message - From: Daevid Vincent dae...@daevid.com To: mysql@lists.mysql.com Sent: Thursday, 21 November, 2013 10:44:39 PM Subject: How do I mysqldump different database tables to the same .sql file? I'm working on some code where I am trying to merge two customer accounts (we get people signing up under different usernames, emails, or just create a new account sometimes). I want to test it, and so I need a way to restore the data in the particular tables. Taking a dump of all the DBs and tables is not feasible as it's massive, and importing (with indexes) takes HOURS. I just want only the tables that are relevant. I can find all the tables that have `customer_id` in them with this magic incantation: SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME` Then I crafted this, but it pukes on the db name portion. :-( mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --tables member_sessions.users_last_login support.tickets mydb1.clear_passwords mydb1.crak_subscriptions mydb1.customers mydb1.customers_free mydb1.customers_free_tracking mydb1.customers_log mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players mydb1content.actors_comments mydb1content.actor_collections mydb1content.actor_likes_users mydb1content.collections mydb1content.dvd_likes_users mydb1content.free_videos mydb1content.genre_collections mydb1content.playlists mydb1content.poll_votes mydb1content.scenes_comments mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections mydb1content.scene_likes_users mydb1content.videos_downloaded mydb1content.videos_viewed merge_backup.sql -- Connecting to localhost... mysqldump: Got error: 1049: Unknown database 'member_sessions.users_last_login' when selecting the database -- Disconnecting from localhost... I searched a bit and found that it seems I have to split this into multiple statements and append like I'm back in 1980. *sigh* mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database member_sessions --tables users_last_login merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database support --tables tickets merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database mydb1 --tables clear_passwords customers customers_free customers_free_tracking customers_log customers_subscriptions customers_transactions players merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database content --tables actors_comments actor_collections actor_likes_users collections dvd_likes_users free_videos genre_collections playlists poll_votes scenes_comments scenes_ratings_users_new2 scene_collections scene_likes_users videos_downloaded videos_viewed merge_backup.sql The critical flaw here is that the mysqldump program does NOT put the necessary USE DATABASE statement in each of these dumps since there is only one DB after the -database apparently. UGH. Nor do I see a command line option to force it to output this seemingly obvious statement. It's a pretty significant shortcoming of mysqldump if you ask me that I can't do it the way I had it in the first example since that's pretty much standard SQL convetion of db.table.column format. And even more baffling is why it wouldn't dump out the USE statement always even if there is only one DB. It's a few characters and would save a lot of headaches in case someone tried to dump their .sql file into the wrong DB on accident. Plus it's not easy to edit a 2.6GB file to manually insert these USE lines. Is there a way to do this with some command line option I'm not seeing in the man page? -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I mysqldump different database tables to the same .sql file?
There is a good reason that the USE database is not output in those dumps.. it would make the tool very difficult to use for moving data around. If I might suggest, a simple workaround is to create a shell script along these lines.. you might to do something a little more sophisticated. # #!/bin/sh echo USE `database1`; outflfile.sql mysqldump -(firstsetofoptions) outfile.sql echo USE `database2`; outflfile.sql mysqldump -(secondsetofoptions) outfile.sql On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent dae...@daevid.com wrote: I'm working on some code where I am trying to merge two customer accounts (we get people signing up under different usernames, emails, or just create a new account sometimes). I want to test it, and so I need a way to restore the data in the particular tables. Taking a dump of all the DBs and tables is not feasible as it's massive, and importing (with indexes) takes HOURS. I just want only the tables that are relevant. I can find all the tables that have `customer_id` in them with this magic incantation: SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME` Then I crafted this, but it pukes on the db name portion. :-( mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --tables member_sessions.users_last_login support.tickets mydb1.clear_passwords mydb1.crak_subscriptions mydb1.customers mydb1.customers_free mydb1.customers_free_tracking mydb1.customers_log mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players mydb1content.actors_comments mydb1content.actor_collections mydb1content.actor_likes_users mydb1content.collections mydb1content.dvd_likes_users mydb1content.free_videos mydb1content.genre_collections mydb1content.playlists mydb1content.poll_votes mydb1content.scenes_comments mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections mydb1content.scene_likes_users mydb1content.videos_downloaded mydb1content.videos_viewed merge_backup.sql -- Connecting to localhost... mysqldump: Got error: 1049: Unknown database 'member_sessions.users_last_login' when selecting the database -- Disconnecting from localhost... I searched a bit and found that it seems I have to split this into multiple statements and append like I'm back in 1980. *sigh* mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database member_sessions --tables users_last_login merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database support --tables tickets merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database mydb1 --tables clear_passwords customers customers_free customers_free_tracking customers_log customers_subscriptions customers_transactions players merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database content --tables actors_comments actor_collections actor_likes_users collections dvd_likes_users free_videos genre_collections playlists poll_votes scenes_comments scenes_ratings_users_new2 scene_collections scene_likes_users videos_downloaded videos_viewed merge_backup.sql The critical flaw here is that the mysqldump program does NOT put the necessary USE DATABASE statement in each of these dumps since there is only one DB after the -database apparently. UGH. Nor do I see a command line option to force it to output this seemingly obvious statement. It's a pretty significant shortcoming of mysqldump if you ask me that I can't do it the way I had it in the first example since that's pretty much standard SQL convetion of db.table.column format. And even more baffling is why it wouldn't dump out the USE statement always even if there is only one DB. It's a few characters and would save a lot of headaches in case someone tried to dump their .sql file into the wrong DB on accident. Plus it's not easy to edit a 2.6GB file to manually insert these USE lines. Is there a way to do this with some command line option I'm not seeing in the man page? -- - michael dykman - mdyk...@gmail.com May the Source be with you.
RE: How do I mysqldump different database tables to the same .sql file?
Except that it outputs the USE statement if you have more than one database, so your theory doesn't hold a lot of water IMHO. Not to mention it's near the very top of the output so it's pretty easy to trim it off if you REALLY needed to move the DB (which I presume is not as frequently as simply wanting a backup/dump of a database to restore). Thanks for the shell script suggestion, that is what I've done already to work around this silliness. -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Thursday, November 21, 2013 1:59 PM To: MySql Subject: Re: How do I mysqldump different database tables to the same .sql file? There is a good reason that the USE database is not output in those dumps.. it would make the tool very difficult to use for moving data around. If I might suggest, a simple workaround is to create a shell script along these lines.. you might to do something a little more sophisticated. # #!/bin/sh echo USE `database1`; outflfile.sql mysqldump -(firstsetofoptions) outfile.sql echo USE `database2`; outflfile.sql mysqldump -(secondsetofoptions) outfile.sql On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent dae...@daevid.com wrote: I'm working on some code where I am trying to merge two customer accounts (we get people signing up under different usernames, emails, or just create a new account sometimes). I want to test it, and so I need a way to restore the data in the particular tables. Taking a dump of all the DBs and tables is not feasible as it's massive, and importing (with indexes) takes HOURS. I just want only the tables that are relevant. I can find all the tables that have `customer_id` in them with this magic incantation: SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME` Then I crafted this, but it pukes on the db name portion. :-( mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --tables member_sessions.users_last_login support.tickets mydb1.clear_passwords mydb1.crak_subscriptions mydb1.customers mydb1.customers_free mydb1.customers_free_tracking mydb1.customers_log mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players mydb1content.actors_comments mydb1content.actor_collections mydb1content.actor_likes_users mydb1content.collections mydb1content.dvd_likes_users mydb1content.free_videos mydb1content.genre_collections mydb1content.playlists mydb1content.poll_votes mydb1content.scenes_comments mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections mydb1content.scene_likes_users mydb1content.videos_downloaded mydb1content.videos_viewed merge_backup.sql -- Connecting to localhost... mysqldump: Got error: 1049: Unknown database 'member_sessions.users_last_login' when selecting the database -- Disconnecting from localhost... I searched a bit and found that it seems I have to split this into multiple statements and append like I'm back in 1980. *sigh* mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database member_sessions --tables users_last_login merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database support --tables tickets merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database mydb1 --tables clear_passwords customers customers_free customers_free_tracking customers_log customers_subscriptions customers_transactions players merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database content --tables actors_comments actor_collections actor_likes_users collections dvd_likes_users free_videos genre_collections playlists poll_votes scenes_comments scenes_ratings_users_new2 scene_collections scene_likes_users videos_downloaded videos_viewed merge_backup.sql The critical flaw here is that the mysqldump program does NOT put the necessary USE DATABASE statement in each of these dumps since there is only one DB after the -database apparently. UGH. Nor do I see a command line option to force it to output this seemingly
How do I mysqldump different database tables to the same .sql file?
I'm working on some code where I am trying to merge two customer accounts (we get people signing up under different usernames, emails, or just create a new account sometimes). I want to test it, and so I need a way to restore the data in the particular tables. Taking a dump of all the DBs and tables is not feasible as it's massive, and importing (with indexes) takes HOURS. I just want only the tables that are relevant. I can find all the tables that have `customer_id` in them with this magic incantation: SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME` Then I crafted this, but it pukes on the db name portion. :-( mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --tables member_sessions.users_last_login support.tickets mydb1.clear_passwords mydb1.crak_subscriptions mydb1.customers mydb1.customers_free mydb1.customers_free_tracking mydb1.customers_log mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players mydb1content.actors_comments mydb1content.actor_collections mydb1content.actor_likes_users mydb1content.collections mydb1content.dvd_likes_users mydb1content.free_videos mydb1content.genre_collections mydb1content.playlists mydb1content.poll_votes mydb1content.scenes_comments mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections mydb1content.scene_likes_users mydb1content.videos_downloaded mydb1content.videos_viewed merge_backup.sql -- Connecting to localhost... mysqldump: Got error: 1049: Unknown database 'member_sessions.users_last_login' when selecting the database -- Disconnecting from localhost... I searched a bit and found that it seems I have to split this into multiple statements and append like I'm back in 1980. *sigh* mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database member_sessions --tables users_last_login merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database support --tables tickets merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database mydb1 --tables clear_passwords customers customers_free customers_free_tracking customers_log customers_subscriptions customers_transactions players merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database content --tables actors_comments actor_collections actor_likes_users collections dvd_likes_users free_videos genre_collections playlists poll_votes scenes_comments scenes_ratings_users_new2 scene_collections scene_likes_users videos_downloaded videos_viewed merge_backup.sql The critical flaw here is that the mysqldump program does NOT put the necessary USE DATABASE statement in each of these dumps since there is only one DB after the -database apparently. UGH. Nor do I see a command line option to force it to output this seemingly obvious statement. It's a pretty significant shortcoming of mysqldump if you ask me that I can't do it the way I had it in the first example since that's pretty much standard SQL convetion of db.table.column format. And even more baffling is why it wouldn't dump out the USE statement always even if there is only one DB. It's a few characters and would save a lot of headaches in case someone tried to dump their .sql file into the wrong DB on accident. Plus it's not easy to edit a 2.6GB file to manually insert these USE lines. Is there a way to do this with some command line option I'm not seeing in the man page?
Re: how to create unique key for long varchar?
In the last episode (Nov 05), Li Li said: I want to create a table with a long varchar column, maybe it's the url. according to dns spec, the url's max length is fixed. but I have to deal with url having long params such as a.html?q=fl= I want the url is unique when inserting it. I googled and found http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql this post suggests use md5 of url. But in theory, there will be conflict that two different urls will have the same md5(even it's probablitiy is very small). I want to a absolute correct solution. one method i can come up with is using select ... for update 1. begin transaction 2. select url from tb where md5='' for update 3. if the url is not exist, insert into this url; else do nothing It might be more efficient to optimize for the common case here. The assumption is that an md5 (or sha1 or sha2) hash collision is extremely unlikely, so you could just insert your new row, and if you get a duplicate entry for primary key error, then you can select url from tb where md5='' , and compare the retreived url with the one you want to insert. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to create unique key for long varchar?
The odds of a spurious collision with MD5 (128 bits) can be phrased this way: If you have 9 Trillion different items, there is one chance in 9 Trillion that two of them have the same MD5. To phrase it another way, it is more likely to be hit by a meteor while winning the mega-lottery. -Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Tuesday, November 05, 2013 7:56 AM To: Li Li Cc: mysql@lists.mysql.com Subject: Re: how to create unique key for long varchar? In the last episode (Nov 05), Li Li said: I want to create a table with a long varchar column, maybe it's the url. according to dns spec, the url's max length is fixed. but I have to deal with url having long params such as a.html?q=fl= I want the url is unique when inserting it. I googled and found http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql this post suggests use md5 of url. But in theory, there will be conflict that two different urls will have the same md5(even it's probablitiy is very small). I want to a absolute correct solution. one method i can come up with is using select ... for update 1. begin transaction 2. select url from tb where md5='' for update 3. if the url is not exist, insert into this url; else do nothing It might be more efficient to optimize for the common case here. The assumption is that an md5 (or sha1 or sha2) hash collision is extremely unlikely, so you could just insert your new row, and if you get a duplicate entry for primary key error, then you can select url from tb where md5='' , and compare the retreived url with the one you want to insert. -- Dan Nelson dnel...@allantgroup.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
Re: how to create unique key for long varchar?
I prefer your solution in that it's something like Optimistic Locking. but the problem is that if I define md5 as unique key and there exists 2 different urls with the same md5. I can't insert the second url anymore On Tue, Nov 5, 2013 at 11:55 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Nov 05), Li Li said: I want to create a table with a long varchar column, maybe it's the url. according to dns spec, the url's max length is fixed. but I have to deal with url having long params such as a.html?q=fl= I want the url is unique when inserting it. I googled and found http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql this post suggests use md5 of url. But in theory, there will be conflict that two different urls will have the same md5(even it's probablitiy is very small). I want to a absolute correct solution. one method i can come up with is using select ... for update 1. begin transaction 2. select url from tb where md5='' for update 3. if the url is not exist, insert into this url; else do nothing It might be more efficient to optimize for the common case here. The assumption is that an md5 (or sha1 or sha2) hash collision is extremely unlikely, so you could just insert your new row, and if you get a duplicate entry for primary key error, then you can select url from tb where md5='' , and compare the retreived url with the one you want to insert. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to create unique key for long varchar?
The odds against the eventuality you are worried about are astronomically high. Much serious work on the internet would fall apart were that not true. Collision is simply not going to happen within the next several hundred thousand years. On Nov 5, 2013 9:59 PM, Li Li fancye...@gmail.com wrote: I prefer your solution in that it's something like Optimistic Locking. but the problem is that if I define md5 as unique key and there exists 2 different urls with the same md5. I can't insert the second url anymore On Tue, Nov 5, 2013 at 11:55 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Nov 05), Li Li said: I want to create a table with a long varchar column, maybe it's the url. according to dns spec, the url's max length is fixed. but I have to deal with url having long params such as a.html?q=fl= I want the url is unique when inserting it. I googled and found http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql this post suggests use md5 of url. But in theory, there will be conflict that two different urls will have the same md5(even it's probablitiy is very small). I want to a absolute correct solution. one method i can come up with is using select ... for update 1. begin transaction 2. select url from tb where md5='' for update 3. if the url is not exist, insert into this url; else do nothing It might be more efficient to optimize for the common case here. The assumption is that an md5 (or sha1 or sha2) hash collision is extremely unlikely, so you could just insert your new row, and if you get a duplicate entry for primary key error, then you can select url from tb where md5='' , and compare the retreived url with the one you want to insert. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to create unique key for long varchar?
I came up with a solution: using special md5 to deal with conflicts
steps to insert a url
1. insert into table(md5,url) values('md5 of url', url)
2. if get a duplicate entry for primary key
2.1 select md5,url from table where md5 like '%' for update
2.2 if url really exists, don't need insert anything
2.3 generate a special md5 for this url starts with ,maybe
1 and insert it
2.4 commit transaction
On Wed, Nov 6, 2013 at 10:56 AM, Li Li fancye...@gmail.com wrote:
I prefer your solution in that it's something like Optimistic Locking.
but the problem is that if I define md5 as unique key and there exists
2 different urls with the same md5. I can't insert the second url
anymore
On Tue, Nov 5, 2013 at 11:55 PM, Dan Nelson dnel...@allantgroup.com wrote:
In the last episode (Nov 05), Li Li said:
I want to create a table with a long varchar column, maybe it's the url.
according to dns spec, the url's max length is fixed. but I have
to deal with url having long params such as
a.html?q=fl=
I want the url is unique when inserting it.
I googled and found
http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql
this post suggests use md5 of url. But in theory, there will be
conflict that two different urls will have the same md5(even it's
probablitiy is very small). I want to a absolute correct solution.
one method i can come up with is using select ... for update
1. begin transaction
2. select url from tb where md5='' for update
3. if the url is not exist, insert into this url; else do nothing
It might be more efficient to optimize for the common case here. The
assumption is that an md5 (or sha1 or sha2) hash collision is extremely
unlikely, so you could just insert your new row, and if you get a duplicate
entry for primary key error, then you can select url from tb where
md5='' , and compare the retreived url with the one you want to insert.
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
how to create unique key for long varchar?
hi all I want to create a table with a long varchar column, maybe it's the url. according to dns spec, the url's max length is fixed. but I have to deal with url having long params such as a.html?q=fl= I want the url is unique when inserting it. I googled and found http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql this post suggests use md5 of url. But in theory, there will be conflict that two different urls will have the same md5(even it's probablitiy is very small). I want to a absolute correct solution. one method i can come up with is using select ... for update 1. begin transaction 2. select url from tb where md5='' for update 3. if the url is not exist, insert into this url; else do nothing also I need to set the transaction isolation to SERIALIZABLE is this solution correct in multithread/process environment? another method is using trigger to check whether the url exist. I don't know whether this method will be faster than previous one. how to ensure it's correctness in multithread environment? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to update MySQL table based on 3 other tables
Hello Neil, On 8/24/2013 5:21 AM, Neil Tompkins wrote: I have the following four MySQL tables Region RegionId City CityId RegionId Hotel HotelId CityId HotelRegion HotelId RegionId I'm struggling to write a UPDATE statement to update the City table's RegionId field from data in the HotelRegion table. Basically how can I update the City table with the correct RegionId where the HotelId in the HotelRegion table matches the City table's CityId. This is my UPDATE statement at the moment UPDATE City cSET c.RegionId = (SELECT DISTINCT(HotelRegion.RegionId) FROM HotelRegion INNER JOIN Hotel ON Hotel.HotelID = HotelRegion.HotelIDINNER JOIN City ON City.CityId = Hotel.CityIdWHERE City.CityId = 1233)WHERE c.CityId = 1233 Have you tried the multi-table syntax of the UPDATE command? http://dev.mysql.com/doc/refman/5.6/en/update.html UPDATE City c INNER JOIN HotelRegion h ON h.HotelID = c.CityID SET City.RegionID = h.RegionID WHERE ... -- 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
How to update MySQL table based on 3 other tables
I have the following four MySQL tables Region RegionId City CityId RegionId Hotel HotelId CityId HotelRegion HotelId RegionId I'm struggling to write a UPDATE statement to update the City table's RegionId field from data in the HotelRegion table. Basically how can I update the City table with the correct RegionId where the HotelId in the HotelRegion table matches the City table's CityId. This is my UPDATE statement at the moment UPDATE City cSET c.RegionId = (SELECT DISTINCT(HotelRegion.RegionId) FROM HotelRegion INNER JOIN Hotel ON Hotel.HotelID = HotelRegion.HotelIDINNER JOIN City ON City.CityId = Hotel.CityIdWHERE City.CityId = 1233)WHERE c.CityId = 1233
Re: how to get the levels of a table or a index in Mysql 5.6?
On 08.07.2013 04:23, 李炜(平安科技数据库技术支持部) wrote: how to get the levels of a table or a index in Mysql 5.6? Level? What is level supposed to be in that context? Cardinality? Or something completely different? /me confused ... -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to get the levels of a table or a index in Mysql 5.6?
Or maybe the number of levels in the BTree? Rule of Thumb: logarithm base 100 -Original Message- From: Hartmut Holzgraefe [mailto:hart...@skysql.com] Sent: Monday, July 08, 2013 6:38 AM To: mysql@lists.mysql.com Subject: Re: how to get the levels of a table or a index in Mysql 5.6? On 08.07.2013 04:23, 李炜(平安科技数据库技术支持部) wrote: how to get the levels of a table or a index in Mysql 5.6? Level? What is level supposed to be in that context? Cardinality? Or something completely different? /me confused ... -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
答复: how to get the levels of a table or a index in Mysql 5.6?
The level is the height of a B-tree table or a B-tree index in mysql 5.6. My question is how to get the height(blevel) of a B-tree table or a B-tree index in mysql 5.6? Thanks. In oracle database,we can use the following statement to query the blevel of a index select index_name,blevel from dba_indexes; -Original Message- RE: how to get the levels of a table or a index in Mysql 5.6? Or maybe the number of levels in the BTree? Rule of Thumb: logarithm base 100 -Original Message- From: Hartmut Holzgraefe [mailto:hart...@skysql.com] Sent: Monday, July 08, 2013 6:38 AM To: mysql@lists.mysql.com Subject: Re: how to get the levels of a table or a index in Mysql 5.6? On 08.07.2013 04:23, 李炜(平安科技数据库技术支持部) wrote: how to get the levels of a table or a index in Mysql 5.6? Level? What is level supposed to be in that context? Cardinality? Or something completely different? /me confused ... -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business. Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks. 收发邮件者请注意： 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
how to get the levels of a table or a index in Mysql 5.6?
Hi how to get the levels of a table or a index in Mysql 5.6? thanks The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business. Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks. 收发邮件者请注意： 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
If a crash occurs in the middle of an ALTER, the files may not get cleaned up. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, June 20, 2013 12:57 PM To: mysql@lists.mysql.com Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash? i know what happened but how get rid of these two bullshit files after *three years* not touched and used by mysqld Am 20.06.2013 21:28, schrieb Rick James: #sql files are temp tables that vanish when the ALTER (or whatever) finishes. If you find one sitting around, it sounds like a crash happened in the middle of the ALTER. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, June 19, 2013 12:19 PM To: mysql@lists.mysql.com Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash? Am 19.06.2013 21:00, schrieb Franck Dernoncourt: `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql- ib203`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table I use innodb_file_per_table. There was a mysql_datadir/logs/#sql- ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? interesting question, i have identical crap since 2009 also after a crash und these blind table has the same structure as a used existing one if i delete the #-files mysql whines every startup while they are never used and it is ridiculous that there are references in the table space to this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm -rw-rw 1 mysql mysql 64K 2011-07-24 11:49 #sql2-704-271.ibd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
that is what we all know but how to get rid of them? but *why* they are not cleaned up? * the global tablespace knows about them * nothing is using them really * so why can mysqld not cleanup this mess? if you delete them all works fine but each start the error-log is cluttered Am 25.06.2013 17:46, schrieb Rick James: If a crash occurs in the middle of an ALTER, the files may not get cleaned up. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, June 20, 2013 12:57 PM To: mysql@lists.mysql.com Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash? i know what happened but how get rid of these two bullshit files after *three years* not touched and used by mysqld Am 20.06.2013 21:28, schrieb Rick James: #sql files are temp tables that vanish when the ALTER (or whatever) finishes. If you find one sitting around, it sounds like a crash happened in the middle of the ALTER. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, June 19, 2013 12:19 PM To: mysql@lists.mysql.com Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash? Am 19.06.2013 21:00, schrieb Franck Dernoncourt: `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql- ib203`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table I use innodb_file_per_table. There was a mysql_datadir/logs/#sql- ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? interesting question, i have identical crap since 2009 also after a crash und these blind table has the same structure as a used existing one if i delete the #-files mysql whines every startup while they are never used and it is ridiculous that there are references in the table space to this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm -rw-rw 1 mysql mysql 64K 2011-07-24 11:49 #sql2-704-271.ibd -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Hi Frank, On 20/06/2013 05:00, Franck Dernoncourt wrote: Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql-ib203`: It's a bit of a workaround, but you should be able to get rid of the file using the steps below. I'm using an example where I killed mysqld while it was dropping the to_date column from the salaries table in the employees sample database: mysql SHOW CREATE TABLE salaries\G *** 1. row *** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql ALTER TABLE salaries DROP COLUMN to_date; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql$ ls -1 employees/#* employees/#sql-36ab_2.frm employees/#sql-ib30.ibd 1. Create a temporary table with the same structure as the salaries table would have looked after the ALTER that failed: mysql CREATE TABLE tmp LIKE salaries; ALTER TABLE tmp DROP COLUMN to_date; 2. Shutdown MySQL. 3. Copy the .frm file from the tmp table to have the same name as the #sql-*.ibd file: mysql$ cp employees/tmp.frm employees/#sql-ib30.frm 4. Start MySQL again. 5. Drop the #sql-ib30.frm table: mysql DROP TABLE `#mysql50##sql-ib30`; Query OK, 0 rows affected (0.01 sec) 6. Do the same for the #sql*.frm file (it'll get removed even though you get an error): mysql DROP TABLE `#mysql50##sql-36ab_2`; ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2' I know it's not very elegant, but should work. The #mysql50# prefix tells MySQL to not encode the table name when mapping to the file system (https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html). Best regards, Jesper Krogh MySQL Support
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Franck, Am 19.06.2013 21:00, schrieb Franck Dernoncourt: A table `logs/#sql-ib203` appeared after a MySQL crash the #sql-ibtableID tables are temporarily created during an ALTER TABLE operation for recovery purposes. Apparently these temporary tables might stay in certain circumstances even after recovery is completed. If you already tried enclosing the table name in backticks (DROP TABLE `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax without success, copying the table along with all its data and dropping the original table afterwards or running `mysqldump database tablename dump.sql mysql dump.sql` for a backup/restore operation at least will help the problem of being unable to run ALTER TABLE commands for the affected main table. Regards, Denis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
This may be a naive question, but I'm not sure I can see you've covered this: Have you tried USE logs before DROP TABLE `#sql-ib203` (without the logs/ bit)? / Carsten On 19-06-2013 21:00, Franck Dernoncourt wrote: Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql-ib203`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table 'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of these error messages display 'logs/#sql-ib203', which is the table name MySQL complains it exists when I try to do ALTER. I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? I use MySQL 5.6.12-winx64 and InnoDB. Thanks, Franck Dernoncourt fran...@mit.edu http://francky.me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
On Thu, Jun 20, 2013 at 7:13 AM, Denis Jedig d...@syneticon.net wrote: If you already tried enclosing the table name in backticks (DROP TABLE `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax without success, Yep, no success with: DROP TEMPORARY TABLE `logs/#sql-ib203`; DROP TEMPORARY TABLE `/#sql-ib203`; DROP TEMPORARY TABLE `#sql-ib203`; DROP TEMPORARY TABLE `sql-ib203`; USE logs; DROP TEMPORARY TABLE `logs/#sql-ib203`; USE logs; DROP TEMPORARY TABLE `/#sql-ib203`; USE logs; DROP TEMPORARY TABLE `#sql-ib203`; USE logs; DROP TEMPORARY TABLE `sql-ib203`; copying the table along with all its data and dropping the original table afterwards or running `mysqldump database tablename dump.sql mysql dump.sql` for a backup/restore operation at least will help the problem of being unable to run ALTER TABLE commands for the affected main table. I copied the data to a new table with a different name, but I wish there were a more subtle way to solve the issue :) I haven't dropped the original table yet, so I cannot confirm this will solve the issue but hopefully it will. On Thu, Jun 20, 2013 at 1:32 PM, Carsten Pedersen cars...@bitbybit.dkwrote: This may be a naive question, but I'm not sure I can see you've covered this: Have you tried USE logs before DROP TABLE `#sql-ib203` (without the logs/ bit)? Thanks, I tried the following, none of them worked: DROP TABLE `logs/#sql-ib203`; DROP TABLE `/#sql-ib203`; DROP TABLE `#sql-ib203`; DROP TABLE `sql-ib203`; USE logs; DROP TABLE `logs/#sql-ib203`; USE logs; DROP TABLE `/#sql-ib203`; USE logs; DROP TABLE `#sql-ib203`; USE logs; DROP TABLE `sql-ib203`; Also, I can create tables with that name: USE logs; CREATE TABLE `logs/#sql-ib203` (id int); USE logs; CREATE TABLE `/#sql-ib203` (id int); USE logs; CREATE TABLE `#sql-ib203` (id int); USE logs; CREATE TABLE `sql-ib203` (id int); It does not conflict with any existing tables. Here is an example where I CREATE and DROP `logs/#sql-ib203`: step 1 14:47:48 USE logs 0 row(s) affected 0.000 sec step 2 14:47:48 CREATE TABLE `logs/#sql-ib203` (id int) 0 row(s) affected 0.047 sec step 3 14:47:53 CREATE TABLE `logs/#sql-ib203` (id int) Error Code: 1050. Table 'logs/#sql-ib203' already exists 0.000 sec step 4 14:48:01 DROP TABLE `logs/#sql-ib203` 0 row(s) affected 0.047 sec step 5 14:48:05 DROP TABLE `logs/#sql-ib203` Error Code: 1051. Unknown table 'logs.logs/#sql-ib203' 0.000 sec step 6 14:48:30 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` Error Code: 1050. Table 'logs/#sql-ib203' already exists 0.016 sec DROP TABLE `logs/#sql-ib203` at step 4 works, which would allow me to do step 2 CREATE TABLE `logs/#sql-ib203` (id int) again, but step 6 ALTER TABLE still complains about the existence of 'logs/#sql-ib203'. One last remark: the main file `ibdata1` contains references to `logs/#sql-ib203`, which is not surprising given the error message I have when trying to ALTER the original table. Is there any way to clean the file `ibdata1` so that it only contains references to tables having an actual data file? I use InnoDB with innodb_file_per_table Thanks for your help, Franck Dernoncourt fran...@mit.edu http://francky.me
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
On Thu, Jun 20, 2013 at 3:28 PM, Rick James rja...@yahoo-inc.com wrote: #sql files are temp tables that vanish when the ALTER (or whatever) finishes. If you find one sitting around, it sounds like a crash happened in the middle of the ALTER. Yes the crash happened during an ALTER: the table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while executing the following query: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` , ADD INDEX `username_event_type_idx` (`username` ASC, `event_type` ASC) ; Shouldn't the recovery take care of cleaning the temporary tables created during the query running at the time of the crash? Or at least, if not, shouldn't the DROP be working on this temporary table?
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
i know what happened but how get rid of these two bullshit files after *three years* not touched and used by mysqld Am 20.06.2013 21:28, schrieb Rick James: #sql files are temp tables that vanish when the ALTER (or whatever) finishes. If you find one sitting around, it sounds like a crash happened in the middle of the ALTER. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, June 19, 2013 12:19 PM To: mysql@lists.mysql.com Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash? Am 19.06.2013 21:00, schrieb Franck Dernoncourt: `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql- ib203`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table I use innodb_file_per_table. There was a mysql_datadir/logs/#sql- ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? interesting question, i have identical crap since 2009 also after a crash und these blind table has the same structure as a used existing one if i delete the #-files mysql whines every startup while they are never used and it is ridiculous that there are references in the table space to this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm -rw-rw 1 mysql mysql 64K 2011-07-24 11:49 #sql2-704-271.ibd signature.asc Description: OpenPGP digital signature
RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
#sql files are temp tables that vanish when the ALTER (or whatever) finishes. If you find one sitting around, it sounds like a crash happened in the middle of the ALTER. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, June 19, 2013 12:19 PM To: mysql@lists.mysql.com Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash? Am 19.06.2013 21:00, schrieb Franck Dernoncourt: `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql- ib203`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table I use innodb_file_per_table. There was a mysql_datadir/logs/#sql- ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? interesting question, i have identical crap since 2009 also after a crash und these blind table has the same structure as a used existing one if i delete the #-files mysql whines every startup while they are never used and it is ridiculous that there are references in the table space to this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm -rw-rw 1 mysql mysql 64K 2011-07-24 11:49 #sql2-704-271.ibd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql-ib203`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table 'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of these error messages display 'logs/#sql-ib203', which is the table name MySQL complains it exists when I try to do ALTER. I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? I use MySQL 5.6.12-winx64 and InnoDB. Thanks, Franck Dernoncourt fran...@mit.edu http://francky.me
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Am 19.06.2013 21:00, schrieb Franck Dernoncourt: `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql-ib203`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? interesting question, i have identical crap since 2009 also after a crash und these blind table has the same structure as a used existing one if i delete the #-files mysql whines every startup while they are never used and it is ridiculous that there are references in the table space to this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm -rw-rw 1 mysql mysql 64K 2011-07-24 11:49 #sql2-704-271.ibd signature.asc Description: OpenPGP digital signature
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
2013/06/13 23:08 +, Rick James
FIND_IN_SET might work the cleanest...
WHERE FIND_IN_SET('action', genres) OR/AND [NOT] ...
And have genres look like 'action,drama,foobar', that is comma-separators, and
no need for leading/trailing comma.
That would also work for genres = '1,3,10,19,38' and FIND_IN_SET('19', genres)
And you seem no fan of named BITs (SET), either. *sigh*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
2013/06/11 12:59 -0700, Daevid Vincent Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres 17 1|3|10|19|38|53|58|59| SELECT * FROM scene_all_genres WHERE scene_id = 11; scene_id genres --- 11 1|10|19|31|32|59| Except that, it seems to me, it somehow reflects the reality of assigning attributes to the scenes (movies?) that you catalog. In a way, it looks very much like using a bitstring wherin each place stands for one attribute. If you then have also a bitstring for each user s likes and one for rows (peeves), telling howmany 1s are at the same place for the genres and liking (bit-AND, MySQL followed by telling the number of 1s), and same for the genres and the row or peeve yields a number howmany match for liking, and how many match for becoming peeved. If the liking is enough greater than the becoming peeved, the scene and the user match. Unhappily, although this, using bitstring for set of attributes to match, is an old and well understood topic, MySQL s support for bitstrings is poor, limited to integers (as C is so limited)--that is, to 64 bits. If you have more, you have to use more words. There is, furthermore, no function for telling howmany 1s (or 0s) there are in an integer. Now, if, in a more perfect world, MySQL had bitstring, and, furthermore, MySQL s SET were mapped onto bitstring, where it belongs, you could not only use bit operations (MySQL s | ^), but also name the bits as you like. The problem with writing one s own bit-telling function is, of course, time, and hiding useful information from the optimizer. In any case, here is a function for it, using an old well worn trick that depends on binary arithmetic: delimiter ? create function bittell(B INTEGER) RETURNS INTEGER DETERMINISTIC NO SQL COMMENT 'Howmany 1s in argument?' begin declare E integer; SET E = 0; WHILE B 0 DO set B = (B-1) B, E = E + 1; end WHILE; RETURN E; end ? delimiter ; If you stick with the character-string set, with a slight change in representation you can use a simpler-looking pattern--not more efficient, if MySQL s implementation is good, but of easier reading: separate the decimal numerals with a character that is neither a decimal digit nor a REGEXP operator, and bound the whole string with it--comma or semicolon (among others) are good. ',1,10,19,31,32,59,' REGEXP ',10,+.*,38,' is 0 ',1,10,19,31,32,59,' REGEXP ',10,+.*,32,' is 1 (See also FIND_IN_SET.) Somewhere I read that for lack of support bitstring has been withdrawn from the SQL standard. This is such an obvious use; why is it not supported? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
2013/06/11 12:59 -0700, Daevid Vincent Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres 17 1|3|10|19|38|53|58|59| SELECT * FROM scene_all_genres WHERE scene_id = 11; scene_id genres --- 11 1|10|19|31|32|59| Except that, it seems to me, it somehow reflects the reality of assigning attributes to the scenes (movies?) that you catalog. In a way, it looks very much like using a bitstring wherin each place stands for one attribute. If, say, the bitstring for that which the user gladly picks something is called glad, and that for which the user is loath to pick something is called loath, an expression for fulfilling all attributes is (glad genre) = glad AND (loath genre) = 0, with no bit-telling. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Thinking out of the box... (And posting my reply at the 'wrong' end of the
email.)...
Are there fewer than 64 genres? Use a SET or BIGINT UNSIGNED.
AND sg.`genre_id` IN (10,38)
AND sg.`genre_id` NOT IN (22,61)
--
AND genre ((110) | (138)) != 0
AND genre ((122) | (161)) = 0
This would avoid having that extra table, and save a lot of space.
If you have more than 64 genres, then
Plan A: clump them into some kind of application grouping and use multiple
INTs/SETs.
Plan B: do mod div arithmetic to compute which genre field to tackle.
For B, something like:
AND (genre1 (10)) + (genre3 (18)) != 0
AND (genre2 (12)) + (genre6 (11)) = 0
(That's assuming 10 bits per genre# field. I would use 32 and INT UNSIGNED.)
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Tuesday, June 11, 2013 4:17 PM
To: mysql@lists.mysql.com
Cc: 'shawn green'
Subject: RE: How do I select all rows of table that have some rows in
another table (AND, not OR)
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows in
another table (AND, not OR)
Hello Daevid,
On 6/11/2013 3:59 PM, Daevid Vincent wrote:
I am trying to implement a filter so that a user could select
various
genres
they want in or out. Perhaps they like 'action' and 'car chases'
but don't like 'foreign' and 'drifting' (or whatever...)
So I want something sort of like this, however IN() is using an OR
comparison when I need it to be an AND
SELECT DISTINCT
s.`scene_id` AS `id`,
GROUP_CONCAT(sg.`genre_id`) FROM
`dvds` AS d
JOIN `scenes_list` AS s
ON s.`dvd_id` = d.`dvd_id`
JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
AND sg.`genre_id` IN (10,38)
AND sg.`genre_id` NOT IN (22,61) GROUP BY
s.`scene_id`;
This is giving me way way too many rows returned.
For example, I would expect this scene_id to be in the result set:
SELECT * FROM scenes_genres WHERE scene_id = 17;
scene_id genre_id
--
17 1
17 3
17 10 --
17 19
17 38 --
17 53
17 58
17 59
And this scene ID to NOT be in the result set:
SELECT * FROM scenes_genres WHERE scene_id = 11;
scene_id genre_id
--
11 1
11 10 --
11 19
11 31
11 32
-- but does not have 38
11 59
I've tried various subselect ideas, but of course this fails b/c
genre_id
can't be multiple things at one time (AND)
JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
AND sg.`genre_id` IN (
SELECT `genre_id` FROM `scenes_genres`
WHERE `genre_id` = 10
AND `genre_id` = 38
AND `genre_id` 22
AND `genre_id` 61
)
And straight up like this failure too...
JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)
So I'm sort of out of ideas at this point and hoping someone has a
way
to
do
this.
Also, just for SG this is how we are currently implementing it, but
we
feel
the REGEXP is killing our queries and while clever is a bit hacky
and nullifies any indexes we have on the genres column as it
requires a file_sort table scan to compare substrings basically...
SELECT * FROM scene_all_genres WHERE scene_id = 17;
scene_id genres
17 1|3|10|19|38|53|58|59|
SELECT * FROM scene_all_genres WHERE scene_id = 11;
scene_id genres
---
11 1|10|19|31|32|59|
SELECT DISTINCT
s.`scene_id` AS `id`,
sg.`genres`
FROM
`scene_all_genres` AS sg,
`dvds` AS d,
`scenes_list` AS s
WHERE dvd_id` = d.`dvd_id`
AND sg.`scene_id` = s.`scene_id`
AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]'
AND sg.`genres` NOT REGEXP
'(([[::]]22[[::]])|([[::]]61[[::]]))'
;
http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp
[[::]], [[::]]
These markers stand for word boundaries. They match the beginning
and
end
of
words, respectively. A word is a sequence of word characters
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Hello Daevid, On 6/11/2013 7:17 PM, Daevid Vincent wrote: -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, June 11, 2013 2:16 PM To: mysql@lists.mysql.com Subject: Re: How do I select all rows of table that have some rows in another table (AND, not OR) Hello Daevid, ... snip ... Shawn, thank you for taking the time to reply. I wasn't expecting the solution to be so much work with multiple statements like that. I was thinking it could be done in one (or two, as in split out a portion of it in PHP and re-insert it to the original SQL to avoid a JOIN or something). Part of the issue is that we use PHP to generate the $sql string by appending bits and pieces depending on the search criteria thereby keeping the 'path' through the SQL statement simple and relatively linear. To implement this would require significant re-writing and/or special cases where we could introduce errors or omissions in the future. The frustrating part is that the REGEXP query we use now only takes about 2 seconds on my DEV VM (same database as PROD), however when the RDBMS is loaded it then takes up to 30 seconds so in theory it's not even that inefficient given the # rows. We do use memcached for the results, but since there are so many combinations a user could choose, our hit ratio is not so great and therefore the cache isn't doing us much good and this is why the RDBMS can get loaded up easily. How can an OR be so simple using IN() but AND be so overly complex? Seems that mysql should have another function for ALL() that works just like IN() to handle this kind of scenario. As I said, we could have used a single command but you would have not been able to review the 'best' match scenario only those rows that were 'complete' matches. And, those three commands can easily be encapsulated within a stored procedure. Pass in two strings (one listing the values to find, one listing the values to reject) and use PREPARED STATEMENTS within the procedure to build the IN() lists within the first and second commands. The value in the 'HAVING' clause in the last command (unless you use the other option of reviewing the list of 'closest' matches) can be set to the number of items in the list of things to find parameter to your procedure. I was demonstrating a principle you could use and not necessarily giving you a full solution. Some tweaking may be required. Also, by encapsulating what I wrote within a stored procedure, this changes my 3 statements to a single call that you can easily configure from your PHP application. 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
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
This idea is so fancy pants and clever I *wish* it could have worked for me. I checked and we actually have 65 genres currently (with more to come I'm sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some bitmasks and this solution is so elegant. It's unfortunate there isn't a way to use more than 64-bits natively. You're RICK JAMES Bitch! :-p (please tell me you know the Dave Chappelles skit I'm referring to) -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Wednesday, June 12, 2013 9:39 AM To: Daevid Vincent; mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) Thinking out of the box... (And posting my reply at the 'wrong' end of the email.)... Are there fewer than 64 genres? Use a SET or BIGINT UNSIGNED. AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) -- AND genre ((110) | (138)) != 0 AND genre ((122) | (161)) = 0 This would avoid having that extra table, and save a lot of space. If you have more than 64 genres, then Plan A: clump them into some kind of application grouping and use multiple INTs/SETs. Plan B: do mod div arithmetic to compute which genre field to tackle. For B, something like: AND (genre1 (10)) + (genre3 (18)) != 0 AND (genre2 (12)) + (genre6 (11)) = 0 (That's assuming 10 bits per genre# field. I would use 32 and INT UNSIGNED.) -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Tuesday, June 11, 2013 4:17 PM To: mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, June 11, 2013 2:16 PM To: mysql@lists.mysql.com Subject: Re: How do I select all rows of table that have some rows in another table (AND, not OR) Hello Daevid, On 6/11/2013 3:59 PM, Daevid Vincent wrote: I am trying to implement a filter so that a user could select various genres they want in or out. Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...) So I want something sort of like this, however IN() is using an OR comparison when I need it to be an AND SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`; This is giving me way way too many rows returned. For example, I would expect this scene_id to be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 17; scene_id genre_id -- 17 1 17 3 17 10 -- 17 19 17 38 -- 17 53 17 58 17 59 And this scene ID to NOT be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 11; scene_id genre_id -- 11 1 11 10 -- 11 19 11 31 11 32 -- but does not have 38 11 59 I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND) JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10 AND `genre_id` = 38 AND `genre_id` 22 AND `genre_id` 61 ) And straight up like this failure too... JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61) So I'm sort of out of ideas at this point and hoping someone has a way to do this. Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Oh! I must have misread. I didn't see how you had a solution for 64 bits. I may have to experiment with that! -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Wednesday, June 12, 2013 11:26 AM To: mysql@lists.mysql.com Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) This idea is so fancy pants and clever I *wish* it could have worked for me. I checked and we actually have 65 genres currently (with more to come I'm sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some bitmasks and this solution is so elegant. It's unfortunate there isn't a way to use more than 64-bits natively. You're RICK JAMES Bitch! :-p (please tell me you know the Dave Chappelles skit I'm referring to) -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Wednesday, June 12, 2013 9:39 AM To: Daevid Vincent; mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) Thinking out of the box... (And posting my reply at the 'wrong' end of the email.)... Are there fewer than 64 genres? Use a SET or BIGINT UNSIGNED. AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) -- AND genre ((110) | (138)) != 0 AND genre ((122) | (161)) = 0 This would avoid having that extra table, and save a lot of space. If you have more than 64 genres, then Plan A: clump them into some kind of application grouping and use multiple INTs/SETs. Plan B: do mod div arithmetic to compute which genre field to tackle. For B, something like: AND (genre1 (10)) + (genre3 (18)) != 0 AND (genre2 (12)) + (genre6 (11)) = 0 (That's assuming 10 bits per genre# field. I would use 32 and INT UNSIGNED.) -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Tuesday, June 11, 2013 4:17 PM To: mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, June 11, 2013 2:16 PM To: mysql@lists.mysql.com Subject: Re: How do I select all rows of table that have some rows in another table (AND, not OR) Hello Daevid, On 6/11/2013 3:59 PM, Daevid Vincent wrote: I am trying to implement a filter so that a user could select various genres they want in or out. Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...) So I want something sort of like this, however IN() is using an OR comparison when I need it to be an AND SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`; This is giving me way way too many rows returned. For example, I would expect this scene_id to be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 17; scene_id genre_id -- 17 1 17 3 17 10 -- 17 19 17 38 -- 17 53 17 58 17 59 And this scene ID to NOT be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 11; scene_id genre_id -- 11 1 11 10 -- 11 19 11 31 11 32 -- but does not have 38 11 59 I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND) JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10 AND `genre_id` = 38 AND `genre_id` 22 AND `genre_id` 61 ) And straight up like this failure too... JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61) So I'm sort of out
