MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
35 views

Mysql - Examine last query run by thread in sleep state

I am seeing some threads in the "sleep" state for longer than normal. Normal maybe being 1-3 seconds with the abnormal being up to 60 seconds or longer. It makes sense that this is an application ...
2
votes
4answers
100 views

Mysql Order By affecting a Query with column alias, group by, and having

Description: I have a query with column alias, group by and having. It's getting altered when I add an order by. This strange behavior is only happening in 5.6.16 and 5.6.21 (which I was using in ...
1
vote
1answer
31 views

Getting Data Type ID in MySQL

So, In SQL, We have the following query to find the system data type ID of columns in SQL Server; select name, system_type_id from sys.columns where object_id = OBJECT_ID(N'sometable') It gives the ...
0
votes
1answer
56 views

mysql Lock wait timeout exceeded; try restarting transaction

we are running java application, running for ages, back end is db is MySQL, recently updated to mysql 5.6. Everything was running fine, past week started getting this error Lock wait timeout ...
0
votes
2answers
37 views

Table partition should not be selected

I am looking to partition a table (nearly 30M rows) by date ranges (mysql version : 5.1.73) CREATE TABLE `TABLE_X` ( `name_x` varchar(50) NOT NULL, `type_x` varchar(5) NOT NULL, `status_x` ...
1
vote
2answers
26 views

mysql copying to tmp table duration

I have a web site which started to have performance issues because of a mysql query which is executed for each request. I have the following table structure. Post table (125000+ records) id: ...
0
votes
1answer
50 views

Does AWS Aurora suffer from the same limitations that MySQL suffer from? [closed]

Specifically, MySQL has a limitations max row size as 64kB. I would like to know if Amazon Aurora, which is supposedly a drop in replacement for MySQL suffer the same limitation.
1
vote
2answers
28 views

Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?

I have a large (3M rows) InnoDB table in MySQL 5.6.21 in production and I want to add some columns. From the MySQL Documentation on Overview of Online DDL I understand that, even if this is an ...
0
votes
2answers
42 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 ...
0
votes
0answers
37 views

error 1067 process terminated unexpectedly

I am facing the following error while creating the Mysql instance from command line in Windows server 2012. Steps i follwed for creating a new Mysql Instance as a window service: I have stopped the ...
0
votes
1answer
35 views

Display the choices even if no rows found in associated table

I have the following 3 tables for a small voting/polling feature: CREATE TABLE IF NOT EXISTS `options` ( `option_id` tinyint(4) unsigned NOT NULL, `poll_id` mediumint(8) unsigned NOT NULL, ...
0
votes
1answer
14 views

'mysql' database in fresh MySQL installation

I installed MySQL database and according to show databases command I have a database named 'mysql'. Is this database used for holding some internal values for MySQL? In addition, is it possible to ...
0
votes
0answers
23 views

where does MySQL store data which is inserted into tables?

I have a small table named animals in database named test: mysql> select * from animals; +----+------------+-----------+ | ID | species | location | +----+------------+-----------+ | 1 | ...
1
vote
0answers
21 views

Correct sentence and paragraph casing [migrated]

I have a database full of descriptions in all lower case. This doesn't look very nice. I am trying to figure out a way to correct the casing, ie., capitalize the first letter after every period, and ...
1
vote
1answer
86 views

Table with more than 100 columns

I have a table in my MySQL database which contains data about XRF analyses. There are about 50 chemical elements and for each element there are two values to be stored (Area and Count). Users need ...
0
votes
1answer
50 views

Database MySQL - Transpoing Table from one form to other

SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(function_NAME = ', function_NAME, ',budget, NULL)) AS ', function_NAME)) INTO @sql FROM country_report dr ...
0
votes
1answer
25 views

Intermittent “access denied” (1045) message from MySQL

I'm importing a fairly large (100GB+) database from MySQL to PostgreSQL using pgloader. After a seemingly random amount of time the process dies with the following error: An unhandled error condition ...
0
votes
0answers
21 views

Mysql is not starting while using xampp.

Mysql is not working when I try to view my site on locahost. I keep getting 'Error: MySQL shutdown unexpectedly.' The Mysql log says: 2015-05-06 19:39:13 5268 [Note] Plugin 'FEDERATED' is disabled. ...
1
vote
1answer
35 views

Getting the count of same value in consecutive rows that are ordered by different field

I have a table with the following structure finals(ID, name, result) CREATE TABLE `finals` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `result` char(4) ...
0
votes
1answer
26 views

improve database design for monitoring application

I need assistance with my DB design, I have a monitoring script that produce the following data every minute , and produce stats about virtual machine performance. Array ( [timestamp] => ...
0
votes
1answer
33 views

OR Operation in JOINS SQL(MYSQL)

I am using MYSQL SELECT m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate, o.msNumber , o.description,o.ics,o.edition FROM mystands_Proposal m INNER join ...
0
votes
1answer
35 views

Scale MySQL trigger for thousands of inserts per seconds

We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256) A row ...
0
votes
0answers
8 views

Changing Numbers from standard form without clicking every cell- Excel [migrated]

I have imported a csv file from MySQL, documenting part numbers and descriptions. Some of these part numbers have values like 1234567890987654321, which is then shortened by excel to 1.23e18. Problem ...
1
vote
1answer
45 views

Why MySQL SELECT performance depends on table size? [closed]

I've got two identical tables with the following structure: CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT ...
-1
votes
1answer
28 views

Set up MySQL server database [closed]

I want to set up MySQL server database for my office on my windows 8 pc, but i dont know exactly which products to use. Can anybody help me with it. I want to set the database such as my pc would be ...
0
votes
1answer
56 views

Get data faster from databasse

My site having a more than 1.5 million records and my question is the how may i fetch record fast. i am spending so much time to find the way for it finally find out the one way which is to apply the ...
0
votes
1answer
28 views

Filling not applicable fields with N/A rather than leaving them empty or Null [duplicate]

I have a database of few million records, many of the fields (call them Ec1 to EcN) have either Null or empty string in them. When I looked at them more closely, I found that for many records, several ...
0
votes
1answer
56 views

unable to add index to column, duplicate entry

I have a table that collects virtual machines usage metrics, I'm trying to add an index to timestamp column, to speed up queries, when selecting time, this is a sample of the table I have, it has ...
0
votes
1answer
37 views

InnoDB Index Doesn't Fit in Memory

I am using InnoDB tables in my database and total index_size is 250 GB and my data size is 500 GB. Server has 128 GB RAM, and innodb_buffer_pool_size is set to 64 GB. Data size is increasing every day ...
2
votes
3answers
63 views

Track entity updates (by who and when)

I need some advice on how to store information about who created/updated an "entity". I keep going back and forth between Option1, 2 and 3 (see below): Background This is a legacy system and there ...
0
votes
3answers
78 views

Adding order by to a query is slowing down the query even after Indexing

While joining columns with huge data even after indexing the column order by is slowing down the query. Without order by query is executing within 2 secs. But with order by query is taking 145 secs. ...
1
vote
2answers
27 views

How to define partition according to Date?

I am currently using MySQL 5.6. In my database, I have a log table called paramlog_ems. It has 2M rows I want like to partition this log table according to Date. Here is the table structure: ...
1
vote
1answer
60 views

Why is it that when I drop a field in table it says `#1050 - Table already exist`

I want to drop some fields of a table but it returns an error. the query is.. ALTER TABLE `tbl_incoming_bub` DROP `approved_date`, DROP `approved_by`, DROP `incoming_date`, DROP `incoming_by`, DROP ...
0
votes
1answer
49 views

Database Design For Social Network apps

I'm going to build a social network app. Each user will have photos, videos, details, posts etc. My question is when a new user registers, should I generate many new tables for that user or have one ...
1
vote
2answers
25 views

Inconsistencies with ADD CONSTRAINT index_name in MySQL

I created this example table CREATE DATABASE foobar; USE foobar; CREATE TABLE foos ( id INT(11) AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, ...
0
votes
0answers
19 views

Adding a constraint to both tables independantly in join [migrated]

I am trying to create a SELECT statement from two different tables, where I use to send messages, I don't want to send a message to someone for 1 wk after they join, however if I have already sent ...
0
votes
0answers
29 views

ERROR 1644 (HY000) at line 1852: Unexpected content found in the performance_schema database

I was upgrading MySQL from 5.1 to MySQL 5.6 on a Linux server installed with RHEL6. I already have 5.1 installed and running with about 300GB of database data, so I want to do a in-place upgrade to ...
0
votes
1answer
29 views

How to store user defined scoring formulas?

First post, so apologize if I have not chosen the best spot for it: If I had table of values, let's say corresponding to a question number and its corresponding answer, what is the correct way to ...
1
vote
0answers
28 views

How to send MySQL query results to outfile and standard output at the same time?

I "normally" query MySQL with SELECT col1, col2 FROM tablename LIMIT 10; and sometimes instead send the output to a file with SELECT col1, col2 FROM tablename LIMIT 10 INTO OUTFILE ...
1
vote
2answers
29 views

Why is restoration from binary logs so slow?

We have a MySQL 5.5 server with binary logging enabled and a backup script that collects the binary logs and, once a day, generates a complete database dump with mysqldump. In testing database ...
1
vote
1answer
78 views

Why SELECT is so slow

I have the following table CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT NULL, w4 int(11) NOT NULL, ...
0
votes
1answer
20 views

Backup/Restore Users/Passwords/Privileges

I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how ...
1
vote
1answer
47 views

Why Update trigger makes Insertion slow in mysql?

I had create one table, that Table contain Log of meters. Name of that table is "paramgraph_ems" This table contain 1M rows, and insertion per minute is 350 rows. 10 Min takes for inserting 3500 rows. ...
0
votes
1answer
27 views

In MySQL why is the first batch executed through client-side prepared statement slower?

I have around a million lines to be inserted into a MySQL (InnoDB) database, so to speed things up I turned to batch/multi-line insert. To be specific, I connect to the database (I'm using Java, so ...
0
votes
0answers
21 views

Cannot change datadir for mysqld

I'm currently using Windows 8.1 MySQL 5.6 community server I once had my own mysql server, and all the data is currently stored in E:\db\data, which was the data directory for previous db server. ...
0
votes
0answers
8 views

Single INSERT query for multiple records using Prepared statement [migrated]

Here's a fairly basic PHP PDO snippet that accesses a MySQL table named "Users" and inserts rows of data to it using a prepared statement and multiple bindings: $conn = new ...
0
votes
1answer
37 views

Logic Value of Assignment Expression

I'm doing the leetcode SQL problems and kind of confused with the code below: select distinct Num from ( select Num, case when @prevNum = Num then @count := @count + 1 ...
0
votes
0answers
20 views

How to accept apostrophe in a form field for MySQL [migrated]

I have a form that accepts a few names and phone numbers and posts them to a PHP script which then inserts those values into a MySQL table. The code (PHP) is pretty straightforward as you can see ...
2
votes
1answer
20 views

How do I change the config path for mysqld?

I want to change configuration file path from C:\ to E:\, but I can't find the way to do this. Alternatively, is there a way to start a new instance with the config file I want? I've done something ...
1
vote
1answer
65 views

Group by maximum consecutive row

I have data like the following: id | src_ip | dst_ip | port | ----|----------------|--------------|------| 256 | 192.168.61.200 | 10.75.64.222 | 80 | 257 | 192.168.61.200 | ...