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
1answer
12 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: ...
3
votes
4answers
1k views

Avoiding MySQL Fabric to become a single point of failure

I managed to install and configure MySQL Fabric that manages multiple MySQL Server nodes. It works very well since I tested it by connecting to the Master node directly and played with the data (...
1
vote
1answer
299 views

What's the approach to figure out reason for mysql crash?

The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" ...
-3
votes
2answers
85 views

How do I find mismatches in two tables?

I have a database where I'm trying to find inconsistencies across two tables. I'm new to SQL and would like some help. The other questions I've looked up are confusing and too convoluted for what I'...
-1
votes
1answer
15 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
11 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: ...
0
votes
1answer
214 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
0answers
11 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 ...
0
votes
1answer
117 views

want to import exported xml file in wamp phpmyadmin

I have exported single table from wamp phpmyadmin as xml file for data verification and want to import it again into database as mysql table. Please give me guidance, how its possible?
1
vote
1answer
158 views

MySQL very long-running queries

I have a quite large db (800+Mb dump file) that I imported to my local server. It's a Wordpress db from witch I need to extract certain posts. There are around 160,000 posts inside. Currently I'm ...
2
votes
1answer
18 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
1answer
165 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
0
votes
0answers
7 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
149 views

How to group records in mysql query

I have a table (user_access) with the following columns in mysql database id int user int access_time datetime I am trying to run a query that gives me the number of times users accesses ...
1
vote
1answer
2k views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
0
votes
1answer
117 views

MySQL Trigger Update

How do I update 'emailupdate' date with a trigger when 'email' is updated through a script? +------+----------------+--------------+ | id | email | emailupdate | +------+----------------+-...
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 ...
0
votes
2answers
219 views

Best configuration for MySQL? InnoDB and MyISAM

I read that InnoDB is supposed to be better at writing into MySQL than MyISAM, however, I'm trying it out, by running 3000 MySQL queries(Inserting rows, 8 Columns + id)... but results haven't turned ...
2
votes
1answer
462 views

mysqldump vs mysqlpump

I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? ...
0
votes
0answers
11 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 ...
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 ...
2
votes
2answers
32 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 ...
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
2answers
2k views

Update MySQL Stored Procedure Definers on shared environment

I am accessing a MYSQL database through PHPMyADMIN. My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL ...
0
votes
1answer
122 views

MySQL database dump bombs out Error 1064 when using LOCK TABLES

This specific machine is running MySQL version 5.0.27. I have tried to get a full dump of this database several times and each time it bombs out. Tried getting just a structure dump of it and this is ...
0
votes
1answer
621 views

MySQL: Transaction and Deadlock

I have table "tbl_data", where have a lot of rows (3 - 5 million). I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table. If I start *.jar file (for example, "run.jar") and after ...
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 ...
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` ...
2
votes
2answers
87 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 ...
1
vote
2answers
18 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
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
1answer
614 views

ERROR 2026 (HY000): SSL connection error: Unable to get private key

I need help with mysql replication with ssl. I got a problem when i'm turn on ssl on my slave - error connecting to master 'replica@1.2.3.4:3306' - retry-time: 60 retries: 1 Here is my setup Master ...
3
votes
1answer
159 views

How to monitor mysql percentage disk utilization?

I am new to mysql world and not sure if there is a way to calculate mysql TableSpace/DiskSpace utilization in percentage. I am aware mysql does not provide upper limit or free space like Oracle DB, ...
2
votes
2answers
204 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) Check-...
1
vote
2answers
2k views

MySQL flush logs, unknown error

I gave been trying to solve this for few days now, and cant seem to figure this out. I have been trying to set log rotation for mysql slow using linux logrotate feature, and my script seems to get ...
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 ...
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 *...
1
vote
1answer
150 views

Special Characters in MySQL using UTF8 Unicode Collation and LOAD DATA INFILE

I have several csv files in am loading into MySQl using Java. In the Description field I have several Special Characters that are causing the load to fail. I am using LOAD DATA INFILE. This is nested ...
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 ...
1
vote
1answer
955 views

MySQL 5.7 doesn't start after installation on CentOS 6.7

I have just installed MySQL 5.7 on a fresh CentOS 6.7(vm). When I start the MySQL service with this command: sudo service mysqld start It doesn't start and raise this error: Initializing ...
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
2answers
9 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
1answer
311 views

How to use explode mysql function in where condition with cakephp

I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 . Now my question is that how can i check if new posted payment method id is available in ...
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 ...
0
votes
0answers
18 views

SOLVED - How to import a text file with “,” value to Mysql

I have data in a text file that have value like this : _______________________________________ |id|name_item |value| _______________________________________ | 1|Luas ...
0
votes
2answers
34 views

Checksum of data imported from mysqldump not equal to the original checksum?

I'm tring to create a mysql replication instance. So I mysqldump --complete-insert --skip-dump-date -uuser -ppass db table > master.sql. Also I've imported the master.sql to the slave instance ...
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
0answers
17 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
3answers
131 views

How do I echo data from two tables that are associated to one user in php

Say I have tbl_company with these cid------- cons_no-------- company_name ------- phone ------ address 1---------- q1----------------aple------------------------22222-------newyork 2 ------...
0
votes
1answer
28 views

Move large mysql table data from one server to another

I have a table to which millions of data are inserted every day. As the table size grows more than 10 GB, I have issues with the performance of server in fetching apis and generating reports, which ...