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

1
vote
1answer
30 views

Why is MySQL choosing that index?

I have a large indexed table with over 35 million rows. Here are the indexes, with a naming convention that shows their sequential column order: IDX_E93438363DAE128B, ...
0
votes
2answers
35 views

Pull entire row per user based on MAX(Column) MySQL

I am trying to pull whole record from database for the highest "Score" column per user. TestTable structure ------------------ ID | Name | Score| ------------------ 1 | john | 10 2 | mark | 20 3 ...
1
vote
2answers
16 views

How to get readonly access to mysql files via g+w permission 750 or 640?

The data-dir itself has permissions 700 thereby limiting access to the mysql user. I want other folks who I add to the mysql group to be able to read binlogs and download the *.frm, *.MYI, *.MYD ...
0
votes
1answer
33 views

mysqldump collation issues

I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment. The problem I am facing now is that our wp database after ...
0
votes
1answer
15 views

Mysql Replication backup and restoration failed multipal time

I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason. I have 2 host Master & Slave with below details. Server1_Master os-centos6.7 mysql- ...
0
votes
2answers
41 views

Pull entire row based on MAX(Column) MySQL

I am trying to pull whole record from database for the highest "Score" column. TestTable structure ------------------ ID | Name | Score| ------------------ 1 | john | 10 2 | mark | 20 3 | ted | ...
2
votes
1answer
25 views

Can't access MySQL database with created user through dll

I have a web service that connects to a MySQL database from a dll, both of them are on the same machine. If I use the root user and password it will connect no problem but if I use a user that I ...
1
vote
1answer
48 views

Prepare MySQL for big UPDATE query on production server

Surprisingly I have not found anything relevant on this subject. What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details ...
1
vote
2answers
24 views

Fulltext searching on shared hosting - 2000 Entires with around 500 words each

I'm fairly new to db administration and would appreciate some help. I want to host a site which requires full text search on around 2000 items, each with around 500 words each. I need this to be ...
2
votes
1answer
37 views

How to compare MySQL privileges granted to privileges used?

I have inherited a MySQL server that has a user with far too many privileges granted but I believe not all of these are required. Is there any way to compare the MySQL privileges granted to the MySQL ...
2
votes
2answers
38 views

Set variable with dynamic name in trigger (MySQL)

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this: delimiter ;; ...
1
vote
1answer
60 views

Select ONE most recent post for each author

I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry. I use recent versions of PostgreSQL and MySQL. I have 2 tables: CREATE ...
1
vote
1answer
49 views

Can't start mysql on Ubuntu 14.04

When I try to start sudo service mysql start start: Job failed to start Then I look the /var/log/mysql/error.log 160122 8:43:42 [Warning] Using unique option prefix myisam-recover instead of ...
0
votes
1answer
16 views

Fabric - Operation CREATE USER failed for 'fabric_server'@'%''

I am finding the use of fabric to be tottalyh unstable. Here is what I do and the below is the result: mysqlfabric group add druid 192.34.59.181:3306 mysqlfabric group add druid 192.34.56.66:3306 ...
0
votes
0answers
20 views

mysql returns FUNCTION .LAST_INSERT_ID does not exist

I'm new to MySQL. I have created two tables. create table Shopping( Trans_ID int not null auto_increment primary key, date date, item varchar, quantity double, rate ...
1
vote
1answer
30 views

Count from a count (distinct)

I have a query like this: select * from questions as q left join question_filters as qf on q.id = qf.question_id left join users as u on q.user_id = u.id left join trainers as t on ...
0
votes
0answers
22 views

Phpmyadmin not displaying table data

I have a table named "log", which basically stores error log for my php application. My application has database running on rds, and recently i had to delete logs from this table, so i downloaded a ...
0
votes
1answer
17 views

mixing binlog formats between master and slave

I have a pretty much straightforward Master --> Slave replication setup in MySQL 5.6 / Innodb. Both my Master and Slave are setup in MIXED binlog format. Now I need my slave to act as a master to ...
0
votes
1answer
58 views

Innodb full table Scan

mytable is an InnoDB table with (a, b) as the primary key. Does this query check all table? Select * from mytable where ( b='2' and c='3') I want to find out if 'cluster' structure ...
0
votes
1answer
40 views

Fetching data from 3 tables

Wanna fetch data from 3 tables by mysql query to display in feed. But stack. My query is "SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, ...
0
votes
0answers
33 views

How to collect prepared statements in performance_schema on MySQL 5.7

Just installed MySQL 5.7.10 64bit locally (Windows environment) and prepared statements are not being logged in performance_schema. In release notes it's being specified this feature is available ...
-1
votes
1answer
75 views

Group when there are multiple items by a particular user within a certain timeframe [closed]

SELECT * FROM ( SELECT articles.id, articles.author_id, users.username, COUNT(articles.id) AS number_of_articles, GROUP_CONCAT(DISTINCT articles.id) AS articles_published FROM articles ...
1
vote
0answers
40 views

What is a good database design approach for my Online Quiz Android Application?

I have been working on an Android Online Quiz Application with PHP & MySQL. I know what attributes i need however when designing the relationship it becomes complex and confusing when I analysed ...
0
votes
2answers
27 views

show variables - Unknown column 'value' in 'where clause'

In mysql I ran the below: mysql> SHOW VARIABLES where variable_name = 'character_set_database'; +------------------------+-------+ | Variable_name | Value | ...
0
votes
0answers
23 views

Mysql partition - How to do list partitioning of a table that contains unique column?

I am doing mysql list partitioning. my table data is as below ---------------------------------------- id | unique_token | city | student_name | ---------------------------------------- 1 | xyz ...
0
votes
1answer
59 views

Restoring MySQL dump ERROR 1449

I took hot backup (dump) from my Master MySQL DB with below command mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql ...
0
votes
0answers
27 views

Configuring additional data node

After configuring the data node and the config.ini for the management node. When I run the ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial The table data that were created for previous ndbd ...
0
votes
0answers
43 views

MySQL performance issue after migration

I have a MySQL performance issue with a brand new server, and am somewhat of a beginner as a database administrator (I'm mostly a general-purpose Linux sysadmin). I'm looking for pointers where to ...
0
votes
1answer
31 views

Fast copy/rename database, not mysqldump

I would like to keep a "default" setup for a database on a testing server, which can be used to "restore" a "dirty" testing database back to the default state. mysqldump is very fast, done in a few ...
1
vote
1answer
42 views

MySQL query taking too long to execute

My MySQL query is taking around 20 minutes to execute which I feel is excessive. SELECT hyp_id,MAX( player_count ) AS MAXPLAYERCOUNT , MAX( viewer_count ) AS MAXVIEWERCOUNT , SUM( player_count ...
0
votes
0answers
35 views

Cannot restart mysql (installed natively) but possibly caused by a Homebrew package

I've previously been running mySql which I had installed with the dmg files without any issues, but I also have Homebrew installed and noticed some errors today which I fixed successfully but ...
0
votes
0answers
25 views

Dynamic columns or many columns? [duplicate]

I am using MySQL and I am trying to figure out how best to store my dynamic data I get from my application so it can easily be transformed into a single row for reporting purposes. My current ...
1
vote
3answers
40 views

MySQL Slave database size >> Master Database

I have a Percona MySQL Master-Master setup (5.6) with second master as passive master(read-only). Slave: mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / ...
-1
votes
2answers
36 views

MySQL: Why is ibdata file growing? How can we shrink it?

Even though we are using "innodb_file_per_table = 1" from day one. The ibdata file still continues to grow. Total size of our database is around 800GB and the ibdata file is currently around 50GB. All ...
2
votes
1answer
56 views

SQL Query for Internal Messaging System

I'm creating an internal messaging system (No SMTP/POP3) and I'm looking for a way to query a user's list of messages. Here's my schema: Messages _MessageID (PK) _MessageParentID (if a reply this ...
0
votes
1answer
29 views

Why running queries one by one yields correct results, but running lots of them in one chunk doesn't?

Let's say I create a simple table and a stored function to populate it: CREATE TABLE t ( id INT PRIMARY KEY, val VARCHAR(255) )// CREATE FUNCTION populateIfNotAlreadyIn (idArg INT, valArg ...
-1
votes
1answer
25 views

How to execute system command in mysql stored procedure? [closed]

Here is the code, delimiter // create procedure trig () begin declare cmd varchar(50); set cmd = "sh /home/yogaraj/Documents/Maastry/firefox.sh"; system cmd; end // delimiter ; But I ...
0
votes
1answer
40 views

Left Join Not returning rows when date is in range of longer than 1 day

How do I left join Table A and B? SELECT dev_hour.hour,rtdogs.delivery_date,rtdogs.delivery_hour,rtdogs.total_offer FROM ( SELECT hour FROM ( SELECT * FROM delivery_hour ) xx ...
0
votes
1answer
25 views

How to clean up tables that don't belong into mysql/mysql database?

It seems that a *.sql file I recently imported had use mysql instead of use dbname. Now the mysql database contains a large number of tables which don't belong there. Is there an easy/efficient way ...
0
votes
1answer
32 views

MariaDB ignores binlog_format

I'm trying to make moodle work on my system, so i need to change bin_log format to MIXED or ROW. But, i'm having a lot of problems doing that. Any changes i try to do don't work; MariaDB ...
0
votes
0answers
28 views

Copy data from one table to another

I have a table named cameraInfo and another named macTable. Both tables have a column for mac address and password. Not all the mac address in macTable are in the cameraInfo table. However, if the mac ...
0
votes
1answer
22 views

MySQL connection timeouts

Apparently, a former DB administrator configured our main DB server (located in germany) so that only from certain IPs would be accessable. Now we just added a new host (which needs to access that ...
0
votes
3answers
31 views

Encrypt and keep sensitive data in MySQL

I have a process that parses a JSON API and loads the data into MySQL database. I have some sensitive data that should be encrypted, such as firstname, lastname, email, zip, address, credit card ...
0
votes
1answer
76 views

How do I write this query?

I have a table that looks like this: CREATE TABLE `message` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `grp_id` int(10) unsigned NOT NULL, ...
1
vote
1answer
50 views

Displaying Distinct Max Value autonumber rows from tables in MySql [duplicate]

i am trying to execute the distinct records from mysql table having max value of it sno column. I have tried the following query but having no luck. SELECT DISTINCT p.sno AS sno, ...
0
votes
1answer
20 views

6 datacenters running the same mysql database, replication layout design question

So each of the datacenters is around the conus. The Master slave replication route seems like my best option here. I have never done this with more then 1 slave and 1 master. Advice? Links? The ...
0
votes
0answers
30 views

Error Code: 1098. Can't generate a unique log-filename /data/mysql-binlog/relay-bin.(1-999)

After starting MySQL 5.5 I receive the error message: Error Code: 1098. Can't generate a unique log-filename /data/mysql-binlog/relay-bin.(1-999) Can anyone point me out what the problem is? The ...
0
votes
0answers
41 views

Column Editing is Disabled. How to Enable?

I am using MySQL Workbench, in order to design an ERD (Entity Relation Diagram). I can make use of indexes, triggers, etc. but for some reason the columns tab, which allows me to add data or just ...
0
votes
1answer
33 views

Mysql multi-master active-active replication : replication doesn't happen for all slaves

so i created a multi-master replication with bellow servers two for master and two for slave. hostname : master-1 ip : 192.168.120.107 hostname : master-2 ip : 192.168.120.111 hostname : slave-1 ip ...
0
votes
0answers
43 views

Configuring MySQL Cluster

I'm having trouble in configuring my data nodes. My setup is 1 management node, 1 SQL node and 2 data nodes. I'm running mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64 on my cluster. My question is do ...