MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
129 views

MySQL 5.7 - Where is the mysql_secret file on Windows 7?

I've just installed a copy of MySQL 5.7 for testing, and I love the fact that it now only creates a single root account, and puts the password into a file called .mysql_secret. There's just one ...
0
votes
2answers
140 views

How to change ibdata1 path in mysql?

My ibdata1 file is keep on increasing in size and for a temporary solution, we want to move the file to a separate location with more hard disk space. But, when I change the my.cnf file parameters ...
1
vote
2answers
42 views

Execute code ONLY if we have the ability to do full text search on database (mysql >= 5.6.4)

I found this answer for how to have conditional logic for execution of mysql statements (http://stackoverflow.com/a/7537348/627473). Seems to work great. But I need to find a way to execute code for ...
0
votes
1answer
18 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
2
votes
1answer
30 views

MySQL connection process slow for non-root user

I'm using Percona server 5.6 on Linux. When I connect to MySQL from the command line as the root user, it connects immediately. I have also created a new user in the database, but when I try to ...
0
votes
2answers
621 views

Can't get mysql running

After canceling an Ubuntu upgrade, Mysql refuses to start. this is what I get when I try to run it : $ mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket ...
0
votes
1answer
1k views

Problems in opening mysql query browser

My mysql query browser doesn't run. I just started to work with xampp and php. So I just installed xampp in my local machine. Now I have a problem in opening mysql query browser... What I'm getting ...
0
votes
2answers
2k views

Mysql Server Login and Permission Deny Error

I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!). Also not able to ...
2
votes
1answer
114 views

Why do these concurrent MySQL inserts deadlock?

I am baffled by this deadlock: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 150511 18:42:43 *** (1) TRANSACTION: TRANSACTION B8972CA4, ACTIVE 0 sec inserting mysql ...
3
votes
4answers
6k views

How to add column to big table in MySQL

I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried: Alter table in screen with table ...
0
votes
1answer
964 views

MySQL General error: 126 Incorrect key file for table '/tmp/#sql_27e6_0.MYI'; try to repair it'

I have a table in MySQL with ~2,000,000 rows. When I try to select all records, it exhausts the system's memory and I get an error about memory exhaustion back. When I try to use the MySQL LIMIT and ...
1
vote
2answers
55 views

MySQL - How to make a DATEDIFF in years?

This is my first post (and English is not my mother tongue) so I hope I won't screw this post up. I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed ...
0
votes
2answers
20 views

Percona XtraDB - Random deadlock

Randomly my database gets locked completely and queries hang and they pile up until it gets "Too many connections". Below is my mysql log file, I couldn't find anything related to the error system ...
6
votes
1answer
9k views

Should a MySQL replication slave be set to read only?

I've got replication running on Percona Server 5.5 by following this guide and wondered if I should add read-only=1 to my slave's my.cnf to make it read only? The guide sets up replication for the ...
1
vote
1answer
489 views

Multiple product attribute inventory database design

I have a database to build based on inventory for my shop. This is the current data that I have put into tables. This is the package: This is the product attribute: This is the table ...
0
votes
3answers
27 views

Unique full text index?

Is it possible to create a UNIQUE FULLTEXT index? I am using a mysql diff tool that is complaining that I am using duplicate indexes. I have a column that needs to be unique but also used for full ...
2
votes
3answers
1k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
199 views

Using non-replicated read/write databases on a MySQL slave server

I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. ...
6
votes
3answers
179 views

Implementing versioning system with MySQL

I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help. Initially I had my blogstories table with this structure: | Column ...
0
votes
1answer
468 views

MyISAM : Should I be concerned by these warnings?

I restored some tables from recent Databases backups. Because Im supporting a legacy system that only uses MyISAM tables in their DBs, for backing up I used a little script that pretty much copies all ...
1
vote
1answer
227 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
4
votes
4answers
11k views

Any option for mysqldump to ignore databases for backup?

We have 40 databases in our server. We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore ...
0
votes
0answers
17 views

mysqldiff utility on all tables

I am using the mysql diff tool at http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldiff.html It works great but I have to do this for each table mysqldiff --difftype=differ ...
0
votes
1answer
23 views

ERROR 1091 (42000): Can't DROP 'created_date'; check that column/key exists

mysql> alter table bill_item add index idx_comp(created_date,product_id); Query OK, 0 rows affected, 1 warning (1 min 2.62 sec) Records: 0 Duplicates: 0 Warnings: 1 Trying to drop the index: ...
1
vote
1answer
36 views

Adding restaurants in database

I wanna store some vendors (like McDonald ,etc) and it's branches in a table. a vendor may,or may not have branches. I have created two tables: 1- A vendors table which have name,logo, etc. 2-A ...
1
vote
5answers
29 views

Anyway to identify mysql slaves have read upto which binary logs

I have multiple slaves reading from one master.. But few of them are keep disconnecting due to network issue. Is there any way that i can make sure before setting up the bin log purging script on ...
0
votes
0answers
24 views

What's better and faster — MySQL or XML based Filesystem [on hold]

Good day, my boss is trying to push a project to be used with a CMS called "Weblication". Weblication is a PHP Framework using only files in XML and processing everything with XSLT and PHP. So all ...
2
votes
1answer
52 views

Deleting large amounts of cascading records with minimal locking

I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE ...
0
votes
1answer
133 views

Need help achieving HAProxy active/passive configuration for mysql

I'm using HAProxy for mysql database backends. The database had to be master-slave. The goal is to point all mysql clients at the proxy and for all requests to go to mysql server #1 and only go to ...
2
votes
3answers
794 views

Mac OS X 10.10 mySQL 5.6.26 accessible from CLI but not port 3306

I did a fresh install of mySQL 5.6.26 on my Mac today. I was able to access mySQL from Terminal.app as expected, however I could not get mySQL workbench to attach to 127.0.0.1 port 3306. What really ...
2
votes
1answer
229 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
1
vote
3answers
54 views

Users vs. People - where to keep the email address?

I'm designing an app similar to Facebook, called Headtome. I want to have users that create public profiles, including email addresses. Then, Mary can choose to message John, and that message goes ...
8
votes
5answers
87k views

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT ...
0
votes
0answers
20 views

MySQL How to get the nearest child for every parent in a single query?

MySQL, I need to get the nearest location for each organization. Between organizations and locations is a one-to-many relation. One organization has many locations. The GPS coordinates are stored in ...
1
vote
0answers
54 views

Space usage when copying

Short version Copy table took up more space than I expected or tested Why would that be? Could it be the replication / binlog? If so, what then is written to the binlog during this query: INSERT ...
1
vote
1answer
68 views

Matching data in two tables

I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure. Say, you have two tables, one listing the customer's creditcard_number, and one listing ...
1
vote
1answer
68 views

Speed up MariaDB?

I have a Wordpress Woocommerce store using WP All Import Pro to run automatic imports on a 15 minute cronjob schedule. It's usually trying to update 400 to 500 items from our supplier every 15 ...
4
votes
1answer
5k views

How can I mysqldump all databases except the mysql schema? [duplicate]

I want to backup all 200+ databases on MySQL server to all-dbs.sql. I want to exclude the mysql schema. How can I do that ?
0
votes
0answers
28 views

MySQL - Optimizing Multiple WHERE Conditions

I have to run the following 2 condition query against a table: SELECT * FROM serial_numbers WHERE type = 'book' AND serial NOT REGEXP 'ISBN(?:-13)?:?\x20*(?=.{17}$)97(?:8|9)([ ...
0
votes
1answer
143 views

Mysql: How can i make a host show up by hostname in 'show processlist' rather than by IP address

THought this might be something controlled via rdns but updated that and still getting connections for a couple machines showing in 'show processlist' as the ip address (and port) rather than hostname ...
0
votes
1answer
579 views

Mariadb on Centos 7 not changing max connections based on my.cnf

The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly. [mysqld] ...
0
votes
1answer
416 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...
0
votes
0answers
24 views

Switching from MySQL to MariaDB resulted in a high number of transactions

I switched from MySQL 5.5.44 to MariaDB 10.0 on a server and the number of transactions went through the roof without any increase in website traffic(the website traffic is the only thing that uses ...
1
vote
1answer
183 views

How do you calculate how much hardware resources you need for a database?

We are in the process of scaling our database servers. I was wondering how should we calculate how much hardware resources we need for our databases? Here is a little bit information about our ...
0
votes
0answers
147 views

mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway ...
5
votes
4answers
307 views

How to exclude NULL values inside CONCAT MySQL?

If I have this - tadd is the Address table: CONCAT(tadd.street_number, ' ', tadd.street_name,', ', tadd.apt_number,', ', tadd.city,', ', ...
2
votes
2answers
210 views

Java Spring application Database triggers auditing - how to provide the right user that made the change

We are developing a new web application. one of the most basic requirement is to audit all entities changes into a separate table. We would like to use DB triggers for that purpose. We use MySQL as ...
0
votes
1answer
54 views

Optimised way to search over 2 milllion poi data in mysql

We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4 select *, ( ...
1
vote
1answer
14 views

MySQL get previous record and compare

Need to make a Comparable between the records of this table and take the total times the value calledstationid 42 preceded the value 34 (this is a system log table and calledstationid is not an ...
0
votes
1answer
220 views

Accuracy of mysqldbcompare vs other tools like pt-table-checksum

I was curious to hear what everyone's experiences are with using mysqldbcompare vs pt-table-checksum( from Percona Toolkit ) to find the differences between two MySQL databases. I have heard that ...