All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

0
votes
0answers
4 views

Best storage engine for huge tables with mostly update operations (74 million records, MyISAM table size 7.6G)

What will be the best storage engine for table with 74 million records, currently stored as MyISAM table size 7.6G. Operations are mostly select and update. DDL operations (ALTER ADD COLUMN) are ...
0
votes
1answer
14 views

Mysql Inner join performance

I have performance problem! I searched for Inner Join, tried optimize table but still the same. I have Users, Entries, Tags and tag_user tables. This code works perfectly : select * from `entries` ...
0
votes
1answer
11 views

Mysql : Aborted connection …/… (Got an error reading communication packets)

This error message had submitted many times in this forum. I have tried the following without success. Context This is a Symfony1/Doctrine1 web application. The error appear on my computer and on ...
0
votes
0answers
9 views

Things we can optimize when runnung TPC-H benchmark

I am a Graduate student running TPC-H benchmark on MYSQL 5.6 . I have EC2 instance M3 large instance with a Intel Xeon E5-2670 v2 , 7.5 GB memory and 2 vCPU. Can someone please suggest me what are ...
0
votes
0answers
17 views

Sql get results only containing all keywords

Let's say I have mysql table called SONGS, with columns: Artist | Featuredartist | Songname | Year What I need is to get Artist and Songname, only containing my keywords (and no matter how they are ...
0
votes
0answers
21 views

MySQL - Harmonize multiple time ranges

I have the following problem with harmonizing multiple time ranges. Let's say I have the following data: |range_object_id | start_time | end_time | |-------------------+---...
0
votes
0answers
11 views

How to restore MySql DB from previous version files

My wamp server crashed a few days ago and I couldn't save the DB from phpMyAdmin. So I saved the folder bin/mysql/mysql5.6.13. Then I installed a new wamp version which comes with myslq5.7.11. I've ...
0
votes
0answers
7 views

SELECT Query is slow when run remotely in MySQL

When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then ...
2
votes
1answer
15 views

Howto implement correctly Businness Logic in database

I'm searching about how to disable triggers, and seems like it's not possible under MariaDB. My problem is, how to manage then, when you import data. I have a database where almost all business logic ...
1
vote
1answer
14 views

best way for creating Index in mysql (multi-column/table)?

i have a big problem with my database.. I need a very fast query over 2 tables. 1. table: (node) ~42.500.000 entrys 2. table: (waynode ~51.200.000 entrys The following query is much to slow: ...
-1
votes
1answer
18 views

How can I create a church attendance report system?

Please am pretty new to php and mysql. I have an assignment to create a church attendance system for our weekly services. The system would be able to know how many males and females came to church ...
1
vote
1answer
23 views

Multiple SELECT With Multiple WHERE

I am stumped and need your help. I am trying to select three columns from my table where one of them is a VARCHAR and the other two are either "Y" or "N". SELECT CurrencyPair, Count(Accepted) As ...
2
votes
2answers
29 views

SUM and Group By Optimization

I use this query: SELECT logdate, SUM(bytes)/1024/1024/1024 as traffic, customer FROM `Logdata` WHERE customer is not null AND logdate >= "2016-07-01" AND ...
0
votes
0answers
9 views

Java Library to Dump MySQL Database to File [on hold]

I am looking for a Java library to embed into a Java web application to enable backup of a MySQL database to a zip file. The first question would be why not use MySQL dump, this is an option that we ...
0
votes
1answer
12 views

Identify duplicates and apply logic

I have a largish 27mil row table. The table contains a large number of duplicate string values due to case variance. All rows have the string column and also one additional numerical column, and I ...
2
votes
2answers
47 views

MariaDB 10.1.16 on Ubuntu fails to start: WSREP Failed to recover position

I'm using 10.1.16-MariaDB-1~xenial from the official MariaDB apt repository for 10.1 [stable], via the University of Texas mirror. I had a perfectly functioning MariaDB Galera cluster setup on 3 ...
1
vote
1answer
20 views

MySQL unique static ID

I need to get an unique static ID that is not changeable by the users, and if the user copies the database to another server this static ID is different. Background My company wants to sell our app ...
0
votes
1answer
13 views

calculate average of a class where students offer different number of subjects

I am trying to calculate the class average of a class where students can hold different amounts of subjects (e.g.: two students in a class of which one takes 3 subjects and the other 2). How do I get ...
0
votes
1answer
13 views

Trying to understand MySQL Replication with SSL

I'm trying to get my head around the use of SSL with MySQL replication. I've spent all day reading blog posts / articles about this, and I'm still no closer to getting an answer to this question: ...
1
vote
1answer
25 views

MySQL usage suddenly hitting through the roof

We have a site with MySQL DB running properly for more than 2 years. However, 2 weeks ago we noticed a sudden spike in load. We started the MySQL service and load normalized. We assumed the issue was ...
1
vote
1answer
32 views

Best way to create dynamic table for search

There is a table of documents: CREATE TABLE `docs` ( `doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `doc_uuid` binary(16) NOT NULL, `sender_uuid` varchar(40) NOT NULL, `receiver_uuid` ...
0
votes
0answers
13 views

MySQL - Trigger on insert to capture the change or Change Data Capture?

I have a MySQL table myTable with 4 columns id,name,version and date. Everyday I get the data and this data changes only when they install new version of our products. I would like to analyze the ...
1
vote
2answers
21 views

Different services using different port pointing to same datadir folder

Let say I have already installed service MySQLa with mysqla.ini (port number=3306) setting and datadir folder in ...\data Is it possible then, I install another service MySQLb with mysqlb.ini (port ...
0
votes
0answers
13 views

Calculate class average where students offers different number of subjects

Please guys help me get the exact class average of a class. In this case the students in the class are offering different number of subjects, so a student in the same class can be offering 8 subject ...
0
votes
1answer
15 views

MYSQL inner join , if not match try another table

I've got a very quick issue here, how can i join a table with two other tables in a way if one's info doesnt match, it goes try another one. something like: $properties = $mysqli->query("SELECT *...
0
votes
0answers
14 views

ERROR 2002 (HY000) … mysql.sock

Mysql Version :: 5.0.51b OS Version :: CentOS 6.4 Error : ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/lampp/var/mysql/mysql.sock' (2) I got this error in live ...
0
votes
0answers
18 views

Collation in variables

After having many issues with ISO-8859-1 and reading a lot about collations I finally understand that I need to use charset UTF8 collate utf8_unicode_ci. So I configure my whole application with these ...
0
votes
0answers
9 views

Moved MySQL server, new entries have wrong timestamp

Setup: Linux (Ubuntu 14.04) + MySQL Server 5.6 I've got a site where the website and MySQL server were running on the same machine, located in France. Now, I've gone ahead and moved the website and ...
0
votes
2answers
10 views

mysql error 1265 data truncated for column

I created a table in mysql like this : create table if not exists data (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, execute_time DATETIME NOT NULL default current_timestamp, db VARCHAR(25) NOT NULL, ...
0
votes
0answers
18 views

Live Syncing Data Between PostgreSQL and MySQL clusters

I have a unique situation to solve. My company is switching over to an app that runs on a Postgres backend. The app is replacing a current set of apps that run on a MySQL cluster. However, our initial ...
0
votes
1answer
17 views

How to make table with row name and column name

I am new to databases and now i came to a point where i need more complex table. I have a table which contains site details. Each site can have multiple devices. Each device have multiple measurement ...
0
votes
1answer
27 views

MySQL Backups - Full vs incremental - innobackupex

So I'm implementing a backup strategy for our project and came across some text which I need absolute clarification, false positives are the death nell of IT. I am looking to do your typical backup ...
2
votes
2answers
89 views

Is 3 minutes to join a 50 million table too much?

I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not. Basically, I need to get a count of old and invalid entries per group, which ...
0
votes
1answer
15 views

pt-table-checksum show wrong DIFF

I'm try to use pt-table-checksum tool. I have Master/Slave tables, and I add some rows to the slave to check it. pt-table-checksum --ignore-databases mysql S=/tmp/5.6.22_3306_Master/data/mysql.sock --...
0
votes
0answers
19 views

how to load columns in specific order while uploading csv file in Mysql [on hold]

I want to upload a CSV file having 17 columns in a specific order from 1 to 33 into a MySQL database. Some of the columns need to be ignored as other files also have to be uploaded in same table. I ...
0
votes
0answers
11 views

sql query to insert the column for adding products in mysql [on hold]

We are trying to save the Multiselect options assigned to particular brand. we are using this table name : outthink_advance_brand_category I want to insert column to this database table name as i ...
0
votes
0answers
12 views

Galera segments relay node — is there a way to prefer one?

I have geo-distributed cluster with 2 segments. Main segment is in Singapore, secondary -- in Europe. Recently I found out, that one node of main segment has RTT to Eu node about 5 times less, than ...
0
votes
1answer
12 views

Restroring MYSQL databse from .frm and .ibd files (database does not exist error)

please help ... I want to restore MYSQL database using .frm and .ibd files (that's all I am having). here is additional information.. I reinstalled same version of WAMP without taking backup of ...
0
votes
0answers
22 views

mysql select duplicate columns for same row

Ok I'm trying to write a customized report for a preexsiting database(IE, I didn't design it and can't change the convoluted structure) that has a user table and 2 other tables that store user ...
1
vote
3answers
15 views

Fix double-encoded data

I have a field defined as Question VARCHAR(1024) CHARACTER SET utf8 NOT NULL. Our application stored double-encoded data into this field. For example, é shows up as é in phpMyAdmin. I've added a ...
1
vote
1answer
16 views

Alter TEXT column to NOT NULL when there is existing NULL rows

Mysql does not support default values for TEXT columns. I have a live database with NULL values What is the proper way to change all null values to a predefined value and change the column to not ...
0
votes
0answers
21 views

mysql pirate users logged in, what should I do

I'm using Mysql Server 5.1.73 and when I do a show processlists I can see some connections which shouldn't be there. In theory only the web app should be connected to this database. The connection ...
0
votes
0answers
15 views

MYSQL : Need Query How to update the NULL value for every next row of current row in MySQL table?

Example : Two table we can take, Table names are table1 and table2. Table1: It having 3 columns are id,date_s,value_s and the records are, 1 2005-01-01 25 2 2005-01-02 30 3 2005-01-03 10 4 ...
0
votes
0answers
23 views

Update Multiple Row's [migrated]

I would like to change one field of multiple row's at once by user input. Something like: I had some books at some branch, and i want to change some of them to other branch. The user select the ...
1
vote
1answer
23 views

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`...
0
votes
1answer
47 views

How could duplicate entry on unique key happen if only one master is active?

Say, I we have master-master replication with only one master active at a time. We have users table: create table users ( id int auto_increment, email varchar(255), primary key id(id), unique ...
0
votes
1answer
10 views

Create trigger that Find, Parse and Insert to another table After inserting on table

I want to create a trigger that will insert to another table after inserting on my table. Scenario: I will insert on my inbox table. Then, on my inbox table there is a field named Msg. Next, I want ...
1
vote
1answer
10 views

Mysql auto insert record in primary table if not exist

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key? For example, assume these tables: - user(id, name, age) - topic(id, name) - ...
2
votes
1answer
57 views

Calculating a percentage discount in SQL [on hold]

Basically just started using sql and having a minor problem. Working out the percentage of a number. Example: What would I need to type in to work out: 2.75 - 5% Drink_price= 2.75 After 5%...
1
vote
1answer
19 views

MySQL 5.6 InnoDB doesn't work

We have been having issues with one of our Windows environments. It uses MySQL and has been running on MyISAM only. We need to create a few InnoDB tables but the engine won't start. We tried changing ...