CONVERT CHARACTER SET option work on TEXT fields?
Folks -- I'm using Client version:mysql Ver 14.6 Distrib 4.1.5-gamma, for pc-linux (i686) Server version: 4.1.5-gamma-standard-log They both happen to both be on the same system, which is RHEL AS 3. All defaults are now UTF8: Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 I'm trying to convert a table (from a database dumped from a 4.0.x server) to UTF8 on this other (4.1.5) server. Following the instructions on http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html, From MySQL 4.1.2 on, if you want to change all character columns (|CHAR|, |VARCHAR|, |TEXT|) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; This is useful, for example, after upgrading from MySQL 4.0.x to 4.1.x. See section 11.10 Upgrading Character Sets from MySQL 4.0 http://dev.mysql.com/doc/mysql/en/Charset-upgrading.html. I ran the following: ALTER TABLE EJOURNAL CONVERT TO CHARACTER SET utf8; ALTER TABLE EJOURNAL DEFAULT CHARACTER SET utf8; ALTER DATABASE ERESDB DEFAULT CHARACTER SET utf8; This was successful on the varchar(255) field, which went from: Acta cir?rgica brasileira to Acta cirúrgica brasileira Unfortunately, it was not successful on the *text* field, which has remained: | Acta cir?rgica brasileira| | Again, the quoted documentation above *explicitly* says that running the ALTER TABLE command with CONVERT TO CHARACTER SET option will change *TEXT* columns. That has not been my experience. Anyone tell me what I'm doing wrong here? TIA, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
general query log
MySQL gurus: I'm a big fan of the general query log for taking the occasional audit of activity against our database server, as well as a tool for checking on the efficiency of new (mostly PHP) projects that interact with MySQL. What I don't like is that I have to stop and restart the server process twice (once to turn the log on and once to turn it back off) in order to get the output. I've read through the documentation at mysql.com and would have searched through the list archives if I wasn't getting document contains no data messages from lists.mysql.com at the moment. We use mytop (http://jeremy.zawodny.com/mysql/mytop/) to keep some track of the queries that are being run, but the output pales in comparison (for this use) to that from the general query log. (1) Is there a way to turn logging on for brief periods (no more than 15 minutes) without having to stop and restart? (2) Is there some way to get similar output with a different tool? [mysql query] TIA, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
handling of diacritics?
Everyone -- We have a bibliographic database in which diacritics are impeding search results. That is to say, we would like for a search of 'zeitschrift fur%' to retrieve records that begin not only with zeitschrift fur but also zeitschrift für (notice the umlaut over the second to last letter in the second item). Currently, the search only retrieves the first type. I've searched through the documentation and there are references to handling specific individual ASCII characters (the character in question above is ASCII character 252), but not a general framework for all ASCII characters. Additionally, there is a way to retrieve the ASCII number assigned to a given character, but not vice versa. Is there any way to have 'ü' match on a 'u' in a search string, etc., etc., etc.? sql TIA, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query optimization with CONCAT query
Folks -- We have built a table with bibliographic information that contains a field for initial articles and for titles (sans initial articles). Some sample data (with all other fields suppressed): +-+--+ | article | title| +-+--+ | The | '60s (mini) | | | À Nous la Liberté [AKA: Freedom for Us | Liberty for Us] | | | Full Speed [AKA: À toute vitesse]| | El | Ángel exterminador [AKA: The Exterminating Angel]| | The | Farm: Angola, USA [AKA: The Farm] +-+--+ For searching, we want to check article (+) title So, we've used CONCAT, as shown below from an example query from our slow query log: SELECT FILM.length, FILM.film_id, FILM.article, FILM.title, FILM.release_date, FILM.description, FILM.displayFlag FROM (FILM LEFT JOIN FILM_DIRECTOR ON FILM.film_id = FILM_DIRECTOR.film_id) LEFT JOIN DIRECTOR ON FILM_DIRECTOR.director_id = DIRECTOR.director_id WHERE FILM.displayFlag = 'yes' AND (CONCAT(article,title) LIKE '%paris is burning%') ORDER BY title, release_date; I am under the impression (from testing and reading previous posts) that it is not possible to construct an index on the CONCATENATION of two fields. A multi-column index on the article and title fields only helps if the query is like: ... article LIKE '%something%' and title LIKE '%something%' ... which doesn't help us. Likewise, a FULLTEXT index only finds a string that is *fully contained* within any of the fields defined in that index. Any ideas of how to address this issue? TIA for any insights, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query optimization with CONCAT query
Actually, I'm not convinced it would, because I want to be able to find a string that is *NOT fully contained* within any given field. In other words, I want a search for The catcher in% to match the row: +-+--+ | article | title | +-+--+ | The | catcher in the rye | CONCAT allows me to do this, but I'm hoping to optimize that operation somehow, perhaps with an index. Andy olinux wrote: Mysql 4.0.1+ supports searches IN BOOLEAN MODE So if mysql 4 is an option, I think that would work for you. http://www.mysql.com/doc/en/Fulltext_Search.html olinux --- Andy Ingham [EMAIL PROTECTED] wrote: Folks -- We have built a table with bibliographic information that contains a field for initial articles and for titles (sans initial articles). Some sample data (with all other fields suppressed): +-+--+ | article | title | +-+--+ | The | '60s (mini) | | | À Nous la Liberté [AKA: Freedom for Us | Liberty for Us] | | | Full Speed [AKA: À toute vitesse] | | El | Ángel exterminador [AKA: The Exterminating Angel]| | The | Farm: Angola, USA [AKA: The Farm] +-+--+ For searching, we want to check article (+) title So, we've used CONCAT, as shown below from an example query from our slow query log: SELECT FILM.length, FILM.film_id, FILM.article, FILM.title, FILM.release_date, FILM.description, FILM.displayFlag FROM (FILM LEFT JOIN FILM_DIRECTOR ON FILM.film_id = FILM_DIRECTOR.film_id) LEFT JOIN DIRECTOR ON FILM_DIRECTOR.director_id = DIRECTOR.director_id WHERE FILM.displayFlag = 'yes' AND (CONCAT(article,title) LIKE '%paris is burning%') ORDER BY title, release_date; I am under the impression (from testing and reading previous posts) that it is not possible to construct an index on the CONCATENATION of two fields. A multi-column index on the article and title fields only helps if the query is like: ... article LIKE '%something%' and title LIKE '%something%' ... which doesn't help us. Likewise, a FULLTEXT index only finds a string that is *fully contained* within any of the fields defined in that index. Any ideas of how to address this issue? TIA for any insights, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: describe all tables
A fellow in our shop wrote a perl script to do just that.
Enjoy!
Andy
Andy Ingham
Systems Librarian
Academic Affairs Library
UNC-Chapel Hill
919-962-1288
[EMAIL PROTECTED]
*
#!/usr/local/bin/perl
# describe.pl
#
# Tim Shearer
#
# Academic Affairs Library
# University of North Carolia at Chapel Hill
# [EMAIL PROTECTED]
# 01/28/2002
#
# Please use, distribute, and tweak freely. I would appreciate
# my name staying with the distributed versions.
#
# This perl script will do a describe for all tables in a mysql database
# and save the output as a text file locally.
#
# It requires that you have perl and the perl DBI library installed.
# Note that you may need to change the location of perl (above) to
reflect
# where it lives on your box.
#
# The script relies on a .my.cnf file for usernames/passwords. It could
# be tweaked to let a user input mysql usernames and passwords
dynamically.
#
# If the .my.cnf user is root, it offers to allow a describe on all
databases.
# If not, the user must input the name of a database where they have
appropriate
# permissions for a describe.
#
# The script does a describe on each table, does some formatting (which
is
# not very sophisticated, long lines won't look pretty), and outputs the
results
# to the users local directory (from which they are running the script)
# in a file with this pattern: databasename.dsc
#
# Naturally, it can be run locally, or put out for anyone on a server to
use.
# Don't forget to make it executable if you're not familiar with
scripting...
use DBI;
$homedir = $ENV{HOME};
$input = $homedir.'/.my.cnf';
open (INFILE, $input) || die Can't open hidden files\n;
while (INFILE){
chomp;
@pair = split(=, $_);
$mycnf{$pair[0]} = $pair[1];
}
my $username = $mycnf{'user'};
my $hostname = $mycnf{'host'};
my $password = $mycnf{'password'};
if ($username eq root){ # if root, give them a list of dbs
my $data_source = DBI:mysql:mysql;
my $dbh = DBI-connect( $data_source, $username, $password );
if ( !defined $dbh ){
die Cannot do \$dbh-connect: $DBI::errstr;
}
my $query = qq(show databases);
my $select_sql = qq{ $query };
my $select_sth = $dbh-prepare( $select_sql );
$select_sth-execute() or die Unable to execute query: $dbh-errstr;
while(@ary = $select_sth-fetchrow() ){
$db = $ary[0];
unless($db eq 'mysql' || $db eq 'lost+found'){
push (@db, $db)
}
}
$select_sth-finish();
$dbh-disconnect;
$count = 1;
foreach $database (@db){
print $count - $database\n;
$count++;
}
print Choose a number from above: ;
$chosen = STDIN;
chomp($chosen);
unless($chosen eq mysql){
$chosen--;
$chosen = @db[$chosen];
}
}else{ # they're not root, let them choose one by typing it directly in
print Please input a database that you have access to: ;
$chosen = STDIN;
chomp($chosen);
}
my $data_source = DBI:mysql:$chosen:$hostname;
my $dbh = DBI-connect( $data_source, $username, $password ) || die
\n\nYou do not have permission to access this database.\n\n\n;
if ( !defined $dbh ){
die Cannot do \$dbh-connect: $DBI::errstr;
}
open(OUTFILE, $chosen.dsc);
print OUTFILE $chosen\n;
# Set up a query and prepare it...
my $query = qq(show tables);
my $select_sql = qq{ $query };
my $select_sth = $dbh-prepare( $select_sql );
$select_sth-execute() or die Unable to execute query: $dbh-errstr;
while(@ary = $select_sth-fetchrow() ){
$table = $ary[0];
push (@tables, $table);
}
$select_sth-finish();
foreach $tb (@tables){
print OUTFILE \n$tb\n;
my $query = qq(describe $tb);
my $select_sql = qq{ $query };
my $select_sth = $dbh-prepare( $select_sql );
$select_sth-execute() or die Unable to execute query: $dbh-errstr;
print OUTFILE
'+--+-+--+-+++'.\n;
print OUTFILE '| Field| Type|
Null | Key | Default| Extra |'.\n;
print OUTFILE
'+--+-+--+-+++'.\n;
while(my ($fld, $type, $nul, $key, $dft, $xtra) =
$select_sth-fetchrow() ){
printf OUTFILE |%-18s|%-33s|%-6s|%-5s|%-12s|%-16s|\n, $fld, $type,
$nul, $key, $dft, $xtra;
}
print OUTFILE
'+--+-+--+-+++'.\n;
$select_sth-finish();
}
$dbh-disconnect;
exit;
*
--- Original message --
Subject: describe all tables
Date: Sun, 27 Jan 2002 18:52:04 -0800
From: Michael Collins [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Can describe be used to show columns from all tables in a database?
All at once that is. I want to document all fields in all tables and
find I need to display one table at a time.
--
Michael
__
||| Michael
Re: Coldfusion?
Mark -- We've been running Cold Fusion with MySQL on the Solaris platform for almost two years now and have had such terrible problems with Cold Fusion errors that we have begun the process of migrating to PHP. Our average load has been about 30,000 .cfm pages (linked to MySQL backend) served per week, which I don't consider to be too heavy a load to expect the system to handle successfully. Unfortunately, our log files are riddled with fatal Cold Fusion errors and subsequent restarts. It got so bad that we had to institute a cron job to check for the happiness of the CF processes every 3 minutes and restart them if there was a problem. No amount of MySQL configuration tweaking, query optimization, Cold Fusion configuration tweaking, Cold Fusion query caching, or version upgrades of MySQL or Cold Fusion made much of a dent in the problem. I've heard from a number of other folks concerning similar experiences. Allaire pointed the finger at MySQL, which I've had no reason to believe is the problem. To back that assertion up, I can say that converting our most heavily used dynamic pages from CF to PHP has already had an *incredible* effect on the stability of the system, with no other MySQL changes. If you are thinking of running CF on Solaris, save yourself the headaches and use PHP instead. I can't speak to it's performance on Windows. My 2 cents, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] -Original message- Subject: Coldfusion? Date: Fri, 11 Jan 2002 14:16:23 - From: MSL [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Quick Newbie question: Can I use MySQL with Coldfusion server? If yes, are there any limitations or provisos I should know about? Thanks as always Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
