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

3
votes
2answers
33 views

Select only rows with at least 2 non-missing values

I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated! I have a large database (a couple ...
1
vote
0answers
29 views

MySQL basic query question

I have a table as follows: k | Ecode | Typ | Score | ---+------+-----+-----------+ 1 | 1 | NYS | 104.17 | 2 | 2 | NYS | 201.55 | 3 | 3 | NYS | 213.22 | 4 | 1 |...
0
votes
1answer
29 views

Get previous field's value in MySQL trigger

I am using a MySQL database and I have this kind of table. IDRefHandler 1 x    299       2 x    300   &...
1
vote
0answers
28 views

Best approach to store and match multiple data set against another set [duplicate]

I am developing a match making system where a user has certain data associated with his profile information. he has set of preferences to match against other users in the system. I am in search for ...
0
votes
0answers
19 views

Innodb Change Buffer merge

I have been testing the performance of my innodb mysql. I ran a script which inserts and selects one after the other for 100000 times. So according to the change buffer concept, the inserted ...
1
vote
0answers
30 views

Fetching equal records of all options MySQL [closed]

there are 4 columns which I need in conditions. age, city, accommodation, sex I want to export total number of 1000 records in the following criteria: I choose ages between 31-35 years old with both ...
1
vote
0answers
34 views

INSERT MySQL stored procedure executes twice when it contains a select statement as well [closed]

I have a very simple MySQL stored procedure as shown below: BEGIN INSERT INTO listing_offers (lo_active, lo_amount, lo_amount_number, lo_contract_reference,lo_created, lo_currency, ...
1
vote
1answer
23 views

Will a mysql db import be interrupted if my ssh session times out?

I have a large-ish (several GB) mysql DB that I'm planning to import via mysql -u root -p mydb < mydb.sql in a SSH session. What would happen if my SSH connection drops? Is it safer to run ...
1
vote
0answers
28 views

“SHOW TABLES” command returning tables not in the schema

I was debugging a backup script at work and noticed that for some of our bigger tables (greater than 5GB), I'm noticing an odd discrepancy. If I run the command mysql -h $host -u $user -p $pass -e '...
0
votes
1answer
38 views

how to update information_schema.tables in mysql [duplicate]

i have a table A and i want to update the creation time of this table. I understand i cant simple update ( using update query) as it is not any table. Is there any way i can update it. for example ...
0
votes
2answers
22 views

Remove String from column based on character

i have table : table : mytable id name 1 abcd ( dljdf 2 kjlkjkldjf ( ( ( ;dfld ) I want to remove string starting from character '('. Result would look like id name 1 abcd 2 ...
1
vote
1answer
14 views

Cannot create schema : permission issue

I am trying to create a schema. But I am getting this error : mysql> create schema myBase; ERROR 13 (HY000): Can't get stat of './myBase' (Errcode: 13 - Permission denied) When I do a ls -l on ...
1
vote
0answers
33 views

How to export views and import it in other DB?

I am trying to export the database and everything is fine but I am not able to export the VIEWS. Is it possible to export the views from sql queries and import it?
0
votes
1answer
24 views

Mysql columns mutual exclusion

I want to create a table which has 2 columns that would only allow one of them to have value in the same row. I tried with CHECK constraint but it not working correctly Ex: SQL Fiddle schema ...
3
votes
1answer
64 views

MySQL applying second incremental backup causes LSN in the ibdata files to be higher than LSN in the ib_logfiles

Using the backup procedure described below, it gives warnings. These warnings start to appear from the 2nd till 6th (last) incremental backup that is being applied to the base backup. How to solve ...
1
vote
2answers
33 views

guessing unused junk space in table. (innodb mysql/mariadb )

We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am ...
1
vote
0answers
26 views

How to compare three columns on WHERE condition? [closed]

I need to compare three columns in a single table. Please help me clarify this problem. $pro_qry1 = "SELECT * FROM orders WHERE customer_id ='$id' AND item_id='$pro_id' AND status='0'"; $pro_run1 = ...
1
vote
0answers
19 views

Repair or force delete corrupt tables

I have an database with the name "build", and when I try to execute "USE BUILD" the MySQL server just crashes, it also crashes when I try to show the tables with phpMyAdmin and MySQL Workbench. I do ...
1
vote
0answers
52 views

What does a database administrator really do? [closed]

I am planning out a website project and have a limited amount of knowledge and understanding, which are two completely different things. I hire freelancers to do my actual work, but for that to work ...
3
votes
1answer
71 views

Best practices, MySQL table with 150+ columns, many null

I have a table storing property data for thousands of properties, all coming from a feed. (updating at least once a day). I'm certainly not an expert with databases and would appreciate some guidance ...
1
vote
2answers
89 views

MySQL doesn't log queries anymore

Some time ago I updated my MySQL server to the v5.6: SELECT VERSION(); 5.6.30-0ubuntu0.14.04.1 Now the query logging isn't working though the configs in the /etc/mysql/my.cnf seem to be correct: [...
-1
votes
0answers
46 views

MySQL Quesries give me a High CPU usage - howto fix it?

I use automatic wordpress plugin that keep my site feeding by using cronjobs -every minute. I use 30 cronjobs on every single minute. Now i have a super slow server and a high cpu usage because of ...
1
vote
1answer
49 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 ...
1
vote
1answer
33 views

ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations. --order-by-primary Dump each table's rows sorted by its primary ...
0
votes
1answer
36 views

Updating records from the past with MySQL query

I've built an application that fetches reports from an external source. It downloads report data once a day. Each report covers the time from now to 30 days ago. E.g. let's say it's 1 May and report ...
0
votes
0answers
54 views

Get rank of users based on dynamic points

I have this db structure for my ranking system. users table id username 1 alex 2 john points table id name description value 1 foo bar 100 2 bar foo ...
1
vote
1answer
57 views

How to restore database with ib_log0, ib_log1 and ibdata1 files in MySQL

We have a Mysql5.5 running on Windows server 2008 from past 4 years. Recently we have moved ib* files to new locations because of space constraint in c drive. When we started the Mysql it created ...
1
vote
0answers
31 views

MySQL partition on 2 tables one with millions of rows the other with 100's of millions

I have two mostly de-normalized tables in Amazon Aurora (MySQL). The first table had mostly dimensions that are used for selecting the proper data. Examples might be station, program name, operator, ...
0
votes
1answer
71 views

Memory(buffer pool) not populating on mysql start on Percona 5.6.28

We are testing Percona 5.6.28 at the moment, during our testing we found out even though the innodb_buffer_pool_populate is set to ON, it doesn't populate the memory(buffer pool) during mysql start. ...
0
votes
1answer
32 views

Mysql read only engine type and indexes

I have a mysql table. This table has only three columns. Two are varchar (5),and one is varchar (32). There will only be one query run against table. Select (column 1),(column 2) from mytable where ...
0
votes
0answers
38 views

re-sync Replication Mysql Slave after down server

i need some help to restablish my slave on mysql. A few days ago my slave machine stop replicating with this error. *************************** 1. row *************************** ...
0
votes
1answer
45 views

MySQL Bug reference in release notes, but where?

This may be a daft question, but on the MySQL release notes, a lot of them are followed by BUG Reference numbers. But most of these BUGS don't exist when you search for them at bugs.mysql.com. ...
0
votes
0answers
27 views

can tables be created in a loop on mysql

i want to create multiple table with different names(based on numbers mostly) which will have same structure. In C++ I used pointers when i wanted to create variables in this manner. Is there a ...
0
votes
1answer
31 views

MySQL Trigger Validate Before Insert [closed]

I am new to MySQL TRIGGER. I want to know is it possible to check before insert using last record found? Table AccountChecking ==================================================== || ID || ACID |...
0
votes
3answers
52 views

Why full index scan over full table scan?

I have a query that pulls from small tables in MySQL: SELECT C.cid, C.email, R.flight_number FROM customer C INNER JOIN reservation R ON C.cid = R.cid WHERE C.email LIKE '%d.com'; There exists an ...
3
votes
2answers
132 views

MySQL hundreds of sleep processes giving “Error establishing a database connection” error

I'm running a LAMP with ubuntu server 12, mysql innodb_version 5.5.49 on a Wordpress platform (3 websites virtualized). Has been running without issue for about 2 years, but I just started getting an ...
4
votes
1answer
31 views

How to use GENERATED from SELECT with JOIN?

This is my database. FoodItems is just normal table but I want to use virtual columns to generate all the related data, e.g. Servings.kcal is equal to related (FieldItem.kcal*unit)/100 (the data is ...
0
votes
1answer
69 views

Compare two sets with a quantity field

Imagine I have a basket from an online shop, with id of item and the quantity. I have a list of items of quantity and I would like to find a basket with the exact same items and quantity for each. I ...
1
vote
1answer
35 views

Hourly Set Report in MySQL

I have a daily reporting system in place which shows the number of calls made by a particular telecaller in a day. And it works well. I'd like to bifurcate it further into hours for e.g 9-10, 10-11 ...
-2
votes
1answer
64 views

select from where row x column A = row x+1 column B

Query select from where row x column A = row x+1 column B Data Car ID | Time 1 | Time 2 1 | 5:15 | 4:12 2 | 3:13 | 5:15 3 | 6:12 | 5:15 4 | 4:12 | 2:15 Result It ...
0
votes
0answers
18 views

MySQL - What does ODBC's BIG_PACKETS option do?

I have seen the line OPTION=10 in some connection entries in ODBC.ini files, and found that it corresponds to the options 2 and 8. Those are defined in the documentation (PDF) as the following (...
0
votes
2answers
81 views

Is there a way to backup a mysql database without having to run mysqldump?

Is there a way to run a backup of a mysql database using sql? We have scripts which run updates against our databases. They only have mysql user credentials, not system login credentials. Is there ...
0
votes
1answer
34 views

Mysql repetition of column in Criteria

What happens when I repeat the same column in the criteria part but with different values . For example col1 ='val1' or col1 = 'val2' or col1 = 'val3' . Assume col1 is not indexed. Will mysql ...
1
vote
1answer
17 views

Scenario to automate the promotion of a read replica when the master db is not responding

I would like to set a scenario like the following: Set an RDS event subscription [that the MySQL is unresponsive] that will publish to an SNS topic that will trigger a Lambda function which will ...
0
votes
1answer
27 views

Migration MySQL to Cloud Environment replication

I'm having 5 MySQL database server , these 5 server are used for e-business web-server. Now I Would like to move the MySQL database to cloud environment for database continuous running. Recently we ...
0
votes
1answer
65 views

Is there a Portable MySQL

For teaching and testing purposes I would like to run MySQL without actually having to install it. I have found portable PostgreSQL for both Macintosh and Windows, which is good, but have been unable ...
3
votes
1answer
32 views

Mysql table crash, what do I need to do to fix this

Table '.\mysql\proc' is marked as crashed and should be repaired. Can anyone help me figure out how to fix this?
-1
votes
1answer
38 views

Join between a table and a recursive table

I've that two table, I know they are not well thinked but my client did this tables and I cannot change them. TABLE A id | codigoFatherTABLEB | codigoSonTABLEB TABLE B id | name | codigo | idPadre ...
1
vote
1answer
25 views

Contradictory reports from MySQL that a binlog file exists

Our replication recently stopped working. I submitted a question but was unsuccessful in fixing the problem (Error 1236 - "Could not find first log file name in binary log index file"). ...
0
votes
1answer
44 views

Effective way to migrate from SQL Server to MySQL

I have a 20GB Microsoft SQL Server database that needs to be migrated to MySQL. Right now I only have the schema for the source db. I tried the migration wizard on the schema in MySQL Workbench and ...