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

1
vote
0answers
27 views

Paginated query for modified times gets slow as table grows

I work on WordPress plugin which implements sitemap functionality. One of the steps is to build an index of sitemap pages for post entries. The logic required is roughly: determine last modified time ...
0
votes
1answer
12 views

MySQL binary log - write operation

Consider a scenario, where you have a MySQL master and slave server in replication. As we know, binary log enabled on the master and the slave reads from the binary log of the master. From MySQL ...
1
vote
1answer
52 views

Different types of users in one table, How to add data off different users

I have 5 type of users: Super Admin Coordinator Supervisor Student External Examiner All of them have some common attributes like ID, name, email etc. I have inserted them in one table user ...
0
votes
2answers
35 views

How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC?

How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone? I tried the following: CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ( ...
1
vote
0answers
19 views

phpMyAdmin export SQL not working with Linux VPS import

I am a newb, just gonna put that out there. I am currently on a shared hosting plan with Godaddy. I have started a free trial with DigitalOcean to see how I would fare with a Linux VPS using putty. I ...
1
vote
2answers
138 views

How to solve intermittent MySQL performance issues on simple queries?

We are currently experiencing an intermittent issue with UPDATE/DELETE/INSERT performance. Simple queries like the following take a long time: DELETE FROM ip_filter WHERE filter_id = '348'; ...
0
votes
1answer
12 views

Chained MySQL replication from Master->Master replicated pair

This is a pictorial view of what I have: Master 1<----->Master 2 | | | | V V Slave 1 Slave 2 The masters are never written ...
0
votes
2answers
36 views

MIN / MAX values with Date

I am trying to get the MAX and MIN values for the current month and the dates for these values. I am getting the MAX and MIN values but I am not getting the dates for these values. Instead I get the ...
0
votes
0answers
19 views

Should I use mysql stored procedure or regular inline statement?

So here is the problem: I have been currently working on a project that would be handling millions of data and thousands of records would be inserted as well as retrieved at the same time. Before ...
0
votes
0answers
7 views

What is the best practice of storing mysql connection details to use in ruby/python scripts

I have ruby and python scripts that connect to mysql DBs. They are controlled in github and can populate to different servers. They run in a schedule way (not adhoc). What is the best practice of ...
8
votes
1answer
7k views

Mail alert from MySQL when databases are edited

How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits. I have tried Triggers but its not working. Server ...
0
votes
1answer
113 views

MySQL table replication on the same server

I have one the same server (Linux) two databases: db1 and db2 and I want to replicate db1.table1 to db2.table1 I added the following lines to my.cnf and restarted mysql service replicate-do-table = ...
1
vote
1answer
175 views

Sync MySQL database and Quickbooks Database

I use Quickbooks as an accounting software. I also have a MySQL database where I record data on orders, customers, inventory, employees and etc. Is there a way for me to sync the two databases? For ...
0
votes
2answers
33 views

Encrypting a Database row-by-row

I have a large database (millions of rows in hundreds of tables) that contains sensitive data (Think SSNs, account numbers, etc). I am tasked with safeguarding the data and have chosen to encrypt only ...
2
votes
1answer
40 views

Prevent duplicate entries

I have log in form with Username and Password. $con=mysqli_connect(LOGINDETAILS); /*if (!$con) { die("Can not connect: " . mysql_error()); } */ $Username = $_POST['Username']; $pswd = ...
0
votes
4answers
41 views

MySQL: Delete all rows older than 30 days, but only if count more than one

I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will ...
1
vote
1answer
30 views

weekly moving average from current_date to the last 52 weeks

I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'. Criteria is : current_date is the end date. start ...
0
votes
1answer
34 views

(mysql) Database replication between 2 DB while 3rd DB as backup

I am not an advanced DB admin or designer. I am sure there are articles on this kind of scenario, probably too many. So I got confused and not sure which solution is appropriate/best for me. I am ...
0
votes
2answers
20 views

MySQL create index with LOCK=NONE still locks a table

I have the following MySQL RoR Migrations: class ReindexRpushNotification < ActiveRecord::Migration def up execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;") ...
1
vote
2answers
46 views

Should MySQL database be in dedicated server?

The application has about 10 databases. Both application and databases are in the same server since is client does not want any time delay that could possibly occur if the database is in a dedicated ...
-1
votes
1answer
23 views

systems hangs after specific time

I am having 2 applications (Java and PHP). Both are sharing same DB Server. After specific time, both systems stops working due to "Too many connections". I tried so many settings (increased ...
3
votes
1answer
124 views

Which is better MySQL search strategy, varchar x 2 or text column?

I have a MySQL database for an SMS application. For the sms_msg column, I initially setup varchar(255) expecting SMS to be short anyway. Now I realize that's not enough and some messages are getting ...
0
votes
1answer
29 views

Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

I have the several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution. 1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both ...
1
vote
2answers
34 views

Insert and select the row autoincremented id

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment. I know how to do it with a Stored Procedure. Will this line of code work: BEGIN ...
0
votes
1answer
27 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
8
votes
1answer
19k views

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` ...
3
votes
1answer
126 views

SQL Freezing on Temp Table Creation

I have done an update to my server yesterday to allow for tracking of user page views. These page views are sent to a database where it records their ip address, time and which page was viewed. On ...
0
votes
1answer
118 views

Zerofill Auto Increment - mySql

Ok, So I'm working on an app and I can't remember, for the life of me, why I set a zerofill on an Auto_Increment column when I created the database. And I couldn't exactly find any compelling info ...
4
votes
1answer
243 views

SQL query to compute longest travel time for each id number

I am a novice in SQL and I have a problem as follows. There are two tables TRNSCTN_TAB: CREATE TABLE TRNSCTN_TAB( `transactionId` int , `UniqueId` int , `transactionTimeStamp` ...
1
vote
1answer
27 views

Order by x DESC - but only continuous rows

I have a table with an ID (integer) and a text field (the name). My current query looks like this: SELECT id, name FROM table WHERE id <= X ORDER BY id DESC X is an ID I get from another query. ...
-1
votes
0answers
19 views

What are the general practices considered to constitute a good user id? [on hold]

I could not settle on a good pattern to generate my user ids for an upcoming project. On what basis are the user ids decided and populated. Would like to know if there are any patterns or formats or ...
1
vote
1answer
69 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 ...
0
votes
0answers
19 views

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

I have 2 tables uz_params and uz_params_exceptions where uz_params is supposed to be updated frequently by an external script. INSERT INTO uz_params (extension, type, subtype, paramvalue) VALUES ...
1
vote
1answer
22 views

What happens to mysql information_schema after switching to innodb_file_per_table?

We're switching our mysql dev deb instance to innodb_file_per_table and moving the data dir onto another mounted drive so we can free up space on our dev box. We've backed all our dbs up and about to ...
1
vote
2answers
2k views

Mysql closing immediately after new installation

I am using mysql as an backend for my webapp ,the software ran without any glitch but today it gave unexpectedly ERROR:2003 i tried many solution on the internet but none worked. so reinstalled the ...
0
votes
1answer
328 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 ...
0
votes
1answer
364 views

Quickly establishing 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
23 views

MySQL error 1593 on replication between 5.5 on WIndows and 5.6 on CentOS 7

I'm setting up replication between the live server (running Windows 2008 R2 and MySQL 5.5.19) and a dedicated replication server (running CentOS 7 and MySQL 5.6.30). The CentOS box does have MySQL not ...
0
votes
0answers
58 views

Order by 2 columns with different order behavior [on hold]

I didn't know how to describe the question in the title the best way, but here's my problem, consider this data: ID grade cost 1 1 0.45 2 1 0.3145 3 2 0.054 4 2 0.4165 5 ...
-1
votes
0answers
12 views

Ranking function when ties are averaged [on hold]

I am looking for a ranking function that averages ties rather than repeats them in MySQL.
0
votes
0answers
15 views

MySQL Innobackupex and Triggers, Events, Routines, Partitions

I am using Innobackupex (from Percona Xtrabackup) to take a physical backup of my InnoDB and MyISAM tables. I'm also backing up the system MySQL table, but skipping Information_Schema. Information ...
0
votes
0answers
32 views

How can I import a database from MySQL version 5.0.92 to Maria DB 5.5.47

I am using phpMyAdmin to export my database. Everything works on one server but on my other server it will not allow me to correctly import this data. It throws over 15,000 errors, which is pretty ...
0
votes
1answer
110 views

Moving data from one table (a million records in it) data to another table which in remote location? (MySQL)

I want to move data from one table which have more than 2 million records in size into another table which is in a remote machine, and that moving should support Logging Restart Exception handling ...
0
votes
0answers
12 views

Searching a text with space in mySql match against Query

I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result. My MySql Query is: SELECT count(id) as total , MIN(product_price) as ...
0
votes
1answer
534 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
0
votes
1answer
25 views

how to purge / shrink .idb tables

I know this has been asked many times but, what is the proper configuration to succesfully purge / shrink .idb tables? I have tried to use optimize table table_name but after the query the .idb ...
9
votes
3answers
62k views

select rows where column contains same data in more than one record

I have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record. Here's ...
1
vote
1answer
40 views

Setup MySQL Master to Master replication/automatic failover when each Master also has a Slave

Thanks in advance. I have always worked with SQL Server and never really had any exposure to MySQL. I now have to determine the preferred setup for MySQL when there are four servers involved. ...
8
votes
2answers
11k views

Best practices for backing up a MySQL DB

I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience ...
1
vote
1answer
467 views

Mysql query is very slow

I am developing a website like any other social networking site using mysql. I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is ...