MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
23 views

MySQL Trigger On UPDATE “Unknown Column” Error

I want to log a table when its updating a row which is column "insertDate" or "updateDate" value is before than 3 month. I wrote this trigger: DELIMITER /// CREATE TRIGGER ...
1
vote
1answer
45 views

Slow MySQL query when the IN list is long

I have 3 tables, namely: product, product_has_category, category: CREATE TABLE IF NOT EXISTS `product` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `des` text COMMENT 'description', ...
4
votes
3answers
120 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
2
votes
1answer
287 views

Get child record from specific root

I have two tables articles and topics and I am using MySQL 5.6 Articles id topic(fk on Topics.id) featured 1 1 1 2 2 0 3 3 ...
5
votes
2answers
6k views

How do I add a MySQL linked server to SQL Server?

I am trying to add a linked server on SQL Server to MySQL Server. Note the SQL Server is installed on a separate machine with a different domain name. The SQL Server is 10.0.1.1 on old_domain.com. ...
0
votes
1answer
109 views

How to upgrade MYSQL from 5.6.12 to 5.6.21?

I have been a MSSQL DBA and dont have much knowledge about MYSQL. Does MYSQL also have patches just like MSSQL? I need to do MySQL version upgrade from 5.6.12 to 5.6.21 but i do not have much ...
0
votes
2answers
2k views

Error changing mysql 5.6 default port

I've changed my.ini with port=3308 but after restart the service mysql 5.6 is working in the same default port 3306. I've tried in mysql 5.5 and it worked fine. C:\Archivos de programa\MySQL\MySQL ...
0
votes
1answer
1k views

How to convert a MySQL database from utf8mb4 to utf8?

I would like to convert a MySQL database, which has utf8mb4 character set and utf8mb4_unicode_ci collation, to utf8 and utf8_unicode_ci. How to do that please? Thank you in advance for your ...
1
vote
1answer
9 views

Automatic replication on slave node after service restart

I am new to mysql administration. we have master - slave replication environment. I added a 2nd slave node to existing env [master-slave] and replication is successful. Whenever I restart the mysqld ...
18
votes
7answers
8k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
0
votes
2answers
108 views

Deleting a Large Crashed MyISAM Table

Our server has a large MyISAM MySQL table that is no longer needed and is currently crashed. If we repair it then it greatly slows down our server consuming sources and it's almost 50GB so the process ...
8
votes
9answers
2k views

Which DBMS are fast enough for an online game (few thousand players)?

I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast ...
0
votes
1answer
12 views

Changing Data Directory on MySQL DB Server

I have installed mysql server 5.6.24 on Windows Server 2012 R2. I imported dump files and the data files are stored in C:\ProgramData\MySQL\MySQL Server 5.6\data I want to change directory like ...
1
vote
1answer
35 views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found

I've got the same error, although I'm running galera cluster with 3 nodes. I tried following the instructions from InnoDB: Error: Table "mysql"."innodb_table_stats" not found after ...
0
votes
1answer
26 views

MongoDB to MySQL Replication Automatically

i have Mongodb server and mysql server. i want to use mongodb for "write" and mysql for "read". so i am looking for some tool or some process by which we can tranfer mongodb to mysql server on ...
0
votes
1answer
1k views

Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?

MySQL 5.1.x | Windows Server 2003 Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue? For example, if I ...
0
votes
0answers
12 views

innodb_autoinc_lock_mode

We have a scenario. We are migrating from master running MYSQL 5.5 statement based replication with innodb_autoinc_lock_mode=1. We have made our new master (in RDS MYSQL 5.6) a slave of old master. ...
2
votes
1answer
38 views

How to get an array as output parameter from stored procedure?

This is my stored procedure: CREATE PROCEDURE `getAttritionData`( IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT ) DETERMINISTIC ...
1
vote
2answers
42 views

group_concat in separate columns

I have a players table id name -- ---- 1 jack 2 john 3 mike 4 pikachu And I have a matches table where these players can be matched together. id pair_id player_id -- ------- --------- 1 ...
0
votes
3answers
3k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
0
votes
0answers
25 views

MySQL: write in one connection then read in another

Say I have a library function, exec_sql(some_sql), every time it's been called, it obtains a connection from a connection pool, execute the sql using that connection, and then release the connection. ...
2
votes
1answer
137 views

SELECT RELEASE_LOCK causing “Mysql has gone away”

During a long running process (90m+), our code calls SELECT RELEASE_LOCK('..');. However it always gives a "Mysql has gone away" error when we do this. We have tried a number of things including ...
0
votes
1answer
381 views

Mysql can't write to filesystem

I need to output some queries as csv files. The directory is in the webroot and I want it to be writable by apache too, so I'm trying to add mysql to the www-data group (on Ubuntu). I thought this ...
1
vote
0answers
201 views

MySql: Slow join and group by on indexed columns

The following query takes 15-20s to run SELECT "Trade_US"."ID" AS "Trade_US.ID", "Server_US"."Name" AS "Server_US.Name", "Trade_US"."TradeTypeID" AS "Trade_US.TradeTypeID", "Users"."ID" AS ...
4
votes
1answer
71 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 ...
1
vote
0answers
24 views

Search a value from multiple column and multiple table [closed]

I have to search a value from multiple column and multiple table and each table is linked by foreign key and private key (in parent table , there is only one parent table and rest is child table of ...
2
votes
1answer
107 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
1
vote
1answer
852 views

NVARCHAR issue when migrating from SQL Server to MySQL with MySQL migration wizard

My issue is similar to this one but and I have tried what has been suggested but I still get the error. I have set the target column from VARCHAR(5120) to TEXT (tried LONGTEXT too) but the issue still ...
0
votes
1answer
40 views

Use WHERE on result of SUM

So, I have this query working great SELECT u.username, SUM(kort) as total FROM user as u LEFT JOIN userresult as r on u.userID = r.userID WHERE r.date >= '$date' group by u.userID order by ...
0
votes
0answers
28 views

mysql MATCH relavancy score

I thought this would be an easy question to google for; but I couldn't not find it. Given the following query: SELECT name, MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) as rel FROM ...
0
votes
1answer
521 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
1
vote
0answers
25 views

2 AUTO INCREMENT COLUMNS in ONE TABLE [duplicate]

I have table named ITEMS where there should be 2 auto increment columns. So the table structure would be: ID | ITEM_ID --------------------------------- 1 | ITEM201521080225-001 2 | ...
2
votes
1answer
243 views

MySQL master slave replication not working

I had MySQL master slave running and working fine but suddenly it stopped. I don't know which commands have been executed but now replication is not working. I am very new to MySQL can anyone help me? ...
2
votes
2answers
849 views

Merge multiple columns into one column in different table?

I've done this before, but I don't remember how. I need to select all rows from several columns in one table and insert them into one column in a different table while keeping them all on separate ...
2
votes
1answer
538 views

MySQL ssl setup failed

I am following word by word of http://dev.mysql.com/doc/refman/5.0/en/creating-ssl-certs.html for creating SSL certificates to setup SSL enable replication but it is not working for me. Steps which I ...
0
votes
0answers
23 views

Ranking refferals [duplicate]

I need to essentially rank user referals that have been made for each category. In my case the category being used is 53 so I need to get the ranking order 1st,2nd,3rd...etc for this category based on ...
0
votes
0answers
17 views

Exception when trying to connect to remove MySQL from Intellij IDEA

I can't connect from Jetbrains IDA to my MySQL at AWS. I am getting exception: Connection to MySQL - aws failed java.sql.SQLException: Communications link failure The last packet sent successfully ...
0
votes
1answer
139 views

mariadb / mysql wont change table_cache / table_open_cache

I've configured my i7 64gb ssd server with centos7 with the following my.cnf [mysqld] key_buffer = 12M max_allowed_packet = 16M table_cache = 4096 table_open_cache = 4096 table_definition_cache = ...
4
votes
1answer
108 views

Column cannot be converted from type 'int' to type 'bigint(20) unsigned'?

I have a Percona MySQL cluster, with a master and a few slaves. Each of them is running "Ver 14.14 Distrib 5.5.40-36.1" of MySQL. Replication is row based. I ran an alter query on one of the tables ...
0
votes
0answers
29 views

pt-online-schema-change deleted our data

We used pt-online-schema-change tool on number of our tables. We ran up into a problem of data inconsistency. We lost some of our data. The way we implemented was on slave and replication was also ...
9
votes
5answers
11k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
2
votes
2answers
82 views

MySQL growth 500k records/day

Im not a DBA however am an infrastructure guy used to installing and configuring database servers such as MSSQL, MySQL, MongoDB etc... I have a MySQL database which is being used for archival of ...
1
vote
0answers
38 views

User Privacy Settings DB Schema Design

This is how settings for users are designed, to so they show on some other Stackoverflow pages. User =================== id e-mail password ... =================== User_Settings ================= ...
0
votes
1answer
2k views

MySQL PDO Cannot assign requested address

Can someone help me with this error? [08-Apr-2013 17:44:08 Europe/Berlin] PHP Warning: PDO::__construct(): [2002] Cannot assign requested address (trying to connect via tcp://****:3306) in ...
0
votes
1answer
23 views

How to make a mysql statement for sent log

I have a table called "make_sent_log" CREATE TABLE IF NOT EXISTS `make_sent_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `transaction_id` varchar(255) NOT NULL, `total_base` int(11) NOT ...
4
votes
2answers
422 views

Best Practices for Creating and Deleting Records in MYSQL

I'm fairly new to working with databases, but I've currently have a webCrawler that I've written in C# using MYSQL database. The crawler is frequently writing and deleting records from the database ...
0
votes
2answers
39 views

“Table doesn't exist in engine” after moving tables from one VPS to another

I have two VPS's running right now. One, which I will refer to as the "old" server, runs on Debian 7 and gives this output for mysql --version: mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu ...
2
votes
1answer
24 views

No mysql database in mysql server - how do I give users permissions?

I have a mysql server (5.5.44) running on my local Ubuntu 14 machine. I can start the interactive shell via $ mysql When I ask to see all the databases, there is only one mysql> SHOW DATABASES; ...
0
votes
1answer
154 views

WordPress MySQL writes during “Flush Tables With Read Lock” are rejected instead of being queued

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup. Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to ...
0
votes
1answer
326 views

Transfer all read request to Slave in Master-Slave MySQL

I have configured master slave on my system (MYSQL). Now, is there is any script or way available or possible to transfer all read request to SLAVE. So, let me make my question here more clear. I ...