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
23 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 ...
0
votes
0answers
24 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
38 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
47 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
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
24 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
41 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
12 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
3answers
34 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
31 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
1answer
24 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
29 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
51 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
34 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
1answer
23 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
35 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
2answers
45 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
23 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
36 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
36 views

Querying in MySQL [closed]

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
25 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
0answers
8 views

Would MySQL `x.example.%` host match the `x.example.attacker.com` domain?

According to the mysql documentation the host x.example.% matches connecting from x.example.net, x.example.com, x.example.edu, and so on. I don't have the setup to test, so do you know if % also ...
0
votes
0answers
9 views

Best way to update user credits for API?

I am constructing an API that will use credits to determine if an API call can be made and the credits will be updated after a successful API request. The simple procedure after an API call is made ...
0
votes
1answer
16 views

MySQL replication without baseline data import

I've been reading about MySQL replication and all the resources I've looked at say that a data import (be it a mysqldump, or a raw data dump import) is needed before replication can start. I'm trying ...
0
votes
1answer
31 views

Please help me optimize MYSQL query

This query is very slow: SELECT ( SELECT SUM(ile.gross) FROM item_ledger_entry AS ile WHERE clr.docNr=ile.docNr AND clr.serialNo=ile....
0
votes
1answer
15 views

Log/Show number of (select) queries per table

I used the following view to summarize some metrics in my db. Is there any way to log the select queries per specific table instead? create or replace view _dba_query_stats as select SUBSTRING(...
0
votes
3answers
37 views

Select youngest record

I have a MariaDB table "mdata" with the following schema: +-----+-----------+---------------------+------+ | id | room | date | temp | +------+-----------+---------------------...
0
votes
1answer
32 views

Removing MySQL Orphan Tables in Google's Managed MySQL

I have a number of orphan tables that I am unable to remove in Google managed MySQL instance. https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html describes how to find them ...
0
votes
1answer
38 views

How to install mysql/mariadb on NIXOS?

I installed mariadb via configuration.nix. For the installation I made the following entries in my configurastions.nix. services.mysql.enable = true; services.mysql.package = pkgs.mariadb; ...
0
votes
2answers
22 views

Count two different columns from two different tables and throw into a single variable

I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. ...
1
vote
1answer
25 views

Should I use VARCHAR or DATETIME?

I am making a blog cms system and the release date and time of the posts should be shown on my site. Should I save the Datetime in the php code with a function and save it in a varchar in the ...
0
votes
0answers
17 views

Possible issues dumping a legacy MySQL 5.0 database to a local MariaDB 10.2 for develpment?

I've inherited a site that uses MySQL 5.0 and want to get a development version up and running with the existing data on my local (OS X) machine where I have MariaDB 10.2 but the compatibility tables ...
0
votes
1answer
31 views

What is the advantage of setting a SSN column as VARCHAR(32) over VARCHAR(255)

What is the advantage of setting SSN column VARCHAR(32) over VARCHAR(255) even though the two of them consume 2-byte memory?
0
votes
1answer
14 views

MariaDB 5.5 — how to customize password rules

I am using RHEL 7, which includes MariaDB 5.5. I want to set up minimum requirements for passwords: minimums length 15, 1 lowercase, 1 uppercase, 1 symbol, 1 number. I figure that I am probably ...
0
votes
1answer
14 views

How to select 3 records before x ID and 5 records after x ID with Laravel Eloquent?

How can I select 3 records before x ID and 5 records after x ID with Laravel Eloquent or mysql?
0
votes
1answer
29 views

In MySQL, how to execute two different SELECT statements depending on a condition?

SET @input1=123; SET @input2='foobar'; IF @inout1>0 THEN SELECT @result := col12 FROM table1 WHERE col11=@input1; ELSE IF @input2 != '' SELECT @result := col21 FROM table2 ...
0
votes
0answers
11 views

slave replication not working with Error (errno: 13 - Permission denied)'

I am adding new slave to the existing master slave configuration. Current existing master and slave are on same network, the new slave which i configured is on AWS. Following steps i have done. 1) ...
0
votes
0answers
12 views

MySQL db export log

I have a mySQL db shared between several users and I would like to know if when a user exports all the db into a file there is any log of that operation and who did what... I don't want the data '...
0
votes
1answer
12 views

mysql master and slave has different schema alter table add column

I have added column in large table it was having some unique index which was causing issue,So i drop index and add column and after adding column i have added unique index. But all of my slave is ...
0
votes
1answer
17 views

Slow logs are not showing full details

I am new to mysql DB. I have set log_slow_verbosity value to full. root@localhost/(none)> SELECT @@global.log_slow_verbosity; +-----------------------------+ | @@global.log_slow_verbosity | +------...
0
votes
2answers
26 views

`command not found` when trying to stop MySQL

On OS X I need to reset the password of MySQL. It was installed with Homebrew. I found this article which explains how reset my password: https://dev.to/oneearedmusic/access-denied-reset-mysql-root-...
0
votes
1answer
17 views

Should I include another column here to keep track of cost + tax or not?

I am making a two database tables tho add a sales order line item functionality to my application. On the second table, which lets call salesorderlineitems, I have the following schema CREATE TABLE ...
0
votes
2answers
21 views

Database layout to keep track of the “freshness” of a column value in a specific row

I have a Person with columns like name, address and bio. How do I store data in order to know the "freshness" of these columns? For instance, "for the Person Y, the name has changed 2 days ago, and ...
1
vote
1answer
66 views

AWS Aurora MySQL Serverless CPU utilization is growing for unknown reason

We have been using Aurora serverless databases in staging and production environments for a few months now, and find the CPU remains in the range 15-20% almost all the time. However in the past week ...
0
votes
0answers
14 views

MySQL, error creating system tablespace ibdata1

I have a MySQL database (version 5.6.21-log) which I wanted to reset. I have erased the database folder (where I had my tables), and also the ibdata1 (the system tablespace). The problem is that ...
0
votes
0answers
20 views

MySQL 5.7 isn't using new innodb buffer pool settings

I have a very tiny VM which keeps running out of memory. I believe this is due to the buffer pool size. Default appears to be 128M for innodb_buffer_pool_size and innodb_buffer_pool_chunk_size. So I'...
2
votes
2answers
31 views

Why does “CREATE TABLE IF NOT EXISTS” lock a table?

We recently run into "waiting for table metadata lock" issues, after "CREATE TABLE IF NOT EXISTS" has been fired several hundred times per second. This is not a request for help (obviously we ...
0
votes
1answer
30 views

How to Calculate Month over Month User Retention based on already active users (not based on user signup date)?

I have a Table which tracks User activity (i-e user started a session at what time ?). This table contains the data from Dec 2018 Till now. I need to calculate monthly retention (not based on signup ...