MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
23 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
16 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 ...
0
votes
2answers
8 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
3answers
18 views

Adding a constraint to both tables independantly in join

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
10 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
0answers
12 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
20 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
19 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
55 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
17 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
27 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
17 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
18 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
25 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
16 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
53 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
20 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
22 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
23 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
66 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? [on hold]

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
23 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
17 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
13 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 ...
0
votes
0answers
22 views

Most efficient way to store a large, changing number of properties

I have an entity with a large number of boolean properties. It currently has ~100 properties It will periodically get new properties The number of properties will grow to 400+ There will be ...
1
vote
1answer
62 views

best design for my data storage

I am looking for advice on storing rows of data. What I have a bunch of users (id), and data about their activities. Each user can perform a type of activity (type_id). And then about 20 columns ...
0
votes
0answers
7 views

MySQL - Update vs. Replace Into — why is Update non-parallel, and Replace Into allows multi-threads?

In trying to optimize a query that does (Insert ... on duplicate key update) .... someone suggested I do "Replace Into" to optimize performance. I'm not 100% certain the difference other than ... ...
0
votes
1answer
21 views

Shell script for backup binlog files for every 8 hours in MySQL

I'm writing a shell script for point in time recovery for MySQL Instance. Our requirement is backing up Binlog for every 8 Hours (Daily 3 times) using mysqlbinlog and it should backup binlog starting ...
0
votes
1answer
28 views

'USE db_name' takes too long to change when login remotely

I gave SELECT privilege to a user on a particular database, Now whenever I try to connect remotely $ mysql -h ip-of-remote -u username -p with that user credentials and try to change the database ...
0
votes
1answer
10 views

Update Column in table on midnight daily

I am designing a simple 1 table database in mySQL that will have two columns. One column will be the name of a specific restaurant on the webpage I will be using this database with. The second column ...
1
vote
2answers
22 views

Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's ...
0
votes
1answer
38 views

How do I perform a query on a dynamic list of tables?

I have a database named as ( conntrack ) with unknown amount of tables.I have a static table "tabidx" which has one column "date". The following table is static and its name is tabidx which provide ...
0
votes
2answers
26 views

Get a user's rank from an equation, and then store it in the table

I'm working on a project for my database class, and I have a table called "accounts". It looks like What I want to do is have a stored procedure where it calculates the new ranks for each account. ...
0
votes
0answers
26 views

How to avoid data fragmentation in a MySQL table

We have a table in MySQL that contains several years worth of hourly time series data. A few thousand rows get added to the table every day. We're using the InnoDB storage engine. At this point we'd ...
0
votes
2answers
33 views

MySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster?

I'm running an ETL process and streaming data into a MySQL table. Now it is being written over a web connection (fairly fast one) -- so that can be a bottleneck. Anyway, it's a basic insert/ update ...
0
votes
0answers
11 views

Can anyone explain TokuDBs seemingly large amount of data_free for an append only table?

I have a large table I've been maintaining replicating in parallel in innodb in one slave and tokudb in another. I'm seeing promising results in expected compression ratios but am noticing something ...
0
votes
0answers
40 views

Best way to restore 400GB+ worth of data in MySQL 5.6

So I'm trying to figure what the best/quickest way would be to refresh a massive amount of data. We're wanting to copy 3 months worth of data from a replication server to a QA server for product ...
3
votes
3answers
46 views

How to generate documentation from stored procedures? [on hold]

I'm working with a new set of tables and an increasing list of stored procedures. Is there a way to generate documentation out of the comments on tables and stored procedures, like autodoc or ...
-1
votes
1answer
21 views

SQL Error 1054 Unknown column in 'where clause' [on hold]

I've created an database with 5 tables and each of the tables have various rows and columns as data. When I try to select some of that data, I'm getting that error in the title and I have no idea how ...
1
vote
1answer
19 views

Updating databases that user's can manipulate

I have a product that includes a MySQL database with some defined countries (very few, not all of them). The user has some control over this table - they can add / edit / delete countries. When we ...
4
votes
6answers
1k views

How to find the nth highest salary in SQL?

I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)? EmpID Salary 1 90000 2 80000 3 54000 4 ...