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
0answers
24 views

Update on a table on MySQL Innodb seems to hang / lock forerever

When performing an update on a MySQL database for an InnoDB table, the update sentence never finishes. This is the update sentence from the MySQL query log: UPDATE `bb_plan` SET `creation_date` = '...
-1
votes
0answers
19 views

When to use different databases for social network site? [closed]

For a social network site that wants to expand to the size of facebook, what database do you suggest using at what time? for example, should messaging use mongodb or mysql? when should redis be used? ...
1
vote
1answer
127 views
+50

Efficient workflow/queries to save unique information?

I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and ...
-3
votes
0answers
24 views

can mysqli handle billions of query at the same time? [duplicate]

My curiosity is How big can a mysql database grow and can it handle billions of queries at a go? One more thing, what's the best practice (database design wise) to when creating a portal application ...
0
votes
1answer
60 views

Reset value each year with a trigger

I want to set up a trigger which will increment a value (based on previous record). But this will also reset the value if the year of date_creation is different of the current year. ref  &...
0
votes
0answers
30 views

Grouping Based on Column Consecutive Values

I have table with let say the below 3 columns Date OfferId Title 4/29/16 - 5/19/16 123 title1 5/20/16 - 5/30/16 123 title2 5/31/16 - ...
0
votes
0answers
11 views

Group by but preserve statistical probability of value in limit

Consider a table in which a specific column is not unique: name -------- john paul paul george ringo paul paul paul paul How might one return N users by random, but preserve the statistical ...
0
votes
0answers
25 views

My SQL Server Upgrade from 5.6.12 to 5.6.22

I have a MySQL Server 5.6.12. I have this server as a windows service. When i upgraded this, i did the following. I stopped the service I copied 5.6.22 server folder, removed the data folder. From ...
0
votes
1answer
31 views

Is there any impact to changing IP address of MySQL Server?

We are changing the network and the IP addresses will be changing. Will there be any impact to MySQL running on the server? I don't use the IP address to reference the server. Instead I use the ...
0
votes
2answers
40 views

Query to retrieve index details for a particular column in all tables

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to ...
0
votes
0answers
28 views

In which cases is a full table scan preferable to an index scan with low cardinality?

Are there real-world scenarios where to perform a FTS is preferable to scanning an index with very low cardinality? Why would MySQL do so? Doesn't an index scan always involve reading less data (...
0
votes
1answer
34 views

MySQL MyIsam Compressing tables

Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows) Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt ...
1
vote
1answer
37 views

Increment fixed value in select clause

How might one increment a "fixed" value in the SELECT clause of a query, so that each row has a different value set? For an obviously not working example that illustrates the intention: SELECT ...
0
votes
1answer
54 views

Extremely slow requests on small innodb tables

The situation is the following. Two InnoDB tables, one has 2 records, another one - 281 - small ones, insert and update requests may last up to two (!) minutes. Dropped db, created again. The ...
0
votes
1answer
25 views

exporting some table table from oracle DB to Mysql DB

I need to export some of the tables from Oracle DB to Mysql DB..i dont have any previous experience of export and Import. Could some please tell me how to do that ?
0
votes
1answer
55 views

Queries per second avg

I have Installed MySQL Server 5.6.23 on Centos server and the configuration of the server is 16 GB RAM 8 Core CPU op-mode(s): 32-bit, 64-bit When I check the status of the server it is showing me ...
0
votes
0answers
52 views

Mysql: select inside select block of a view [closed]

I was hoping you have an idea here: I got a code which of course doesnt compute because of some errors like the nested select inside the select, etc. Is there any way of solving this? CREATE VIEW `...
0
votes
0answers
13 views

BigQuery daily cron jobs

In one of my PHP application, I need to show a report based on the aggregate data, which is fetched from BigQuery. I am planning to execute the queries using a PHP cron job then insert data to MySQL ...
0
votes
0answers
17 views

connecting a java file with mysql [migrated]

I am getting these error in Linux, how to fix it? Exception in thread "main" java.lang.ClassNotFoundException: com.mysql.jdbc.Driver at java.net.URLClassLoader.findClass(URLClassLoader.java:381) ...
0
votes
1answer
23 views

DBIO Error: Utility 'mysqldump.exe' not found in MySQL home 'MySQL Connector/Net'

I am using dbeaver 3.6.10 and I would like to export my database (MySQL). I select the database > right mouse click on it > tools > export database > next > start I get the following error. IO ...
0
votes
1answer
28 views

Limiting the input to a field as some pattern

i have a table student having field admission no. i want to restrict its input to the pattern '-/----/--'. If the input does not match this format then it should show an error. I tried doing this ...
1
vote
2answers
27 views

MySQL CREATE INDEX slowing down

My analytics table has 190M rows, about 150GB ins size. Storage engine is MyISAM with key-buffer-size=16G myisam_sort_buffer_size=2G myisam-max-sort-file-size=200G. Machine has 32GB memory. While ...
0
votes
2answers
58 views

MySQL Optimization Suggestions for EC2 T2 Small 2GB RAM

I'm trying to optimize our MySQL configuration for our server. The reason I believe that MySQL needs to be configured further is because my log has shown that our SELECT queries are taking a long time ...
-1
votes
1answer
52 views

Specific Names not showing up in database query [closed]

We have a MySQL database with hundreds of names. Included are 3 entries for the name Down. These do not show up when we perform a php Search. The same search will find the entry if I change the name ...
1
vote
2answers
42 views

MySQL Stored Procedure - no of times executed

My question is simple, is there any way to calculate, number of times a stored procedure is called on applications or executed on MySQL server. For example: Procedure name: sptest Executing ...
2
votes
1answer
104 views

Compare two tables with same structure, but differing membership numbers

I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below. The ...
1
vote
0answers
30 views

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for ...
0
votes
0answers
44 views

Should I make a new table to store date?

I want to store date on which users sign up on my site. Since on a given date several users will be signing up, there will be repetition of data. Should I make a new table to store date to make it ...
0
votes
1answer
20 views

Is it possible to log mysql queries from other hosts in general log?

I'm having a task to figure out what are the hosts that are still running scripts that accessing our database directly so we can prevent that from happening(we only want our web app to access the ...
0
votes
0answers
48 views

Is there a clean way to extract data from this single-column value?

I'm attempting to extract data from a single MySQL column which contains data that looks like this: a:6:{s:15:"info_buyRequest"; a:3:{s:7:"product"; s:1:"1"; s:3:"qty"; ...
0
votes
0answers
44 views

mysql: Error reading file 'UNOPENED' (Errcode: 5 “Input/output error”)

I restore the backup of my main server daily in this other server. Today something went wrong and I received this message: mysql: Error reading file 'UNOPENED' (Errcode: 5 "Input/output error") I ...
0
votes
0answers
39 views

MySQL DB server had restarted automatically

We are using MySQL 5.6.27 version and Amazon AWS instance for a database server with the configuration given below: innodb_buffer_pool_size = 40G innodb_buffer_pool_instances = 40 ...
0
votes
1answer
46 views

Trying to fit indexes in memory: partitioning vs compression or neither?

I have a "transaction" table that grows at about 150k rows-per-day rate. 97% of user aggregate queries refer to the last 3 months of data and must run very efficiently. Trimming older transaction data ...
0
votes
0answers
32 views

MySQL Commands out of sync error

I am new to MySQL, normally use SQL Server. I have a MySQL server (version 5.1.73) and I have two databases, syslog and syslog_archive. There are three tables: syslog.logs syslog_archive.logs ...
0
votes
1answer
44 views

View created using Join Queries taking too much to load

I have written a query to create a view which i can use as a flat table in my application, which is having more than 30 inner join conditions across 25 tables. Now the problem is every time i am ...
0
votes
0answers
32 views

Unable to fetch data based on latest date and time

I have a table which stores application details. Applications are nothing but a set of data showing scores. Each application has 6 parts namely 1,2,3,4,5, and 6. So, in the database table parts can ...
1
vote
1answer
35 views

Inserting BLOBs through PHP are always 0 bytes

I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB. The first row there was inserted manually through phpMyAdmin. The second row was ...
1
vote
2answers
60 views

Fix MySQL server crash

We recently suffered a pretty severe server crash on one of our linux machines (Fedora Core 5), which has resulted in a lot of corruption of data, unfortunately the mysql database is amongst the data ...
0
votes
1answer
44 views

MySQL MVCC implementation

The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table: We have a relatively large table which we periodically scan reading several fields including a field named, ...
1
vote
1answer
52 views

MySQL57 is using 25% CPU and 10% Memory on Desktop

I do a simple left join query on two fairly large table using MySQL57. It takes a long time. I check my desktop's CPU and memory usage for MySQL. It only uses 25% CPU and 10% Memory on my Desktop of ...
0
votes
1answer
48 views

PostgreSQL to MySQL migration to use database for WordPress site - how to's?

I have a complex issue to solve, related to database migration. I have a website full of news to migrate that is currently based on a PostgreSQL database and written in PHP, without a CMS. I have a ...
2
votes
1answer
142 views

join to other table multiplied by loop foreign key in mysql

i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases . table nodes +-------------+--...
0
votes
1answer
41 views

How to migrate an Informix database from IBM AIX to MySQL on Linux [closed]

I have a migration activity where I have to migrate an Informix database which is running on an IBM AIX machine to MySQL running on a RedHat Linux machine. How can I do that?
0
votes
0answers
64 views

Why does CREATE TRIGGER lock a table in MySql?

What I've done? I tried to execute a CREATE TRIGGER statement on a table with about 4.x million rows. The trigger I tried to create was a INSERT trigger. And it looked like that the whole table was ...
4
votes
2answers
71 views

Behavior of transactions on multiple connections

I have a MariaDB database with InnoDB tables. I have two connections to the same database. One connection is using a transaction around a large chunk of work. The other connection is tasked with ...
0
votes
1answer
53 views

pt-online-schema-change — can't get it to stop quitting w/ “Threads_running=108 exceeds its critical threshold 100”

I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this: pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX....
2
votes
3answers
118 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...