Re: Server-Crash - What to do?
Karel W. Dingeldey wrote: Ok, I will do it more clearly. The server has two harddisks, one for the system, one for the data. The system harddisk breaked, hardware failure. It seems that this happed while mysqld was writing some data, so that some tables are corrupted. Most tables are reconstructable, only the one where I have only the MYD file is very important. On filesystem level I made all rescue trials. My problem is, that only the MYD file is still available. I tried reconstructing it with "myisamchk -r -o table_name.MYD", but as I said without the wanted result. My question is, if there is any solution to get a working table from this MYD file. Because the data is saved in this file, IMHO it should. Am I right? If you're using MyISAM tables, then you should have an .MYD (data), .MYI (index) and .frm (table definition?) file for every table. If you don't have a backup copy (or two) of your MYD file by now, make one. Then try repeating the CREATE TABLE statement that defines the table. If you can get a CREATE TABLE that exactly matches the table definition previously used, the data in the MYD file may be accessible again. You may have to move the MYD file out of the way while you do the CREATE TABLE. How you get your CREATE TABLE statement is up to you. Have you got a mysqldump created since the last time the table definion was changed? Was it automatically created by some program which uses the database? If you can't get anywhere with that and the data is valuable, you should consider paid support. --Ludwig Pummer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble with read-only
Rod Heyd wrote: Hello, I'm trying to set up a replication slave as read-only, but the read-only part doesn't seem to be working. Replication between the master and the slave works fine. I have added the read-only keyword to the slave's my.cnf, and looking at the output of show variables indicates that read-only is set to 'ON' So as far as I can tell, it I should not be able to connect to the slave and insert rows into my tables. However, I AM able to insert rows into the tables. Have I missed some additional configuration option? I'm running 5.0.21 on Solaris... Thanks! Are you logging in as root on the slave when you try to insert rows? read-only is ignored for users with root or replication privileges. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql for freebsd 6.0
kalin mintchev wrote: hi all... i can't see the mysql 5 version for freebsd 6.0 on the mysql developer site? am i blind or it's on purpose?!?! curious... and actually need it... thanks... /usr/ports/databases/mysql50-server/ /usr/ports/databases/mysql51-server/ I suggest you familiarize yourself with the search feature at http://www.freebsd.org/ports/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is this safe against sql-injection?
Jochen Kaechelin wrote: Can somebody give me some general hints how to prevent sql-injection? I always go this way to build my queries: function clean_mysql_string($string) { $clean_string = stripslashes($string); $clean_string = htmlentities(strip_tags(($clean_string))); $clean_string = trim($clean_string); $clean_string = rtrim($clean_string); $clean_string = mysql_real_escape_string($clean_string); return($clean_string); } $searchstring = clean_mysql_string($_POST["searchstring"]); $query = " SELECT id,uname,nickname, MATCH(uname,nickname) AGAINST('$searchstring' IN BOOLEAN MODE) AS mtch FROM wlh_accounts HAVING mtch > 0.001 ORDER BY mtch DESC"; $results = mysql_query($query); while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) { $values[] = array ( "id" => $row["id"], "uname" => $row["uname"], "nickname"=> $row["nickname"], "mtch"=> $row["mtch"] ); } Is this safe?? AFAIK, all you really need to prevent SQL injection is to use mysql_real_escape_string and enclose the variable in single-quotes when you construct the query. Stripslashes is a good idea if magic quotes are enabled in PHP. htmlentities, strip_tags, trim and rtrim are not necessary for preventing SQL injection (and the rtrim is redundant). Look at Example 3 on http://www.php.net/mysql_real_escape_string (but pay attention to the user comments regarding the is_numeric check). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_real_query.... probably asked a lot
Eric Smith wrote: OK, I'll bet you guys get a lot of this one, but I can't seem to find the answer in the archives. I have binary data that I want to store in a longblob. This is just byte data null bytes are possible. So, I use mysql_real_query. How do I format the char* query string? Here's the way my format looks: sprintf(queryString,"update images set imageData=%p where imageID=\'%s\'",imageData,[imageID cString]); and then I do the query: result = mysql_real_query(theConnection,queryString,strlen(theDBData)+nBytes); where strlen(theDBData)+nBytes gives the total byte count for queryString. Well, I get an error message saying that I have an error in my syntax. How do I format this properly? Thanks, Eric The documentation for mysql_real_query does explain that it can handle null bytes, but what if your binary data contains single-quote? You get a syntax error. I see two options for you here: 1) use mysql_real_escape_string() on the binary data before you build it into the final query string 2) use the prepared statement API If you go with option 1, you'll need to allocate another buffer twice the size of imageData to hold the escaped version. If you go with option 2, you can use the imageData buffer directly, but you'll have to use the prepared statement functions instead of mysql_real_query(). I've never used prepared statements in the C API so I'm just going on what the documentation says. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help...I am desparate
Logg, Connie A. wrote: I was asked (told) by my security people to use a port < 1024. I am running with 1000 other places, and was running with 1000 on both of these machines. -Original Message- From: Jeremy Cole [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 4:25 PM To: Logg, Connie A. Cc: mysql@lists.mysql.com Subject: Re: Help...I am desparate Hi Connie, 060103 15:54:02 [ERROR] Can't start server: Bind on TCP/IP port: Permission denied 060103 15:54:02 [ERROR] Do you already have another mysqld server running on port: 1000 ? You can't bind to a port less than 1024 unless you're running as root. I suspect that's the problem here. Try another port, higher than 1024. I'm kind of curious why you aren't running it on the standard 3306? Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 This might work... 1. Follow the steps in http://dev.mysql.com/doc/refman/4.1/en/changing-mysql-user.html 2. Run the mysql init script as root. However, if MySQL drops privileges before binding to its sockets, then it won't work. I'm afraid I don't know that much about MySQL's internals. --Ludwig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quotation marks in string causing repace to not work.
Gregory Machin wrote: I tried the following UPDATE temp SET 'file_content' = REPLACE(file_content, '' , ''); but it didn't work, i think thing problem is that the string i need to replace / null has quotation marks .. how can i work around this You need to escape the quotation marks. Also, did you mean to write 'file_content', or `file_content` ? Try UPDATE temp SET `file_content` = REPLACE(file_content, 'require($_SERVER[\'DOCUMENT_ROOT\']."/scripts/define_access.php");?>', ''); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing php pages with sql queries in a mysql database
Gregory Machin wrote: Hi all. I'm writing a php script to store the contents of html and php pages in a data base, it works well until there are mysql queries in the pages source then give errors such as this one. Query failed: You have an error in your SQL syntax near 'temp' how do stop mysql from trying to interperate this data and blindly store it ?? Many Thanks You need to escape the string before you sent it to MySQL to be stored. The link below is for the C API function; whichever language API you're using has something equivalent. http://dev.mysql.com/doc/mysql/en/mysql-real-escape-string.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to select MySQL record
This is not really a MySQL question so much as it is a PHP/HTML question, but it's a quick answer. Replace echo "".$rows['lastname'] With echo "".$rows['lastname'] On the next page, $_REQUEST['person'] contains the selected chairid. Martin Toombs wrote: Sorry if this is simple, but I'm a fairly new user and it's got me stumped. Using PHP and MySql; I want to look up a specific record from a table of people by using a select query to populate a PHP statement. I can do it with one field, but I need to do it with more than one field since I have people with the same last name. This populates my option box quite well: $sql = "SELECT * FROM chairs order by lastname"; $result = mysql_query($sql); echo "Chairman's Name: "; while ($rows = mysql_fetch_array($result)){ echo "".$rows['lastname'].", ".$rows['firstname']." ".$rows['chairid'].""; }//end while echo ""; ?> It gives me a "Smith, John 42" to select, with 42 being the chairid (index field) I need to know how to use this to pick a specific record up for editing. I appreciate any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to match on something that is not there
Or without a subquery, SELECT s.questionid, s.userid FROM SurveyAnswers s LEFT JOIN Users u ON u.id = s.userid WHERE u.id IS NULL; Brian wrote: select s.userid from surveyanswers s where s.userid not in (select distinct u.id from users u) On Tue, 19 Oct 2004 16:30:29 -0600, Jonathan Duncan <[EMAIL PROTECTED]> wrote: I have a user who is using the following query to try and delete rows from one table based on the lack of a user id in another table: SELECT s.questionid, s.userid, s.questionanswer FROM Users u, SurveyAnswers s WHERE u.id != s.userid The corresponding user rows have already be deleted from the table Users. Thus, this of course seems to match on just about everything since there is no actualy record in the Users table to match on. Is there some way to match on a lack of information? Thanks, Jonathan Duncan -- 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: Mysql, php and unsubscribe or delete
My guess is that since your id field is not auto_increment and you're not specifying a value, all email addresses are getting an id of 0. Since your primary key is composed of both id and email, MySQL isn't complaining. [EMAIL PROTECTED] wrote: However, Problem application is giving me is in case I unsubscribe one email it deletes all emails from database. table. I have pasted php code and mysql dump. Any guidance, please. //add record $sql = "insert into kemails values('', '$_POST[email]')"; //unsubscribe the address $id = mysql_result($check_result, 0, "id"); $sql = "delete from kemails where id = '$id'"; $result = mysql_query($sql,$conn) or die(mysql_error()); $display_block = "You're unsubscribed!"; mysql> describe kemails -> ; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | | PRI | 0 | | | email | varchar(150) | | PRI | | | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL query problem in conjunction with PHP
Thomas Nyman wrote: I found an instruction on the net concerning "Date Arithmetic" and which seems to be exactly what I need to hel me get going..however there seems to be either a mistake on my behalf or on the writers behalf. Can anyone say whats wrong with this query $query2="UPDATE KK_Fatalie SET datum = DATE_ADD(date,INTERVAL intervall' ''.$typeArray[$row->type].")WHERE kk_ID="".$row->kk_ID""; The query is suppose to result in the following; do an UPDATE on table KK_Fatalie and set the column datum equal to todays date increased with the interval stated in the column intervall where the kolumn kk_ID equals the kk_ID in my earlier php query. You appear to have some syntax errors in your PHP (too many quote marks, and they're mismatched in some places). You also don't tell us the exact MySQL error message, and it's not obvious based on your $query2 line what all problems are without more knowledge about your table structure. Try printing out your $query2 string to the web page where you're running this query. Then enter the query using the interactive mysql program and seeing what error message MySQL produces. You could also edit your mysql_query() call to do something like: $rid2 = mysql_query($query2); if(! $rid2) echo "Error in query! Error: ".mysql_errno().": ".htmlspecialchars(mysql_error())."\n"; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Plz help quick - mysql/php/web server undefined function all of a sudden
Chip Wiegand wrote: John, Yep, looking at phpinfo.php shows no support for mysql. This is very strange. I know these things don't just happen by themselves. I also know there are only two people with the password to the server, myself and my boss (and he knows nothing about the server to begin with). Anyway, looks like php needs to be configured to work with mysql. Now that it is the way it is, how do I go about that? According to pkg_info the version of php is 4.3.4_3. Being a port install how do I configure it to use mysql? Thanks, Chip If you install it using the ports collection and not using any sort of automation tool like portupgrade, it should have a text-mode configurator which will give you the option of MySQL support if you enable the checkbox. You may need to 'make clean' in the port directory before running 'make' again to force it to give you the menu. It may fail to work if MySQL or Apache were not installed from the ports collection to start with, since the PHP port makes some assumptions about the paths to Apache and MySQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Similar simple query slow down dramatically, by just select one more field, why?
Oscar Yen wrote: create table a ( imgid int not null, parent int, imgtype char(3), img longtext, primary key (imgid), key searchkey (parent, imgid) ) type = innodb; T1) select imgid, parent from a where parent = 10; returns 3357 rows in 0.08 sec. T2) select imgid, parent, imgtype from a where parent = 10; return 3357 rows in 8.32 sec.!!! T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in almost same speed. Anybody can explain my questions: Q1- What cause the slow down, T2 vs T1 and T4 vs T3? Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance of T6 and T4? Best Reguards. Q1: It's explained in the MySQL Documentation under Optimization - How MySQL Uses Indexes. See http://www.mysql.com/doc/en/MySQL_indexes.html and pay close attention to the 5th bullet. This explains the behavior you see from T7. Q2: I have no answer for you there, Sorry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
elminating filesort
Hello folks :) I'm trying to eliminate a filesort from a very simple query but having no luck. Even though I've read the manual section on when indexes are used in conjunction with ORDER BY, it seems I just can't get an index to be used. I'm running MySQL 3.23.58. The table: CREATE TABLE `minifatboy` ( `p1date` date NOT NULL default '-00-00', `p2date` date NOT NULL default '-00-00', `struct` char(120) binary default NULL, PRIMARY KEY (`p2date`,`p1date`) ) TYPE=MyISAM This table has just under 1 million rows. It has a "big brother" table called fatboy which has over 143 mil rows on which I'll be running the exact same query later, assuming I can get good performance out of this. The query? SELECT struct FROM minifatboy ORDER BY p2date, p1date; An explain gives me: mysql> explain select struct from minifatboy order by p2date, p1date; ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | minifatboy | ALL | NULL | NULL |NULL | NULL | 999370 | Using filesort | ++--+---+--+-+--+++ 1 row in set (0.00 sec) I feel like I have to be missing something obvious here. I don't want to have to wait while MySQL performs a filesort to sort the data into the order already specified by the primary key. It doesn't matter much for minifatboy, but for fatboy this means performing a filesort on a 31gb table. Is it just because I'm not restricting rows and therefore MySQL thinks it should just do a table scan? I know I can try to force the use of an index with MySQL 4, but I'd rather not upgrade if I don't have to (USE INDEX doesn't help, btw). Thanks, --Ludwig Pummer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]