Update log
i'm still using 3.23 can anyone show me how to activate update log by modifying the my.cnf/ my.ini And a question about update log, does it only record update and delete (mysql documentation), how about insert? do the log recorded those too? thanks __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: first time accessing
Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Error: tablespace size stored in header is 17024 pages,
Ian, - Original Message - From: Ian Grant [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 05, 2005 4:47 PM Subject: Re: InnoDB: Error: tablespace size stored in header is 17024 pages, Dear Heikki Many thanks for your reply. On Mon, 3 Jan 2005 20:41:22 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: are you sure that you copied the complete ibdata1 file to the new place? It is strange how 7 MB can be missing from the file end. I have just checked the tar file I used to do the transfer and sure enough, the file length is right there, but not in my database directory. Obviously I didn't notice any error when I unpacked it, but it is definitely short now. hmm... a bug in tar? ... Then I added it to the start of the tablespace path in my.cnf by changing innodb_data_file_path = ibdata1:10M:autoextend to innodb_data_file_path = ibdata2:7M;ibdata1:10M:autoextend You have to add new data files to the end of the line, not to the start. Please follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Then I shut down: 050105 14:25:57 [Note] /local/own-1/rt/rt-3.2.2/libexec/mysqld: Normal shutdown 050105 14:25:57 InnoDB: Starting shutdown... 050105 14:26:01 InnoDB: Shutdown completed; log sequence number 0 407778847 050105 14:26:01 [Note] /local/own-1/rt/rt-3.2.2/libexec/mysqld: Shutdown complete 050105 14:26:01 mysqld ended ... and restarted MySQL but I got an error: 050105 14:26:04 mysqld started 050105 14:26:05 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050105 14:26:05 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 407778847. InnoDB: Doing recovery: scanned up to log sequence number 0 407778847 InnoDB: Page directory corruption: supremum not pointed to followed by a page dump and a stack trace. I removed the entry for the new file in the data file path and it has started as it did before. Have I misunderstood your instructions? Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update log
one more question, how about when i'm using 'BEGIN' and doesn't end it with 'COMMIT' neither 'ROLLBACK' what will happen. I've tried before and nothing happen but does anyone have an opinion? --- Chenri J [EMAIL PROTECTED] wrote: i'm still using 3.23 can anyone show me how to activate update log by modifying the my.cnf/ my.ini And a question about update log, does it only record update and delete (mysql documentation), how about insert? do the log recorded those too? thanks __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
adding automatic alert
Hi all, I'm make an inventory goods database, in one of my table I need to make an automatic alert when my stock reach it's limit (say for tires it's limit is 4), can I make an automatic alert for it so when it reach it limit I can have an alert may be a beep ? Thanks __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Retrieving partial field values
This sounds like a good candidate for the two-table model: Table 1 contains an ID and the basic names. Table 2 contains the 1-many relationship of basic name entry mapped to all the variants - one record per variant. So you might have Table 1 containing ID, Prefix, GivenNames, Surname, Suffix and Table 2 containing ID, Type, AlternateName where Type is one of Prefix, GivenNames, Surname, Suffix. example: Table 1: ID Prefix GivenNames Surname Suffix 123 Miss Mary Elizabeth StottsIII Table 2: ID Type AlternateName 123 Prefix Miss 123 Prefix Ms. 123 GivenNames Mary Elizabeth 123 GivenNames Marg Elizabeth 123 GivenNames Mary Ellen 123 GivenNames Marg Ellen 123 Surname Stotts 123 Surname Stitts 123 Surname Stutts Note that you include all alternatives, including the default ones, for ease of constructing queries. Queries would be constructed in terms of a join based on ID: select Table2.AlternateName from Table1,Table2 where Table1.ID = Table2.ID and Table2.Type = GivenNames and Table1.ID = '123' Hope this helps. - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding automatic alert
Aji Andri wrote:
Hi all,
I'm make an inventory goods database, in one of my
table I need to make an automatic alert when my stock
reach it's limit (say for tires it's limit is 4), can
I make an automatic alert for it so when it reach it
limit I can have an alert may be a beep ?
This is a typical task for the programming language/tool you are using
to create your application. What you ask may look a bit like a task for
a trigger, but triggers work serverside, and I assume you want the beep
on the client. I really think you would be better of just using a simple
script, running every 30 minutes or so. Something like this (meta code):
cnt = select stock from goods where goodstype='tires'
if (cnt 5):
beep()
send_email('[EMAIL PROTECTED]','Out of tires!')
(This script will of course keep beeping/sending emails every 30 minutes
until you stop the script or increase the registered stock above the
limit... )
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding automatic alert
Roger Baklund [EMAIL PROTECTED] wrote on 06/01/2005 12:25:31:
Aji Andri wrote:
Hi all,
I'm make an inventory goods database, in one of my
table I need to make an automatic alert when my stock
reach it's limit (say for tires it's limit is 4), can
I make an automatic alert for it so when it reach it
limit I can have an alert may be a beep ?
This is a typical task for the programming language/tool you are using
to create your application. What you ask may look a bit like a task for
a trigger, but triggers work serverside, and I assume you want the beep
on the client. I really think you would be better of just using a simple
script, running every 30 minutes or so. Something like this (meta code):
cnt = select stock from goods where goodstype='tires'
if (cnt 5):
beep()
send_email('[EMAIL PROTECTED]','Out of tires!')
(This script will of course keep beeping/sending emails every 30 minutes
until you stop the script or increase the registered stock above the
limit... )
Or, more generally, add a column min_stock to the table so that it will
check all lines:
cnt = select stock from goods where stock min_stock ;
if (cnt 5):
beep()
send_email('[EMAIL PROTECTED]','Restock needed for items')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding automatic alert
- Original Message - From: Aji Andri [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 06, 2005 5:16 AM Subject: adding automatic alert Hi all, I'm make an inventory goods database, in one of my table I need to make an automatic alert when my stock reach it's limit (say for tires it's limit is 4), can I make an automatic alert for it so when it reach it limit I can have an alert may be a beep ? The functionality you are describing is normally accomplished via a technique called a 'trigger'. MySQL is supposed to support triggers beginning in Version 5. An alpha of Version 5 is currently available for download but I'm not sure if it is mature enough for you to consider in a production environment. Also, I don't know if triggers are actually present in the Version 5 code and are working. Someone who has used Version 5 may be able to tell you that. There may be other ways to accomplish the same functionality in MySQL 3.* or 4.*. I'll leave it to others to tell you if they have thought of ways to do that. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update log
Chenri J wrote: i'm still using 3.23 can anyone show me how to activate update log by modifying the my.cnf/ my.ini Show you? It is not clear what you want. Do you need help starting the editor? What OS are you using? On Windows systems Notepad can be used to edit the configuration files. Which 3.23.x are you using, what is the last digit in the version number? And a question about update log, does it only record update and delete (mysql documentation), how about insert? do the log recorded those too? URL: http://dev.mysql.com/doc/mysql/en/Update_log.html URL: http://dev.mysql.com/doc/mysql/en/Binary_log.html I don't think you should use the update log, you should use the binary log, even if you use version 3.23.x. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BEGIN...nothing? (Re: Update log)
Chenri J wrote: one more question, how about when i'm using 'BEGIN' and doesn't end it with 'COMMIT' neither 'ROLLBACK' what will happen. I've tried before and nothing happen but does anyone have an opinion? This depends on a number of things: If the table handler in question does not support transactions (like MyIsam), all statements are commited as they are executed, regardless of BEGIN/COMMIT/ROLLBACK (those are just ignored by the table handler). If the table handler supports transactions, then if the client disconnects or the server goes down, a ROLLBACK is effectively executed serverside, all changes are ignored by the server. If the server is still up, and the client is still connected, well, then the transaction is still active, and it is not yet defined if the changes will be commited or not... this is a bit like Schroedingers cat... ;) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
matt_lists wrote: Raj Shekhar wrote: Mark Maggelet wrote: Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Not sure if this would apply in your case but have a look at the discussion on http://bugs.mysql.com/bug.php?id=563 . Some useful hints provided there. Regards I have the same problem, with 4.1.9 always the same table, very simple setup every day 5 times the table has a set of records deleted, and then reloaded from the text files we recieve from the old mainframe delete from mrf where site='site1' then load data concurrent infile blah blah for 5 separate sites that's it, it's queried at a low load via websites every few days I get one of these MySql: Can't open file: 'mrf.MYI' (errno: 145) For more information, see Help and Support Center at http://www.mysql.com. Checking table: './db/mrf' For more information, see Help and Support Center at http://www.mysql.com. Recovering table: './db/mrf' For more information, see Help and Support Center at http://www.mysql.com. always recovers fine I guess, nothing gets wrote to the .err file 3 more of the same table crashing last night -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving partial field values
A full-text search won't work. Too many records -- at least 8 million. From what I've read, full-text won't pick up the individual initials or very short names, like Vu, Lee, Doe, etc. Jigal van Hemert [EMAIL PROTECTED] wrote: Example a (GivenNames): Mary Elizabeth or Marg Elizabeth Example b (GivenNames): J. W. or I. W. Example c (Surname): Stotts, Statts or Stutts I need to be able to retrieve the following based upon the examples: For Example a: Return Mary Elizabeth where GivenNames begins with M; Return Marg Elizabeth where GivenNames begins with M; Return Mary Elizabeth where GivenNames contains the whole word Mary; Return Marg Elizabeth where GivenNames contains the whole word Marg; Return Mary Elizabeth where GivenNames=Mary Elizabeth Return Marg Elizabeth where GivenNames=Marg Elizabeth I'm beginning to think that a form of FULL TEXT search is what you need; take a look at these articles: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html and even query expansion might be usefull in your case (though it might be a bit slow): http://dev.mysql.com/doc/mysql/en/Fulltext_Query_Expansion.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? All your favorites on one personal page Try My Yahoo!
RE: first time accessing
[snip] DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 [/snip] I would try your connection from a command line ie. 'mysql -u Jerry -h GJWPP88 -p'. The problem is now not with your perl, it is your mysql user permissions. From your error, I see that mysqld is running on your local machine. In this case you should use localhost instead of the actual hostname ie. 'mysql -u Jerry -h localhost -p' (you can omit the host on this one, it is default). If this works, you can change the host in your perl script to localhost, and you should be ready to go. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Thursday, January 06, 2005 5:25 AM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving partial field values
From: Kentucky Families A full-text search won't work. Too many records -- at least 8 million. From what I've read, full-text won't pick up the individual initials or very short names, like Vu, Lee, Doe, etc. You can set the ft_min_word_len in an option file in the [mysqld] section to change the minimum word length (rebuild the full text indexes afterwards!) Maybe alter the data a bit? If you store only firstnames you can search for J* to pick up an initial. If you only know initials simply use Jxzx as a first name (and let your application filter out the xzx part) If a name is shorter than four letters add xzx to the name and filter out in the application that shows the data. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving partial field values
From: Kentucky Families A full-text search won't work. Too many records -- at least 8 million. From what I've read, full-text won't pick up the individual initials or very short names, like Vu, Lee, Doe, etc. You can set the ft_min_word_len in an option file in the [mysqld] section to change the minimum word length (rebuild the full text indexes afterwards!) Maybe alter the data a bit? If you store only firstnames you can search for J* to pick up an initial. If you only know initials simply use Jxzx as a first name (and let your application filter out the xzx part) If a name is shorter than four letters add xzx to the name and filter out in the application that shows the data. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving partial field values
After hearing both sides being suggested, I think I would recommend the more normalized approach of having one or more separate tables for the names (one name per row) and a mapping table that associates a name field (mother's maiden name, decedent's given name, decedent's surname, etc.) on a record (marriage, birth, death, etc.) to the possible names the transcriptionist thinks were written on the record. A simple index would very quickly find all full matches and partial beginning matches. With mid-word or word-ending matches**, the engine will still need to scan the entire list of names (not every name in the database, just the unique list of spellings which will be much smaller) . This (the multi-table design) may add a little complexity to your GUI code but I think that the performance improvement on the searches will more than offset a little extra effort added to the front end and data entry portions of your process. Shawn Green Database Administrator Unimin Corporation - Spruce Pine ** You could optimize word-ending searches by storing a copy of the name reversed. Then a regular index could also be used to quickly find the end of a name. Lets say you want to find all of the names that end in esh... You have names like Abesh, Janesh, and Phresh. If you added another column (indexed of course) to your names table that stored the names as hsebA, hsenaJ, and hserhP a search using LIKE 'hse%' would use that index where a search using LIKE '%esh' could not. The trade off is that you nearly double the storage size of your names tables (one extra column plus its index) but you seriously improve search performance for an entire class of queries (names ending with ). Jigal van Hemert [EMAIL PROTECTED] wrote on 01/06/2005 09:30:08 AM: From: Kentucky Families A full-text search won't work. Too many records -- at least 8 million. From what I've read, full-text won't pick up the individual initials or very short names, like Vu, Lee, Doe, etc. You can set the ft_min_word_len in an option file in the [mysqld] section to change the minimum word length (rebuild the full text indexes afterwards!) Maybe alter the data a bit? If you store only firstnames you can search for J* to pick up an initial. If you only know initials simply use Jxzx as a first name (and let your application filter out the xzx part) If a name is shorter than four letters add xzx to the name and filter out in the application that shows the data. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving partial field values
On Wed, 5 Jan 2005 08:19:07 -0800 (PST) Kentucky Families [EMAIL PROTECTED] wrote: This is a huge database so the option of using LIKE to bring up everything beginning with the search term will result in too many hits. I need a way to isolate these entries and search them on whole words. If you have control over input format then you could store multiple values as say, ':Stott::Statt::Stutt:' and then search thus: SELECT * FROM people WHERE surname LIKE '%:statt:%' which will only match whole words. Better would be regular expressions capable of matching word boundaries, like perl's \b and \B patterns, but MySQL doesn't do that AFAIK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Error: tablespace size stored in header is 17024 pages,
On Thu, 6 Jan 2005 11:30:33 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: innodb_data_file_path = ibdata2:7M;ibdata1:10M:autoextend You have to add new data files to the end of the line, not to the start. Please follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Thanks. I realised this when I woke up in the middle of the night! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with php mysql reinstall. Redhat Linux
Hi folks, A little background. Around Christmas, my Redhat linux box was hacked. I'm not sure what the hacker did other than look around and install trojans all over the place and somehow the system became unbootable. All my data files appeared to be OK and I had them backed up anyway. I'm certainly not a Linux expert but I'm not a newbie eithersomewhere in between I guess. Anyway I decided to reinstall Linux from the distro. Fortunately my /home directory was on a separate partition so I didn't have to worry about rebuilding all that stuff. Also my etc directory was backed up so I was OK there too. My system is pretty much back to where it was before the hack. Apache seems fine, mysql seems fine and php is MOSTLY fine. However, any of my php scripts that use mysql will not run. I ran a phpinfo() and in the dbx section it states in the Supported Databases row: MySQLbr /ODBCbr /PostgreSQLbr /Microsoft SQL Serverbr /FrontBase The message I get when I run my php script is: Fatal error: Call to undefined function: mysql_connect() in /pub/address/index.php on line 58 It appears that I still have some more configuration or recompiling to do. Can anybody help with this? Thanks in advance, Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing the worst InnoDB corruption bug in 3 years - when
Hi Bruce SNIP - Original Message - From: Bruce Dembecki [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 2:51 AM Subject: Re: Fixing the worst InnoDB corruption bug in 3 years - when As a side note with demonstrated performance increases when using innodb_file_per_table why aren't more people using it? Best Regards, Bruce /SNIP What demonstrated performance increases are you referring to? I would love to use file_per_table, but as it is new we are very conservative with our production DB's and this feature is too new. But if there are demonstrated performance increases we would love to start using it! Thanks, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data Dictionary
how do I access the Data Dictionary through SQL? What I want to do is loookup the length of a varchar for validation? Ben -- Ben Edwards - Poole, UK, England WARNING:This email contained partisan views - dont ever accuse me of using the veneer of objectivity If you have a problem emailing me use http://www.gurtlush.org.uk/profiles.php?uid=4 (email address this email is sent from may be defunct) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: in regards to merge table in MySQL 4.1.x
Hello. Have you been at: http://dev.mysql.com/doc/mysql/en/MERGE_storage_engine.html JiSang YUN [EMAIL PROTECTED] wrote: Hi, list i'd like to know about that merge table type. what i get to the best benefits, when i use this engine type? thank you. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot create Windows service for MySql. Error: 0
Hello. While I am trying to install MySql Server 4.1, What exact version do you install? Use 4.1.8. Did you stop the previous instance of MySQL (if you have such one)? Please close all the executable files and the applications that uses MySQL's dlls. Can you start MySQL from Windows Command Line? Eugenia Mariani [EMAIL PROTECTED] wrote: My O.S in Win Xp Pro SP2. My web server is Apache 5.0 While I am trying to install MySql Server 4.1, I have the following error and I cannot install the Server: Cannot create Windows service for MySql. Error: 0 Can someone help me to install without error? Thanks Eugenia Mariani _ Ricerche online pi? semplici e veloci con MSN Toolbar! http://toolbar.msn.it/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Table Name Completion has stopped working?
Hello. Try 'rehash' command. Kieran Kelleher [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: US-ASCII, 15 lines --] Normally, when you type the first few letters of a table name and press tab, the mysql client does not auto complete anymore. Can this be enabled with a my.cnf setting or something since it is no longer the default behaviour on my machine? Regards, Kieran Dev Config = OS X 10.3.5 / Java 1.4.2_05 / WO 5.2.3 / XCode v1.5 / MySQL 4.0.20 / Connector-J 3.0.11 Deploy Config = OS X 10.3.7 Server / Java 1.4.2_05 / WO 5.2.3 / MySQL 4.0.20 / Connector-J 3.0.11 My Blog: http://webobjects.webhop.org/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FLUSH syntax query
Hello. Also, from which version of MySQL LOCAL and NO_WRITE_TO_BINLOG options supported? 4.1.1 RESET MASTER and RESET SLAVE were named FLUSH MASTER and FLUSH SLAVES before MySQL 3.23.26. Karam Chand [EMAIL PROTECTED] wrote: Hello, From the MySQL docs: http://dev.mysql.com/doc/mysql/en/FLUSH.html What does FLUSH MASTER and FLUSH SLAVES does? Its not documented? Also, from which version of MySQL LOCAL and NO_WRITE_TO_BINLOG options supported? Regards, Karam __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble w/ mysqldump
Hello. Right, three is a true dump file, and I can restore the database, everything 'seems' to be there, So, as I understand, the only thing that were confusing you, was the small dump file? If you see binary files after restoring from the dump file - they are in the dump file. I think that clues are in the bulk inserts which are tighter than ordinary. you [EMAIL PROTECTED] wrote: Right, three is a true dump file, and I can restore the database, everything 'seems' to be there, I can browse the table all day long, and I can see the binary files in the database, but the length of the tables are different. mysql@lists.mysql.com In a message dated 1/5/2005 3:50:39 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: Hello. The actual tables are there I assume that 'there' is a dump file. One more question, can you select the Avatars data using SQL queries? For example: SELECT 'Avatar_field' from 'avatar_table' limit 1; Does your application work after upgrade? [EMAIL PROTECTED] wrote: Thanks for the reply. My original install was w/ an RPM from the Redhat Network, I uninstalled all RPM's associated w/ MySQL, Apache, and PHP and downloaded the latest of everything and compiled it. After I did an updatedb on my box, I searched for mysqldump and mysql to confirm it was gone -- nothing was found. ./mysqldump Ver 10.9 Distrib 4.1.8a, for redhat-linux-gnu (i686) The actual tables are there, but the images (or any attachments) are really messed up. Looks like something from the 60's, lol. In a message dated 1/4/2005 9:36:01 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: Hello. In what way have you upgraded? How do you dump the data? Is everything fine, execept the database dump? As of MySQL 4.1, --opt command line option is on by default, so it can produce a smaller output. The definions of tables which stores Avatars are also absent in dumps? May be you use mysqldump from old 3.23 version? [EMAIL PROTECTED] wrote: Hi All, I run a website, and am having a problem w/ corrupt databases.. I was runnig MySQL v3.23 previously, and my db dumps worked fine .. However, I have since upgraded to v4.1.8a-log and I am having problems getting completed (or non corrupt) backups.. The problem is pretty big.. My Original Backup of the DB using 3.23 was 208MB --- When I upgraded to 4.18 the backup is only 138MB ... My users Avatar's, all attachments, etc are totally whacked out. Does anyone have any insight on what is causing this, and how to fix it? Any help is appreciated.. I need a new good backup desperately -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Retrieving partial field values
Hi, Full text search should be fine with 8 million records. These are short records too, so there shouldn't be any problem at all. Also, you can configure the minimum word length in the my.cnf file, so if you want it to find short words, you can. I've got it set to two in one of my applications. Note you have to delete the index and rebuild it if you change this value. Best regards, Andy -Original Message- From: Kentucky Families [mailto:[EMAIL PROTECTED] Sent: 06 January 2005 14:05 To: Jigal van Hemert; mysql@lists.mysql.com Subject: Re: Retrieving partial field values A full-text search won't work. Too many records -- at least 8 million. From what I've read, full-text won't pick up the individual initials or very short names, like Vu, Lee, Doe, etc. Jigal van Hemert [EMAIL PROTECTED] wrote: Example a (GivenNames): Mary Elizabeth or Marg Elizabeth Example b (GivenNames): J. W. or I. W. Example c (Surname): Stotts, Statts or Stutts I need to be able to retrieve the following based upon the examples: For Example a: Return Mary Elizabeth where GivenNames begins with M; Return Marg Elizabeth where GivenNames begins with M; Return Mary Elizabeth where GivenNames contains the whole word Mary; Return Marg Elizabeth where GivenNames contains the whole word Marg; Return Mary Elizabeth where GivenNames=Mary Elizabeth Return Marg Elizabeth where GivenNames=Marg Elizabeth I'm beginning to think that a form of FULL TEXT search is what you need; take a look at these articles: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html and even query expansion might be usefull in your case (though it might be a bit slow): http://dev.mysql.com/doc/mysql/en/Fulltext_Query_Expansion.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? All your favorites on one personal page Try My Yahoo! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot create Windows service for MySql. Error: 0
I usually find that this means there is already a MySQL service defined - you can verify this in Start/Control Panel/Administrative Tools/Services. If there is a service that exists, go to your current \mysql\bin directory within a command prompt and issue mysqld-nt --remove which will delete the current installed service. If you can't get to this (i.e the install dir has been removed) - with the new installer you can use a different name for the service, such as MYSQL41. HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 06 January 2005 08:31 To: mysql@lists.mysql.com Subject: Re: Cannot create Windows service for MySql. Error: 0 Hello. While I am trying to install MySql Server 4.1, What exact version do you install? Use 4.1.8. Did you stop the previous instance of MySQL (if you have such one)? Please close all the executable files and the applications that uses MySQL's dlls. Can you start MySQL from Windows Command Line? Eugenia Mariani [EMAIL PROTECTED] wrote: My O.S in Win Xp Pro SP2. My web server is Apache 5.0 While I am trying to install MySql Server 4.1, I have the following error and I cannot install the Server: Cannot create Windows service for MySql. Error: 0 Can someone help me to install without error? Thanks Eugenia Mariani _ Ricerche online pi? semplici e veloci con MSN Toolbar! http://toolbar.msn.it/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.8 - Release Date: 03/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.8 - Release Date: 03/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble w/ mysqldump
I am confused over the much smaller DataBase, but also of the corrupted avatars and file attachments (I've attached examples).. I did some research using mysqladmin, and the 'attachment' and the 'customavatar' tables are exactly the same size in both databases, but the entire DB is about 10MB smaller on the 4.1 dump (according to mysqladmin) But the difference in my acutal dump files is 70MB.. I was told by vBulletin that they think it has somthing to do with the way mysql 4.1.X handles tables with languages and character encoding? Thanks! In a message dated 1/5/2005 12:16:19 PM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: Hello. Right, three is a true dump file, and I can restore the database, everything 'seems' to be there, So, as I understand, the only thing that were confusing you, was the small dump file? If you see binary files after restoring from the dump file - they are in the dump file. I think that clues are in the bulk inserts which are tighter than ordinary. you [EMAIL PROTECTED] wrote: Right, three is a true dump file, and I can restore the database, everything 'seems' to be there, I can browse the table all day long, and I can see the binary files in the database, but the length of the tables are different. mysql@lists.mysql.com In a message dated 1/5/2005 3:50:39 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: Hello. The actual tables are there I assume that 'there' is a dump file. One more question, can you select the Avatars data using SQL queries? For example: SELECT 'Avatar_field' from 'avatar_table' limit 1; Does your application work after upgrade? [EMAIL PROTECTED] wrote: Thanks for the reply. My original install was w/ an RPM from the Redhat Network, I uninstalled all RPM's associated w/ MySQL, Apache, and PHP and downloaded the latest of everything and compiled it. After I did an updatedb on my box, I searched for mysqldump and mysql to confirm it was gone -- nothing was found. ./mysqldump Ver 10.9 Distrib 4.1.8a, for redhat-linux-gnu (i686) The actual tables are there, but the images (or any attachments) are really messed up. Looks like something from the 60's, lol. In a message dated 1/4/2005 9:36:01 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: Hello. In what way have you upgraded? How do you dump the data? Is everything fine, execept the database dump? As of MySQL 4.1, --opt command line option is on by default, so it can produce a smaller output. The definions of tables which stores Avatars are also absent in dumps? May be you use mysqldump from old 3.23 version? [EMAIL PROTECTED] wrote: Hi All, I run a website, and am having a problem w/ corrupt databases.. I was runnig MySQL v3.23 previously, and my db dumps worked fine .. However, I have since upgraded to v4.1.8a-log and I am having problems getting completed (or non corrupt) backups.. The problem is pretty big.. My Original Backup of the DB using 3.23 was 208MB --- When I upgraded to 4.18 the backup is only 138MB ... My users Avatar's, all attachments, etc are totally whacked out. Does anyone have any insight on what is causing this, and how to fix it? Any help is appreciated.. I need a new good backup desperately -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble w/ mysqldump (images attached)
Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar attachment: brokenavatar.jpegattachment: goodavatar.jpeg-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying table to another server.
Hello, I'm having a problem with moving a database from one server to another using mysqldump. Since the servers are different platforms, some ascii characters are getting corrupted. So, my thought was to copy the table from one database to the other via the mysql interface. I found this online at mysql: mysqladmin -h 'other_hostname' create db_name mysqldump --opt db_name | mysql -h 'other_hostname' db_name However, all it does is hang...so I'm guessing its a permission problem? Any suggestion to how to setup this up so it works or is there an easier way? Thanks! -Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Dictionary
The only way I know to access anything resembling a data dictionary in
MySQL V4.* is via the Java MetaData interfaces. I use these occasionally in
my programs. In your case, you want the getColumnDisplaySize() method in the
ResultSetMetaData Interface. This code fragment demonstrates the technique:
/* Determine the maximum length of the event_name column in the Events2
table. */
String query2 = select event_name as event from SDAC.Events2;
ResultSetMetaData rsmd2 = null;
try {
stmt = conn01.createStatement();
rs = stmt.executeQuery(query2);
rsmd2 = rs.getMetaData();
String columnName = rsmd2.getColumnName(1);
String columnLabel = rsmd2.getColumnLabel(1);
String columnTypeName = rsmd2.getColumnTypeName(1);
int columnSize = rsmd2.getColumnDisplaySize(1);
System.out.println(The name of the event_name column is +
columnName);
System.out.println(The label of the event_name column is +
columnLabel);
System.out.println(The column type name of the event_name column is
+ columnTypeName);
System.out.println(The size of the event_name column is +
columnSize);
} catch(SQLException sql_excp) {
sql_excp.printStackTrace();
}
This is the result of running that fragment:
The name of the event_name column is event
The label of the event_name column is event
The column type name of the event_name column is VARCHAR
The size of the event_name column is 100
(Note: If you omit the 'as event' from the query, both the column name and
the label name are 'event_name', not 'event. This may be a driver bug: I
would have though the original query should give 'event_name' as the column
name and 'event' as the label name.)
Aside from that, I think you have to wait until V5.* before you have any
command line SQL access to the MySQL catalog (or what you call a data
dictionary).
Then again, there may be some third party tools that give you that
information which I don't know about.
Rhino
- Original Message -
From: Ben Edwards [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 06, 2005 10:25 AM
Subject: Data Dictionary
how do I access the Data Dictionary through SQL? What I want to do is
loookup the length of a varchar for validation?
Ben
--
Ben Edwards - Poole, UK, England
WARNING:This email contained partisan views - dont ever accuse me of
using the veneer of objectivity
If you have a problem emailing me use
http://www.gurtlush.org.uk/profiles.php?uid=4
(email address this email is sent from may be defunct)
--
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: Trouble w/ mysqldump (images attached)
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 4:57 PM: Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar Looks like a character set issue - what's the column type, BLOB or TEXT or something in between? This could be due to the server converting UTF-8 into a different character set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into 0x3F, which is ? and often indicates that the character does not exist in the target collation. Basically, MySQL is treating the content as text, and replacing characters which it doesn't understand with ?. Try using a different collation or character set, and importing again? Unfortunately, the conversion is not reversible - a set of characters have been replaced with a single character, so although the image is the same binary size, some of the data has been permanently lost unless you can restore from the backup. cheers, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Copying table to another server.
It should just exit unless you are using -p with mysql. In that case, if mysqldump got an access denied, nothing would output to stdout and mysql would be left waiting for a password. I would try the following: mysqladmin -h 'other_hostname' create db_name mysqldump --opt db_name dump.sql mysql -h 'other_hostname' -D db_name dump.sql Running the commands separately will allow you to see what is going on more easily. You may need to specify a username and a password with each command i.e. 'mysql -u user -p'. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Andrew Mull [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 11:17 AM To: mysql@lists.mysql.com Subject: Copying table to another server. Hello, I'm having a problem with moving a database from one server to another using mysqldump. Since the servers are different platforms, some ascii characters are getting corrupted. So, my thought was to copy the table from one database to the other via the mysql interface. I found this online at mysql: mysqladmin -h 'other_hostname' create db_name mysqldump --opt db_name | mysql -h 'other_hostname' db_name However, all it does is hang...so I'm guessing its a permission problem? Any suggestion to how to setup this up so it works or is there an easier way? Thanks! -Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Dictionary
As Rhino said, the INFORMATION_SCHEMA views (data dictionary) are coming
in v5.x (so they are close but not here yet). Until then a more native
alternative is to parse the results of either SHOW CREATE TABLE
tablename or SHOW COLUMNS FROM tablename LIKE 'columnname' in whatever
language you use.
Does *your* database connection library expose any of that metadata (like
in Rhino's Java example)? (for example, I also know that it's possible to
use ADO or ODBC properties to get that information, too). It may not be as
easy to get at the column size information as performing an INFORMATION
SCHEMA query but it's an option.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Rhino [EMAIL PROTECTED] wrote on 01/06/2005 12:28:52 PM:
The only way I know to access anything resembling a data dictionary in
MySQL V4.* is via the Java MetaData interfaces. I use these occasionally
in
my programs. In your case, you want the getColumnDisplaySize() method in
the
ResultSetMetaData Interface. This code fragment demonstrates the
technique:
/* Determine the maximum length of the event_name column in the Events2
table. */
String query2 = select event_name as event from SDAC.Events2;
ResultSetMetaData rsmd2 = null;
try {
stmt = conn01.createStatement();
rs = stmt.executeQuery(query2);
rsmd2 = rs.getMetaData();
String columnName = rsmd2.getColumnName(1);
String columnLabel = rsmd2.getColumnLabel(1);
String columnTypeName = rsmd2.getColumnTypeName(1);
int columnSize = rsmd2.getColumnDisplaySize(1);
System.out.println(The name of the event_name column is +
columnName);
System.out.println(The label of the event_name column is +
columnLabel);
System.out.println(The column type name of the event_name column
is
+ columnTypeName);
System.out.println(The size of the event_name column is +
columnSize);
} catch(SQLException sql_excp) {
sql_excp.printStackTrace();
}
This is the result of running that fragment:
The name of the event_name column is event
The label of the event_name column is event
The column type name of the event_name column is VARCHAR
The size of the event_name column is 100
(Note: If you omit the 'as event' from the query, both the column name
and
the label name are 'event_name', not 'event. This may be a driver bug: I
would have though the original query should give 'event_name' as the
column
name and 'event' as the label name.)
Aside from that, I think you have to wait until V5.* before you have any
command line SQL access to the MySQL catalog (or what you call a data
dictionary).
Then again, there may be some third party tools that give you that
information which I don't know about.
Rhino
- Original Message -
From: Ben Edwards [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 06, 2005 10:25 AM
Subject: Data Dictionary
how do I access the Data Dictionary through SQL? What I want to do is
loookup the length of a varchar for validation?
Ben
--
Ben Edwards - Poole, UK, England
WARNING:This email contained partisan views - dont ever accuse me of
using the veneer of objectivity
If you have a problem emailing me use
http://www.gurtlush.org.uk/profiles.php?uid=4
(email address this email is sent from may be defunct)
--
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 on OS X
Hello, I'm interested to hear peoples' experiences running mysql on OS X. I've moved the database for a fairly heaily used website (~ 2M queries a day) over to a new dual 2GHz XServe running OS X Server 10.3.7. This database has run smoothly on an aging dual PIII machine running freebsd for the past several years. My initial impression is that the performance gains aren't nearly what I would have expected. For the most part the new machine is less loaded, but at peak times it's arguably doing worse that it did the old freebsd machine. The number a variables involved has hindered my creating comprehensive benchmarks but some initial impressions from running stock mysql benchmarks are that 4.0.23a on OS X performs around 10% faster than 4.1.8a and that my old freebsd machine running 4.0.18 is less than a factor of two slower. These are all using similar my.cnf settings tuned along the lines of the my-huge.cnf sample config. Does anyone have any tips to offer for tuning OS X and mysql to play well together? Is anyone running a heavily loaded mysql server in production under OS X? Thanks for you help! scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: first time accessing
Tom, I still get access denied. I just reinstall the latest version yesterday. What are my options now? Thanks, Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 6:28 AM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing [snip] DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 [/snip] I would try your connection from a command line ie. 'mysql -u Jerry -h GJWPP88 -p'. The problem is now not with your perl, it is your mysql user permissions. From your error, I see that mysqld is running on your local machine. In this case you should use localhost instead of the actual hostname ie. 'mysql -u Jerry -h localhost -p' (you can omit the host on this one, it is default). If this works, you can change the host in your perl script to localhost, and you should be ready to go. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Thursday, January 06, 2005 5:25 AM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- 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: Trouble w/ mysqldump (images attached)
Hi Tom, Thanks for the reply! I show the following information for my DB, and shows the same for both the 3.23 DB And the 4.18a DB Field Type Collation avatardata mediumtext latin1_swedish_ci I pasted a data table from the bad avatar and the good avatar to a file differential program, there was no differential at all that the system found.. I'm not too sure where or what to do to change this information? Do you mean that I recompile MySQL using different ./configure commands? Thanks Tom! [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 4:57 PM: Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar Looks like a character set issue - what's the column type, BLOB or TEXT or something in between? This could be due to the server converting UTF-8 into a different character set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into 0x3F, which is ? and often indicates that the character does not exist in the target collation. Basically, MySQL is treating the content as text, and replacing characters which it doesn't understand with ?. Try using a different collation or character set, and importing again? Unfortunately, the conversion is not reversible - a set of characters have been replaced with a single character, so although the image is the same binary size, some of the data has been permanently lost unless you can restore from the backup. cheers, Tom In a message dated 1/6/2005 12:48:28 PM Eastern Standard Time, Tom Molesworth [EMAIL PROTECTED] writes: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 4:57 PM: Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar Looks like a character set issue - what's the column type, BLOB or TEXT or something in between? This could be due to the server converting UTF-8 into a different character set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into 0x3F, which is ? and often indicates that the character does not exist in the target collation. Basically, MySQL is treating the content as text, and replacing characters which it doesn't understand with ?. Try using a different collation or character set, and importing again? Unfortunately, the conversion is not reversible - a set of characters have been replaced with a single character, so although the image is the same binary size, some of the data has been permanently lost unless you can restore from the backup. cheers, Tom -- 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: first time accessing
If you are using a fresh install of mysql, you need to connect with 'mysql -u root' from the local machine and then configure access for other users with the GRANT command. See http://dev.mysql.com/doc/mysql/en/GRANT.html. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 3:08 PM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I still get access denied. I just reinstall the latest version yesterday. What are my options now? Thanks, Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 6:28 AM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing [snip] DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 [/snip] I would try your connection from a command line ie. 'mysql -u Jerry -h GJWPP88 -p'. The problem is now not with your perl, it is your mysql user permissions. From your error, I see that mysqld is running on your local machine. In this case you should use localhost instead of the actual hostname ie. 'mysql -u Jerry -h localhost -p' (you can omit the host on this one, it is default). If this works, you can change the host in your perl script to localhost, and you should be ready to go. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Thursday, January 06, 2005 5:25 AM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- 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 on OS X
While part of the problem may be OS X, Apple is still optimizing parts of the OS, I would say the problem is that you are comparing it to an aging PIII. Some people have gotten better performance from a PIII than a P4. The reason is cache. The PIII has a larger cache, MySQL loves cache. The G5 XServe have a comparatively small cache (512k L2, 1MB L3 I think). If you have a G4 XServe around, I would try that. You may get better performance. The G4 XServe came with 2MB L3 cache. Your best hardware may actually be Intel's Extreme Edition CPU (4MB cache) designed for gaming. Although I haven't seen any benchmarks, just my speculation. Another possibility is your disks. Did the PIII have SCSI? SCSI uses a technique called command queueing to optimize reads and writes. Command queueing has only recently become available on some ATA drives and Apple does not ship SATA drives with command queueing in their XServes. If your old system had SCSI disks, you could try moving them over to the XServe if you have a SCSI card in the XServe. Or replace the SATA drives in the XServe with SATA drives with command queueing. The G5 is excellent for compute intensive tasks, databases are typically throughput intensive. If you can't take advantage of the vector processor in the G5 (and G4), you're missing a big part of where the G5 gets it's performance. You may try compiling a version of MySQL yourself, optimized for the G5 chip. http://developer.apple.com/technotes/tn/tn2086.html#G5options This thread from the archive may help in compiling: http://archives.neohapsis.com/archives/mysql/2004-q2/0759.html But besides all that, you should first determine what's causing the bottleneck. It's either disk (I/O), CPU, RAM (not enough allocated), or Network. On Jan 6, 2005, at 1:58 PM, Scott Wilson wrote: Hello, I'm interested to hear peoples' experiences running mysql on OS X. I've moved the database for a fairly heaily used website (~ 2M queries a day) over to a new dual 2GHz XServe running OS X Server 10.3.7. This database has run smoothly on an aging dual PIII machine running freebsd for the past several years. My initial impression is that the performance gains aren't nearly what I would have expected. For the most part the new machine is less loaded, but at peak times it's arguably doing worse that it did the old freebsd machine. The number a variables involved has hindered my creating comprehensive benchmarks but some initial impressions from running stock mysql benchmarks are that 4.0.23a on OS X performs around 10% faster than 4.1.8a and that my old freebsd machine running 4.0.18 is less than a factor of two slower. These are all using similar my.cnf settings tuned along the lines of the my-huge.cnf sample config. Does anyone have any tips to offer for tuning OS X and mysql to play well together? Is anyone running a heavily loaded mysql server in production under OS X? Thanks for you help! scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: first time accessing
Tom, I am still getting access denied when I try ',mysql -u root'. Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 11:24 AM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing If you are using a fresh install of mysql, you need to connect with 'mysql -u root' from the local machine and then configure access for other users with the GRANT command. See http://dev.mysql.com/doc/mysql/en/GRANT.html. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 3:08 PM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I still get access denied. I just reinstall the latest version yesterday. What are my options now? Thanks, Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 6:28 AM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing [snip] DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 [/snip] I would try your connection from a command line ie. 'mysql -u Jerry -h GJWPP88 -p'. The problem is now not with your perl, it is your mysql user permissions. From your error, I see that mysqld is running on your local machine. In this case you should use localhost instead of the actual hostname ie. 'mysql -u Jerry -h localhost -p' (you can omit the host on this one, it is default). If this works, you can change the host in your perl script to localhost, and you should be ready to go. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Thursday, January 06, 2005 5:25 AM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- 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: first time accessing
Tom, I am running 'WinMySQLadmin 4.1. Can I use this tool to change anything? Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 11:24 AM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing If you are using a fresh install of mysql, you need to connect with 'mysql -u root' from the local machine and then configure access for other users with the GRANT command. See http://dev.mysql.com/doc/mysql/en/GRANT.html. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 3:08 PM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I still get access denied. I just reinstall the latest version yesterday. What are my options now? Thanks, Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 6:28 AM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing [snip] DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 [/snip] I would try your connection from a command line ie. 'mysql -u Jerry -h GJWPP88 -p'. The problem is now not with your perl, it is your mysql user permissions. From your error, I see that mysqld is running on your local machine. In this case you should use localhost instead of the actual hostname ie. 'mysql -u Jerry -h localhost -p' (you can omit the host on this one, it is default). If this works, you can change the host in your perl script to localhost, and you should be ready to go. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Thursday, January 06, 2005 5:25 AM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- 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]
select into the same outfile repetitively
Hi, mysql Gurus, Sometimes I need to execute 'select * into outfile '/tmp/t1.out' more than 1 times before I get the right result. But once mysql succeeds in writing t1.out, it does not overwrite it if the same sql command is executed for the 2nd time unless t1.out is removed. Is there anyway to change it or somehow have mysql just write it once more? Hope I am clear. Thanks a lot for help -- ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select into the same outfile repetitively
[snip] Sometimes I need to execute 'select * into outfile '/tmp/t1.out' more than 1 times before I get the right result. But once mysql succeeds in writing t1.out, it does not overwrite it if the same sql command is executed for the 2nd time unless t1.out is removed. Is there anyway to change it or somehow have mysql just write it once more? Hope I am clear. Thanks a lot for help [/snip] From http://dev.mysql.com/doc/mysql/en/SELECT.html The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. The file cannot already exist, which among other things prevents files such as `/etc/passwd' and database tables from being destroyed. The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table on the server machine. You would have to do something with your programming language -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing the worst InnoDB corruption bug in 3 years - when
At the 2004 Users Conference in Orlando in April there were two sessions on optimizing MySQL hosted by a MySQL staffer who's name eludes me for the moment. He told the assembled masses that in benchmarks he ran that innodb_file_per_table was somewhat faster than using the large innodb table space. I didn't get the impression it was like 50% faster or anything, but once finished optimizing indexes and so on any gains are likely to be in small pieces, but they all add up. I can see the logic of it of course... Most of our servers are running 40Gbyte InnoDB table spaces, two are running 100G space. Some of our tables are small, some have only 7 rows of 2 columns... It must be easier for InnoDB to find 100bytes of data in its own file rather than in 100GBytes of shared table space. So I don¹t have anything quantitative, just hearsay from the folks at MySQL who are the performance and fine tuning experts. As to it's being new... It's different. It's as new as MySQL 4.1 - if you are using 4.1 then it's no newer than anything else there. MySQL staffers were giving us benchmarks with it back in April at the Users Conference, and I had already figured out I wanted to do it last January, I've just been waiting for a) the production version, and b) an opportunity to down my services. MySQL historically has released very stable products by the time they get to Beta. We always wait till it goes Production because we couldn't explain to a client why a problem occurred on beta software, but it's only labeling. MySQL beta typically is more stable than most folks release.1 or release.2 versions. This is because of the very large base of people around the globe using and testing MySQL and contributing to it's development. Best Regards, Bruce On 1/6/05 7:38 AM, Ken Menzel [EMAIL PROTECTED] wrote: Hi Bruce SNIP - Original Message - From: Bruce Dembecki [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 2:51 AM Subject: Re: Fixing the worst InnoDB corruption bug in 3 years - when As a side note with demonstrated performance increases when using innodb_file_per_table why aren't more people using it? Best Regards, Bruce /SNIP What demonstrated performance increases are you referring to? I would love to use file_per_table, but as it is new we are very conservative with our production DB's and this feature is too new. But if there are demonstrated performance increases we would love to start using it! Thanks, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW SLAVE STATUS hangs while LOAD DATA FROM MASTER runs (4.0.23)
Hi! I never saw a follow-up to the post about SHOW SLAVE STATUS hanging while LOAD DATA FROM MASTER is running as of about 4.0.20 on. (http://lists.mysql.com/mysql/167238) Is this a known bug? Or a feature? thanks! chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select @qty1=@qty+qty from goods2 , result 0 ?
Hi, a table goods2 itemid qty 001 12.5 002100.0 field qty float(10,2) not null default 0 set @qty1=0 select qty, @[EMAIL PROTECTED] allqty from goods2 The result is 12.50 100.0 0 Why not 12.512.5 100.0 112.5 ? It seems @[EMAIL PROTECTED] not work. Best regards, Shuming Wang
Re: select @qty1=@qty+qty from goods2 , result 0 ?
Hi Shuming, select qty, @[EMAIL PROTECTED] allqty from goods2 That should be @qty1:[EMAIL PROTECTED] You are doing a comparison using =, which is returning false (0). Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on OS X
Hi Scott! We use MySQL on 9 Mac OS X machines. While we are looking at moving some of that back to big Sun boxes, that's a memory access/64 bit issue, not (directly) a performance issue. Looking at the live stats one of the machines has an uptime of 55 days and has averaged 405.78 queries per second across the whole time (that¹s about 35M queries per day) - we run 8 in production, and one admin server which replicates from everyone and does our backups and feeds the reports servers and so on, not all 8 production machines are running this busy, but it gives you an idea of our traffic and throughput. In our experience the key here is configuration. Overall for our use (discussion boards) we find InnoDB tables are dramatically faster for us than MyISAM. The key thing will be setting your memory settings in my.cnf to be as generous as possible. Query Cache is great for us, typically we see about 30% of our SELECT queries going through query cache, so that¹s definitely worth turning on. Does the machine do anything else or is it just serving MySQL? How much memory does it have? my-huge.cnf in actual fact isn't that generous. Typically my-huge would take about 500M of ram, maybe a gig if you have LOTS of connections set. Works OK if you are running a machine that needs power for other things, such as running Apache and PHP and MySQL all in one box... But if your mysql box is just serving mysql and nothing else, you need to tune the machine as much as possible for mysql. Turn off system processes and options not really needed for running a database. And tune up that memory Due to memory limits on the Mac OS X quasi 64 bit emulation it is possible for the Operating System to access more than 2Gbytes of ram, but not possible for any process to do so (including, sort of) mysqld. The trick here is that innodb grabs it's own chunk of memory, so in actual fact we can get nearly 4Gbytes of memory allocated to MySQL on OS X... Settings of note here (if you are into InnoDB) are: key_buffer_size=1024M sort_buffer_size=4M read_buffer_size=4M read_rnd_buffer_size=8M myisam_sort_buffer_size=64M query_cache_size=128M innodb_buffer_pool_size=1850M innodb_additional_mem_pool_size=256M innodb_flush_log_at_trx_commit=0 This last one improved performance for us under InnoDB dramatically. We have a lot of connections, so the read buffers and so on are multiplied by the number of connections... If you are only using MyISAM and don't have so many connections in max_connections you can probably increase key_buffer_size to closer to 1500 or so... The query cache you should increase and monitor (using SHOW STATUS LIKE 'Qcache_%';) and when your server has been running more than 48 hours and Qcache_free_memory is still giving you a comfortable overhead you can be happy with it... If there's not much left in Qcache_free_memory, increase it and try again. Total memory available under OSX with no InnoDB is 2Gbytes... So add up your key_buffer and the myisam_sort_buffer and the query_cache and thesort/read buffers multiplied by the number of connections and you get to where you can get to. Of course if your server has 2Gbytes or less you need to reduce this somewhat to leave room fro the OS to run and stay within the available memory of the machine. We are trying now to determine if we can wait with OS X for their true 64 bit operating system due sometime in the first 6 months of 2005 (which we have to assume is June) or if our new database server budget should go to Sun boxes which give us real 64 bit now, and thus let us throw a whole lot of memory at InnoDB, or if we stay with our Apple strategy and expand when we get the new OS. Problem, here being Apple has a great Storage situation, both loc al to the Xserver and using the Xserve Raid... Sun has a problem in storage land right now. Its not clear to us which way we should go. From a cost perspective once you load up a machine with 8Gbytes of ram and multi processors and lots of disk space and multi year onsite support contracts it doesn't make a lot of difference if we go Sun, Apple, Dell, HP etc etc... They all come out within a few $$ of each other. So for us it's 64 bit which is important, Sun leads the way here, but Apple should get there very soon. Anyway, it most certainly is possible to run mysqld under high load on OS X, we do it all day, every day. The servers have 8Gbytes of ram but really aren't using much more than 2 yet... (we have some memory settings for MyISAM caches but our MyISAM tables are really only the archived data, so we don't get much benefit from that. Once OS X 10.4 comes out and we can go 64 Bit, I expect we'll be a lot happier with our OS X G5s and can put enough through them that we'll start to see CPU use become significant... It'll be like getting two more servers for every server we already have. Best Regards, Bruce On 1/6/05 7:58 PM, Scott Wilson wrote: Hello, I'm interested to hear peoples' experiences running mysql on OS X.
MySQL insert with PHPMyAdmin
Can using PHPMyAdmin cause problems. What I'm doing is, I'm using PHPMyAdmi as a data entry tool. I create a new record in a table and then fill in the fields with the field editing and insert abilities of PHPMyAdmin. For a remote user of course I'd escape any inserted strings with addslashes or mysql_escape PHP functions. But I wouldn't do anything mailicious to my own DB so I'm just using PHPMyAdmin to insert, edit...anything. Are any problems possible by me doing this? Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search string less than 4 characters
I want to know on Solaris how I could lower the the minimum fulltext search string from 4 to 3. Right now using the FullText search any string less than 4 chars is ignored. I'm sure there's a link explaining how. Maybe UNIX help in general on his would be good as well. Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL insert with PHPMyAdmin
As far as I know there should not be any problem but I prefer SQLyog for all my MySQL work. It even has HTTP Tunneling to access dbs that dont allow remote access. Karam --- leegold [EMAIL PROTECTED] wrote: Can using PHPMyAdmin cause problems. What I'm doing is, I'm using PHPMyAdmi as a data entry tool. I create a new record in a table and then fill in the fields with the field editing and insert abilities of PHPMyAdmin. For a remote user of course I'd escape any inserted strings with addslashes or mysql_escape PHP functions. But I wouldn't do anything mailicious to my own DB so I'm just using PHPMyAdmin to insert, edit...anything. Are any problems possible by me doing this? Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FLUSH syntax query
So if I use FLUSH MASTERS and FLUSH SLAVES, it will have the same effect with new versions of MySQL too. Karam --- Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Also, from which version of MySQL LOCAL and NO_WRITE_TO_BINLOG options supported? 4.1.1 RESET MASTER and RESET SLAVE were named FLUSH MASTER and FLUSH SLAVES before MySQL 3.23.26. Karam Chand [EMAIL PROTECTED] wrote: Hello, From the MySQL docs: http://dev.mysql.com/doc/mysql/en/FLUSH.html What does FLUSH MASTER and FLUSH SLAVES does? Its not documented? Also, from which version of MySQL LOCAL and NO_WRITE_TO_BINLOG options supported? Regards, Karam __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select datetime older than X weeks
I'm using OpenBSD 3.6 (latest version) which comes with binary packages for MySQL 4.0.20 - More recent binary packages are not yet available for this platform and installation from source is not an option. I have a table with datetime field and I would like to select all data older than X weeks, where X is a variable in my php script. DateDiff(datetime,now()) looks like its the right function for this purpose, but this function requires date arguments and date() isn't in 4.0.20 to convert my field from datetime to date. I have tried many different combinations, only to find a function isn't on my system or my data is not the proper type for functions that I do have. Can anyone suggest a workable SQL query for this situation? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating first table
Hi! Running mysql on a XP command line. Doing dhow databases list the following; Database Club Mysql Test I did a grant all user id; How can I list the rows and columns. I tried alter table club add ME varchar( 3)); and get : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right sysntax to use near 'gjwpp88 club add (ME varchar( 3 )) an line 1; What have I missed? Thanks, Jerry
Re: Auto Table Name Completion has stopped working?
That worked. Thanks very much! Danke, Gracias, Grazie, Merci, Go raibh maith agat! On Jan 6, 2005, at 3:38 AM, Gleb Paharenko wrote: Hello. Try 'rehash' command. Kieran Kelleher [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: US-ASCII, 15 lines --] Normally, when you type the first few letters of a table name and press tab, the mysql client does not auto complete anymore. Can this be enabled with a my.cnf setting or something since it is no longer the default behaviour on my machine? Regards, Kieran Dev Config = OS X 10.3.5 / Java 1.4.2_05 / WO 5.2.3 / XCode v1.5 / MySQL 4.0.20 / Connector-J 3.0.11 Deploy Config = OS X 10.3.7 Server / Java 1.4.2_05 / WO 5.2.3 / MySQL 4.0.20 / Connector-J 3.0.11 My Blog: http://webobjects.webhop.org/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search string less than 4 characters
Lee, establish the fulltext minimum word length system variable as follows... [mysqld] ft_min_word_len=3 reference: http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html cheers, --bemansell Brian E. Mansell MySQL Professional On Thu, 06 Jan 2005 20:59:23 -0500, leegold [EMAIL PROTECTED] wrote: I want to know on Solaris how I could lower the the minimum fulltext search string from 4 to 3. Right now using the FullText search any string less than 4 chars is ignored. I'm sure there's a link explaining how. Maybe UNIX help in general on his would be good as well. Thanks, Lee G. -- 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]
easy way to drop a tempoary table created by Create view?
Hi, What is the easiest way to drop a table created by create view? I m using mysql 5.0. I would like to execute the drop from Unix commandline. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing the worst InnoDB corruption bug in 3 years - when
Hi Bruce, On Dec 28, 2004, at 2:38 PM, Bruce Dembecki wrote: As a person in the process of migrating from 4.0 to 4.1 and having already scheduled the downtime with my clients for this Friday morning, and having to do a full dump and import already as part of the migration process I'd like to know WHEN the fix will be available. I dont have a lot of opportunities for a full dump and import, so this is a crucial time for me, and there are some benefits with innodb_file_per_table that are important to us. While the fix is already in the source, we have not completed putting together what will be 4.1.9 yet. We have a couple of patches to add for NDB and then we are done. These should be completed today, which means that we will build 4.1.9 over the weekend and release on Monday. If we go with history then we should expect a new version of the current MySQL products every 2 months approximately. Having just received 4.1.8 I'd Our goal has been to increase the number of builds we do. As you have probably noticed in recent months, this has been happening. Our end goal is to do a release of all active versions once a month. So at the moment this means you should be seeing monthly, if not more frequent, 4.1 and 5.0 builds. Hope this helps some in your planning. Cheers, -Brian -- Brian Aker, Director of Architecture MySQL AB, www.mysql.com Seatle, WA. USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
