Ошибка 1193 mysql

Re-create the dump file by appending the --set-gtid-purged=OFF option would resolve the problem.

It was because GTIDs was added in MySQL 5.6, which is not recognized by the earlier versions.

Your command might look like below:

mysqldump -u username -ppassword -h mydbhost --set-gtid-purged=OFF db_name > dump_file.sql

More on my story, I got the same problem with a dump file originated from MySQL 5.7. I was trying the import the data into a new CentOS 7 installation with the default MariaDB installation, which is 5.5 (I guess).

The first idea came to my mind was to upgrade to latest MariaDB. Luckily their website provides a great utility to help set the package repository for Linux variaties. Moreover, digitalocean has a very short and clear guide for the upgrade process, thanks to them too!

While upgrading to the lastest MariaDB stable version 10.2 does NOT get rid of this problem. So I still have to use the option mentioned above, but it let me upgrade to the latest MariaDB anyway.

Another problem after my upgrading was that the innodb_additional_mem_pool_size config from my-innodb-heavy-4G.cnf is not supported anymore on the latest MariaDB, server failed to start. From the MySQL documentation, it was removed from MySQL 5.7. I can start the server after commenting it out. I’m not the DB expert, I would not spend more time to check how exactly MariaDB version mapping to MySQL DB, and what difference they have.

Источник

I have recently installed MySQL and phpMyAdmin, but when I try to login to phpMyAdmin with the root credentials I get the following error for this query:

SQL query:

SET lc_messages = 'en_US';

MySQL said: Documentation

#1193 — Unknown system variable ‘lc_messages’

Why am I experiencing this error?

Blue

22.5k7 gold badges60 silver badges90 bronze badges

asked Jun 28, 2016 at 19:12

I faced the same problem. Check your mySQL version:

For mySQL 5.5, use phpMyAdmin 4.4.x and above

For mySQL 5.1, use phpMyAdmin 4.0.x

Hope this helps someone.

answered Nov 1, 2016 at 19:31

Shadi AlnamroutiShadi Alnamrouti

11.6k4 gold badges55 silver badges54 bronze badges

It isn’t true. PhpMyAdmin can work with older Mysql as always.

Quick fix is easy

in /usr/share/phpMyAdmin/libraries/common.inc.php delete these lines

if (PMA_MYSQL_INT_VERSION < $cfg['MysqlMinVersion']['internal']) {
  PMA_fatalError(
    __('You should upgrade to %s %s or later.'),
    array('MySQL', $cfg['MysqlMinVersion']['human'])
  );
}

in /usr/share/phpMyAdmin/libraries/classes/DatabaseInterface.php delete these lines

if (! empty($locale)) {
  $this->query("SET lc_messages = '" . $locale . "';", $link, self::QUERY_STORE);
}

Reference https://github.com/phpmyadmin/phpmyadmin/issues/12822

answered Dec 23, 2017 at 23:06

Uninstall your mysql version and all dependencies.
To see all your mysql and dependencies packages installeds try this command:

- rpm -qa | grep mysql

For uninstall:

- yum erase mysql
- yum erase mysqlPackageDependencie
...

When all dependencies are uninstalled install the new mysql version:

- yum install mysql55-server

Start your Service Mysql:

- service mysqld start

Great! All works perfect now!

answered Dec 16, 2016 at 16:47

I found that I had an older version of phpmyadmin and the error was because of a change they had made to PHP 7.

If your running any version of php 5.5 or higher you will get this error unless you update your phpmyadmin.

answered Aug 2, 2016 at 14:08

JeremyJeremy

1611 gold badge1 silver badge4 bronze badges

I followed Krashan Brahmanjara’s instructions. And I deleted

    if (! empty($locale)) { $this->query("SET lc_messages = '" . $locale . "';", $link, self::QUERY_STORE);
}

I also deleted if ($GLOBAL

answered Mar 20, 2020 at 15:29

I was having the same problem unexpectedly, but I tried the Krashan Brahmanjara ‘s answer’s after which the lc_message error got resolved. But it was still asking me to upgrade the PHPMyAdmin.

For this,

I opened services.msc and stopped MySQL service as I have MySQL Server 5.0 installed apart from XAMPP’s MySQL.

(I thought of doing this after seeing the database tables on PhpMyAdmin were of my other Mysql and not the XAMPP ones.)

Upon reloading the PHPMyAdmin page, it started working with no error!

So I guess PMA was just trying to use a MySQL that was running on the computer without checking if it was the XAMPP’s MySQL or not.

answered Oct 12, 2022 at 6:50

Источник

Steps to reproduce

Install IIS On windows 10 with php 7.0 and mysql
Install PHP My Admin
Log in

Expected behaviour

Log me into my MySQL server.

Actual behaviour

Logs in and says #1193 - Unknown system variable 'lc_messages'

Server configuration

Operating system: Windows 10

Web server: IIS 10

Database: MySQL

PHP version: 7.0

phpMyAdmin version: Latest

Client configuration

Browser: Firefox

Operating system: Windows 10

Could you also please let us know what MySQL version you have installed?

This variable is available since MySQL 5.5, so you’re most likely running phpMyAdmin with older version what is not supported…

nijel

added
the

question

Used when we need feedback from the submitter or when the issue is a question about PMA

label

Dec 17, 2016

It isn’t true PhpMyAdmin can work with older Mysql.
Quick fix is easy

in common.inc.php delete these lines

if (PMA_MYSQL_INT_VERSION < $cfg[‘MysqlMinVersion’][‘internal’]) {
PMA_fatalError(
__(‘You should upgrade to %s %s or later.’),
array(‘MySQL’, $cfg[‘MysqlMinVersion’][‘human’])
);
}

in DatabaseInterface.php delete these lines

if (! empty($locale)) {
$this->query(
«SET lc_messages = ‘» . $locale . «‘;»,
$link,
self::QUERY_STORE
);
}

Both conditions should be fixed using proper version test

There are some other things which will break with older MySQL version (eg. see bc7d57f).

MySQL 5.5 is available for more than 7 years, if you are using something older, use older phpMyAdmin as well.

github-actions
bot

locked as resolved and limited conversation to collaborators

Jun 22, 2020

Источник

According to MySQL docs, the error #1193 occurs when you use wrong code for SQLSTATE.

Message: Unknown system variable %s

And, as you can see on the same page, the SQLSTATE 99003 is not defined.

Comments

Ok so i’m working on triggers, and it tells me it(MySQL workbench 5.2) doesn’t recognize this variable.
*Error Code: 1193. Unknown system variable error_msg_3*

I think it would be correct using it in a trigger, please help me

CREATE TRIGGER controlla_MaxCARDINALITA_INSERT
BEFORE INSERT ON SENTIERO__HA__TAPPA
FOR EACH ROW 
BEGIN

DECLARE max_cardinalita INTEGER;
DECLARE error_msg_3 CONDITION FOR SQLSTATE '99003';

SELECT COUNT(*) into max_cardinalita
FROM SENTIERO__HA__TAPPA
WHERE IDsentiero=NEW.IDsentiero;

IF max_cardinalita>=10 THEN
        SIGNAL error_msg_3;
        SET error_msg_3='INSERT: Il sentiero ha già il massimo numero di tappe consentito';

END IF;

END$$

EDIT ::

I tried this, and it seems working

DECLARE msg VARCHAR(255);
set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ');
signal sqlstate '45000' set message_text = msg;

please see my edit, why does that code work? there isn’t 45000 in the table, i think
@Jimmy5nomana Please again, refer to the docs on usage of SIGNAL clause. It clearly mentions: SIGNAL is the way to “return” an error. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. To signal a generic SQLSTATE value, use ‘45000’, which means unhandled user-defined exception.
Thanks, last question for you: mysql doesn’t let me using multiple triggers for the same event and action time for one table… so i heared about functions and procedures; are them the solution? I’d like to solve it in the easiest way, i’m in hurry. Thanks
@Jimmy5nomana For that, you’d get better advices over at DBA stackexchange
That’s not theory!!! if you have 3 triggers as these: AFTER UPDATE ON TABLE 1… how could they run on that table?

Recents

Источник

If your run SHOW SLAVE STATUSG on the Slave, and you see Slave_IO_Running: Yes, then the IO Thread is fine.

Notwithstanding, please note the Warnings

2016-11-18 22:48:03 12808 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

Error Code 0 means it’s not an error, just informational.

2016-11-18 22:48:03 12808 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable ‘binlog_checksum’, Error_code: 1193

You mentioned the error involves the variable binlog_checksum.

According to the MySQL Documentation for binlog_checksum

Enabling this option causes the master to write checksums for events written to the binary log. Set to NONE to disable, or the name of the algorithm to be used for generating checksums; currently, only CRC32 checksums are supported. As of MySQL 5.6.6, CRC32 is the default.

This option was added in MySQL 5.6.2.

Since your Master is 5.5.42, there is no binlog_checksum available to compare the Master to the Slave. The warning is valid.

Perhaps setting binlog_checksum to ‘NONE’ on the Slave might disable this check.

2016-11-18 22:48:03 12808 [Warning] Slave I/O: Unknown system variable ‘SERVER_UUID’ on master. A probable cause is that the variable is not supported on the master (version: 5.5.42-log), even though it is on the slave (version: 5.6.31-log), Error_code: 1193

This is the same problem. The option server_uuid does not exist in MySQL 5.5.42 (the Master) as the message itself says.

EPILOGUE

As long as replication is running you are OK for the moment. It strongly recommend get your Master to MySQL 5.6.31 ASAP. Why ?

MySQL 5.6 introduced microseconds to DATETIME. If the Master (5.5.42) sends a DATETIME to the Slave (5.6.31), the IO Thread will break immediately. Please read my post from 2015-01-02 where I mentioned this.

You could work around this DATETIME mess by switching to STATEMENT replication (setting binlog_format to STATEMENT) on both Master (5.5) and Slave (5.6).

Источник

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

Client configuration

Related videos on Youtube

Comments

Recents

Related

EPILOGUE