72,897 reputation
1172166
bio website
location Jersey City, NJ (USA)
age 49
visits member for 3 years, 11 months
seen 3 hours ago

14h
comment MySQL always writes simple temporary/memory tables to disk (both tmp_table_size and max_heap_table_size are set to 8GB)
Doing WHERE 0 or WHERE 1=0 could possibly force an O(n) (order-n) full table scan of website.gallery_likes (just personal fear of that happening), whereas doing LIKE is a O(1) (order 1) structure copy (including indexes). Regardless of website.gallery_likes having 1 row or 1 billion rows, LIKE will take the same amount of time to create.
1d
comment Purge mysql log bins older than two weeks automatically
+1 for finding your own answer.
2d
comment what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata
This question asked for disadvantages only and that's what I named, just disadvantages. That other answer from Dave Rix (dba.stackexchange.com/a/7929/877) named advantages that can outweigh disadvantages. Even with my own answer to that same question (dba.stackexchange.com/a/7930/877) I described a mutlitentant situation I had to manage at the customer's request (I had no say with scale-out or scale-up). I have discussed it before (dba.stackexchange.com/a/1690/877) Having face nothing but disadvantges over the years, Dave Rix's answer was refreshing, cool and deserved +1.
2d
comment Cannot add a column to an InnoDB table in Mariadb
Good work, +1 !!!
2d
comment MySQL Data Files Missing?
InnoDB allows multiple ibdata1 files (See my post dba.stackexchange.com/questions/43503/…)
2d
comment MySQL Data Files Missing?
As shown, you have 1.608 GB of MyISAM, 210MB of InnoDB. Therefore, your data is not missing.
2d
comment MySQL Data Files Missing?
I am sorry wrong query. Please run this: SELECT * FROM information_schema.global_variables WHERE variable_name IN ('datadir','innodb_file_per_table','innodb_data_file_path','innodb_data_home_dir‌​‌​');
Jan
27
comment MySQL Data Files Missing?
Please run this SELECT variable_name FROM information_schema.global_variables WHERE variable_name IN ('datadir','innodb_file_per_table','innodb_data_file_path','innodb_data_home_dir‌​'); What are the values ?
Jan
27
comment MySQL Data Files Missing?
Please go to my post dba.stackexchange.com/questions/8661/…, look for the bold heading Total MySQL Data and Index Usage By Storage Engine and run that query please. Does it report any data ?
Jan
27
comment MySQL Data Files Missing?
Were all the tables using InnoDB ?
Jan
27
comment what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata
@ravnur It's harder to scale back. It's also harder to redesign should related groups have to be redistributed or merged.
Jan
25
comment What is the mysql.db table used for?
@Pacerier Look at my answer again (dba.stackexchange.com/questions/13361/…), please. Here is what I said in my answer : Based on this, the following databases can be accessed fully by anonymous users: test test_db test_001 test_1 test_data While the following databases cannot be accessed fully by anonymous users: testdb test1 testdata.
Jan
24
comment How to batch multiple insert statements for database dump with MySQL?
You still get a +1 because as long as the mysqldump is reloaded into a similar environment or back to the same DB Server it came from, changing the net_buffer_length would not be as risky.
Jan
24
comment How to batch multiple insert statements for database dump with MySQL?
Please note in my answer that I expanded the max_allowed_packet to 1G. The MySQL Packet will expand from the initial setting of net_buffer_length bytes in size to max_allowed_packet, making the mysqldump reload more tolerable.
Jan
24
comment How to batch multiple insert statements for database dump with MySQL?
net_buffer_length (dev.mysql.com/doc/refman/5.6/en/…) is only for TCP/IP communication. It does not explicitly control the number of rows for doing extended inserts. It may increase the number of rows but you cannot make it a fixed number of rows. Setting it for a mysqldump can possibly make the mysqldump more difficult (or at least sluggish) to reload in another environment as I stated in my answer.
Jan
24
comment How to auto assign ordinal ranking in mysql with as minimal queries for new rows?
Your are welcome, and thank you for the bounty !!!
Jan
23
comment How to auto assign ordinal ranking in mysql with as minimal queries for new rows?
Very sweet solution, especially the delete query. +1 !!!
Jan
23
comment What is the mysql.db table used for?
@Pacerier To thoroughly answer your question, see my question dba.stackexchange.com/q/13361/877 and my answer dba.stackexchange.com/a/13363/877
Jan
22
comment Query running slow on script and producing error 500 when run in phpmyadmin
If my answer helped, please click on the checkmark in the upper left corner of my answer to mark it accepted.
Jan
21
comment How to get something written to MySQL error log without restart
Linux or Windows ? (Please say Linux)