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
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 ...
-4
votes
0answers
12 views

how to create a migration script in python, [closed]

The data is from MySQL and I convert it into csv file. I want to create an executable script. How to create an executable migration script?
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
38 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
1answer
53 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
51 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
117 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 ...
1
vote
1answer
14 views

MYSql Full Text Boolean search and relevancy score improvements

I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant. Query: SELECT phppos_items.name,MATCH (phppos_items....
1
vote
1answer
17 views

Where do i change the my.cnf on mysql cluster

I'm trying to change the max_allowed_packet setting on mysql cluster (version 7.4.8) - i have 5 nodes 2x SQL Node 2x Data Node 1x Managament Node I have updated the my.cnf on all of the servers -> ...
-1
votes
2answers
60 views

Is there an SQL statement that I can run in MySQL that returns the location of my database(s) on my server? [closed]

I did a bit of research online and it looks like I need to have a column (or something) in my database called "sys.master_files" but I don't know what that consists of or how to create it.
1
vote
0answers
23 views

Mysql replication, master_log_pos sync but tables not update

First, my replication config on Master: replicate-do-db = mydb binlog-do-db = mydb expire_logs_days = 2 max_binlog_size = 100M binlog_format = MIXED log-...
1
vote
1answer
20 views

Is it possible to reorder tables in phpMyAdmin?

By default, database tables are ordered in alphabetical order. Can this be reordered? For example: default alphabetical: company, profile, user common User Flow: user, profile, company Maybe the ...
1
vote
2answers
42 views

Slave_IO_Running: Connecting in Master-Slave Replication

I was trying to implement Master-Slave Database Replication on two systems. I followed the steps from the following links https://www.youtube.com/watch?v=DmQWcU2INqQ and Master Slave Replication ...
1
vote
0answers
44 views

MySQL table filed with text datatype inserting some script value automatically and ignored user input

In my MY-SQL database table having with text datatype. In that field having some unwanted script tag added automatically. If I given that any value also ignored. and also I tried to edit that value ...
1
vote
2answers
72 views

High CPU usage from sorting result queries

I have two machine have two master-master mysql replicate, only problem i have that i got massive queries like "sorting result" queries and i don't know how to fix these queries . mysql process cpu ...
2
votes
1answer
21 views

Which is better in a master-slave setup to use local queue or write changes to remote instantly?

This is more of a a design question I have a local db (mysql if relevant) for reads and remote master for writes but wondering if it's better to.. 1) Write changes to a local queue and bulk insert ...
1
vote
2answers
68 views

How to decide that a new table is needed?

This might seem a vague question but I am going through a bit of a dilemma here. How do you people decide when a new table will be need? For example, when creating a table for storing user ...