Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
15 views

How to limit schema size by a trigger

The problem: I am running on a Webhotel a MySql database with different schemas with a maximum size limit on each schema. I can't change any settings on the DBMS. To be able to keep the schema below ...
1
vote
1answer
33 views

MySQL repair deleted data - Any chance of recovery?

Well, this has been a long Saturday morning. MySQL was throwing an error when dumping backups. That error was... mysqldump: Error 1030: Got error 5 from storage engine when dumping table mytable at ...
1
vote
0answers
40 views

innodb slower much slower than expected

Trying to figure this one out and I apologize for not providing a lot of information from the start (if you need further let me know and I can try to provide it). Query is performing very poorly - it ...
0
votes
1answer
15 views

Insert GEOMFROMTEXT (lat lng)

I am stuck, Please help. "INSERT INTO `routeplans` ( `polygon` ) VALUES ( GEOMFROMTEXT( 'POLYGON( (39.593254951279626 -119.2415714263916, 39.596231192849004 -119.2441463470459, ...
0
votes
1answer
24 views

Stacked Left Joins or Left Join inside a Subquery?

OK, this is a fun one. Designing a database for a gaming system. I have 3 tables that are relevant for the query I'm trying to work on right now. character_skill_log with the relevant fields of id, ...
1
vote
1answer
101 views

Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21. I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
0
votes
1answer
8 views

How many requeries/second can my server handle?

I have a dedicated server and my provider doesn't want to tell me how many requeries/second MySQL can handle. Is this just in relation with MySQL or also with other things? Operating system: CentOS ...
1
vote
1answer
21 views

Importance of uniformity of hardware for Galera cluster

How important is it for the nodes in a Galera cluster to be identical? I've found this answer which says they should be the same: http://dba.stackexchange.com/a/55224/770 I understand that you ...
0
votes
1answer
68 views

Why is MySQL Server software so huge? [closed]

How come MySQL Server software is bigger (in size) than SQLite? What makes it so big? Isn't it just about some network protocols and code that comes to save the tables and its entries? SQLite, with ...
0
votes
0answers
21 views

Mysql select using regexp

I have some user input i am receiving from some forms exactly in this format Names#Age#Gender#City The problem is,some forms come with incomplete data which is acceptable during the filling of the ...
0
votes
1answer
25 views

create foreign keys for pivot tables

I'm trying to optimise and speed up a mysql 5.6 datatabase. I have two tables with a many to many relationship: Reservation and Resource. Each Reservation can have many Resources and each Resource ...
0
votes
1answer
10 views

What does “as anonymous on” means in mysql's general log?

In mysql's general log I see things like: 141017 8:57:31 4 Connect root@localhost as anonymous on What is this "as anonymous on" thing even means? I log in as root@localhost. Am I still ...
0
votes
3answers
26 views

Mysql Create table shows ERROR 1005 errno 150

I am trying to make a junction table, but I getting a ERROR 1005 errno 150 CREATE TABLE 61furiousFistPokemon( cardNumber int NOT NULL PRIMARY KEY, pokemonName VARCHAR(12), type VARCHAR(10), stage ...
1
vote
1answer
34 views

Integrity constraint violation in Percona XtraDB Cluster

We have a PHP application that uses AWS Load Balancer that connects to 3 Percona Servers. Before we used a single standard Percona Server and that never happened. The cluster starts throwing Primary ...
0
votes
1answer
27 views

I noticed that my MySQL slave's DB size is smaller than the Master's DB

I have a very simple setup of a master MySQL DB with one slave MySQL DB replicating from it. Initially, when setting up the slave, I used mysqldump to get an initial DB dump from the master like ...
0
votes
1answer
19 views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
0
votes
0answers
46 views

MySQL has gone away with two connections

I'm using MySQL 5.6.19 on OSX 10.9.5 (DMG installation from mysql.com) and I'm receiving the infamous MySQL has gone away when I try to open two connections to this server. If I try connecting from ...
0
votes
0answers
25 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 ...
1
vote
1answer
35 views

Impact of using UUIDs with an UNIQUE index vs. PK?

It's well known how the performance of random UUIDs as PKs in an InnoDB table degrades terribly as it increases in size. Would an UNIQUE index for a non-PK UUID column have the same impact? UUIDs are ...
0
votes
1answer
30 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
2answers
41 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
57 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 ...
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
19 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
34 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 ...
1
vote
1answer
28 views

Unexpected empty result [closed]

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
0answers
18 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 ...
2
votes
1answer
35 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 * ...
0
votes
0answers
15 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
20 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
0answers
12 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
1answer
31 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
15 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
17 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 ...
0
votes
0answers
11 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 ...
0
votes
0answers
24 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
1answer
34 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
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 ...
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
2answers
42 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
23 views

combine results from two columns without union

I have a table with two columns : select his_name , her_name, other_name from foo I want to combine the result set into on row. I can do it with union select her_name as name from foo union all ...
1
vote
2answers
42 views

Using if statement outside of a stored procedure or function

I want to call a query to update a mysql table but my query contains an if statement set @id = 0; set @the_number = 0; set @the_message = 0; set @selected_message = 0; SELECT id, dest_msisdn, ...
-4
votes
1answer
43 views

How to design type table with indefinite multiple items?

I want to design an invoice system. In invoice table there should be an invoice_type, which comes from a table called invoice_type_table. I am confused with the structure of this invoice_type_table. ...
1
vote
1answer
52 views

Normalized Database Diagram

So I am working on my DB design skills, and have tried to create a normalized MySQL database. Here is the diagram: The reason I added the Address table is to break the address fields into separate ...
1
vote
0answers
35 views

triggers vs views [closed]

I have a mysql database with the following tables Penalties Scores player_instance When penalties is updated/deleted then it will update scores (via a trigger). Also when a player-instance row is ...
0
votes
1answer
31 views

Best datatype to store betting odds? [closed]

I have a lot of soccer betting odds (hundreds per match and thousands of matches), for example: <_10Bet_Home_Home>8.00</_10Bet_Home_Home> ...
0
votes
1answer
18 views

How to combine duplicate parent rows in a relational MySQL table

I have my DB structured like this with people.person_id as the foreign key for each row in people_meta. people table ╔═════╦════════╦═══════╗ ║ id ║ fname ║ lname ║ ╠═════╬════════╬═══════╣ ║ ...
0
votes
1answer
29 views

how to give GLOBAL PRIVILEGES in MySQL?

I need limited Global permission like Select Execute and grant privileges to one user. how can i give, can any one help me.
0
votes
0answers
24 views

SQLDependency equivalent for MySQL [migrated]

I want to implement a real time update on my ASP.net project, so i tried to implement the long poling mechanism and Comet. var isPolling = 0; function longPolling() { isPolling++; $.ajax({ ...