MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
41 views

Table partitioning in MYSQL Master Slave

I have to Partition a MYSQL table using Range partitioning. Table is 200M rows big and I have Master/Slave replication. What I want to do is to stop replication on slave, partition the table on ...
1
vote
1answer
42 views

Combining data from 3 tables depending values stored in 1 table

I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single ...
1
vote
1answer
23 views

MySQL: Incorrect binary log format - OFF.000

I have a Percona MySQL 5.5.30 as a master(Debian 6.0.7). I got a weird issue that all my binary logs started saving in format OFF.00* instead of the specified format in .cnf file. mysql> show ...
0
votes
1answer
41 views

How can i update table when counter reach a specific value? (thread safe) [closed]

I increase a field when somebody click the image. Also i want to update (decrease) customer budget when counter value reach to 1k. It is like that: UPDATE click SET count = count + 1 WHERE ...
0
votes
1answer
69 views

Cross Join and Left Join within a query

I have query which has a cross join and also a left join which produces an error Unknown column 'a.hour' in 'on clause' simplified version of query is like this SELECT b.*,a.hour,c.datetime FROM ...
1
vote
1answer
25 views

unknown variable 'log_slow_verbosity=query_plan'

What is the use of log_slow_verbosity = query_plan parameter in my.cnf ? I got an error when installing MySQL in my machine because of this parameter.
1
vote
1answer
22 views

UPDATE with Nested Query

This is what i've tried UPDATE (SELECT * FROM music GROUP BY songalbum) SET albumID = (SELECT albumID FROM albumList GROUP BY album ) WHERE songalbum = albumList.album this give the error #1248 ...
1
vote
1answer
16 views

incorrect result when filtering by association

I have a docket, which has many entries. I want only dockets, where none of the associating entries contains the text "PENDING". For example, if docket A has 10 entries, and one of those entries' ...
2
votes
1answer
59 views

How to optimize this MySQL query?

Hi can someone help me to optimize this MySQL query, it's taking very long to complete, I have set indexes correctly, but it seems that string operations are slowing this query SELECT ...
0
votes
0answers
6 views

Use MYSQL to Convert Status from one row to two and calculate date difference from prior row [migrated]

I want to use mysql to do the following: 1) take a raw data table that contains a product number column, datestamp column and a status column 2) convert it to a table that with a product number ...
1
vote
0answers
30 views

Why do I get this syntax error? [duplicate]

Here's the create statement pulled out of the database itself for a trigger: CREATE DEFINER=`walkdot_logan`@`67.53.251.66` TRIGGER `wd_artists_bi_trg` BEFORE INSERT ON `wd_artists` FOR EACH ROW BEGIN ...
1
vote
1answer
19 views

mysql how to replace a column content

I using Mysql. Table: Content_T1 id : int title : varchar255 content: text my all page content will be save in content columns. mysql content column... there is some <img ...
1
vote
1answer
25 views

Finding same values and updating rankings

thank you for taking the time to look at this. I have a simple table that I am trying to update based on the entries. table name is TOPUSER and there us a field called tu_total and tu_rank, I would ...
1
vote
1answer
18 views

How to reinitialise /var/lib/mysql files?

Due to a mishap I deleted the entire /var/lib/mysql directory. Since the database did not contain anything important, I do not want to go through the hassle of restoring it from an old backup, but ...
-5
votes
1answer
105 views

How to select only few columns from a table in database? [closed]

I have a table which consists of around 200 columns where name of each one is col_i (i=1 ,2,..200).Suppose I have to select only first 50 columns, how should I proceed? I found about concat() function ...
0
votes
2answers
20 views

cannot access my DB server on ubuntu

server OS: Ubuntu 14.04 MYSQL Server version: 5.5.43-0ubuntu The output of: select User, host from user is: db % db localhost In my /etc/mysql/my.cnf, I made sure that bind-address is ...
0
votes
0answers
21 views

pt-online-schema-change with statement based replication in MYSQL

Is it possible/safe to use pt-online-schema-change in a Master slave environment where as the way replication is setup is statement based(SBR). What is the better way of doing it as currently I have ...
1
vote
1answer
30 views

Innobackupex apply-log should be done before emergency restore or after each backup?

Basically Percona Innobackupex consist with three steps: 1. Backup 2. Apply log 3. Copy back restore during emergency My question: When should I do apply log? Is it after each backup or before ...
1
vote
2answers
41 views

Choosing what table fields to partition on

I'm having a tough time deciding whether or not to partition my table based on a unix timestamp or by a descriptor attribute. It's just over a billion rows currently. My table basically contains: ...
2
votes
1answer
32 views

MySQL Workbench team strategy

I'm considering to use MySQL Workbench at work, mainly because of the modelling tools and to easily sync the development and the production DBs. I work with another developer on the same project, and ...
0
votes
0answers
31 views

No data transferred when trying to transfer columns from one database to another [closed]

I'm trying to transfer columns from one database to another using the following: INSERT INTO ykz2d_purchas_integration (name, amount, summary) SELECT user_email, orderpayment_amount, ...
-1
votes
1answer
72 views

mySQL Failed and Recovered

For some reason mySQL keeps failing and recovering through the day. We have increased max database connections from 150 to 200, but that was not the problem and did not solve the issue. Does anyone ...
0
votes
1answer
15 views

Debug why MySQL/mariadb table is crashing

I have a MyISAM table that keeps crashing. How would I figure out why it is crashing? The error messages say nothing other than that it crashed. Is there a log setting I could activate? This is ...
0
votes
0answers
9 views

MySQL 5.6 or 5.7 sharding geospatial data

I have ran into a problem where I need to start to scale horizontally. With my database I am performing only nearest point searches with my data. So from a lat/long inoput I find all the nearest ...
-2
votes
3answers
57 views

webscalesql vs mysql. Which to use? [closed]

I am confused on what the difference is between webscalesql and mysql. From my understanding webscalesql is a branch of mysql. Does this mean that webscalesql is a subset of mysql, so it is better to ...
2
votes
1answer
80 views

How to select from 3 tables while using count on 1, and sum on another?

I'm stuck on this one query. Here is my setup: table 1 (shows) show_user show_count table 2 (views) view_user view_views table 3 (users) user user_name What I need to is produce results ...
0
votes
2answers
31 views

When rows of a resultset are generated?

Consider a client which asks MySQL server for 100000 rows, but retrieves rows later one-by-one. Does MySQL server generate the entire resultset before sending to client, or does it generate rows ...
1
vote
2answers
63 views

Is LIMIT processed before or after retrieving data?

MySQL. My heavy query crashed our MySQL server. If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries). Which way LIMIT are processed: before the rest of ...
0
votes
1answer
30 views

Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

I have 3 tables, users (user info), mghours (volunteer time), mgtraining (training time). I want a result that lists some user columns, sum of volunteer hours and sum of two types of training: uid ...
0
votes
0answers
51 views

Referral table structure

I need to make a simple referral system and I have a database table set out as follows: mem_id | date | refer_email | accepted And the meaning of such columns is the following: mem_id: The ...
0
votes
0answers
35 views

How mysql order data without order by clause? [duplicate]

Is there any way to order data without order by clause in MYSQL? select * from table table name order by col1 asc.
2
votes
1answer
67 views

Have foreign key constraint and index key for the same column(s)

I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My ...
1
vote
1answer
18 views

MySQL Crashing every few days

Our website hasn't launched yet so we're gaining little to no traffic at the moment. For some strange reason MySQL seems to crash every 2-3 days unexpectedly. Here's the SQL log of the crash ...
0
votes
2answers
55 views

Query syntax is checked at java level or Database Level?

If i used normal JDBC Connection for accessing database of Mysql, The syntax of query is checked at java level or Mysql level. Example String Query="Select * fromEMP" above wrong query syntax throw ...
0
votes
0answers
30 views

Get last updated date and group by in mysql

trans_id | item_id | price | last_updated 01 |001 | 100 | 2015-01-01 02 |002 | 200 | 2015-01-02 03 |001 | 150 | 2015-01-03 03 |003 | 200 | ...
0
votes
2answers
23 views

MySQL security group view permission query

I have a table of records that are only viewable by users with the correct group permission. The records may be viewable by multiple groups. Users may also be in multiple groups. record ...
1
vote
1answer
18 views

Completely removing InnoDb tables

To cut a long story short, I deleted several .idb files and then tried to re-import them from a .sql file. I'd imagine there is some relic of the old tables somewhere. Does anyone know what to remove ...
1
vote
2answers
60 views

How to prevent a trigger from running on certain statements?

I'm looking for an answer that satisfies my need on both SQL Server and MySQL. I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations ...
1
vote
3answers
104 views

Are TEXT fields stored separately from the row in MySQL-ISAM?

We have a "notes" table and some of the queries are getting very slow even though we're not searching on the note contents. We're wondering if moving the note content into a separate table would speed ...
0
votes
1answer
36 views

MySQL Database Keeps Crashing

Our mysql instance is keep crashing after every 5-10 minutes very frequently. We have around 600 databases on that instance. I think 600 is not a big number of databases given the size of databases ...
0
votes
1answer
21 views

Optimize slave .ibd tables, where slave has innodb_file_per_table and master does not

We are trying to reclaim file space on our replication server. The space we want to reduce are .ibd files on the slave. We have a simple Master -> Slave (read only) replication setup, for some reason, ...
2
votes
1answer
27 views

Insert into a DB that was selected via other DB?

I have a table that stores what database should I use for a given device( specified by its IMEI), like this: imeiDB( IMEI VARCHAR(15), db VARCHAR(100)); Let's say I have the database ...
0
votes
0answers
18 views

Mysql database name

My Mysql version is Server version: 5.6.19-0ubuntu0.14.04.1 (Ubuntu) I have two databases, with a test table in each database. I can run the following select statement in one db, but not the other. ...
-1
votes
0answers
11 views

Does version MySQl 5.6.24 not support the none object orientated code of previous versions? [migrated]

I have the following code that works fine in other versions of Mysql but not in 5.6.24. Its on a testing server and not live. It works fine on the on-line server, which uses a version lower. 5. $con ...
0
votes
1answer
28 views

Are there any risks on using 'single-transaction' and 'extended-insert' on mysqldump?

I'm new in db administration and I've looked for a faster import of a mysqldump. I had a db with about 1.1 million rows and mostly InnoDB tables and wanted this to import on a managed server. Before ...
1
vote
1answer
50 views

Fast query for value between 2 fields

I'm asking something very similar to this question. In the two options provided, one is SELECT * FROM your_table WHERE 1265631252 BETWEEN `IP FROM` AND `IP TO`; which another poster said is slower ...
1
vote
1answer
64 views

Is it ok to increase `innodb_stats_sample_pages` up to large number?

MySQL sometimes generates bad execution plan irregularly. 2 years age, I have increased innodb_stats_sample_pages(default:8) up to 64 and recently changed into 128 because Data Volumn has been ...
-1
votes
0answers
37 views

Get available first booking date in my system

I am creating an application which connects users with workers. Users are those who have some requests to be done and Workers are those who works for the users and get paid based on the working time. ...
0
votes
2answers
31 views

How to grant access permision for multiple database and multiple tables to a user in MySQL?

I have two databases WORLD,TEST each one contain 3 Tables.I also created a new User test_grant now i want to give SELECT,DELETE,UPDATE permission to test_grant user.But here i want to give two tables ...
0
votes
0answers
28 views

subquery in set clause retrieving different values

I want to update the column "kstplcode" using different values coming from column warehouse except the value "main" with their corresponding delivery numbers. I've tried this code but the set clause ...