Error Code 1241. Operand should contain 1 column(s) function MYSQL

Cree una función en mysql para trabajar con una secuencia el codigo de la función es el siguiente:

FUNCTION `fn_nextValue`(SEQNAME varchar(50)) RETURNS bigint(20)

BEGIN

DECLARE EXIST_SEQ INT;
DECLARE CUR_VALUE BIGINT(20);

SET EXIST_SEQ = (SELECT COUNT(1) FROM bd_triggers.secuencias WHERE nombreSecuencia = SEQNAME);

IF EXIST_SEQ > 0 THEN
    UPDATE bd_triggers.secuencias
    SET secuenciaValor = ((IFNULL(secuenciaValor,0) + secuenciaIncremento) < secuenciaMaximo,
                            IFNULL(secuenciaValor,0) + secuenciaIncremento,
                            secuenciaMaximo);
END IF;

SET CUR_VALUE = (SELECT secuenciaValor FROM bd_triggers.secuencias WHERE nombreSecuencia = SEQNAME); 

RETURN CUR_VALUE;
END

para la tabla de secuencias la cual tiene la siguiente estrcutura:

introducir la descripción de la imagen aquí

En mi tabla de secuencias ya existe un registro:
SEQ_ID |1 |1 |18446744073709551615 |NULL

Pero al intentar llamar la función (SELECT bd_triggers.fn_nextValue ('SEQ_ID');) me marca este error:
Error Code 1241. Operand should contain 1 column(s)

Si me pudieran indicar donde cometi el error para poder corregirlo me ayudarian mucho gracias.

All topic

Mysql Restore – ERROR 1227 (42000) at line 30908: Access denied DEFINER issue

Im trying to restore the database dump which has the DEFINER=test@%.

Backup file is 50GB.

OS – CentOS 7.5

While restoring this, I got the below error.

ERROR 1227 (42000) at line 30908: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The line 30908 is,

CREATE DEFINER=`test`@`%` PROCEDURE `myprod`(IN id INT)

So I tried to remove the definer using perl.

perl -p -i.bak -e "s/DEFINER=`w.*`@`d[0-3].*[0-3]`//g" my_dump_file.sql

Its executed, but after that also, I got the same issue and its not changed in file.

Then I tried,

sed -e 's/DEFINER[ ]*=[ ]*[^*]**/*/' < my_dump_file.sql > my_dump_file.new

This is also executed, and tried to restore my_dump_file.new but still same issue on the same line.

Then finally I tried to create an user on the MySQL.

create user `test`@`%` identified by 'test';
grant all on *.* to `test`@`%`;
flush privileges;

Again, Im getting the same issue.

Can anyone help me to fix this?

All topic

Trying to use MySQL Workbench with TCP/IP over SSH – failed to connect

I can’t connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What’s going on?

I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP over ssh. It runs on port 3306 on the remote server where command-line mysql works fine.

I used the following session details:

  • Connection Method: TCP/IP over SSH.
  • SSH Hostname: mysql.myhost.com:3306
  • SSH username: my linux login
  • SSH public key file: my local public key file
  • MySQL hostname: 127.0.0.1 MySQL
  • Server Port: 3306
  • Username: root

I get an error message when I try to connect:
“Failed to connect to MySQL at 127.0.0.1:3306 through SSH tunnel at mysql.myhost.com with user root”

“Can’t connect to MySQL server on ‘127.0.0.1’ (10061)”

As another test – I set up a SSH tunnel with port 3306 using Putty and I can connect OK using MySQL Workbench through that tunnel which forwards connections to my local 3306 to the remote server as described above. But I can’t get “TCP/IP over SSH” working in Workbench.

Secondary question: when Workbench asks for “Path to SSH public key file” doesn’t it really need my private key file?

All topic

Unable to get Result Using MySQL Inner Joins

I am unable execute query using joins as it is.

But,after adding some more conditions using WHERE CLAUSE(conditions for every table) it is working.But still taking some time.

Could any one explain why it is working after adding more conditions in WHERE Clause

Query and Query Execution Plan are as follows

Query Plan

All topic

installing mysql 8.0.12 in Mac 10.13.6

I have just installed mysql 8.0.12 in Mac 10.13.6 but when I try to open the localhost I got this error:

enter image description here

All topic

MySQL Range partitioning vs Hash partitioning

We have one large table with 100 million records and its size is 100 GB and data is continuously growing day by day. This table has 30 partitions with RANGE by client_id(table's column). One client_id is having minimum 1 million records.

For solution we have break this large table into 30 small tables with group of client_id data in each table. Now we want to do partition in each table.

  • So which partition would work for us best: Range or Hash?

We don’t know which table has which set of client_id.

Thanks

All topic

Failed to open the bootstrap file in MySQL

After doing an installation of MySQL Ver. 5.7.10 for RH/Oracle Linux I get an error that during the initialization the bootstrap file was not found. I’m not sure where this may come from, as it is a fresh installation and the error was not yet covered.

The rights to the folder were set up correctly by the installer:

# ls -l /var/lib | grep mysql-files
drwxr-x---. 2 mysql         mysql         4096 Dec  8 15:13 mysql-files

mysqld.log:

2015-12-08T14:13:24.524728Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-08T14:13:26.107212Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-12-08T14:13:26.440941Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-12-08T14:13:26.612704Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d95e041f-9db5-11e5-a33f-525400eb9be6.
2015-12-08T14:13:26.650484Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2015-12-08T14:13:26.668489Z 1 [Note] A temporary password is generated for root@localhost: YZ+#zIY/m2=:
2015-12-08T14:13:39.994706Z 1 [ERROR] Failed to open the bootstrap file /var/lib/mysql-files/install-validate-password-plugin.OFJRaf.sql
2015-12-08T14:13:39.994728Z 1 [ERROR] 1105  Bootstrap file error, return code (0). Nearest query: 'LSE SET @sys.tmp.table_exists.SQL = CONCAT('SELECT COUNT(*) FROM `', in_db, '`.`', in_table, '`'); PREPARE stmt_select FROM @sys.tmp.table_exists.SQL; IF (NOT v_error) THEN DEALLOCATE PREPARE stmt_select; SET out_exists = 'TEMPORARY'; END IF; END IF; END;
'
2015-12-08T14:13:39.994880Z 0 [ERROR] Aborting

All topic

mysql (Perconadb) Galera/Xtrabackup cluster join fails with “Invalid Argument”

I have a MySQL Galera cluster, using Perconadb and Xtrabackup. The nodes can start stand-alone, or can join the cluster if only an IST is required. However, if an SST is required, then this runs to completion and then fails.

The logs show that, after the xtrabackup SST is completed, it exits with stats 22 (Invalid Argument) causing the SST to be rolled back and the node fails to come up.

2018-08-09 00:43:25 860 [Note] WSREP: 0.0 (xmdadb01): State transfer to 1.0 (xmdadb02) complete.
2018-08-09 00:43:25 860 [Note] WSREP: Member 0.0 (xmdadb01) synced with group.
2018-08-09 00:43:25 860 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.93.40.122' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '860'  '' : 22 (Invalid argument)
2018-08-09 00:43:25 860 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2018-08-09 00:43:25 860 [ERROR] WSREP: SST script aborted with error 22 (Invalid argument)
2018-08-09 00:43:25 860 [ERROR] WSREP: SST failed: 22 (Invalid argument)
2018-08-09 00:43:25 860 [ERROR] Aborting

The relevant parts of the my.cnf:

[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_provider_options="gcache.size=256M;gcs.fc_factor=1.0;gcs.fc_limit=512;gcs.fc_master_slave=YES;pc.checksum=true;"
wsrep_cluster_name="galera01-xmd"
wsrep_cluster_address="gcomm://10.93.40.121:4567,10.93.40.122:4567"
wsrep_node_name=xmdadb02
wsrep_node_address="10.93.40.122"
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sst_user:password-goes-in-here

As the SST runs, I can see the files coming over into /var/lib/mysql/.sst, so I know this is working. I have verified the user and password are correct. However, why is the xtrabackup-v2 returning 22, and how can I stop it from doing so in order for the SST to complete?

Annoyingly, when this setup was first installed, SST worked without issue. I do not know what changed in the intervening time to prevent SST while still allowing IST to work.

All topic

Múltiples criterios en búsqueda AJAX en mySQL con PHP

Muy buenas!!

Tengo un script para buscar en mySQL con php y ajax que funciona correctamente con 1 campo input. En el index.php tengo el formulario y el script y en el archivo fetch.php tengo el código de búsqueda.

Ahora necesito poner otro campo de búsqueda tipo dropdown. El script tendrá que coger los valores de ambos campos, enviárselos al archivo fetch.php con el método POST y éste deberá leerlos para poder hacer la búsqueda.

¿Podríais echarme un cable?
Gracias!

Archivo index.html:


    
        
        DEMO
        
        
        
    
    
        


TITULO DEMO


¿Qué canción estás buscando?






Archivo fetch.php:

 0)
{
    $output .= '
'; while($row = mysqli_fetch_array($result)) { $output .= ' '; } echo $output; } else { echo '¡No encontramos ninguna canción!'; } ?>

All topic

Canción Año Disco Duración
'.$row["nombre"].' '.$row["ano"].' '.$row["disco"].' '.$row["duracion"].'

how to retrieve data from foreign key in php mysql? [on hold]

$query_importer1 = "SELECT item_name FROM items where item_id ='".$row1["item_id"]."' limit 1"; 

$result_importer1 = mysqli_query($conn,$query_importer1);

while ($row = mysqli_fetch_array($result_importer1)) 
{
    $item_id = $row['item_id'];
    $item_name = $row['item_name'];
    echo '  '.$row['item_name'].'
'; }

All topic