MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
29 views

Warning: mysqli::mysqli(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: NO)

I am using MySQL with PHP for printing details from a database. I tried to make a new user, which was successful, although I get an error from MySQL when I tried to fetch data from it. It showed me ...
0
votes
2answers
963 views

How to allow remote connections to mysql from specific IPs only?

I have a fresh install of mysql server 5.2.6 on a 64 bit Ubuntu running on a remote server with static IP. Is there anyway to allow Mysql remote connections to be made from pre-approved IPs only? I ...
0
votes
1answer
33 views

Index Strategy for MySQL Query that matches on one column and between two others

I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorised into groups. The events have a start and end ...
0
votes
1answer
20 views

MySQL Cluster node utf8

I have configured MySQL Cluster (5.6) with two data nodes and managment node. Problem is that I cannot put special chars - looks like encoding is not set to utf8/utf16. I logged into each node and ...
0
votes
2answers
21 views

Unable to remotely access MySQL

I am experiencing problems with mysql 5.1 at the moment and possibly due to that I am unable to connect. The port that I am using for mysql is open (http://www.yougetsignal.com/tools/open-ports/) ...
0
votes
1answer
1k views

How can I get Performance Statistics to work in MySQL Workbench?

I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema. The EXPLAIN plan is ...
0
votes
0answers
30 views

How to interact with a foreign key in database [migrated]

I have been working on a database for sometime now and i got stocked a particular section. I am trying to write a code to control the foreign key column in a database. mysql is the back-end and Java ...
1
vote
1answer
1k views

MySQL slave stuck at 'System lock' most of the time, high CPU usage

Let me describe what we are facing now: We have a MySQL Master-Slave, Row Based Replication setup and at the moment, the slave is stuck with a perpetual System Lock. The Master is an active server ...
1
vote
1answer
3k views

Amazon RDS - Can't connect to MySQL database server?

So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. ...
1
vote
1answer
252 views

Adding foreign keys constraint on databases used

We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all ...
0
votes
1answer
16 views

How can I append new data from a particular mySQL table to an excel spreadsheet?

We have a database trigger that automatically adds new clients to a table when they first contact the server, it takes a number of unique identifiers that the client provides on first contact and ...
0
votes
0answers
499 views

Blob datatype issue with log file

I am getting continuously below error message in mysqld.log [ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase ...
0
votes
1answer
23 views

Copy a Large INNODB table in MySQL

I have an INNODB table that's > 93 million rows. A lot of the data is considered "temp" data and is governed by an "is_active" flag of 1/0. When a user updates a form, new data is written with an ...
0
votes
1answer
737 views

Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows. The query ...
0
votes
2answers
240 views

Creating a MySQL db from an Informix database

There is an existing database that was created in Informix. I would like to export it and then import it into MySQL. I tried to export it using dbschema and it created the dbase.sql file and several ...
0
votes
3answers
262 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
0
votes
2answers
1k views

mySQL Optimization Suggestions

I'm trying to optimize our mySQL configuration for our large Magento website. The reason I believe that mySQL needs to be configured further is because New Relic has shown that our SELECT queries are ...
0
votes
2answers
21 views

Show sql statements from mysql binlog (GTID)

I have mysql binlogs for replication through GTID. I'm trying to show executed update/insert statements like this: mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000024 But all I see is ...
2
votes
2answers
24 views

Identify slow query without slow query logs in mysql server

I am wondering is there any other way to to check our slow queries without logging slow query. Suppose, I have a highily busy server can't afford to log much to save memory and I/Os. Then, is there ...
0
votes
1answer
190 views

mysql innodb_buffer_pool_size let me understand

I couldn't find answer for my situation. I have a windows server 2008 with 2 cores and 7 GB RAM (Azure D1 VM). I'm running mariadb 10.0.14 on it. I have 2 databases (1.5GB and 9GB in size). ...
0
votes
0answers
16 views

How does mysqlslap calculate average number of seconds to execute all queries when number-of-queries specified?

Let's say I have 10000 queries (q1,q2,q3....,q10000) and want to test the queries performance using mysqlslap with number-of-queries option along with 100 concurrent sessions(c1,c2---c100) and one ...
0
votes
0answers
40 views

slave mysql server crash : mysqld got signal 6

Hi I am trying import a mysql logical backup file on server A.Server A and B are in master slave configuration and both running on same unix server.slave is running while import is in progress.Due to ...
1
vote
2answers
576 views

how to avoid duplicated column in two different tables?

OK, I'm inexperienced in terms of database design, so hopefully my question isn't too silly. I have 2 tables users and user_profiles these two tables has one-to-one relation between them. ...
0
votes
1answer
14 views

Working with variables in stored proc

I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow. My code looks like this: SET @table_ = ...
1
vote
3answers
704 views

How to remove master role from MySQL server

I have 2 MySQL servers, one is the master and other one is slave, that's all good. The problem I have is that the slave is also set up as a master. I would like to disable or remove the master role ...
0
votes
0answers
15 views

Searching a keyword from multiple tables using my querry , [migrated]

I want to search "law" from three tables tbl_books, tbl_books_author and tbl_books_subject. I am running following query. SELECT * FROM tbl_books p, tbl_books_author d, tbl_books_subject m WHERE ...
0
votes
2answers
10k views

I can't get MySQL56 service to start after i stopped it

I am having an issue on my server. I am running MySQL on Windows Server 2008 R2. I have done some changes to my.ini file so I had to restart the MySQL for the changes to take effect. and when I went ...
0
votes
1answer
157 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 ...
0
votes
2answers
44 views

How to get several specific records from a query result?

I know by using Like with "LIMIT 1, OFFSET x" I can get the Xth record from a returned query result. But what if I want to get X, Y, and Z all at once? Currently I do a for loop and it's like I'm ...
0
votes
1answer
253 views

Update table from calculated temporary table

I have two tables, one containing a set of user ID's, vote direction (-1 or 1) and ID of the object they voted on (casted votes table). In the other table, the object id, upvotes, downvotes and ...
0
votes
1answer
135 views

How to convert database mysql4 to mysql5

I have mysql dump file named booster.dump. It was export from 4.0.27-standard mysql server. Now I want to convert this database to mysql 5.1.73-cll. How can I do it. I search about this topic in ...
0
votes
1answer
31 views

What is the most efficient way to exclude records with a particular ID in a MySQL query for random records?

I have an api endpoint that returns X amount of random posts from a table called "posts". This endpoint is used by a mobile app. It retreives random posts by doing a SELECT COUNT(*) on the posts table ...
0
votes
1answer
23 views

How to mimic a full outer join using mysql views?

I have all kinds of views in mysql with employee data. I have tried to use methods like this -http://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join but ...
0
votes
0answers
30 views

Is doing an online alter on a MySQL table to add comments safe?

Is doing an online alter on a massive MySQL table to add or edit table or column 'comments' safe? Or do you need to take precautions like you would while adding, removing columns, etc on that table?
0
votes
1answer
42 views

Giving a MYSQL Database a “version”

I am writing a game that uses an embedded mySQL database for keeping track of scores and I am releasing an alpha soon. I know there are going to be features added down the road which are going to ...
0
votes
1answer
14 views

What does MySQL “USE INDEX()” without any arguments do?

Does the USE INDEX() have any effect here? SELECT `posts`.`id` FROM posts USE INDEX() where `posts`.`size` > 1024; (I have it presently in some SQL because of the way the query is ...
1
vote
1answer
32 views

how would i merge a column where matches a different column? [closed]

It's very similar to the below post How to merge column in mysql depending on match table user Merge customer_id from table user to table cust where customers_firstname AND customers_name ...
0
votes
1answer
462 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, ...
7
votes
2answers
89k views

How to resolve too many connections and fatal error in mysql running on vps

I am running an application PHPlist on my linode server, simultaneously running 12 PHP scripts, each of which opens a MySQL connection. Now when I access PHPlist it often shows this error: Fatal ...
1
vote
2answers
360 views

MySQL 5.1 High Cpu usage on CentOS 6

I am running MySQL 5.1 on a CentOS 6 server with 16G ram. I got also apache on it. MySQL is constantly using too much cpu. top - 17:40:36 up 112 days, 12:13, 1 user, load average: 1.24, 1.72, 1.82 ...
0
votes
0answers
14 views

MySQL ignores ZWNJ character in string comparison

If you add a unique index to a char columns you can not add two string that differ only in having ZWNJ characters (e.g. 'فا' and 'ف‌ا'). However I need to store both of them. How can I overcome to ...
1
vote
1answer
21 views

Unable to update table after creating an After update trigger

I have a table like this /*Table: pageprivilege*/ ------------------------ /*Column Information*/ ---------------------- FIELD TYPE COLLATION NULL KEY DEFAULT Extra ...
1
vote
3answers
192 views

Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB. I know the risks involved based on resources such as ...
0
votes
1answer
35 views

Unknown sticky Problem after MySQL upgrade

few weeks ago we upgraded mysql server from 5.5 to 5.6 on 1st server few days ago (5May) I reboot mysql server , after this customer reported that some database data table not load to their site . ...
0
votes
0answers
22 views

select with a multiple row in pivot table [migrated]

I really don't know how to retrieve a specific row from a pivot table. Here's my pivot table: +----+-------------+--------+ | id | peticion_id | tag_id | +----+-------------+--------+ | 1 | ...
1
vote
1answer
128 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
0
votes
0answers
57 views

Advice designing a stock management database

I am developing a webapp for my business, a mobile phone repair shop. The problem I have is I don't know how exactly to implement a way to manage the parts inventory. Here what i have now: ...
1
vote
1answer
43 views

mysql: counting number of tickets which are open per day basis

I have a table tabName with the following rows: id(PK) .. Created(datetime) Resolved(datetime) Status(new/resolved/open) 1 2015-05-10 00:00:00 1970-01-01 .. open 2 ...
6
votes
1answer
131 views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
0
votes
1answer
36 views

Mysql - Examine last query run by thread in sleep state

I am seeing some threads in the "sleep" state for longer than normal. Normal maybe being 1-3 seconds with the abnormal being up to 60 seconds or longer. It makes sense that this is an application ...