Re: mysqldump corrupting utf8 data

[Ravi Prasad LR]

How are you importing the dump into mysql? Importing by piping the dump 
file to mysql may broke some chars due to shell.

Have you tried this: (with in mysql client)
SET NAMES UTF8; SET CHARACTER SET UTF8
source /pathto/dump.sql 


Cheers,
--Ravi

Sean O'Hara wrote:

Hi All,

I've been googling all morning trying to find info on how to do a 
mysqldump of a utf8 encoded database from which I can restore without 
corrupting all the non ascii characters. If anyone has any pointers on 
this, I'd be most grateful.


Here is my setup. I am building a ruby on rails app and all the data 
is being entered from that application. The data is displayed fine if 
when it hasn't undergone a backup with msyqldump. I'm using mysql 
server 4.1.16 on Fedora Core 4. Here is an example show create table 
on one of the relevant tables:


artists | CREATE TABLE `artists` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `biography` text,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `sort_name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

The restored table has the same show create table definition. I've 
used enca on the dump file to see if it's been encoded properly:

enca -L none testdump1.sql
Universal transformation format 8 bits; UTF-8

So that seems to be in order. Here's the top of the dump file:

  1 -- MySQL dump 10.9
  2 --
  3 -- Host: localhostDatabase: alienrails_production
  4 -- --
  5 -- Server version   4.1.16
  6
  7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8 /*!40101 SET 
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

  9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 10 /*!40101 SET NAMES utf8 */;
 11 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, 
UNIQUE_CHECKS=0 */;
 12 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, 
FOREIGN_KEY_CHECKS=0 */;
 13 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, 
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

 14 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 15
 16 --
 17 -- Table structure for table `artist_images`
 18 --

Obviously I'm mussing something, but I have no idea what.

Thanks in advance,
Sean


--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: Relay Log Lost on Slave

[Dilipkumar]

Hi,

If you relay log is lost try out the this :-


Run the Change Master Position script, See the log output from where did the 
replication stopped.

So you can start your replication.

Thanks & Regards
Dilipkumar
- Original Message - 
From: "Kenji HIROHAMA" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, August 02, 2006 9:36 AM
Subject: Relay Log Lost on Slave



Hi,

Under my replication environment, what should I do if I lose the
current relay-log file on slave side?

1. one master and one slave replication is working
2. stop the master and the slave
3. remove the current relay log file manually
4. I can't start replication with "start slave" command
the error message is;
"ERROR 29 (HY000): File 'xxx-relay-bin.25' not found (Errcode: 2)"

Should I sync the data manually and start replication from the beginning?

Thanks,

--
[EMAIL PROTECTED]
Kenji Hirohama

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]




** DISCLAIMER **
Information contained and transmitted by this E-MAIL is proprietary to 
Sify Limited and is intended for use only by the individual or entity to 
which it is addressed, and may contain information that is privileged, 
confidential or exempt from disclosure under applicable law. If this is a 
forwarded message, the content of this E-MAIL may not have been sent with 
the authority of the Company. If you are not the intended recipient, an 
agent of the intended recipient or a  person responsible for delivering the 
information to the named recipient,  you are notified that any use, 
distribution, transmission, printing, copying or dissemination of this 
information in any way or in any manner is strictly prohibited. If you have 
received this communication in error, please delete this mail & notify us 
immediately at [EMAIL PROTECTED]



Watch the latest updates on Mumbai, with video coverage of news, events,
Bollywood, live darshan from Siddhivinayak temple and more, only on
www.mumbailive.in

Watch the hottest videos from Bollywood, Fashion, News and more only on
www.sifymax.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Relay Log Lost on Slave

[Kenji HIROHAMA]

Hi,

Under my replication environment, what should I do if I lose the
current relay-log file on slave side?

1. one master and one slave replication is working
2. stop the master and the slave
3. remove the current relay log file manually
4. I can't start replication with "start slave" command
the error message is;
"ERROR 29 (HY000): File 'xxx-relay-bin.25' not found (Errcode: 2)"

Should I sync the data manually and start replication from the beginning?

Thanks,

--
[EMAIL PROTECTED]
Kenji Hirohama

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Help for Loosely Couple Properties

[Jay Pipes]

On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> I have a table that contains properties that can be associated with any
> table whose primary key is a LONG. Lets say that there is just one kind
> of property. The table looks something like this:
> 
> TABLE StringVal
>   REF_ID  BIGINT// row to associate property with
>   TYPE_ID BIGINT// type of string property
>   VAL VARCHAR   // property value
> 
>   P_KEY( REF_ID, TYPE_ID )
> 
> There is another table to represent a specific StringVal type along with
> its default value:
> 
>TABLE StringType
>   ID  BIGINT   // The TYPE ID
>   NAMEVARCHAR  // The unique name of this property
>   DEF_VAL VARCHAR  // The default value of this property

Actually, the rub is that you are not using specific columns for
specific entity attributes, and are pretty much storing everything in
one gigantic table.  Any particular reason for this?  For instance, why
not have a column called "color", instead of overcomplicating things?

-jay


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Is this query possible?

[Tanner Postert]

ok, here is the schema that I am working with:

CREATE TABLE `cd` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `user_id` int(10) unsigned NOT NULL,
 `title` varchar(100)  NOT NULL,
 `description` text NOT NULL,
 `dt` datetime NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM

CREATE TABLE `song` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `user_id` int(10) unsigned NOT NULL,
 `title` varchar(50) NOT NULL,
 `artist` varchar(50)  NULL,
 `album` varchar(50)  NULL,
 `featuring` varchar(50) NULL,
 `length` int(11) NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM

CREATE TABLE `track` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `song_id` int(10) unsigned NOT NULL,
 `cd_id` int(10) unsigned NOT NULL,
 `track` tinyint(3) unsigned NOT NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `cd_id` (`cd_id`,`track`),
 UNIQUE KEY `song_id` (`song_id`,`cd_id`)
) ENGINE=MyISAM


the query now, is pulling back all the cd table data, as well as the total
length of the songs that are tracks on that CD, as well as the number of
tracks. that query is working fine.

my goal now, is to pull the same data, but only for CDs that contain a
specific track. but if i add "where song.id = 'X' the the count only returns
1 and the sum only returns the length for that 1 song. I'd like the full
length and track count, but only for CDs that contain a specific song.

SELECT cd.*,
count(track.track) as tracks,
sum(song.length) as length
from cd LEFT JOIN (track, song) on
(track.cd_id = cd.id
and track.song_id = song.id)
GROUP BY cd.id
ORDER BY dt DESC
LIMIT 0,1

thanks,

Re: swapping column values in update

[Pete Harlan]

On Wed, Aug 02, 2006 at 12:35:30AM +0200, Martin Jespersen wrote:
> I just ran the following sql (on mysql 4.1.20):
> 
>   update tbl set col1=col2, col2=col1

I went through this recently with the MySQL folks and the long and
short of it is that the above statement is undefined in MySQL.  It may
seem to work one way consistently (left-to-right evaulation of
assignments, as you noticed), but they are free to change it whenever
they want.

The case that bit me was when I converted something like the above to
use a multiple-table update, and at that point it evaulated all
right-hand sides in a context where none of the assignments had yet
been done.

The answer was that neither case is guaranteed or defined in MySQL.

The SQL standard seemed to me to define the "all right-hand sides are
evaulated in a context where none of the assignments have been done"
behavior.  Perhaps someday MySQL will work that way, but until they
say it does you can't count on any specific behavior.

I suppose that means:

begin transaction
update tbl set tmp=col1, col2=col1
update tbl set col1=tmp
commit

--Pete

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't get v5.0.22 to work;alternatives?

[cnelson]

n> http://dev.mysql.com/downloads/
> Look for "older releases"

Thanks but I want an older _build_ of the 5.0 release, not an older 
release.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query Help for Loosely Couple Properties

[Robert DiFalco]

I have a table that contains properties that can be associated with any
table whose primary key is a LONG. Lets say that there is just one kind
of property. The table looks something like this:

TABLE StringVal
REF_ID  BIGINT// row to associate property with
TYPE_ID BIGINT// type of string property
VAL VARCHAR   // property value

P_KEY( REF_ID, TYPE_ID )

There is another table to represent a specific StringVal type along with
its default value:

   TABLE StringType
ID  BIGINT   // The TYPE ID
NAMEVARCHAR  // The unique name of this property
DEF_VAL VARCHAR  // The default value of this property

The rub is that the target table could have millions of records and I
only want a record in StringVal if the associated property is going to
be a value other than the default. 

So consider that StringType has a record that defines a property named
"COLOR" with a default value of "ORANGE". For some table T, a T record
will only have a corresponding row in StringVal if it has a COLOR
property whose value has been explicitly set. It *could* be ORANGE but
in most cases it will be something else. Each row implicitly gets a
COLOR value of ORANGE.

The question is, how do I query this? Say I want all records from table
T whose COLOR property value is ORANGE.

The only thing I can come up with (and I'm no SQL expert and this looks
wrong to me) is the following:

SELECT *
FROM T
WHERE
(
T.ID NOT IN 
( 
SELECT StringVal.REF_ID 
FROM StringVal 
WHERE StringValue.TYPE_ID = COLOR 
)
AND
EXISTS
( 
SELECT * 
FROM StringType 
WHERE StringType.DEF_VAL LIKE "Orange" AND StringType.ID = COLOR

)
)
OR
(
T.ID IN 
(
SELECT StringVal.REF_ID 
FROM StringVal 
WHERE StringVal.VAL LIKE "Orange" AND StringVal.TYPE_ID = COLOR
)
)

Any suggestions on how to simplify this (besides writing a row for each
T that has the default value)?  Should I lay out the tables differently
or keep the DDL the same and just clean up the query?

R.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: swapping column values in update

[Martin Jespersen]

it's a frequent operation based on a where clause

Barry Newton wrote:

At 06:35 PM 8/1/2006, Martin Jespersen wrote:

I just ran the following sql (on mysql 4.1.20):

  update tbl set col1=col2, col2=col1

To my surprise, mysql updates col1 via col1=col2 before reading it for 
use in col2=col1, so I end up with the same value in both columns, 
which, of course, was not my intention. Thinking about it, this 
behavior in mysql makes perfect sense, so thats not the issue.


If this is a one-time operation, it would seem easier to rename the 
columns.  In some cases, even if it's a little more frequent than that.



Barry





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: swapping column values in update

[Barry Newton]

At 06:35 PM 8/1/2006, Martin Jespersen wrote:

I just ran the following sql (on mysql 4.1.20):

  update tbl set col1=col2, col2=col1

To my surprise, mysql updates col1 via col1=col2 before reading it for use 
in col2=col1, so I end up with the same value in both columns, which, of 
course, was not my intention. Thinking about it, this behavior in mysql 
makes perfect sense, so thats not the issue.


If this is a one-time operation, it would seem easier to rename the 
columns.  In some cases, even if it's a little more frequent than that.



Barry



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

swapping column values in update

[Martin Jespersen]

I just ran the following sql (on mysql 4.1.20):

  update tbl set col1=col2, col2=col1

To my surprise, mysql updates col1 via col1=col2 before reading it for 
use in col2=col1, so I end up with the same value in both columns, 
which, of course, was not my intention. Thinking about it, this behavior 
in mysql makes perfect sense, so thats not the issue.


I could of course add a temporary col3 to use as a kind of buffer field,
and do

 
 update tbl set col3=col1, col1=col2, col2=col3, col3=''
 

That seems like a waste to me tho. There must be a smarter way.

So my question is:

Does anyone know of a way to force mysql to read all the values first 
before actually doing the update? Or just has a smarter way of doing this?




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Spreading Database across multiple disks

[Tripp Bishop]

Howdy all,

We're looking at building a new database server and
I'm looking into strategies for optimizing disk i/o.

Bit of background. We will be running a single
database on this box under MySQL 5.0.15. All of the
tables are INNODB. We have about 130 tables in the db.

I've read that it's a good idea to have the innodb log
files written out to a seperate physical drive so that
those operations don't bog down the rest of the
database disk I/O operations. Configuring INNODB to do
that looks straightforward.

Then there's the data. I know that I can create
multiple shared table spaces and locate them on
seperate disks but that doesn't seem to give me
control over where individual tables' data are written
on the disk array. Is there a way using
innodb_file_per_table to control where the individual
.idb files are located in the disk array? Do I have to
use symbolic links to trick innodb or is there a
cleaner way?

Also, I've been told that innodb table spaces never
shrink. Is this true or is there a way to periodically
cleanup the idb files to reclaim unused space?

Cheers,

Tripp

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't get v5.0.22 to work;alternatives?

[Dan Buettner]

http://dev.mysql.com/downloads/
Look for "older releases"

Dan

On 8/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I filed bug #20941 (mysqld seg faults during instance configuration on
XP pro, http://bugs.mysql.com/?id=20941) some time ago and have seen no
real movement on fixing it.  I'm dead in the water.  I can't get MySQL
v5.0 to work on my system.  I'd  like to try installing an older build
as a stop gap (even if it has other, non-critical bugs).  Is there
somewhere I can find an older build?


--
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: can't connect to server using mysqladmin or mysql

[scott . anderson]

I would like to withdraw my earlier bug report with the same subject
line.  I discovered that an erroneous IP address for the server
machine had gotten into /etc/hosts (due to some IP changes in my
organization). Once the DNS error was resolved, I was able to connect
correctly and fix the password problem.

I apologize for the mistake.

>Description:

>How-To-Repeat:

>Fix:


>Submitter-Id:  
>Originator:root
>Organization:
 Wellesley College Computer Science
>MySQL support: none
>Synopsis:  can't connect to server and can't reset root password
>Severity:  critical
>Priority:  high
>Category:  mysql
>Class: sw-bug
>Release:   mysql-5.0.22-standard (MySQL Community Edition - Standard (GPL))
>Server: /usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.22, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  5.0.22-standard
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 56 min 0 sec

Threads: 1  Questions: 876  Slow queries: 0  Opens: 0  Flush tables: 1  Open 
tables: 64  Queries per second avg: 0.261
>C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>C++ compiler:  gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>Environment:

System: Linux puma.wellesley.edu 2.4.21-47.ELsmp #1 SMP Wed Jul 5 20:38:41 EDT 
2006 i686 i686 i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.3/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man 
--infodir=/usr/share/info --enable-shared --enable-threads=posix 
--disable-checking --with-system-zlib --enable-__cxa_atexit 
--host=i386-redhat-linux
Thread model: posix
gcc version 3.2.3 20030502 (Red Hat Linux 3.2.3-56)
Compilation info: CC='gcc'  CFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686'  
CXX='gcc'  CXXFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686'  LDFLAGS=''  
ASFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Jul 25 18:20 /lib/libc.so.6 -> 
libc-2.3.2.so
-rwxr-xr-x1 root root  1512793 Jun 16 07:32 /lib/libc-2.3.2.so
-rw-r--r--1 root root  2468490 Jun 16 06:50 /usr/lib/libc.a
-rw-r--r--1 root root  204 Jun 16 06:36 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' 
'--with-server-suffix=-standard' '--without-embedded-server' '--with-innodb' 
'--with-archive-storage-engine' '--without-bench' '--with-zlib-dir=bundled' 
'--with-big-tables' '--enable-assembler' '--enable-local-infile' 
'--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' 
'--with-pic' '--prefix=/' '--with-extra-charsets=complex' '--with-yassl' 
'--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' 
'--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' 
'--infodir=/usr/share/info' '--includedir=/usr/include' 
'--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=MySQL 
Community Edition - Standard (GPL)' '--with-readline' 'CC=gcc' 'CFLAGS=-O2 -g 
-pipe -march=i386 -mcpu=i686' 'CXXFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686' 
'CXX=gcc' 'LDFLAGS='


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimization

[mos]

At 02:37 PM 8/1/2006, Cesar David Rodas Maldonado wrote:

Hello to all!

How can I optimize this query

select * from table where col1 = 5 && col2 = 15;


Put both fields in the same index index. The first index should be the 
column with the least unique values (col1 by your example).




If I  know that col2 I have 15 rows and col1 I have just 100 rows, I
know that because I save that data in another table.


You could just do:

select count(distinct col1) from table;
select count(distinct col2) from table;

to get the number of distinct values in each  column.



How can I do for search first where is minus number of result?



Not sure what you mean by this, but if you want rows where col1 is negative 
just try:


select * from table where col1 < 0




The two columns are indexes.


Yes but probably you are using 2 different indexes. Putting both columns in 
the same index will make the query faster because you are searching on both 
columns.


Mike 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Optimization

[Cesar David Rodas Maldonado]

Hello to all!

How can I optimize this query

select * from table where col1 = 5 && col2 = 15;

If I  know that col2 I have 15 rows and col1 I have just 100 rows, I
know that because I save that data in another table.

How can I do for search first where is minus number of result?


The two columns are indexes.

Thanks to all.

Re: tune a geometric query

[Jay Pipes]

On Tue, 2006-08-01 at 17:39 +0530, ViSolve DB Team wrote:
> Hello Prashant,
> 
> If you do need the duplicate rows in the final result, use UNION ALL with 
> your query. Otherwise you can opt for UNION as UNION is faster than UNION 
> ALL.

I have never heard of any evidence of this; in fact, it makes more sense
that the reverse would be true, as MySQL would not have to do an
implicit DISTINCT on the outermost resultset.

Prashant:

Please post an EXPLAIN of your original posted SQL query.  Use the /G
flag from the command line client to make it easier to read the results.
Thanks!

-jay


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL 4 vs 5 - threading model change?? Config Problem?

[Travis Rabe]

When I ran version 4, mysql would start as many mysqld process as it
needed.  This would vary depending upon how busy it was.  As a lay
perosn I will refer to this as the threading model.

I have upgraded to version 5 and notice that no matter what it only
runs 1 mysqld.  Everything seems to work fine, but I want to make sure
that mysqld is taking advantage of my multiple processors.  Has the
htreading model changed and now MYSQL will only run one mysqld OR have
I missed something and need to enable it?

Am I getting the best bang for my buck by haveing just one mysqld running?





Travis

can't connect to server using mysqladmin or mysql

[scott . anderson]

>Description:

I'm sorry This is long, but it's because I've tried hard to fix this
myself; please bear with me.

Last week, for unknown reason, my root password for MySQL wasn't
working.  I may have changed it without updating the "/root/.my.cnf"
file.  So, I went to the online documentation (which is usually
excellent) and followed the instructions for resetting the password.
I first tried the one with the init-file and that didn't work, so then
I used the one where you skip the grant tables, and that didn't work
either.  I would update the mysql.user table, and I would see the
changed encrypted password, but the new password wouldn't work.

I should probably have stopped there, but seeing that there was a new,
stable version of MySQL (I was running 4.1.9), I decided to upgrade,
figuring that the instructions for setting the password would take
care of the problem.  In any event, I figured that you'd rather fix
the problem in 5.0.22.

So, I downloaded the RPM and installed it.  Here is the transcript:
# rpm -Uvh MySQL-server-standard-5.0.22-0.rhel3.i386.rpm
Preparing...### [100%]
 
Giving mysqld a couple of seconds to exit nicely
   1:MySQL-server-standard  ### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h puma.wellesley.edu password 'new-password'
See the manual for more instructions.
 
NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!
 
Please report any problems with the /usr/bin/mysqlbug script!
 
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
/usr/bin/mysqlcheck: unknown option '--check-upgrade'
This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.
 
This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1
 
Got a failure from command:
cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql 
--no-defaults --force --user=mysql --host=localhost --database=mysql
Please check the above output and try again.
 
Running the script with the --verbose option may give you some information
of what went wrong.
 
If you get an 'Access denied' error, you should run this script again and
give the MySQL root user password as an argument with the --password= option
Starting MySQL.[  OK  ]

#

Following your instructions, I started the server and ran the
password-setting command.  Here's the transcript:

# service mysql start
Starting MySQL [  OK  ]
# mysqladmin -u root password 'blahblah'
mysqladmin: connect to server at 'puma' failed
error: 'Lost connection to MySQL server during query'
# ps -ef | grep mysql
root 12811 1  0 11:11 pts/000:00:00 /bin/sh /usr/bin/mysqld_safe 
--datadir=/var/lib/mysql --pid-file=/var/lib/mysql/puma.wellesley.edu.pid
mysql12832 12811  0 11:11 pts/000:00:00 /usr/sbin/mysqld --basedir=/ 
--datadir=/var/lib/mysql --user=mysql 
--pid-file=/var/lib/mysql/puma.wellesley.edu.pid --skip-locking

So, it looks like the server is running, but for some reason I can't
connect.  I've tried a bunch of other ways (such as starting it with
--skip-grant-tables) but no luck.  I've checked the error log, and
it's quite clean.  For example:

060801 11:39:31  mysqld started
060801 11:39:31  InnoDB: Started; log sequence number 0 81455176
060801 11:39:31 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.22-standard'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  
MySQL Community Edition - Standard (GPL)

Interestingly, if I use "nmap", it shows that port 3306 is open, but
if I try to telnet to that port, it never responds.

>How-To-Repeat:

Start mysql server in the usual way, which yields no errors, then try
something as simple as

# mysqladmin -u root ping

This will hang for a very long time (several minutes) and eventually
say:

mysqladmin: connect to server at 'puma' failed
error: 'Lost connection to MySQL server during query'

>Fix:

None that I know of.  This is a real problem for me. 

>Submitter-Id:  
>Originator:root
>Organization:
  Wellesley College Computer Science
>MySQL support: none
>Synopsis:  can't connect to server and can't reset root password
>Severity:  critical
>Priority:  high
>Category:  mysql
>Class: sw-bug
>Release:   mysql-5.0.22-standard (MySQL Community Edition - Standard (GPL))

>C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>C++ compiler:  gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>Environment:

System: Linux puma.wellesley.edu 2.4.21-

mysqldump corrupting utf8 data

[Sean O'Hara]

Hi All,

I've been googling all morning trying to find info on how to do a  
mysqldump of a utf8 encoded database from which I can restore without  
corrupting all the non ascii characters. If anyone has any pointers  
on this, I'd be most grateful.


Here is my setup. I am building a ruby on rails app and all the data  
is being entered from that application. The data is displayed fine if  
when it hasn't undergone a backup with msyqldump. I'm using mysql  
server 4.1.16 on Fedora Core 4. Here is an example show create table  
on one of the relevant tables:


artists | CREATE TABLE `artists` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `biography` text,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `sort_name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

The restored table has the same show create table definition. I've  
used enca on the dump file to see if it's been encoded properly:

enca -L none testdump1.sql
Universal transformation format 8 bits; UTF-8

So that seems to be in order. Here's the top of the dump file:

  1 -- MySQL dump 10.9
  2 --
  3 -- Host: localhostDatabase: alienrails_production
  4 -- --
  5 -- Server version   4.1.16
  6
  7 /*!40101 SET  
@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8 /*!40101 SET  
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  9 /*!40101 SET  
@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

 10 /*!40101 SET NAMES utf8 */;
 11 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,  
UNIQUE_CHECKS=0 */;
 12 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,  
FOREIGN_KEY_CHECKS=0 */;
 13 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,  
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

 14 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 15
 16 --
 17 -- Table structure for table `artist_images`
 18 --

Obviously I'm mussing something, but I have no idea what.

Thanks in advance,
Sean


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can't get v5.0.22 to work;alternatives?

[cnelson]

I filed bug #20941 (mysqld seg faults during instance configuration on
XP pro, http://bugs.mysql.com/?id=20941) some time ago and have seen no
real movement on fixing it.  I'm dead in the water.  I can't get MySQL
v5.0 to work on my system.  I'd  like to try installing an older build
as a stop gap (even if it has other, non-critical bugs).  Is there
somewhere I can find an older build?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL for integrity RTOS

[ravi.karatagi]

Hi All,
I am a new to this mailing list. Also to Mysql.
Anybody knows how mysql is installed and used in Integrity RTOS.
Plz fwd if any docs are available.

Thanks,
Regards,
Ravi K


The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email.

www.wipro.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: tune a geometric query

[ViSolve DB Team]

Hello Prashant,

If you do need the duplicate rows in the final result, use UNION ALL with 
your query. Otherwise you can opt for UNION as UNION is faster than UNION 
ALL.


Thanks,
ViSolve DB Team.

- Original Message - 
From: "PRASHANT N" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 01, 2006 5:28 PM
Subject: tune a geometric query



hi,

we are working on automotive traking solutions and insert our location 
records into the mysql database v 4.1.20. If we want search for a 
particular record, its taking too long time and mysql is identifying the 
queries as slow queries. I have attached the queries.


How to  optimize the attache query ?

regards
shann


___
Hot new product - Spider Networks introduces stunning online ePortfolio 
solution for students and teachers



http://www.spider-networks.net/solutions/eportfolio.html







select A.name,A.district,x(GeomFromText(AsText(A.geo))) as
x,y(GeomFromText(AsText(A.geo))) as
y,(GLength(LineStringFromWKB(LineString(AsBinary(geo),AsBinary(GeomFromText('POINT(76.67472
11.83884)')) as Distance FROM (select geo,name,district from
cities_point union all select geo,name,district from cities_font_point
union all select geo,name,district from State_Highways_point union all
select geo,name,district from Other_Roads_point union all select
geo,name,district from Major_Roads_point union all select
geo,name,district from Vet_Clinics_point union all select
geo,name,district from University_point union all select
geo,name,district from Tourist_Info_point union all select
geo,name,district from Temples_point union all select geo,name,district
from Taxi_Stands_point union all select geo,name,district from
Stadiums_point union all select geo,name,district from
Sports_Clubs_point union all select geo,name,district from
Shops_WhiteGds_point union all select geo,name,district from
Shops_Sports_point union all select geo,name,district from
Shops_RealEstate_point union all select geo,name,district from
Shops_Music_point union all select geo,name,district from
Shops_Misc_point union all select geo,name,district from Shops_LPG_point
union all select geo,name,district from Shops_Jewellery_point union all
select geo,name,district from Shops_Furnt_point union all select
geo,name,district from Shops_Footwear_point union all select
geo,name,district from Shops_Computer_point union all select
geo,name,district from Shops_Chemists_point union all select
geo,name,district from Shops_Bakery_point union all select
geo,name,district from Shops_Apparel_point union all select
geo,name,district from Shopping_Ctrs_point union all select
geo,name,district from Services_Travel_point union all select
geo,name,district from Services_Professional_point union all select
geo,name,district from Services_Financial_point union all select
geo,name,district from Service_Stations_point union all select
geo,name,district from Schools_point union all select geo,name,district
from Restaurants_point union all select geo,name,district from
Religious_Pls_Oth_point union all select geo,name,district from
Railway_Stations_point union all select geo,name,district from
Railway_Reservations_point union all select geo,name,district from
PreSchools_point union all select geo,name,district from
PostOffices_point union all select geo,name,district from
PoliceStations_point union all select geo,name,district from
PoliceChaukis_point union all select geo,name,district from
PetrolPumps_point union all select geo,name,district from Parks_point
union all select geo,name,district from OtherInstt_point union all
select geo,name,district from Offices_point union all select
geo,name,district from Office_Airlines_point union all select
geo,name,district from Museums_point union all select geo,name,district
from Mosques_point union all select geo,name,district from Misc_point
union all select geo,name,district from Libraries_point union all select
geo,name,district from LevelCrossing_point union all select
geo,name,district from Industries_point union all select
geo,name,district from Hotels_point union all select geo,name,district
from Hostels_point union all select geo,name,district from
Hospitals_point union all select geo,name,district from
Historical_Pls_point union all select geo,name,district from
Gymnasiums_point union all select geo,name,district from Gurdwaras_point
union all select geo,name,district from Graveyards_point union all
select geo,name,district from GovtOffices_point union all select
geo,name,district from Dispensaries_point union all select
geo,name,district from Discotheques_point union all select
geo,name,district from Diagnostic_Ctrs_point union all select
geo,name,district from CyberCafes_point union all select
geo,name,district from Cultural_Centres_point union all select
geo,name,district from Crematory_point union all select
geo,name,district from Cou

Re: tune a geometric query

[chris smith]

we are working on automotive traking solutions and insert our location records 
into the mysql database v 4.1.20. If we want search for a particular record, 
its taking too long time and mysql is identifying the queries as slow queries. 
I have attached the queries.


You've posted this multiple times, that's just going to annoy everyone.

Break the query down and find out which part is slow. Each union part
should work by itself, so take each one and tune that. Then worry
about the whole lot.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

tune a geometric query

[PRASHANT N]

hi,

we are working on automotive traking solutions and insert our location records 
into the mysql database v 4.1.20. If we want search for a particular record, 
its taking too long time and mysql is identifying the queries as slow queries. 
I have attached the queries.

How to  optimize the attache query ?

regards
shann


___ 
Hot new product - Spider Networks introduces stunning online ePortfolio 
solution for students and teachers


http://www.spider-networks.net/solutions/eportfolio.html
select A.name,A.district,x(GeomFromText(AsText(A.geo))) as 
x,y(GeomFromText(AsText(A.geo))) as 
y,(GLength(LineStringFromWKB(LineString(AsBinary(geo),AsBinary(GeomFromText('POINT(76.67472
 
11.83884)')) as Distance FROM (select geo,name,district from 
cities_point union all select geo,name,district from cities_font_point 
union all select geo,name,district from State_Highways_point union all 
select geo,name,district from Other_Roads_point union all select 
geo,name,district from Major_Roads_point union all select 
geo,name,district from Vet_Clinics_point union all select 
geo,name,district from University_point union all select 
geo,name,district from Tourist_Info_point union all select 
geo,name,district from Temples_point union all select geo,name,district 
from Taxi_Stands_point union all select geo,name,district from 
Stadiums_point union all select geo,name,district from 
Sports_Clubs_point union all select geo,name,district from 
Shops_WhiteGds_point union all select geo,name,district from 
Shops_Sports_point union all select geo,name,district from 
Shops_RealEstate_point union all select geo,name,district from 
Shops_Music_point union all select geo,name,district from 
Shops_Misc_point union all select geo,name,district from Shops_LPG_point 
union all select geo,name,district from Shops_Jewellery_point union all 
select geo,name,district from Shops_Furnt_point union all select 
geo,name,district from Shops_Footwear_point union all select 
geo,name,district from Shops_Computer_point union all select 
geo,name,district from Shops_Chemists_point union all select 
geo,name,district from Shops_Bakery_point union all select 
geo,name,district from Shops_Apparel_point union all select 
geo,name,district from Shopping_Ctrs_point union all select 
geo,name,district from Services_Travel_point union all select 
geo,name,district from Services_Professional_point union all select 
geo,name,district from Services_Financial_point union all select 
geo,name,district from Service_Stations_point union all select 
geo,name,district from Schools_point union all select geo,name,district 
from Restaurants_point union all select geo,name,district from 
Religious_Pls_Oth_point union all select geo,name,district from 
Railway_Stations_point union all select geo,name,district from 
Railway_Reservations_point union all select geo,name,district from 
PreSchools_point union all select geo,name,district from 
PostOffices_point union all select geo,name,district from 
PoliceStations_point union all select geo,name,district from 
PoliceChaukis_point union all select geo,name,district from 
PetrolPumps_point union all select geo,name,district from Parks_point 
union all select geo,name,district from OtherInstt_point union all 
select geo,name,district from Offices_point union all select 
geo,name,district from Office_Airlines_point union all select 
geo,name,district from Museums_point union all select geo,name,district 
from Mosques_point union all select geo,name,district from Misc_point 
union all select geo,name,district from Libraries_point union all select 
geo,name,district from LevelCrossing_point union all select 
geo,name,district from Industries_point union all select 
geo,name,district from Hotels_point union all select geo,name,district 
from Hostels_point union all select geo,name,district from 
Hospitals_point union all select geo,name,district from 
Historical_Pls_point union all select geo,name,district from 
Gymnasiums_point union all select geo,name,district from Gurdwaras_point 
union all select geo,name,district from Graveyards_point union all 
select geo,name,district from GovtOffices_point union all select 
geo,name,district from Dispensaries_point union all select 
geo,name,district from Discotheques_point union all select 
geo,name,district from Diagnostic_Ctrs_point union all select 
geo,name,district from CyberCafes_point union all select 
geo,name,district from Cultural_Centres_point union all select 
geo,name,district from Crematory_point union all select 
geo,name,district from Couriers_point union all select geo,name,district 
from Computer_Instt_point union all select geo,name,district from 
Colleges_point union all select geo,name,district from Clubs_point union 
all select geo,name,district from Clinics_point union all select 
geo,name,district from Cinemas_point union all select geo,name,district 
from Church_point union all select geo,name,di

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Dan Nelson, Jochem van Dieten, and Chris

Thx!

I think I understand it after your replies.

Regards,
Leo Huang

2006/7/30, Dan Nelson <[EMAIL PROTECTED]>:

In the last episode (Jul 29), Jochem van Dieten said:
> On 7/28/06, Dan Nelson wrote:
> >In the last episode (Jul 28), leo huang said:
> >>So,  the deleted rows' disk space in tablespace can't re-use when I
> >>use Innodb, can it? And the tablespace is growing when we update
> >>the tables, even the amount of rows do not increase.
> >
> >It can be re-used after the transaction has been committed
>
> After all transactions that were started before the transaction that
> did the delete committed have either been committed or rolled back.

Ouch.

--
Dan Nelson
[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: want to insert unicode myanmar characters into MySQL database

[Visolve DB Team]

Hi Khaing su yee,

If the character set is not available in your MySQL, then add the particular 
character set to MySQL. You must have a MySQL source distribution to use 
these instructions.
First, decide whether the character set is simple or complex and then 
proceed with it.


/the name of your character set is represented by "myanmar".

If the "myanmar" is a simple character set,

Add "myanmar" to the end of the sql/share/charsets/Index file. Assign a 
unique number to it.

Create the file sql/share/charsets/myanmar.conf.
(...The syntax for the file is very simple:
Comments start with a '#' character and proceed to the end of the line.
Words are separated by arbitrary amounts of whitespace.
When defining the character set, every word must be a number in hexadecimal 
format.)


Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS 
lists in configure.in.

Reconfigure, recompile, and test.

Thanks
Visolve DB Team.

- Original Message - 
From: "khaing su yee" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 01, 2006 9:28 AM
Subject: want to insert unicode myanmar characters into MySQL database



I use Toad for MySQL 2.0 and SQLyog 5.02.
I want to insert unicode myanmar characters.
I change uft8 charset and utf8_unicode_ci collation.
But I can't insert myanmar characters.
What is needed to do?
Please tell me.

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help on EXPLAIN in rating queries

[Aleksandar Bradaric]

Hi,

> I am trying to JOIN 2 tables TBL1 and TBL2 on TBL1.fld_id
> = TBL2.fld_id . And finally I filter out the results that
> i need in the where clause using
>
> where TBL1.fld_col = 100;
>
> Running an EXPLAIN shows that it is an impossible where
> condition. This may be because there may be no rows with 
> fld_col = 100. But in future there could be rows with this
> value in fld_col. So how should I rate this query?
> Should I consider this query as a bad one just because it
> has an impossible where currently?

No,  it's  not a bad query - at the moment it's very fast as
it returns no data :) As for the future, try running EXPLAIN
with  an existing value instead of `100`. In this particular
case, you will probably want an index on `fld_col` for it to
run smoothly.


Best regards,
Aleksandar


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help on EXPLAIN in rating queries

[Ratheesh K J]

Helo all,

I need explanation on EXPLAIN here.

I am trying to JOIN 2 tables TBL1 and TBL2 on TBL1.fld_id = TBL2.fld_id . And 
finally I filter out the results that i need in the where clause using

where TBL1.fld_col = 100;

Running an EXPLAIN shows that it is an impossible where condition. This may be 
because there may be no rows with  fld_col = 100. But in future there could be 
rows with this value in fld_col. So how should I rate this query?
Should I consider this query as a bad one just because it has an impossible 
where currently?

Thanks

Ratheesh Bhat K J