MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
28 views

Mysql master master with high latency

We have a single master setup with many slaves in germany and some slaves in australia. To have faster writes in australia we want a master-master setup between germany and australia. Read slaves ...
1
vote
1answer
32 views

federated tables? synchronization? replication? [mysql]

I have a REMOTE suppliers table with, lets say, a code and a name, and several client databases will be creating new suppliers. Server database code | name ______________ 1 | Mark 2 | ...
0
votes
0answers
13 views

Suddenly high CPU usage

I've read a lot about MySQL optimisation and I already optimised MySQL about a year ago. Reason for that was a high system load average. I tuned many queries and edited the my.cnf file. Since then ...
0
votes
1answer
22 views

Only LIMIT rows with specific number

Sorry for the title, As I'm not that experienced I had a real hard time trying to come up with a proper title. So I have this query to fetch all the comments of a specific topic. Now for my paging I ...
1
vote
1answer
28 views

My HD crashed and I can't start MySQL

I am on Windows 8, my SSD got corrupted and I installed Windows on a new drive. I had before MySQL 5.6.12, now I installed 5.6.17 and tried to move the entire data dir. The location of the MySQL ...
0
votes
0answers
19 views

MYSQL — Inventory View with calculated fields

Trying To create an inventory view. I have created this query using MS Access query builder since I am no pro at SQL and this is pretty convoluted. When translating this into MYSQL syntax I get an ...
1
vote
1answer
30 views

Why is the subquery apparently correlated? How to avoid it?

I have a table orders and a table orderpositions. The orderpositions (basically the items bought with the order) are linked via orderid (indexed) to the orders primary key. For a given set of orders ...
0
votes
0answers
31 views

MySQL workbench: Cannot Connect to Database Server (ssh)

General pointers would be appreciated -- it's likely I'm misunderstanding the mechanisms involved in a remote connection through ssh. I can use ssh to connect remotely, and then, through the CLI, ...
1
vote
1answer
41 views

MySQL Deadlock - accessing different primary key values also creating deadlock

I went through MySQL logs and found the reason for deadlock 1st thread is trying to execute below query. Which is waiting for thread 2. UPDATE M_SAMP SET MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM ...
0
votes
0answers
24 views
0
votes
0answers
13 views

Different encoding in one file after importing to MySQL

Trying to import ONE SQL dump into the MySQL database by "source " command, and half of text (posts table on the forum) is going ok ("as is") but second half converted to another encoding. Anybody ...
1
vote
1answer
23 views

Import SQL dumps (tables) in folder from shell

Im trying to import SQL tables (actually this is files, one file for 1 table), and this is files are too big for importing in phpMyAdmin. It needs to be imported from shell. I know the command "source ...
1
vote
1answer
15 views

Alter all table's - discard tablespace

After having an awful experience with a dedicated server and spending 3 whole days repairing corrupted databases (Yes I know.... backup!).... I have narrowed the issues down to a few websites. The ...
0
votes
1answer
14 views

Mysql events table

I am trying to create a script to backup all mysql databases. This is the command that I am trying : sudo mysqldump -u user -ppassword --all-databases > /tmp/all-database.sql And this is the ...
0
votes
2answers
27 views

How to export image filenames from Wordpress database?

I have a Wordpress install that's using about 10,000 images. I'd like to export all filenames used in Wordpress to a file, filenames.log filenames.log would look like this: car.jpg boat.jpg milf.jpg ...
-1
votes
1answer
16 views

mysql database table corruption [closed]

Today when I opened MySQL database and I couldn't see very important table which were present/created about couple months ago. Yesterday I worked on it. Do you have some suggestions, how to get my ...
1
vote
2answers
17 views

How pt-online-schema-change ensures consistency during data copy

I have read pt-online-schema-change documentation and understood that it works by creating triggers and copying data in chunks with in chunk-time seconds(0.5 seconds in default) Let us consider this ...
1
vote
1answer
23 views

Replace string in the all tables of DB

I'm moving my website to new domain, need to replace all references of old domain to a new, in fact actually replace one string on another in all tables of MySQL DB. Please help me build the query to ...
0
votes
1answer
34 views

What can I do about this MariaDB 10.0 error?

I have a serious error that crashes the mariadb service, and it won't start up again until I delete the database folder in the data directory of the database I was working on at the time. Here is the ...
1
vote
1answer
44 views

Use a set database schema or EAV design? [duplicate]

I have already created a database design with a set schema. The database schema is for a web quotation form. PS it is not a simple quotation form and hence need to customise the form, form builders ...
0
votes
0answers
16 views

How the statement based replication works internal on secondary node of mongodb?

Mongodb supports statement-based-replication. Would like to know, how the statement based replication converts the code internally on secondary node of mongodb. Is that working related to the ...
0
votes
0answers
15 views

Finding all possible combinations of data set

I know I have seen other variations of this question, however I do not think any of them seem to handle my unique case. I have a database table that will be queried monthly, currently at 88,000 rows ...
0
votes
4answers
27 views

If all tables are myisam, should I use myisam engine instead of default innodb?

I have read all of the innodb/myisam threads and they are confusing. Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.? The software I am using creates ...
0
votes
1answer
15 views

When you use a replica to backup from so you don't have to turn off the master, is this considered High Availablity?

I read somewhere that MySQL can have a master / slave setup in which the master database can continue to run live and in production while the slave database can be turned off to snatch a backup. And ...
0
votes
0answers
26 views

Select items matching whole list

I have 2 tables: TABLE 1 and TABLE 2 I would like to select all items in TABLE 1 who fulfill all criterias (PHP variable). The criterias are made of multiple table2 values AND must match TABLE1.id ...
0
votes
0answers
19 views

How to get list of ids from joining two tables (one to many) whose results is not present in a list?

Say Table 1: tID, tName Table 3: t2ID, tID, tSubjects One tID can be have multiple t2ID. What I want is list of tIDs who's t2IDs should not just have a list of t2IDs (1,5,6). i.e They can 1,2,5,6 ...
1
vote
1answer
27 views

Are CREATE INDEX statements replicated in row-based Mysql Replication?

I've not been able to find a simple explanation of what kinds of statements are or are not replicated from a master db to a slave in MySQL, row-based replication. Specifically I want to add a ...
-4
votes
1answer
19 views

ExecuteNonQuery () not fungsional and database failed [closed]

Public Class Form1 Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load Dim SumberDb As String = "Data Source=c:\users\ogieuntung\documents\visual studio ...
0
votes
0answers
27 views

Getting Waiting for table metadata lock for select statements

I am using MySQL server 5.6.23 on CentOS and server configuration is like RAM - 32 GB Cores - 6 cores Innodb buffer pool size = 8 GB Key cache size = 2 GB We are using only select queries. But all ...
0
votes
1answer
39 views

MySQL Syntax Error 1064 On Set

Let's see if I can edit this and put the whole procedure in. I am trying to convert an Oracle database to MySQL. I have all the tables, keys, indexes, and views converted. I now need to convert a ...
0
votes
1answer
7 views

Check mysql domain that is using resources

I have a webserver hosting about 300 websites and sometimes my CPU usage gets close to 100% and when I use "top" in my CENTOS to check who is using all CPU, I discover it's mysql. I would like then to ...
0
votes
1answer
22 views

MySQL - Is it possible to assign permission to count the number of tables in a database without permissions on individual tables?

As part of a script I am trying to count the number of tables that exist in a particular MySql schema. Using my own user I am using the query SELECT COUNT(*) FROM information_schema.tables WHERE ...
0
votes
0answers
33 views

How to fetch data on a single column from a table having composite key

I am working on a messaging application, where the users can have a group conversation with multiple recipients. So in my database, I have a table called user_message where user_id and message_id is ...
1
vote
0answers
54 views

Limit relation one to many or one to one

In a relationship one to many or one to one you can limit the relationship of the related table? For example, I have a table 'cliente' and another 'acct', each 'cliente' can have thousands (perhaps ...
0
votes
0answers
28 views

Export the pivot-table-with-dynamic-columns as CSV

Export the pivot-table-with-dynamic-columns as CSV. I Need to export the following Query As CSV. SELECT id, name, description,GROUP_CONCAT(if (DAYS='2015-01-01',AMOUNT,NULL)) As ...
0
votes
1answer
26 views

mysql UNIQUE KEY multiple columns with one column as NULL

I understand the way UNIQUE KEY works in Mysql 5.5+, and understand that NULL allows duplicate entries to be created. In following schema and I would finally want that family_relation should have ...
1
vote
4answers
43 views

with MySQL, are multiple single column indexes faster than one multi-column index? [closed]

I have a performance related question, in MySQL, are multiple single column indexes faster than one multi-column index?
0
votes
1answer
29 views

MySQL server stops accepting connections partially (“gone away” error)

Hope someone can help me debug this issue. There is a MySQL server 5.6.22 running on OS X 10.2. The load isn't too heavy. All databases are stored in InnoDB. From time to time it stop accepting ...
0
votes
1answer
54 views

How to optimize a slow query?

I work as an administrator and software developer for a company that provides services to the car selling business. In essence we develop the software that they use when selling cars and such. This ...
1
vote
1answer
33 views

MySQL Master not sending events to another Master

We have Master-Master Replication and it is configured correctly as it transmit data for certain period of time and after that it does not send further events till we stop/start slave on both nodes. ...
-1
votes
1answer
29 views

Mysql query giving incorrect values [closed]

When this SQL query is executed it gives incorrect value, please advise select sum(cash_management.cash_in), sum(cash_management.cash_out), sum(cash_management.pay_in), ...
0
votes
2answers
37 views

Join that returns always 1 record

I have a table that holds tests. For every test you have a generic one (TYPE field is NULL) but for some tests you can have also more specific tests (TYPE field has a value). I need a join to this ...
2
votes
1answer
76 views

how can optimize this sql query for being faster than superman

this my sql query: SELECT * FROM (SELECT * FROM ClassifiedAd WHERE city_id = :city AND currency = :currency AND price > 100) UNION DISTINCT (SELECT c.* ...
1
vote
1answer
15 views

Why does most Galera cluster documentation recommend having at least 3 nodes as the typical set up?

The Galera documentation and most experts implementing this type of setup, recommend having at least 3 nodes in the Galera cluster. What is the reasoning behind this? Is a cluster setup with only two ...
0
votes
0answers
22 views

Uber-wide tables: Time to switch to Mongo?

As a web developer, I've typically only worked with nice, normalized tables in SQL-based RDBMS, but I'm doing some data consolidation for a researcher who will be analyzing correlations between all ...
0
votes
0answers
25 views

How to fix slow performed query with joins while pagination?

I've following tables with respective records. 1) wi_individual_g with 85K records 2) wi_organization with 550 records 3) wi_program with 2 records I've used following query for creating ...
0
votes
1answer
17 views

Optimizing query for large number of rows in mysql

I have the following database table in MySQL : and is running the following query SELECT `scratch_tickets`.`id` AS ticket_id, `scratch_tickets`.`created_at`, `scratch_tickets`.`prize_type`, ...
0
votes
1answer
15 views

How do I write this MySQL query?

I have a table with a column of numbers that are consecutive but not concurrent. So 91 may be followed by 92 or it may be followed by 96, for example) with intervening numbers not in the column. I ...
0
votes
2answers
7 views

How does InnoDB access disk?

I know that block(multiple disk sectors) is the basic unit of data transfer for the virtual file system so, if NTFS uses 4 KB cluster size for files how does InnoDB access disk if it uses 8 KB page ...
0
votes
2answers
127 views

Make DB tables customizable from an Admin

I want to build an commercial product promotional mobile application in which the administrator of the app will be able to customize some texts / pictures or whatever from the database that ...