rds – What is the best practice when MYSQL Slabe misses data?

We have a huge database running into a TB plus running on AWS RDS. We have a MYSQL slave set up on premises basically for compliance and statutory needs. We have a big audit coming up in weeks. And I notice that why data seems to be syncing perfectly there are number of rows missing across many tables. There is no pattern which is emerging.

In such cases, what is the best recommended practice to trouble shoot? To set up everything again is not an option.

Does mysql “order by” guarantees to give the same order for the same query every time if the sort key is equal?

When the field being sorted on is not unique, then the order of the results are non-deterministic, meaning there is no guarantee on their exact ordering. Specifically in your example case where the first 10 results all share the same exact value for the s field could yield indeterminate results for the first time you run the query vs the second time. It is possible you see the same result in both cases or not at all as you guessed. You would need to add another field to the ORDER BY clause that uniqifies the sort in order to always guarantee deterministic results.

For a little proof of case and further reading, here is a DBA.StackExchange question and answer where the user was experiencing unpredictable ordering of results because the field they were sorting on wasn’t unique. The comments on that answer briefly mention that being the cause.

Mysql keeps re-adding disable-log-bin=1 to my.cnf

We have mysql 8 server configured as master. Every time WHM updates mysql, disable-log-bin=1 is added to my.cnf. Not sure where is the issue, but it is already 3rd time it happens. Current problem was when it updated from 8.0.22 to 8.0.23. Any way to ensure my.cnf will not be touched during update?

mysql – Se puede poner un limite en un auto_increment?

Me gustaria saber si existe alguna manera de poner un rango en un id auto incrementable en mysql.
Por ejemplo:

Que solo se puedan añadir ids desde 1200 hasta 1500, y que cuando se añada un id 1501, este no se agregue porque ha llegado al limite.

Se que se puede cambiar el valor de inicio de un auto_incremente con el codigo

ALTER TABLE nombreTabla AUTO_INCREMENT=1200

mysql – Query to find the second highest row in a subquery

The goal is to send notifications about the customer updates but only for the first one if there are consecutive updates from the customer in a ticketing system.

This is the simplified query that I’m using to get the data that I need. There are a few more columns in the original query and this subquery for threads is kind of required so I can also identify if this is a new ticket or if existing one was updated (in case of update, the role for the latest threads will be a customer):

SELECT t.ref, m.role 
  FROM tickets t 
  LEFT JOIN threads th ON (t.id = th.ticket_id) 
  LEFT JOIN members m ON (th.member_id = m.id) 
 WHERE th.id IN ( SELECT MAX(id) 
                    FROM threads 
                   WHERE ticket_id = t.id
                )

It will return a list of tickets so the app can send notifications based on that:

+------------+----------+
| ref        | role     |
+------------+----------+
| 210117-001 | customer |
| 210117-002 | staff    |
+------------+----------+

Now, I want to send only a single notification if there a multiply consecutive updates from the customer.

Question:

How I can pull last and also one before last row to identify if this is consecutive reply from the customer?

I was thinking about GROUP_CONCAT and then parse the output in the app but tickets can have many threads so that’s not optimal and there are also a few more fields in the query so it will violate the ONLY_FULL_GROUP_BY SQL mode.

db<>fiddle here

MySQL error: Missing index for constraint, can someone help me pls?

CREATE TABLE Account_Base
(ACCT_ID INT(4) NOT NULL,
OPEN_DATE DATE NOT NULL,
CLSE_DATE DATE NOT NULL,
PDCT_TYPE_C VARCHAR (10),
OPEN_CHNL VARCHAR (100),
ASST_OPEN_CHNL_DETL VARCHAR (100),
MOST_FREQ_USED_CHNL VARCHAR (100),
MOST_FREQ_USED_CHNL_DETL VARCHAR (100),
PRIMARY KEY (ACCT_ID, OPEN_DATE, CLSE_DATE, PDCT_TYPE_C),
CONSTRAINT FK_Account_Base_Customer_Account FOREIGN KEY (ACCT_ID) REFERENCES Customer_Account(ACCT_ID) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT CK_Account_Base_OPEN_DATE_CLSE_DATE CHECK (OPEN_DATE>CLSE_DATE));

ALTER TABLE Account_Base MODIFY COLUMN PDCT_TYPE_C VARCHAR (10) NOT NULL;

CREATE TABLE Product_Reference
(PDCT_TYPE_C VARCHAR (10) NOT NULL,
PDCT_TYPE VARCHAR (100) NOT NULL,
PDCT_TYPE_DETL VARCHAR (100),
MNTH_FEE_COST INT (3),
EFFT_D DATE,
EXPY_D DATE,
PRIMARY KEY (PDCT_TYPE_C, PDCT_TYPE),
CONSTRAINT FK_Product_Reference_Account_Base FOREIGN KEY (PDCT_TYPE_C) REFERENCES Account_Base (PDCT_TYPE_C) ON UPDATE RESTRICT ON DELETE RESTRICT);

mysql – Keep getting this error for wordpress database set-up on my pi? ERROR 1044 (42000): Access denied for user ‘pi’@’localhost’ to database ‘wordpress’

As I’m setting up my wordpress database I keep getting this error:

ERROR 1044 (42000): Access denied for user ‘pi’@’localhost’ to database ‘wordpress’

After typing this and entering my password:

sudo mysql -upi -p

I got this:

Welcome to the MariaDB monitor. Commands end with ; or g.

Your MariaDB connection id is 57

Server version: 10.3.27-MariaDB-0+deb10u1 Raspbian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

MariaDB ((none))>

And this was what I typed:

MariaDB ((none))> GRANT ALL PRIVILEGES ON wordpress.* TO 'pi'@'localhost' IDENTI FIED BY 'password';

I replaced the actual password with ‘password’ in this example for security reasons, but I tried all of the passwords that I had. Nothing worked. I keep getting the same error.

I also tried removing ‘pi’@’localhost’ and changing it to just ‘pi’. I also dropped and recreated the wordpress database and tried again. It’s always the same error.

Any suggestions? I have no clue where to continue from here.

mysql – About database design

I am considering data modeling (table design) And DB types(MySQL, MongoDB and so on).

I am thinking of a service that handles financial information and economic indicators.
Options have daily data by price range and by Special Quotation.On the other hand, economic indicators have daily or monthly data, and there is breakdown data. For example, the relationship between GDP and personal consumption, housing investment, etc.

In such a case, I would like to design a table that stores data as simply as possible, but what kind of architecture should I adopt?

sql – Cómo cambiar en MySQL la cardinalidad de la relación de 2 tablas

estoy inicándome en JPA y tengo mi base de datos en MySQL

Tengo sólo 2 tablas en la base de datos de MySQL , Cuenta (que tiene numeroCuenta, y saldo) y Movimiento que tiene (idMovimiento, tipo y idCuenta). idcuenta es la clave foránea para relacionar la tabla Movimiento con Cuenta.

Hasta allí todo bien, la cuestión es la siguiente.
La relación Cuenta Movimiento es (1 n) o (1 *) ya que una cuenta puede tener muchos movimientos pero 1 movimiento sólo está asociado a 1 cuenta.

En JPA (En eclipse) , al conectar la base de datosMySQL con JPA , me reconoce todo bien, pero me dice que la relación de Cuentas y Movimientos es de 1, 1.

No lo entiendo. ¿Qué se debe hacer para indicarle a MYSQL que esas dos tablas son (1,n) ?
¿Hay que poner algo en el campo de la foránea? ¿Se puede de alguna manera decirle la cardinalidad 1,n tocando algún botón desde el WORKBENCH?

Muchas gracias, ojalá alguien pueda ayudarme.

base de datos – Tabla Usuarios MySQL

actualmente estoy creando una base de datos con MySQL , y tengo que crear 2 tipos de usuarios , Super-Admin y Moderadores , lo que estaba pensando era en crear una tabla User y las respectivas tablas Super-Admin y Moderadores , lo que pretendo es que crear un inventario donde se guarden una lista de productos y que el acceso de los moderadores sea solo para editar precios nombres , medidas , y obviamente el Super-Admin tendría el control total.