mysql – Error migrar una aplicacion PHP

Tengo una aplicación web en php (la hizo el que estaba antes que yo) en local. Me han pedido de migrarla a la nube, básicamente es añadir datos de empresas a la base de datos. En local funciona perfectamente, pero lo he migrado a una VM en azure, le he instalado LAMP y lo que es la página funciona correctamente, se ve y se puede navegar, pero ha perdido la funcionalidad, me explico:
Antes de la migración se podían rellenar los campos de las empresas y se enviaban a la base de datos, pero ahora en azure no, he cambiado el archivo php de la base datos, y lo que es login funciona por lo que puede acceder a la base datos, pero al insertar datos de empresas, me da este error:

Fatal error: Uncaught DelightFileUploadThrowableTotalSizeExceededError in /var/www/html/BotCamara/panel/vendor/delight-im/file-upload/src/AbstractUpload.php:60 Stack trace: #0 /var/www/html/BotCamara/panel/vendor/delight-im/file-upload/src/AbstractUpload.php(85): DelightFileUploadAbstractUpload->withMaximumSizeInBytes(5242880) #1 /var/www/html/BotCamara/panel/requerimentSubmitter.php(90): DelightFileUploadAbstractUpload->withMaximumSizeInMegabytes(5) #2 {main} thrown in /var/www/html/BotCamara/panel/vendor/delight-im/file-upload/src/AbstractUpload.php on line 60

Error AbstractUpload.php:60:
introducir la descripción de la imagen aquí

Error AbstractUpload.php:85:
introducir la descripción de la imagen aquí

Error requerimentSubmitter.php(90):
introducir la descripción de la imagen aquí

Parece que el problema ha sido subir el archivo PDF, pero el PDF pesa menos de 5MB que es el límite. Por ultimo he probado a realizar los mismo en la aplicación en local y con el mismo PDF no me ha dado problema al subirlo.

Dejo también el directorio de la app, por si puediera servir de algo, la VM solo tiene apache, mysql y php php-mysql, y lo que es todo el directorio está copiado y pegado de local a la nube por lo que los archivos son los mismo a excepción de la comunicación con la base de datos. Se “ve”, pero no “funciona”.
introducir la descripción de la imagen aquí
Espero me puedan ayudar.

Un saludo

database design – MySQL: Is it best practice to check whether some column is NULL by `NOT ISNULL(column_name)`?

Given the following schema, my question is that since I have already added the NOT NULL to IsBanned, would I have to check whether it is not null using the syntax NOT ISNULL(IsBanned)? And what’s the best practice to do the same if I didn’t add NOT NULL?

CREATE TABLE Viewer (
    AccountID varchar(25),
    IsBanned boolean                NOT NULL
                                    DEFAULT 0,
    ...
    CHECK                           (NOT ISNULL(isBanned)),
);

As you can see I

database design – MySQL: Should I add NOT NULL UNIQUE to the field for primary key?

Hi I’m completely new to MySQL, I’m creating a database and my question is that:

Should I add NOT NULL UNIQUE to the field for primary key?

For example I have:

CREATE TABLE Account (
    AccountID varchar(25) NOT NULL,
    ...
    PRIMARY KEY(AccountID)           // <------- (*)

I’m thinking about whether the (*) would implicitly mark the field as UNIQUE NOT NULL.

Btw, could anyone provide good resources about best practices of MySQL(I’m currently interested about creating table) so I won’t add redundant restriction(s) in any schema.

mysql – insert into table with default values from a select statement in php

this is the code without the variables

$sql1 = "INSERT INTO new_table (st_id,t_id,date,status) SELECT s_id, 't1234','1000-10-10','0' 
        FROM student_courses WHERE course1 = 'cs402' OR course2 = 'cs402' OR course4 = 'cs402' OR course4 = 'cs402'; ";

this works
but when i put in the variables it doesn’t

this is the sql code i have written

$course_name = "Data Mining and Ware Housing";

$table_name = "new_table";

$t_id = 't1234';

$time = time();

$c_id = 'cs402';

$sql1 = "  INSERT INTO " . $table_name . "(st_id,t_id,date,status)
        SELECT s_id, '" . $t_id . "', '" . $time . "'  ,'0' 
        FROM student_courses WHERE course1= '" . $c_id . "'  OR course2= '" . $c_id . "'  OR course3= '" . $c_id . "' OR course4='" . $c_id . "';";

but the details are not getting entered

new_table(id,st_id,t_id ,date ,status)
student_courses(s_id,course1,course2,course3,course4)

How can I find which table belongs to which two users in a MySQL database that stores user messages?

I’m building a chat application in which I want to store user messages in a MySQL database. I have came up with the solution of creating a seperate database for messages and creating tables for each conversation between users. One example table would look like this:

+-----------------+-----------------+------+-----+---------+----------------+
| Field           | Type            | Null | Key | Default | Extra          |
+-----------------+-----------------+------+-----+---------+----------------+
| message_count   | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| message_content | varchar(2000)   | YES  |     | NULL    |                |
| sent_by         | varchar(32)     | YES  |     | NULL    |                |
| sent_at         | datetime        | YES  |     | NULL    |                |
+-----------------+-----------------+------+-----+---------+----------------+

But how would I be able to figure out which table should I load for a given two users? I could store the usernames of both participants of the chat room in a column named “participants” seperated by a whitespace and use a query that looks like SELECT FROM chats WHERE participants LIKE username, but that does not sound like a healthy solution at all.

mysql – Selecionar os últimos meses, mostrando também os meses sem registros

Olá.

Estou fazendo um SELECT para retornar os últimos 06 meses, mostrando a contagem de registros (no exemplo: vendas), agrupando por ano-mês.

Minha consulta atualmente está assim:

**Schema (MySQL v5.7)**

CREATE TABLE vendas (
   id INT NOT NULL,
   data_venda DATE NOT NULL,
   PRIMARY KEY (id),
   UNIQUE (id)
);

INSERT INTO vendas
   (id, data_venda)
VALUES
    (1, '2020-10-01'),
    (2, '2020-10-01'),
    (3, '2020-12-01'),
    (4, '2020-12-01'),
    (5, '2020-12-01'),
    (6, '2020-12-01'),
    (7, '2021-02-01'),
    (8, '2021-02-01'),
    (9, '2021-02-01'),
    (10, '2021-02-01'),
    (11, '2021-02-01'),
    (12, '2021-03-01'),
    (13, '2021-03-01'),
    (14, '2021-03-01'),
    (15, '2021-03-01'),
    (16, '2021-04-01');

Query #1

SELECT
    DATE_FORMAT(data_venda, "%Y-%m") AS ano_mes,
    COUNT(id)
FROM
    vendas
WHERE
    data_venda >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY
    DATE_FORMAT(data_venda, "%Y-%m");
ano_mes COUNT(id)
2020-12 4
2021-02 5
2021-03 4
2021-04 1

View on DB Fiddle

Repare que, para ser os últimos 06 meses, deveria retornar também o mês 2020-11 e 2021-01, mostrando como 0 (zero). Assim:

ano_mes COUNT(id)
2020-11 0
2020-12 4
2021-01 0
2021-02 5
2021-03 4
2021-04 1

Já pensei em fazer o tratamento após a consulta, diretamente na programação do sistema (criar uma função para corrigir o retorno do SELECT, direto em PHP, por exemplo). Mas me parece ficar mais legível e simples se a consulta já vier completa (com meses zerados).

Acredito que a correção passa por algum SUBSELECT que construa os último 06 meses e depois combine com o meu SELECT atual (exemplo acima), mas não estou conseguindo fazer. Vi algumas perguntas aqui, mas também não consegui aplicar a resposta. Algumas falavam em criar um procedure ou ajustavam o mês dentro de um ano fixo, mas repare que nesse caso tem dois anos envolvidos.

Obrigado a quem puder auxiliar. 🙂

Set MySql read-only from K8s cluster

I have a Kubernetes cluster running with MySql app (5.7.32 MySql version). I’m trying to make MySql app be in a consistency mode, using this command:

“FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON;”

When I run this command from inside the app – it works (getting error when trying to write when the DB is locked):

kubectl exec -ti mysql-0 -c mysql — mysql

mysql> FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES(‘hello’);

But When I run this command using kubectl exec command – it doesn’t work (this command doesn’t fail, and it is able to write to the DB):

kubectl exec -ti mysql-0 -c mysql — mysql -h mysql -e “FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES(‘hello’); “

I also try using a grant privileged user, it didn’t help:

kubectl exec -ti mysql-0 -c mysql — mysql -h mysql -e “CREATE USER $USER@’$HOST’ IDENTIFIED BY ‘$PASS’; GRANT ALL PRIVILEGES ON * . * TO $USER@’$HOST’;”

kubectl exec -ti mysql-0 -c mysql — mysql -h mysql -u $USER -p$PASS -e “FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES(‘hello’); “

How Can I bring my MySql app to be in a read-only state without running the command from inside the application?

Thanks!

mysql – how do i use “?” as variables for my values so that i don’t get hacked?

Caution These methods of escaping values only works when the NO_BACKSLASH_ESCAPES SQL mode is disabled (which is the default state for MySQL servers).

In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods:

var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function (error, results, fields) {
  if (error) throw error;
  // ...
});

Escaping-Query-Values

magento2 – Don’t Sort by Name when have Mysql Search Engine Magento 2.4.1

I installed the Weltpixel AjaxLayeredNavigation extension and with the Swissup extension of Mysql Legacy Search Engine, having both activated when I want to sort by name gives me the following SQL error: Column not found: 1054 Unknown column ‘nameasc’ in ‘order clause’

If someone has gone through a similar error please it would be of great help.

mysql – Is using strings as keys for reference to other tables bad in terms of memory usage?

Yes MySql and all other rdms will store the complete email as varchar and reserve space for the number of bytes.

Integer with up to 8 bytes for a big integer will on löy use these bytes, and are so faster when referencing.

In terms of speed you use INTEGER, and consider other like varchar(36) for uuids when the need arises, for example different servers have to save data in the same table.

whith emails a unique and so be indexed for referencing, you should take the extra mile and use Integer if you expect you will have big tables.