I really wanted to be able to import text files into a column for a little project I'm working on. I know that MySQL isn't the ideal place for storing TEXT files but the project was small and so this is what I wanted. But the LOAD_FILE simply wouldn't work. Eventually I found that someone had posted a work around. You have to put the files in directoy inside of this directory: /var/lib/mysql and then give the correct permissions.
I found the information from here for the "ugly workaround":
http://stackoverflow.com/questions/4607486/mysql-load-file-returning-null
Steps I performed:
Create a new directory under mkdir -p /var/lib/mysql/:
mkdir -p /var/lib/mysql/upload_data/
Create a test file:
ps -ef > /var/lib/mysql/upload_data/test_import.txt
Verify the file has some data:
head /var/lib/mysql/upload_data/test_import.txt
Changing permissions wasn't needed on my system but others said it might be needed:
chown mysql:mysql /var/lib/mysql/upload_data/*
chmod go+rw /var/lib/mysql/upload_data/*
Give your user the needed permissions:
mysql -u root
mysql>
GRANT file ON *.* to 'test'@'%';
GRANT file ON *.* to 'test'@'localhost';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' IDENTIFIED BY 'test_password';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test_password';
exit
mysql -u test
mysql> SHOW GRANTS;
DROP TABLE IF EXISTS `test`.`table_a`;
CREATE TABLE IF NOT EXISTS `test`.`table_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`output` text COLLATE utf8_unicode_ci COMMENT 'program output from command execution if available',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='stores test files';
REPLACE INTO `test`.`table_a` (`id`, `output`) VALUES (1, NULL);
SELECT hex(LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt'));
UPDATE test.table_a
SET output=LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt')
WHERE id=1;
SELECT * FROM test.table_a WHERE id =1;
One of the problems with this ugly work around is the new folder in var/lib/mysql/ will look like a new database (unless your data directory is some where else). One way to get around this it to make the folder hidden by starting the folder name with a period.
Tuesday, September 27, 2016
Monday, September 19, 2016
Replication errors on tables in mysql schema ERROR 1146 (42S02): Table doesn't exist
I've been trying to setup orchestrator but when making a slave a co-master I would get errors like this:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(server_name:3306) show slave hosts: Will not resolve empty hostname
In order to get passed that error, I needed to create the slave_master_info table on each server. And then after creating the table, I would grant SELECT access to the orchestrator user.
This is what I was running on my servers (in addition to modifying the my.cnf):
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
COMMIT;
However, on some servers (maybe 10% of them), my attempts to create it on a slave would break replication and then the table wouldn't exist when I would grant SELECT access. I would end up with an error like this:
Error 'Table 'mysql.slave_master_info' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON mysql.slave_master_info TO ...
I found this useful blog post:
That blog post mentioned yet another post:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(server_name:3306) show slave hosts: Will not resolve empty hostname
In order to get passed that error, I needed to create the slave_master_info table on each server. And then after creating the table, I would grant SELECT access to the orchestrator user.
This is what I was running on my servers (in addition to modifying the my.cnf):
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
COMMIT;
GRANT SELECT ON mysql.slave_master_info TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*xxx';
However, on some servers (maybe 10% of them), my attempts to create it on a slave would break replication and then the table wouldn't exist when I would grant SELECT access. I would end up with an error like this:
Error 'Table 'mysql.slave_master_info' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON mysql.slave_master_info TO ...
I found this useful blog post:
http://anothermysqldba.blogspot.com/2013/09/error-1146-42s02-table-doesnt-exist.html
That blog post mentioned yet another post:
http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/head:/scripts/mysql_system_tables.sql#L103
By creating the slave_master_info table like this on the servers where replication was broken, I was able to get passed these errors:
use mysql;
CREATE TABLE IF NOT EXISTS `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
GRANT SELECT ON mysql.slave_master_info TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*xxx';
Tuesday, September 13, 2016
Setting up orchestrator
I've been testing Orchestrator for MySQL and after following the installation instructions, I kept getting these error and simply could not figure out what was wrong. I opened up a ticket and within a day, the developer got back to me and let me know what was probably wrong. I had setup replication and the MySQL instances with IP address on my local network and never setup DNS entries. Orchestrator used the hostname, not the IP address to setup replication. Every time I issued an orchestrator command, replication would start to fail. Here is the issue I opened on GitHub:
https://github.com/outbrain/orchestrator/issues/249
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) Cleanup: dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) Cleanup: dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex2:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex1:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) Cleanup: dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex0:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:28 DEBUG outdated keys: []
2016-08-29 01:38:29 DEBUG outdated keys: []
2016-08-29 01:38:30 DEBUG outdated keys: []
2016-08-29 01:38:31 DEBUG outdated keys: []
2016-08-29 01:38:32 DEBUG outdated keys: []
2016-08-29 01:38:33 DEBUG outdated keys: [delloptiplex0:3306 delloptiplex1:3306 delloptiplex2:3306]
Next I noticed more errors with Orchestrator:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(delloptiplex1:3306) show slave hosts: Will not resolve empty hostname
I got passed those by adding this into the my.cnf file for each mysql instance:
report_host=my_hostname.example
Here is a good post that illustrates that:
https://avdeo.com/2015/04/19/show-slave-hosts-on-master-not-reporting-hostname/
https://github.com/outbrain/orchestrator/issues/249
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) Cleanup: dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) Cleanup: dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex2:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex1:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) Cleanup: dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex0:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:28 DEBUG outdated keys: []
2016-08-29 01:38:29 DEBUG outdated keys: []
2016-08-29 01:38:30 DEBUG outdated keys: []
2016-08-29 01:38:31 DEBUG outdated keys: []
2016-08-29 01:38:32 DEBUG outdated keys: []
2016-08-29 01:38:33 DEBUG outdated keys: [delloptiplex0:3306 delloptiplex1:3306 delloptiplex2:3306]
Next I noticed more errors with Orchestrator:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(delloptiplex1:3306) show slave hosts: Will not resolve empty hostname
I got passed those by adding this into the my.cnf file for each mysql instance:
report_host=my_hostname.example
Here is a good post that illustrates that:
https://avdeo.com/2015/04/19/show-slave-hosts-on-master-not-reporting-hostname/
Monday, September 5, 2016
Clearing out the results of show slave status hosts after removing a slave
I've been working on setting up automatic failover in replication topologies and saw that the results of "show slave hosts" was not being cleared out even after re-assigning the slave replica to a different master. I'm not sure the best way to do "safely" and have only found "flush status" to do the trick. However, the manual only recommends doing this for debugging.
http://dev.mysql.com/doc/refman/5.6/en/flush.html
mysql> show slave hosts;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+--------------+------+-----------+--------------------------------------+
| 2 | vmware.db1_1 | 3306 | 3 | c03e14b1-6e06-11e6-a052-000c29f50e10 |
+-----------+--------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave hosts;
Empty set (0.00 sec)
http://dev.mysql.com/doc/refman/5.6/en/flush.html
FLUSH STATUS
This option adds the current thread's session status variable values to the global values and resets the session values to zero. Some global variables may be reset to zero as well. It also resets the counters for key caches (default and named) to zero and sets
Max_used_connections to the current number of open connections. This is something you should use only when debugging a query.mysql> show slave hosts;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+--------------+------+-----------+--------------------------------------+
| 2 | vmware.db1_1 | 3306 | 3 | c03e14b1-6e06-11e6-a052-000c29f50e10 |
+-----------+--------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave hosts;
Empty set (0.00 sec)
Friday, September 2, 2016
Percona Server Error - The server quit without u[FAILED]PID file
Even thought the error I see is: The server quit without u[FAILED]PID file - I think the error is supposed to be: The server quit without updating PID file (however this tends to be a different problem and you should look in the error log)
I created some new VMs using VMWare fusion and I kept getting this error after installing Percona Server 5.6 and adding in a custom my.cnf file.
The error is vague an doesn't provide any context. I am running CentOS 6.8.
[root@vmware mysql]# /etc/init.d/mysql start
Starting MySQL (Percona Server)...The server quit without u[FAILED]PID file (/var/run/mysqld/mysqld.pid).
I saw this in the error log file:
160826 03:47:28 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
The error log also mentioned that multiple instances of MySQL might be running. I did find multiple instances of MySQL running but every time I killed them and tried to re-start I would get the problem again.
I didn't see an error code in the log file but I thought it might be a permissions problem after reading on this site:
https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/6431-percona-server-not-starting
The my.cnf file that came with the install and which works fine is very simple like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Since I couldn't figure out exactly what the problem was was with my settings, I went through each section and each line until mysql would start with my settings.
I had this in my.cnf:
lc_messages_dir = /usr/share/mysql
When I commented that out, MySQL started fine. I still don't know what it is with that dir that it doesn't like. After commenting it, I checked to see what Percona Server was using:
mysql> show global variables like '%lc_messages_dir%';
+-----------------+----------------------------+
| Variable_name | Value |
+-----------------+----------------------------+
| lc_messages_dir | /usr/share/percona-server/ |
+-----------------+----------------------------+
I've had this same problem but with other settings and I've followed the same approach my commenting out lines until I've figured out what the offending my.cnf entry is.
I created some new VMs using VMWare fusion and I kept getting this error after installing Percona Server 5.6 and adding in a custom my.cnf file.
The error is vague an doesn't provide any context. I am running CentOS 6.8.
[root@vmware mysql]# /etc/init.d/mysql start
Starting MySQL (Percona Server)...The server quit without u[FAILED]PID file (/var/run/mysqld/mysqld.pid).
I saw this in the error log file:
160826 03:47:28 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
The error log also mentioned that multiple instances of MySQL might be running. I did find multiple instances of MySQL running but every time I killed them and tried to re-start I would get the problem again.
I didn't see an error code in the log file but I thought it might be a permissions problem after reading on this site:
https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/6431-percona-server-not-starting
The my.cnf file that came with the install and which works fine is very simple like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Since I couldn't figure out exactly what the problem was was with my settings, I went through each section and each line until mysql would start with my settings.
I had this in my.cnf:
lc_messages_dir = /usr/share/mysql
When I commented that out, MySQL started fine. I still don't know what it is with that dir that it doesn't like. After commenting it, I checked to see what Percona Server was using:
mysql> show global variables like '%lc_messages_dir%';
+-----------------+----------------------------+
| Variable_name | Value |
+-----------------+----------------------------+
| lc_messages_dir | /usr/share/percona-server/ |
+-----------------+----------------------------+
I've had this same problem but with other settings and I've followed the same approach my commenting out lines until I've figured out what the offending my.cnf entry is.
Subscribe to:
Posts (Atom)