All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

0
votes
1answer
43 views

How to handle HUGE 16GB SQL file?

I've abc.sql file exported from MySQL Table. This abc.sql file is 16 GB in size with around 28 million insert statements. I'm looking for option to store it in SQL Server Database. Couple of ...
1
vote
2answers
77 views

Index not used on partitioned table

We have a table similar to this one mysql> SHOW CREATE TABLE my_requests\G *************************** 1. row *************************** Table: my_requests Create Table: CREATE TABLE ...
1
vote
1answer
59 views

Composite Primary keys and auto increment?

i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways ...
0
votes
0answers
7 views

MySQL - Database Design : Encrypting user id and creating a mask for further internal references

For a food order delivery system that I am working on, I want to make sure my user's private information is well protected; from system admins too. So I thought of coming up with this concept of ...
2
votes
1answer
192 views

Mysql high cpu usage - cpanel

After an update from whm, I see a high CPU usage from MySQL (5.6). my.cfg: [mysqld] slow_query_log=1 slow_query_log_file="/var/lib/mysql/slow-query.log" long_query_time=1 skip_name_resolve ...
0
votes
2answers
46 views

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script. INSERT INTO params (extension, type, subtype, paramvalue) VALUES ('12345', ...
1
vote
1answer
151 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
1
vote
2answers
122 views

MySQL Junction Table as Foreign Key?

I have several tables: company, customer, company_has_customer, and transaction. company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK. In almost all ...
1
vote
1answer
153 views

mysqldump collation issues

I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment. The problem I am facing now is that our wp database after ...
1
vote
1answer
10 views

mysql replication and GRANT that doesn't quite grant

I am trying to set up mysql replication. All's well, but I see an error 1045 for the slave: mysql> show slave status\G *************************** 1. row *************************** ...
0
votes
1answer
482 views

How to check mysql password change and other events logs

Recently someone change my phpmyadmin password and drop database I think it was sql injecting. Now I want to find information about this like when, how and who change my password Please tell me ...
0
votes
1answer
17 views

Grand read privilege to the user but is not allowed to grant the same privilege to another user?

i am a noob and this is how far i got so far, thanks and also how would you do it if you want to allow user to grant the same privilege to another user. GRANT SELECT ON DATABASE_NAME.* TO USER;
0
votes
0answers
11 views

Speed up SUM() on a result with JOINS in MySQL

I have a query with a combination of 6 left joins and inner joins. The query gives a result of 25000 records in 112ms. When I do a SUM() the query takes 4s. A SUM() another table with 35000 records ...
0
votes
0answers
6 views

Mysql How do I insert multiple table with duplicate key checking?

I hope to this work. When UserAccount table duplicated, Out Parameter put -1. When UserInfo table duplicated, Out Parameter put -2. When all work is succeed, Out Parameter put LAST_INSERT_ID. ...
1
vote
1answer
9 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, (SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p.id) AS postlock_id, (SELECT COUNT(rn.id) FROM ...
1
vote
1answer
144 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
1
vote
0answers
5 views

Add index to mysql table with ndbcluster engine

I'm having trouble while adding index on mysql table with ndbcluster storage engine below is the error . [mysqlcluster ver. 7.4 GA] mysql> alter table `sks_staff_office` add index `pid_index` ...
0
votes
1answer
107 views

How to recover performance_schema after restoring from partial innobackupex backup?

I am taking a partial backup from a Master server, which needs to be restored on a to-be slave server. I only need one of the databases, and the mysql database. I am following instructions on ...
1
vote
2answers
2k views

Can't create file /var/lib/mysql/user.lower-test

MySQL database has been problem free for a few months. Today when I checked for upgradeable packages using the Synaptic Package Manager it came up with the various MySQL components (-common, -client, ...
3
votes
1answer
199 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) ...
0
votes
2answers
109 views

MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column?

Here's what I'm trying to achieve: use a trigger to divide an update query which updates more than 1 columns into several update query and each of the query updates just 1 columns. For example, the ...
1
vote
1answer
118 views

MySQL After Update Trigger apparently not running

I have the following After Update trigger on a MySQL table: CREATE DEFINER = 'root'@'localhost' TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD AFTER UPDATE ON ...
-1
votes
0answers
14 views

Backup whole database including procedures

I'm using MariaDB 10.1. I backup my database using mysqldump. My stored procedure, view, function and event definitions are not backed up. How to backup full database including tables, functions, ...
0
votes
1answer
127 views

What is the best way to update a Production database with Test instance changes

I have deployed a Mysql database into Production. As I work on the next release of software I am making changes to a test database. I now need to update our Production system without losing the data ...
0
votes
1answer
144 views

Replication Issue - Changing the replication-do-db filter

I have a server that someone has setup which are acting as a master on both database. Since they have been configured as both master on the same database, changes one on end has caused all sorts of ...
-1
votes
1answer
15 views

How to perform multiple checks on same column of same table in Mysql?

Table Structure : +-------+--------+-------------+------------------+---------------+----------------+ | recid | cartid | attributeid | attributevalueid | attributename | attributevalue | ...
0
votes
1answer
130 views

How to/Where to find MySQL templates for Toad Data Modeler

I have recently install Toad Data Modeler 5.3.4 and it only has Oracle data model templates. Where can I find and import the MySQL data templates so that I can create MySQL database models. I have ...
0
votes
0answers
18 views

Query that runs lighting fast in Postgres send MySQL to 100% CPU and takes forever [on hold]

I have a query that runs against a database that an industry association puts out every month. The query is complex in that it joins together 26 tables and brings back only 15 rows from among tens of ...
6
votes
1answer
11k views

UPDATE table based on the same table

I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
1
vote
1answer
189 views

MySQL query to create a Matrix view using pivot

I have a table with the following fields: p1 p2 s1 s2 where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The number of players can vary from 3 - 10. The table have the results between ...
0
votes
0answers
10 views

Connect to MySQL Webserver (not localhost)

I have a problem. And I hope someone can help me or point me in the right direction. I am working on a project and need to sync a MySQL database (running on PHPmyAdmin) with a MS SQL Server. I ...
3
votes
1answer
651 views

What does “cleaning up” mean in MySQL InnoDB transaction status and how to prevent it?

In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load: > SHOW ENGINE innodb STATUS; ... ------------ TRANSACTIONS ------------ ... ...
0
votes
0answers
12 views

Copy data from one table into other on some condition

I have two table as follows. Table : city city_id city_name state_id 1 Cachar 1 2 Darrang 1 3 Nicobar 1 Table 2 : locality pincode Address ...
0
votes
1answer
129 views

Error code 1064 when trying to add anything manually

So i tried adding via php and i noticed i got a error , so i went into phpmyadmin and tried adding it manually by clicking the SQL in phpmyadmin and write in INSERT INTO `test`(`IDtest`, ...
0
votes
0answers
7 views

Recursive Conditional trigger for insert in table MYSQL

user[ user_id, parent_user_id] user_id parent_user_id 1 null 2 1 3 1 4 1 5 1 user_assigned_role [user_assigned_role_id, role_id, ...
1
vote
3answers
623 views

Fastest way to do bulk import in MYSQL

I have got a question about the fastest way to copy a large number of records (around 10 million) from 1 table to another in MySQL. The target table has 1 primary key, 4 indexes and 3 foreign key ...
1
vote
1answer
255 views

Handling SQL updates for production deployments

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment. I run a pretty ...
0
votes
1answer
576 views

inno_db_buffer_pool_size and key efficiency

I have the following metrics from mysql workbench After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size. While the used buffer ...
-4
votes
1answer
34 views

Could someone explain how PHP, MySQL, SQL work together?

I feel so confused on what they are and how they work. From what I've read PHP is a server-side language. Therefore, it's a language basically designed to talk with the web server to get it to do ...
0
votes
1answer
273 views

Debugging lock time out with show engine innodb status

I am keeping getting this lock wait time out. Below is my current settings. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to ...
2
votes
1answer
556 views

MySQL replication error 1146

Any idea why there is '1146 - table doesn't exists' on 'CREATE TABLE IF NOT EXISTS...' statement. It actually happened only on one of the slaves (there are two in total): Last_SQL_Error: Error 'Table ...
1
vote
2answers
194 views

join same table twice and get the results of rows

i have table like wp_term_relationships object_id term_taxonomy_id term_order 194 2 0 194 7 0 194 8 0 194 ...
0
votes
1answer
461 views

Shell script for backup binlog files for every 8 hours in MySQL

I'm writing a shell script for point in time recovery for MySQL Instance. Our requirement is backing up Binlog for every 8 Hours (Daily 3 times) using mysqlbinlog and it should backup binlog starting ...
0
votes
1answer
15 views

PostgreSQL vs MariaDb On Read/Write when table has about 200M Rows [on hold]

I want to build a new web application with my collected data, but I don't know witch DBMS is best choose for my Project, MariaDB or PostgreSQL there is 2 table : categories and data more than 200 ...
-2
votes
1answer
117 views

How to store complex data using MySQL and JSP [on hold]

At present I am creating a small web application called slam book. We know slam page contains so many fields. By using MySQL tables it takes too many columns and leads to complexity for accessing the ...
1
vote
1answer
169 views

Using index on datetime field not working really

Here is my query select msisdn, sum(score) as score from contest_player_update_history_ideacontest6_35 where request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' ...
0
votes
3answers
70 views

What is the performance impact of indexing a frequently updated column?

I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer. I have a table with potentially a very large amount of inserts and updates. Any ...
3
votes
1answer
53 views

Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED

Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be ...
3
votes
3answers
65 views

How to format a phone number in a SELECT statement?

I have a database with table name student. I would like to display the register_number and phone_number of the student. The phone_number should be in the following format: +91-123-456-7890 and if ...
0
votes
1answer
67 views

Delete query does not end

I have a following table with 1 million records : CREATE TABLE `activity` ( `id` int(9) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `subscription_id` int(10) unsigned NOT NULL, ...