Re: can't start mariadb with client certs specified
hopefully you did run mysql_upgrade first on the slave as all docs in that context saying and after that on the master Cool!! Yeah, running mysql_upgrade did the trick. After I performed that on the slave, that error went away. I actually did the same on both nodes figuring it would be best to keep both nodes identical. Sorry, I'm a little new to database administration. Been working mostly on the SA side for years now however. however, i would just double-rsync (hot and than cold) the whole datadir to the not running slave and just start replication with that binary identical copies from scratch, doing that in case of any replication issues for many years now and if it's just for safety What I'm doing is using a dump from last night's backup on both nodes. Once I get replication working the way I want I'm going to do another final dump from the live web node. And then stop replication and import that backup into both nodes. Then start it up again and point the web nodes to the new HA/Mariadb setup that I'm working on. We've been having some availability problems with the database lately on this wiki site. The site runs on 3 t2 micros and the database lives on one of the web nodes. And occasionally the database crashes because it's too squeezed for memory. I figure that moving mysql (or mariadb in this case) to another 2 nodes and going for high availability will solve that problem. So now that I have replication working fine going from db1 to db2 in master slave, I just reversed the replication direction of the replication so I can have master/master working. So now I have master / slave going from db1 - db2 and just a minute ago I setup master / slave going from db2 - db1. And if I'm not mistaken that's all that Master/Master is? Is there anything else I need to worry about really, in terms of achieving HA? Not sure if I'm oversimplifying this Thanks, Tim On Wed, Jul 22, 2015 at 10:38 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 22.07.2015 um 16:24 schrieb Tim Dunphy: Hi Reindl, what about running mysql_upgrade *directly* after the major update and *before* touch anything else? That was precisely what happened. In setting up the new database machine, puppet had installed version 5 of mariadb. Before even starting up version of for the first time, I uninstalled the RPM's for that version and installed version 10.0.20 from the mariadb repo. However I was able to solve this problem my recreating the keys and certs. I had setup master/slave replication using this tutorial: https://www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-centos-5.4 I'm not sure what the problem is with the way the keys are generated in this article that could have caused that last, rather long email. :) So after I regenerated the keys and certs using this method: 182 openssl genrsa -des3 -out db1.example.com.key 4096 183 openssl rsa -in db1.example.com.key -out db1.example.com.key 184 openssl req -new -key db1.example.com.key -out db1.jokefire.com.csr 185 openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out db1.example.com.crt you need the same certs and same CA on both sides *Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 * Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Any idea what that error means and how I can get rid of it? hopefully you did run mysql_upgrade first on the slave as all docs in that context saying and after that on the master however, i would just double-rsync (hot and than cold) the whole datadir to the not running slave and just start replication with that binary identical copies from scratch, doing that in case of any replication issues for many years now and if it's just for safety -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: can't start mariadb with client certs specified
Hi Reindl, what about running mysql_upgrade *directly* after the major update and *before* touch anything else? That was precisely what happened. In setting up the new database machine, puppet had installed version 5 of mariadb. Before even starting up version of for the first time, I uninstalled the RPM's for that version and installed version 10.0.20 from the mariadb repo. However I was able to solve this problem my recreating the keys and certs. I had setup master/slave replication using this tutorial: https://www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-centos-5.4 I'm not sure what the problem is with the way the keys are generated in this article that could have caused that last, rather long email. :) So after I regenerated the keys and certs using this method: 182 openssl genrsa -des3 -out db1.example.com.key 4096 183 openssl rsa -in db1.example.com.key -out db1.example.com.key 184 openssl req -new -key db1.example.com.key -out db1.jokefire.com.csr 185 openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out db1.example.com.crt I was able to start up both db's 1 and 2 with the SSL keys and certs specified in both the client and server sections of the my.cnf. Like this example from the first node shows: [root@db1:~] #egrep -v '^#|^$' /etc/my.cnf [mysqld] ...redacted. sslssl-ca=/opt/mysql/ca.crt ssl-cert=/opt/mysql/db1.example.com.crt ssl-key=/opt/mysql/db1.example.com.key ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:!aNULL server-id=1 log_bin=/var/log/mariadb/mariadb-bin.log gtid-domain-id=1 ... [mysqld_safe] [client] ssl-ca=/opt/mysql/ca.crt ssl-cert=/opt/mysql/db1.example.com.crt ssl-key=/opt/mysql/db1.example.com.key Anyplace you see a line of dots there was omitted to save space. I am facing a new issue however. It looks as if I've been able to get slave replication working as I try to demonstrate below: [root@db2:~] #mysql -e show slave status \G | egrep -i Slave_IO_State|Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master Slave_IO_State: Waiting for master to send event Master_Log_File: mariadb-bin.04 Relay_Master_Log_File: mariadb-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 But I'm seeing an error that I've never seen before relating to 'gtid': [root@db2:~] #mysql -e show slave status \G | egrep -i error|errno Last_Errno: 1146 *Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 * Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Any idea what that error means and how I can get rid of it? I've done some reading on the error and tried setting my gtid variables for both nodes. For example this is what I have in the first node: MariaDB [(none)] show variables like '%gtid%'; ++---+ | Variable_name | Value | ++---+ | gtid_binlog_pos| | | gtid_binlog_state | | | gtid_current_pos | | | gtid_domain_id | 1 | | gtid_ignore_duplicates | OFF | | gtid_seq_no| 0 | | gtid_slave_pos | | | gtid_strict_mode | OFF | | last_gtid | | ++---+ 9 rows in set (0.00 sec) Can you please help me out with this one as well? Thanks, Tim On Wed, Jul 22, 2015 at 4:31 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 22.07.2015 um 06:39 schrieb Tim Dunphy: Hey guys, I'm trying to setup multi master replication using SSL under MariaDB 10.20. I've been able to specify my client certs on the second node (db2) with no issue and start up the mysql service what about running mysql_upgrade *directly* after the major update and *before* touch anything else? 150722 4:18:47 [ERROR] Column count of mysql.file_summary_by_event_name is wrong. Expected 23, found 5. Created with MariaDB 50541, now running 100020. Please use mysql_upgrade to fix this error -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
can't start mariadb with client certs specified
Hey guys, I'm trying to setup multi master replication using SSL under MariaDB 10.20. I've been able to specify my client certs on the second node (db2) with no issue and start up the mysql service. But for some reason when I do the same on the first node (db1) the mysql service takes a really long time and then times out with the following message. [root@db1:~] #systemctl start mysql Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details. [root@db1:~] #systemctl status mysql.service -l mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql) Active: failed (Result: timeout) since Wed 2015-07-22 02:58:53 UTC; 5min ago Process: 22906 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS) Process: 23247 ExecStart=/etc/rc.d/init.d/mysql start (code=killed, signal=TERM) Jul 22 02:53:53 db1 systemd[1]: Starting LSB: start and stop MySQL... Jul 22 02:58:53 db1 systemd[1]: mysql.service operation timed out. Terminating. Jul 22 02:58:53 db1 systemd[1]: *Failed to start LSB: start and stop MySQL.* Jul 22 02:58:53 db1 systemd[1]: *Unit mysql.service entered failed state.* Jul 22 02:58:53 db1 mysql[23247]: Starting MySQL... But at the end, mysql (mariadb) is actually running, but its not running correctly. [root@db1:~] #ps -auxwww | grep mysql | grep -v grep root 1867 0.0 0.1 115344 1696 ?S04:18 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/db1.pid mysql 1976 0.1 9.5 722928 97256 ?Sl 04:18 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/lib/mysql/db1.pid --socket=/var/lib/mysql/mysql.sock And mysql is listening on the right port: [root@db1:~] #lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 1976 mysql 16u IPv6 100319 0t0 TCP *:mysql (LISTEN) If I try to go into the mysql command prompt I get this following error: [root@db1:~] #mysql ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed And I'm seeing the following errors in the logs: [root@db1:~] #grep -i error /var/log/mariadb/mariadb.log 150722 4:18:47 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it 150722 4:18:47 [ERROR] Column count of mysql.events_waits_current is wrong. Expected 19, found 16. Created with MariaDB 50541, now running 100020. Please use mysql_upgrade to fix this error. 150722 4:18:47 [ERROR] Column count of mysql.events_waits_history is wrong. Expected 19, found 16. Created with MariaDB 50541, now running 100020. Please use mysql_upgrade to fix this error. 150722 4:18:47 [ERROR] Column count of mysql.events_waits_history_long is wrong. Expected 19, found 16. Created with MariaDB 50541, now running 100020. Please use mysql_upgrade to fix this error. 150722 4:18:47 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure 150722 4:18:47 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type bigint(20), found type int(11). 150722 4:18:47 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure 150722 4:18:47 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure 150722 4:18:47 [ERROR] Column count of mysql.file_summary_by_event_name is wrong. Expected 23, found 5. Created with MariaDB 50541, now running 100020. Please use mysql_upgrade to fix this error. 150722 4:18:47 [ERROR] Column count of mysql.file_summary_by_instance is wrong. Expected 25, found 6. Created with MariaDB 50541, now running 100020. Please use mysql_upgrade to fix this error. 150722 4:18:47 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure 150722 4:18:47 [ERROR] Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11). 150722 4:18:47 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure 150722 4:18:47 [ERROR] Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11). 150722 4:18:47 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure 150722 4:18:47 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure 150722 4:18:47 [ERROR] Native table
Re: can't authenticate ssl user account
Hi Reindel, you client configuration shows no indication for SSL, i see it in my.cnf only in the [mysqld] section and remember when you initrialize replication you need to specify it there too i doubt there is anything to change the logging but since you *know* what that user requires that should really not be the problem - said from somebody using SSL for any mysql connection over TCP for years now (replication, php-applications, cli-client...) Ok!! Thanks. But when I try to setup my client configuration to use SSL, mariadb server refuses to start. It times out: [root@db2:~] #systemctl status mysql.service mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql) Active: failed (Result: exit-code) since Tue 2015-07-21 18:29:24 UTC; 13s ago Process: 19965 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS) Process: 21973 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=1/FAILURE) Jul 21 18:29:23 db2 systemd[1]: Starting LSB: start and stop MySQL... Jul 21 18:29:24 db2 mysql[21973]: Starting MySQL. ERROR! Jul 21 18:29:24 db2 systemd[1]: mysql.service: control process exited, code=exited status=1 Jul 21 18:29:24 db2 systemd[1]: Failed to start LSB: start and stop MySQL. Jul 21 18:29:24 db2 systemd[1]: Unit mysql.service entered failed state. Here's my my.cnf on the client side that is causing the time out error to occur: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 old_passwords=1 ssl server-id=2 replicate-do-db=jfwiki [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid master-connect-retry=60 !includedir /etc/my.cnf.d [client] ssl-ca=/opt/mysql/ca-cert.pem ssl-cert=/opt/mysql/client-cert.pem ssl-key=/opt/mysql/client-key.pem Any idea why that's happening or how to correct it? Thanks, Tim On Tue, Jul 21, 2015 at 4:25 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 21.07.2015 um 05:03 schrieb Tim Dunphy: I see absolutely NO indication as to why the login for the 'slave2' user (that requires SSL) is failing So my questions are 1) how to I bump up the verbosity on the logs so I can get an indication as to why this is failing? 2) what is the best way to troubleshoot this? you client configuration shows no indication for SSL, i see it in my.cnf only in the [mysqld] section and remember when you initrialize replication you need to specify it there too i doubt there is anything to change the logging but since you *know* what that user requires that should really not be the problem - said from somebody using SSL for any mysql connection over TCP for years now (replication, php-applications, cli-client...) CHANGE MASTER TO MASTER_HOST='masterip', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_CONNECT_RETRY=3600, MASTER_SSL=1, MASTER_SSL_CA='/etc/mysqlssl/ca.crt', MASTER_SSL_CERT='/etc/mysqlssl/client.pem', MASTER_SSL_KEY='/etc/mysqlssl/client.pem'; START SLAVE; -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
can't authenticate ssl user account
Hey all, I need to setup replication via SSL. 2 nodes master/master for H/A. Then 2 slave nodes for backup. Node 3 will slave off of node 1, and node 4 will slave off of node 2 for redundant backups. Nodes 3 4 will store backups to a directory mapped to S3 via S3FS. All nodes are physical. We're using mariadb-5.5.41 server on CentOS 7 hosts. It's a neat plan! However I am unable to get authentication to work with ssl required of the user. We already have a CA certificate and key established in the environment. And we used those to generate the cert and key to be used with mariadb. This is the process we used to generate the cert/key: openssl genrsa -des3 -out db1.example.com.key 4096 openssl req -new -key db1.example.com.key -out db1.example.com.csr openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out db1.example.com.crt Using those keys I put together this my.cnf file. Haven't gotten around to configuring replication yet, as I have yet to get ssl logins to work. [root@db1:~] #cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd ssl ssl-ca=/opt/mysql/ca.crt ssl-cert=/opt/mysql/db1.example.com.crt ssl-key=/opt/mysql/db1.example.com.key [mysqld_safe] general_log_file=/var/log/mariadb/mariadb.log general_log=1 log-error=/var/log/mariadb/mariadb_error.log pid-file=/var/run/mariadb/mariadb.pid log_slow_queries=/var/log/mysql/mysql-slow.log long_query_time=2 log-queries-not-using-indexes # # include all files from the config directory # !includedir /etc/my.cnf.d Made sure mariadb could read the cert files: [root@db1:~] #ls -ld /opt/mysql/ /opt/mysql/* drwx--. 2 mysql mysql 86 Jul 20 06:20 /opt/mysql/ -r. 1 mysql mysql 2212 Jul 20 05:14 /opt/mysql/ca.crt -r. 1 mysql mysql 1956 Jul 20 05:17 /opt/mysql/db1.example.com.crt -r. 1 mysql mysql 3247 Jul 20 05:15 /opt/mysql/db1.example.com.key And restarted mariadb. And if I take a look at my SSL variables in my mysql command line, everything is looking good. MariaDB [mysql] show variables like '%ssl%'; +---+-+ | Variable_name | Value | +---+-+ | have_openssl | YES | | have_ssl | YES | | ssl_ca| /opt/mysql/ca.crt | | ssl_capath| | | ssl_cert | /opt/mysql/db1.example.com.crt | | ssl_cipher| | | ssl_key | /opt/mysql/db1.example.com.key | +---+-+ 7 rows in set (0.00 sec) If I create one replication user without SSL required, he is able to log in from node 2 to node 1 no problem: MariaDB [mysql] grant replication slave on *.* to 'slave1'@' db2.example.com' identified by 'secret'; Query OK, 0 rows affected (0.00 sec) [root@db2:~] #mysql -uslave1 -p -h db1.example.com Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 5.5.41-MariaDB MariaDB Server Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)] If I show grants on this user I can confirm that SSL is not required MariaDB [mysql] show grants for 'slave1'@'db2.example.com'; ++ | Grants for sla...@db2.example.com | ++ | GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'db2.example.com' IDENTIFIED BY PASSWORD '*somelongpasswordhash' | ++ 1 row in set (0.00 sec) If I create the second slave user with the SSL requirement, and even flush privileges, I can not log into node 1 from node 2 MariaDB [mysql] grant replication slave on *.* to 'slave2'@' db2.example.com' identified by 'test' require ssl; Query OK, 0 rows affected
alter table modify syntax error
Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1 I'm just wondering what I'm doing wrong here, because the syntax looks correct to me! Thanks -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
Cool guys, that did it.. ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; For some reason the book I'm following doesn't specify that you have to note the data type in moves! This helped. and thanks again. Tim On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen cars...@bitbybit.dk wrote: On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column color varchar(10) sixth' at line 1 Here's my table one more time for reference: mysql describe car_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | car_id | int(11) | NO | PRI | NULL| auto_increment | | vin| varchar(17) | YES | | NULL|| | year | decimal(4,0) | YES | | NULL|| | make | varchar(10) | YES | | NULL|| | model | varchar(20) | YES | | NULL|| | color | varchar(10) | YES | | NULL|| | price | decimal(7,2) | YES | | NULL|| ++--+--+-+-++ 7 rows in set (0.01 sec) I appreciate your suggestions so far and it would be great if I could get some help with this one too. Thanks Tim On Sat, Jun 28, 2014 at 1:34 PM, Tim Dunphy bluethu...@gmail.com wrote: Cool guys, that did it.. ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; For some reason the book I'm following doesn't specify that you have to note the data type in moves! This helped. and thanks again. Tim On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen cars...@bitbybit.dk wrote: On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in-mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Thanks Tim On Sat, Jun 28, 2014 at 7:46 PM, Jesper Wisborg Krogh my...@wisborg.dk wrote: Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 03:45 Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column color varchar(10) sixth' at line 1 The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html Best regards, Jesper Krogh MySQL Support -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Hey, that's some good input. Thanks and makes total sense. I guess the reason I thought I could use that syntax is that the book uses MySQL for all it's examples and explains that it does so because MySQL is a free and open source version of SQL that's easy to install. But maybe you're right and they do depart into other syntaxes of SQL. I just don't know where they got that 'first, second, third, etc' version of the alter table syntax from. Definitely not sweatin' this detail tho, I am totally fine with what you showed me that works. Thanks again for your input! Tim On Sat, Jun 28, 2014 at 9:14 PM, Jesper Wisborg Krogh my...@wisborg.dk wrote: Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 10:09 To: Jesper Wisborg Krogh Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in- mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Best regards, Jesper Krogh MySQL Support -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
mysql failed login attempts
hello, currently my php app is failing logins to it's mysql database. My config file is set like this: [mysqld_safe] general-log=1 general-log-file=/var/log/mysqld-general.log log-output=/var/log/mysqld-general.log log=/var/log/mysqld.log log-error=/var/log/mysqld-error.log pid-file=/var/run/mysqld/mysqld.pid Yet if I tail the /var/log/mysqld-general.log I cannot see the login attempts. This is how my environment variables are set: mysql show variables like 'log%'; +-+---+ | Variable_name | Value | +-+---+ | log | ON| | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | /var/log/mysqld-error.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries| OFF | | log_warnings| 1 | +-+---+ Can someone please tell me what I am doing wrong and how I can see failed login attempts? Tim -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: HA Scalability w MySQL + SAN + VMWare: Architecture Suggestion Wanted
Why not use a load balancer such as HA/Proxy as a way of maintaining high availability on your mysql nodes? http://blogs.reliablepenguin.com/2011/03/31/mysql-load-balancing-with-haproxy http://linuxadminzone.com/how-to-install-setup-and-config-haproxy-loadbalancer-for-content-switching/ http://agiletesting.blogspot.com/2010/10/mysql-load-balancing-with-haproxy.html I've done this a few times in the past over the years and (depending on your setup) the combination of HA/Proxy can be dead simple to setup and rock solid in terms of reliability. Regards, tim - Original Message - From: Wes Modes wmo...@ucsc.edu To: mysql@lists.mysql.com Sent: Monday, April 2, 2012 7:47:18 PM Subject: Re: HA Scalability w MySQL + SAN + VMWare: Architecture Suggestion Wanted Thanks again for sharing your knowledge. I do believe the answers I've receiving, but since I have requirements that I cannot easily alter, I'm also gently pushing my expert advisers here to look beyond their own preferences and direct experience. RE: Shared storage. I can easily let go of the preference to take advantage of shared storage. I understand duplicated databases are the essence of database redundancy. You make good points. In terms of the acceptability of a small fraction of users being temporarily unable to access services: rather than sharding, which again requires more control over the application than we have, I was more envisioning that would be the fraction of users who hit the one peer MySQL server that is temporarily unavailable due to h/w or s/w failure or DB corruption while its fail over is powered up. Does MySQL cluster seem like it will address my requirements to allow us to horizontally scale a number of MySQL nodes as peers without separating reads and writes, or slaves and masters. Wes On 4/2/2012 2:25 PM, shawn green wrote: Hello Wes, On 4/2/2012 4:05 PM, Wes Modes wrote: Thanks Shawn and Karen, for the suggestions, even given my vague requirements. To clarify some of my requirements. *Application: *We are using an open-source application called Omeka, which is a free, flexible, and open source web-publishing platform for the display of library, museum, archives, and scholarly collections and exhibitions. Without getting into how free (or scalable) free software really is, we can view it as one aspect we cannot change, having been written into the grant requirements we received for the project. Experienced Omeka developers and our own developer have suggested that/it is not feasible to separate database writes from reads in the application/ (given time and resources). That's a shame. Sounds like you are back to one big server or several smaller servers with in-program sharding. *SAN: *The SAN is a Dell EqualLogic 6100 which has redundant everything, including multiple NICs, controllers, and power. So we are less concerned about the SAN being a SPoF. On the other hand, if we have a single big MySQL server that fails, we could bring up another copy of it via VMWare, but until the server came up, the application would be dead in the water. If the database is corrupted, service will be interrupted for a considerable time. Again, each MySQL instance needs it's own copy of the data. Having only one big powerful disk system means that each instance you fire up must both share spindles and networking to access its data. Just like a freeway at rush hour, you may find the traffic into and out of this one device crawling to a halt exactly when you don't want it to. *High Availability:* It sounds like there is some debate over how to provide HA best, but do people really disagree on the desired results? Without getting into the many meanings of this buzz word, here's what we mean: /We desire to maintain high availability of service, allowing a small fraction of users to experience outage for only seconds at a time. We desire to provide this through horizontal scaling, redundancy, failover planning, and external monitoring. / Small fraction of users - this implies data sharding. Multiple MySQL instances each with enough data to operate independently for one slice of your most important data and an application smart enough to know which shard to go to for each slice of data. For a few seconds at a time - you do not want a shared disk. Should the active MySQL die, it's data will be in an inconsistent state. Once you fire up the passive daemon it will need to perform a recovery restart. This down time is more than likely not going to take only a few seconds. The more data you have, the longer the checks will take. An independent copy maintained by a slave instance, provides a logically consistent copy of the master's data as it will only replicate complete transactions. horizontal scaling - one master, multiple slaves. This requires the separation of writes and reads. *Scalability: *Again, seems like there are lots of applications and
Re: delete all hosts using a wildcard
Hello again list, Thanks for pointing out where I was making my mistake. I just needed to select the right field. And this is just a test environment so getting rid of those users won't have any meaningful impact. Also previewing what you will be deleting by using a select is great advice I intend to use. Best tim - Original Message - From: Paul DuBois paul.dub...@oracle.com To: Tim Dunphy bluethu...@jokefire.com Cc: mysql@lists.mysql.com Sent: Saturday, January 14, 2012 6:46:38 PM Subject: Re: delete all hosts using a wildcard On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: hello list, I have a number of hosts that I would like to delete using a wildcard (%) symbol. Here is the query I am using: mysql delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compare your pattern to the host column, not user. * To match the pattern, use LIKE, not =. So: WHERE host LIKE '%.summitnjhome.com' But to see what rows your DELETE will affect, try this first: SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; Something else to consider: What if these accounts have privileges defined in the other grant tables, such as database-level privileges in the db table? http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html Query OK, 0 rows affected (0.00 sec) And I am attempting to delete all the hosts at the domain 'summitnjhome.com'... But as you can see I am unsuccessful: mysql select user,host from mysql.user; +--+-+ | user | host| +--+-+ | root | 127.0.0.1 | | repl | virtcent10.summitnjhome.com | | admin| virtcent11.summitnjhome.com | | repl | virtcent19.summitnjhome.com | | repl | virtcent23.summitnjhome.com | | repl | virtcent30.summitnjhome.com | +--+-+ I know I can delete them individually and this is what I am going to do. But I would like to use this as a learning opportunity to help me understand how the wildcard works. Thanks in advance.. Best regards, Tim -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
delete syntax
hello list, I am attempting to delete a user from the mysql.user table without success. mysql delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
Hello Krishna, Thanks but I probably should have noted that I only want to delete the wildcard user. There are other users I would prefer to not delete. mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | | mail_admin | 127.0.0.1 | | mail_admin | localhost | | mail_admin | localhost.localdomain | ++---+ 4 rows in set (0.00 sec) sorry for not including enough information last time. best tim - Original Message - From: Krishna Chandra Prajapati prajapat...@gmail.com To: Tim Dunphy bluethu...@jokefire.com Cc: mysql@lists.mysql.com Sent: Thursday, December 1, 2011 9:03:46 PM Subject: Re: delete syntax delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy bluethu...@jokefire.com wrote: hello list, I am attempting to delete a user from the mysql.user table without success. mysql delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
