MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
22 views

How to cache query for re-use in following UNION of queries?

I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB). This is the query I want to save: SELECT `event`.id, event.name FROM ...
0
votes
1answer
25 views

How to setup large database access/cache on LAMP environment? [closed]

My application is getting very slow I guess because of the ever growing database (30+ tables some with 50,000+ rows). Using CodeIgniter's model tier to call MySQL database, I optimized queries & ...
1
vote
1answer
27 views

How can I speed up this simple query

I am trying to speed a query that grabs data to be used to display a graph for a research project. It's currently VERY slow. It has 18295603 items only and takes about 20s per query. Here is an ...
1
vote
1answer
41 views

MySQL: IF statement check is a table exists

I run in the following issue and can't figure out how to resolve it. delimiter # create procedure pr1() begin declare v1 double default 0; start transaction; ...
0
votes
2answers
29 views

Create table on a Master - Master config avoiding replication

I have a Master-Master configuration. What I want is to create an auxiliary table only for a report project so it's not necessary to replicate it. Is there a specific way to make this in the CREATE ...
0
votes
0answers
5 views

ODBC/MYSQL Insert a Query Result from ODBC to a databse in MYSQL [migrated]

I'm connecting to a cloud database through a ODBC connection: $conn=odbc_connect('MYDATABASE','',''); if (!$conn) { exit("Connection Failed: " . $conn); } $sql="SELECT DATETIME_ID, NAME, Sum(CNDROP) ...
0
votes
0answers
15 views

MySQL - Checksum won't match between master and slave hosts despite dumping and reloading the table [duplicate]

I am playing around with table checksums and I am puzzled as to why I cannot get two checksums to match. I have two hosts, one being a slave for replication. I have a particular problem table that ...
0
votes
1answer
30 views

Log all table updates for all tables in MySQL

Is there anyway I can get the last updated time of a set of tables? By last updated time, I mean not only when the table's schema changed but when any of the rows changed or even when rows were ...
0
votes
1answer
44 views

Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dropped my UNIQUE KEY index 'myindex' on a table and I wanted to change the UNIQUE KEY to something else, so after dropping it I did: alter table mytable add unique key anykey (add1, city, state); ...
0
votes
0answers
11 views

Setting the Relationship as a Continuous Line Without Changing the Qty INT into Primery Key on SQL

Now it is required to make the following database. In the following, if I tick the checkbox of "Identifying Relationship", the line will become continuous line but the key type will also be ...
0
votes
1answer
48 views

sql or hql query to find all parents of a particular record

I need a sql or hql query to find all parents of a particular record, that should work in oracle as well as mysql, for eg if I have the id: 201, the parents would be: 188, 187, 1. any help would ...
2
votes
1answer
37 views

Should I use utf8_unicode_ci or utf8_slovak_ci?

I was using utf8_slovak_ci collation until now, but I read that utf8_unicode_ci sorts very wide range of languages. Does it mean, I do not need to use separate collation for every language and I can ...
1
vote
1answer
27 views

Deciding which MySQL execution plan is better

Background: I have a query that I'm trying to speed up, involving 3 tables: omgenvelope has 5 million rows omgcust has 195 rows omginput has 35836 rows I'm obtaining the distinct omginputs ...
0
votes
0answers
27 views

mysql advanced query

I'm sorry for my English. I have a problem with db mysql. I have a table like this id|rat|time 1 |2 |12:00 2 |0 |12:55 3 |0 |13:05 4 |6 |13:45 5 |0 |14:00 6 |8 |15:00 7 |0 |16:30 8 |9 |17:00 ...
0
votes
1answer
30 views

How do I organize tables for simple lottery game? mysql [closed]

I am building a simple lottery web game that will let users pick a number that is in between that weeks given range. A number will be randomly select to determine the winner. I have a table for users ...
0
votes
1answer
23 views

Is it possible to modify an existing trigger definition in MySQL?

I am wondering if it is possible to update a trigger definition in MySQL. For example, I have a trigger T and I want to add some new functionality to it. My assumption is that I need to drop and ...
0
votes
0answers
22 views

How to handle too many instances of multiple tables?

If there is a table accessed by multiple DB threads ( for example 100) at a time then how many instances of that table will be opened? If I run show open tables and see that in_use column value of a ...
1
vote
2answers
44 views

How to swap roles of Master-Master?

I've just taken over the responsibility of managing our MySQL databases. We're running Percona Server (GPL), Release rel24.1, Revision 217. Our setup involves two servers both running as master and ...
0
votes
1answer
25 views

Why does my MySql Savel replicate changes that are neither in binlog_do_db nor replicate_do_db?

I have a standard simple master slave replication that usually runs fine. But sometimes, all of a sudden, I get replication errors on the "phpmyadmin" table. I have explicitly only specified one ...
0
votes
0answers
23 views

MySQL check if user has the required privileges before execute CREATE query

I'm working on a get started wizard in my application, and I've come to the part where the user needs to create tables in order to proceed. That can be done in two ways; either copy/paste the provided ...
2
votes
0answers
30 views

Mysql search matching multiple values in multiple columns

I need to speed up a search. Currently we let our users input a name: John Doe Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms ...
0
votes
1answer
53 views

MySQL uses more than 500% of the CPU

Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU. I'm getting a lot of these errors: 1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file ...
0
votes
1answer
19 views

Identifying root cause of slow query outliers from pt-query-digest?

I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of ...
0
votes
2answers
29 views

Updating a table with a single UPDATE statement vs several UPDATE statements

CREATE TABLE `messages` ( `mes_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'номер объявления', `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата/время дачи/изменения ...
0
votes
0answers
12 views

Uninstalling phpMyAdmin after deleting associated MySQL Database

I'm new to MySQL and phpMyAdmin so please forgive me if this is a stupid question. This morning I created a MySQL database and user on my web server. I then installed phpMyAdmin onto this database. I ...
0
votes
0answers
24 views

MySQL Enterprise Monitor - Query Analyzer configuration problem

I'm using MySQL 5.1.69. protocol_version 10 version 5.1.69-log version_compile_os redhat-linux-gnu I have a problem with configuration/using MySQL ENTERPRISE MONITOR. I don't see data in ...
0
votes
0answers
16 views

Merge colums from two different databases using phpMyAdmin

Is there a way to: Replace all the data from database1 > table1 > column2 With Data from Database2 > table1 > column2 ONLY under the condition that the ID value in database1 > table1 > column1 ...
0
votes
0answers
24 views

MySQL moving large data from one schema to another

Good day, I have a DB schema that has a single table that grows about 30 million rows per week, or about 25GB. Every day we hit our limit of the data that can stay there so we move the data on to ...
-4
votes
1answer
59 views

When you type an SQL query in interpreter, what does happen? [closed]

Actully this question is asked in an interview from me . When you type an SQL query in interpreter, what does happen? How is the query processed?
0
votes
1answer
30 views

Sudden periods of performance degradation

For the past couple of days, Ive been observing periods of huge performance degradation. CPU usage on our master MySQL server (Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version ...
0
votes
0answers
20 views

MySQL: What should I do if too many tables opened during peak hours?

In Peak hours: When I run below statement, I found around 100 tables out of 250 have opened. show open tables where in_use>0; There are around 10 tables for which in_use value are >50. Storage ...
0
votes
1answer
40 views

Optimisation and Performance Mysql , Zend Framework 2 and Doctrine 2?

i'm facing some problem with performance in an application using zend framework2 and doctrine 2. i would like to optimis it but i don't know from where to start , i read some many questions/ answers ...
1
vote
1answer
23 views

Do threads in cache still use memory?

I was studying about how and how much memory allocated to each connection thread and found below MySQL tutorial: http://dev.mysql.com/doc/refman/5.1/en/memory-use.html There is a point mentioned as ...
2
votes
1answer
40 views

3 minutes to import 327Mo database

We do database import a lot of times per day on a server with those specs : Xeon E5-1620v2 4c/8t @ 3,8Ghz 32 Go DDR3 ECC 1600MHz Hard Drive 2x 3 To SATA3 MariaDB 10.14 with XtraDB Given a 327Mb ...
2
votes
3answers
95 views

mysql service takes too much memory

i have a mysql 5.6.19 on linux server, and it takes about 8 Gb of memory. That is much more than i expect. There are about 150 db, each with 700 tables. All of them are innodb. We do not care about ...
1
vote
1answer
34 views

Simple query hangs on a specific column condition

I have two servers, and I a dumped the database of the first to restore it on the second. Everything works great, except that on the second server, I can't do any selection based on a specific column ...
1
vote
1answer
38 views

Shut Down MySQL during InnoDB rollback after crash

I am puzzled once again. We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is ...
0
votes
0answers
13 views

Handle a Search on 2 Big Tables

Hello i want to realize a exchange system with 2 tables. 1 Table for "the user is looking for" 1 Table for "the user has to share" the Tables are very simple id bigint user_id bigint object_id ...
0
votes
0answers
46 views

MySQL 5.6: explicit_defaults_for_timestamp

I have the following replication topology: DB1 (MySQL 5.5) -> DB2 (MySQL 5.6, explicit_defaults_for_timestamp = 1) -> DB3 (MySQL 5.6, explicit_defaults_for_timestamp = 1) - "date" field: ...
1
vote
1answer
34 views

Updating mysql trigger via information_schema — access denied?

I have some wrong "definer"s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of: use information_schema; update triggers set ...
0
votes
1answer
26 views

Clarification on MySQL Workbench Options File Configuring

When in the MySQL Workbench tool, configuring the "Options File," I see a drop-down in the lower right corner with the options: client mysql mysqld By default, 'mysqld' is selected and I see my ...
0
votes
0answers
15 views

connecting Pervasive PSQL and MYSQL

I have Peachtree/Sage 50 on the server and trying to sync/integrate with MySQL, I already created a ODBC datasource but how can import all tables in sage to MySQL. What im trying to accomplish is to ...
0
votes
0answers
30 views

innodb fulltext Table handler out of memory

I am running mariadb 10 and have a large innodb table with a fulltext index. When searching I am regularly getting this error ERROR 128 (HY000): Table handler out of memory Is there some way I ...
0
votes
1answer
30 views

How do you record & replay mySQL queries for performance analysis?

We would like to record a real workload (i.e. the queries run against the database) so that we can replay the same (web application) workload against a clone of the database with different mySQL ...
0
votes
1answer
20 views

Find points within a distance using MySQL

I have a MySQL table with State Name,City Name,Zip Code, latitude and longitude of the user. I would like to get a list of user who are inside the circle or square of a given latitude and longitude ...
0
votes
0answers
24 views

MySQL master slave replication

I Had mysql master/slave replication working properly but suddenly replication stopped and accidentally someone has changed mysql.binxxx file in salve so i am getting this error ERROR: 'Got fatal ...
0
votes
1answer
21 views

1000 visitors per day have my web page and MySQL scratching at certain times and my.cnf [closed]

1000 visitors per day have my web page and MySQL scratching at certain times. "tuning primary" and I edit my my.cnf file is as follows. In what way do you optimize it the best I can. nice = 0 ...
0
votes
2answers
51 views

Complex query: find all users who rated all the movies rated by user_id='1234'

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated ...
0
votes
1answer
65 views

Select Multiple Values from Same Column; one sql statment

I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same ...
-2
votes
2answers
43 views

Select data from one table with id from another [closed]

so as text is saying I need to grab user id by name from one table and then select count from another table by that ID. is it possible? users table ID | User online table ID