Re: mysqldump and the dreaded lost root password dilemma
Thanks Brent, your solution is the one that worked for me. In 4.0.20 there was no 'Super_priv' column however. ? On Fri, 17 Sep 2004 15:20:43 -0400, Brent Baisley [EMAIL PROTECTED] wrote: There probably is a root user, but it's not called root. You can name the root user whatever you want. You probably just don't have a user named root, which is why you can change the password for user root. You want to start MySQL with the skip grant tables options, just like in the documentation. But instead of reseting the password, you want to launch the mysql client, which you should be able to do without providing a user name or password since there are no grant tables loaded. Once in mysql, switch to the mysql database and do a select on the user table to see what user name are set. There is a column called Super_priv, any record with a Y in that column is probably a valid root user. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and the dreaded lost root password dilemma
Hi list, First question - I have a machine that was being managed by Plesk, and an update to mod_python left Plesk in a nonrunning state (actually causes apache to segfault). So I am attempting to manually manage MySQL (the way it should be done!) - but there is apparently no root user in MySQL's tables! I assume this is a Plesk thing, but have no patience for it. So I'm doing the tried-and-true method listed here: http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html And it fails. There is no root user... So how can I get MySQL back under my control? Second question - the database is 4.0.20 (RPM provided by MySQL). When I run: mysqldump --opt -h localhost -u mylogin -p mydb database.sql I get an export file, and when I try to execute that SQL on another 3.23.58 machine, I get SQL syntax errors. This database is not using any 4.x stuff, it is just standard MyISAM tables. Is there a trick to getting exports from a 4.x database to work on 3.23.x systems? -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure Database Systems
Rory McKinley wrote: Hi Sarah This is more of a PHP question than a MySQL question as to my mind while it is all possible, the bulk of the work would need to be done on the PHP side. Assuming that you don't have the time to write all the necessary code from scratch, you might want to look for a content-management system (CMS) written in PHP and using MySQL that has some (ideally, all) the functionality that you require. This can serve as a good foundation and can be tweaked to your relevant requriements. A good place to start looking for this would be Sourceforge or google. Take a look at http://opensourcecms.com - there you can actually demo the CMS before you download it. You can definitely work on an existing CMS and patch it to work with encrypted data storage. I recommend Mambo, as most of your SQL modifications are localized in one handy file (DISCLAIMER: I am a Mambo core developer). Maybe there are others that also take this approach. I also agree with Shawn, that your best bet is to use a single server, ideally hosted by a hosting company that provides MySQL/PHP. This removes the need for replication to multiple machines, and you can just keep a regular backup copy on a local machine - also reduces some of your security concerns. This has been discussed over at the NYPHP lists, and one of the ideas that I came up with was to use the user's password as the key to encrypt/decrypt all of their data in the database. In the event that Joe could actually view Sally's data, he wouldn't have her key to decrypt it so it would have little use. Ditto for h4x0rd00d, as he would have to brute force the key for every row in the database... My favourite part of this design is that the 'key' is actually stored as an MD5 hash in the database as the user's password; and the only time the server sees it in the clear is when the user logs in. This can be added to the session (stored on the server, not in a cookie) so that it is resistant to tampering. I'm considering this topic for a future article, if you are interested please contact me off-list ;) -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More semaphore mystery
Hi gang, Back in the throes of semaphores that refuse to play nice with all the other kids on the playground ;-) I'm getting the following in my error file: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 79461482, signal count 75199550 --Thread 23744585 has waited at btr0cur.c line 401 for 556.00 seconds the semaph ore: X-lock on RW-latch at 0x868f29b0 created in file buf0buf.c line 438 a writer (thread id 23744585) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0cur.c line 401 Last time write locked in file btr0cur.c line 401 ...So what's a guy to do? Is there a way I can determine just what exactly is causing this? -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and long semaphore waits
Heikki Tuuri wrote: Mitch, please send the FULL .err log to me. Hey Heikki, Not sure you want that, the file is 44MB uncompressed, and only talks about the errors reading communication packets. Makes for some really boring reading ;) The InnoDB error I managed to figure out - I once upped max_connections without doing the math, and the machine was exhausting physical RAM. So I backed off max_connections and the really ugly errors are now gone :) But I still do not have a solution to my error logs filling up with the connection error, which is now unfortunately every 1 to 2 seconds... The machines are connected with gigabit ethernet, and have been running without trouble for at least three months - and now are filling up the error logs. Any ideas, anybody? Bueller? -- Mitch Best regards, Heikki - Original Message - From: Mitch Pirtle [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, July 03, 2004 4:41 PM Subject: InnoDB and long semaphore waits Hi listers, I just got here, so please let me know if this is not the appropriate list! :) Running MySQL 4.0.20 on Fedora Core 1, with InnoDB tables. Installed from RPMs provided at MySQL.com. Last night the beastie came down hard, and requred a physical reboot in order to free/kill some mysqld processes. I say this as I worry that it could be from a hardware error, however it is running on a dual Xeon machine that is not even 6 months old... When I attempted to get the stack trace I only get nm: /usr/sbin/mysqld: no symbols, and the docs point at stuff that is not on this box. ? I see two errors that need resolution: -- --- 1) In the error log, I see the following: InnoDB: ## Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23207961 has waited at btr0sea.c line 480 for 625.00 seconds the semaphore: X-lock on RW-latch at 0x4506a768 created in file btr0sea.c line 139 a writer (thread id 23207961) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 745 Last time write locked in file btr0sea.c line 480 InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 040702 20:45:27InnoDB: Assertion failure in thread 24583 in file sync0arr.c line 925 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. See section 6.1 of InnoDB: http://www.innodb.com/ibman.php about forcing recovery. mysqld got signal 11; ...and goes on to say: key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=176 max_connections=500 threads_connected=146 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2439212 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) 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... Cannot determine thread, fp=0xbfedf758, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80720d4 0x8250d48 0x81ed044 0x80f9148 0x824e4fc 0x828452a New value of fp=(nil) failed sanity check, terminating stack trace! Again, I try to follow the instructons on dealing with the stack trace but the instructions fail, and also do not provide enough explanation for me to figure out on my own how to fix it :( -- --- 2) After rebooting the server, the error log fills up with: 040703 9:07:59 Aborted connection 55 to db: 'db1' user: 'www' host: `192.168.1.1' (Got an error reading communication packets) ...and it repeats itself roughly every 5-to-10 seconds, which I obviously find alarming. Cannot find any reference on that error, and don't really have enough data to know how to react... Can somebody please whack me upside the head with a cluestick? -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and long semaphore waits
Heikki Tuuri wrote: Not sure you want that, the file is 44MB uncompressed, and only talks about the errors reading communication packets. Makes for some really boring reading ;) The InnoDB error I managed to figure out - I once upped max_connections without doing the math, and the machine was exhausting physical RAM. So I backed off max_connections and the really ugly errors are now gone :) ok, good. It was then a hang caused by excessive OS swapping or something like that. Definitely a 'user error' and not caused by InnoDB or MySQL. But I still do not have a solution to my error logs filling up with the connection error, which is now unfortunately every 1 to 2 seconds... The machines are connected with gigabit ethernet, and have been running without trouble for at least three months - and now are filling up the error logs. Any ideas, anybody? Bueller? I think those ugly warnings will again be removed in the next 4.0 release. They were instroduced to 4.0.xx accidentally. Thanks Heikki, as long as I know it is a known error I am happy to wait (and truncate my error logs)... :^) -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB and long semaphore waits
Hi listers, I just got here, so please let me know if this is not the appropriate list! :) Running MySQL 4.0.20 on Fedora Core 1, with InnoDB tables. Installed from RPMs provided at MySQL.com. Last night the beastie came down hard, and requred a physical reboot in order to free/kill some mysqld processes. I say this as I worry that it could be from a hardware error, however it is running on a dual Xeon machine that is not even 6 months old... When I attempted to get the stack trace I only get nm: /usr/sbin/mysqld: no symbols, and the docs point at stuff that is not on this box. ? I see two errors that need resolution: - 1) In the error log, I see the following: InnoDB: ## Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 23207961 has waited at btr0sea.c line 480 for 625.00 seconds the semaphore: X-lock on RW-latch at 0x4506a768 created in file btr0sea.c line 139 a writer (thread id 23207961) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 745 Last time write locked in file btr0sea.c line 480 InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 040702 20:45:27InnoDB: Assertion failure in thread 24583 in file sync0arr.c line 925 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. See section 6.1 of InnoDB: http://www.innodb.com/ibman.php about forcing recovery. mysqld got signal 11; ...and goes on to say: key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=176 max_connections=500 threads_connected=146 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2439212 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) 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... Cannot determine thread, fp=0xbfedf758, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80720d4 0x8250d48 0x81ed044 0x80f9148 0x824e4fc 0x828452a New value of fp=(nil) failed sanity check, terminating stack trace! Again, I try to follow the instructons on dealing with the stack trace but the instructions fail, and also do not provide enough explanation for me to figure out on my own how to fix it :( - 2) After rebooting the server, the error log fills up with: 040703 9:07:59 Aborted connection 55 to db: 'db1' user: 'www' host: `192.168.1.1' (Got an error reading communication packets) ...and it repeats itself roughly every 5-to-10 seconds, which I obviously find alarming. Cannot find any reference on that error, and don't really have enough data to know how to react... Can somebody please whack me upside the head with a cluestick? -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
