Questions tagged [mysql]

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.

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

SQL query returns most popular item with “*gmail.com” email

I am learning SQL and I am not sure how to approach this query: returns the five most popular items for users with a ‘gmail’ email in the past 30 days, based on number of sales. Very much thanks ...
0
votes
1answer
13 views

InnoDB vs MyISAM vs OThers for Huge Table [on hold]

I would like a db with a single table with billion of lines (50gb data). Primary key will be a md5hash . Which storage engine suits better InnoDB , MyISAM or alternative ? Only Select will be used.
1
vote
1answer
18 views

How to get all records where a joined column contains some value (at least once)

Forgive my horrible description in the title. I have a table called timesheet which has one record per week per employee. There are many rows in timecard_entries for a timesheet. A timesheet can have ...
0
votes
2answers
40 views

How to write an sql database to a file? [on hold]

I am writing a password manager in which one of the features that I would like to include is the ability to transfer the Password database from one computer to another or to export that database. So, ...
0
votes
0answers
9 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
0
votes
0answers
16 views

Query returning Union of two tables

I'm trying to retrieve data from a table using multi-column subquery but it unions the result from both tables. I'm unable to modify it to get actual results. Here is my query select * from Recipe,...
-2
votes
0answers
16 views

Write a query which returns the viewer_ids and associated view counts (view_count) of the users with the most views per video_type [on hold]

Write a query which returns the viewer_ids and associated view counts (view_count) of the users with the most views per video_type. If there are four video_types: food, music, games and sports, the ...
0
votes
0answers
26 views

Left join where IS NULL gives not expected results

I have two MySQL tables where table1 is supposed to contain the exact numbers of rows of the second one and table1.id is supposed to be equal to table2.t1_id. However both tables have different sizes, ...
0
votes
1answer
62 views

Is there a database table design to avoid storing table names in a table?

So my scenario demands to store details of businesses which offer varied services. One business can offer n different services. Since each service is different, details pertaining to a service are in ...
0
votes
1answer
21 views

Speed up simple MySQL query on large database

I have a database with 1 million records and I frequently run this very simple query: select id from my_table limit 1000000; It takes around 5 seconds to execute. And if I add more columns to the ...
-1
votes
1answer
40 views

How can I achieve the below case in marketing cloud without using loop? [on hold]

I have a table like this and in the table you can see 123 losing to 456, 456 losing to 789 and 789 losing to 101112 and in the same fashion for ID's 5 and 6. In this case I want to replace winner ...
1
vote
3answers
36 views

Under load, the following job-queue design does not work reliably, why?

I have a job queue table which is meant to mark-and-return one job at a time to the caller. The stored procedure works like this: update job_queue set status = "WORKING", job_id = <...
0
votes
1answer
17 views

Auto-archive MariaDB10 database table

I am trying to auto-archive active database table to archive database table using stored procedure and event in MariaDB10. These are the steps I am following: Create new table everyday just before ...
0
votes
0answers
17 views

Mysql considers a constant column NULL in left join

I have a query that uses an left join between a table and a subquery. The subquery has a constant field 'Toys' as Product that in the final result set returns null for every row. The query is: ...
0
votes
0answers
8 views

Replicatation broker between master and slave with 'Can't find master key from keyring

I Have created new instance on AWS installed mysql on the server and made that server as Slave and added it to master. Master is in different network. 1) Installed the keyring. Started the ...
-1
votes
1answer
23 views

How to check existence of a value in between two columns of the same table

I have a table bills having columns id, starting_bl_no, ending_bl_no. How can I check a new bill number input is already exist in the table? id starting_bl_no ending_bl_no 1 100 ...
0
votes
1answer
22 views

P2P message system design

Database noob here, just curious to see if my design could benefit from any improvements. My goal was to keep this as simple as possible: a user can create an account, two accounts can message each ...
1
vote
0answers
19 views

Person table with list of friends (=persons) field in MySQL

New to database design. What i'm trying to do is transform a java object called "Persons" into a MySQL schema. Usual values are fields like first/last name, adresses etc. with key=numeric id. But ...
0
votes
0answers
27 views

MariaDB failing steps to reset lost root password

I'm running a local copy of MariaDB on OS X from Homebrew. It's working fine, but I've forgotten my root password (yeah, I know) and the usual reset is failing like this: $ mysqld_safe ‐‐skip-grant-...
0
votes
0answers
21 views

Insert Missing Data in Slave Database

We have a master slave replication [MySQL 5.6]. However In slave database 2 rows is missing in one table. I've tried to solve this using Percona Toolkit as suggested here. But pt-table-checksum ...
-1
votes
0answers
19 views

Data showing correct in database, but not in webpage [on hold]

I have Indian languages in my database. When i check them through phpmyadmin they are shown perfectly. But in my website i can see only ?????. SHOW VARIABLES LIKE '%char%' results +----------------...
0
votes
0answers
16 views

MySQL Service won't start with error [ERROR] InnoDB: Trying to access page number 4294963199 in space 0

My MySQL installation on Ubuntu 18.04 crashed and now won't start. The error log says Trying to access page number 4294963199 in space 0, space name innodb_system, which is outside the ...
0
votes
3answers
31 views

How to get last inserted rows where a condition is satisfied

table1: esps -------------- A B C -------------- table2: id _name esp ------------------------------ 1 Ana A 2 Ana B 3 Mario A 4 Jorge A 5 ...
2
votes
2answers
45 views

How to have a “kinda” unique column

How can I have a MySQL database number column that only allows one 1, but infinite 0's? Some type of constraint or something. Elaboration To clarify what I mean and why I want this: Imagine you ...
0
votes
1answer
28 views

How to improve my large table's performance? [on hold]

I know very little about databases. My table size is 3.5GB and query times are horrible - they can take well over 30 seconds. Clients generate reports for their vehicles very often, so fixing this ...
0
votes
0answers
10 views

Need updated connection string for MySQL ODBC 5.3

I'm upgrading MySQL ODBC from 5.1.6 to 5.3.13, Windows 64-bit, to be used with a Visual FoxPro application. The previous connection string for DNSless operation was: lcDSNLess="DRIVER={MySQL ODBC 5....
1
vote
1answer
16 views

What should be innodb_buffer_pool_chunk_size for pool_size=2G, pool_instances=2?

I have 4G VPS with following config innodb_buffer_pool_size=2048M innodb_buffer_pool_instances=2 SELECT @@innodb_buffer_pool_chunk_size/1024/1024 128.00000000 Should I change ...
0
votes
1answer
33 views

Speed up DB restore from file (mysql)

I am running 5.5.60-MariaDB and I need to upload a DB on the server. The .sql file is located on an SSD drive (locally) and it contains ~700 million rows, with 4 columns indexed. Everything is ...
0
votes
0answers
11 views

Mysql, dump all databases based on user privileges or owning

Question Is possible dump all databases based on users privileges or owning. I mean all databases, where user has all privileges, or is the owner. Example mysqldump --all-databases ...
0
votes
2answers
20 views

Show null column on products [on hold]

I was having trouble showing null values on this query SELECT t1.name, SUM(CASE MONTH(date_added) WHEN 7 THEN T2.qty END) AS 'July', SUM(CASE MONTH(date_added) WHEN 8 THEN T2....
0
votes
3answers
30 views

Database design for monthly leaderboards

I have a system where users can make predictions on events. There are about two events a week. When the event ends the system scores all the predictions and updates this in the database. Here is an ...
1
vote
0answers
26 views

Database design for a customer and payment system

I need help with database schema design. Business rules We have users, each user can have listings. Each listing has one or more license. There are n type of licenses (products) Each listing can ...
0
votes
0answers
31 views

Using group by only on specific case MySql [closed]

i m trying to add GROUP BY in specific case: if countIssues > 0 but it seems to not be working. I get error: Reference 'countIssues' not supported (reference to group function) EDIT: my sql_mode: ...
0
votes
0answers
44 views

Assistance with appropriate schema for this dataset involving the services of parcel couriers [on hold]

I would like some assistance with the appropriate schema for this dataset. I initially thought that it would be simple to determine which table should contain which data and which columns should ...
0
votes
1answer
12 views

JSON field saves as longtext datatype

I'm trying to create JSON column in 10.3.16-MariaDB but every time I save the configuration in PHPMyAdmin, the column saves as longtext datatype. No errors shown :| Where could be the problem? ...
0
votes
1answer
27 views

Why do I get “Truncated incorrect date value” when using str_to_date()?

I have data in one of my tables as "Sun Dec 14 22:49:13 2014 -0800" and need to load it into another table but skipping the time filed i.e. HH:MM:SS. I used: insert into final_data select Book_Name, ...
0
votes
1answer
17 views

Summ up between events

SELECT A.date as timestamp, A.driveNumber as 'Drive Number', SUM(2 * 6374.612 * asin(sqrt(POW(sin(radians((B.latitude - A.latitude) / 2)),2) + cos(radians(A.latitude)) * cos(radians(B.latitude)) ...
1
vote
0answers
50 views

How to avoid running multiple selects with the same subquery join?

I am using MySQL 5.7.26. I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to ...
0
votes
3answers
31 views

Trying to count the number of character in a string

I've created this MySQL function to count the number of characters (str) in a string(search): drop function if exists find_str; delimiter // CREATE FUNCTION `FIND_STR`(search VARCHAR(255), str CHAR(...
0
votes
0answers
10 views

Should I use the native user table (mysql.user) OR create a new user table? [closed]

In your opinion, I should use the native user table (mysql.user) or should to create a new table (CREATE TABLE user...)?
0
votes
1answer
14 views

Cannot login in as root user after MySQL installation (RHEL)

I am new to MySQL. I installed MySQL (version 8) in RHEL 7.7 using yum . Now , I am trying to configure MySQL. [root@devdbvm278 ~]# cat /etc/my.cnf # commented lines removed for readability datadir=/...
0
votes
1answer
27 views

Hints on bulk loading 3.6 billion rows to InnoDB on Aurora MySQL

everyone! I'm struggling for a week to bulk load 3.6 billion rows to an InnoDB table on Aurora MySQL 5.6.10a. This table has one FK to a "Main" table and has 12 columns. The first 1.4 billion were ...
0
votes
1answer
19 views

Update column value based on other column with manage duplicate value MySQL query

I need to update column value based on other column with adding separator and with manage duplicates values using sql query. For example : I have one database table A. Here in it two columns : ...
0
votes
1answer
21 views

insert Query throwing error

I am ran into a weird problem this particular query is not working insert into `timesheet` (`type`,`app_id`,`app_name`,`app_title`, `app_path`,`timestamp_start`,`timestamp_end`,`duration`, ...
-1
votes
1answer
29 views

Displaying data horizontally in mysql [duplicate]

I have this table in my db id | major | code_lesson | files Which the contents look like this id | major | code_lesson | files 1 | Math | MAT12 | mat.pdf 2 | Socio | SOC15 | soc.pdf ...
0
votes
1answer
20 views

insert id from other tables [closed]

I have three tables with information (id, name), (id, product), (id, package). In the fourth table, production, I want to change the names with the proper id via a foreign key. I tried a select but it ...
0
votes
1answer
35 views

Querying in MySQL

I have data like this +----+-----+------------+-------------+--------------+----------------+------------+-------+ | id | seq | allocation | template_id | headcount_id | cost_center_id | country_id | ...
0
votes
1answer
23 views

For MySQL, how to INSERT the result of a SELECT statement? [closed]

For MySQL, how to INSERT the result of a SELECT statement? I tried SET @input1 = 1; SET @input2 = 2; INSERT INTO my_table (col1, col2) SELECT @input1 AS col1, @input2 AS col2 WHERE @...
0
votes
1answer
24 views

Is it possible to commit a transaction that is waiting to RollBack?

I'm using Aurora MySql 5.6.10a and a query that was loading a table with 700 million rows crashed and now it's rolling back. I turned autocommit off to try to speed the data load up, but now it's ...
0
votes
1answer
16 views

implementing MySQL Natural Key [closed]

I'am having difficulties implementing a natural key in MySQL. It keeps asking for the surrogate key. Create Table IF NOT EXISTS Employer ( idEmployer int not null, nameEmployer varchar(45), ...