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
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 ...
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 ...
1
vote
1answer
278 views

What's the approach to figure out reason for mysql crash?

The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" ...
1
vote
1answer
793 views

MySQL 5.7 doesn't start after installation on CentOS 6.7

I have just installed MySQL 5.7 on a fresh CentOS 6.7(vm). When I start the MySQL service with this command: sudo service mysqld start It doesn't start and raise this error: Initializing ...
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  &...
2
votes
1answer
164 views

MySQL running fine while data files (disk) are not present. Data folder nowhere to be found

I'm running MySQL server 5.6 on Windows 2012 R2, and since the installation didn't go as planned, I had to re-install it to change the data directory path. The installer for some reasons never gave ...
-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
2answers
417 views

How to select different format of the date in mysql

0 down vote favorite. I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its ...
2
votes
1answer
119 views

AWS RDS - Changing Instance Class

So I'm fairly new to AWS and I'm hoping you guys can reassure me as I couldn't find anything in the AWS documentation either way. I actually have two questions, the first: I have an RDS MySql ...
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
2answers
2k views

Update MySQL Stored Procedure Definers on shared environment

I am accessing a MYSQL database through PHPMyADMIN. My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL ...
0
votes
2answers
223 views

How to change mysql password

I am on OS X 10.9.5 and I did a reinstall of mysql2 and I provided a password during installation but it is not working when I run the command mysql -uroot -p. The result I get is: ERROR 1045 (28000): ...
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
1answer
348 views

Using non-replicated read/write databases on a MySQL slave server

I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. ...
8
votes
1answer
44k views

How to get rid of “maximum user connections” error ?

I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than '...
1
vote
5answers
370 views

Replication in same server within different databases. Mysql 5.6

What I have : a windows machine with one instance of MySQL 5.6 Server. two database named test and test2. Test database has a table called activity with columns id and class What I need : ...
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
2answers
325 views

Dealing with data stored as arrays in a MySQL DB

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
1
vote
1answer
176 views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
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 ?
1
vote
1answer
162 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
3
votes
5answers
30k views

Remote MySQL connection fails (10060) - “Can't connect to MySQL server on domainname.com(10060)”

I have developed an application in VB6, and am using an online MySQL database server for storing my application's data. The first time I try to connect to my database server is successful, but after ...
8
votes
2answers
233 views

Data Warehouse: How can i query daily snapshots?

I have some snapshots of a database that are not timeseries. For example: Snapshot day 1: +----+---------------+------------+------------+ | ID | Title | Category | Date | +-...
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 ...
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
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 ...
1
vote
3answers
2k views

Mysql Replication Automation Script

I am looking for something that will help us to create a lot of mysql slaves connected to the specified master, let's say kind of mass-replication. If there are any existing solution for this ...
1
vote
1answer
183 views

How do you remove reported non existing tables from mysql?

I was trying to export a mysqldump --all-databases and noted errors. A subsequent mysqlcheck --all-databases revealed several non existent tables like the following Error : Table 'stats....
2
votes
1answer
243 views

innodb , mysql Indexes difference when adding index

When I am altering one of my MySQL table to add an index I am getting below error in error log. Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined ...
1
vote
1answer
48 views

Distinguish between unknown and non-present foreign keys

I'm in the situation of importing data from multiple other applications. The data is stored in multiple tables that contain foreign keys. Some of these applications don't provide all the needed data ...
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
1answer
121 views

MySQL user privileges lost on reboot

On occasion our MySQL 5.6 server loses some user privileges when restarted. The CREATE USER and GRANT commands are run through phpMyAdmin and function for as long as the server is running. After a ...
1
vote
1answer
148 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
0
votes
1answer
155 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
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 `...
1
vote
1answer
354 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
2
votes
5answers
3k views

MySQL SSL disabled on Windows

I want to enable an SSL connection to my database. I've created SSL certificates and so on, however SSL remains disabled: mysql> show variables like '%ssl%'; +---------------+---------------------...
3
votes
1answer
135 views

How to monitor mysql percentage disk utilization?

I am new to mysql world and not sure if there is a way to calculate mysql TableSpace/DiskSpace utilization in percentage. I am aware mysql does not provide upper limit or free space like Oracle DB, ...
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
2answers
226 views

Please recommend changes for my.cnf

I am not having any performance problems per se but want to improve my sites' performance if possible. Would appreciate any advice for how I can improve my.cnf. I have just one server, not ...
1
vote
1answer
1k views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
11
votes
1answer
164 views

How to estimate the I/O operations performed by MySQL queries?

On Amazon's RDS, you pay about $0.10 per 1 million I/O requests. I've noticed a very high number (in hundreds of thousands) of I/O requests for a very low traffic database. On seeing this, I did some ...
0
votes
1answer
752 views

MySQL data transfer to different schema

We have two servers: one for the developer (me) and one for the tester. While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type) I'm trying ...
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
1k views

ClearDB Granting Created User Access

I added the ClearDB addon to Heroku, but upon using the created username to create a new database, the error appeared: Executing: CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8 ; ...
1
vote
1answer
122 views

How can i improve my count(distinct id) query in mysql laravel 4.2

I am using three join and fetching more than 40lacs of records for now it is 15 lacs . Below is my query, can you please take a look? select count(distinct enrollments.userid) as aggregate from ...
2
votes
1answer
7k views

Loop through Self JOIN on table until the operand column is NULL completely

For the problem I have a single table which store a Member's ID and His Referee's ID. This is something like a Multi-Level-Marketing plan where you join a company for some tasks and then someone join ...
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) ...
1
vote
1answer
184 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...