HTML in MySQL?
Hi guys, I'm fairly new to MySQL and I've searched for about a week looking for an answer to this... I'm trying to design a Database that would hold HTML sites in the DB itself and use PHP to call for the HTML file - which would be populated with Content from another table in MySQL. I was told I could put HTML into MySQL... so my main question would be: is this possible?, is this a BAD thing? but most of all... would this be resource intensive? Thanks for any answers! I hope I'm using this list correctly - I did search Google and I've been reading an MySQL Manual for over a week now trying to get it down 110%! Thanks, Clint
Re: HTML in MySQL?
clint lenard wrote: Hi guys, I'm fairly new to MySQL and I've searched for about a week looking for an answer to this... I'm trying to design a Database that would hold HTML sites in the DB itself and use PHP to call for the HTML file - which would be populated with Content from another table in MySQL. I was told I could put HTML into MySQL... so my main question would be: is this possible?, is this a BAD thing? but most of all... would this be resource intensive? Of course -- HTML is just a string like any other. Just use a TEXT field of the appropriate size for the size of your HTML pages, and then store the HTML as a string in that field. Then retrieve it and output the string with PHP. -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
Thanks Jasper for the info! This may sound stupid - but I just want to be sure... do I need to strip the slashes and special characters out and add them when they're called... or..? I'm just trying to get a clear picture of exactly what needs to be done with this particular job... thanks! On 9/9/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: clint lenard wrote: Hi guys, I'm fairly new to MySQL and I've searched for about a week looking for an answer to this... I'm trying to design a Database that would hold HTML sites in the DB itself and use PHP to call for the HTML file - which would be populated with Content from another table in MySQL. I was told I could put HTML into MySQL... so my main question would be: is this possible?, is this a BAD thing? but most of all... would this be resource intensive? Of course -- HTML is just a string like any other. Just use a TEXT field of the appropriate size for the size of your HTML pages, and then store the HTML as a string in that field. Then retrieve it and output the string with PHP. -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: HTML in MySQL?
Hi Clint, Another possibility is to actually run the mysql client with a --html option and let mysql do the work for you eg : prompt $ echo SELECT some_stuff FROM table | mysql -u xyz -pxxx --html This generates a block of html for a table with the data encapsulated within, then it is just a matter of capturing the output and putting it in the html stream. You can also generate xml the same way. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: clint lenard [mailto:[EMAIL PROTECTED] Sent: Friday, 9 September 2005 3:31 PM To: mysql@lists.mysql.com Subject: HTML in MySQL? Hi guys, I'm fairly new to MySQL and I've searched for about a week looking for an answer to this... I'm trying to design a Database that would hold HTML sites in the DB itself and use PHP to call for the HTML file - which would be populated with Content from another table in MySQL. I was told I could put HTML into MySQL... so my main question would be: is this possible?, is this a BAD thing? but most of all... would this be resource intensive? Thanks for any answers! I hope I'm using this list correctly - I did search Google and I've been reading an MySQL Manual for over a week now trying to get it down 110%! Thanks, Clint -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
David and Jasper - thank you both! I will play around with this more now that you have explained my biggest questions! Thanks guys, Clint :-) On 9/9/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: clint lenard wrote: Thanks Jasper for the info! This may sound stupid - but I just want to be sure... do I need to strip the slashes and special characters out and add them when they're called... or..? I'm just trying to get a clear picture of exactly what needs to be done with this particular job... If you're using PHP, you need to run mysql_real_escape_string() on any string data that is going into MySQL. This will handle escaping and special characters for you, and there is no need to unescape it when retrieving it from the DB. See http://www.php.net/mysql_real_escape_string HTH -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
clint lenard wrote: Thanks Jasper for the info! This may sound stupid - but I just want to be sure... do I need to strip the slashes and special characters out and add them when they're called... or..? I'm just trying to get a clear picture of exactly what needs to be done with this particular job... If you're using PHP, you need to run mysql_real_escape_string() on any string data that is going into MySQL. This will handle escaping and special characters for you, and there is no need to unescape it when retrieving it from the DB. See http://www.php.net/mysql_real_escape_string HTH -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delivery failed
The original message was received at Fri, 9 Sep 2005 15:00:22 +0800 from chosun.com [77.253.195.244] - The following addresses had permanent fatal errors - mysql@lists.mysql.com - Transcript of session follows - ... while talking to host 147.168.60.215: 550 5.1.2 mysql@lists.mysql.com... Host unknown (Name server: host not found) Warning : [EMAIL PROTECTED] was infected with the malicious virus WORM_MYDOOM.M and has been passed. Note: The attachment that HAS BEEN PASSED was infected with non-cleanable virus. Open such an attachment is NOT recommended. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Total newb at performance tuning mysql
Scott Haneda wrote: Unless I am totally off base here, the mysql docs tell very little in regards to how to performance tune mysql. So far I have found this article to help: http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3 But it still leaves me with a ton of questions. For starters, and don't laugh, I just installed mysql and let it run, started adding sites to it and such. The machine has 1 gig of ram in in, but at times, I could ask it were a little snappier. It is a 1.2Ghz machine. First thing I did last night was to try to get a hnalde on this. From what I can tell, I have no my.cnf file in place, so there must be some default settings that are compiled in. I set up a cron job to run the following: show variables like 'table_cache' show status like 'open%_tables' I have been running this once a minute for a day now, how long should I run it to get an idea of what I need to change to better suit my servers load usage? First entry after a mysql restart: Wed Sep 7 03:18:00 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 1050 --- Last entry as of now: --- Wed Sep 7 16:27:01 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 4407 So it looks like Opened_tables is going to increase forever at the rate I have it, which I am guessing is not a good thing, but not sure what I need to do to fix this. I am really looking for some pretty detailed docs on exactly what I can do in my.cnf to make this work out better. Thanks again, and if there are any questions that would help me get a better answer, please let me know. Try to find a book called High Performance MySQL from Derek J. Balling Jeremy Zawodny (the guy from Yahoo). It's an interesting and useful book which skips all the basic stuff and gets you into performance tuning. ISBN : 0-596-00306-4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.26 has been released
...and when MySQL-shared-compat-4.0.26-0.i386.rpm will be released? rpm (and some older tools) complains :( There is only: Dynamic client libraries (including 3.23.x libraries)4.0.25-0482.1K Joerg Bruehe wrote: Hi, MySQL 4.0.26, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production version. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Added the mysql_get_client_version C API function to the embedded server library. (It was present in the regular client library but inadvertently omitted from the embedded library.) (Bug #10266 (http://bugs.mysql.com/10266)) Bugs fixed: * An optimizer estimate of zero rows for a non-empty InnoDB table used in a left or right join could cause incomplete rollback for the table. (Bug #12779 (http://bugs.mysql.com/12779)) * Query cache is switched off if a thread (connection) has tables locked. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385 (http://bugs.mysql.com/12385)) * For PKG installs on Mac OS X, the preinstallation and postinstallation scripts were being run only for new installations and not for upgrade installations, resulting in an incomplete installation process. (Bug #11380 (http://bugs.mysql.com/11380)) * On Windows, applications that used the embedded server made it not possible to remove certain files in the data directory, even after the embedded server had been shut down. This occurred because a file descriptor was being held open. (Bug #12177 (http://bugs.mysql.com/12177)) * Creation of the mysql group account failed during the RPM installation. (Bug #12348 (http://bugs.mysql.com/12348)) * Attempting to repair a table having a fulltext index on a column containing words whose length exceeded 21 characters and where myisam_repair_threads was greater than 1 would crash the server. (Bug #11684 (http://bugs.mysql.com/11684)) * When two threads compete for the same table, a deadlock could occur if one thread has also a lock on another table through LOCK TABLES and the thread is attempting to remove the table in some manner and the other thread want locks on both tables. (Bug #10600 (http://bugs.mysql.com/10600)) Bye, Joerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed UPDATE execution?
Hello. So now my question... is it possible that MySQL didn't execute that first UPDATE query on that first page until after the rest of the process completed? ... - Every page has UPDATEs, INSERTs, or DELETE commands so I'm wondering whether the There is no 'UPDATE DELAYED' or similar. Possible cause of the problem is that your application uses REPLACE DELAYED instead of UPDATE, but you haven't meant this statement. Are you sure that this issue doesn't come from browser's (proxy) cache? - I did notice that the website was very sluggish during that time and even unresponsive. Would that affect the order of query execution? If you don't use DELAYED statements the order of the query execution from one connection shouldn't change. Hi all, I'll give you a little background so that my question is put into context. I've already posted this question to my local programmers message group and they said to post it here. So, here it goes: I have a phone order system that I wrote in PHP. It has a series of pages starting with a customer select and edit screen. After the billing and shipping information is verified and modified, the employee continues through the shopping process until the final checkout page where the order is stored. One of the employees contacted me and said that she was placing a new order, found an existing customer, modified the shipping address on that first page and then entered the rest of the order. The order does not reflect the change in shipping address, BUT looking at the account now shows the correctly changed shipping address. She says that she did not go back after the order was entered to change the account. So now my question... is it possible that MySQL didn't execute that first UPDATE query on that first page until after the rest of the process completed? - The customer page is a basic form that posts the information to itself with a simple UPDATE command and then goes to the next page. - Every page has UPDATEs, INSERTs, or DELETE commands so I'm wondering whether the commands are queued and that first update was delayed for some reason? - There are no session variables, or hidden form fields, that would cause a problem with hitting BACK or whatnot in the process. - I did notice that the website was very sluggish during that time and even unresponsive. Would that affect the order of query execution? Any ideas? Thanks in advance for your help! Jenifer Subscriptions [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL db size using show table status
Hi, I am trying to compute the MySQL db size using show table status command. It gives me the size of .MYD and .MIY files, but not .frm which is typically 12k (using 4.1.9 version of MySQL) Qus 1. is there any way to deterministically compute the value of .frm file Qus 2. Is there any other way to compute the db size (other than disk quota). Thanx in anticipation, Jaspreet Singh -- Don't Walk as if you own the world, Walk as if you don't care who owns it. Jaspreet Singh Software Engineer, Ensim India. [EMAIL PROTECTED] +91 9890712226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
Hello, Clint! I'm trying to design a Database that would hold HTML sites in the DB itself and use PHP to call for the HTML file - which would be populated with Content from another table in MySQL. I was told I could put HTML into MySQL... so my main question would be: is this possible?, is this a BAD thing? but most of all... would this be resource intensive? If you use textarea field of a form, it produces null characters (\n) in the end of every string. I recommed to replace them with br tags before writing into the database. It'll help to avoid output problems. Use preg_replace(); for it. -- Good Luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: `gmake test` probs Solaris9 for M. 4.1.4.
I've see no reply to this and I checked the archives, so please excuse my resubmitting it. Maybe I shouldn't be using 4.1.14 but 4.0.x? Thank you, Hugh On Wed, 7 Sep 2005, Hugh Sasse wrote: Attempts to do make test prior to installation, to check that I'm installing something workable, give: quote neelix hgs 63 % gmake test cd mysql-test; perl mysql-test-run.pl perl mysql-test-run.pl --ps-protocol No ndbcluster support Killing Possible Leftover Processes Removing Stale Files Installing Master Databases Installing Master Databases Installing Slave Databases Installing Slave Databases Installing Slave Databases === Finding Tests in the 'main' suite Starting Tests in the 'main' suite TESTRESULT --- alias [ pass ] alter_table [ pass ] analyse [ pass ] analyze [ pass ] ansi[ pass ] archive [ fail ] Errors are (from /scratch/hgs/mysql-4.1.14/mysql-test/var/log/mysqltest-time) : This test is not supported by this installation mysqltest returned unexpected code 15872, it has probably crashed (the last lines may be the most important ones) Aborting: archive failed. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master(s) shutdown finished Slave(s) shutdown finished gmake: *** [test] Error 1 neelix hgs 64 % /quote So, firstly, invoke what with '--force'? You don't get anything useful if you pass that to make Also, how can I tell if InnoDB built OK? I'm doing this for use with Ruby on Rails, and I need it to be in a separate directory from the version we already have (3.x) used by others. I modified mysql-4.1.14/BUILD/compile-solaris-sparc thusly neelix hgs 81 % display_diffs.rb . --- ./compile-solaris-sparc.orig2005-08-17 18:06:41.0 +0100 +++ ./compile-solaris-sparc 2005-09-06 18:36:25.386697000 +0100 @@ -11,6 +11,6 @@ (cd gemini aclocal autoheader aclocal automake autoconf) fi -CFLAGS=-g -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -O3 -fno-omit-frame-pointer -mcpu=v8 -Wa,-xarch=v8plusa CXX=gcc CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-pointer -mcpu=v8 -Wa,-xarch=v8plusa -g ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client +CFLAGS=-g -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -O3 -fno-omit-frame-pointer -mcpu=v8 -Wa,-xarch=v8plusa CXX=gcc CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-pointer -mcpu=v8 -Wa,-xarch=v8plusa -g ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-tcp-port=3308 --with-unix-socket-path=/tmp/mysql4.sock --prefix=/usr/local/mysql-4.1.14 gmake -j 4 neelix hgs 82 % and I invoked the script directly, and also tried with bash. Not sure what else to pass on. GNU Make version 3.79.1, by Richard Stallman and Roland McGrath. Built for sparc-sun-solaris2.9 gcc (GCC) 3.4.3 Thank you Hugh -- 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: Delayed UPDATE execution?
Okay, I asked the web host guys and this is what they said: Hi Jenifer, we only use single instances of MySQL so the master/slave replication issue would not be possible. As far as I know, the only way a delayed update could occur would be if you had multiple updates queued behind a slow update or any slow query that has left the table locked. But if that were to occur, I don't think the user of your application would have been able to advance to the next page of the site since the queries from them hitting submit would have been hung. Unless you are using timeout values in your php so after a delay, it gives up and lets the person move on anyway, and then ultimately the final page is reached while that first update finally went through. That's what I had thought that the user would have been stuck at that first page. I'm trying to figure out how a cache could affect it. The values are taken from the $_POST variable and inserted into the table that way. When I looked at the order and customer pages, it was after she had done everything, and I saw the incorrect order shipping address and a correct account address. I haven't looked at this client's account before as the company has 40,000+ customers in their database. (a big cudo for MySQL for handling them so well!) I think I'll just give up on this one and readdress it if it happens again. It will probably eat at me for awhile, though, not knowing the answer. lol! Jenifer - Original Message - How is your MySQL installation set up? At my company, we have a master server and several slaves replicating off that master. All inserts/updates go to the master, and all selects go to the slaves. We have had some instances where we insert or modify a record in the master, and request that information from a slave before the replication has had a chance to complete, so we get old data. Do you have any sort of setup like that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing query WHERE date0
Devananda [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Dan Baker wrote: Eric Bergen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] When you add that index are more than 30% of the rows in the table DateTimeNext1126215680? There are currently 28.53% of the rows that have DateTimeNext1126215680 Does this mean something of interest? If so, what? Thanks DanB Dan Baker wrote: I have lots of tables that are similar in nature: id int(11) PRI NULL auto_increment Name varchar(30) DateTimeNext int(11) The DateTimeNext field represents when this records needs attention. A value of zero indicates it is being ignored. There are times when *lots* of records DateTimeNext values will be zero. I want to find all records in the database that need attention today, so a typical query looks like: SELECT id,Name FROM tbl WHERE DateTimeNext1126215680 When I EXPLAIN this query, I get the following: table type possible_keys key key_len ref rows Extra Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where If I add an index for DateTimeNext, the EXPLAIN shows: table type possible_keys key key_len ref rows Extra Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where It appears that the index does NO good in this query. Is there anything I can do to optimize this query? Alter the table to improve the query? Do anything to not scan the entire stinkin' table? Thank you, DanB You may want to take a look at this page: http://dev.mysql.com/doc/mysql/en/how-to-avoid-table-scan.html Another possibility would be to change your data structures so that you can use an equality, rather than a range scan. For example, make DateTimeNext into a date or datetime field (rather than an int), and then alter your SELECT statement to be SELECT id,Name FROM tbl WHERE DateTimeNext = DATE(NOW()); I did notice that if I use an = comparison, that it will use the index. Unfortunately, I need all records that are after a given date, and every record has a different date, so I can't use an = comparison. It does seem strange that the = will use the index, but a or won't. Thanks for the idea. DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with SQL Command
Greetings all, I am knew to the the world of SQL and am interested in putting data into a database that I have created. The data will be in the form of a tab delimited text file that will generated several times a day with different data. What I would like to do is use the LOAD LOCAL DATA INFILE xxx.txt INTO TABLE tablename; to do this from windows scheduler or CRON to schedule the job to be ran as needed. I know the SQL command works fine. I'm using it on another database, but I am manually entering it in. On this project, I won't have that kind of time to manually type it or open the MySQL Query Brower and re-run the command. Any help would be greatly appreciated, but remember I am still real new to this. Thanks, eric
RE: HTML in MySQL?
If you use textarea field of a form, it produces null characters (\n) in the end of every string. I recommed to replace them with br tags before writing into the database. It'll help to avoid output problems. Use preg_replace(); for it. Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. And i would highly recommend *not* replacing them with br tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HTML in MySQL?
Amen. Translating user input into HTML is great until you need to read the data *out*, and someone decides the output should be formatted as RTF or PDF or text. Best to store it as you got it, IMO. -Original Message- From: Alan Williamson [mailto:[EMAIL PROTECTED] Sent: Friday, September 09, 2005 10:11 AM To: mysql@lists.mysql.com Subject: RE: HTML in MySQL? If you use textarea field of a form, it produces null characters (\n) in the end of every string. I recommed to replace them with br tags before writing into the database. It'll help to avoid output problems. Use preg_replace(); for it. Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. And i would highly recommend *not* replacing them with br tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- 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]
Replication started but not running
Hey all, Just brought up a new RH EL3 server and updated MySQL to ver 4.1.13-standard I'm trying to get replication from an older server running version Server version 4.0.16-standard-log Replication on the slave system seems to start ok but then just sites there with connecting to master mysql show slave status \G *** 1. row *** Slave_IO_State: Connecting to master Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: DB01TC07927-bin.057 Read_Master_Log_Pos: 590592661 Relay_Log_File: FIN01TC07927-relay-bin.03 Relay_Log_Pos: 4 Relay_Master_Log_File: DB01TC07927-bin.057 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 590592661 Relay_Log_Space: 4 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Any ideas as to what is locking it up? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: `gmake test` probs Solaris9 for M. 4.1.4.
Hugh, I get the same thing on Mac OS X 10.3.9. My thinking was that rather than replying with an unhelpful Me too, I would do some digging and then reply with what I found. I realize now that left you hanging (sorry), so I'll report what I've found so far. The test suite is broken. The archive test is meant to test the archive table engine, but the archive engine is not built into mysql by default. You have to add the --with-archive-storage-engine if you want it. You didn't do that, so the archive test should have been skipped. With mysql 4.1.12, `make -n test` reveals cd mysql-test; ./mysql-test-run ./mysql-test-run --ps-protocol With 4.1.14, that has changed to cd mysql-test; perl mysql-test-run.pl perl mysql-test-run.pl --ps-protocol Apparently, the test program shell script, mysql-test-run, has been rewritten as a perl script, though there is no mention of this in the change log http://dev.mysql.com/doc/mysql/en/news-4-1-x.html. A quick glance at the source shows that the perl script is not finished -- it has quite a few commented-out, fix me sections. You still have a couple of options to test your build. You can run the new test suite with the --force option. Do what make would do, `cd mysql-test`, then `perl mysql-test-run.pl --force`. You will see that every test that should have been skipped will instead be run and fail (archive, the bdb tests, blackhole, csv, example, func_des_encrypt, isam, the ndb tests, openssl_1, raid, and so on). If every test passes except for tests of things you don't have, you should be OK. In particular, there are a set of innodb tests which should answer your question about whether or not innodb is working in your build. There's a second way, which I think is better. It turns out that the old shell-script test suite is still built. Hence, you can still test the old way. cd mysql-test ./mysql-test-run --force This will properly skip tests of features not compiled in. Note that I added --force because, at least in my case, the new embedded test, not_embedded_server, is run and fails. I'm not yet sure if that should have been skipped. As the old test script is still built and the new test script is unfinished, undocumented, and broken, I am suspicious that the real problem is that make was prematurely (accidentally?) changed to use the new one before it was ready. I've copied the bugs list in hopes of an answer. Michael P.S. Out of curiosity, why did you find it necessary to edit BUILD/compile-solaris-sparc? Couldn't you make the changes you wanted with options to configure? Hugh Sasse wrote: I've see no reply to this and I checked the archives, so please excuse my resubmitting it. Maybe I shouldn't be using 4.1.14 but 4.0.x? Thank you, Hugh On Wed, 7 Sep 2005, Hugh Sasse wrote: Attempts to do make test prior to installation, to check that I'm installing something workable, give: quote neelix hgs 63 % gmake test cd mysql-test; perl mysql-test-run.pl perl mysql-test-run.pl --ps-protocol No ndbcluster support Killing Possible Leftover Processes Removing Stale Files Installing Master Databases Installing Master Databases Installing Slave Databases Installing Slave Databases Installing Slave Databases === Finding Tests in the 'main' suite Starting Tests in the 'main' suite TESTRESULT --- alias [ pass ] alter_table [ pass ] analyse [ pass ] analyze [ pass ] ansi[ pass ] archive [ fail ] Errors are (from /scratch/hgs/mysql-4.1.14/mysql-test/var/log/mysqltest-time) : This test is not supported by this installation mysqltest returned unexpected code 15872, it has probably crashed (the last lines may be the most important ones) Aborting: archive failed. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master(s) shutdown finished Slave(s) shutdown finished gmake: *** [test] Error 1 neelix hgs 64 % /quote So, firstly, invoke what with '--force'? You don't get anything useful if you pass that to make Also, how can I tell if InnoDB built OK? I'm doing this for use with Ruby on Rails, and I need it to be in a separate directory from the version we already have (3.x) used by others. I modified mysql-4.1.14/BUILD/compile-solaris-sparc thusly neelix hgs 81 % display_diffs.rb . --- ./compile-solaris-sparc.orig2005-08-17 18:06:41.0 +0100 +++ ./compile-solaris-sparc 2005-09-06 18:36:25.386697000 +0100 @@ -11,6 +11,6 @@ (cd gemini aclocal autoheader aclocal automake autoconf) fi -CFLAGS=-g -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -O3
Re: `gmake test` probs Solaris9 for M. 4.1.4.
On Fri, 9 Sep 2005, Michael Stassen wrote: Hugh, I get the same thing on Mac OS X 10.3.9. My thinking was that rather than replying with an unhelpful Me too, I would do some digging and then reply with what I found. I realize now that left you hanging (sorry), so I'll report what I've found so far. Ok, thanks for this report, which is in plenty of detail, even if it is so far :-) The test suite is broken. Who guards the guards? :-) The archive test is meant to test the archive table engine, but the archive engine is not built into mysql by default. You have to add the --with-archive-storage-engine if you want it. You didn't do that, so the I'm not sure what it is, so I probably don't need it. archive test should have been skipped. With mysql 4.1.12, `make -n test` reveals cd mysql-test; ./mysql-test-run ./mysql-test-run --ps-protocol With 4.1.14, that has changed to cd mysql-test; perl mysql-test-run.pl perl mysql-test-run.pl --ps-protocol Apparently, the test program shell script, mysql-test-run, has been rewritten as a perl script, though there is no mention of this in the change log http://dev.mysql.com/doc/mysql/en/news-4-1-x.html. A quick glance at the I was rather surpised about the dependency on Perl. source shows that the perl script is not finished -- it has quite a few commented-out, fix me sections. I'll have a look. You still have a couple of options to test your build. You can run the new test suite with the --force option. Do what make would do, `cd mysql-test`, then `perl mysql-test-run.pl --force`. You will see that every test that should have been skipped will instead be run and fail (archive, the bdb tests, blackhole, csv, example, func_des_encrypt, isam, the ndb tests, I'm fairly fluent in perl, though mine's a bit Chaucerian, since I learned Perl4 :-), and am not *so* familiar with the 5 constructs, but I could try to send patches (to whom?). openssl_1, raid, and so on). If every test passes except for tests of things you don't have, you should be OK. In particular, there are a set of innodb tests which should answer your question about whether or not innodb is working in your build. Thanks. There's a second way, which I think is better. It turns out that the old shell-script test suite is still built. Hence, you can still test the old way. Yes, and It may be worth trying both. cd mysql-test ./mysql-test-run --force This will properly skip tests of features not compiled in. Note that I added I can probably steal how to skip them and stick that in the Perl. --force because, at least in my case, the new embedded test, not_embedded_server, is run and fails. I'm not yet sure if that should have been skipped. The errors explicitly told me I needed --force, so that's OK. As the old test script is still built and the new test script is unfinished, undocumented, and broken, I am suspicious that the real problem is that make was prematurely (accidentally?) changed to use the new one before it was ready. I've copied the bugs list in hopes of an answer. Definitely some weirdness about the release process there :-) I'm sure I've done worse in the past, though. I'm not subscribed to the bugs list, so this might bounce back off there. The third way, I suppose, is to ask whether I'm actually using the correct version -- should I be on 4.1,x, or 4.0.x, or what? I'm under the impression that for work with Ruby on Rails I need the latest 4.x but I could have misunderstood something. What I really mean is: which versions are considered stable? [Ruby has a custom that minor release numbers are odd for developement, even for stable, but minorminor ones increment successively after that: 1.8.2, and 1.8.3 (due soon) are stable, 1.9.x is development (at present). I'm not sure how things work in the MySQL world.] Michael P.S. Out of curiosity, why did you find it necessary to edit BUILD/compile-solaris-sparc? Couldn't you make the changes you wanted with options to configure? I was passing options to configure, and the was a bit of setup in compile-solaris-sparc that was probably there for a reason. So I changed it there. I changed it to give me the alternative port and directory so I don't munge things for people using V3.x, used for teaching. [Besides, I'd have to mess with autoconf to fix configure properly, as it is setup by that, and I'm not so fluent in autoconf as I'd wish, plus it seems silly to mod the autoconf stuff for something so parochial as port + directory.] Maybe there's a better place altogether to define this stuff? Thank you, Hugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: `gmake test` probs Solaris9 for M. 4.1.4.
Michael, you beat me in replying, and saved me from typing the analysis - thanks! Michael Stassen wrote: [[...]] The test suite is broken. Well, I would not put it that way - a diplomatic wording is: The tool to run the test suite does not run as it should. I understand that the effect for Hugh and you is the same ;-) The archive test is meant to test the archive table engine, but the archive engine is not built into mysql by default. You have to add the --with-archive-storage-engine if you want it. You didn't do that, so the archive test should have been skipped. With mysql 4.1.12, `make -n test` reveals cd mysql-test; ./mysql-test-run ./mysql-test-run --ps-protocol With 4.1.14, that has changed to cd mysql-test; perl mysql-test-run.pl perl mysql-test-run.pl --ps-protocol Apparently, the test program shell script, mysql-test-run, has been rewritten as a perl script, though there is no mention of this in the change log http://dev.mysql.com/doc/mysql/en/news-4-1-x.html. A quick glance at the source shows that the perl script is not finished -- it has quite a few commented-out, fix me sections. Correct. A Perl script for the test suite is being worked on. Reasons: 1) A shell script will never work for native Windoes users who do not install Cygwwin, MinGW, MKS or similar suites. 2) The shell script lacks some functionality which is needed, and it is already too convoluted. So that Perl script is work in progress which we needed to test on all build platforms. It was an oversight that it got included in the source tree in the current status, the tests have also been done using the old shell script. [[...]] There's a second way, which I think is better. It turns out that the old shell-script test suite is still built. Hence, you can still test the old way. cd mysql-test ./mysql-test-run --force Right, this is what I recommend. To be a bit picky: There is one standard test suite only (which is included in the source tar-ball), but there are currently two different scripts to run it, the (old) shell and the (new) Perl script. The specific error causing your and Hugh's problem, the incorrect skipping of tests which are not applicable to the server to be tested, has been fixed, but maybe this version has not yet been pushed to the 4.1 tree. [[...]] As the old test script is still built and the new test script is unfinished, undocumented, and broken, I am suspicious that the real problem is that make was prematurely (accidentally?) changed to use the new one before it was ready. I've copied the bugs list in hopes of an answer. Both yes and no: yes in the sense that the new suite (as included in the 4.1.14 tree) is not yet correct. no in the sense that the change to use it _internally_ was deliberate in order to get full platform coverage. We are sorry it caused you inconveniences! Regards, Jörg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to save graphs in a table?
I would like to save graphs in a mysql data base. The table definition I have set up is: CREATE TABLE `GRAPHS` ( `graphid` int(11) NOT NULL auto_increment, `graphic` blob, `f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`graphid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 And I have allocated the user account the file privilege. The sql I am trying to use is: load data infile 'filename.png' into table GRAPHS (graphic); What happens is that the takes the file and breaks it up into lines and loads each line as a row, thus storing it over many columns. How can I get it to store the file in the 'graphic' blob with one 'graphid'. Thanks in advance. Connie Logg, Network Analyst Stanford Linear Accelerator Center ph: 650-926-2879 Happiness is found along the way, not at the end of the road, and 'IF' is the middle word in life. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row level replication in 5.1
Kenji HIROHAMA [EMAIL PROTECTED] wrote on 09/08/2005 08:54:58 PM: Hi, Does somebody point me out where I should refer to understand what is row level replication implemented in 5.1? I should have read the source comments in the source tree, but the bk port is closed at my environment. Regards, Kenji I couldn't find it in the manual either but I can try to explain the concept and how it is different than the currently used replication process, statement level replication (SLR). If you know how replication currently works in MySQL, you understand that each SQL statement that makes a change to data (INSERT, UPDATE, or DELETE) plus several others that modify database structures can be recorded to what is called the binary log or binlog for short. I say it as can be written because you can apply some filters so that you only binlog the events that apply to certain tables or datbases that you are interested in duplicating at the slave server. Events are only written to the binlog as each transaction is committed, transactions that fail or are rolled back are not logged in the binlog. Each slave server reads the binlog from the master, copies it to a local drive, then processes each command in sequence attempting to duplicate the results that occurred on the Master when it executed the same statement. Because the slave needs to execute each command within the exact same set of enviromental settings (what time did the original command occur on the MASTER, within the context of which database, using which user account, etc.) there are certain problems with duplicating the effects of particular commands correctly on the slave servers. Row level replication (RLR) gets around those environmentally-based pitfalls by actually duplicating each change, row by row, from master to slave. For statements that change many rows of data, RLR will need a lot of bandwidth because every change will replicate row-by-row from the master to the slave. (I don't remember reading if they are optimizing the difference stream by implementing field-level replication or not). This keeps the slave database in perfect coordination with the master (after some transfer and processing lag) because stored procedures and other functions that rely on system- and user-level environmental settings (like time zones) are not executed on the slave but only on the master. Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Total newb at performance tuning mysql
One of our engineers first installed MySQL on one of our Sun boxes which was doing nothing more than MySQL... It seems we also put it on the server and turned it on... it behaved very badly. Essentially when we started to investigate MySQL and find out if we could use it we discovered that our Sun box with four processors and 4Gbytes of ram was running MySQL in 64M of memory... it's an easy mistake to make, and the lesson here is that out of the box (as it were) MySQL settings are a little on the low side for performance... but work well for a shared environment where you may have web server, mail server and more all running on the same box. If you want MySQL to sing... you are going to have to do a lot of tuning. On the table_cache issue... We have about 40 tables per database, and some of our servers have 30 databases. Our servers have as many as 500 connections... one server at random which has an uptime of 60 days shows: mysql1 (none): show status like 'open%_tables'; +---+---+ | Variable_name | Value | +---+---+ | Open_tables | 2748 | | Opened_tables | 3288 | +---+---+ 2 rows in set (0.01 sec) mysql1 (none): show variables like 'table_cache'; +---+---+ | Variable_name | Value | +---+---+ | table_cache | 4096 | +---+---+ 1 row in set (0.01 sec) So we have a table cache value, but it's clearly on the high side and could be lower. Yours at 64 is on the low side. While I won't ignore table cache as being important, there are many many performance tuning things that need to get done to have an impact on the server. Firstly (knowing the type of things you are doing) InnoDB will likely be a far better choice for most of your tables than the default database engine, myisam. You need to tune the machine to run InnoDB, and then convert your data to InnoDB. If you are using MySQL 4.1 (which I can't remember) I would advise using innodb_file_per_table. If you are looking at upgrading to 4.1 I'd do that first before switching to innodb_file_per_table... it's a little hard to claim back the shared table space after the fact. If not 4.1 then go with InnoDB and build a big enough shared table space file set to hold all your data with room to spare. We typically build it with 20 2Gbyte files... for 40Gbytes of InnoDB table space. Decide how much memory you have to run MySQL... i the server does only MySQL, this is easy... if it's also a web server running Apache and so on, then you have to decide the mix. Assuming MySQL only give InnoDB 80% of the server's total memory, up to certain limits with 32 bit operating systems and the like... For OS X we found these are pretty much the magic numbers for max values if you have more than 2Gbytes of ram but can't handle 64 bit: innodb_buffer_pool_size=1850M innodb_additional_mem_pool_size=256M innodb_log_files_in_group=2 innodb_log_file_size=250M innodb_log_buffer_size=20M innodb_thread_concurrency=16 innodb_flush_log_at_trx_commit=0 innodb_lock_wait_timeout=30 Once you convert everything to InnoDB the regular MySQL buffers have less importance, but should still have some values. InnoDB or not the query_cache is a good thing, but don't set it too high... We are at 128Mbytes and that's a little higher than we need... it appears we could live in under 64Mbytes, and our query cache handles about 25% of our queries... properly set it's a good thing. So... most likely switching to InnoDB will improve performance... Managing all your memory settings and caches so that the ones that matter have values that will help will make a great deal of difference... of course to do this you'll need to make a my.cnf file and install it where MySQL will look for it. Also important for tuning is watching the slow queries, finding out if there are moe things you can do with indexes, or if there are other ways to optimize the queries. Turn on the slow query log... leave it set to the default 10 seconds... find out what queries are running longer than 10 seconds and figure out how to optimize them... changing indexes, changing the query etc... Once you have worked that out and your slow query log gets few hits, reduce it to 5 seconds and work through those queries... again reduce it further as you work out the performance issues and you'll find that you are streaming along. There's a lot more that can be done with specific memory settings and so on... but I think I've given you a handful of things to get started on, and you can come back for more when you have made some headway on this part. Best Regards, Bruce On Sep 7, 2005, at 4:28 PM, Scott Haneda wrote: Unless I am totally off base here, the mysql docs tell very little in regards to how to performance tune mysql. So far I have found this article to help: http://www.databasejournal.com/features/mysql/article.php/
Re: `gmake test` probs Solaris9 for M. 4.1.4.
On Fri, 9 Sep 2005, Joerg Bruehe wrote:
Michael,
you beat me in replying, and saved me from typing the analysis - thanks!
Michael Stassen wrote:
[[...]]
The test suite is broken.
Well, I would not put it that way - a diplomatic wording is:
The tool to run the test suite does not run as it should.
I understand that the effect for Hugh and you is the same ;-)
Nobody was claiming *deliberate* breakage! :-)
[...]
quite a few commented-out, fix me sections.
Correct. A Perl script for the test suite is being worked on. Reasons:
1) A shell script will never work for native Windoes users who do not
install Cygwwin, MinGW, MKS or similar suites.
2) The shell script lacks some functionality which is needed, and it is
already too convoluted.
Good reasons.
[...]
cd mysql-test ./mysql-test-run --force
Right, this is what I recommend.
To be a bit picky: There is one standard test suite only (which is included
in the source tar-ball), but there are currently two different scripts to run
it, the (old) shell and the (new) Perl script.
The specific error causing your and Hugh's problem, the incorrect skipping of
tests which are not applicable to the server to be tested,
has been fixed, but maybe this version has not yet been pushed to the 4.1
tree.
Can we grab the new version off the web (CVSweb or similar?)
somewhere? If it is feasible for me I'd like to translate it to
Ruby to give you that much wider coverage. Depends if my Perl
fluency is sufficient, etc. Whether {you, the committee, ???} accept
the contribution is another matter entirely, of course.
Regards, J?rg
Thank you,
Hugh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with SQL Command
Hello. ..e, but I am manually entering it in. Use mysql command line client. You may pass SQL statements as the command line options or execute them from the file (i.e mysql -e sql_statement or mysql sql_file). See: http://dev.mysql.com/doc/mysql/en/mysql.html Eric Mynes [EMAIL PROTECTED] wrote: Greetings all, I am knew to the the world of SQL and am interested in putting data into a database that I have created. The data will be in the form of a tab delimited text file that will generated several times a day with different data. What I would like to do is use the LOAD LOCAL DATA INFILE xxx.txt INTO TABLE tablename; to do this from windows scheduler or CRON to schedule the job to be ran as needed. I know the SQL command works fine. I'm using it on another database, but I am manually entering it in. On this project, I won't have that kind of time to manually type it or open the MySQL Query Brower and re-run the command. Any help would be greatly appreciated, but remember I am still real new to this. Thanks, eric -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default my.cnf vs huge.cnf nearly same performance with sql-bench/run-all-tests
infact .. the default debian config (some of these are just explicit defaults but this is what debian provides): [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking old_passwords = 1 key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 log-bin = /var/log/mysql/mysql-bin.log max_binlog_size = 104857600 skip-bdb outperforms the huge.cnf example: [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english old_passwords = 1 key_buffer = 384M max_allowed_packet = 16M table_cache = 512 sort_buffer_size= 2M read_buffer_size= 2M read_rnd_buffer_size= 8M myisam_sort_buffer_size = 64M query_cache_size= 32M thread_concurrency = 8 log-bin = /var/log/mysql/mysql-bin.log server-id = 1 skip-bdb skip-external-locking in almost every regard. What gives? :) This is a pretty beefy config: dual 3ghz HT xeon .. 2gig 800mhz fsb mem .. U320 SCSI RAID5. I've attached a compare-results for a few machines. the only important ones are 1 and 2. 1 is debians my.cnf and 2 is the slightly modified huge.cnf example. What about that thread_concurrency setting in huge.cnf.. it doesn't seem to show up in a 'show variables' when using it.. is it deprecated? -Matt The result logs which where found and the options: 1 mysql-Linux_2.4.27_2_686_smp_i686 : MySQL 4.1.11 Debian_4sarge1 log 2 mysql-Linux_2.4.27_2_686_smp_i686_db0_te: MySQL 4.1.11 Debian_4sarge1 log 3 mysql-Linux_2.4.27_2_686_smp_i686_db1 : MySQL 4.1.11 Debian_4sarge1 log 4 mysql-Linux_2.6.10-1.770_FC3smp_i686: MySQL 4.1.12 standard 5 mysql-Linux_2.6.10-1.770_FC3smp_i686_rai: MySQL 4.1.12 standard 6 mysql-Linux_2.6.11-1.14_FC3_x86_64 : MySQL 4.1.11 standard 7 mysql-Linux_2.6.8_2_686_smp_i686_kevinz : MySQL 4.1.11 Debian_4sarge1 log = Operation | 1| 2| 3| 4| 5| 6| 7| |mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|mysql-L| - Results per test in seconds: | - ATIS| 8.00| 9.00| 8.00| 16.00| 17.00| 13.00| 32.00| alter-table | 14.00| 14.00| 13.00| 13.00| 10.00| 21.00| 49.00| big-tables | 10.00| 10.00| 10.00| 13.00| 12.00| 10.00| 36.00| connect | 108.00| 105.00| 99.00| 72.00| 71.00| 58.00| 394.00| create | 67.00| 89.00| 89.00| 223.00| 219.00| 98.00| 475.00| insert | 904.00| 908.00| 873.00| 854.00| 845.00| 959.00|3751.00| select | 76.00| 76.00| 73.00| 353.00| 351.00| 250.00| 291.00| wisconsin | 7.00| 7.00| 7.00| 6.00| 5.00| 5.00| 20.00| - The results per operation: | - alter_table_add (100) | 6.00| 6.00| 5.00| 5.00| 4.00| 9.00| 20.00| alter_table_drop (91) | 6.00| 6.00| 6.00| 6.00| 4.00| 9.00| 18.00| connect (1) | 6.00| 6.00| 6.00| 5.00| 5.00| 5.00| 28.00| connect+select_1_row (1)| 8.00| 8.00| 8.00| 7.00| 7.00| 7.00| 33.00| connect+select_simple (1) | 8.00| 7.00| 8.00| 6.00| 6.00| 6.00| 32.00| count (100) | 8.00| 9.00| 8.00| 9.00| 8.00| 6.00| 43.00| count_distinct (1000) | 1.00| 0.00| 1.00| 11.00| 11.00| 6.00| 1.00| count_distinct_2 (1000) | 0.00| 0.00| 0.00| 16.00| 15.00| 8.00| 0.00| count_distinct_big (120)| 8.00| 8.00| 7.00| 19.00| 20.00| 14.00| 32.00| count_distinct_group (1000) |
Re: MySQL 4.0.26 has been released
Hi! Remo Tex wrote: ...and when MySQL-shared-compat-4.0.26-0.i386.rpm will be released? rpm (and some older tools) complains :( There is only: Dynamic client libraries (including 3.23.x libraries)4.0.25-0482.1K It has been built by now, and it is on its way propagating to the mirrors. Expect it to become visible tomorrow morning (Saturday, Sep 10, before noon UTC). Sorry about the delay! Joerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
Hello! Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. Agree, I was wrong. And i would highly recommend *not* replacing them with br tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. If you need to output a HTML string (not in a textarea field), you'll need to replace \n with br. That is why, I think that it is better to replace it before saving (You will not need to replace \n during the output process. It will save you some lines of code, and add productivity.). Of course, you need to be sure that your program will make HTML output more often, than any other type of output. -- Удачи! Владимир Please, avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
Vladimir B. Tsarkov wrote: Hello! Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. Agree, I was wrong. And i would highly recommend *not* replacing them with br tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. If you need to output a HTML string (not in a textarea field), you'll need to replace \n with br. That is why, I think that it is better to replace it before saving (You will not need to replace \n during the output process. It will save you some lines of code, and add productivity.). Of course, you need to be sure that your program will make HTML output more often, than any other type of output. But once you have done that, you can never recover the origional text. Try it on this email if you doubt it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
Use addslashes($htmlCode) and execute query normally. ?php $slashedHtmlCode = addslashes($htmlCode); ? []´s Tomita On 9/9/05, Vladimir B. Tsarkov [EMAIL PROTECTED] wrote: Hello! Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. Agree, I was wrong. And i would highly recommend *not* replacing them with br tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. If you need to output a HTML string (not in a textarea field), you'll need to replace \n with br. That is why, I think that it is better to replace it before saving (You will not need to replace \n during the output process. It will save you some lines of code, and add productivity.). Of course, you need to be sure that your program will make HTML output more often, than any other type of output. -- Удачи! Владимир Please, avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Data import problems
Hi people, I having problems to import a dump generated via mysql dump in mysql 4.0.20slackware version to a mysql 4.1.14. The dump generated with other table name (Insert into temp, temp2 , temp3) in 20 sql large (420mb.) sql files. I tried LOAD DATA INFILE but data not inserted correctly, someone help-me?? tks. Tomita
Re: Data import problems
Filipe Tomita [EMAIL PROTECTED] wrote on 09/09/2005 04:37:01 PM: Hi people, I having problems to import a dump generated via mysql dump in mysql 4.0.20slackware version to a mysql 4.1.14. The dump generated with other table name (Insert into temp, temp2 , temp3) in 20 sql large (420mb.) sql files. I tried LOAD DATA INFILE but data not inserted correctly, someone help-me?? tks. Tomita If you look at the output from mysqldump, you will realize that it is just a series of SQL statements. Execute them with your command line client just as you would any other SQL script file. mysql database -u username -p dumpfile.txt or use the source or . commands if you are already in the CLI mysqlsource dumpfile.txt Where you will run into problems is if you didn't specify a max_packet_length WHEN YOU MADE THE DUMP. That option tells mysqldump just how long the longest single statement can be. If you try to run a dump file that contains a statement that exceeds the max_packet_length setting on your server, it will fail silently (server gone away). If you failed to dump with a maximum set, your options are to increase the MAX_PACKET_LENGTH on the server to accept the longest single statement in your dump file or to edit your dump by hand and break your long statements into shorter ones or both. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Data import problems
tks for reply but i dont have acces to original database... the dump file not generated from me.. Someone tell me about SAD command to replace table name to correct name but i dont find anything about it. On 9/9/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Filipe Tomita [EMAIL PROTECTED] wrote on 09/09/2005 04:37:01 PM: Hi people, I having problems to import a dump generated via mysql dump in mysql 4.0.20slackware version to a mysql 4.1.14. The dump generated with other table name (Insert into temp, temp2 , temp3) in 20 sql large (420mb.) sql files. I tried LOAD DATA INFILE but data not inserted correctly, someone help-me?? tks. Tomita If you look at the output from mysqldump, you will realize that it is just a series of SQL statements. Execute them with your command line client just as you would any other SQL script file. mysql database -u username -p dumpfile.txt or use the source or . commands if you are already in the CLI mysqlsource dumpfile.txt Where you will run into problems is if you didn't specify a max_packet_length WHEN YOU MADE THE DUMP. That option tells mysqldump just how long the longest single statement can be. If you try to run a dump file that contains a statement that exceeds the max_packet_length setting on your server, it will fail silently (server gone away). If you failed to dump with a maximum set, your options are to increase the MAX_PACKET_LENGTH on the server to accept the longest single statement in your dump file or to edit your dump by hand and break your long statements into shorter ones or both. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Data import problems
Perhaps you mean 'sed'. Filipe Tomita wrote: tks for reply but i dont have acces to original database... the dump file not generated from me.. Someone tell me about SAD command to replace table name to correct name but i dont find anything about it. On 9/9/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Filipe Tomita [EMAIL PROTECTED] wrote on 09/09/2005 04:37:01 PM: Hi people, I having problems to import a dump generated via mysql dump in mysql 4.0.20slackware version to a mysql 4.1.14. The dump generated with other table name (Insert into temp, temp2 , temp3) in 20 sql large (420mb.) sql files. I tried LOAD DATA INFILE but data not inserted correctly, someone help-me?? tks. Tomita If you look at the output from mysqldump, you will realize that it is just a series of SQL statements. Execute them with your command line client just as you would any other SQL script file. mysql database -u username -p dumpfile.txt or use the source or . commands if you are already in the CLI mysqlsource dumpfile.txt Where you will run into problems is if you didn't specify a max_packet_length WHEN YOU MADE THE DUMP. That option tells mysqldump just how long the longest single statement can be. If you try to run a dump file that contains a statement that exceeds the max_packet_length setting on your server, it will fail silently (server gone away). If you failed to dump with a maximum set, your options are to increase the MAX_PACKET_LENGTH on the server to accept the longest single statement in your dump file or to edit your dump by hand and break your long statements into shorter ones or both. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stop loading when error
when using the loading command load data local infile '/home/kzhou/xxxy.data' into table mytable; it will run to completion, after a long time, then telling me that 90% of the rows are skipped. load data local infile '/home/kzhou/RUN_BLAST/pep2genome_ost9901.tbn.tab' into table peptidetblne1000 - ; Query OK, 206876 rows affected (7 min 19.26 sec) Records: 3700906 Deleted: 0 Skipped: 3494030 Warnings: 3700906 When I check the key constraints in my data, I found there is no duplicated rows in my input data. How do I tell the parser to stop when seeing the first error? So that I can figure what is wrong. The man page for this function, does not seem to give any hint: http://dev.mysql.com/doc/mysql/en/load-data.html Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple Count Query
I know this has to be a simple query but its really kickin' my butt. I have the table below where fld1 is the year, fld2 is a number, fld 3 is a subnumber, and fld4 is the user. I need to know the count of all the records for user am without the sub number getting in the way. For example, the first record for user am shows in the 3rd year number 1 with two sub records was for user am . That needs to be counted as one item. So when all the items are counted I should have a total of 5 items for user am and not 17 like you'd normally get Any thoughts? thanks +--+--+--+--+ | fld1 | fld2 | fld3 | fld4 | +--+--+--+--+ | 3 | 1 | a | am | | 3 | 1 | b | am | | 3 | 2 | a | am | | 3 | 3 | a | pm | | 3 | 3 | b | pm | | 3 | 3 | c | pm | | 4 | 1 | a | pm | | 4 | 2 | a | pm | | 4 | 3 | a | am | | 4 | 3 | b | am | | 4 | 3 | c | am | | 4 | 3 | d | am | | 4 | 3 | e | am | | 4 | 3 | f | am | | 4 | 4 | a | am | | 4 | 4 | b | am | | 5 | 1 | a | pm | | 5 | 1 | b | pm | | 5 | 1 | c | pm | | 5 | 1 | d | pm | | 5 | 1 | e | pm | | 5 | 2 | a | am | | 5 | 2 | b | am | | 5 | 2 | c | am | | 5 | 2 | d | am | | 5 | 2 | e | am | | 5 | 2 | f | am | +--+--+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple Count Query
I think you want this or something similar: select count(distinct fld2) from yourtable where fld4 = 'am'; N.B. I have not tested this solution. Rhino - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 09, 2005 6:08 PM Subject: Simple Count Query I know this has to be a simple query but its really kickin' my butt. I have the table below where fld1 is the year, fld2 is a number, fld 3 is a subnumber, and fld4 is the user. I need to know the count of all the records for user am without the sub number getting in the way. For example, the first record for user am shows in the 3rd year number 1 with two sub records was for user am . That needs to be counted as one item. So when all the items are counted I should have a total of 5 items for user am and not 17 like you'd normally get Any thoughts? thanks +--+--+--+--+ | fld1 | fld2 | fld3 | fld4 | +--+--+--+--+ | 3 | 1 | a | am | | 3 | 1 | b | am | | 3 | 2 | a | am | | 3 | 3 | a | pm | | 3 | 3 | b | pm | | 3 | 3 | c | pm | | 4 | 1 | a | pm | | 4 | 2 | a | pm | | 4 | 3 | a | am | | 4 | 3 | b | am | | 4 | 3 | c | am | | 4 | 3 | d | am | | 4 | 3 | e | am | | 4 | 3 | f | am | | 4 | 4 | a | am | | 4 | 4 | b | am | | 5 | 1 | a | pm | | 5 | 1 | b | pm | | 5 | 1 | c | pm | | 5 | 1 | d | pm | | 5 | 1 | e | pm | | 5 | 2 | a | am | | 5 | 2 | b | am | | 5 | 2 | c | am | | 5 | 2 | d | am | | 5 | 2 | e | am | | 5 | 2 | f | am | +--+--+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple Count Query
Oops, that should be: select count(distinct fld1, fld2) from yourtable where fld4 = 'am'; That version of the query is tested and works with your data; it returns the fact that there are 5 combinations of fld1 and fld2 for all the rows where fld4 is 'am'. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Ed Reed [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, September 09, 2005 6:26 PM Subject: Re: Simple Count Query I think you want this or something similar: select count(distinct fld2) from yourtable where fld4 = 'am'; N.B. I have not tested this solution. Rhino - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 09, 2005 6:08 PM Subject: Simple Count Query I know this has to be a simple query but its really kickin' my butt. I have the table below where fld1 is the year, fld2 is a number, fld 3 is a subnumber, and fld4 is the user. I need to know the count of all the records for user am without the sub number getting in the way. For example, the first record for user am shows in the 3rd year number 1 with two sub records was for user am . That needs to be counted as one item. So when all the items are counted I should have a total of 5 items for user am and not 17 like you'd normally get Any thoughts? thanks +--+--+--+--+ | fld1 | fld2 | fld3 | fld4 | +--+--+--+--+ | 3 | 1 | a | am | | 3 | 1 | b | am | | 3 | 2 | a | am | | 3 | 3 | a | pm | | 3 | 3 | b | pm | | 3 | 3 | c | pm | | 4 | 1 | a | pm | | 4 | 2 | a | pm | | 4 | 3 | a | am | | 4 | 3 | b | am | | 4 | 3 | c | am | | 4 | 3 | d | am | | 4 | 3 | e | am | | 4 | 3 | f | am | | 4 | 4 | a | am | | 4 | 4 | b | am | | 5 | 1 | a | pm | | 5 | 1 | b | pm | | 5 | 1 | c | pm | | 5 | 1 | d | pm | | 5 | 1 | e | pm | | 5 | 2 | a | am | | 5 | 2 | b | am | | 5 | 2 | c | am | | 5 | 2 | d | am | | 5 | 2 | e | am | | 5 | 2 | f | am | +--+--+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timezone query similiar to pgsql
Hi List, Does MySQL have something similiar to SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Canada/Pacific'; I need to handle the timestamp in the database. Previous posts and some googling suggests it should be in the PHP layer which is not an option for me. My users come from various timezones, and so I plan to store everything in GMT (server time) and select the time based on their timezone preference which is stored in a user preference table. Suggestions always welcome :) Thanks Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timezone query similiar to pgsql
In the last episode (Sep 10), Terence said: Does MySQL have something similiar to SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Canada/Pacific'; I need to handle the timestamp in the database. Previous posts and some googling suggests it should be in the PHP layer which is not an option for me. My users come from various timezones, and so I plan to store everything in GMT (server time) and select the time based on their timezone preference which is stored in a user preference table. Start at http://dev.mysql.com/doc/mysql/en/time-zone-support.html and http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html (especially CONVERT_TZ() ). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
