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

-3
votes
1answer
50 views

Get user rankings based on points from database

I've created a website where members collect points to get a reward at the end of the contest. I want to create a page that shows the top 10 members who have the most points. I've tried a few pieces ...
1
vote
1answer
26 views

How to join multiple results into one row with dynamic columns? [duplicate]

Foreword - I don't know how to formally describe the situation in a single question so the below is as verbose as I can make it... Taking wordpress as an example, there's the "wp_users" table which ...
1
vote
0answers
28 views

Dynamic table name in Insert query

I have asked a question about dynamic table name in a prepared statement here , but I want to know if Mysql server support dynamic table name like this : SET @tbl = 'Dawin'; INSERT INTO @tbl (col1,...
3
votes
0answers
76 views

Database design for auto-incrementing, grouped, unique hashes?

I've come up with a (hopefully) better way to save unique company data, which now consists of unique hashes for each entity. Basically, there are multiple hashes associated with a company: hash1: ABC ...
2
votes
1answer
24 views

MySQL table concatenation - sort of!

I have a db with the following: Name, Team, Score, Points I want to be able to do a query that returns Name, Score, Points for a particular team and another table that returns Points, Score, Name ...
1
vote
0answers
15 views

Getting Beginning Balance inventory of every month for the whole year in MYSQL

Good day, I was trying to create a report of inventory movement rate for the whole year, so I need to get the beginning total of every item in my inventory table every month... my sql table looks ...
2
votes
1answer
23 views

Out of range errors while with insert statements

I'm having an issue regarding every third insert line of the database I'm trying to create. Some background, I have a huge .JSON file and used software (sqlizer) to turn it into input statements, ...
1
vote
0answers
21 views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
1
vote
0answers
8 views

How to clear gcache pages in Percona PXC

I have a 4 node cluster(percona PXC) and one of the nodes have more than 150 gcache disk files each of size ~128 MB. I would like to purge or flush them so that the server user fresh gcache disk file. ...
1
vote
1answer
26 views

Sync two mysql database one in intranet and the other on internet

I'm making the training and placement module for my college. But I'm facing problems to sync the databases, one that will be used by the students and the other that will be used by the HR of a company....
1
vote
0answers
9 views

MySQL NDB cluster maximum data size

According to the limitations outlined at http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-limitations.html, there can only be 48 data nodes. Does that mean, with number of replicas 2, I can ...
3
votes
2answers
78 views

What is wrong with this SQL statement?

I cannot get this statement running on MySQL server: DECLARE @tabl varchar(40) SET @tabl = :uId; SET @location = CONCAT('data_',@tabl); SET @b = 'INSERT INTO' + @location + '(Id, uId , name , address,...
0
votes
0answers
27 views

Advantages of binary installation over the RPM installation [closed]

What are the advantages of binary installation over the RPM installation for MySQL? What's the main difference?
1
vote
1answer
18 views

Multi-table sum of cost breakdown by pricing category

Looking for help with a query to get the sum of the subtotals for each price category from a list of line items, by order. Scratching my head on the group by stuff. There are 3 categories: setup, ...
0
votes
1answer
26 views

Returning data based on if another column in another table is NULL

I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been ...
1
vote
0answers
19 views

How to convert MySQL master-slave to master-master

I've searched around, but can't find the exact details to point me in the proper direction. I currently have 2 MySQL instances under 5.5.41 running as a master-slave setup. I followed these ...
1
vote
0answers
43 views

MySQL Load Balancing what do i need Replication or Cluster?

So My MySQL database server is reaching it's limits with the number of queries selects, updates, inserts etc it can concurrently take resulting in high CPU usage more than what a single machine can ...
1
vote
0answers
54 views

Is there a faster way of importing 8m+ rows?

I'm creating a new feature on my website in order to provide player locations (my website is for a multiplayer gaming service), and am going to use a simple IP lookup in my database. For this I need ...
0
votes
0answers
24 views

count rows that has same values from 2 different tables mysql php [closed]

Let's say that I have 2 tables, one of them is called table1 and the second table is called table2. in each table i have 6 columns that has the same name. Table1: Columns: one|two|three|four|five|...
4
votes
1answer
82 views
+50

MySQL locking in Duplicate Key Error

From the docs: If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to ...
1
vote
0answers
24 views

MySQL Supported versions of MySQL Enterprise TDE

We are bidding for a project involving developing a portal using Liferay, which requires that we use MySQL version 5.5 which is the current compliant version of MySQL with Liferay. We need also use ...
1
vote
0answers
30 views

Can't drop table after creating table with wrong engine

I'm trying to drop a table containing several hundred thousand column-based records. Normally when creating the database I use a column-based engine (infinidb) but in this case I forgot to include the ...
1
vote
1answer
69 views

How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL

I need to retrieve data from several tables. For now I am getting maximum of them from SELECT disc.id, users.user_login as 'last_active_user', disc.discussion_sub, list....
-3
votes
0answers
16 views

How to write a shell script [closed]

I want to write an alert shell script. It needs to fetch the data from table a. Upon status 1 it should fetch the data from table b. There we have the contact details. It should send the alert based ...
-1
votes
1answer
32 views

Server Specifications for Web Application [closed]

I am trying to select the configuration for a web application built in PHP 5.5 and MySQL 5.1. I will also be deploying PHPmyAdmin for the MySQL database. Both the web application and the database will ...
0
votes
0answers
26 views

Are read replicas affected by heavy writtings in master?

This is my problem: Sometimes one long lasting query (>60 secs, and I have a lot of those) which is always a SELECT makes my whole application stops working properly up until the query is solved. ...
0
votes
1answer
18 views

Do dormant databases impact server performance?

On our web server, my team has been creating new databases in MySQL to manage backups. They simply create new database_DATE and copy over our live site. Which made me think today... Can having a ...
0
votes
0answers
24 views

Can InnoDB use a clustered index as a covering index?

The scenario I have in mind is a query like: SELECT id FROM table WHERE id = ? or equivalently, SELECT 1 FROM table WHERE id = ? So here's some background: In a B+ tree (which InnoDB uses as ...
0
votes
1answer
40 views

MySql How to rollback my database to a certain point in time the fastest way?

I'm currently working in a testing database and we are not cleaning up after test because of our current configuration and we probably aren't changing that configuration soon, we are currently using a ...
0
votes
1answer
20 views

MySQL issue - startup

I have Percona Server 5.6 running in linux platform.. recently I faced an issue where mysql instance was continuously restarting for no reason.. mysql logs had this error: [ERROR] InnoDB: File ./...
1
vote
1answer
20 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
0
votes
0answers
30 views

Backup /www/ directory and all mysql databases

I'm trying to write simple shell script which will zip all databases + www directory. I don't know what is the problem but I've got wired output. I've got zip with .sql file full of hieroglyphics... ...
0
votes
1answer
62 views

Select distinct values of two columns as single [duplicate]

For starters: yes, I've seen there are lots of questions on combining two/multiple columns - but so far I neither found the answer to my question nor something to get me started trying further (which ...
0
votes
0answers
16 views

failed to retrieve rpm info for /var/lib/mysql/ib_logfile0

SELinux is preventing /usr/sbin/mysqld from open access on the file /var/lib/mysql/ib_logfile0. For complete SELinux messages. run Jun 21 16:35:39 localhost.localdomain python[17438]: SELinux is ...
1
vote
1answer
31 views

Multiple subqueries in columns with joins to return multiple rows

I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there ...
1
vote
1answer
39 views

any better way for data archiving?

I'm in one security company and our history data grows fast, causing data archiving sqls run slower and slower. Say, for the sake of data real-time need, the scripts must run in a high frequency for ...
1
vote
1answer
38 views

mysql performance test

We are planing to test the performance of the new DB server. For that we have the below the plan: get production sql queries replay those sql queries against the different DB I was planing to use ...
3
votes
2answers
52 views
+250

MySQL Online DDL Index Creation

I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is ...
1
vote
0answers
10 views

MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default

What do you guys think about: After each query, I put "Order BY PrimaryKey DESC" VS I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there. Which one will be ...
0
votes
0answers
34 views

Trigger to Get Last Row Value and Set

I have been having a problem with this one. Granted this is the first trigger I have ever made (pretty new to this). I think it's a formatting issue. I have a table called filleradown. I need for ...
0
votes
1answer
48 views

How to SELECT cells from mysql for a HTML table? [duplicate]

I have data for individual cells of a HTML (or excel) table as CREATE TABLE table_cols ( column_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, column_number tinyint(3) unsigned, title varchar(255), ...
2
votes
2answers
86 views

Will hacking away ibdata1 destroy databases with only MyISAM tables?

I made a mistake. I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so. I know that safely shrinking ...
1
vote
0answers
24 views

MySQL dump only has SET commands, no data

I've done an export of a Wordpress database. The MySQL file that was exported only contains the Host, Database name, server version and SET commands. Pastebin: http://pastebin.com/pFgdPDRa Another ...
1
vote
1answer
30 views

How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup?

I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 ...
0
votes
0answers
15 views

Mysql arabic text [closed]

i have MYSQL DB on WAmp server, and a php website, if i enter arabic text from the php website it appears in arabic only at the php website, and appears like this on phpmyadmin هو و انا, and if ...
1
vote
0answers
24 views

MySQL Temporary Tables

I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this ...
-4
votes
1answer
42 views

Insert and Update a sql table at same time is not working [closed]

I am facing an issue with my SQL DB. I have a stored proc which inserts data into a tables [Table1],[Table2],[Table3]. But when I tried to update some data which already exists in those tables at the ...
1
vote
0answers
41 views

MySQL – BLObs Hitting Performance

One of my tables contains seven BLObs. Six of these refer to raw data and are typically 1.0 – 1.5Mb each. The seventh is the result of processing this data and can be nearly equal in size to the sum ...
1
vote
1answer
29 views

how to increase column values by 20 percent using mysql

How do i increase the values in a column with percentage increment using MySQL, for example: ID price 1 500 2 800 3 450 How do i increase the values in price column by 20% ...
3
votes
2answers
34 views

Need a help in Query to check whether Task is completed by other user or not before showing on dashboard

I have 3 tables in database Task, User & assignedTo. Task contains information of Task created by Admin, User is list of User & assignedTo contains information about which task assign to ...