MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
7 views

Hi disk I/O and crash when turning on sync_binlog in MySQL

I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it. When I set global sync_binlog = 1, the server I/O goes very high and after some ...
0
votes
0answers
12 views

MYSQL Query Optimization

MYSQL version 5.6.23 CENTOS 7 Hi, I have the following query that is coming from an application that I am hoping to make more efficient from the mysql side of things. I have an index built on ...
2
votes
3answers
1k views

Mysql datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system... Is it fine to have the username column as ...
0
votes
2answers
104 views

Mysql 5.5.25 must be restarted to connect

We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 ...
0
votes
0answers
18 views

MySQL: Capture every 5 values from column a in Table A to create one row in Table B

I have submissions of a form from a web page records in a database. the table has the following schema submit_time form_name field_name field_value field_order file the form has 5 fields, ...
0
votes
2answers
163 views

Dealing with data stored as arrays in a MySQL DB

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
2
votes
3answers
993 views

MySql shell command not replicated to slave

I have a shell script that looks something like that: #!/bin/bash mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv' INTO TABLE db_name.db_table FIELDS TERMINATED BY ...
0
votes
1answer
25 views

InnoDB Table has backed up INSERT and UPDATE queries, high CPU

I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST; Basically, I keep a table of every logged in customer and update ...
2
votes
2answers
65 views

How to interpret correlated subqueries?

EDIT: Both answers are great but I think I must clarify my question. NOTE: I must indicate that in the following, by executed, I mean logically. I understand that the actual execution made by the ...
0
votes
0answers
11 views

Avoid slow query from slowing down the site [on hold]

I have a slow select query which I optimized and still takes 3 minutes. It does not lock any tables it uses. But at the same time the tables are been used by other queries which slows down the ...
2
votes
1answer
15 views

Geo localized database RTREE

I'm using MYSQL V5.6.24 GPL I have a database with 200K rows and growing and I'm trying to get the nearest, based on points. I have all the database in InooDB, and to use Spatial data, I create one ...
0
votes
1answer
21 views

Sumed all which has been summed [duplicate]

Is it possible to sum all row which has been summed? For instance I have this query: SELECT SUM(registered) AS `totalOftheDay` FROM table WHERE DATE(ds.register_time) BETWEEN '2015-03-01 ...
1
vote
1answer
108 views

Reviewing relational database tables

I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong. I have ...
0
votes
0answers
12 views

How to do updates on all rows at every button click [migrated]

I have a python app that has an admin dashboard. There I have a button called "Update DB". (The app uses MySQL and SQLAlchemy) Once it's clicked it makes an API call and gets a list of data and ...
2
votes
2answers
167 views

MySQL - How to check whether all values of a given SELECTed row are equals? [on hold]

Using MySQL, how can i SELECT all rows whose values are equals ? For instance, suppose a SELECT of a given table as follows COLUMN_A COLUMN_B COLUMN_C ... COLUMN_N 1 2 4 ...
3
votes
1answer
399 views

Removing “duplicates” within a time range

I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple ...
0
votes
1answer
14 views

Student Attendance, null not returning [on hold]

My question is based on the answer given here Attendance: id | student_id | date | ------------------------------ 1 | 2 | 2013-10-24 | Students: student_id | student_name | ...
0
votes
1answer
68 views

Sync MySQL database and Quickbooks Database

I use Quickbooks as an accounting software. I also have a MySQL database where I record data on orders, customers, inventory, employees and etc. Is there a way for me to sync the two databases? For ...
-2
votes
3answers
57 views

How to interract with my database? [on hold]

I am generally new to Databases, and I have a MySQL server with several tables which have a unique ID that is connecting them all. I am about to populate the db with data from external resources ...
2
votes
1answer
1k views

Mysql search matching multiple values in multiple columns

I need to speed up a search. Currently we let our users input a name: John Doe Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms ...
0
votes
1answer
11 views

Gotchas converting latin1_swedish_ci to utf8mb4_unicode_ci

I have a MySQL 5.5.31 database which has approx 220 tables - of these 220 tables, around half of them are already using utf8mb4_unicode_ci but the "older" tables are still using latin1_swedish_ci. The ...
0
votes
1answer
151 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
-4
votes
0answers
16 views

stored procedure not working

i am running this one on mysql phpmyadmin. DROP PROCEDURE `gettree`// CREATE DEFINER=`root`@`localhost` PROCEDURE `gettree`(in team_id bigint) BEGIN declare count bigint; declare tmp_team_id ...
0
votes
2answers
64 views

Query that was working on MySQL is not working on PostgreSQL

I had the following query (with bin_number being a string) which was working on MySQL 5.5: Postgres - was working on MySQL and doesn't work on PostgreSQL SELECT "menu_items".* FROM "menu_items" ...
1
vote
1answer
34 views

updating same column as in the select query

When I try to do this, I get an error saying I cannot use the same table in where select as the column that I am updating. UPDATE table_name SET quantity = 19 WHERE productID = 148357 ...
1
vote
2answers
603 views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
0
votes
1answer
98 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
1
vote
2answers
537 views

How to insert values in junction table for many to many relationships?

I have three tables in database: trips(trip_id(pk), trip_name(unique), user_id(fk)) places(place_id(pk), place_name(unique)) trips_places_asc(trip_id(fk), place_id(fk)) Since many trips can ...
0
votes
1answer
156 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
0
votes
1answer
719 views

Can't change root password: “The system cannot find the file specified.”

I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message: ...
0
votes
1answer
285 views

phpmyadmin not working after mysql update

i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal. But now there is another ...
0
votes
1answer
28 views

What is the performance benefit when using a temporary table to update a regular table?

I'm trying to understand what the performance benefit between two different methods of updating multiple table rows. For the first method we have an explicit update statement for each line to be ...
0
votes
1answer
129 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
0
votes
1answer
113 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...
0
votes
2answers
205 views

How can I optimize this query and support multiple SKUs?

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
0
votes
1answer
24 views

sql query for hierarchal search [on hold]

In the below relational table, I want to search all user id's whom are are under any particular sponsorid in hierarchical manner User Id Name Sponsor Id 10030080 TULSI RAM ...
0
votes
1answer
47 views

Get the latest record and count them with time interval

I have a table as below id fk_sales_id status created_at 1 001 5 2015-02-17 16:45:44 2 001 8 2015-02-18 18:45:44 3 002 30 2015-02-20 16:45:44 ...
0
votes
1answer
170 views

How do I write this script?

I have a very large database and I want to select certain records and then set a column value such as: SELECT name, email FROM table WHERE zipcode = 12345 INTO outfile /tmp/file.csv; Then I ...
0
votes
1answer
274 views

unable to insert utf8mb4 characters in mysql 5.6

recently i was facing problem in inserting emoji and other 4 bytes character in my mysql database then i found that my mysql version was 5.1 and it does not support utf8mb4 so i upgraded to mysql 5.6 ...
0
votes
1answer
190 views

Get child record from specific root

I have two tables articles and topics and I am using MySQL 5.6 Articles id topic(fk on Topics.id) featured 1 1 1 2 2 0 3 3 ...
5
votes
1answer
625 views

SQL Query challenge

I have what seemed like an easy query to write, that really has me stumped - can anyone help me please? So this is really easy on paper, but I can't translate it into SQL. Lets say you have some ...
1
vote
1answer
118 views

mysql restore from data directory

I recently asked a question regarding the restoration of a pretty huge (around 300 to 400 GB) mysql database and got some nice suggestions as seen here. Now, I have backed up the data directory of ...
0
votes
1answer
200 views

Many mysql queries stuck at sending data

I am using opencart and I can se many queries blocking the mysql server. All queries are the same except the p.manufacturer_id. Attached there is the query and the explain of the query. The query is ...
1
vote
1answer
121 views

Relaying changes to a slave from another master with Tungsten on MySQL

I am trying to setup a multi-master type of replication with each master having an additional slave using Tungsten. As an example, lets say I have 4 hosts (A,B,C,D) and hosts A and C are the masters, ...
0
votes
1answer
128 views

Please suggest my.cnf configuration based upon mysqltuner findings

I have been facing a lot of slow browsing on my site nerdeky.com. My server administrator suggested me to use mysqltuner.pl to find and tune my databases. Can anyone select me what should be the good ...
0
votes
1answer
99 views

Fastest way to insert data into mysql from csv

I have a dataset of 233,902,846 rows in a csv file. I want to load the data into mysql table. What is the most efficient way of transferring the data so that data insertion is the fastest. actually ...
0
votes
0answers
21 views

I want to rewrite my MySQL query so that FIND_IN_SET doesn't do a full table scan unless necessary

I want to rewrite my MySQL query so that the last table join doesn't do a full table scan unless necessary. Here's what I mean. Under certain conditions I don't need to select any rows in that join ...
2
votes
1answer
248 views

Table design problem

I'm looking for a SQL design solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his data. ...
1
vote
1answer
579 views

MySQL relay log rotates too soon and too often

I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small: 4:00 PM ...
0
votes
1answer
91 views

MySql MBRCONTAINS not accurate

I have a table that has 3 column. city_id, city_name and city_geo. city_geo type is geometry. and i have a another table that has a vehiclelog. I want to query if the latitude and longitude from ...