MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
5 views

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

I have a table finals(ID, name, result) CREATE TABLE `finals` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `result` char(4) DEFAULT NULL, PRIMARY KEY ...
0
votes
1answer
16 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
20 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
0answers
15 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 ...
-2
votes
0answers
10 views

Mysql and php query is not working [on hold]

$servername = "localhost"; $username = "root"; $password = ""; $dbname = "BBAHOTEL"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ...
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
35 views

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

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 ...
0
votes
0answers
24 views

How to take backup of rows which are going to be deleted and restore it? (Mysql) [on hold]

I want to backup the rows which are going to be deleted for that I want to use a trigger that stores those rows somewhere in another temporary table.. For that following things I am going to need ...
-1
votes
1answer
24 views

Set up MySQL server database [on hold]

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
44 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
20 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
40 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
23 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
52 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
57 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. ...
0
votes
0answers
28 views

Deadlock occur during multiple insert on Single database? [on hold]

I have multiple windows services thread that insert values into database. But when parallel insertion happens it gives error After show innodb engine status gives Deadlock issue.
1
vote
2answers
24 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
56 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
34 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
21 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
13 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
18 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
23 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
22 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
18 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
43 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
23 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
20 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
26 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
17 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
58 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 | ...
0
votes
0answers
6 views

MySQL Queries in UDF

I would like to create a MySQL UDF (i.e. User Defined Function) that embeds a query; for instance, suppose the UDF is named foobar: mysql> SELECT foobar(); When foobar function receives the ...
1
vote
1answer
23 views

MySQL/InnoDB - Clustering Index with UUID

In MS SQL Server it is possible to cluster an index on a non-primary, non-unique key. For example, if I wanted to create a clustered index on the insert date to prevent page splits and fragmentation ...
2
votes
2answers
23 views

Mysql slow slave

Background: We have our main databases at a data centre which are all working fine. I wanted to add a slave server at our office for backup purposes. I have copied all of the data from an existing ...
2
votes
1answer
16 views

Too many mysql instances. How to kill?

I should be ashamed of myself, for asking, I know. Check out this screenshot attached. My server, which is indeed not seeing HUGE amounts of traffic, should not be this overloaded. Mysql is having ...
0
votes
0answers
12 views

How to tell when a stored procedure is scheduled in MySQL [on hold]

We have a stored proc that runs daily but I'm trying to figure out when and how it runs. There's nothing scheduled in cron and when I do a show events nothing comes up. I even checked select * from ...
1
vote
2answers
32 views

Mysql causing high load average with large amount of server resources left

I've have 2 servers running a application and the server that runs the mysql sgbd is with the load average around 3 times more than the webserver. I think that is something wrong because i have a lot ...
1
vote
2answers
71 views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
1
vote
1answer
24 views

How do you grant permissions to a MySQL user using a shell script with variables?

I'm trying to create a provisioning script for Scotch Box (Vagrant) that will populate a database with seed content when I fire up the server. When I name the actual user, database, password, and ...
1
vote
1answer
15 views

MySQL, can't find MYD files on OS X, MAMP

Try as I may I can't find the data files for my MAMP installation! FRM files are here: /Applications/MAMP/db/mysql/database_name/ but, there are no corresponding MYD or MYI files anywhere. The ...
1
vote
1answer
17 views

In slow_log tables, sql_text column showing “throttle: index not used' warning(s) suppressed” instead of exact query

I'm trying to find the slow running queries from slow_log table (as I don't have direct access to server to check slow log file). In sql_text column instead of query it is showing "throttle: index not ...
0
votes
0answers
7 views

table with reference to 2 other tables for ORM [migrated]

I need to have a comment table which references 2 other tables, posts, and article. 3 in total including the user_id. Comment ------- comment_id user_id article_id post_id Is this possible? I ...
0
votes
1answer
33 views

What is a good size to accommodate a single-use database for a small application? [closed]

I'm looking to provision out arbitrarily many sandboxed MySQL databases for anyone to use, similar to how SQLFiddle does, but permanent and with API keys (one database per key), which would allow ...
1
vote
1answer
27 views

Correct 'lines terminated by' for query

I am trying to query two tables of data and send them to a csv file for upload to a website. However i am having trouble getting the formatting to play ball. I want headings to separate into columns ...
0
votes
0answers
19 views

SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails

I am trying to install Zabbix by following this guide. I ran into the following problems when trying to run data.sql after running scheme.sql successfully mysql -D zabbix -uzabbix -pPassword < ...
0
votes
0answers
19 views

Update MySQL column from column in another table

I have a table, products2013, with column upload_id which I would to contain the value upload_new_name from correlating table uploads (also containing upload_id). I tried putting together the follow ...