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

0
votes
1answer
13 views

SQL design for hierarchical structure where parent unit should not exist without subunits [closed]

I'm new to SQL so I wasn't sure what to even search for this besides looking at hierarchical structures which don't seem to be exactly my use case. I'm building an app where a Post contains multiple ...
0
votes
1answer
31 views

MySQL Trigger to Update a Row in a Separate Table via Joins if Only One Row is Updated

I am trying to create a trigger so that when a column in a table called bookings is updated it will update a a calculated price value in table departments. I currently have my tables set up as so ...
2
votes
2answers
57 views

Multi level hierarchy relationship

My table design is users (id, username..., parent_id) Data is id username parent_id ------------------------------- 1 | admin | null 2 | reseller 1 | 1 3 | client 1 | 1 4 ...
2
votes
2answers
55 views

Update multiple rows with different columns

I am trying to perform a bulk MySQL update where I know only certain columns need to be updated for each row, e.g. row A only "name" changed to "Sue", row B "name" and "address" changed to "Joe" and ...
0
votes
1answer
31 views

Cast as INT(11) instead of DECIMAL 11,0 in a view

I have a view with a column defined as: IFNULL(priority.id, 2) priority_id, ... where priority.id has type INT(11), however the resulting view column has type: DECIMAL(11,0) How do I get the ...
2
votes
2answers
29 views

List all articles with their amounts at a certain date+time

I've got a table with articles, and their stock amount changes: TABLE stock_history: +------------+---------------------+--------+ | article_id | amount_at | amount | ...
0
votes
2answers
28 views

MySQL Binary Installation (mysql_install_db)

While installing from binaries. The below always gives error when we execute from 'scripts' directory [mysql@hostname ~]$ cd /mysql/product/5.6.12/scripts/ [mysql@hostname ~]$ . mysql_install_db ...
5
votes
1answer
19 views

Execute query cache on startup - Reasonable?

I have a rather database intensive application for a server which is intended to be turned off and on throughout the day. The issue comes at first use of the application after startup; it is slower ...
4
votes
1answer
65 views

Subquery can't find column from superquery's join

I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" ...
0
votes
1answer
33 views

How to import a single table in Windows environment

I am running Windows 7 with xampp. I can easily import a database with the following command: mysql -u user -p database < backup.sql The problem is that I have a database around 8.5 GB, which ...
0
votes
1answer
28 views

Master Slave Error user specified as a definer ('dbadmin'@'%') does not exist

I setup mysql (version 5.5) master slave replication and when test it with root user from phpmyadmin data succesfully transferd. When start application slave have an error: mysql> show slave ...
0
votes
1answer
38 views

Query other database's INFORMATION_SCHEMA tables

I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as ...
4
votes
1answer
33 views

Is there an alternative to “Create or replace procedure” in MySQL?

Is there an version of "create or replace procedure" for MySQL? I can't seem to do this or script the dropping of the procedure if exists before recompiling without getting an error message that the ...
0
votes
2answers
25 views

MySQL Workbench - store a query to apply to various schemas

We have multiple identical schemas. How can I store a query and easily apply it to any of multiple schemas? Bonus points for a drag & drop or point & click method of executing the query. My ...
1
vote
1answer
7 views

Linked tables change during dump

Having difficulty cloning a live DB. In the table structure i have a number of tables that extend are linked from one another, they are quite big and the names means the tables aren't very close in ...
0
votes
2answers
47 views

Un-optimize a MySQL database?

I want to run ANALYZE TABLE and OPTIMIZE TABLE on a large production MySQL database (v 5.0) to increase performance. But as this would lock tables causing downtime for an unknown amount of time, I ...
3
votes
0answers
74 views

MySQL process stuck on “cleaning up”, locking table forever

It was suggested I post here as opposed to StackOverflow. I'm new to debugging mysql/innodb issues so here is the best I have: I'm trying to run an integration test so these results are near ...
0
votes
1answer
35 views

Joining 4 tables of which one is unrelated

I have 4 tables as follows. I want to find a wholesum resultset, limited to 25 records, in the following way: Users from Table1 based on a condition And those Users should not be in Table2 [Non ...
0
votes
0answers
24 views

How can i make month columns in MySQL? [duplicate]

I want to return table like that(column for each month) : Jan-Feb -Mar -Apr-May-Jun-Jul-Aug-Sep-Oct-Nov-Dec My query : SELECT CASE WHEN month(date) = 1 then 'January' WHEN ...
1
vote
1answer
47 views

What are the cons of implementing faceted search with WHEREs and JOINs?

I'm implementing a filter search system (I understood today that is called faceted search) for an ecommerce website. The fron end looks something like this: Country: [Select a country] Categories: ...
-1
votes
1answer
107 views

Grouping by each user's statuses or activity datetime

I’ve been trying to figure out in what best way I can implement a stream of activities, that groups a user’s activities that were done on a specific date where the time hh:mm:ss doesn’t differ much ...
0
votes
0answers
36 views

mysqldump: restored by mistake with no backup

While trying to take a mysqldump on a database, we have by mistake run a restore command mysql -u -p abc.sql <abc.sql. This has resulted in the abc database going back to a state 5 days back when ...
0
votes
1answer
37 views

Shared entities across multiple databases

I'm currently working on an application with a master database and individual user databases where all user input data resides. These individual databases are exactly the same. master connects the ...
0
votes
0answers
30 views

Individual inserts wait for several seconds for primary key lock (inno DB)

my application inserts about 2,000 records in a table per hour, at some time the database started to delay this inserts throwing a "lock time wait" exception, after some research I got this output ...
1
vote
1answer
38 views

If I use varaibles in the order by clause, is there any guarantee the result will be sorted?

I have a table like this: // Table1 +----+--------------------+------+------------------+---------+-----------+ | Id | QuestionOrAnswer | Type | AcceptedAnswerId | related | timestamp | ...
0
votes
1answer
38 views

MySQL optimizer difference between 5.6.27 and 5.5

We've just updated the MySQL version of the database of one of our clients from the 5.5 to the 5.6, everything is working fine except one simple query which is causing us troubles. Having the ...
0
votes
0answers
17 views

Get records with specific group name from table A and see if any exist in table B, if it does then add matching records in Table B to group

Let me start off by saying I am not a DB admin or a DB pro, I work with SQL as a php developer but that's about it. So I hope I can explain this properly and I hope one of you DB guys/gals can help. ...
0
votes
1answer
47 views

MySQL Using a trigger to update quantity on another table if part is used

I am new to triggers but want to know how I can use one in my database. I have created a table for a maintenance log, a parts inventory, and a parts_used junction table. When maintenance is carried ...
4
votes
1answer
48 views

Quite large (400k) mysql database design for multiple users

I'm seeking advice of experienced admins. I'm working on a website, where you solve word anagrams. If it solved it should never be displayed again. Wordbase contains ~400k entries. What would be the ...
-1
votes
0answers
24 views

MySQL: Is there a way to transform string formats? [migrated]

EDIT: Please don't focus on the value it self. I'm well aware of that I should save such a value as float or decimal. It's not about saving integer as string etc. It's about changing the format of a ...
0
votes
2answers
54 views

Document Store & Relational DB Hybrid. Exists? Possible?

Does anyone know of any product or open source project that attempts to strap together Mongo DB and MySQL for instance? I hear a lot about how relational DBs being ACID compliant makes it nearly ...
1
vote
1answer
18 views

AWS Webapp tutorial: how to choose source in database security group?

I am following the AWS tutorial to create a Webapp here: http://docs.aws.amazon.com/gettingstarted/latest/wah-linux/getting-started-create-rds.html I am stuck at the part 'Create a Security Group for ...
1
vote
1answer
66 views

WordPress with massive MySQL database slow. Would MySQL replication help?

Database Administration is usually not my strength, but one friend asked me if I can help him with his project. He is running a WordPress (MySQL) website with WooCommerce. The postmeta table stores ...
0
votes
1answer
13 views

MySQL 5.7.9 How to get more explicit error message?

I am running MySQL 5.7.9 on a Windows machine, upgraded recently from a 5.6.17. I used to get explicit error message with my previous versio, but somehow since the upgrade I get very mysterious error ...
3
votes
1answer
50 views

Creating a query to pull very specific information using four tables

I have four tables, with a structure like this: testData This table holds all test data for every user. Each row associates one response for a question, divided up by test. A user can have many ...
2
votes
2answers
74 views

SELECT with WHERE and CASE

I do not really know if this is possible to do this but I will expose my problem. I have two tables cases and progress cases +----------+--------------+---------------------+---------+------+ | ...
0
votes
1answer
38 views

Best to isolate the database in multiple instances?

I read on a forum about the use of database isolated environments are neccessary for any large project that handles "big" data. For instance I will be using the following table for ALL blogs: ...
0
votes
2answers
35 views

mysql stops working after import

I've been running into problems whenever I'm importing a .sql file into my database running locally. After the import, no matter how large the DB was (last one was only 800kb), I'm signed out of ...
3
votes
2answers
28 views

import mysql schema data folder instead of from a dump

current state I have prod and dev mySQL databases. To create the dev database, I used the various schema backups generated from production and imported them like so: mysql -u my_user -p target_db ...
3
votes
0answers
49 views

Why does replication with SSL fail on my MySQL database in AWS RDS?

I'm trying to replicate from AWS RDS to my own server. It works without SSL. Whenever I include the SSL property to the slave, it breaks with this error: error connecting to master ...
1
vote
1answer
67 views

Database Design and structure

I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, ...
1
vote
0answers
24 views

PDOException: SQLSTATE[HY000]: General error: 23 Out of resources when opening file xxxx.MYD' (Errcode: 24) [migrated]

I am getting this error message when trying to access a php page: PDOException: SQLSTATE[HY000]: General error: 23 Out of resources when opening file xxxx.MYD' (Errcode: 24) How can I solve this ...
1
vote
1answer
31 views

Slave stopped : Could not parse relay log event entry

My Slave machine stopped somehow, and when I ran START SLAVE to start replication, I got following error, SLAVE 1 !!! > show slave status \G *************************** 1. row ...
0
votes
0answers
26 views

cannot install_db after deleting /var/db/mysql

I am trying to reset mysql5.6-server back to its installation defaults and blowout/nuke all the databases to start off fresh and clean. [root@yoda:~] # rm -fr /var/db/mysql [root@yoda:~] # mkdir ...
3
votes
0answers
41 views

mysqldump with --skip-grant-tables

It appears the files in my /var/lib/mysql/mysql are borked, and I can only start mysqld with --skip-grant-tables. Now I want to export all tables with mysqldump --all-databases, wipe and reinstall ...
2
votes
0answers
46 views

MySQL: Is the read committed isolation level safe with mixed binary log format?

When using the MIXED format with the READ COMMITTED isolation level, am I safe? The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed) If you use ...
0
votes
0answers
26 views

DB table design for attributes with multiple entries (eg: Person -> Social Media)

I am trying to decide the structure of a DB table to store Employee data. I am bit confused on how to design attributes such as Address, Social Media etc. There should be provision for multiple Social ...
0
votes
0answers
19 views

Can't log in to MariaDB with --password=<password> but not with user-interactive login?

I can log into my MariaDB server when I put the password directly in the bash command, but not when I'm doing the user-interactive login (which is of course safer). root@vm# mysql -u root --password ...
1
vote
0answers
23 views

How to upload a large file (above 15 MB) in MYSql database, using Codeigniter [closed]

How to upload a large file (above 15 MB) in MYSql database, using Codeigniter. When I try to upload a file of size 15MB, I get this error Fatal error: Allowed memory size of 67108864 bytes ...
4
votes
2answers
130 views

slow select query on datetime column

I have a table in MySQL which has approximately 10 million records, and now a query which used to take micro seconds now takes more that 8 seconds to execute! Here is my table structure: CREATE TABLE ...