Questions[mariadb]

I am trying to create an index, it always works for me but since version 10.6 it does not, I am using the following command CREATE OR REPLACE INDEX And ​​it tells me that the entry is duplicated as follows ERROR:

ERROR DE SQL(102):Entrada dupliada '9' para la clave "tipodeopcion"

The error occurs when I try to add an index with the following command:

create OR REPLACE UNIQUE INDEX tipodeopcion on nueva.opciones (terminalid) USING BTREE;

Being the following table:

CREATE TABLE `opciones` (
    `dato` MEDIUMBLOB NULL DEFAULT NULL,
    `datobinario` TINYINT(1) NULL DEFAULT NULL,
    `diayhora` DATETIME NULL DEFAULT NULL,
    `numero` DECIMAL(11,2) NULL DEFAULT NULL,
    `opciones` INT(12) NOT NULL AUTO_INCREMENT,
    `texto` TEXT NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `tipodeopcion` VARCHAR(100) NOT NULL COLLATE 'utf8mb3_general_ci',
    `tipop` INT(1) NOT NULL DEFAULT '0',
    `terminalid` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`opciones`) USING BTREE,
    INDEX `tipodeopcion2` (`tipodeopcion`) USING BTREE,
    INDEX `terminalid` (`terminalid`) USING BTREE,
    INDEX `opciones` (`opciones`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=148
;

As you can see, what I want to add is an index that is unique for the optiontype and terminalid columns, called "optiontype", and it gives an error even with heidisql, when doing it by interface, why has the error happened to someone?

Hello and thank you very much in advance for your help,

I'm trying something that I'm sure should be easy, but after searching for a few days, I've decided to ask this community. Although with some experience in programming, I'm a novice when it comes to databases...

I am using MariaDB 10.3.21 , and I have the following table:

  CREATE TABLE `Inventory` (
  `InventoryId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'InternalIdentifier',
  `ProductId` int(10) unsigned NOT NULL COMMENT 'Related Product Identifier',
  `DeviceSN` varchar(40) DEFAULT concat(year(current_timestamp()),month(current_timestamp()),dayofmonth(current_timestamp())),
  `ExternalManufactured` tinyint(1) DEFAULT 1 COMMENT 'if =1 No Needs generate My own serial Number',
  PRIMARY KEY (`InventoryId`),
  KEY `Inventory_FK` (`ProductId`),
  CONSTRAINT `Inventory_FK` FOREIGN KEY (`ProductId`) REFERENCES `Products` (`ProductId`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COMMENT='Device Inventory table'

I would like to insert an automatic value for DeviceSN. This value is the current date and a counter. This counter would be the amount of equipment NOT manufactured externally ( that is, the number of columns whereExternalManufactured=0 )

I can create a helper table with this counter, which is added via a trigger when there is data inserted into the table Inventory. What I don't know is how to concatenate this value (counter) to the current date and make it the default value in DeviceSN.

I've tried with triggers, and with generated columns , but I don't know how to add this counter to be able to concatenate it with the current date...

In fact, with a counter, even if the condition is not met, ExternalManufactured=0it would be enough, but I have tried with an auto-increment column:

ALTER TABLE DeviceManagement.Inventory MODIFY COLUMN DeviceSN varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT concat(year(current_timestamp()),month(current_timestamp()),dayofmonth(current_timestamp()),CAST(InventoryId AS CHAR)) NULL;

And I get the error:

Function or expression 'AUTO_INCREMENT' cannot be used in the DEFAULT clause of `InventoryId

Any advice will be welcome...

Thank you very much!

I would like to know how a new record can be inserted in a table when a condition is met. In SQLlite you can as far as I know, but in MARIADB I can't do it. An example of what I would like to do:

INSERT INTO rubros (nombre) SELECT 'Articulos varios' WHERE nombre != 'Articulos varios' ;

This code is tested and returns the following error:

sql error(1064): Something is wrong in your syntax near "WHERE name != 'Miscellaneous Items'" on line 1

In this case, it would be to enter in the table "Items" the record with the name "Miscellaneous articles" if it does not exist. Namely

INSERT INTO table (value) SELECT * WHERE (Condition) In condition, if it is fulfilled, the record is inserted.

PS: I have tried this answer and it didn't work, I have MariaDB version 10.3.12. The code I used to try to use "EXISTS" is the following code:

INSERT INTO rubros (nombre, descripcion) 
SELECT 'Articulos varios', '' 
WHERE EXISTS (
    SELECT nombre FROM rubros WHERE nombre = 'Articulos varios'
)  ;

The error that I get is the following:

sql error(1064): Something is wrong in your syntax near " WHERE EXISTS (SELECT name FROM items WHERE name = 'Miscellaneous Items') " on line 1

From already thank you very much

I am following this tutorial to set up a cluster with MariaDB and Galera.

My question is, if for whatever reason (the machine is turned off, it is removed from the network, etc.) node 1 is affected, will the rest of the nodes continue to maintain database replication?

I have arrays that I want to save in the database, the arrays have about 1000 elements, should I save them in string or in what type?

    +--------------------+ 
    tabla caracteristicas 
    piel [datos] 
    ojos [datos] 
    expresiones  [datos]
   +--------------------+ 

I must save the complete arrays and not element by element

in the bd I put

piel           varchar(1000) not null,

But what if my skin array has 2000 elements? varchar(1000) would no longer suffice, do I need unlimited size?

Note : Also, if I want to save 1000 elements with varchar(1000) it would not supply either, because since it is a string it counts up to the brackets, commas...

when I insert with json ( VALUES (1,'{"data": {"language": "spanish", "age": 39}}',1,1);) according to @Alfredo's answer I get the following: