Hot answers tagged

20 votes

Can't remote access MySQL server running on Mac OS X

I have been banging my head against this error today on OSX Yosemite with MySQL 5.7 recently updated with Homebrew. Following suggestions on StackOverflow and elsewhere, I hunted around after my.cnf ...
Emma Burrows's user avatar
13 votes

Is it safe to delete mysql-bin files?

Try this: RESET MASTER; as the document said: RESET MASTER enables you to delete any binary log files and their related binary log index file, returning the master to its state before binary ...
Xiaofeng's user avatar
  • 241
12 votes

Is it safe to delete mysql-bin files?

Yes, it is safe. For mysql 8, here's how you can do it. We can set binlog_expire_logs_seconds as a option or as a system variable or as a command line parameter too, but I will show how to use it as ...
Eduardo Santana's user avatar
9 votes

Can't remote access MySQL server running on Mac OS X

Running locate my.cnf found the file /usr/local/etc/my.cnf Edit that file and set bind-address = Restart mysql: brew services restart mysql
Pablo Díaz Ogni's user avatar
5 votes

Are many NULL columns harmful in mysql InnoDB?

According to Mysql Official Doc : Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each ...
Navrattan Yadav's user avatar
4 votes

mysql duplicate entry error 1062 when restoring backup

One can even search and replace each occurrence of Insert into with Insert Ignore into in the mysqldump file.
Sachin Singh's user avatar
4 votes

Will a mysql db import be interrupted if my ssh session times out?

If the SSH drops without putting the mysql session into the background (along with nohup), then the DB connection is at the SSH session's mercy. So, yes, mysql would care about the lifecycle of the ...
RolandoMySQLDBA's user avatar
4 votes

MySQL secure random string generator

There are many encryption methods available in mySQL. If you need two way encryption you could use aes_encrypt which has the accompanying aes_decrypt If if you only need a secure hash then you could ...
Tim Penner's user avatar
3 votes

ALTER TABLE - Rename a column

Here's what worked for me: ALTER TABLE vm_list CHANGE `vm_notes]` vm_notes VARCHAR(255); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Yes, I somehow got a column ...
Boris Epstein's user avatar
2 votes

Will a mysql db import be interrupted if my ssh session times out?

Little bit late for the party, But myself struggled with this issue and found a solution and wanted to share. Solution 1 Using screen is ideal for this situation as your processes will run even ...
Sachi.Dila's user avatar
1 vote

MySQL with subquery becomes slow

Use a subquery to get all order_id's of orders with at least an eligible product, then join it with the rest. SELECT * FROM products p1 JOIN orders o1 ON o1.barcode = p1.barcode JOIN users ON users....
Andrea B.'s user avatar
  • 1,483
1 vote

Structure of my database design?

I quickly scanned your SQL file that generates your schema. I think you're off to a good start, a lot of it makes sense. These are the things that stood out to me that you might want to think about a ...
J.D.'s user avatar
  • 37.1k
1 vote

Table Creation Order

I use to follow this path: create all table, only structure (fields), without any Primary Keys nor Foreign Key, without any Index, the order doesn't matter 1.1 inject predefined (aka static) datas ...
tdaget's user avatar
  • 813
1 vote

MySQL Master 5.5 - Slave 5.6 - unknown system variable 'binlog_checksum', Error_code: 1193

If your run SHOW SLAVE STATUS\G on the Slave, and you see Slave_IO_Running: Yes, then the IO Thread is fine. Notwithstanding, please note the Warnings 2016-11-18 22:48:03 12808 [Warning] Slave SQL: ...
RolandoMySQLDBA's user avatar
1 vote

How to rewrite subquery to return only 1 row

Or a little bit simpler is to use "WHERE EXISTS". SELECT DISTINCT store_type FROM stores WHERE EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type); For details ...
tom's user avatar
  • 23
1 vote

Sleeping connections

In the c# code you posted the connection is only closed if it is 'Open', if the state was 'Connecting', 'Broken', 'Executing' or 'Fetching' it would be left open. It is standard practice in c# wrap ...
jonnarosey's user avatar
1 vote

How do you encrypt data while performing a mysqldump?

Using a password inside the script is a really bad idea as this can be seen in ps aux and read out by every system user. I would suggest you to look into mysqldump-secure. This is a shell script that ...
lockdoc's user avatar
  • 111
1 vote

In MySQL, does the order of the columns in a WHERE clause affect query performance?

MySQL where optimization doc says : You might be tempted to rewrite your queries to make arithmetic operations faster, while sacrificing readability. Because MySQL does similar optimizations ...
AbbasAli Hashemian's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible