Re: MySQL Guru Needed!!
If you use ProvenScaling - ask for Jeremy. Time and time again is he a great resource and he is a great speaker at the MySQL Users Conferences. J.R. Bullington, CMA From: Ewen Fortune ewen.fort...@gmail.com Sent: Friday, December 12, 2008 2:26 PM To: bruce bedoug...@earthlink.net Subject: Re: MySQL Guru Needed!! As an alternative you also have www.percona.com www.pythian.com www.openquery.com.au www.provenscaling.com Percona's minimum billing unit is 15 minutes, not sure about the rest On Dec 12, 2008, at 17:35, bruce wrote: Hi. I've got a situation where I need to reach out/talk to a mysql guru every now and then. For the most part, the questions are probably 5-10 minutes for the right person, but they might take me hours/days to cobble together a good solution. (I'm not a mysql guru!!) As an example, I have a situation now where I've been trying to figure out a solution for a day now... I'm looking for someone that I can talk to periodically if I have questions. I'm willing to drop something in a paypal acct for this function. Posting to the email list, or the IRC chat isn't always expedient for my needs. Thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bull...@innovatim.com
re: I need to add to content somehow
Use CONCAT().
SELECT CONCAT('/',your_field,'/') 'newData' FROM your_table.
Then use similar logic to do an UPDATE statement, i.e. UPDATE your_table SET
your_field = CONCAT('/',your_field,'/')
You need to first make sure that the field you are trying to update is a
VARCHAR, because as it stands, it looks as though you have that field as an INT.
J.R.
From: Matthew Stuart [EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 12:55 PM
To: MySQL email support mysql@lists.mysql.com
Subject: I need to add to content somehow
I have a DB that has a field in it that currently just holds single
or double numbers - these numbers are basically a reference to a
category in which the particular record should be displayed. However
I have now been asked if I can make it so that a particular record
can be displayed in more than one category.
All I need to do is somehow ask the field to add a forward slash to
the front and end of the data, so the data will go from this:
1
33
21
9
11
to this:
/1/
/33/
/21/
/9/
/11/
How do I get MySQL to do this? I guess I might have to do it in two
steps by firstly adding the slash to the front and then lastly to the
back. But I have no idea on how to do it. My reason for doing this is
so that I get the webpage to look for numbers that are between the
slashes. this will enable me to have numbers like this: /1/33/9/
enabling me to have a record in more than one category.
Any help would be appreciated.
Thanks
Mat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: DIAGNOSTIC MYSQL
You can also try the MySQL Monitoring Service. It's a great tool that looks at your server's health and tells you if any changes need to be made. It's especially helpful in tuning your server variables, query cache, and index usage. It also sends mail if you need / want it. From: Krishna Chandra Prajapati [EMAIL PROTECTED] Sent: Thursday, September 13, 2007 10:25 AM To: MySql mysql@lists.mysql.com Subject: DIAGNOSTIC MYSQL Hi All, Is there is any script or tool that can diagnostic the mysql. It can check for the errors, give suggestion, give reports and send emails. It can check the mysql system variables and give suggestion. Thanks, Prajapati
Best Practice - Encryption
Hi MySQL'ers -- I run multiple HIPAA compliant databases and a lot of very secure patient information in my MySQL databases. However, I am a bit of a security phreak and want to go a step further and encode the data INSIDE the database so that, in the very unlikely event that someone can bum-rush my security guards, break past the bullet-proof door walls, and rip out my hard drives through the locked rack cabinet, I want to ensure my data is safe. Also, I want to make it so that you cannot even LOOK at the data unless you know the correct encryption keys. I currently employ AES encryption for keeping passwords and such, and have been very successful in doing so. However, I have run into a snag and I am hoping that you all can suggest ways to rectify this or provide better ideas than I currently employ. Quick Rundown: MySQL 5.0.38 running on Debian Linux, Kernel 2.6, fully up-to-date. Server Side Language - ASP (yes, I know, don't say anything) Web Server - IIS 6.0, Windows Server 2003 SP2. ODBC - MyODBC 3.15.17 5 different firewalls (3x Linux (technically 1 firewall, 3 chains), 1 Windows, 1 hardware router firewall). 1024-bit SSL encryption from client to Web app, 256-bit SSL from Web app to MySQL (and the server will kick you if you are not using SSL). Here are the questions at hand: 1) When using any encryption method, I continuously get a Error Nr 2014: Commands out of sync. I have checked the manual, but it does not provide any helpful information, as I can be connecting only through the CLI on my Linux box and it gives me the same error. MySQL Query Browser also supplies said error. Question: What exactly are the correct command sequences so as I can rectify this error, and why do I get it from 3 different clients, even when I am the only one connected? 2) When using any encryption method, I can successfully ENCODE, DECODE, AES_ENCRYPT, and AES_DECRYPT all of the necessary data, however, the Query Browser (and hence, my app) cannot display the data as it seems as though the column length is NULL, even though you can expand the column and see the data. The CLI reports the data correctly. The fields in question are LONGTEXT. Is this a case where encoding or encryption is not worth the CPU clocks, or is the field just too long to encode? Should I consider moving these to VARCHAR() instead of LONGTEXT? 3) We do research-based data mining on these records, and of course, encryption will hinder this process. Would setting up temp tables with the unencrypted data be the best course of action for doing the research that is necessary? Thank you for your time and I look forward to discussing these issues with you. J.R.
RE: 2007 MySQL Community Survey - Got Ten Minutes to Spare?
I received the same as David. The question was: What OS do you currently use? Please check all that apply: (page 3, question 4 or 5): I chose Linux 2.6.x and Windows. It kept telling me that I needed to answer the question. I changed the answer to just Linux and it let me thru. Just to provide a little more info for you. J.R. From: [EMAIL PROTECTED] Sent: Tuesday, August 07, 2007 9:35 AM To: 'Jay Pipes' [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: 2007 MySQL Community Survey - Got Ten Minutes to Spare? Having a moment of altruism, I started doing the survey only to find that it wouldn't let me advance to the next page (from either page 1 or page 2, can't recall). I kept getting an error of an answer is required of this question even when I had provided one. No good deed goes unpunished perhaps... David So I was gonna take this survey (I don't need or care about the book, just wanted to help you out) and honestly, it's more like a quiz -- needless to say I didn't do it. :-| -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Friday, August 03, 2007 2:33 PM To: mysql@lists.mysql.com Subject: 2007 MySQL Community Survey - Got Ten Minutes to Spare? 2007 MySQL Community Survey - Bribe Included I've created a survey on SurveyMonkey that I am hoping to get a bunch of responses for. The survey will help the community team identify how (in)effectively we communicate development and other goals and also what features you, our community users, most want in future versions of MySQL. So, hey, give us ten minutes of your time and help us make MySQL better. A Blatant Bribe for Participating And for those who need a bribe, we'll be giving away two Apress books (each) to 3 random survey takers. The survey is anonymous, but if you would like to go into the drawing for the books, just include your email address in the very last question...otherwise, just leave it blank. Link to the 2007 Community survey: http://www.surveymonkey.com/s.aspx?sm=ONCdTFPxjyq55NAUBUJ_2fvQ_3d_3d Thanks much! Jay Pipes Community Relations Manager, North America MySQL, Inc.
Re: off-topic unsubscribe concern
Same here, although they have been a little less frequent lately. From: B. Keith Murphy [EMAIL PROTECTED] Sent: Friday, July 06, 2007 3:50 PM To: Michael Dykman [EMAIL PROTECTED] Subject: Re: off-topic unsubscribe concern I have been having the same problems. Have no idea what is up. - Original Message - From: Michael Dykman To: MySQL General Sent: Friday, July 6, 2007 11:55:41 AM (GMT-0500) America/New_York Subject: off-topic unsubscribe concern Hi everyone. I have been on this list for a pretty long time but in the last few months I have started to receive random 'confirm unsubscribe' messages.. They always seem to originate from a Roadrunner IP (I have not thoroughly tested that hypothesis). I have no accounts on or near roadrunner, so I doubt I am inadvertantly kicking these off, which was my first theory. Is anyone else suffering from this or is it just me? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
re: Type Mismatch
This is an ASP error, not a MySQL error.
However, try doing a
response.write rs(Score)
response.flush
Then you will see why you are getting the mismatch error. It is probably the
fact that rs(Score) is not returning an integer or number of any kind (i.e.
if rs(score) is null).
HTH!
From: Critters [EMAIL PROTECTED]
Sent: Tuesday, June 19, 2007 7:44 AM
To: MySQL General mysql@lists.mysql.com
Subject: Type Mismatch
Hi,
I hope someone can help me with my problem, something that has come up
when moving code and DB to a new server:
Connection:
driver={MySQL ODBC 3.51
DRIVER};server=localhost;uid=xx;pwd=xx;database=xx;option=16387
SQL:
SELECT (sum_score/sum_votes) AS 'score' FROM xx WHERE id =
xx
Value of score:
6.2153
ASP:
%=int(RS(score)*25)-20%
Error:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
Any help appreciated, I did not have this problem when I had the same
set-up but on a server running an earlier version of MySQL and the ODBC
driver.
--
David Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type Mismatch
It's not an error. MySQL Cast will do the 'rounding' for you.
The acutal case is that the 64-bit integer floating value is not affected by
the CAST.
See the manual page for CAST:
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
If you would prefer to do the rounding in ASP, push the result into a temporary
variable, then do a CDbl on the variable. Yes, it's a slightly extra step, but
it will ensure that your data stays in the correct format.
J.R.
From: Critters [EMAIL PROTECTED]
Sent: Tuesday, June 19, 2007 8:37 AM
To: [EMAIL PROTECTED]
Subject: Re: Type Mismatch
Thanks for responding.
If I just response.write score I get 6.5714
I got it working by doing this: cast(sum_score/sum_votes as signed) AS
'score'
Which returns 7. So it is a MySQL error? I would prefer to do the
rounding in ASP and not have to update other scripts giving the same
problems.
--
Dave
J.R. Bullington wrote:
This is an ASP error, not a MySQL error.
However, try doing a
response.write rs(Score)
response.flush
Then you will see why you are getting the mismatch error. It is probably the
fact that rs(Score) is not returning an integer or number of any kind (i.e.
if rs(score) is null).
HTH!
From: Critters
Sent: Tuesday, June 19, 2007 7:44 AM
To: MySQL General
Subject: Type Mismatch
Hi,
I hope someone can help me with my problem, something that has come up
when moving code and DB to a new server:
Connection:
driver={MySQL ODBC 3.51
DRIVER};server=localhost;uid=xx;pwd=xx;database=xx;option=16387
SQL:
SELECT (sum_score/sum_votes) AS 'score' FROM xx WHERE id =
xx
Value of score:
6.2153
ASP:
%=int(RS(score)*25)-20%
Error:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
Any help appreciated, I did not have this problem when I had the same
set-up but on a server running an earlier version of MySQL and the ODBC
driver.
--
David Scott
Re: how to get Number of rows matched?
Ace, I am sorry to get into this so late, but you didn't mention what version you are running. If you are running 5.0.1 or greater, you can use the MySQL function ROW_COUNT(). You will find that it will help you in returning the results that you need. Here's the page in the manual for your review: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count HTH! J.R. From: Ace [EMAIL PROTECTED] Sent: Tuesday, June 12, 2007 1:07 AM To: Michael Dykman [EMAIL PROTECTED] Subject: Re: how to get Number of rows matched? Thanks All for your help! If someone from MySQL team is looking at this mail thread, we request to include this feature in future release. Cheers, Rajan On 6/11/07, Michael Dykman wrote: no, there is nothing else. There are cleaner interfaces to this information but, for PHP. the string returned by mysql_info() is all you get. The format of that string is very regular and we have been using it in production software for well over a year now with no issues. - michael On 6/11/07, Ace wrote: Yes, you are right! mysql_info() is not most useful. It does give me number of rows matched but will involve complications of parsing the string. Is there no other way to this? How can this be missed? I am not so convinienced on mysql_info()! On 6/11/07, Jerry Schwartz wrote: Have you looked at mysql_info()? The format of the return value might not be the most useful, but it should give you what you need. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: ViSolve DB Team [mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2007 6:15 AM To: Ace; mysql@lists.mysql.com Subject: Re: how to get Number of rows matched? Hi AFAIK, before changing data, the old values are saved in the rollback segment. On saving the updated values, from the Buffer to the rollback segment/data files, --- it checks if there is any matched row that matches the condition. If found, then flags Matched. ---after filtering out the matched row, it check whether there is need to change the old value to new value. if need then flags Changed and rewrite the same in the datafile/rollback segment. Thanks ViSolve DB Team. - Original Message - From: Ace To: Sent: Monday, June 11, 2007 11:41 AM Subject: how to get Number of rows matched? Hi Experts, When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using mysql_affected_rows(), but is there any way to get the number of rows matched? I want to find out, when rows updated = 0, if there were no updates because the row wasn't found (rows matched will = 0) or because the update would not have changed any data (rows matched = 1). mysql select * from test; +--+--+ | roll | s| +--+--+ |1 | new | +--+--+ 1 row in set (0.00 sec) mysql update test set roll = 1, s = 'new' where roll = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'new' where roll = 17; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'neww' where roll = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- Cheers, Rajan -- -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.13/843 - Release Date: 6/10/2007 1:39 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful.
Re: maximum number of records in a table
Olaf is right. It is really more about query execution time, and more importantly, QUERY OPTIMIZATION. Depending on how you setup your keys, your table type, and what else your server does, you should be able to run multiple queries on this table without too much of an issue. 2 BIG suggestions -- 1) Whatever query you want to run on this table, run EXPLAIN. Then study the results and do your optimization and key creation. 2) QUERY_CACHE. This is where you are going to live or die. Since you said you will be doing a lot of SELECTs and not-so-many INSERTs or UPDATEs, the QUERY_CACHE is going to help out a lot here. HTH! J.R. From: Olaf Stein [EMAIL PROTECTED] Sent: Tuesday, June 12, 2007 8:13 AM To: [EMAIL PROTECTED], David T. Ashley [EMAIL PROTECTED] Subject: Re: maximum number of records in a table I guess a lot of that depends what an acceptable query execution time for you is. Also, what else does the machine do, are there other databases or tables that are queried at the same time, do you have to join other tables in for your queries, etc? Olaf On 6/12/07 3:24 AM, kalin mintchev wrote: hi david.. thanks... i've done this many times and yes either trough php, perl, python or on the mysql cl client. but my question here is not about doing it and insert times it's more about hosting it and query times. i currently have a working table for the same purpose with about 1.5 million records in it. and the thing runs smoot on a machine that is 4 years old with 1 gig of ram and 2.8 ghz ghz processor. the thing is that now i'm talking about this x 100 times. more or less. i'm not worried about the insert times - this happens only ones and for a million entries, depending on what technic is used, it takes no longer than a few minutes. what i was asking basically was somebody to share experience with running a server with that amount of records in one table. currently the table i have has a size of 65 mgb which by 100 is about 6600 mgb or 6.6 gigs. which means that i have to have about 8 gigs of ram to successfully use a table like that. either that or cluster 2 machines with 4 gigs each and split the table. does this sound reasonable? is my logic flawed somehow? i'll appreciate any comments on this subject thanks... On 6/11/07, kalin mintchev wrote: hi all... from http://dev.mysql.com/doc/refman/5.0/en/features.html: Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows. that's cool but i assume this is distributed over a few machines... we have a new client that needs a table with 99 000 000 rows, 2 -3 columns. i was just wondering if i have a two dual core 2 processors in a machine with 4 gigs of ram - is that enough to host and serve queries from a table of this size? a few tables on the same machine? more than one machine? what are the query times like? can somebody please share some/any experience s/he has/had with managing databases/tables with that amount of records. i'd really appreciate it... 99 million isn't that large of a number. If you key the database properly, search times should be very modest. I can't speak for insert times, though, especially when keys are involved. This kind of thing is easy enough to do in your favorite scripting language. I would just create a table with a few keys and just for($i=0; $i9900; $i++) it with random numbers. If you have PHP on your system, here is some PHP code (runnable from the command line) that you should be able to hack down. It should answer your immediate questions about which PHP statements to use (if you've never done this from PHP before): http://gpl.e3ft.com/vcvsgpl01/viewcvs.cgi/gpl01/webprojs/fboprime/sw/standalo ne/dbtestpop.php?rev=1.31content-type=text/vnd.viewcvs-markup http://gpl.e3ft.com/vcvsgpl01/viewcvs.cgi/gpl01/webprojs/fboprime/sw/phplib/u srs.inc?rev=1.11content-type=text/vnd.viewcvs-markup Near the end of it, especially if the software writes output, you should get an intuitive feel for how long each INSERT is taking. You can even do test queries using the barebones MySQL client ... you should see interactively how long a query takes. I would ALMOST do this for you, but it is just beyond the threshold of what I'd do because I'm bored and watching TV. I'm just a little curious myself. I've never messed with a table about 10,000 rows or so. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum number of records in a table
If you are talking about a Dual Core computer with 4 GBs of RAM and at least 6GB of swap space, you should have no problems running it on one computer. MySQL is really robust and can handle quite a load, esp. on Linux. If you are running Windows, just remember to remove all the processes that you don't need to give MySQL the most memory and availability. Depending on the size of your queries and the results of said queries, you may be able to keep most of them in the query cache (QC). Since you said there were only 2-3 columns in the table, and said columns only have integers (for example), you could keep quite a bit of information in the QC. However, of course, if they are all longtext, you will run into a lot of disk swapping. To quote Jay Pipes at the MySQL Users Conference, the default answer to your IT question is IT DEPENDS. I know for a fact that I was using commodity hardware (Intel Celeron D 2.0 GHz, 1GB RAM) and was able to run heavy statistical analyses (MIN, MAX, STDEV, AVG, MEAN, etc) on 130M records with 50+ columns in 1 table and that was returning the data in 80 seconds. Not bad for that old of a computer. Nowadays it's a Quad Xeon 2.5GHz with 4GB RAM and the same query on 400M+ records returns in less than 15 seconds. It's all about query optimization and the query cache (or key buffer length, if you use InnoDB). J.R. From: kalin mintchev [EMAIL PROTECTED] Sent: Tuesday, June 12, 2007 11:20 AM To: [EMAIL PROTECTED] Subject: Re: maximum number of records in a table thanks j.r... Olaf is right. It is really more about query execution time, and more importantly, QUERY OPTIMIZATION. Depending on how you setup your keys, your table type, and what else your server does, you should be able to run multiple queries on this table without too much of an issue. are you suggesting that ONE machine can handle that load without problems? 2 BIG suggestions -- 1) Whatever query you want to run on this table, run EXPLAIN. Then study the results and do your optimization and key creation. ok... 2) QUERY_CACHE. This is where you are going to live or die. Since you said you will be doing a lot of SELECTs and not-so-many INSERTs or UPDATEs, the QUERY_CACHE is going to help out a lot here. well. not sure about this since basically the queries will be different every time - i mean the query itself is the same but the looked up values are 99 million different ones. i'll be running some tests and will report... might be helpful to somebody else... HTH! me too.. thanks. J.R. From: Olaf Stein Sent: Tuesday, June 12, 2007 8:13 AM To: [EMAIL PROTECTED], David T. Ashley Subject: Re: maximum number of records in a table I guess a lot of that depends what an acceptable query execution time for you is. Also, what else does the machine do, are there other databases or tables that are queried at the same time, do you have to join other tables in for your queries, etc? Olaf On 6/12/07 3:24 AM, kalin mintchev wrote: hi david.. thanks... i've done this many times and yes either trough php, perl, python or on the mysql cl client. but my question here is not about doing it and insert times it's more about hosting it and query times. i currently have a working table for the same purpose with about 1.5 million records in it. and the thing runs smoot on a machine that is 4 years old with 1 gig of ram and 2.8 ghz ghz processor. the thing is that now i'm talking about this x 100 times. more or less. i'm not worried about the insert times - this happens only ones and for a million entries, depending on what technic is used, it takes no longer than a few minutes. what i was asking basically was somebody to share experience with running a server with that amount of records in one table. currently the table i have has a size of 65 mgb which by 100 is about 6600 mgb or 6.6 gigs. which means that i have to have about 8 gigs of ram to successfully use a table like that. either that or cluster 2 machines with 4 gigs each and split the table. does this sound reasonable? is my logic flawed somehow? i'll appreciate any comments on this subject thanks... On 6/11/07, kalin mintchev wrote: hi all... from http://dev.mysql.com/doc/refman/5.0/en/features.html: Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows. that's cool but i assume this is distributed over a few machines... we have a new client that needs a table with 99 000 000 rows, 2 -3 columns. i was just wondering if i have a two dual core 2 processors in a machine with 4 gigs of ram - is that enough to host and serve queries from a table of this
re: Administrative Tools
MySQL GUI tools is one of the best out there, and it's specifically designed for MySQL. MySQL Administrator allows you to control the server. MySQL Query Browser lets you create and edit tables, indecies, etc MySQL Migration Tool helps you migrate from other popular RDBs to MySQL. http://dev.mysql.com/downloads HTH! From: [EMAIL PROTECTED] Sent: Monday, June 04, 2007 2:18 PM To: mysql@lists.mysql.com Subject: Administrative Tools Hello I am looking for the tools that I would use to be able to do administrative duties and to be able to create and update databases (tables, indexes and so forth). I would rather do this than do it via a command line. I would appreciate any links available. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Documentation
You can download the MySQL manual from their website in PDF, ZIP, and HTML sources. http://dev.mysql.com/doc/index.html From: [EMAIL PROTECTED] Sent: Monday, June 04, 2007 3:17 PM To: mysql@lists.mysql.com Subject: Documentation Hello I am going to be going on vacation and will be bringing my laptop with mysql installed on it. Is there a source of good documentation that can be downloaded (.doc or .pdf) for mysql? Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
Here's a question that begs to be asked -- When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump your data and then re-import? MySQL 5.x's query cache and table optimizers work very differently than in 4.1, so the results you are getting are probably from 2 issues: 1) You didn't dump your data first, and only did an inplace upgrade, and; 2) Your system isn't properly optimized for using the query cache. Try using either mysqldump or mysql-administrator to dump out your data to an .sql file. Then re-import all of your data into 5.x. You will see a significant difference in your query speeds once you do this. As to your query cache, make sure that it's on (on by default) and, based on your tables, either your MyISAM key_buffer_size or your InnoDB buffer_pool_size are correct for your server's amount of RAM. HTH! J.R. From: Gmail User [EMAIL PROTECTED] Sent: Tuesday, May 22, 2007 2:30 AM To: mysql@lists.mysql.com Subject: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks! I had perfectly working complex queries both with LEFT JOIN and without and they were returning results in under a second. After upgrade to 5.0.x, the same queries would return results in 20-30 second range. Through trial and error, I discovered that in case of SELECT ... FROM table1, table2 ... ORDER BY table2.column will be very slow (10s of seconds), while the same query ordered by table1.column will be in under 1 second. If I switch the order of tables in FROM, the same will hold true in reverse order. Is this a bug or I missed something in my googling? More importantly, what can I do change this--I need those sorts! :-( I got same results with 5.0.22, 5.0.27 (Linux). TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: append information
CONCAT_WS() works as well, but the format is still the same. You still want to
add your new data to your old data.
UPDATE tmpTable SET Textarea = CONCAT_WS(',',Textarea,'data you wish to add')
WHERE ID = someID
Just remember to make the delimiter something that you wouldn't normally see in
a textfield so that you can differentiate between the new text and the old text.
J.R.
From: sam rumaizan [EMAIL PROTECTED]
Sent: Friday, April 06, 2007 12:31 PM
To: [EMAIL PROTECTED]
Subject: re: append information
How About CONCAT_WS()
J.R. Bullington [EMAIL PROTECTED] wrote:
Use the CONCAT() function to achieve this:
UPDATE tmpTable SET Textarea = concat(Textarea,'data you wish to add') WHERE ID
= someID
HTH!
From: sam rumaizan
Sent: Thursday, April 05, 2007 12:23 PM
To: mysql@lists.mysql.com
Subject: append information
I have a question if you don't mind.
The update statement will replace the existing information. What if I
have Textarea instead and I want to add information to the field
without erasing previous information. Basically append information
-
Looking for earth-friendly autos?
Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.
RE: append information
Sam --
Please use REPLY-ALL when sending email to the list.
Can you use \t? I don't think so.
I know that if you want to enter in '\t', you will need to escape it '\\t',
but MySQL may treat that as something else. It may treat it as a TAB in its
own right, as I know that it will replace '\\r' with a Carriage Return in
BLOB and TEXT fields.
You should use something like pipe '|', brace '[', or bracket '{' and then
use code to do a replace().
You should try it though, as I have not. It may work just fine, but your
application code is really the way you want to do this.
J.R.
1-Can I use Tab (\t) instead?
2- Can I insert the date this new data was added to the field?
J.R. Bullington [EMAIL PROTECTED] wrote:
CONCAT_WS() works as well, but the format is still the same. You still want
to add your new data to your old data.
UPDATE tmpTable SET Textarea = CONCAT_WS(',',Textarea,'data you wish to
add') WHERE ID = someID
Just remember to make the delimiter something that you wouldn't normally see
in a textfield so that you can differentiate between the new text and the
old text.
J.R.
From: sam rumaizan
Sent: Friday, April 06, 2007 12:31 PM
To: [EMAIL PROTECTED]
Subject: re: append information
How About CONCAT_WS()
J.R. Bullington wrote:
Use the CONCAT() function to achieve this:
UPDATE tmpTable SET Textarea = concat(Textarea,'data you wish to add') WHERE
ID = someID
HTH!
From: sam rumaizan
Sent: Thursday, April 05, 2007 12:23 PM
To: mysql@lists.mysql.com
Subject: append information
I have a question if you don't mind.
The update statement will replace the existing information. What if I
have Textarea instead and I want to add information to the field
without erasing previous information. Basically append information
-
Looking for earth-friendly autos?
Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.
_
Looking for earth-friendly autos?
Browse
http://autos.yahoo.com/green_center/;_ylc=X3oDMTE4MGw4Z2hlBF9TAzk3MTA3MDc2B
HNlYwNtYWlsdGFncwRzbGsDZ3JlZW5jZW50ZXI- Top Cars by Green Rating at
Yahoo! Autos' Green Center.
re: append information
Use the CONCAT() function to achieve this: UPDATE tmpTable SET Textarea = concat(Textarea,'data you wish to add') WHERE ID = someID HTH! From: sam rumaizan [EMAIL PROTECTED] Sent: Thursday, April 05, 2007 12:23 PM To: mysql@lists.mysql.com Subject: append information I have a question if you don't mind. The update statement will replace the existing information. What if I have Textarea instead and I want to add information to the field without erasing previous information. Basically append information - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
re: Counting Row Results inside the select
This may sound mundane, but why not use a subselect COUNT?
SELECT COUNT(
select concat('Test row ',currentreturnrownum()) as testdata ,
mytestdata from mytestdatatable;) FROM mytestdatatable;
(I can't test this as I don't have currentreturnrownum() and cannot find this
function in v.5).
You may also want to try the SQL_CALC_FOUND_ROWS and then FOUND_ROWS() to get
the result.
SELECT SQL_CALC_FOUND_ROWS,
concat('Test row ',currentreturnrownum()) as testdata ,
mytestdata from mytestdatatable;
SELECT FOUND_ROWS();
J.R.
From: [EMAIL PROTECTED]
Sent: Wednesday, March 28, 2007 10:46 AM
To: mysql@lists.mysql.com
Subject: Counting Row Results inside the select
Has anyone found a way to get a result set with the current row return
number in a select statement without using vars?
select concat('Test row ',currentreturnrownum()) as testdata , mytestdata
from mytestdatatable;
++---+
|Test row 1 | testdata |
|Test row 2 | testdata |
|Test row 3 | testdata |
|Test row 4 | testdata |
|Test row 5 | testdata |
|Test row 6 | testdata |
|Test row 7 | testdata |
|Test row 8 | testdata |
|Test row 9 | testdata |
++---+
(9 rows returned)
Wishing you the best you know you deserve,
__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ 08405
Phone 609.485.5401
re: Does MySQL require patch for Daylight Savings Time 2007 change
As far as I know, MySQL does not need a TZ patch (unless you use specific/custom Time Zone information), but your OS does. The best way to check is to run: mysql SHOW VARIABLES LIKE 'TIME_ZONE'; If it says SYSTEM, then you need only patch your OS. (Patching the OS is [OT] for this list). Here's the section from the manual in regards to 3.23 Time Zones. http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html J.R. From: Causevic, Dzenan [EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:29 AM To: mysql@lists.mysql.com Subject: Does MySQL require patch for Daylight Savings Time 2007 change I am running Apache HTTP 1.3 with PHP 4.2.2 and MySQL 3.23. Do I need to apply any kind of patches to my MySQL related to Daylight Savings Time 2007 change? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited.
re: Fulltext problem
Hi Devy --
There are a couple of issues with your query below, and hopefully we can help
you figure it out.
First off, your table and query structure are fine. However, one can ask why
not use a TINYTEXT or even a TEXT field instead of VARCHAR(255). It's all in
the memory overhead. =)
Moving on, it's not that you don't have a relevance, it's that you don't have
enough records in your database to compare the MATCH() to. Read
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html for more
information, but pay particular attention to the last 4 paragraphs.
Your search text must match at most 49% of the rows in the database, or a 0
relevancy will appear. Since, in your test case, it matches 100%, you won't get
a relevance. Try adding 5-6 more records to your table and then do a search
with MySQL in the field1.
Try this:
CREATE TABLE as below.
INSERT INTO ft_test (field1,field2,field3) VALUES
('mysql full text', 'this is a test', 'mysql fulltext'),
('Email Tutorial','DBMS stands for DataBase ...','hi mom'),
('How To Use Yahoo Well','After you went through a ...','hi dad'),
('Optimizing your databases','In this tutorial we will show ...','hi sis'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...','hi bro'),
('MSSQL vs. YourSQL','In the following database comparison ...','hi uncle'),
('SQL Injection Security','When configured properly, MySQL ...','hi aunt');
Then, when you run your query, SELECT *, MATCH(field1) AGAINST('mysql') as
relevancy FROM ft_test; you get
id, field1, field2, field3, relevance
1, 'mysql full text', 'this is a test', 'mysql fulltext', 0.88573294878006
2, 'Email Tutorial', 'DBMS stands for DataBase ...', 'hi mom', 0
3, 'How To Use Yahoo Well', 'After you went through a ...', 'hi dad', 0
4, 'Optimizing your databases', 'In this tutorial we will show ...', 'hi sis', 0
5, '1001 MySQL Tricks', '1. Never run mysqld as root. 2. ...', 'hi bro',
0.88573294878006
6, 'MSSQL vs. YourSQL', 'In the following database comparison ...', 'hi uncle', 0
7, 'SQL Injection Security', 'When configured properly, MySQL ...', 'hi aunt',
0
(sorry about the spacing..., thanks to MySQL manual for the source of inserts)
Notice that in the last column, the relevance is 0.88 in two of the fields, as
MySQL is in those fields and no other.
Then you can try other search terms in other fields, such as SELECT *,
MATCH(field2) AGAINST('database') as relevancy FROM ft_test; . When looking at
the manual for the FULLTEXT searches, also make sure to pay attention to the
STOPWORDS section, IGNORED WORDS section, and the ft_min_word and ft_max_word
. The default minimum word length for FULLTEXT searches is 4, unless you change
it in the VARIABLES.
HTH!
J.R.
From: devy [EMAIL PROTECTED]
Sent: Thursday, January 04, 2007 8:54 AM
To: mysql@lists.mysql.com, [EMAIL PROTECTED]
Subject: Fulltext problem
Hi,
today I've experienced a problem that I don't understand and I can't solve!
I've created a table as follows:
---
CREATE TABLE `ft_test` (
`id` int(11) NOT NULL auto_increment,
`field1` varchar(255) NOT NULL ,
`field2` varchar(255) NOT NULL ,
`field3` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `field1` (`field1`),
FULLTEXT KEY `field2` (`field2`),
FULLTEXT KEY `field3` (`field3`),
FULLTEXT KEY `f1d2` (`field1`,`field2`)
) ENGINE=MyISAM CHARSET=utf8;
-
and then I've filled this table with the following statement:
-
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-
the problem is that when I execute this query I always get 0 as relevance:
select *, match(field1) against('mysql') as relevancy from ft_test;
id field1 field2 field3 relevancy
-- --- -- -- -
1 mysql full text this is a test mysql fulltext 0
I expected a value for relevancy! shouldn't I?
I tested with all other words and combination of fulltext index:
---
select *, match(field2) against('mysql') as relevancy from ft_test;
select *, match(field3) against('mysql') as relevancy from ft_test;
select *, match(field3) against('fulltext') as relevancy from ft_test;
---
but I always get 0!
This is a show variables of my mysql server
Variable_name Value
--
version 5.0.18-nt
ft_boolean_syntax + -()~*:|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
Any advice?
Thanks
RE: www.innodb.com
Depending on when Heikki and the InnoDB team moved the DNS records, it could take between 24 and 48 hours to propogate throughout the entire internet. I also see the cyber-squatter page, but I am sure that this will resolve itself shortly. J.R. From: George Law [EMAIL PROTECTED] Sent: Friday, November 10, 2006 7:50 AM To: Heikki Tuuri [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: www.innodb.com I'm seeing: innodb.com Here are some related websites for: innodb.com Sponsored Links MySQL database design Data modelling, architecture Terabyte database designs www.shoreview.com Easy Event Log Compliance Consolidate event logs to a DB. MsSQL, MySql Oracle are supported www.eventsentry.com SQL Server Monitoring Free White Paper: Discover what drives SQL server performance. www.heroix.com/SQL_monitoring MySQL Manager - Download Admin, Import/Export, Data Sync, Query Builder, Backup, Report! www.mysqlfrontend.com 100% Linux Native Backup Postgres, MySQL, AS400, DB2 Send your data securely offsite www.vaultlogix.com New Database Query Tool Easy-to-Use DB Data Management, Query Design Tool. Free Trial!
re: excel and Mysql?!
Always being the last to input, there are lots of other database tools out there that let you do this. One in particular is DBTools Professional (which is what I use). You can ADO IMPORT Excel, MS-Access, and others like FoxPro and PostgreSQL. Another is Database Workbench, which one of the guys on this list works on. However, if free is the way to go, then MyODBC and the format below (which I will start using now =) ) are the ways to do it. My 0.02... J.R. cheap is good, free is better From: Jerry Schwartz [EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 10:17 AM To: 'Roberto William Aranda-W Roman' [EMAIL PROTECTED], 'MySQL List' mysql@lists.mysql.com Subject: RE: excel and Mysql?! As usual, Dan's suggestion is better than mine, especially for large amounts of data (I usually work with perhaps 100 rows, at most). When you save a spreadsheet as a text file, the columns will be delimited by tabs by default. Also, if you go with my suggestion you should probably surround any text values with an extra pair of single quotes: ... SET col1 = ', 'Sheet1'!A1, ', ...) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 8:37 AM To: MySQL List Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using replace on columns containing *
Part of the problem is that you have a ' ' in between your REPLACE and your (). The REPLACE function needs to have that space removed. SELECT REPLACE(deviceID,'*','.*') FROM MY_TABLE; Try that and see if it helps. J.R. -Original Message- From: Eitan Gur [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 27, 2006 9:07 AM To: mysql@lists.mysql.com Subject: Using replace on columns containing * Hi all I have a column in a table containing strings with the '*' character. I'm trying to use the REPLACE command on this column: SELECT REPLACE (deviceId, '*', '.*') FROM MY_TABLE; But I get the following error: ERROR 1270 (HY000): Illegal mix of collations (utf8_bin,IMPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'replace' I use WindowsXP with MySQL version 4.1.7. How can I overcome this problem? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ASP Reporting EOF?
I used to have this problem, but I solved it by using 'IS NOT NULL' in my queries v. using if rs(fld.value) Here's something else from the MySQL manual... Don't know if this will help or not... *** ASP users: if you're getting empty recordset returned when using COALESCE, add OPTION=16384 to your connectionstring, or check Change Bigint to Int in the DSN manager! *** You could also try SELECT (IF ELSE) statements to force MySQL to output a '0' instead of a NULL value. Other than that, this would seem to be an ODBC or ASP issue, not a MySQL issue, as MySQL is reporting 215 rows correctly and ASP is not. I would be happy to help off-list if you need another set of eyes on the coding. J.R. -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 27, 2006 9:49 AM To: MySQL List Subject: Re: ASP Reporting EOF? I have found a bug report, #11541 which appears to be reporting the same thing that I am reporting. However, it doesn't indicate that it has been fixed. If it has not been, this is a serious flaw, and I will not be able to convert this particular application over to MySQL as it's critical that it work properly. Does anyone have any information on this? Thanks, Jesse - Original Message - From: Jesse [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, June 26, 2006 4:58 PM Subject: ASP Reporting EOF? When I run the following query in my ASP Application: SELECT S.State, S.Sub, S.Region, S.District, Sum(Males) AS TotMales, Sum(Females) AS TotFemales, Sum(AfricanAmerican) AS TotAfricanAmericans, Sum(Asian) AS TotAsians, Sum(Caucasian) AS TotCaucasians, Sum(Hispanic) AS TotHispanics, Sum(NativeAmerican) AS TotNativeAmericans, Sum(Other) AS TotOthers, Sum(Grade6) AS TotGrade6s, Sum(Grade7) AS TotGrade7s, Sum(Grade8) AS TotGrade8s, Sum(Grade9) AS TotGrade9s, Sum(Grade10) AS TotGrade10s, Sum(Grade11) AS TotGrade11s, Sum(Grade12) AS TotGrade12s, Sum(AgeBelow22) AS TotAgeBelow22s, Sum(Age22_25) AS TotAge22_25s, Sum(Age26_30) AS TotAge26_30s, Sum(Age31_40) AS TotAge31_40, Sum(AgeOver40) AS TotAgeOver40s, Sum(Disabilities) AS TotDisabilitiess, Sum(EducationallyDisabled) AS TotEducationallyDisableds, Sum(EconomicallyDisadvantaged) AS TotEconomicallyDisadvantageds, Sum(LimitedEnglishProficiency) AS TotLimitedEnglishProficiencys, Sum(NonTraditional) AS TotNonTraditionals FROM Chapters C, Schools S WHERE C.SchoolID=S.ID GROUP BY S.State, S.Sub, S.Region, S.District ORDER BY S.State, S.Sub, S.Region, S.District The dataset returns EOF, however, it's NOT EOF. I can execute this query in MySQL Query Browser, and it returns 215 rows. I've recently converted this table from InnoDB to MyISAM, which seems to be faster, but I can't figure out why it would say it was at EOF when it's not! I've got other queries that don't return EOF, why would this one? Thanks, Jesse -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ASP Reporting EOF?
When you run the query in the Query Browser, do any of the records return a 'NULL' value? If so, then MS's ASP engine would return an EOF because MySQL's NULL is NOT the same as objRS(fld.name) = . J.R. -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Monday, June 26, 2006 4:58 PM To: MySQL List Subject: ASP Reporting EOF? When I run the following query in my ASP Application: SELECT S.State, S.Sub, S.Region, S.District, Sum(Males) AS TotMales, Sum(Females) AS TotFemales, Sum(AfricanAmerican) AS TotAfricanAmericans, Sum(Asian) AS TotAsians, Sum(Caucasian) AS TotCaucasians, Sum(Hispanic) AS TotHispanics, Sum(NativeAmerican) AS TotNativeAmericans, Sum(Other) AS TotOthers, Sum(Grade6) AS TotGrade6s, Sum(Grade7) AS TotGrade7s, Sum(Grade8) AS TotGrade8s, Sum(Grade9) AS TotGrade9s, Sum(Grade10) AS TotGrade10s, Sum(Grade11) AS TotGrade11s, Sum(Grade12) AS TotGrade12s, Sum(AgeBelow22) AS TotAgeBelow22s, Sum(Age22_25) AS TotAge22_25s, Sum(Age26_30) AS TotAge26_30s, Sum(Age31_40) AS TotAge31_40, Sum(AgeOver40) AS TotAgeOver40s, Sum(Disabilities) AS TotDisabilitiess, Sum(EducationallyDisabled) AS TotEducationallyDisableds, Sum(EconomicallyDisadvantaged) AS TotEconomicallyDisadvantageds, Sum(LimitedEnglishProficiency) AS TotLimitedEnglishProficiencys, Sum(NonTraditional) AS TotNonTraditionals FROM Chapters C, Schools S WHERE C.SchoolID=S.ID GROUP BY S.State, S.Sub, S.Region, S.District ORDER BY S.State, S.Sub, S.Region, S.District The dataset returns EOF, however, it's NOT EOF. I can execute this query in MySQL Query Browser, and it returns 215 rows. I've recently converted this table from InnoDB to MyISAM, which seems to be faster, but I can't figure out why it would say it was at EOF when it's not! I've got other queries that don't return EOF, why would this one? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Leading zero where strlen 5
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
Of course, this will involve you changing the length() if the ZipCode has
only 3 digits.
Also, of course, try this with a LIMIT to make sure that this updates your
fields correctly. I know it works on mine...
J.R.'s $0.02
-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Monday, June 26, 2006 7:17 PM
To: mysql@lists.mysql.com
Subject: Leading zero where strlen 5
I need to update a column, if the string length is less than 5, I want to
add leading zeros to it until it has 5. These are zip codes, I think there
are no 00 leading zips, so most should all be four chars long.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
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: Copy database to a file
The command is mysqldump Here's the man file on it: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html -Original Message- From: Karl Larsen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 20, 2006 3:09 PM To: mysql@lists.mysql.com Subject: Copy database to a file I have been reading the Reference and saw how to convert my database tables and stuff to the words that made them and puts it into a file. But now I need it I can't find it. If you know how please send along how or a page(s) in the Reference. Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Sad, I know...
So, by using MacOS X, you are using a *nix based system. Everything posted to this list is usually based in code (as in WebApps) or by the MySQL command line. You need to learn how to use the command line (terminal.app) and SQL. There are lots of books out there, including on using and installing MySQL. Might be advantageous to buy one. Or you can read the free and wonderful manual provided by MySQL on their website. So, the command written by Daniel is used on the command line for accessing the database(s). To dump all your databases and uninstall MySQL, just stop the service terminal mysqladmin shutdown and then delete the installation directory. Then restart the installation. PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even if you only read the section on Installing MySQL (http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will help immensely with your original problems. Hope this helps! J.R. -Original Message- From: tomáz rezistänz [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 12:34 AM To: mysql@lists.mysql.com Subject: Re: Sad, I know... I wish I could uninstall mySQL and start over but I don't know how.. On 6/7/06, tomáz rezistänz [EMAIL PROTECTED] wrote: !! I apologize.. I should have mentioned that I am using a Mac in OSX 10.4.5 My main concern is should I begin logging into mSQL using the terminal or is this done from a browser??... Yes, I am know nothing.. no commands.. nada.. On 6/6/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 6/6/06, tomáz rezistänz [EMAIL PROTECTED] wrote: extreme snip I have no idea how to log in to mySQL... can you help? MySQL_dir/bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix like YMMV Run it with -u root -p and you can provide your password. That's the problem with wizards and howtos, you gotta KNOW what you're installing and how to deal with errors. I used to be able to log straight into phpmyadmin, but now I get this: Welcome to phpMyAdmin 2.8.1 # Probably reason of this is that you did not create configuration file. You might want to use setup script http://localhost/phpmyadmin/scripts/setup.php to create one. #1045 - Access denied for user 'root'@'localhost' (using password: NO) # It can't be more verbose than that... So, you do have MySQL installed, you've setup a password for it, and for some reason (probably a lack of a config file, as PHPMyAdmin told you) PHPMyAdmin tries to connect to mysql without a password, and its kicked in the butt by trying. You could try running the script it told you (setup.php) to let PHPMyAdmin know that you must provide a password to MySQL. You can also run the console application (mysql or mysql.exe ) using mysql -u root -p, provide your password and login, but that will not solve PHPMyAdmin, you MUST configure it. You can also login and reset your password, so PHPMyAdmin will log you in without a password and you can change your passwd from the Web Interface. You can also take a look at the MySQL Manual for a way to reset your password in case you can't login using console. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: ERROR 1064 (42000): You have an error in your SQL syntax;
You can't just change the name without changing (or stating) the type. ALTER TABLE actors CHANGE director_id actos_id varchar(96) default NULL; J.R. -Original Message- From: Mark Sargent [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 10:10 AM To: mysql@lists.mysql.com Subject: ERROR 1064 (42000): You have an error in your SQL syntax; Hi All, gee I really hate bugging you all for this. I looked at this page, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html which has this, To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as renaming it from |b| to |c|: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql ALTER TABLE actors CHANGE director_id actor_id; I get this, ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 Sorry, little confused right about now, eh. Cheers. Mark Sargent. -- 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: Sad, I know...
You really should learn some of the command line items before delving into the GUIs. MySQL has the Administrator and Query Browser for MacOS X. If you need to start with any GUI, these are probably the ones you should start with. It's a nice blend of command line and GUI tools. J.R. -Original Message- From: tomáz rezistänz [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 10:15 AM To: Daniel da Veiga Cc: mysql@lists.mysql.com Subject: Re: Sad, I know... I understans, but within phpmyadmin I was given the option to add passwords to 4 different accounts, including root. That's where I locked myself out, so that's where I tried getting back in.. On 6/7/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 6/7/06, tomáz rezistänz [EMAIL PROTECTED] wrote: OK, thanks for the guidance. I will try once more to make sense of the mySQL documentation. I thought I was going in the right direction when I clicked on setup script (below) and went to the security panel, but then I wasn't sure what to enter as far as blowfish question, socket, folder, etc, etc.. You started wrong by thinking PHPMyAdmin was necessary in the first place, or that it was a step to configure MySQL. The server can work alone, PHPMyAdmin is simply a frontend. You must learn how to use the console (mysql) and use SQL commands on it. After that, you can use any GUI for a faster access to commands you already know. -- Probably reason of this is that you did not create configuration file. You might want to use setup scripthttp://localhost/phpmyadmin/scripts/setup.phpto create one. Error *MySQL said: *[image: Documentation] http://dev.mysql.com/doc/refman/5.0/en/error-returns.html #1045 - Access denied for user 'root'@'localhost' (using password: NO) Open new phpMyAdmin window http://localhost/phpmyadmin/index.php?target=index.phplang=en-iso-885 9-1convcharset=iso-8859-1token=19846bf63e5f9bcd5a2eaf9e658d377c -- On 6/7/06, J.R. Bullington [EMAIL PROTECTED] wrote: So, by using MacOS X, you are using a *nix based system. Everything posted to this list is usually based in code (as in WebApps) or by the MySQL command line. You need to learn how to use the command line (terminal.app) and SQL. There are lots of books out there, including on using and installing MySQL. Might be advantageous to buy one. Or you can read the free and wonderful manual provided by MySQL on their website. So, the command written by Daniel is used on the command line for accessing the database(s). To dump all your databases and uninstall MySQL, just stop the service terminal mysqladmin shutdown and then delete the installation directory. Then restart the installation. PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even if you only read the section on Installing MySQL (http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will help immensely with your original problems. Hope this helps! J.R. -Original Message- From: tomáz rezistänz [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 12:34 AM To: mysql@lists.mysql.com Subject: Re: Sad, I know... I wish I could uninstall mySQL and start over but I don't know how.. On 6/7/06, tomáz rezistänz [EMAIL PROTECTED] wrote: !! I apologize.. I should have mentioned that I am using a Mac in OSX 10.4.5 My main concern is should I begin logging into mSQL using the terminal or is this done from a browser??... Yes, I am know nothing.. no commands.. nada.. On 6/6/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 6/6/06, tomáz rezistänz [EMAIL PROTECTED] wrote: extreme snip I have no idea how to log in to mySQL... can you help? MySQL_dir/bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix like YMMV Run it with -u root -p and you can provide your password. That's the problem with wizards and howtos, you gotta KNOW what you're installing and how to deal with errors. I used to be able to log straight into phpmyadmin, but now I get this: Welcome to phpMyAdmin 2.8.1 # Probably reason of this is that you did not create configuration file. You might want to use setup script http://localhost/phpmyadmin/scripts/setup.php to create one. #1045 - Access denied for user 'root'@'localhost' (using password: NO) # It can't be more verbose than that... So, you do have MySQL installed, you've setup a password
RE: Inserting ' into database
As a word of advice, before posting to any list, please RTFM (Read The Fine
Manual).
You have to escape all special characters, like apostrophes and single
quotes, with the backslash.
INSERT INTO thetable (name) VALUES ('O\'Connell');
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
J.R.
-Original Message-
From: Peter Lauri [mailto:[EMAIL PROTECTED]
Sent: Monday, May 15, 2006 1:37 AM
To: mysql@lists.mysql.com
Subject: Inserting ' into database
Hi all,
Assume that I want to insert Juanita O'Connell into my database. How do I
do that? The problem is the ' in her last name. If I just put it in it will
be
INSERT INTO thetable (name) VALUES ('O'Connell');
And that does not work :) How can I solve this?
Best regards,
Peter Lauri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
smime.p7s
Description: S/MIME cryptographic signature
RE: inserting server time into mysql
The now() function would be used in the INSERT/UPDATE statement, not the
form field.
$query = INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', now());
mysql_query($query) or die('Error, insert query failed');
See the change to your variable $signin. Change that to now() and then
remove the form field signin.
To make your life a lot easier, you really should use the TIMESTAMP field.
ALTER TABLE staffs MODIFY `signin` `signin` timestamp default
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
This will ensure that you have the server's timestamp, not the client's
(timezones are a b**ch sometimes...)
J.R.
-Original Message-
From: Alla-amin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 09, 2006 12:41 PM
To: mysql@lists.mysql.com
Subject: Re: inserting server time into mysql
Thanks for your help,
How can I capture this information from a php form into a mysql table.
This is what I did:
1. I created the table and the user to access the database the table is in
create table staffs ( id int not null auto_increment primary key, firstname
varchar(20) not null, lastname varchar(20) not null, signin datetime not
null );
2. I created a php form to insert data into this table. The form works but
the datetime field isn't populated
html
head
titleStaff Detail/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
/head
body
?php
if(isset($_POST['add']))
{
include 'config.php';
include 'opendb.php';
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$signin = $_POST['signin'];
$query = INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', '$signin');
mysql_query($query) or die('Error, insert query failed');
include 'closedb.php';
echo New staff detail added;
}
else
{
?
form method=post
table width=400 border=0 cellspacing=1 cellpadding=2 tr td
width=100First Name/td tdinput name=firstname type=text/td
/tr tr td width=100Last Name/td tdinput name=lastname
type=text/td /tr tr td width=100Sign-In Time/td tdinput
name=signin type=text/td /tr tr tr td width=100nbsp;/td
tdnbsp;/td /tr tr td width=100nbsp;/td tdinput name=add
type=submit id=add value=Submit/td /tr /table /form ?php }
? /body /html
Am I doing something wrong?
You mentioned that I can insert the now() function into a regular datetime
field, how can I do that?
In the last episode (May 09), Alla-amin said:
I am trying to capture my server time automatically using php and
insert it in a mysql table.
Can the timestamp or time data type capture this information
automatically without having me code anything else?
You can use the 'timestamp' type to automatially insert the current
date/time when inserting or updating, or you can insert now() into a regular
'datetime' field.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html
smime.p7s
Description: S/MIME cryptographic signature
RE: mysql query browser- editing resultsets
There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql query browser- editing resultsets
Then, as the documentation states, you cannot edit the fields in that particular query. Either the table is read-only, you have multiple tables (like a join) in your SQL string, or you have functions (max(),min(),count()) in your string. Check out those items again, and if you still can't edit it, post your SQL query and your CREATE TABLE statement so that we (the list) can test this out for you. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:42 AM To: J.R. Bullington; mysql@lists.mysql.com Subject: Re: mysql query browser- editing resultsets The edit button is 'greyed out' Ross - Original Message - From: J.R. Bullington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 24, 2006 4:20 PM Subject: RE: mysql query browser- editing resultsets There is an EDIT button on the bottom of the Query Browser. You have to activate the edit feature by clicking on this. You will see that it remains highlighted. To edit your cells, double click on them. When finished, hit APPLY CHANGES at the bottom. If you do not apply them, they will not commit. J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 11:14 AM To: mysql@lists.mysql.com Subject: mysql query browser- editing resultsets Can someone explain to me how to edit this...I click on the cells and nothing From the manual If the query resultset is editable, you can click the Edit button below the resultset view. Double clicking cells in the resultset will allow you to edit their contents. Changes are commited to the database only after the Apply Changes button is pressed. For a resultset to be editable, the following conditions must be met: a.. the resultset must contain columns from no more than one single table; b.. the queried columns must be proper column names (e.g: no functions, such as in SELECT max(price) FROM products); c.. this table must contain a Primary Key column, although not necessarily in the query. If the query does not contain the primary key, it will be automatically added before the MySQL Query Browser sends the query to the MySQL server, but will display only the columns you requested; -- 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 Backups
Some people use MySQLAdministrator. Some people use mysqldump. Yet others use scripts (as was mentioned before). We use a combo of mysqldump and Cron (Linux)/Task Scheduler (Windows). Using a text editor, create the mysqldump file that will push all of your data to a SQL file. For example: shell mysqldump --opt --all-databases -u [username] -p[password] -h[hostname] /path-to/sqlfile.sql Then, save that as either a BAT file or a CRON job. Set your time schedule to be when the server is the least busy, then dump your data. Easiest way (IMHO) to set a routine backup schedule and have the data be as portable as necessary. Just my $0.02. J.R. -Original Message- From: Dana Diederich [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 9:15 AM To: Greg 'groggy' Lehey; Jim Cc: mysql@lists.mysql.com Subject: RE: mySQL Backups We use a dedicated replicated instance for backups. Every night, we lock all of the tables, and dump all of them to compressed files, and unlock them afterwards. It takes a while to catch up, but that doesn't hurt anything. Cheers, -Dana -Original Message- From: Greg 'groggy' Lehey [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 4:05 AM To: Jim Cc: mysql@lists.mysql.com Subject: Re: mySQL Backups On Tuesday, 11 April 2006 at 18:40:23 +1000, Jim wrote: What is the standard procedure for mySQL backups. We need to backup at least once a day on a windows OS. Is there a SQL Server Job Agent type solution? At the moment it's a mess. We're working on cleaning up the mess, and you should see something soon. As soon as we have something to show, we'll let you know. In the meantime, we're still defining the architecture. If (any of) you have specific wishes, please let me know. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ - *** *** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Confidential ** -- 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: Results Rank
Select COUNT(names) 'cName' FROM tblname GROUP BY names ORDER BY cName http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html J.R. -Original Message- From: jakot05 [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 12:37 PM To: mysql@lists.mysql.com Subject: Results Rank Hi! I have a table like this names ··· john peter mary peter john peter mike mary mary mary ··· I want to get a list ordered by the number of times the name appears in the table, in the example: mary peter john mike How I can do this?...thanks a lot for the answers *** note: I don´t know wich are the names in the table, are changing all the time *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weird M$ Pasting issue
If they are pasting from Word, there is a lot of Word-proprietary XML and formatting that is being pasted as well. Your insert statement may be failing because: 1) Because of the XML and formatting, the statement is going beyond the TEXT fields limit; 2) There are ' (single quotes) in the formatting forcing an SQL / JDBC error; 3) The JDBC is reading and interpreting the XML and causing a failure of some kind. Your best bet is to either ask the people pasting to not use Word, have them paste from Word into NOTEPAD, TextEdit or VI before pasting into your app, OR try converting your TEXT field into a LONGTEXT field. Just a few thoughts... J.R. -Original Message- From: Vinny [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 3:53 PM To: mysql@lists.mysql.com Subject: Weird M$ Pasting issue Hello All, I am running across a very weird problem. Sometimes when a person paste text from a Worddoc into the text field of our webapp, the insert fails. Unfortunately I am not seeing the failure in the logs. There are a lot of factors to consider. The path to mysql looks like this. Firefox (OSX) - JDBC - Mysql (on linux) the field we are pasting to is a TEXT field. when I paste into an emacs editor. I see what looks like formatting code. Not sure why that is getting pasted into the text field and also not sure why the jdbc prepared statements are not making the text safe for insert. Anyone have a clue as to what might be happening? -- Ghetto Java: http://www.ghettojava.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]
RE: from MySQL to MS Excel ...
Here's the skinny -- YES and NO. NO in that it won't export directly, YES in that you have to do a little leg work in order for it to be done. You have 3 options -- ODBC, Code and CSV. ODBC -- Excel has the ability to use ODBC connections to the MySQL database. Run your MySQL query with the HTML flags turned on and then export to a file so that Excel can read it. (Thanks to SGreen for this info from an earlier post). CODE -- If you code it in ASP or PHP, you can get your code to push directly into Excel with field headers and data, and have formatting options because Excel can interpret HTML code. CSV -- Do your MySQL query from the CLI and then use MySQL to export your results to a CSV file. Then open the CSV file in Excel (using the Excel File Open). See ODBC connection above for another option using the HTML flag. HTH, J.R. -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Saturday, December 10, 2005 9:35 AM To: mysql@lists.mysql.com Subject: from MySQL to MS Excel ... Hi Friends, I am looking for an easy and seamless way to export MySQL query output to MS Excel. At this moment I am using MS Access 2003 as front-end for a MySQL database. With MS Access I can easily send the output of queries on my database to MS Excel. All I need to do is select Tools Office Links Analyze it with Microsoft Office Excel. That's all. This applies to all kinds of MySQL queries, including WITH ROLLUP options. In the manual I found: http://dev.mysql.com/doc/refman/5.0/en/doctoexcel.html But this is too much trouble, and does not allow full functionality of MySQL queries. Question: is it possible to create MS Excel files directly from MySQL ? TIA, Cor smime.p7s Description: S/MIME cryptographic signature
RE: Killing my curly quotes
Windows or Linux?? The REPLACE() function would do it if using the Character Mapping in Windows. UPDATE tbl_Name SET col1 = REPLACE(col1,'','') UPDATE tbl_Name SET col1 = REPLACE(col1,'','') They are ALT+0147 and ALT+0148 in Windows Character Map (charmap.exe) J.R. -Original Message- From: Brian Dunning [mailto:[EMAIL PROTECTED] Sent: Monday, December 05, 2005 2:13 PM To: mysql@lists.mysql.com Subject: Killing my curly quotes OK, I'm bad - I have curly quotes in my db that I failed to eliminate prior to the insert. Now that they're in there, is there a way to replace them with straight quotes? Everything I try fails to find them. -- 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: Windows - logging into MySQL
You need to be careful what you write. I have found that Shawn is one of the best guys on this list. His answers are 99% right on target (1% because not enough information was given) and can help on every version of db out there. Don't start a flame war because your feelings were hurt. Grow up. J.R. -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 30, 2005 6:05 PM To: 'Logan, David (SST - Adelaide)' Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL There are always idots like Shawn Green that give BS answers just for the sake of responding, and for me it just reflects badly on the group as a whole. There are many others that help, but when I open my email and this is the first response I see, it just gets under my skin. FYI. I have posted some information in another response as to the problems with later versions etc. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 5:57 PM To: Beauford; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL Sorry Beauford, The url that Shawn gave you is valid for the version you quoted. There hasn't been much change and they work just as well. This is also the earliest documentation that now exists on the website. What is the bad name this list has gained? I've found it helpful on numerous occasions. I think a reconsideration of your opinions would result in a change of heart. The documentation states the defaults that the mysql client program would accept, it also details how to set users and passwords exactly as you requested. These all work on 3.23.58 (I have this version installed at a customer site as we write). Perhaps if you detail the problems you had installing the later versions, somebody may be able to help. 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: Beauford [mailto:[EMAIL PROTECTED] Sent: Thursday, 1 December 2005 9:21 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL This is why lists like this get a bad name. First off, these instructions are for 4.1. Secondly. What makes you think I haven't read the instructions or searched extensively on Google etc. Either give a proper answer or just shut your mouth. Never mind anyone responding, I'll find my own damn answers. Thanks for the help. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 12:16 AM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Windows - logging into MySQL Beauford [EMAIL PROTECTED] wrote on 11/29/2005 11:31:24 PM: I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. You could try following the installation instructions in the manual http://dev.mysql.com/doc/refman/4.1/en/windows-installation.html 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] -- 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] smime.p7s Description: S/MIME cryptographic signature
RE: BLOB Source
You are going to need some sort of Upload software.
For this example, we are going to use Persits.Upload for the upload manager.
!-- BEGIN SNIPPET --
%
Set Upload = Server.CreateObject(Persits.Upload) 'need to
download ASPUpload to use this.
Upload.IgnoreNoPost = true
'ASPUpload Specific
Upload.Save
'ASPUpload Specific
Set File = Upload.Files(fld_2)
'ASPUpload Specific
'
if Upload.form(button)=Upload! then
'
If Upload.form(fld_2) then
If Not File Is Nothing Then
'
SQL = INSERT INTO table_name
(fld_0,fld_1,fld_2,File_Type,File_Size) VALUES ('
SQL = SQL value_0 ',' value_1 ',?,'
right(File.FileName,3) ',' File.Size ')
'Here the ? Is the uploaded file from your web page.
File.ToDatabase DSN=yourDSN, SQL'The Connection to
the Database.
Response.Write File saved.
Else
Response.Write File not selected. 'If INPUT
TYPE=FILE IS Empty then bail
End If
else
response.write There is an Error... Check this out'Other type
of error message
end if
Set Upload = nothing'Clean Up
Set File = nothing 'Clean up
end if
%
form method=post action=your_page_goes_here.asp
enctype=multipart/form-data
input type=file name=fld_1 size=20
input type=submit value=submit name=button
/form
!-- END SNIPPET --
What you will notice is that if the form is OK (not missing any req.
fields), then it sends the file to the database. The ? is the value of the
uploaded file. It is recommended that you send the file type in case you
want to pull them later without having to worry about putting in specific
Response.ContentType for every page. You can use the File_Type in the db to
use the right ContentType.
The File.ToDatabase object pushes the SQL string (including the Uploaded
BLOB string) to the database.
That's it. Of course, you will have to tailor this to your upload software
and to your database.
J.R.
-Original Message-
From: Brian E Boothe [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 13, 2005 10:50 PM
To: mysql@lists.mysql.com
Subject: BLOB Source
Can someone please post some asp or php working with BLOB Files, and how to
Get files into a Mysql Database,, in working woth PDF Files thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does BLOB=Memo?
Access MEMO is actually closer to LONGTEXT than to BLOB. Blob stands for Binary Large OBject. Although you can use BLOB, depending on what you are inserting, it is recommended that you use LONGTEXT for your memo field. J.R. http://dev.mysql.com/doc/refman/5.0/en/blob.html -Original Message- From: Jesse Castleberry [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 09, 2005 2:32 PM To: MySQL List Subject: Does BLOB=Memo? I'm a bit new to MySQL. I need to create a Memo field (a field that could contain a lot of information). I know that in some languages, BLOB is a Memo field. Is that the case with MySQL? If so, are there some parameters that I need to use here, or should I use another type to represent my Memo field? Thanks, Jesse -- 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: PHP/MySQL vs ASP/MSSQL?
My turn to chime in on this one... I have been using ASP/MySQL on a Windows AND Linux box without any issues. Yes, that's right, ASP, with the right 3rd party software, can even run on Linux. (Don't ask Why??, just note that it can be done!) As long as you create a system DSN (IMOH, the easiest way to go) to connect to your MySQL databases (you have to use the MyODBC 3.51 driver), you won't have any problems coding ASP with MySQL as your backend. You can use the MS Jet to connect, however it puts another layer of complexity into the mix. Just another option... J.R. -Original Message- From: Pooly [mailto:[EMAIL PROTECTED] Sent: Friday, November 04, 2005 7:37 AM To: MySQL General Subject: Re: PHP/MySQL vs ASP/MSSQL? 2005/11/4, Marc Pidoux [EMAIL PROTECTED]: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... A setup of ASP/MySQL could be a right option for you if you already know ASP. I'm not sure if it's possible though (using ODBC ?). -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
MSSQL-MySQL Compatibility Question
Hi All, Quick question -- I have a client who co-owns a server with me. I am a die-hard MySQL guy, they are MSSQL. They have some proprietary Access-frontend/MSSQL-backend financial system that they want to continue to use (i.e. pigheaded and won't convert). Can MS-SQL and MySQL run on the same box and not conflict with each other? I don't have the SQL disks in front of me to test and I was wondering if anyone else ran into this situation. Box is a Quad Xeon 2.0GHz, 1GB RAM, 80GB Ultra3 SCSI Raid. Thanks! J.R.
RE: MSSQL-MySQL Compatibility Question
Thanks guys! J.R. -Original Message- From: JamesDR [mailto:[EMAIL PROTECTED] Sent: Thursday, October 27, 2005 9:21 AM To: mysql@lists.mysql.com Subject: Re: MSSQL-MySQL Compatibility Question J.R. Bullington wrote: Hi All, Quick question -- I have a client who co-owns a server with me. I am a die-hard MySQL guy, they are MSSQL. They have some proprietary Access-frontend/MSSQL-backend financial system that they want to continue to use (i.e. pigheaded and won't convert). Can MS-SQL and MySQL run on the same box and not conflict with each other? I don't have the SQL disks in front of me to test and I was wondering if anyone else ran into this situation. Box is a Quad Xeon 2.0GHz, 1GB RAM, 80GB Ultra3 SCSI Raid. Thanks! J.R. I have MSSQL + MySQL on the same box here, no issues with that... My server is hardly Big Iron or Small Iron by any reach: P3 933 1GB Ram, SCSI160 non-raid ;-D More memory, if you can do it, is better... (roll on new server...) Best thing to look at is your current load, if you are pushing the limits, adding another RDBMS may cripple that box. As far as negative interaction, I've seen none. -- Thanks, James -- 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: Best configuration OS WebServer
I don't want to speak out of turn, but here is the real question... What are you more comfortable with? If you feel that you are more comfortable with Windows, then use Windows and Windows 2000/2003 Server. If you want to stretch your comfort level and think outside the box, Linux is your choice. Without trying to start a flame war, I think it would be easier on you in the long run to start on Linux. Since you are a newbie willing to learn, Config 1 would give you the option of learning a new OS, security, MySQL, PHP, and a whole lot more not listed here. You can still connect with your laptop to Linux, and it would help you to learn WHY things work, not just HOW they work. HTH, J.R. -Original Message- From: Serge Goyette [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 26, 2005 10:24 AM To: mysql@lists.mysql.com Subject: Best configuration OS WebServer Hi, I am a brand new newbie...two questions. Question 1 What is the best configuration for using MySQL and PHP from the three following combinations: #1 OS: Linux WebServer: Apache #2 OS:Windows XP SP2(Family edition) Webserver: Apache #3 OS:Windows XP SP2 (Family edition) Webserver: Windows Server Or any other suggestion is welcome. That configuration will be running from a bootable HD in a tray either from a P4 or a PIII machine. One of my MAIN CONCERN is that I should be able to produce demo from my laptop running under Windows XP SP2(family edition). Question 2 What is the best linux distribution for running Apache - MySql - PHP Thank you for helping me. Excuse my english... -- 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: Best configuration OS WebServer
Just because your webpage and your MySQL server are running on Linux doesn't mean that you cannot connect to it using Windows. If you need to transfer files back and forth, you can use SAMBA (http://www.samba.org) or FTP (see your Distro's man pages for more info). Most notably, if you are using Linux/Apache as your webserver, use your laptop to view the webpage with a MySQL client on it to attach to the database and Dreamweaver to write the PHP files. Lastly, you asked what is the best distro. That IS a flame war question as everyone has their own opinion. I like RedHat. Most like Fedora Core. You can also get YellowDog (for Macs), Mandrake, LinSpire, whatever you like/want. Research is your friend. IMHO, the best site to get distros is http://www.linuxquestions.org. They have all the distros and is one of the best Linux sites I have found. If you have any Linux questions, that's the place to go. HTH, J.R. -Original Message- From: Serge Goyette [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 26, 2005 11:03 AM To: 'J.R. Bullington'; mysql@lists.mysql.com Subject: RE: Best configuration OS WebServer Hi Thank you for the answer You can still connect with your laptop to Linux, and it would help you to learn WHY things work, not just HOW they work. How will I do that ? Laptop run on Windows os. -Message d'origine- De : J.R. Bullington [mailto:[EMAIL PROTECTED] Envoyé : 26 octobre 2005 10:40 À : mysql@lists.mysql.com Objet : RE: Best configuration OS WebServer I don't want to speak out of turn, but here is the real question... What are you more comfortable with? If you feel that you are more comfortable with Windows, then use Windows and Windows 2000/2003 Server. If you want to stretch your comfort level and think outside the box, Linux is your choice. Without trying to start a flame war, I think it would be easier on you in the long run to start on Linux. Since you are a newbie willing to learn, Config 1 would give you the option of learning a new OS, security, MySQL, PHP, and a whole lot more not listed here. You can still connect with your laptop to Linux, and it would help you to learn WHY things work, not just HOW they work. HTH, J.R. -Original Message- From: Serge Goyette [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 26, 2005 10:24 AM To: mysql@lists.mysql.com Subject: Best configuration OS WebServer Hi, I am a brand new newbie...two questions. Question 1 What is the best configuration for using MySQL and PHP from the three following combinations: #1 OS: Linux WebServer: Apache #2 OS:Windows XP SP2(Family edition) Webserver: Apache #3 OS:Windows XP SP2 (Family edition) Webserver: Windows Server Or any other suggestion is welcome. That configuration will be running from a bootable HD in a tray either from a P4 or a PIII machine. One of my MAIN CONCERN is that I should be able to produce demo from my laptop running under Windows XP SP2(family edition). Question 2 What is the best linux distribution for running Apache - MySql - PHP Thank you for helping me. Excuse my english... -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Map of MySQL Users
Thank you for your email. It seems as though you are getting rave reviews from the list. I am sure that if you want to make this really neat, try creating a simple web login that allows for you to create an account using the email address, then update your location so that it can be a little more accurate. As stated in some of the posts, a lot of times your lat/long service may not be entirely right as it is basing the lat/long off the mail carrier, not necessarily the location of the user. J.R. PS -- Listers, sorry I didn't CC the list on my first correspondence. Here it is with her answers. GREAT JOB CLAIRE COMPANY! -Original Message- From: Claire McLister [mailto:[EMAIL PROTECTED] Sent: Monday, October 24, 2005 6:24 PM To: [EMAIL PROTECTED] Subject: Re: Map of MySQL Users We use the origin IP and then convert it to a latitude/longitude using a commercial service. There are several services available these days to do this conversion. We use any email posted to the list for which we can get a decent IP value. It could be from a registered MySQL user or not. Even a single post should get you on the map. The only thing is a special email address needs to be added to the list so that we can get location information from each email. Thanks for your feedback. Yes, I think adding a geographical dimension to the email lists brings up interesting possibilities. Let me know if you want to see maps of other email lists. Claire On Oct 24, 2005, at 3:05 PM, J.R. Bullington wrote: Couple of questions for you -- What are you using to keep pinpoint the origin of the submitted emails? PING, TRACERT, WHOIS off the domain names? Are you only doing original posts, or original replies? Do they need to be registered users of mySQL or just post to the list? Do you have to post / reply a minimum number of times to be noticed/added onto the list? -- Now back to the original topic -- The popup JS is great (love the filter / search feature). It's a great system and, if used, I think it will bring MySQL users together and help in creating site/regional specific MySQL lists/conferences. Thanks for listening! J.R. -Original Message- From: Claire McLister [mailto:[EMAIL PROTECTED] Sent: Monday, October 24, 2005 5:19 PM To: mysql@lists.mysql.com Subject: Map of MySQL Users Hi, We've developed an automatic email mapping capability from Google Maps API. To try it out, we mapped origins of emails to this group from October 3 through October 14th. The result of this map is at: http://www.zeesource.net/maps/map.do?group=460 Would like to hear what you think of it. Best wishes Claire -- Claire McLister [EMAIL PROTECTED] 1684 Nightingale Avenue Suite 201 Sunnyvale, CA 94087 408-733-2737(fax) http://www.zeemaps.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: SQL statement help
I am not the smartest on the list, but I think this would do it for you. UPDATE table_one t1, table_two t2 SET t1.ID = t2.ID WHERE t2.num = t1.num AND t1.ID = 0 J.R. -Original Message- From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: Sunday, October 16, 2005 1:45 PM To: mysql@lists.mysql.com Subject: SQL statement help Importance: High Hello All, I have two MySQL 4.1 tables. The first table has an ID field which is set to zero. The second one has ID fields and filenames. I need a single SQL statement to set the ID from table 1 to the ID from table 2 ONLY IF the ID on one is zero. That is, if the ID on table one is not set (still equal to zero), grab the ID from table2 where the num of table2 is equal to table1. table one - Title - num - ID (set to zero initially) table two - num - ID - filename SET table1.ID = someID if table1.ID EQUAL to zero. Set the ID on table1 from the table 2 where the num on table 2 equals the num on table1 only if the ID on table 1 is zero. Thank you in advance, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Getting x characters from text
SELECT ID, title, date, left(content,40) FROM news ORDER BY date asc LIMIT 0,10 If you want the last 40, use right(), and if you want to choose a section, use the mid() function. HTH, J.R. -Original Message- From: Dotan Cohen [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:48 PM To: mysql@lists.mysql.com Subject: Getting x characters from text I have this SQL statement: SELECT ID, title, date, content FROM news ORDER BY date asc LIMIT 0,10 However, I only need the first 40 characters of the content field, which in some cases is over 5000 characters long. Is there a way to request only the first 40 characters of a field? Thanks in advance. Dotan Cohen http://www.technology-sleuth.com/mobile/cellphones.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Security Question
Title: Security Question Hi All -- I have been a member of this list for a while but I actually have a question that I can't answer. MySQL v4.1.14-nt on Win2k3 Server I've got someone who is trying to get in, but I have locked it down. Methods used include, but are not limited to: No Outside Root Access System DSNs for Web connectivity Strong Passwords for each user User Permissions different for each purpose Here's the question -- It's a DoS attack and it's locking up the system for other users (max_connections_allowed). Anything I can do extra via MySQL that will keep this person away, or perhaps free up the server? I would rather not increase the max_conn_allowed var as it's already at 800 (more than I need). Do not have access to the Router (I wish I did, ACLs are such a great thing), but have full Admin rights to the server. Thanks everyone! J.R. smime.p7s Description: S/MIME cryptographic signature
RE: Startup Error
Check your hostname.err logs located in the /data/ directory. This will tell you why you can't start the server. J.R. -Original Message- From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 10:38 AM To: 'mysql@lists.mysql.com' Subject: Startup Error Hi there Total Newbie here.Any idea why I am getting these error messages [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/ RJLINUX.rj.johnson.net.pid 050919 10:05:36 mysqld ended Here are the steps I followed to loading mysql onto my linux box.. shell groupadd mysql shell useradd -g mysql mysql shell cd /usr/local shell gunzip /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf - shell ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql shell cd mysql shell scripts/mysql_install_db --user=mysql shell chown -R root . shell chown -R mysql data shell chgrp -R mysql . shell bin/mysqld_safe --user=mysql Richard Johnson 212-589-6503 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Startup Error
Well, as you can see in the first few lines, you have a Permission Denied problem. This means that your /data/ folder doesn't have the write permissions to it via the mysql user. Try running the permission scripts again, but this time use the full shell names. shell chown -R root /usr/local/mysql/. shell chown -R mysql /usr/local/mysql/data/. shell chgrp -R mysql /usr/local/mysql/. Then check to make sure that user has write permission to the folders: shell ls -l /usr/local/mysql/data It should say something similar to: shell ls -l /usr/local/mysql/data total 63868 drwx--2mysqlmysqlsizedatedirname -rw-rw1mysqlmysqlsizedatefilename-bin.000 . . -rw-rw1mysqlmysqlsizedatehostname.err J.R. _ From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 11:35 AM To: 'J.R. Bullington' Subject: RE: Startup Error Here are the contents of the file 050919 09:33:52 mysqld started 050919 9:33:52 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 9:33:52 [ERROR] bdb: /usr/local/mysql/data/log.01: Permission denied 050919 9:33:52 [ERROR] bdb: PANIC: Permission denied 050919 9:33:52 [ERROR] bdb: PANIC: DB_RUNRECOVERY: Fatal error, run database recovery 050919 9:33:52 [ERROR] bdb: fatal region error detected; run recovery 050919 9:33:52 [ERROR] bdb: /usr/local/mysql/data: Permission denied InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050919 9:33:52 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050919 9:33:53 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050919 9:33:53 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050919 9:33:53 InnoDB: Started; log sequence number 0 0 050919 9:33:53 [ERROR] Can't init databases 050919 9:33:53 [ERROR] Aborting 050919 9:33:53 InnoDB: Starting shutdown... 050919 9:33:55 InnoDB: Shutdown completed; log sequence number 0 43634 050919 9:33:55 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 050919 09:33:55 mysqld ended 050919 09:46:55 mysqld started 050919 9:46:55 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 9:46:56 [ERROR] bdb: /usr/local/mysql/data/log.01: Permission denied 050919 9:46:56 [ERROR] bdb: PANIC: Permission denied 050919 9:46:56 [ERROR] bdb: PANIC: DB_RUNRECOVERY: Fatal error, run database recovery 050919 9:46:56 [ERROR] bdb: fatal region error detected; run recovery 050919 9:46:56 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 9:46:56 InnoDB: Started; log sequence number 0 43634 050919 9:46:56 [ERROR] Can't init databases 050919 9:46:56 [ERROR] Aborting 050919 9:46:56 InnoDB: Starting shutdown... 050919 9:46:58 InnoDB: Shutdown completed; log sequence number 0 43634 050919 9:46:58 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 050919 09:46:58 mysqld ended 050919 09:56:10 mysqld started 050919 9:56:10 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 9:56:10 [ERROR] bdb: /usr/local/mysql/data/log.01: Permission denied 050919 9:56:10 [ERROR] bdb: PANIC: Permission denied 050919 9:56:10 [ERROR] bdb: PANIC: DB_RUNRECOVERY: Fatal error, run database recovery 050919 9:56:10 [ERROR] bdb: fatal region error detected; run recovery 050919 9:56:10 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 9:56:10 InnoDB: Started; log sequence number 0 43634 050919 9:56:10 [ERROR] Can't init databases 050919 9:56:10 [ERROR] Aborting 050919 9:56:10 InnoDB: Starting shutdown... 050919 9:56:12 InnoDB: Shutdown completed; log sequence number 0 43634 050919 9:56:12 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 050919 09:56:12 mysqld ended 050919 10:05:33 mysqld started 050919 10:05:34 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 10:05:34 [ERROR] bdb: /usr/local/mysql/data/log.01: Permission denied 050919 10:05:34 [ERROR] bdb: PANIC: Permission denied 050919 10:05:34 [ERROR] bdb: PANIC: DB_RUNRECOVERY: Fatal error, run database recovery 050919 10:05:34 [ERROR] bdb: fatal region error detected; run recovery 050919 10:05:34 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 10:05:34 InnoDB: Started; log sequence number 0 43634 050919 10:05:34 [ERROR] Can't init databases 050919 10:05:34 [ERROR] Aborting 050919
RE: Populate values in an Excel sheet from MySQL
Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. -Original Message- From: Nick Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 2:23 PM To: mysql@lists.mysql.com Subject: Populate values in an Excel sheet from MySQL Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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]
RE: Populate values in an Excel sheet from MySQL
Here's a little heads-up, as you will need some XML coding to make it look right. PLEASE NOTE: I'm an ASP programmer, so PHP is a little foreign to me. You will need to do some conversion. Here is the way to do it: 1) Define your recordset and connection strings. 2) Write PHP strings to add ContentType (or similar call) = application/vnd.ms-excel 3) (optional) Write PHP echo strings for adding XML tags. (You can find these at http://msdn.microsoft.com, although I'll be damned if I can find them now. Ask for them and I will get them from my other computer and send them on.) 4) In the body of your webpage, put the following snippet: body table width=100% thead tr tdCol 1/td tdCol 2/td ... tdCol end/td /tr /thead tbody ?PHP Open the records, pull the set 'do the loop movefirst while not eof ? tr td? PHP DATA 1 ?/td td? PHP DATA 2 ?/td ... td? PHP DATA end ?/td /tr ?PHP Next in loop Wend Close ? /tbody /table /body /html I hope this helps a little! J.R. PS - Shawn FYI, if you copy and paste a tab into the replace with in notepad, you can do it that way... -Original Message- From: Nick Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 3:19 PM To: mysql@lists.mysql.com Subject: RE: Populate values in an Excel sheet from MySQL --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- 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: Why does this fail
First glance -- It's missing a comma (,) between T1.BldgName and T1.StreetName, Missing a period (.) in PstlAddr T1 and in PrsnAddress T2 Also, how does the system know the table names if you are putting them first, (i.e. shouldn't it be BldgName.T1 not vice versa)? You don't define what T1 is, unless they have somehow skipped using the AS statement (and without seeing the table structure, you don't know if this is an alias or the true table name). Of course, I am not the expert on this list, just some common SQL sense. J.R. -Original Message- From: Andrew Hargreaves [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 9:14 AM To: mysql@lists.mysql.com Subject: Why does this fail Can anyone explain why this SQL statement would fail? A friend was asked it in a pre-interview assessment and it threw him a bit. SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr T1 Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from PrsnAddress T2) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and HIPAA Compliance?
What a great discussion thread! Gary, I currently use MySQL as part of a HIPAA compliant system for the integration of web-based apps with Patient Care information. HIPAA (Health Information Portability and Accountability Act) is a set of standards set by the US Government to protect people's private health information. Although created in 1996, only recently (April 2005) has it started to really affect the way that health care organizations really needed to worry about it's IT implications. So, let's get to quick and dirty bottom of this: Is MySQL compliant -- NO. Neither is any other RDBMS on the market today. The way that data is stored is not at the issue. It's the way that data is collected that is at the heart of the RDBMS part of HIPAA. You are correct that the Electronic Data Interchange (EDI) and application layers are responsible for the encryption of the data. As long as the data is in a locked, windowless, office (hopefully a server room) with adequate security features (like physical security) and the system is locked with passwords and has virus protection, then the data is HIPAA compliant. What HIPAA was truly designed for was not the storage of data but the PORTABILITY of data. How is the data accessed and who sees it? Data needs to be accessed over a secure connection, either via SSL or other encryption standards (AES, WEP, TLS, etc.) and must have a strong password (minimum 8 characters, alphanumeric, and special characters) in order to decrypt it. That being said, only certain individuals are even allowed to access that data, set up via Active Directory, directory shares, or by access lists. With the web based applications that I use, you must either be inside the network or access it via a 128-bit encrypted VPN. Not only does the data transmission need to be encrypted, but it also needs to be adequately logged as to who sees it, what they were looking at, how long they were there, and what their purpose was. Now the systems that I designed/use were designed with HIPAA in mind, so, save 1 or 2 tables, everything is in integers. Printing out an entire table of data and leaving it in the cafeteria is not an issue as you would see nothing but numbers. Without having the database schema in hand to reference what all the numbers mean, you won't be able to determine anything. Now, I am just a computer jockey with ump-teen years experience under my belt, so don't take what I have written here to be law. However, being the in healthcare field and designing databases with HIPAA compliance in mind has been a huge help. There are lots of sites out there with much more information than I have in my head, so I would seek those out as well. One site, http://www.wpc-edi.com/hipaa/ has all the data that you need straight from the ass's (oops, horse's) mouth of the US Gov't. I have helped in the authoring of a few papers on HIPAA compliance and computers in the healthcare industry that, if this would be of interest to anyone, are available. Please email me directly, as putting them on the list would be unwanted propaganda. I hope this sparks some more discussion from 'the group'. Sincerely, J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 12:56 PM To: mysql@lists.mysql.com Subject: MySQL and HIPAA Compliance? Hi, I'm taking a database class and we are given open ended discussion questions each week. The question this week is: Will MySQL take away market share from popular DBMSs? Will your comments change if you are told that MySQL is not HIPPAA compliant? I have been using MySQL for well over 3 years, and other databases for well over 10 years (professionally, as a coderbout time I took a class eh?) and I do think I have an informed opinion for the first part. I'm weak in the area of HIPAA compliance though. I know it basically centers around privacy. I know it covers things like adequate logging, encrypted connections, etc, but it also seems to include a lot of EDI interoperability. Now that seems to be something that should be handled at an application level and MySQL shouldn't be penalized because of this. From the searching I have done, it appears that MSSQL for example offers this mandatory feature via their Biztalk server (to handle all the EDI) There are all manner of sites that will discuss HIPAA compliance for a fee. Is anyone here familiar with this that could provide a reference or a simple summary. It seems an interesting and important topic that I thought the list might be interested. Regards, Gary Huntress -- 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 and HIPAA Compliance?
Now see, if I gave that away, I wouldn't have interested you, now would I? As with all databases, you need to collect some kind of identifying information. That's what the save 1 or 2 tables statement was about. Take this simple system for example. Table 1 ... UserID UserNameFirst_Name Last_Name 1 logmeon J.R.Bullington Table 2 ... ID1 UserID SitePassword 1 1 145 12345 Table 3 ... SiteID SiteNameSiteLoc 145 HomeSomePlace USA And so on and so forth... Out of the information above, let's print some... If I printed 2 and 3, what could you do with it? Nothing, since the FK is not the same as PK. Now, we database people know what we could do, but not the layman. If I printed 1 and 3, what could you do with it? Nothing, no references... If I printed 1 and 2, a little more could be done, because you have username and password, but the rest of the data is unrecognizable. Now here is the real fun... Take away the table names, add 5000 records to each table, and shuffle them. That's the typical database size in a medium-sized clinic. Since you wouldn't actually print all 5000 records onto paper, you would only print what you wanted to see, how could you figure it out? Rhetorical question as you couldn't without the database schema in hand to relate back to. Only 1 table out of the ubound(Table(x)) that I could have created (only 3 shown) have personally identifiable data in them. No one but the administrator and/or data entry person(s) has/ve access to that one table. Hence... HIPAA compliance. Welcome to my world... J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 1:46 PM To: J.R. Bullington Cc: mysql@lists.mysql.com Subject: Re: MySQL and HIPAA Compliance? Now the systems that I designed/use were designed with HIPAA in mind, so, save 1 or 2 tables, everything is in integers. Printing out an entire table of data and leaving it in the cafeteria is not an issue as you would see nothing but numbers. Without having the database schema in hand to reference what all the numbers mean, you won't be able to determine anything. That's a clever idea! But didn't you have to store personal information at somepoint? I guess you could do a clever encoding scheme to map a name and address to a very large integer, but that seems... not much better. How did you handle that issue? -- Ross Vandegrift [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: free MySQL conversion to MSSQL tool
Although finding a tool that will automatically transfer files from MySQL to MS SQL format will be hard to do, both will accept txt files that have the CREATE statements and data in SQL. mysqldump will do this for you. http://dev.mysql.com/doc/mysql/en/mysqldump.html Just export the files to an .SQL file and load it into MS SQL. So your command will be something like: shell /path/to/mysql/bin/mysqldump --opt -u [username] -p [password] File_Name.SQL If you want a tool to do it for you, try dbTools software (http://www.dbtools.com.br). It lets you copy tables from one database to another, is PHP/ASP/JSP independent (as it is 3rd party), and has other data management tools. However, you must have a Windows box in order to use it. I do not believe they make a Linux version. HTH, J.R. -Original Message- From: Bastian Balthazar Bux [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 6:13 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: free MySQL conversion to MSSQL tool [EMAIL PROTECTED] wrote: Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew phpMyAdmin (http://www.phpmyadmin.net/) version 2.6.3-pl1 has the option to export the data in mssql format (and much others). You need a php enabled web server able to connect to your mysql database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Network drive
something = ODBC is the first thing that comes to mind. You can set specific permissions on the ODBC and you don't have to open up but 1 port (3306 or whatever you choose) in your proxy / firewall. Whenever your app calls the ODBC, the connection is made and everything is happy. Just my $.02 J.R. -Original Message- From: Ruben Carvalho [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 06, 2005 1:20 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Network drive I don't have a web server, I mean, I have a standalone java application running in my clients and the application calls the database. Is there any way of having something listening to my application calls in my open machine (outside the proxy) and this something would then call the database running behind the proxy? --- [EMAIL PROTECTED] wrote: Ruben Carvalho [EMAIL PROTECTED] wrote on 07/06/2005 12:54:24 PM: Well, thank you very much for your explanation. My problem is I would like to have the data files being saved in a machine behind a proxy but the server running in a machine outside the proxy (the clients don't have access to the machine behind the proxy). Any ideas? Thank you --- [EMAIL PROTECTED] wrote: Ruben Carvalho [EMAIL PROTECTED] wrote on 07/06/2005 11:06:10 AM: I think I haven't understood your question. I guess that in case of a network failure you can have the same behavior as a power shutdown. About the networked drives? Anyone? --- Martijn Tonies [EMAIL PROTECTED] wrote: Hi Ruben, I would like to make a short, quick and simple question. Is it possible to have the following line: innodb_data_home_dir=X:/data/ in a my.ini config file? I'm using windows XP, mysql 4.1.12, X: is a mapped network drive to a Linux folder using samba, all the permissions are set and tested. I have seen this posted many times but without many replies. I want to use a folder in a mapped drive to save my InnoDB data. Is this possible? I don't know this particular answer for MySQL, but I do have 1 question: If the database engine doesn't have control over the files and/or drive, what should it do in the case of a network failure? Let alone the latency of a networked file... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Rúben Carvalho RDBMS over a network: NOT recommended. Not only can you not enforce OS-level locking on your files (maybe you can, I guess it may depend on your device and inteface protocols) but the MOST COMMON bottleneck to database performance is disk I/O. If you went with networked storage, you are not only going to suffer through disk lag (seek time + rotational positioning before the operation can start) but you are incurring network overhead on top of it IN BOTH DIRECTIONS. Unless your network device is flash-only (all memory, no disks), you just cut your throughput by at least 75%. And even if your device is flash-only you will reduce your data throughput by 25-50% (all performance numbers are rough estimates pulled out of my a** but based on the number of extra network hops necessary to get at and read your files). I don't care how fast your network is, networked storage can't be as fast as local disks. Again, it is highly discouraged to use networked storage for anything but the most trivial database uses (small file sizes, low traffic, etc). For any application that requires even modest performance, spend your money on a fast RAID configuration. You will be much happier in the long run. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rúben Carvalho There are different kinds of secure setups. As a first idea, your database server and your web server DO NOT need to be on the same machine. There are MANY ways to setup a secure web system. How many of each type of networking component are at your disposal (proxy servers, firewalls, web servers, network interface cards, routers, etc.)? Different types of security are available with different hardware/software configurations. Basically, it all boils down to keeping the users only where you want the users to be (outside of your network) and allowing only certain servers (or even just particular processes on those servers) to access your internal resources. Shawn Green Database Administrator Unimin Corporation -
RE: No result at all (expected 0)
You got exactly what the db found. An Empty Set. You wouldn't get '0' results because there aren't '0', it's EMPTY. It's not NULL either since NULL is a form of non-existence. Why were you expecting '0'? J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 16, 2005 10:47 AM To: mysql@lists.mysql.com Subject: No result at all (expected 0) Hi everyone! I have a little question. I have two tables which are used for saving private messages. If a user has NO messages, the result doesn't return _anything_. I expected to get 0 results but this really confuses me. Query: SELECT upn.id, upn.fid, upn.tid, upn.subject, upn.message, upn.date, upn.state, u.login FROM tx_user_pns AS upn, tx_user AS u WHERE upn.fid = u.id AND upn.tid = 19 ORDER BY upn.state ASC, u.login ASC, upn.date DESC; Tables: CREATE TABLE tx_user ( id int(11) NOT NULL auto_increment, login varchar(255) NOT NULL default '', password varchar(255) NOT NULL default '', level int(11) NOT NULL default '0', intern tinyint(1) unsigned NOT NULL default '0', last_login datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id), UNIQUE KEY login (login) ) TYPE=MyISAM; CREATE TABLE tx_user_pns ( id int(11) unsigned NOT NULL auto_increment, fid int(11) NOT NULL default '0', tid int(11) NOT NULL default '0', subject varchar(255) NOT NULL default '', message text NOT NULL, date datetime NOT NULL default '-00-00 00:00:00', state tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Tanks in advance, Marco -- 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]
alter table - add a column
Alter table foo drop Primary Key Alter table add `userid` int(8) first Alter table add Primary Key `userid` Those 3 statements should do it. However, knowing the people on this list, they will have a faster and better way than mine. Don't forget about the manual as well... http://dev.mysql.com/doc/mysql/en/alter-table.html J.R. -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, June 13, 2005 10:30 AM To: mysql@lists.mysql.com Subject: alter table - add a column hi.. i have a table that i want to add a column to, and define the columm to be the primary key. however, i already have a column in the table that's used as the primary. can someone tell me the approach/commands i should use to get my results? table foo username varchar(50), primary what i want... userid int(), primary username varchar(50) with userid listed before username!! can someone tell me what the commands are that i need to enter to get this!! i thought something like, alter table foo add userid int(8), primary first (but i couldn't get it to work.. errors) and then i couldn't see how to remove the primary key fom username... thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Alter Column Name?
Why not just ALTER TABLE tbl_Foo CHANGE `ID` `CHILD_ID` options go here Since I don't know the rest of your options or what you want to change the column to, that's just for the name change. J.R. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Monday, June 13, 2005 4:56 PM To: Scott Purcell; mysql@lists.mysql.com Subject: RE: Alter Column Name? I'm not sure if this will work for a column name but there is a CHANGE COLUMN feature in the ALTER TABLE statement so that sounds promising. Then again, I don't have a MySQL system to try it on this week - I am travelling - so I can't be sure if it will work for the column name. Rhino -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Monday, June 13, 2005 1:00 PM To: mysql@lists.mysql.com Subject: Alter Column Name? I cannot find any information on this, so I am thinking the answer is no. But I have a column that I would like to change the name of: current the column name isID, and I would like it to change it to CHILD_ID, as the the software is changing, and we are adding parent_id column. Thanks, Scott -- 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] smime.p7s Description: S/MIME cryptographic signature
RE: Seriously.. When are we going to get subqueries?!
snip This is the weirdest thread I've ever seen. I've never seen so many seques used in a thread /snip Agreed. However, if you read the entire thread you have learned more in ONE location about timestamps, default values, creating tables, other RDBMS types, interoperability, contribution to the open source community, and subqueries than you would have found simply by looking on your own. This is the kind of thread that most people both love and hate -- 1) It takes up space in all our mailboxes and time to read it (the hate) 2) It shows what collaboration and putting multiple heads together on 1 project can accomplish (the love). Great job everybody. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: no 3306 but mysqld running
Have you tried to telnet into that port? Are you sure that it's open in your firewall? J.R. can somebody explain the events below: mysql is running but its not listening on 3306,...as a result clients on remote machines are not able to connect.. smime.p7s Description: S/MIME cryptographic signature
RE: default current_timestamp problem
You do not need to set a default value if you want the current_timestamp. Just leave the default option out when creating your table and the server will do it for you. CREATE TABLE COOKIE_REF ( cookie_ref varchar(50), dat timestamp ); OR If you really want to put in a value for the default, use CREATE TABLE COOKIE_REF ( cookie_ref varchar(50), dat timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ); J.R. -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 4:30 PM To: mysql@lists.mysql.com Subject: default current_timestamp problem Hello, I am trying to create simple reference table for some cookies I am creating. I wanted to put in a current_timestamp each time I do an insert so I can delete this data after 2 or 3 weeks. Here is the insert statement: CREATE TABLE COOKIE_REF (cookie_ref varchar(50), dat timestamp default current_timestamp) I am trying to insert the below statement, but it fails with a 1064 Error Code : 1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_timestamp)' at line 1 (0 ms taken) I am running version 4.0.15-max-debug on windows. and following this link but It will not work. What can I be doing wrong? http://dev.mysql.com/doc/mysql/en/datetime.html Thanks, Scott - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- 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: varchar to text
Changing types to larger shouldn't be an issue. If you are changing to smaller, of course you are going to truncate the data more. You do not need to move the data out and then back in again unless you really want to. mysql ALTER TABLE tbl_name CHANGE `col1_old_name` `col1_new_name` text default null J.R. -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 9:37 AM To: mysql@lists.mysql.com Subject: varchar to text Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a text type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a text column? I have no experience with this area of sql. Thanks, Scott -- 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]
FW: update and select question
http://dev.mysql.com/doc/mysql/en/update.html (B (BSee the last line on the page. You cannot, in the most current stable (Bversion of MySQL, SELECT from the table you are trying to UPDATE. (B (BJ.R. (B (B-Original Message- (BFrom: $B2+9bJv(B [mailto:[EMAIL PROTECTED] (BSent: Tuesday, April 26, 2005 11:26 PM (BTo: mysql@lists.mysql.com (BSubject: update and select question (B (B (Bi use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B WHERE KAI_CDE = :KAI_CDE_T1 (B AND EDT_NUM != ''); (B (Bthe error message was: (B (BYou can't specify target table 'NGLDENHDT' for update in FROM (Bclause (B (B (Bwhat can i do ,help me ? (B (B (B thank you very much!! smime.p7s Description: S/MIME cryptographic signature
RE: dateTime vrs. Timestamp
IMO, dateTime doesn't parse now() the same way that timeStamp does. If you use now() in a dateTime field, I have found that it doesn't always write the time to the record, while using now() with the timestamp always does. As far as your other questions, the indexing and increased performance, I have found that dateTime is usually only good for storing static dates v. dynamic dates with timeStamp (i.e., a LastEdit field with dateStamp now() only captures date, but a LastEdit now() with timpstamp catches it all...) HTH, J.R. -Original Message- From: DePhillips, Michael P [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 10:02 AM To: mysql@lists.mysql.com Subject: dateTime vrs. Timestamp Hello, Am I gaining anything by using a timestamp instead of using dateTime and calling now(), for example, increased performance, better indexing, etc. I guess another way to ask this is does the MySQL internals handle a timestamp more efficiently than a dateTime. Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: Need Help with 813-MDB File
DB Tools software will convert the file for you. You can download it at http://dbtools.com.br/EN/index.php. All you have to do is download and install the FreeWare version and then use the TOOLS DAO Import Wizard. J.R. -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 29, 2005 11:29 PM To: mysql@lists.mysql.com Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: updating date fields using sql
Check your DATE format in your server variables... MySQL default date format is -MM-DD. You can change it, however until then, you have to use update policies set renewdate = '2006/02/21' where polnumber = 'WRIM01002'; HTH, J.R. snip Hello. This is a frequently asked question. For example read these threads: http://lists.mysql.com/mysql/175324 http://lists.mysql.com/mysql/177730 -- 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 /snip -Original Message- From: Mark Mchugh [mailto:[EMAIL PROTECTED] Sent: Thursday, March 24, 2005 9:22 AM To: mysql list Subject: updating date fields using sql Hi, Dont know whats happening here i'm using the following SQL update policies set renewdate = '21/02/2006' where polnumber = 'WRIM01002'; but, when i update the databse, the date sets to the year 2021 ? i've prob done something daft.. please help if you can... MArk __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: changing default date format on server
You can change it on the command line by mysql set date_format = '%m-%d-%Y'; However, this may be a client-only view, as I am still trying to get the global variable to change. J.R. PS - Sorry it took me so long Mark, was busy and AFK -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 24, 2005 10:57 AM To: Mark Mchugh Cc: mysql list Subject: Re: changing default date format on server Mark Mchugh [EMAIL PROTECTED] wrote on 03/24/2005 10:26:44 AM: hi, How can i change the default date field to european format, i.e. dd/mm/ ? thanks MArk __ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250 You don't. The server's internal representation of dates is not something you can modify. However, your _client_ may have many ways to format date data for viewing. How you change _what_you_see_ depends entirely on which tool you are using to get data from the server and present it for viewing or other operations. Refer to the documentation for the client you are using for details on how to get it to show you dates in the format you want to see. Shawn Green Database Administrator Unimin Corporation - Spruce Pine smime.p7s Description: S/MIME cryptographic signature
RE: remote connection problem
1) Are you running a firewall? If so, make sure that port 3306 is open. 2) Is your MySQL server is up and running? 3) Do you have the correct permissions to connect to the server via your mysql.hosts table? Just some thoughts. J.R. -Original Message- From: gerardo Villanueva [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 4:43 PM To: mysql@lists.mysql.com Subject: remote connection problem I have mysql version 4.0.15-nt in a server NT, I can connecting with mysql localy, but when i try remote connection the error is: Error Number 2003 Can't connect to MySQL server on 'IP' (10060) . I use mysql odbc 3.51 Is necesary the file my.cnf in c:\my.cnf Regards Gerardo Campos _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
RE: remote connection problem
When you are running as root, but are you running as 'root'@'%' or 'root'@'localhost'? This does make a big difference. The '%' means all network connections, which isn't secure. I would only using it as testing purposes. However, because your error message says that you cannot see the server, are you sure that your remote connection can see the NT server? This may be a bigger problem, i.e. DNS or IP routing/access lists. You may also want to check the hostname.err logs on the MySQL server to see if there is a connection problem there. J.R. -Original Message- From: gerardo Villanueva [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 5:03 PM To: [EMAIL PROTECTED] Subject: RE: remote connection problem 1.- Yes I have a firewall but the port 3306 is open 2.- Mysql is running in the server 3.- I connecting with user root and his has all the permissions I try to remote connection to my server NT 4.0 , mysql run there. Gerardo Campos _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com smime.p7s Description: S/MIME cryptographic signature
RE: Use MySQL with Microsoft Office
I know that this is a little off topic, but if you want a real Desktop Publishing Suite, try Adobe (InDesign, specifically). They cannot do MailMerges like in OpenOffice or M$, but it's much easier to use than M$ and looks a lot more professional. As a side note, OpenOffice, as of 1.1.2, does not have a Publisher type equivalent. It also does not have an Access equivalent, hence MySQL interoperability. J.R. -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 08, 2005 9:59 AM To: GH Cc: mysql@lists.mysql.com Subject: Re: Use MySQL with Microsoft Office From: GH Does Open Office have a MSPublisher like program? I'm not familiar with Publisher, but I gues that it is supposed to help you make publications in a kind of desktop pulishing way. In the article at http://www.newsforge.com/article.pl?sid=04/10/04/150207 the author compares Writer (the wordprocessing part of OpenOffice.org) with Adobe's Framemaker. Maybe that will help you a bit. It's worth mentioning that the current version OpenOffice.org 1.1.4 will soon be replaced by a major upgrade as the 2.0 beta has recently become available. On http://www.openoffice.org you can read all about the new features in this release. I use both OpenOffice.org and MS Office and both have their pros and cons. Regards, Jigal. -- 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]
Inserting Other Binary data into DB (NOT IMAGES)
Title: Inserting Other Binary data into DB (NOT IMAGES) Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. smime.p7s Description: S/MIME cryptographic signature
RE: Inserting Other Binary data into DB (NOT IMAGES)
Since this post, I have been able to enter a PDF file into the DB using MySQLCC / MySQL CLI and INSERT / UPDATE statements. Now my issue is the ASP / aspUpload. That is not appropriate for this list. Thanks anyways! J.R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 01, 2005 5:09 PM To: [EMAIL PROTECTED]; mysql Subject: Re: Inserting Other Binary data into DB (NOT IMAGES) Inserting Other Binary data into DB (NOT IMAGES)Inserting images in no different compared to inserting PDFs. What is your problem? What have you tried so far? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. smime.p7s Description: S/MIME cryptographic signature
RE: Unable to start MySQL
2 things -- 1) Permissions on your /mysql/data/ directory. User mysql needs to have ownership, group mysql needs to have ownership as well. shell groupadd mysql shell useradd -g mysql mysql shell chown -R root /path/to/mysql/. shell chown -R mysql /path/to/mysql/data/. shell chgrp -r mysql /path/to/mysql/. 2) Check the hostname.err file. It will tell you why you are getting those errors. More than likely the permissions. J.R. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 15, 2005 12:33 PM To: mysql@lists.mysql.com Subject: Unable to start MySQL Hello, I have been trying to install and run MySQL on a Redhat Enterprise machine. The installation appears to go just fine, but when I go to run it, MySQL doesn't startup. Here is what I have done so far: INSTALLATION: ./configure --with-openssl=/usr/local/ssl --with-openssl-includes=/usr/local/ssl/include --with-openssl-libs=/usr/local/ssl/lib --with-isam --with-archive-storage-engine --with-csv-storage-engine make make install /usr/bin/mysql_install_db --user=mysql After doing the above, I tried running MySQL with the following commands: /usr/bin/safe_mysqld /usr/bin/safe_mysqld --user=mysql Each of these resulted in this sort of response: --- [EMAIL PROTECTED] usr]# /usr/bin/safe_mysqld --user=mysql [1] 14204 [EMAIL PROTECTED] usr]# Starting mysqld daemon with databases from /var/lib/mysql 050215 11:31:25 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=mysql [EMAIL PROTECTED] usr]# --- Any assistance with this would be greatly appreciated. --- Thank You, Jason Williard -- 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: Where can I download The MySQL C API??
http://dev.mysql.com/downloads/ Please note the section of Official APIs # Application Programming Interfaces (APIs) * Official APIs: o The C API is included with the server, above. J.R. -Original Message- From: Shuva, Judith [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 8:30 AM To: mysql@lists.mysql.com Subject: Where can I download The MySQL C API?? Hi, I just downloaded mysql-essential-4.1.9-win32.msi, and as I understood, the C API supposes to be in here. Where exactly? I can't find it Thanks, __ Judith Shuva -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Where can I download The MySQL C API??
Here's a little more info for you. http://dev.mysql.com/doc/mysql/en/c.html J.R. -Original Message- From: Shuva, Judith [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 9:08 AM To: mysql@lists.mysql.com; J.R. Bullington Subject: RE: Where can I download The MySQL C API?? Hi J.R. Thanks for your answer. I saw that the API suppose to be in the MySQL server, so I downloaded mysql-essential-4.1.9-win32.msi, but after the installation, I can't find the API in the MySQL folder... Please, HELP! Thanks, Judith -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 3:55 PM To: mysql@lists.mysql.com Cc: Shuva, Judith Subject: RE: Where can I download The MySQL C API?? http://dev.mysql.com/downloads/ Please note the section of Official APIs # Application Programming Interfaces (APIs) * Official APIs: o The C API is included with the server, above. J.R. -Original Message- From: Shuva, Judith [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 8:30 AM To: mysql@lists.mysql.com Subject: Where can I download The MySQL C API?? Hi, I just downloaded mysql-essential-4.1.9-win32.msi, and as I understood, the C API supposes to be in here. Where exactly? I can't find it Thanks, __ Judith Shuva -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: adding a large file to a database....
I have found that MySQL really doesn't like the rs.Update and rs.AddNew
features like SQL and Access
do. You are better off with the INSERT INTO tblname (`rs.Flds`) VALUES
('objitem') or the UPDATE
tblname SET column1=value1,column2=value2,... statements with
CONPUBS.EXECUTE.
It's a pain to convert, but you will find that it writes faster to your MySQL
database and you have
less errors.
Just a little more info...
J.R.
-Original Message-
From: Mark Mchugh [mailto:[EMAIL PROTECTED]
Sent: Monday, January 24, 2005 11:31 AM
To: mysql list
Subject: adding a large file to a database
hi all,
I am trying to add a large file to my database, and it does not seem to work?
i am using the following code
Dim mystream As ADODB.Stream
Set mystream = New ADODB.Stream
mystream.Type = adTypeBinary
Set rs = New ADODB.Recordset
rs.ActiveConnection = connMySQL
rs.Open sqlstr, connMySQL, adOpenStatic, adLockOptimistic
rs.AddNew
mystream.Open
mystream.LoadFromFile strFileName
rs!file_name = CatDir
rs!file_size = mystream.Size
rs!File = mystream.Read
rs.Update
mystream.Close
rs.Close
when i try to add a file that is ( dont laugh) 1.5 megs or over, i get a
message saying lost
connection to mysql server during query
can anybody help?
thanks
__
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
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: Does MySQL support server with 2 cpu??
This is really based on your OS, not MySQL. If you install MySQL on Red Hat Linux Enterprise Symmetric Multi-Processing (SMP), then MySQL will use both CPUs based on instruction sets in the OS. If you install it on WinXP or Win2k3 server, they both have SMP and can support it. So the question is now turned to - What OS are you thinking of using it on? J.R. -Original Message- From: Scott Fletcher [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 11, 2005 9:25 AM To: mysql@lists.mysql.com Subject: Does MySQL support server with 2 cpu?? Hi! We wanna know is does MySQL support the use of the server with 2 cpu? I have trouble finding that on the mysql.com website. If so, how does the configuration for the use of 2 CPU work?? Thanks, Scott D. Fletcher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyODBC 3.51.10
You need to set the OLD_PASSWORDS variable in the [MYSQLD] section of the my.ini file. Set-variable = old_passwords=1 It's not the ODBC, but your version of MySQL. 4.0.x uses 1 variant of password hashing, 4.1.x uses a more secure one that will be covered in MyODBC 3.53 (coming soon...). J.R. -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 2:53 PM To: mysql@lists.mysql.com Subject: MyODBC 3.51.10 I somehow managed to download and install MyODBC 3.51.10 on my XP box, and it connects with no problems to my database server running version 4.1.7. However, it seems that MySQL is only allowing downloads for 3.51.9, and when I installed it on my Server 2003 and attempted to connect, I get the following error: Client does not support authentication protocol requested by server; consider upgrading MySQL client I can only assume it's because of the older driver? Best regards, Erich Beyrent Systems Administrator Information Technology Services Plymouth State University (603) 535-2948 [EMAIL PROTECTED] Si vis pacem para bellum -- 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: List for newbie
smime.p7m Description: S/MIME encrypted message
RE: installing mysql / error
This error is caused when the server isn't started. Make sure that you start the server first, then log in. When you skip the HOSTS.FRM tables (by doing mysqld --skip-grant-tables), you have to restart the server normally to effect your changes in the mysql root user. J.R. -Original Message- From: David Katz [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 10:30 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: installing mysql / error I did the first 2 step you listed below, but when I typed in the mysql -u root, I got a new error message ERROR 2003 (HY000) Can't connect to MySQL server on 'localhost' (10061) Thanks David. - Original Message - From: Tom Crimmins [EMAIL PROTECTED] To: David Katz [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 2:32 PM Subject: RE: installing mysql / error [snip] When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) [/snip] In that case, you may have a password set for root. To reset the password, shutdown the mysql server. Then start it from a command line using: [PATH TO MYSQL BINS]/mysqld-nt --skip-grant-tables Then open another command prompt and run 'mysql -u root' and run the following query: UPDATE mysql.user SET Password='' WHERE User='root' AND Host='localhost'; Then shutdown the server, and restart it normally. You should then be able to connect with 'mysql -u root'. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa office 712.328.4808 mobile 402.677.1592 -Original Message- From: David Katz [mailto:[EMAIL PROTECTED] Sent: Thursday, December 30, 2004 1:25 PM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: installing mysql / error When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) - Original Message - From: Tom Crimmins [EMAIL PROTECTED] To: David Katz [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 1:57 PM Subject: RE: installing mysql / error [snip] We loaded a new server with Windows XP professional, and mysql server 4.1. We can't get Mysql to run. After installing mysql and trying to execute it we get the following message: ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using password: NO) [/snip] Ignore my previous post, I misunderstood. ODBC is the default account on a windows server. You need to use run 'mysql -u root' from a command-line. You should then be able to grant privs to [EMAIL PROTECTED] --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- 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: No connect to mysql
The reason that you cannot connect is that your MySQL server is not running. Make sure that you have run `#/usr/local/mysql/bin/safe_mysqld ` First and then try to run mysqladmin. The other option is to run `#/usr/local/bin/mysqladmin status` to check to see if the server is running. J.R. -Original Message- From: sasa [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 14, 2004 6:42 AM To: [EMAIL PROTECTED] Subject: No connect to mysql Hi, I have a problem with mysql 4.0.22 on fedora core 2. ..when I try: #/usr/bin/mysqladmin -u root password '' /usr/bin/mysqladmin: connect to server at 'localhost' failed error: Can't connect to local MySQL server through socket '/var/lib/mysql.sock (2)' Check that mysql is running and that the socket: '/var/lib/mysql.sock' exists ! .. the file mysql.sock not exists but I can to resolve ? .. thanks and sorry for my banal question. Salvatore. -- 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: Deleted: 0 Skipped: 0 Warnings: a lot
Command is SHOW WARNINGS; You can run this from either the command line or from the MySQL CC SQL viewer. J.R. -Original Message- From: kalin mintchev [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 6:53 PM To: [EMAIL PROTECTED] Subject: Deleted: 0 Skipped: 0 Warnings: a lot hi all... where can i see what these warnings are. i get them when i do load data infile. i was looking for some kind of log under /var/logs/ or mysql/var/log but there isn't a log file for mysql at any of those locations. and the mysql/var/ doesn't exist at all. i created it and redid the load but still no log file.. is there a way to read the binary log files... thanks... -- -- 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: Deleted: 0 Skipped: 0 Warnings: a lot
I have never had a problem running that command, however you do have to run it right after you run your query (or LOAD DATA). That may be why you can't use it. Also, try looking under HOSTNAME.ERR instead of a log file. That might help out. J.R. -Original Message- From: kalin mintchev [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 7:29 PM To: J.R. Bullington Subject: RE: Deleted: 0 Skipped: 0 Warnings: a lot Command is SHOW WARNINGS; You can run this from either the command line or from the MySQL CC SQL viewer. thanks.. but from the command line i get: Check the manual that corresponds to your MySQL server version for the right syntax to use near 'warnings' at line 1 is mysql-standard-4.0.13 to old for show warnings!? J.R. -Original Message- From: kalin mintchev [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 6:53 PM To: [EMAIL PROTECTED] Subject: Deleted: 0 Skipped: 0 Warnings: a lot hi all... where can i see what these warnings are. i get them when i do load data infile. i was looking for some kind of log under /var/logs/ or mysql/var/log but there isn't a log file for mysql at any of those locations. and the mysql/var/ doesn't exist at all. i created it and redid the load but still no log file.. is there a way to read the binary log files... thanks... -- -- 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] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL configuring on Linux
Windows has always been more graphical. For Linux, depending on the version that you have downloaded (binary v. source) you have to do very little to configure it for a dedicated server. Check out the my.cnf files in the /support-files/ folder of your MySQL installation. Use these as a guideline. The more RAM you have or the more dedicated you want the server, the different my.cnf files that you want to copy to /etc/my.cnf For Small Servers (256 - 512 RAM) or Development, use: shell cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf For Medium Servers (512 - 1GB RAM) or Server, use: shell cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf For Large Servers (1GB - 2GB RAM) or Dedicated, use: shell cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf For Extra Large Servers ( 2GB RAM ) running only MySQL, use: shell cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf Each of these can be found in the my-(small, medium, large, huge).cnf files. They are commented out sections. There is nothing new that you need to download. Also, make sure you read the manual and installation instructions. All of this information and more is located in there. J.R. -Original Message- From: Danesh Daroui [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 10:18 AM To: [EMAIL PROTECTED] Subject: MySQL configuring on Linux Hi all, I have installed MySQL Server 4.1.7 on both my Windows XP and Linux Fedore Core 3 systems. It was amazing that everything can be configured in a visual way and it is almost ready to use after installation when install it on Windows XP and I could even change the way thatI want to use MySQL server if it is Developing, Sever or Dedicated Server. But when I installed MySQL Server 4.1.7 on my Linux system, I couldn't configure it as Windows XP. It just installed and there was not any configuration window or anything elase. How can I configure my Linux server for example to act as Dedicated server ? Is there any extra program for Linux so I have to download ? Thanks, Danesh Daroui -- 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 configuring on Linux
This error results from Berkley Databases (bdb) not being enabled. This is normally OK (unless you want to use them), but it seems as though your have it set in your my.cnf file to have them enabled and not in MySQL. Go back into your my.cnf file and put a '#' in front of all the bdb and innodb configuration options. Secondly, the socket will be missing as it is a temp. file that is created and deleted whenever the server is started or shut down. It will be recreated when the server is restarted. J.R. _ From: Danesh Daroui [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 1:30 PM To: J.R. Bullington Subject: Re: MySQL configuring on Linux I just did it and configured it to be a Server but after all when I restarted mysqld I got this error: shell Can't connect to local mysql server throught socket 'var/lib/mysql/mysql.sock' I also checked and socket files has been deleted. There was aölso an error log and there was: unknown argument bdb_cache_size=4 MB or something like that ? How can I re-generate socket file ? Why the original configuration for Server has errors in it ?
RE: ODBC connection
It is my Digital Signature. Sorry that it didn't come through.
J.R.
-Original Message-
From: Ferguson, Michael [mailto:[EMAIL PROTECTED]
Sent: Friday, November 12, 2004 11:19 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: ODBC connection
Thanks. I will give it a try. I also saw a reply from a J. Bullington but I
cannot open it due to some underlying security stuff.
Thanks though
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, November 12, 2004 11:05 AM
To: Ferguson, Michael
Cc: [EMAIL PROTECTED]
Subject: Re: ODBC connection
The MySQL ODBC driver v3.51 acts as a pre-4.1 MySQL client. That
means it does not use the new (v4.1+) password hashing.
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
http://dev.mysql.com/doc/mysql/en/Access_denied.html
http://dev.mysql.com/doc/mysql/en/Old_client.html
I would create a new user just for your ODBC connection.(Use a
GRANT statement with all of the correct bells and whistles for your
situation)
http://dev.mysql.com/doc/mysql/en/GRANT.html
GRANT SELECT on *.* to 'remoteuser'@'%'
Then reset the password to the OLD hashing style
UPDATE mysql.user set
password=OLD_PASSWORD('plaintextpassword) where user='remoteuser';
FLUSH PRIVILEGES;
My example creates a pre-4.1 user account on your 4.1+ server for
the user 'remoteuser' with read-only rights to all databases on your server
with permission to login from anywhere. Adjust it to suit your situation.
One other gotcha that I had to figure out on my own... I had to
declare an initial database in my ODBC connection (DSN). Without it my
connections kept failing (sorry but I don't remember the exact message) even
when everything else was fine.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Ferguson, Michael [EMAIL PROTECTED] wrote on 11/12/2004
10:30:14 AM:
G'Day All,
I am going nuts trying to setup ODBC to my MySQL database.
Help Please.
My workstation is WinXP Pro. I downloaded the MySQL ODBC 3.51,
unzipped
it and am trying to configure it to connect to the database on a
RedHat
ES box.
In the Add Data Source Name my DSN is closing Description is
TCAM
server is xxx.xxx.xxx.xxx local ip address for my server. I
enter the
user and password and I keep getting the failure
[MySQL][ODBC 3.51 Driver]Access denied for user
'root'@'myworkstationname.domainname.com' ( using
password:YES)
I am stuck. Help please. Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error connecting to mysql db
This is usually one of two things... 1) MySQL is not running on that machine 2) Permissions for accessing the socket via PHP. More likely this first, but try both. Check you [HOST].err file to be sure. J.R. -Original Message- From: Ferguson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 11:57 AM To: [EMAIL PROTECTED] Subject: Error connecting to mysql db G'Day All, When I try to connect to my mysql/php through Apache I get the following error: Warning: mysql_pconnect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) in /var/www/html/taxes/db_mysql.php on line 99 Database error: pconnect(localhost:3306, root, $DBPassword) failed. MySQL Error: 0 () Session halted. Can someone please help me to determine what's wrong here. Thanks very much Ferg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
FW: Error connecting to mysql db
This is usually one of two things... 1) MySQL is not running on that machine 2) Permissions for accessing the socket via PHP. More likely this first, but try both. Check you [HOST].err file to be sure. J.R. -Original Message- From: Ferguson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 11:57 AM To: [EMAIL PROTECTED] Subject: Error connecting to mysql db G'Day All, When I try to connect to my mysql/php through Apache I get the following error: Warning: mysql_pconnect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) in /var/www/html/taxes/db_mysql.php on line 99 Database error: pconnect(localhost:3306, root, $DBPassword) failed. MySQL Error: 0 () Session halted. Can someone please help me to determine what's wrong here. Thanks very much Ferg -- 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: table size WAS RE: optimizing database
I have multiple databases running tables with thousands of records in them. Some of my tables have as many as 130 million records in them. Memberships and patient data can easily run from thousands to tens of thousands of records. If you are looking into things like DNA/Genome mapping, you can easily run into billions of records. J.R. -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 3:41 PM To: [EMAIL PROTECTED] Subject: OT: table size WAS RE: optimizing database Razor Fish mailto:[EMAIL PROTECTED] on Thursday, October 21, 2004 3:19 PM said: i need consulting help for optimizing a database with 1.2 million records to handle 3-4 million hits a day. this is going to be a 'duh' question on my part but i just had to ask anyway. my largest table probably has 700 records in it. what the heck kind of data is being stored where it reaches the millions (or more)? chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL v ASP problem
I have searched high and low and I know that you guys can help out ( as you
have helped me before ).
First, I will list table descriptions, then the problem, then I will list
the code, and finally the permissions.
The Descriptions:
2 tables - both MyISAM.
Table 1 has 9 fields, 1 index PID (PK, Index);
Table 2 has 36 fields, 1 index EcnID (PK, index). PID is a FK in
Table 2;
ASP and IIS 6 on a Win2k3 server
MySQL 4.0.20 on a Linux RHEL AS
The Problem:
I am trying to do an rs.update using ASP. In Table 1, code works
perfectly, retrieves and updates without issue. In Table 2, same code,
doesn't work. NOTE: I have to use rs.update and not UPDATE tablename SET
... due to the large amount of data that needs to be pushed. I get the old
Query-based update failed because the row to update could not be found.
So here we go with the code:
BEGIN NECESSARY CODE
*
%
Dim Conn
Conn = DRIVER={MySQL ODBC 3.51 Driver}; SERVER=IPADDRESS;_
'MyODBC driver is 3.51.9
DATABASE=DBNAME; UID=UID;PWD=PWD; OPTION=3
'
dim resdata(36), resflds(36), reschks(36), resnote(36), resfrmt(36), errtext
Set rs = Server.CreateObject(ADODB.Recordset)
'
if request.querystring(EcnID) then
session(EcnID)=request.querystring(EcnID)
** CODE JUMP **
'
if request.form(B1)=Exit Without Changes then
response.redirect(SOMEOTHERPAGE.ASP) 'Handle bail-out
'
if request.form(B1)=COMPLETE REVISION then
' START HERE TO STORE UPDATES
'
vararray=session(resdata)
'retrieve the session data provided by
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
sql = Select * from tblEncounter where EcnID= session(EcnID)
response.write sql
response.flush
rs.Open sql, conn,3,3
'
For Each objItem in request.form
'look at form field
for x=0 to ubound(resflds)
'search all the field name array
if ucase(objitem)= ucase(resflds(x)) then
'update the data
resdata(x)=request.form(objitem)
'resdata array now contains newest data
end if
next 'x
next 'objitem
rs(Compdate)=now()
'
rs.update
response.redirect(SOMEOTHERPAGE.ASP)
end if
'
if request.form(B1)=Update Information then
' START HERE TO DO UPDATE / ERROR CHECK
'
vararray=session(resdata)
'retrieve the session data provided by the
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
CODE JUMP *
Else
' START HERE FOR NEW DATA PULL-UP
'
dim resname
dim rs
dim sql
sql = Select * from tblEncounter where EcnID=session(EcnID)
rs.Open sql, conn
rs.MoveFirst
'
x=0
for each fld in rs.Fields
'Load RS into an session array
resflds(x) = fld.name
'Load field names from database
resdata(x) = fld.value
'Initialize the fields to null
reschks(x) = 1
'set field status to good
resnote(x) =
'set field comment to null
x=x+1
next
'
rs.close
'
session(resdata)=resdata
session(resflds)=resflds
session(reschks)=reschks
session(resnote)=resnote
end if
'
%
*** END NECESSARY CODE **
Now, permissions:
MySQL - FULL CONTROL FOR THIS DATABASE ( ALL PRIVLIGES WITH GRANT OPTION )
IUSR - Read, Read Execute, Write, Modify, List Folder Entries
Please, if any help can be offered I would greatly appreciate it. If you
need anything else from me, please don't hesitate to ask!
TIA
J.R.
smime.p7s
Description: S/MIME cryptographic signature
RE: mysql_install_db problem
The easy way around this is to change your hostname to 'localhost,' install the scripts, and then change your hostname back. There are other ways to fixing this, but that's the fastest, I've found. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alter Table question
Alter table 'tablename' change column 'columnname' 'new-columnname' not null default 0 If you have any null values in that column, you will not be able to make the change. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Converting from MS SQL Server to MySQL
The *best* way to do this is via conversion software. I find that for table creation and data transfer, http://dbtools.com.br has the best free tool. It converts all your Access and SQL based databases over to MySQL without any hitches. Really good help files and web site as well. Intelligent Converters also has a good tool, but it's not free (http://convert-in.com) J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fun Query with Question
Greetings! I have a query that I would like to refine a little more. I am using this to send email to a directory with so many users in it that it causes my SMTP server to clog up. I want to break up the database send module so that it sends to only last names with the letters A - K first, do a pause, and then send the email to letters L - Z. Any help would be appreciated! Here's the original query (please ignore the session(MemberType), as it is a session variable chosen from a screen prior): select last, email from tblMembers where email is not null and MemberDesc session(MemberType) Thank you for your help in advance! J.R.
RE: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
The next step is to shutdown mysqld, delete the mysql and temp tables from your /data/ folder, and then re-run the scripts/mysql_install_db. That will reinstall the mysql/hosts.frm, which will basically reset your root user and password. However, it will also kill any users you have created. To reset the root password without harming other mysql users: Shell kill cat /path/to/mysql/data/hostname.pid Shell ./bin/mysqld_safe --user=mysql --skip-grant-tables Shell mysqladmin -u root password 'newpassword' Shell mysqladmin shutdown Then restart the mysqld like normal. Hope this helps! J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
