MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
7k views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
2
votes
1answer
350 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
1
vote
1answer
135 views

how to connect mysql database in my vps from remote machine?

I have installed mysql database on my vps ip, and input the following command when to login mysql. mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%'IDENTIFIED BY 'passwd' WITH GRANT OPTION; mysql> ...
0
votes
1answer
160 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
2
votes
1answer
295 views

Bi-directional replication for the same MySQL table

AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA ...
1
vote
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
3
votes
1answer
97 views
+50

MySQL loading NULLs in numeric columns

MySQL 5.6.23, InnoDB I am loading tables from character delimited text files using the LOAD DATA INFILE commnd and I would like every field with a \N, which is the NULL character in this setting, to ...
1
vote
1answer
45 views

Why conditioning before and after join, gets different results?

I want to left join two tables and then return some of the records. My first query was: SELECT * FROM card LEFT OUTER JOIN user_card ON card.id = user_card.card_id WHERE card.package_id =14 AND ( ...
0
votes
1answer
21 views

Best approach to compare specific columns of 2 tables in different databases?

I'm running an application who does have a shorten version of a table in the backend database. Both application- and backend-tables are having the same primary keys and some specific key columns. ...
2
votes
1answer
18 views

Why to mysql stored procedure updates twice?

I have this stored procedure: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT) BEGIN DECLARE ...
0
votes
0answers
6 views

Benefits of compressed MySQL tables on INSERT-only workload?

We have a MySQL table that is 18gb of email record blobs (the text of every email sent stored in a blob). There are several million records and each one is about 5kb. The only operation on the table ...
0
votes
1answer
151 views

MySQL - How to see the queries executing by slave io thread

We have a strange problem with MySQL replication. We have observed huge load and IO wait on server while replication is in progress. We can have information of the queries from bin-log by using ...
2
votes
1answer
183 views

Randomizing the Auto Increment in Mysql

We have a database, where we insert data into table whenever we receive an order from our services. We use auto_increment to generate the ID and assign the order with the same. This is usually same ...
0
votes
3answers
334 views

Database structure for hierarchical quiz?

I'm doing a personal project where I have a quiz with a number of boolean questions. Each question will have a two answer choices "true" and "false". Based on the selected answer, a new question will ...
0
votes
1answer
34 views

Adding columns & index to a huge table

I'm working with a Rails app backed by a MySQL database. One of the more-frequently used models is backed by a table with over 200,000,000 rows (and growing). We've been aware for a while that the way ...
0
votes
0answers
8 views

mysql 5.5 statement based to rds mysql mixed replication

I am moving to rds mysql 5.6 that has by default mixed replication. I am moving from 5.5 where I have statement based replication. I was wondering if i enable replication between them i.e make rds ...
4
votes
2answers
90 views
+50

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
0
votes
2answers
2k views

Mysql Server Login and Permission Deny Error

I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!). Also not able to ...
0
votes
3answers
30 views

Error trying to SET a field when two fields in different tables match

I am trying to set catid to 584 whenever the rows in the tables ykz2d_crmery_deals and ykz2d_maps_integration have the same company id using the following query: UPDATE ykz2d_maps_integration SET ...
0
votes
1answer
21 views

Talend collation issue in mysql

I am using Talend to backup MSSQL TO MYSQL Server. Everything goes well, I have a connection, data flows from one db to the other... however I cannot correctly copy diacritic signs. On MSSQL I have ...
0
votes
2answers
2k 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 ...
3
votes
2answers
134 views

ALTER TABLE … ROW_FORMAT=Compressed times out

I'm trying to change a table with the command: ALTER TABLE ... ROW_FORMAT=Compressed This fails after 15 minutes, the log says: InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We ...
1
vote
1answer
49 views

Trying to restore users/passwords for a MySQL server

I had a system failure, but I managed to save the /var/lib/mysql library on my linux system. I have since rebuilt a new Linux (Ubuntu) system (LAMP). I copied the old /var/lib/mysql/mysql folder to ...
0
votes
2answers
156 views

why does mysql query running slower on server?

select glpi_tracking.id AS gi, glpi_tracking.date AS gdate, glpi_tracking.closedate AS cdate, glpi_tracking.contents AS gc, glpi_followups.tracking AS gft, glpi_followups.date ...
0
votes
1answer
27 views

MySql Insert signal if where clause fails

As part of a chained sequence of transactional queries, I'm inserting values that exist in a temporary table into a new table. Every query is validated for errors in PHP using: if ...
0
votes
1answer
187 views

Update query in a trigger

I have a configuration table: Table: refdatecol - id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
1
vote
1answer
24 views

Is there another/better way to create and maintain large amounts of procedures?

So I've decided to take a step back, and scrutinize the way I implement procedures on my databases. I'll list what I have done, give my reason for doing so and hopefully I will be told that I'm a ...
1
vote
1answer
4k 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
2answers
29 views

MySQL “case as column” question in WHERE clause

I'm setting up a new column with this case statement: SELECT * , CASE t2.field_max_occupancy_value WHEN 'one' THEN 1 WHEN 'two' THEN 2 WHEN 'three' THEN 3 WHEN 'four' THEN 4 WHEN 'five' ...
0
votes
1answer
304 views

How to automatically Reset Query Cache for improve table cache hit in MySQL?

I have a VPS running CentOS 6.4 and 2.5GB RAM, Intel(R) Xeon(R) CPU. Its MySQL version is Server version: 5.5.35-cell. I have two active wordpress sites and two inactive sites (inactive sites use for ...
0
votes
1answer
41 views

Filter from the same user times

i have a table with user id-s, and many times for the same users, like this: id time 1 13:00:01 1 13:00:01 2 13:00:09 1 13:00.23 1 13:00:24 2 13:00:11 2 13:00:11 I ...
0
votes
1answer
225 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
0
votes
1answer
24 views

Showing all values when at least match a given one

Given three tables items, genres and itemGenres; how can I filter out the items that have at least the given value while also outputting the rest of its genres. With this data INSERT INTO items ...
1
vote
3answers
36 views

guideline for calling optimize table after alter table?

Should I be calling 'optimize table' when changing datatypes of columns, adding indexes or dropping indexes, assuming a table with millions of rows, heavily used, both for reading and writing ...
0
votes
1answer
873 views

#1044 - Access denied for user '#username'@'10.%' to database '#database'

I'm trying to connect my webpage to mySQL with ipage hosting. When I copy the snippet: <?php $link = mysql_connect('username.ipagemysql.com', 'username for mySQL', 'password for mySQL'); if ...
2
votes
2answers
37 views

How can I Search for a string in all fields across all tables of a MySQL database?

I want to find a string and I don't know exactly which column and which table. How can I find it? Thanks.
1
vote
0answers
20 views

Mysql binlog warnings [closed]

My system happens to write a lot of unsafe statements to the binlog and the result is a lot of IO wasted. I found that turning the warnings off in the log fixes the problem but i was wondering if ...
0
votes
1answer
20 views

Unable to show binary logs how to remove logs from mysql?

Server version: 5.1.50-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show binary logs ; PROBLEM Data directory is full I want to remove ...
0
votes
1answer
150 views

MySQL Subquery Returns Null In Stored Procedure, Works Fine In Query

I have a subquery in a MySQL Stored Procedure where the value returns null even though the data is clearly there. Pulling the subquery out and running it works fine. I've checked the parameter values ...
0
votes
1answer
34 views

How to insert multiple values in a specific field in a table using mysqli?

I am working on a project and I I have a scenario like this: I have many field in my table : table_name : transaction_tbl -id -name -description -ref_number : text(datatype) In my inserting here is ...
0
votes
1answer
14 views

MySQL database dump bombs out Error 1064 when using LOCK TABLES

This specific machine is running MySQL version 5.0.27. I have tried to get a full dump of this database several times and each time it bombs out. Tried getting just a structure dump of it and this is ...
6
votes
3answers
3k views

How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
0
votes
1answer
36 views

Multiple left join where not in another table

I have the following tables (there is more detailed data in each one, I just put the minimum to create a sample): __________ _____________ _____________ _____________ | person | | provider | | ...
0
votes
2answers
59 views

How can i update my database from another database using temporary tables?

I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it? This my temporary table : use data_pro; ...
0
votes
0answers
24 views

Is there an equivalent in postgresql to MySQL's “safe update mode” or “safe delete mode”?

Is there a configuration or flag in PostgreSQL I can turn on to disable bulk updates and bulk deletes, similar to MySQL's "safe update mode" or "safe delete mode"?
1
vote
2answers
1k views

mysql.sock not found on CentOS 7 machine

I am getting the following error when I try to log into mysql from the CentOS 7 terminal of the machine where mysql is installed: ERROR 2002 (HY000): Can't connect to local MySQL server through ...
1
vote
1answer
352 views

Syncronize mysql databases between local and hosted servers automatically

We have many website with Development , Staging and Production Server. we have many developers for many projects, we need a solution to synchronize the database with developer database with staging ...
0
votes
1answer
12 views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
0
votes
1answer
122 views

How to track only DDL statements executed in MySQL

Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements. I had gone through the ...
0
votes
0answers
21 views

How to get one result with multiple data in other table [migrated]

My problem is this. I want to put table1 in combo box with one result with different data from table2. I hope you can get my point.. sorry.. :) My tables: This is my coding. ...