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
1answer
10 views

Process possessing lock crashes

I have two machines, M1 and M2, through which I am connecting to MySQL Database on machine M3 using Perl script (DBI.pm). So, here is the scenario that I am confused about: M1 places a table level ...
0
votes
1answer
34 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
-1
votes
1answer
40 views

Recovering a lost database [on hold]

Before I start, there might not be a way to do this but I thought I would ask anyway.. I built a php application with Laravel 5.2 and have suddenly noticed I have lost the MySQL database for the ...
0
votes
0answers
15 views

Migrating SQL Blob to Server File System

I was given a MySQL database that was created by making a .sql dump file from an existing MS Access DB. During that migration I have an attachment column that was given a BLOB datatype and the ...
0
votes
1answer
23 views

Create a MySQL Database using a MS SQL database dump (.CSV)

I have a dump of a MS SQL database in the form of CSV files for every table in the database. There are 679 .csv files each for every table. The highest size of the table extract is 9 GB and the lowest ...
-1
votes
1answer
44 views

Most efficient way to log user login IPs

I am looking for ideas on how to log user's login IPs without compromising on performance in MySQL. There are few primary tasks which the log would be used for: Finding list of other users who have ...
-4
votes
1answer
39 views

I need to change the format of 'date' from yyyy-dd-mm to mm-dd-yyyy [on hold]

My dilema, I was requested to populate a table of sample data and I completed the command to add a date. I entered '07-07-2016' as sample data. When running a query the "date_ordered" field came ...
0
votes
1answer
17 views

MySQL replication on slave server Broken/Stopped without error

(Hi; sorry for my English) My question is: how can I restart MySQL replication on a slave server. At first sight everything seems to be normal but when I check with SHOW SLAVE STATUS\G besides ...
1
vote
1answer
71 views

Primary Index VS Unique Index Performance

I have the following table: CREATE TABLE `actions` ( `user_id_from` int(11) unsigned NOT NULL, `user_id_to` int(11) unsigned NOT NULL, `action_type` int(11) NOT NULL, `counter` smallint(11) ...
1
vote
0answers
46 views

MYSQL Extremely slow - Heavy CPU Load

I am having an issue with my Percona MySQL 5.7 node. Since past 3 days, MySQL is processing queries very slowly. When I run top, the % CPU usage of MySQL is always 600. # top | grep mysqld 8409 ...
-4
votes
0answers
38 views

Retrieving the data based on id from table contains 2 million rows [closed]

I have one table/collection contains 2 million rows/document (id(20 alphanumerical digits), name, ... etc). From that table I want to retrieve data based on the id within 1 sec Which database ...
0
votes
1answer
16 views

shrink ibdata1 in Galera Cluster

I'm having a Galera cluster (3 nodes, but currently using only one), MariaDB 10.0 Galera. However, my ibdata1 is growing HUGE (although using inno_file_per_table=1). Now i want to "shrink" it by ...
0
votes
2answers
24 views

How can I fetch parent table's value within a trigger?

I have a PARENT table and a CHILD table. PARENT ------ ID BIGINT PRIMARY KEY NAME VARCHAR(255) NOT NULL CHILD ----- ID BIGINT PRIMARY KEY NAME VARCHAR(255) NOT NULL PARENT_ID BIGINT NOT NULL I ...
0
votes
0answers
7 views

Can I use --no-lock --slave-info at the same time with innobackupex

I am now going to setup a new slave for my master DB instance (MySQL 5.5). And I see there's two parameter offered by innobackupex: --no-lock & --slave-info. Is it possible that I use the both ...
-1
votes
0answers
12 views

how do we know if mysql server is overloaded [on hold]

I am developing an application using PHP. Sometimes Mysql is overloaded. Is there anyway I can tell the user the database is overloaded & to come back later?
0
votes
0answers
17 views

SOLVED - How to import a text file with “,” value to Mysql

I have data in a text file that have value like this : _______________________________________ |id|name_item |value| _______________________________________ | 1|Luas ...
0
votes
0answers
10 views

Trying to recover a database with just .ibd and .frm files, but no idbata1 file

Trying to restore a WAMP database with just .frm and .ibd files. The uninstall of WAMP left .ibd and .frm files, but no ibdata1 file. I have read several posts here to figure out the process for doing ...
0
votes
1answer
16 views

MySQL - Upgrading from 5.5 to 5.6

I have MySQL community version 5.5.50 running in linux having 4-5 databases on it. Requirement is to upgrade to Percona Server 5.6.31-77.0. What are the pre-requisites that I need to check before ...
0
votes
0answers
27 views

Data Base Error

I have an online game. The game server every 2 hrs lost connection with data base & when i check the gameserver log i found this 2016-7-18 2:1:8 -- ERROR: Database ExecuteSQL(MySQL client run out ...
0
votes
2answers
33 views

General Database Optimizations - correct data types - why to avoid NULL? [closed]

Oracle recommends: Choose the correct data types and size to: – Avoid NULLs – Improve performance – Protect data – Use data compression where appropriate I don't understand why I need to avoid ...
0
votes
1answer
31 views

Why MySQL does not provide stddev(distinct expression)? [closed]

I ask the question because some other numerical aggregates, like sum, avg or count support the distinct modifier. And other databases like Oracle or MSSQL support distinct modifier for stddev.
0
votes
2answers
17 views

Open Source tools to visualise MySQL/MariaDB performance metrics (show global status with intervall)

I search for an Open Source tool to visualise MySQL/MariaDB performance metrics (output of show global status in nice graphs). In MongoDB world there is the Closed Source Ops Manager and the Open ...
0
votes
2answers
30 views

Rank rows using weighted average with multiple sub queries

So what I want to do, is to rank students based on their weighted average which will be calculated by separating the grades based on the type (whether "exam" or "not exam). The average for both will ...
0
votes
0answers
13 views

MySQL - sys_exec to trigger python scripts - Alternatives

I am trying to log some heartbeat data to check successful completion of errors in some frequently running stored procedures to Datadog. At the moment these are triggered from a Java application ...
0
votes
1answer
21 views

Moving log files & data in MySQL

Is it possible to separate out data files and log files of MySQL from one drive to another? For eg: Data (actual database data and system tablespace ibdata) in one partition, say /data and log files ...
0
votes
3answers
24 views

Create a mysql event with condition [closed]

I currently have a mysql database where I keep data about rented books. When somebody rents a book, I save on the database an expiration date. So, I want to create an mysql event to update the book ...
0
votes
1answer
52 views

Reset MySQL table data after each session is completed in php

I am creating a PHP application demo. The users who login can insert, modify and delete data but when they logout the data must be reset to the default data I set before. The data must reset ...
1
vote
1answer
47 views

Retrieving specific results from a query

I have the following database table Firstname Varchar(40) Surname Varchar(40) Department Binary JobCode Char(1) Normally, pulling the records of a particular department is no problem. The ...
1
vote
1answer
25 views

How do I select the next row in SELECT FOR UPDATE?

I am modifying my web crawler program to use SELECT FOR UPDATE to avoid multiple crawlers (with multiple children) pulling the same row to process. How do I do this? select * from visited where ...
0
votes
1answer
39 views

Rank students based on their average in MySQL

I have been researching and trying various queries to get this to work. What I want to happen is to rank each student based on their average which will be calculated by using AVG(grade) and this will ...
1
vote
1answer
56 views

Error code 1054: Unknown column when inserting data into a table [closed]

I have been trying to join data from two separate tables with distinct columns into a third table, but Error code 1054: Unknown Column 'Company' in field list keeps popping up. My current ...
0
votes
1answer
19 views

MySQL AlphaID Function

While trying to optimize a few queries to not do with PHP something I could bring ready-to-use from database, I tried to convert two routines originally created in PHP to encode an integer in its ...
1
vote
1answer
18 views

How do MariaDB and MySQL differ in character set handling?

In MySQL/MariaDB, I set the connection character set to UTF-8: SET NAMES 'utf8mb4'; SET CHARACTER SET 'utf8mb4'; There are some tables with a latin1_swedish_ci collation and latin1 encoded data in ...
0
votes
0answers
14 views

Merging 3 tables in Access

I have three tables in Access. I want to merge them together removing all duplicates however there are many blank records under the FirstName and LastName tab. The common columns between the three ...
0
votes
1answer
19 views

Make a boolean false after every n number of minutes in MySQL

I have a table with a boolean value, if that value is not explicitly set to anything in the database for a few minutes by the back-end (django) then it must automatically be set to false. How do I do ...
0
votes
0answers
17 views

How to store non-numeric data for a numeric field

I am looking for an SQL or NOSQL solution for the following problem: In a MySQL database (which I have no control over), I have some numeric fields in which numeric data is stored. But for some ...
1
vote
0answers
16 views

Modeling Multiaccounts + content interactions [closed]

I'm having trouble to model a scalable database schema with performance in mind. The requirements are wide, as such that I need a schema that supports; user can swith between multiple types of ...
0
votes
0answers
42 views

Find Intermediate Bus Stop and Source and Destination Bus

I have a table which consists of 4 Columns: PKID, Bus_ID, Stop_Position and Stop_ID. Data: BUS_ROUTE_TABLE PKID BUS_ID STOP_POSITION STOP_ID ------------------------------- 1 1 1 ...
1
vote
1answer
64 views

Optimize mysql database performance

I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this: Id: (int 11, ...
1
vote
1answer
34 views

How can I set TIMESTAMP's default to future date?

I know I can set default value with current timestamp like this ... DEFAULT CURRENT_TIMESTAMP Is there any way to set the column with fixed amount of units based on current timestamp? ... DEFAULT ...
0
votes
2answers
61 views

How to select the same data for unique item?

I have this SQL table: +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+-...
0
votes
1answer
16 views

Using value of a column in another table for ENUM/SET values

I have two tables tbl_events with a column location_id. Then I have a table specifically to hold locations and their IDs,the columns in table tbl_locations are location_id and name. I want the ...
0
votes
0answers
23 views

Getting values from tables to string, combined one-to-many and many-to-many

I am stuck with one task. I have a database with one-to-many and many-to-many relationship. The whole problem is that I need to take from one table all persons in to a separate strings and count how ...
0
votes
0answers
13 views

What triggers/prevents MyISAM Flushing

I have a database that has between 10k and 400 CUD operations/s on MyISAM tables. Whilst the number is slightly lower at night, I see a recurring pattern night after night which causes a backlog to ...
0
votes
1answer
13 views

Table cache hit rate: 0%

I have an AWS RDS Mysql Server with Table cache hit rate's problem. If I do a query on phpmyadmin (also simple query) always do it without cache. Here the mysqltuner log: MySQLTuner 1.6.12 - Major ...
1
vote
2answers
55 views

mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command: ALTER [IGNORE] TABLE tbl_name [...
0
votes
0answers
11 views

storing email address and associated data

I want to save email addresses and for every email address a group of tags. For example: "a1@temp.com", 2 "a1@temp.com", 3 "a1@temp.com", 7 "a2@temp.com", 2 "a1@temp.com", 12 ... What is the best ...
1
vote
3answers
28 views

Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3. use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(...
0
votes
2answers
20 views

Innodb_buffer_pool explanation

The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process. According to the mysql docs: ...
0
votes
0answers
6 views

Help updating a field from a vb program in to an sql database [closed]

send new user's highscore to sql table "Select Highscore from Members where username='" & username & "' UPDATE Members SET Highscore = REPLACE(Highscore, '" & HScore & "', '" & ...