MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
30 views

building structure for employee office relation

I have a table defined like this empid fromdate todate office_ID 1 2012-03-01 2014-04-2 1 1 2014-04-03 ---- 2 2 2012-03-01 ...
0
votes
2answers
28 views

MySQL replication - ibdata1 huge

I'm trying to implement MySQL replication in production after I successfully implemented it on my dev environment. Basically, what I did is export the dump using the following : mysqldump ...
0
votes
1answer
26 views

MySQL Data Synchronisation from different Databases in C#

I am supposed to create a C# solution to compare the data (table, column and row) from two different databases and update the 2nd one as follows: 1. with just the rows that are updated recently ...
1
vote
1answer
134 views

MySQL FULLTEXT search not returning any results

I'm wondering why my fulltext search isn't returning any results. To me this seems pretty straightforward, but alas it's not working. I'm far from a DBA, but looking at the EXPLAINed query, I see ...
0
votes
0answers
27 views

Cannot open table slave_master_info

I have a dabatase working on InnoDB engine Now it is damaged. I can´t open any table, it shows error 1146 Table 'database.tablefromdatabase' doesn't exist I tried innodb_force_recovery with all ...
1
vote
2answers
30 views

Schema to make a Payment History/Viewing History

On some websites I have seen two history of events: Payment History and View/Watched History. I want to have both payment and viewing histories in my app, but I got stuck when I started thinking ...
3
votes
1answer
69 views

Optimizing Join between a very small and a very large table

I have two tables: CREATE TABLE `TBL_REPORT` ( `ID_TBL_REPORT` bigint(20) NOT NULL AUTO_INCREMENT, `INDEX_NAME` varchar(100) NOT NULL, `INDEX_VALUE` varchar(100) NOT NULL, ...
0
votes
2answers
35 views

fixed length table

I only have ever used mysql and storing data on .txt files. Currently I'm working with tick data (I have node.js on my server). My custom setup so far stores a fixed amount of tick data as comma ...
0
votes
1answer
37 views

MySQL - How to replace a given value from multiple columns when using SELECT?

I need to SELECT a set of rows of a given table whose structure looks like - 1º line is the name of columns A01 A02 A03 A04 A05 ... A200 99999 99999 10 753 99999 ...
1
vote
0answers
21 views

Very weird Innodb Corruption Issue

I'll try to keep this to the point. System: OPenVZ VPS, debian 6, 512 MB RAM, 50 GB HDD (25GB free) I have a mysqldump dump file (3GB), that was created when the innodb database was working fine. ...
0
votes
0answers
44 views

MySQL denying access to user with necessary privileges

I have a MySQL server and a user with access to all databases. When this user attempts to connect to the database and execute a query, access is denied. I have ran the following statement (specifics ...
1
vote
1answer
21 views

MySQL: Replicate data within 2 same schemas on 1 mysql server

This might sound a bit crazy, please let me explain the reasoning behind this: we plan to use a real master-slave replication for production we want to use something small'n easy for local ...
0
votes
0answers
25 views

Unique indexes on table with multiple columns

I have a table called organogram: SHOW CREATE TABLE organogram: CREATE TABLE `organogram` ( `organogram_id` int(11) NOT NULL AUTO_INCREMENT, `locatie_id` int(11) NOT NULL, `afdeling_id` int(11) ...
1
vote
0answers
32 views

How WHERE works with column name only?

I have table with varchar column. Some rows are empty, some have content. While preparing SELECT statement, I accidentaly run query like that: SELECT column_x FROM table_x WHERE column_x; It gave ...
0
votes
0answers
28 views

CPU jumps massively and stays high, under very light load

We had two separate RDS instances exhibit the same pattern today. This was both during low load times, one during the lowest load time for that server. They both started at almost the same time. No ...
0
votes
0answers
19 views

Automysqlbackup fix error “Skipping the data of table mysql.event. Specify the --events option explicitly”

I need to fix the mysqldump warning "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.". I found that its because of not specifying whether or not to add ...
2
votes
3answers
42 views

How to synchronize two MySql tables using command line

I have two tables in the same database (MySql) where one has been populated and there are data been added to it frequently. I want to have the data added to the first table to be present in the ...
1
vote
1answer
16 views

Query data from two tables and substitute foreign keys of first table with values of second [closed]

There is data table mysql> select * from data; +----+--------------------------------------+ | id | uid | +----+--------------------------------------+ | 1 | ...
0
votes
1answer
44 views

Hi disk I/O and crash when turning on sync_binlog in MySQL

I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it. When I set global sync_binlog = 1, the server I/O goes very high and after some ...
0
votes
1answer
54 views

MYSQL any way to improve this query's performance?

MYSQL version 5.6.23 CENTOS 7 Hi, I have the following query that is coming from an application that I am hoping to make more efficient from the mysql side of things. I have an index built on ...
0
votes
1answer
31 views

MySQL: Capture every 5 values from column a in Table A to create one row in Table B

I have submissions of a form from a web page records in a database. the table has the following schema submit_time form_name field_name field_value field_order file the form has 5 fields, ...
2
votes
2answers
27 views

Geo localized database RTREE

I'm using MYSQL V5.6.24 GPL I have a database with 200K rows and growing and I'm trying to get the nearest, based on points. I have all the database in InooDB, and to use Spatial data, I create one ...
0
votes
0answers
12 views

How to do updates on all rows at every button click [migrated]

I have a python app that has an admin dashboard. There I have a button called "Update DB". (The app uses MySQL and SQLAlchemy) Once it's clicked it makes an API call and gets a list of data and ...
0
votes
1answer
24 views

Sumed all which has been summed [duplicate]

Is it possible to sum all row which has been summed? For instance I have this query: SELECT SUM(registered) AS `totalOftheDay` FROM table WHERE DATE(ds.register_time) BETWEEN '2015-03-01 ...
2
votes
2answers
193 views

MySQL - How to check whether all values of a given SELECTed row are equals? [closed]

Using MySQL, how can i SELECT all rows whose values are equals ? For instance, suppose a SELECT of a given table as follows COLUMN_A COLUMN_B COLUMN_C ... COLUMN_N 1 2 4 ...
1
vote
1answer
33 views

Student Attendance, null not returning [closed]

My question is based on the answer given here Attendance: id | student_id | date | ------------------------------ 1 | 2 | 2013-10-24 | Students: student_id | student_name | ...
3
votes
3answers
104 views

How to interpret correlated subqueries?

EDIT: Both answers are great but I think I must clarify my question. NOTE: I must indicate that in the following, by executed, I mean logically. I understand that the actual execution made by the ...
0
votes
1answer
22 views

Gotchas converting latin1_swedish_ci to utf8mb4_unicode_ci

I have a MySQL 5.5.31 database which has approx 220 tables - of these 220 tables, around half of them are already using utf8mb4_unicode_ci but the "older" tables are still using latin1_swedish_ci. The ...
-4
votes
0answers
22 views

stored procedure not working

i am running this one on mysql phpmyadmin. DROP PROCEDURE `gettree`// CREATE DEFINER=`root`@`localhost` PROCEDURE `gettree`(in team_id bigint) BEGIN declare count bigint; declare tmp_team_id ...
1
vote
1answer
60 views

updating same column as in the select query

When I try to do this, I get an error saying I cannot use the same table in where select as the column that I am updating. UPDATE table_name SET quantity = 19 WHERE productID = 148357 ...
0
votes
1answer
31 views

InnoDB Table has backed up INSERT and UPDATE queries, high CPU

I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST; Basically, I keep a table of every logged in customer and update ...
0
votes
2answers
77 views

Query that was working on MySQL is not working on PostgreSQL

I had the following query (with bin_number being a string) which was working on MySQL 5.5: Postgres - was working on MySQL and doesn't work on PostgreSQL SELECT "menu_items".* FROM "menu_items" ...
6
votes
1answer
743 views

SQL Query challenge

I have what seemed like an easy query to write, that really has me stumped - can anyone help me please? So this is really easy on paper, but I can't translate it into SQL. Lets say you have some ...
0
votes
1answer
50 views

Get the latest record and count them with time interval

I have a table as below id fk_sales_id status created_at 1 001 5 2015-02-17 16:45:44 2 001 8 2015-02-18 18:45:44 3 002 30 2015-02-20 16:45:44 ...
0
votes
0answers
36 views

I want to rewrite my MySQL query so that FIND_IN_SET doesn't do a full table scan unless necessary

I want to rewrite my MySQL query so that the last table join doesn't do a full table scan unless necessary. Here's what I mean. Under certain conditions I don't need to select any rows in that join ...
0
votes
0answers
22 views

MySQL Server Install Setup Types Developer Default vs Full

As I'm installing MySQL Server Community Edition the possible Setup Types are: Developer Default Server Only Client Only Full Custom Is there any difference between options Developer Default and ...
0
votes
1answer
37 views

combining two different record into single result

I am fetching data from 3 tables with the help of join. My output is in JSON format. Output in JSON format:- {"order_id":"2", "location_address":"Testing location Delhi", ...
-2
votes
1answer
52 views

how many database tables is too much?

I'm working on a project for a school and so far I have 91 tables! Everything is linked to the student table, is it normal to have this many tables in one database? or do i need to make multiple ...
0
votes
1answer
28 views

Export MySQL query log to JSON format

Is there any tool which is capable of exporting MySQL query log to JSON format (or any other structured format)?
0
votes
1answer
35 views

Finding the same value in a column and only returning rows without the same value

I have a query that i have to find the DiscountPercent that are the same and not show the rows with the same DiscountPercent. I have tryd this and it brings back the values that are the same SELECT ...
0
votes
1answer
18 views

Unable to get any type of connection using mysql on LAN

My question is rather simple, I have a Windows 7 machine that is hosting an Apache server and a MySQL server using the XAMPP control panel. Both of these have been installed as a service on the ...
0
votes
1answer
51 views

How to merge two databases, both with primary keys

I have two databases, both with the same tables, but the data is out of sync. I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a ...
0
votes
1answer
38 views

How to restore database from /var/lib/mysql

I have a database folder from a crashed server called gain_wordpress. I'm trying to put that database on a new server but I'm not able to do it successfully. I don't have a .sql dump, all I have is ...
0
votes
1answer
30 views

Change MariaDB 10.0.17 (MySQL) master to a slave

I have a MySQL master/slave configuration -------- -------- | master | -> | slave1 | -------- -------- I want to kill my current master, transform slave1 to be the new ...
0
votes
0answers
13 views

How to create a communication link failure in MySQL

For testing purposes I would like my MySQL server to generate a communication link failure (com.mysql.jdbc.exceptions.jdbc4.CommunicationsException). How can I tweak MySQL that this error is easily ...
1
vote
2answers
29 views

mysql - order by first condition in where clause

I have a table consists of the following fields id, name with the following values 1, ciro 2, test ciro 3, ciprox 4, other 5, other i would like to get all the values that begin with "ci" or ...
0
votes
0answers
26 views

Mysql cursor alternative / Optimization - each row updates are too slow

I'm looking to optimize my cursor based update or actually replacing it... Situation We're carrying out promotional campaigns and I'd like to track user activity per campaign. Logic Each campaign ...
0
votes
0answers
29 views

delete query performance issue on mysql

I have two tables table1, table2 where table2 is a subset of table1 (basically created with the query create table table2 as select * from table1 where .....) Now, I am trying to run the below query. ...
0
votes
0answers
23 views

Subquery optimization

I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ I am working with an existing design. What I want is to be able to select ...
0
votes
1answer
24 views

Unable to get the latest value added to Mysql table based on posted time

SELECT * FROM discussion_comments GROUP BY disc_id ORDER BY posted_date DESC I have table example like given below: CREATE TABLE example ( id int(11), cname varchar(11), posted_date date, ...