MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
21 views

Receiving duplicate results in one condition of JOINED query

I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas ...
1
vote
1answer
10 views

Why MySQL init_connect variable gets updated after rebooting?

After SET GLOBAL init_connect=''; query everything works fine. However after rebooting the server or restarting the MySQL service it updates the init_connect value to some random value (OÇÿOÇÖ). Do I ...
0
votes
1answer
134 views

How to get names form 3 different tables

I have 4 tables to proceed. In table A I have the parameters of columns in tables B,C,D and the names are there. TB_A ...
0
votes
0answers
53 views

Rows are not getting deleted from MySQL Table

We have one innodb table and one Federated table in our environment and while deleting rows from Federated table it shows 24-rows affected with no warning, but rows are not deleted. There is no ...
1
vote
1answer
74 views

Reviewing relational database tables

I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong. I have ...
1
vote
2answers
32 views

Lost connection to MySQL server during query issue

SELECT UNIX_TIMESTAMP(a.timestamp) AS timestamp, a.adid, a.adtitle, a.cityid, a.price, a.area, a.addesc, a.subcatid, ct.cityname, p.picfile, scat.subcatname, cat.catid, cat.catname FROM xzclf_ads a ...
0
votes
0answers
18 views

HTML Form: Insert id into related table [migrated]

I'm relatively new to both PHP and MySQL and here's my first question: I have 3 tables. One as the "main table" and two as basically lists/categories of which I can choose and relate them to my main ...
0
votes
1answer
26 views

MySQL: Rank by Department in an employees table

I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.
1
vote
1answer
24 views

Unexpected empty result [on hold]

select a,b from table1 where a not in (select x as a from table2) AND b not in (select y as b from table3) The above query gives an empty result. Why? And how should I construct this combined ...
1
vote
1answer
397 views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
2
votes
1answer
30 views

1 query using OR operator in where clause vs multiple query equal condition where clause in mysql

case 1: 1 query using OR operator in where clause like select * from users where name='smith' or nick='smith' (using index_merge ) case 2: multiple query equal condition in where clause select * ...
4
votes
3answers
6k views

Why do the MySQL bin log files still exist after a purge or flush?

I've used PURGE BINARY LOGS as well as FLUSH LOGS, but the mysql directory still contains these files: mysql-bin.000025 mysql-bin.000024 mysql-bin.000023 mysql-bin.000022 mysql-bin.000021 ...
0
votes
1answer
18 views

Query condtion for values not being the default value?

Can I reference a columns default value directly in a query? Something like this: SELECT column FROM table WHERE column <> column.DEFAULT_VALUE Background: I'm writing a application test, and ...
0
votes
1answer
31 views

Where does mysql store data?

Where does mysql store data? I found out (by using mysql>SELECT @@datadir ) that it's in var/lib/mysql - but that can't be it. I have a quite big database (4 GB) called 'bot', but all the files in the ...
3
votes
1answer
657 views

MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3

I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew brew install mysql Everything seemed to go smoothly, but...not really, as it turns out. I cannot add a new user (neither ...
0
votes
1answer
201 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
1
vote
0answers
14 views

Any way to mitigate the large server load during LOAD DATA LOCAL INFILE operations?

I'm using MySQL LOAD DATA LOCAL INFILE to load csv files, and have noticed that during these loads, server output speed to other users diminishes eg long page download times, logins timeout. The LOAD ...
0
votes
0answers
29 views

MySQL Binlog Dump process stops after net_write_timeout

We have 2 MySQL 5.6.19-1 servers with master-master replication that were working fine (DB01 and DB02). One of them was rebooted unexpectedly (DB01) and seemed to be working fine, but we saw that the ...
0
votes
1answer
110 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
0
votes
1answer
27 views

Dealing with Mysql Replication and Functions

In MySQL replication Master-Slave, is there anyway to deal with the functions: LOAD_FILE() UUID(), UUID_SHORT() USER() FOUND_ROWS() SYSDATE() GET_LOCK() IS_FREE_LOCK() IS_USED_LOCK() ...
0
votes
1answer
277 views

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Can someone tell me how to prevent the following commands DROP TABLE <tbl-name>; TRUNCATE TABLE <tbl-name>; from being replicated to slaves?
2
votes
4answers
273 views

MySQL: Keep the 10 most recent rows for each user

I have a table saving scores for a game that have 3 columns (more in practice, but it is easier for everyone this way): userid : the id of the user that got the score timestamp: the time the score ...
0
votes
0answers
10 views

What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration?

What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration? The default is 2. The MySQL server is running on the same Windows machine, installed with defaults. ...
0
votes
2answers
18 views

How to check/change MySQL named pipe settings?

I'm having a hard time finding anything on this because I don't know what to call it. But when you install MySQL it asks you if you want to allow connections to the database from other machines or ...
0
votes
1answer
445 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
0
votes
1answer
33 views

to pivot or not to pivot - schema design question [closed]

I am implementing something similar to this schema http://www.databaseanswers.org/data_models/event_reservations/index.htm It occurs to me that the pivot table for Customer_Bookings would just keep ...
1
vote
1answer
669 views

mysqlbinlog failing with “ ERROR 1032 (HY000) at line 48: Can't find record in `` ” Error

I am facing some issue with PITR restore using mysqlbinlog . It is failing with the following error . # mysqlbinlog mysql-bin.000035 | mysql -u root -p db Enter password: ERROR 1032 (HY000) at ...
0
votes
2answers
3k views

How to sync two databases (alternative to mysql replication)?

Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This ...
0
votes
0answers
11 views

Recreate partition in mysql

I have a table having datetime column partitioned by RANGE (DAYOFYEAR(datetime_col)) By this I want to drop oldest of 4th week partition data and recreate immediately for next year similar week ...
0
votes
2answers
37 views

Merging tables, but ignoring duplicate columns

I have two tables I want to merge: city1(id, city, state, zip, lat, lon, county) city2(id, city, state) The tables contain overlapping city and state, but not primary keys. I can't figure out how ...
0
votes
1answer
158 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
8
votes
4answers
50k views

MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

I use the root account created the account 'a'@'%'. But I can't use the it to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. Please see ...
1
vote
4answers
23 views

How to alter/migrate the schema of a table with tons of records

I have a table which has about 1 million records, and recently i'm going to ship a new feature which requires 2 new fields. But I found it takes about minutes to get it finish, and during the ...
0
votes
1answer
11 views

mysql “can't create test file” error on Centos

When starting mysql on Centos servers with a custom data directory you might get an error similar to the following: 2014-10-15 10:23:56 17081 [Warning] Can't create test file ...
0
votes
0answers
16 views

Data usage increase although remove data in table MySQL Cluster

I try to test my MySQL cluster system. I add data to a table and use command ndb_mgm all report memoryusage. The data usage in data node increases. When I remove all data in the table and use command ...
2
votes
1answer
331 views

How to normalize my db schema for a quiz application?

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
1
vote
1answer
387 views

MySQL Workbench Tabs refuse to close

I'm using MySQL Workbench 6.1.6, and I have opened a SQL Editor Window and have 14 Query tabs open. If I close the connection, and then open the connection again from the Home page it reopens all 14 ...
0
votes
1answer
85 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...
1
vote
1answer
329 views

creating a report on the mysql jasperReport server

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf ...
0
votes
0answers
9 views

Is online schema change possible with MySQL Cluster?

I believe that this is the first question on this community. I'm surveying MySQL Cluster for next project in my workplace. I read a lot official/unofficial pages on the web. But I'm not a native ...
5
votes
4answers
11k views

MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)

when I tried to restore all database dump which is in 5.0 version to 5.6 version, it got restored and after that when I tried to reconnect, am getting the following error ERROR 2049 (HY000): ...
1
vote
1answer
30 views

Help on MySQL joins

I'm building a system to monitor currency prices. Part of my database structure looks like this: My system is trying to pull the api ticker for each exchange with this query: SELECT distinct ...
0
votes
0answers
23 views

Mysql sum of column group by question

I have the results of a select that I am joining on that I need to get a slightly complex sum of a column from. I need the sum of result grouped on id PLUS the sum of result of any row where ...
0
votes
0answers
534 views

The total number of locks exceeds the lock table size, even after increasing buffer pool size

I am trying to partition a table with 200 million records in it. This table already exists: CREATE TABLE linkmap_ntoken ( target_nToken varchar(200) NOT NULL, ntoken_hashcode int(15) NOT NULL, ...
0
votes
1answer
17 views

error Error Code : 1054 Unknown column 'v_column' in 'OLD' [closed]

DELIMITER $$; DROP TRIGGER `opendental`.`patientAfterUpdate`$$ create trigger `patientAfterUpdate` BEFORE UPDATE on `patient` for each row BEGIN declare v_column varchar(20); if new.LAST_UPD_SRC ...
1
vote
1answer
22 views

Is there any drawback of MIXED replication mode?

So, recent mysql server allows three types of replication mode: STATEMENT, ROW, MIXED. They all have pros/cons, and I pick the mode according to site characteristics. When I get a replication error I ...
0
votes
1answer
53 views

pt-table-sync for 3 master servers

We had a mysql multi master with 3 nodes and unfortunately the replication broke. We have some set of tables which has to be in sync with other database servers, so i was looking into Percona-Toolkit ...
0
votes
1answer
216 views

MYSQL Timezone support

We are having a shared hosting plan and they are saying that do provide MYSQL Timezone support in a shared hosting plan. I can create timezone related tables in our database and populate them with ...
0
votes
0answers
31 views

How can I speed up MySQL Workbench in a SQL Server to MySQL conversion? [closed]

I am using MySQL Workbench to convert a 9GB SQL Server database (2012) to MySQL 5.6 on the same AWS Windows machine. (It's an r3.2x large, 8 vCPUn, 26 ECU, 61 GB RAM). It has been running for about ...
7
votes
1answer
5k views

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?