MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
33 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
2
votes
2answers
181 views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
0
votes
1answer
26 views

MySQL data transfer to different schema

We have two servers: one for the developper (me) and one for the tester. While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type) I'm trying ...
1
vote
1answer
28 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
0
votes
1answer
21 views

Slow query log of wp_options WHERE autoload = 'yes'

Slow query log: # User@Host: # Query_time: 181.648376 Lock_time: 2.114138 Rows_sent: 1425838 Rows_examined: 1663532 SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'; Table ...
0
votes
1answer
32 views

MySQL Insert with Select

Trying to insert data to a table with values from different tables. INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref) select (select class.id as class_ref from class ...
2
votes
1answer
22 views

Is it possible to import a single mysql database directory?

I am somewhat new to mysql. I created some databases recently and, to back them up, I just copied the folders with those database names in the mysql data directory (i.e containing only db.opt, .frm, ...
2
votes
3answers
199 views

MySQL buffer pool usage + high load

We've got a quite powerful server: HP ProLiant DL360p Gen8, 2xIntel Xeon E5-2630 2.30GHz, 128GB of RAM. The whole data set should fit in memory: mysql> SELECT ...
2
votes
2answers
58 views

Should I drop a foreign key if I use a soft delete?

"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have ...
0
votes
0answers
20 views

Mysql query with LIMIT much slower under RDS (5.6) than old 5.1 system

We inherited an old legacy system running on mysql 5.1, and moved it to RDS running 5.6. One query is running much slower - it can take up to 20 minutes to run, where previously it took only a few ...
1
vote
1answer
29 views

MySQL InnoDB table accepts Non-Existent Foreign Key

Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page. Working on Windows 7 64-bit. ...
2
votes
1answer
26 views

Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE

I'm trying to do the following query in mysql 5.5 INSERT INTO countingTable( image_count, article_id ) SELECT COUNT( article_id ) AS sum, article_id FROM imageTable ON DUPLICATE KEY UPDATE ...
-1
votes
0answers
12 views

Error stored procedure Mysql

I have a table and every month need to delete data for new data are included. I made a stored procedure to automate this process. This table has about 3 million lines in a monthly period. This can ...
2
votes
2answers
76 views

Do MySQL optimizations hold for MariaDB also?

I am a developer who will be starting a new project soon in which database performance will be crucial. The current candidate for the backend is MariaDB. I have worked with MySQL and MariaDB before in ...
0
votes
0answers
24 views

Restore data from MYSQL Bin log

I am trying to restore some data from mysql bin log file. But when it encounter some special characters, query gets break and I am not able to continue after that. Is there any way to handle special ...
0
votes
1answer
64 views

MySQL select with left join query

I have following code. select class.name as classname, class.hours as hours, teachers.first_name as teacherFirstName, teachers.last_name as teacherLastName from teachers,class left join ...
1
vote
1answer
24 views

Loop through all users in MySQL and add REQUIRE SSL flag

How do I loop through all users (we have > 3000) in a MySQL database, and add the REQUIRE SSL flag to the every account? I don't want to modify the existing users permissions, host, or password, ...
2
votes
1answer
58 views

Understanding JOINs and why the syntax works the way it does

Heyo, I'm confused as to how this works: UPDATE original SET price=(SELECT price FROM new WHERE new.id=original.id) WHERE id IN (SELECT id FROM new); What it does is updating an original table ...
-4
votes
1answer
36 views

How to run mssql server dependent application to mysql workbench [closed]

I am very new in mysql workbench. I have created an application in c# which uses sql server 2008 r2 database. Now i have migrated my database on mysql workbench. i want to run my application now. My ...
2
votes
1answer
129 views

mysql privilege to kill processes of other users

I have a percona mysql database. The version is 5.5.29-29.4-log. I have created a user called admin which should be able to kill processes initiated by other users, i.e worker. GRANT SELECT, ...
2
votes
2answers
248 views

Changing data type from mediumint to int without downtime

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need ...
0
votes
1answer
24 views

move content from one table to another table and add a new value

I need to move content from one table to another table, this part is done: INSERT INTO `insc_mwc` (`nome`,`empresa_idempresa`,`cat_mwc_idcat`,`points_pontos`,`n_registo`) SELECT ...
0
votes
1answer
27 views

How to update MySQL with CentOS 6 in most unintrusive way - in regard to perl and PHP packages? [closed]

For my few hobby projects (web games + forums) I have been using CentOS 5 (then 6) with Drupal and PostgreSQL plus few custom PHP and Perl scripts written by mysef. Since PostgreSQL version delivered ...
1
vote
1answer
24 views

MySQL: can a table get stuck in the table cache?

I've asked this question on the ServerFault stackexchange, someone directed me here. I'm a PHP developer and am responsible for a set of PHP scripts that run on a shared host running FreeBSD, that ...
0
votes
3answers
43 views

Redesign basic Mysql storage architecture

We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this: capture_table ---------- ...
0
votes
0answers
26 views

Show weeks based on the date range

I have a table that has Start Date and End Date. I am very new to MySQL, I was wondering what MySQL query can I use to get all the data from Monday to Friday and show in which week it falls. like ...
0
votes
1answer
24 views

MySQL cluster replication: Slave cluster should not store any data except data replicated from master cluster?

I am researching MySQL cluster replication. I read a section "We assume that the slave server or cluster is dedicated to replication of the master, and that no other data is being stored on it" in ...
0
votes
0answers
26 views

Percona MySQL LDAP authentication fails through .NETmysql connector

Basically MySQL uses its native authentication, where the username and passwords are stored in mysql.user table. MySQL authenticates user against the credentials stored in mysql.user table. Our ...
0
votes
1answer
23 views

score ranking no tie, if tie order by score submit date

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score SubmitDate So far so good. The question is: How do I get what's a users rank EVEN THEIR SCORE IS ...
1
vote
1answer
51 views

How to loop SQL query in mysql?

I have a SQL query as following: SET @thedate := CURDATE(); create temporay table tmp1( ... where thedate=@thedate ); create temporay table tmp2( ... where thedate=@thedate ); delete from tbl1; ...
1
vote
1answer
52 views

MYSQL Query - Unexpected result in text messaging database

On a text messaging database I have built, I am trying to return the total amount of messages a user has sent and the total number of messages a user has received. I have the following code: SELECT ...
0
votes
1answer
33 views

Read Replicas with multiple instances of MySQL running on different ports

I have a specific need to create a single linux server which can host 4 MySQL instances with read replication. Here are a few questions I cannot seem to find answers to: Can you setup read ...
0
votes
0answers
23 views

MySQL: Select a subset/range of a result

Assuming there's an application for running events (marathons etc.). An event (i.e. a run) has an ID and serveral segments, divided by checkpoints. So here's what we might get: SELECT * FROM segments ...
0
votes
1answer
29 views

How to delete all the tab and enter in a table?

I have a big table with several \n (enter) and \t (tab) characters scattered in my data It looks something like the following: col1 col2 col3 data \ndata data \ndata data \tdata I need to ...
0
votes
0answers
8 views

tables federated in mysql say in use

I have a mysql server with 30 databases which there federated tables. At one point in the column appears collate me "in use" and I can not use those tables. I appreciate any help.
1
vote
2answers
37 views

Merge output of two Queries MYSQL

I have a mysql table with the following schema: +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
29 views

MySQL: What is SQL profiler, does it different for MySQL?

Whenever SQL profiling comes in mind, focus goes to two below sections: GUI tools like - MONyog, Neor Profile SQL set profiling =1; Execute Query; show profiles; show profile; I am really confuse ...
1
vote
2answers
24 views

What's the best design for a logical structure that contains different properties of various types for different entity categories? (MySQL or NoSQL)

I'm designing a MySQL database that needs to contain a list of products. Those products, based on their category, will need to have a list of various properties. Let's say all products in category ...
0
votes
0answers
16 views

How to manage MySQL database with Oracle Fusion Middleware?

Is it possible to connect and manage a MySQL database with Oracle Fusion Middleware?
0
votes
1answer
40 views

Replace missing data in mysql database

I have a database that is generated from a csv file and it contains time stamps in it, the timestamps are sometimes missing from the database at certain hours, so i need to get in data from the ...
0
votes
0answers
7 views

MySQL: Is it possible to run a search on a specific database for a specific string?

I need to find a specific email address in a database. I don't know in which table the email is set so I'd like to run a search on the whole database. Is that possible? Thanks
1
vote
1answer
43 views

Detect data spikes (loops) in MySQL database and ignore these

i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical ...
0
votes
0answers
42 views

Why Percona MySQL is crashing again and again

We have Percona MySQL Cluster running with three nodes on CentOS. Server : Percona-XtraDB-Cluster-server-56-5.6.19-25.6.824.el6.x86_64 One node is crashing repeatedly. Thread pointer: 0x18910ba30 ...
0
votes
1answer
35 views

Quickly etablishing if a remote MySQL connection is valid using PHP

I have a hosted website (Namecheap Pro shared hosting) that connects to a remote MySQL database using a read-only user privilege. This remote DB is updated by a separate system. The website works ...
1
vote
1answer
79 views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
0
votes
0answers
22 views

All MySQL databases are unavailable because of a broken table

I am using phpmyadmin to manage my MySQL DBs on local machine. I cannot see db/table contents and/or structures since one of the table in a DB has been broken. For example I have a DB which has 90 ...
1
vote
1answer
97 views

Summarize from unlimited level depth parent-child in mysql

I have 2 table master coa and coa transaction in MySQL like this, master : id | coa | id_parent | ----+------+-----------+ 1 | 1 | NULL | 2 | 11 | 1 | 3 | 111 | 2 ...
0
votes
0answers
25 views

Difference in dates between last 2 rows group by user

I have a orders table where each it has a column ord_user_fk which is the user and a ord_date which is the order date. One user can have many orders. I am trying to find the difference between the ...
1
vote
1answer
34 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
1
vote
1answer
42 views

MySQL cluster: Failover between data node

Today, I do some expirements for understanding MySQL cluster better. MySQL cluster works fine: ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration ...