Re: Session ID Generation
Mysql assigns its session IDs sequentially as they come in. I suspect, however, that you're looking for session IDs as used by websites -generation of those is entirely not a mysql issue, it is only a potential store for them. Steven Siebert smsi...@gmail.com wrote: Hello all, I've looked though, what I believe to be, the relevant areas in the MySQL docs as well as standard search engine searches without luck. I was hoping to find some documentation that would tell me: - how MySQL session Ids are generated (specifically, are they considered random) - does MySQL require session ids sent from the client to be server generated (ie the client can't make one up and that is used for the session) - is there any other relevant security protections or concerns for mysql session management that would be of interest? Thanks, Steve -- Sent from Kaiten Mail. Please excuse my brevity.
Re: help: innodb database cannot recover
boah you *must not* remove ibdata1 it contains the global tablespace even with file_per_table ib_logfile0 and ib_logfile1 may be removed, but make sure you have a as cinsistent as possible backup of the whole datadir I removed ib_logfile0 and ib_logfile1 and restarted mysql with innodb_force_recovery=1, mysql keeps crashing and restart: thd: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = (nil) thread_stack 0x3 /usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84bbbae] /usr/libexec/mysqld(handle_segfault+0x4bc) [0x81eca1c] [0xf57fe400] [0xf57fe416] /lib/libc.so.6(gsignal+0x51) [0x45a7bb71] /lib/libc.so.6(abort+0x17a) [0x45a7d44a] /usr/libexec/mysqld(fil_io+0x377) [0x83ba177] /usr/libexec/mysqld() [0x83a257b] /usr/libexec/mysqld(buf_read_page+0x282) [0x83a3132] /usr/libexec/mysqld(buf_page_get_gen+0x351) [0x839c111] /usr/libexec/mysqld(btr_cur_search_to_nth_level+0x3c1) [0x838ca31] /usr/libexec/mysqld(row_search_index_entry+0x79) [0x840d3c9] /usr/libexec/mysqld() [0x840bf97] /usr/libexec/mysqld(row_purge_step+0x574) [0x840d1e4] /usr/libexec/mysqld(que_run_threads+0x535) [0x83fa815] /usr/libexec/mysqld(trx_purge+0x365) [0x8427e25] /usr/libexec/mysqld(srv_master_thread+0x75b) [0x842009b] /lib/libpthread.so.0() [0x45bf09e9] /lib/libc.so.6(clone+0x5e) [0x45b2dc2e] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 130620 00:47:21 mysqld_safe Number of processes running now: 0 130620 00:47:21 mysqld_safe mysqld restarted InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. 130620 0:47:22 [ERROR] Plugin 'InnoDB' init function returned error. 130620 0:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. if I set innodb_force_recovery=4 to restart mysql and then run mysqldump, i got the following error: mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES it looks that all data from innodb is messed up and gone forever even though *.frm is still there. Peter
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Hi Frank, On 20/06/2013 05:00, Franck Dernoncourt wrote: Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql-ib203`: It's a bit of a workaround, but you should be able to get rid of the file using the steps below. I'm using an example where I killed mysqld while it was dropping the to_date column from the salaries table in the employees sample database: mysql SHOW CREATE TABLE salaries\G *** 1. row *** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql ALTER TABLE salaries DROP COLUMN to_date; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql$ ls -1 employees/#* employees/#sql-36ab_2.frm employees/#sql-ib30.ibd 1. Create a temporary table with the same structure as the salaries table would have looked after the ALTER that failed: mysql CREATE TABLE tmp LIKE salaries; ALTER TABLE tmp DROP COLUMN to_date; 2. Shutdown MySQL. 3. Copy the .frm file from the tmp table to have the same name as the #sql-*.ibd file: mysql$ cp employees/tmp.frm employees/#sql-ib30.frm 4. Start MySQL again. 5. Drop the #sql-ib30.frm table: mysql DROP TABLE `#mysql50##sql-ib30`; Query OK, 0 rows affected (0.01 sec) 6. Do the same for the #sql*.frm file (it'll get removed even though you get an error): mysql DROP TABLE `#mysql50##sql-36ab_2`; ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2' I know it's not very elegant, but should work. The #mysql50# prefix tells MySQL to not encode the table name when mapping to the file system (https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html). Best regards, Jesper Krogh MySQL Support
Re: help: innodb database cannot recover
As a matter of dumb questions, what versions are the old and new mysqld; and are they running on the same platform (OS, 32/64 bit, ...) ? - Original Message - From: Peter one2001...@yahoo.com To: Reindl Harald h.rei...@thelounge.net, mysql@lists.mysql.com Sent: Friday, 21 June, 2013 10:04:27 AM Subject: Re: help: innodb database cannot recover I removed ib_logfile0 and ib_logfile1 and restarted mysql with innodb_force_recovery=1, mysql keeps crashing and restart: thd: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = (nil) thread_stack 0x3 /usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84bbbae] /usr/libexec/mysqld(handle_segfault+0x4bc) [0x81eca1c] [0xf57fe400] [0xf57fe416] /lib/libc.so.6(gsignal+0x51) [0x45a7bb71] /lib/libc.so.6(abort+0x17a) [0x45a7d44a] /usr/libexec/mysqld(fil_io+0x377) [0x83ba177] /usr/libexec/mysqld() [0x83a257b] /usr/libexec/mysqld(buf_read_page+0x282) [0x83a3132] /usr/libexec/mysqld(buf_page_get_gen+0x351) [0x839c111] /usr/libexec/mysqld(btr_cur_search_to_nth_level+0x3c1) [0x838ca31] /usr/libexec/mysqld(row_search_index_entry+0x79) [0x840d3c9] /usr/libexec/mysqld() [0x840bf97] /usr/libexec/mysqld(row_purge_step+0x574) [0x840d1e4] /usr/libexec/mysqld(que_run_threads+0x535) [0x83fa815] /usr/libexec/mysqld(trx_purge+0x365) [0x8427e25] /usr/libexec/mysqld(srv_master_thread+0x75b) [0x842009b] /lib/libpthread.so.0() [0x45bf09e9] /lib/libc.so.6(clone+0x5e) [0x45b2dc2e] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 130620 00:47:21 mysqld_safe Number of processes running now: 0 130620 00:47:21 mysqld_safe mysqld restarted InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. 130620 0:47:22 [ERROR] Plugin 'InnoDB' init function returned error. 130620 0:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. if I set innodb_force_recovery=4 to restart mysql and then run mysqldump, i got the following error: mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES it looks that all data from innodb is messed up and gone forever even though *.frm is still there. Peter -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Session ID Generation
- Original Message - From: Steven Siebert smsi...@gmail.com Subject: Re: Session ID Generation I am indeed looking for MySQL session ID's, not an HTTP session ID. I'm doing a defense in depth audit and reviewing potential threats to each remote connection - in this case session fixation. I know I can set various session timeout properties that help mitigate fixation and hijacking, but a randomly generated server-only generated session id goes a log way to mitigate the risk. Just a note, we are following industry best practices utilizing a DMZ...but out biggest threat is an insider, so we need to realize any potential risk. You stated these IDs are sequential...do you know if there is any way to modify this to utilize a random generation? Sequential session IDs are an avenue to session hijacking. I have to admit that's way out of my depth. My response merely concerned the session ID that is shown to the administrators, and those are just an incremental counter. I have no idea how sessions are handled internally. You might be better off on the developer mailing list for those kind of questions, I think. -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: Session ID Generation
On 21.06.2013 12:48, Steven Siebert wrote: You stated these IDs are sequential...do you know if there is any way to modify this to utilize a random generation? Sequential session IDs are an avenue to session hijacking. as a MySQL client session is bound to a specific TCP connection ... how would being able to predict a session ID help with hijacking that TCP session? Even more so as the session ID is not really part of the communication protocol between client and server at all and more like an identifier for SHOW PROCESSLIST (that would most likely be visible to an internal attacker anyway) and KILL (which requires SUPER privileges on the database anyway, and at that point you've already lost to an attacker ...) -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Session ID Generation
On 21.06.2013 13:35, Steven Siebert wrote: Hartmut - if the session Id is not a meaningful part of the client/server protocol, is the session managed my the transport layer rather than the app layer? If the TCP connection is lost...is the effectively session over and can not be re-established on another socket? yes, the lifetime of a connection is bound to the lifetime of the underlying transport session. Also even if you could hijack an established TCP or Unix Domain Socket connection you'd still need to figure out how to use it withough bringing the protocol flow out of sync. -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Session ID Generation
Am 21.06.2013 12:48, schrieb Steven Siebert: You stated these IDs are sequential...do you know if there is any way to modify this to utilize a random generation? Sequential session IDs are an avenue to session hijacking. There is no attack vector opening up by knowing a session ID. A session is tied to a socket which in turn would be a TCP/IP network connection. As long as TCP/IP connection hijacking is considered unfeasible, so will the corresponding session. If connection hijacking is a concern in your environment, consider using SSL/TLS as an additional measure against a number of attack - including eavesdropping and data manipulation. http://www.yassl.com/files/yassl_securing_mysql.pdf Denis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Session ID Generation
Hartmut/Denis - Great information, thank you! I was unaware that mysql bound the session id to the socket in such a way that it would not permit that session id to be provided on other socket. This was the missing piece. Hartmut - if the session Id is not a meaningful part of the client/server protocol, is the session managed my the transport layer rather than the app layer? If the TCP connection is lost...is the effectively session over and can not be re-established on another socket? In a mysql client sense, I would need to re-establish a connection and set my session variables again rather than just reconnect using the session ID from the dropped connection? I apologize about these basic mysql-mechanics questions - I need to satisfy our auditors, so I need to understand =) Thanks, S On Fri, Jun 21, 2013 at 7:13 AM, Hartmut Holzgraefe hart...@skysql.comwrote: On 21.06.2013 12:48, Steven Siebert wrote: You stated these IDs are sequential...do you know if there is any way to modify this to utilize a random generation? Sequential session IDs are an avenue to session hijacking. as a MySQL client session is bound to a specific TCP connection ... how would being able to predict a session ID help with hijacking that TCP session? Even more so as the session ID is not really part of the communication protocol between client and server at all and more like an identifier for SHOW PROCESSLIST (that would most likely be visible to an internal attacker anyway) and KILL (which requires SUPER privileges on the database anyway, and at that point you've already lost to an attacker ...) -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Session ID Generation
Steven, Am 21.06.2013 13:35, schrieb Steven Siebert: If the TCP connection is lost...is the effectively session over and can not be re-established on another socket? Yes. In a mysql client sense, I would need to re-establish a connection and set my session variables again rather than just reconnect using the session ID from the dropped connection? Yes. There is no way for a client to specify a desired session ID. The session ID is only used once - the server notifies the client of the ID used in the initial handshake upon connection establishment, even before authentication is attempted. Take a look at the docs for protocol details: http://dev.mysql.com/doc/internals/en/connection-phase.html#plain-handshake I apologize about these basic mysql-mechanics questions - I need to satisfy our auditors, so I need to understand =) The auditors should know their trade and not simply try pressing requirements they've read about in an IT manager magazine. Denis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
On 21.06.2013 13:59, Rafał Radecki wrote: Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ has a pretty good description of the SHOW ENGINE INNODB STATUS output, even though it is not too detailed in this specific section. -- Hartmut Holzgraefe hart...@skysql.com Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? I am wondering if my innodb_buffer_pool setting is not to low. Does 'file reads' show number of times innodb files have been read into memory from server's start? What about file writes/fsyncs? Best regards, Rafal Radecki.
Re: Session ID Generation
Great, thanks to all. I don't mean to defend our auditors, because they are a PITA, but they do appear to be decently knowledgeable in general - but they aren't, not can they be expected to, be specific application-level experts - otherwise, the number of auditors we would be required to hire would be cost prohibitive...there is a necessary balance =) Just because MySQL implements this way (and, obviously is concious of these security concerns), doesn't mean the latest NoSQL solution deployed to github, written in python during a cocaine fuelled weekend, does...they aren't here to say no to whatever software I desire to use, they just need to verify. So, really, the wand of ignorance should be pointed in my direction =) This leads me to my final question: is this documented anywhere beyond the source code and this thread? I was specifically searching for session id generation, but clearly this search was too narrow. I'll look more generally for how MySQL establishes connections and maintains sessions - but if you happen to know where it might be document off the top of your head, I would appreciate it. Thanks again for everyone's insightful and quite helpful responses. S On Fri, Jun 21, 2013 at 7:58 AM, Denis Jedig d...@syneticon.net wrote: Steven, Am 21.06.2013 13:35, schrieb Steven Siebert: If the TCP connection is lost...is the effectively session over and can not be re-established on another socket? Yes. In a mysql client sense, I would need to re-establish a connection and set my session variables again rather than just reconnect using the session ID from the dropped connection? Yes. There is no way for a client to specify a desired session ID. The session ID is only used once - the server notifies the client of the ID used in the initial handshake upon connection establishment, even before authentication is attempted. Take a look at the docs for protocol details: http://dev.mysql.com/doc/**internals/en/connection-phase.** html#plain-handshakehttp://dev.mysql.com/doc/internals/en/connection-phase.html#plain-handshake I apologize about these basic mysql-mechanics questions - I need to satisfy our auditors, so I need to understand =) The auditors should know their trade and not simply try pressing requirements they've read about in an IT manager magazine. Denis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Session ID Generation
Tanks for responding Johan. I am indeed looking for MySQL session ID's, not an HTTP session ID. I'm doing a defense in depth audit and reviewing potential threats to each remote connection - in this case session fixation. I know I can set various session timeout properties that help mitigate fixation and hijacking, but a randomly generated server-only generated session id goes a log way to mitigate the risk. Just a note, we are following industry best practices utilizing a DMZ...but out biggest threat is an insider, so we need to realize any potential risk. You stated these IDs are sequential...do you know if there is any way to modify this to utilize a random generation? Sequential session IDs are an avenue to session hijacking. Thanks, S On Fri, Jun 21, 2013 at 2:40 AM, Johan De Meersman vegiv...@tuxera.bewrote: Mysql assigns its session IDs sequentially as they come in. I suspect, however, that you're looking for session IDs as used by websites -generation of those is entirely not a mysql issue, it is only a potential store for them. Steven Siebert smsi...@gmail.com wrote: Hello all, I've looked though, what I believe to be, the relevant areas in the MySQL docs as well as standard search engine searches without luck. I was hoping to find some documentation that would tell me: - how MySQL session Ids are generated (specifically, are they considered random) - does MySQL require session ids sent from the client to be server generated (ie the client can't make one up and that is used for the session) - is there any other relevant security protections or concerns for mysql session management that would be of interest? Thanks, Steve -- Sent from Kaiten Mail. Please excuse my brevity.
