Need Help Migrating DB from MySQL 5.0.x to MySQL 4.0.x

[Shaun Adams]

When I perform a dump in mysql5 to  mysql 4 DB, I get the error (below).
Does anyone know how I can resolve this?

 

QUERY (windows server from the cmd prompt)

mysqldump --lock-tables --user=root [SOURCE DB] | mysql --user=[USERNAME]
--password=[PASSWORD] --host=[HOST]  [TARGET DB]

 

ERROR MESSAGE RETURNED

ERROR 1193 (0) at line 23: Unknown system variable
'character_set_client'

mysqldump: Got errno 22 on write

 

 

 

 

 

Re: Need Help Migrating DB from MySQL 5.0.x to MySQL 4.0.x

[Werner D.]

Shaun Adams schrieb:
 When I perform a dump in mysql5 to  mysql 4 DB, I get the error (below).
 Does anyone know how I can resolve this?
 
  
 
 QUERY (windows server from the cmd prompt)
 
 mysqldump --lock-tables --user=root [SOURCE DB] | mysql --user=[USERNAME]
 --password=[PASSWORD] --host=[HOST]  [TARGET DB]
 
  
 
 ERROR MESSAGE RETURNED
 
 ERROR 1193 (0) at line 23: Unknown system variable
 'character_set_client'
 
 mysqldump: Got errno 22 on write
 

try the mysqldump-option --compatible=mysql40


Werner

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

Upgrading MySQL 4.0 to 5.0

[John Pacylowski]

Has anyone upgraded MySQL 4.0 to 5.0 on a Mac running Mac OS X  
Panther, 10.3.9 Sever with Lasso 8.5.4?  I'm debating whether to just  
upgrade to MySQL to 5.0 or jump to Apples Leopard Server.


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

Mysql 4.0 Adding fields to large tables

[James Sherwood]

Hello all,

I am trying to add a field to a very large table.  The problem is that mysql 
locks up when trying to do so.  I even tried deleting the foreign keys on 
the table and it wont even let me do that, again locking up.


It works for around 5 minutes or so then just either locks or the database 
dies and I have to restart it.


Any ideas how to do this?

Thanks,
James 



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

How to emulate a DATE() in MySQL 4.0?

[mikesz]

Hello mysql,

I have a PHP script that I have written and it runs beautifully on
current MySQL and PHP sites BUT, it barfs on MySQL 4.0 because I am
using the DATE() to extract the Date from a timestamp. Does anyone
remember how to do an equivalent function in 4.0? I have searched
through hundreds of pages and can not find anything that looks like it
might work. Essentially I need to extract the -MM-DD from a
timestamp field that I populated in a PHP script so I can do a diff
against a current timestamp.

Here is the query that works on current and falls over dead on 4.0:

SELECT count(SimulatedTimeSet) as count_live FROM `Simulator`
WHERE DATE( `SimulatedTimeSet` ) = DATE(CURRENT_TIMESTAMP)

I appreciate in advance any clue to get me in the right directions
other than upgrading the system. I don't have any control over that
one...

TIA
-- 
Best regards,
 mikesz  mailto:[EMAIL PROTECTED]


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

Re: How to emulate a DATE() in MySQL 4.0?

[Baron Schwartz]

[EMAIL PROTECTED] wrote:

Hello mysql,

I have a PHP script that I have written and it runs beautifully on
current MySQL and PHP sites BUT, it barfs on MySQL 4.0 because I am
using the DATE() to extract the Date from a timestamp. Does anyone
remember how to do an equivalent function in 4.0? I have searched
through hundreds of pages and can not find anything that looks like it
might work. Essentially I need to extract the -MM-DD from a
timestamp field that I populated in a PHP script so I can do a diff
against a current timestamp.

Here is the query that works on current and falls over dead on 4.0:

SELECT count(SimulatedTimeSet) as count_live FROM `Simulator`
WHERE DATE( `SimulatedTimeSet` ) = DATE(CURRENT_TIMESTAMP)


Try FROM_UNIXTIME().

Have you read the manual section on date and time functions?  There are 
quite a few.  You don't need to read hundreds of pages.


http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Baron

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

MySQL 4.0, max_allowed_packet and blob insert

[Kilian]

Hello all,

I'm running MySQL 4.0.24 on Debian Linux and have a problem with a very
large blob insert query - it always fails with #2020: Got packet bigger
than 'max_allowed_packet'. I searched the docs and found some
references that this value can not be greater than 1 GB with MySQL 4.x -
indeed, when I change it to some value greater than 1 GB, it has no
effect, the value just stays at 1 GB (1073740800 Bytes).

The query is from an application (mnoGoSearch), so I can't just go and
split the query into smaller ones.

The tables in question are all in MyISAM format.

What can I do about this? I have no clue about how to solve this
problem, any hint is appreciated!

Regards,
kil

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

Mysql 5.0.* client connection to Mysql 4.0.* Database

[dpgirago]

Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27 
system? I know there is an old_passwords function. Can this be used 
somehow from a DOS command prompt?

David

Re: Mysql 5.0.* client connection to Mysql 4.0.* Database

[Michael Dykman]

Have youtried this yet?  While 4.0 client can't connect to a v.5
server unless the passwords have explicitly beewen stored as
old_password, I have never run into an issue connecting a 5.x client
to a 4.0 server.

- michael


On 6/7/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:

Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27
system? I know there is an old_passwords function. Can this be used
somehow from a DOS command prompt?

David



--
- michael dykman
- [EMAIL PROTECTED]

- All models are wrong.  Some models are useful.

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

Re: Mysql 5.0.* client connection to Mysql 4.0.* Database

[dpgirago]

Yes, I did try. The issue was the security level setting on the new DB 
server, and not the password stuff. I've upgraded apache, mysql, and php 
in the last few weeks, and 3-4 issues became painfully apparent when going 
from php4 to php5, so I guess I was operating under similar assumptions 
and frustrations. 

My apologies to all. I'll try to keep from functioning in iamadummy mode.

David


**
Have you tried this yet?  While 4.0 client can't connect to a v.5
server unless the passwords have explicitly been stored as
old_password, I have never run into an issue connecting a 5.x client
to a 4.0 server.

 - michael


On 6/7/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27
 system? I know there is an old_passwords function. Can this be used
 somehow from a DOS command prompt?

 David


-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

upgrading from mysql 4.0 to 4.1 : TIMESTAMP OTHER FEATURES

[tere]

Hi! I would like to upgrade the database of my organisation from
mysql4.0.22 from 4.1. We use Debian.
I've read info in the manual, but i don't have things clear.

We process data of DB with scripts, and  I'm annoyed because the change
of format  of timestamp, is there any command in 4.1 to obtain  this
info in the previous format (4.0) ??? I want that out in 4.1 as
MMDDHHMMSS

Furthermore, my databases are in latin1, i've read that 4.1 take data in
utf8, but i don't understand reading manual how this affect to my info.

And to finish, do i have to keep more features in mind

Thanks

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

Re: upgrading from mysql 4.0 to 4.1 : TIMESTAMP OTHER FEATURES

[Dan Buettner]

Regarding the format of TIMESTAMP columns, one of the user comments on
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
offers the solution below:

Posted by Kjell Arne Rekaa on April 14 2005 11:11pm
If you want the same view of a timestamp field in 4.1.x as it was in
in earlier mysql versions, without the delimiter characters in date
and time, simply add a +0 to the column name:
mysql create table date (remember timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql insert date values ('2005051712');
Query OK, 1 row affected (0.00 sec)
mysql select remember from date;
+-+
| remember|
+-+
| 2005-05-17 12:00:00 |
+-+
1 row in set (0.00 sec)
mysql select remember+0 from date;
++
| remember+0 |
++
| 2005051712 |
++
1 row in set (0.00 sec)

As far as your character set, I believe that is stored by table, so
your data should remain OK.

Another major consideration with 4.1 is that passwords changed
considerably from 4.0 to 4.1:
http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html

HTH,
Dan



On 12/20/06, tere [EMAIL PROTECTED] wrote:

Hi! I would like to upgrade the database of my organisation from
mysql4.0.22 from 4.1. We use Debian.
I've read info in the manual, but i don't have things clear.

We process data of DB with scripts, and  I'm annoyed because the change
of format  of timestamp, is there any command in 4.1 to obtain  this
info in the previous format (4.0) ??? I want that out in 4.1 as
MMDDHHMMSS

Furthermore, my databases are in latin1, i've read that 4.1 take data in
utf8, but i don't understand reading manual how this affect to my info.

And to finish, do i have to keep more features in mind

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]

Subquery Problem With mysql-4.0 (Works with mysql-5.0)

[MySQL Nexeia]

I face one problem with mysql4.0

I've two tables, one called company and the other called favorites.  The
relation between those two tables is CO_ID on the company table and
MASTER_CO_ID on the favorites table. When I run the following query it works
fine on mysql-5.0 but it give error with mysql-4.0.

Query**
SELECT c.MASTER_CO_ID as Expr1 FROM company
LEFT OUTER JOIN (select MASTER_CO_ID,SLAVE_CO_ID from favorites where
MASTER_CO_ID = '1' GROUP BY MASTER_CO_ID,SLAVE_CO_ID) c ON company.CO_ID =
c.SLAVE_CO_ID
GROUP BY c.MASTER_CO_ID
***

Error with mysql4.0
#1064 - You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select MASTER_CO_ID,SLAVE_CO_ID from favorites where MAS
***


I have two tables company and favorites, like

CREATE TABLE `company` (
 `CO_ID` varchar(36) NOT NULL default '',
 `CO_NAME` varchar(50) default NULL,
   PRIMARY KEY  (`CO_ID`),
 FULLTEXT KEY `CO_NAME`
(`CO_NAME`,`INDUSTRY_NAME`,`CO_ADDR1`,`CO_ADDR2`,`CO_PIN`,`CO_URL`,`CO_INFO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `favorites` (
 `FAV_ID` bigint(20) NOT NULL auto_increment,
 `MASTER_CO_ID` varchar(36) default NULL,
 `SLAVE_CO_ID` varchar(36) default NULL,
 PRIMARY KEY  (`FAV_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

Re: Subquery Problem With mysql-4.0 (Works with mysql-5.0)

[Pooly]

2006/6/5, MySQL Nexeia [EMAIL PROTECTED]:

I face one problem with mysql4.0

I've two tables, one called company and the other called favorites.  The
relation between those two tables is CO_ID on the company table and
MASTER_CO_ID on the favorites table. When I run the following query it works
fine on mysql-5.0 but it give error with mysql-4.0.

Query**
SELECT c.MASTER_CO_ID as Expr1 FROM company
LEFT OUTER JOIN (select MASTER_CO_ID,SLAVE_CO_ID from favorites where
MASTER_CO_ID = '1' GROUP BY MASTER_CO_ID,SLAVE_CO_ID) c ON company.CO_ID =
c.SLAVE_CO_ID
GROUP BY c.MASTER_CO_ID
***




MySQL 4.0.x does not support subquery. Subqueries are only supported
starting with 4.1.




Error with mysql4.0
#1064 - You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select MASTER_CO_ID,SLAVE_CO_ID from favorites where MAS
***


I have two tables company and favorites, like

CREATE TABLE `company` (
  `CO_ID` varchar(36) NOT NULL default '',
  `CO_NAME` varchar(50) default NULL,
PRIMARY KEY  (`CO_ID`),
  FULLTEXT KEY `CO_NAME`
(`CO_NAME`,`INDUSTRY_NAME`,`CO_ADDR1`,`CO_ADDR2`,`CO_PIN`,`CO_URL`,`CO_INFO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `favorites` (
  `FAV_ID` bigint(20) NOT NULL auto_increment,
  `MASTER_CO_ID` varchar(36) default NULL,
  `SLAVE_CO_ID` varchar(36) default NULL,
  PRIMARY KEY  (`FAV_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;





--
http://www.w-fenec.org/

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

Query Browser and MySQL 4.0 - UTF-8

[Yves Goergen]

Hello everybody,

I'm using Query Browser 1.1.17 on Windows with a MySQL 4.0 database
server on Linux and was just editing a piece of text for a web CMS in
the database. But what do I get to see there? The content was stored as
UTF-8, although the website uses ISO-8859-1 encoding, the data was
stored in that encoding before, it was displayed correctly in Query
Browser editor and the database server doesn't transparently support
Unicode at all. Why the hell is it UTF-8 now? The entire system isn't
supposed to support that encoding, yet is it used. I haven't found any
option to turn it off of course, because it is not supported. So where's
that hidden option please to tell Query Browser to NOT use any kind of
Unicode knowledge?

-- 
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Does the movement of the trees make the wind blow?
http://newsboard.unclassified.de - Unclassified NewsBoard Forum

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

~75% performance drop upgrading from Mysql 4.0 to 5.0

[Joe Kislo]

Here are the timings:

64-bit 5.0 Single Thread 1:00:12.17 total (~76% slower)
64-bit 4.1 Single Thread 41:38.07 total (~20% slower)
64-bit 4.0 Single Thread 34:50.23 total

I have been trying to get a stable configuration for a 64-bit mysql on
ubuntu for the past 6-8 months, and have developed a number of stress
tests to try to isolate problems.  I've been able to successfully
discover several bugs in MySQL (or libc) running in 64-bit mode.  I've
recently started running these tests against 4.1 and 5.0 versions of
MySQL, but I have discovered very very different performance
characteristics compared to MySQL 4.0.  

For this test, it imports one of our customer databases, records the
import time, drops the database, and restarts.  I've run this test
against the 4.0, 4.1 and 5.0 binaries.  I've found a *dramatic*
performance drop on the 4.1 and 5.0 lines against the 4.0 line of MySQL
when executing this test.  The tests and configurations are identical.
This test may be lopsided (insert + alter table enable keys), however it
is something that we actually do daily in our production environment.
If we were to consider upgrading our production environment from 4.0 to
5.0, a 75% drop in performance would not be acceptable for these types
of operations.

I ran these tests for a minimum of 24 hours each (although there was
very little variance between the 1st and the last run).  Each
installation was a fresh mysql binary installation, with newly built
grant tables (EG: blank).  The machine is a 2-cpu 2.4GHz Opteron 4GB
Memory with a 4 disc RAID 0+1 array, running Ubuntu Breezy.  I ran these
tests against the 32-bit version for each of those versions, and saw
similar timings, except on average they were 11% slower than their
corresponding 64-bit version.  I also ran these tests with two threads,
(operating on separate databases), and found similar performance hits
with MySQL 4.1 and 5.0 and an overall 17% drop in performance against
single threaded operation.

All my.cnf files were identical between the versions, except that I
changed the datadir and language settings (to point to the appropriate
directory).  I have attached the my.cnf file.  I am using MyISAM tables
for all tests.

The test harness is very simple:

#!/bin/zsh
while true; do
  echo create database $1
| /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/bin/mysql -u
root
  time zcat TEST_DATABASE.mysql.gz
| /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/bin/mysql -u
root $1
  echo drop database $1
| /var/lib/mysql/mysql-standard-5.0.17-linux-x86_64-glibc23/bin/mysql -u
root
  date
done

I thought perhaps MySQL 5.0 may be doing a better job determining index
cardinalities than 4.0, and as a result there would be no need to do an
optimize-db after an insert.  I modified the test to insert the
database, then optimize each of the tables.  I discovered the same test
execution time (MySQL 5.0 ~75% slower) and MySQL 4.0 was able to
optimize the tables 20% faster.

Although I cannot provide our customer data, I can show you an example
of what the import file looks like:

CREATE TABLE ABCState (
  userID int(11) NOT NULL default '0',
  roleID int(11) NOT NULL default '0',
  aiFilter int(11) default NULL,
  PRIMARY KEY  (userID,roleID)
) TYPE=MyISAM;

--
-- Dumping data for table `ABCState`
--

/*!4 ALTER TABLE ABCState DISABLE KEYS */;
LOCK TABLES ABCState WRITE;
INSERT INTO ABCState VALUES
(15,4,8),(20,4,8),(21,4,8),(22,4,8),(19,5,8),(40,4,8),(42,4,8),(38,4,8),(39,4,8),(43,4,8),(33,4,8),(27,
4,8),(28,4,8),(26,4,8),(25,4,8),(34,4,8),(32,4,8),(35,4,1),(31,4,8),(24,4,8),(36,4,8),(37,4,8),(30,4,8),(29,4,8),(47,6,8),(66,6,8),(64,6,8),(46,
6,8),(67,10,8),(60,6,8),(68,10,8),(69,10,8),(44,5,8),(70,10,8),(71,10,8),(72,5,8),(73,10,8),(77,6,8),(76,6,8),(75,4,8),(74,4,8),(78,4,8),(76,4,8
),(79,4,8),(77,4,8),(18,5,8),(82,4,8),(11,5,8),(81,5,8),(84,4,8),(85,4,8),(106,4,8),(107,4,8),(31,6,8),(108,4,8),(108,6,8),(110,6,8),(109,6,8),(
111,4,8),(6,5,8),(35,6,8),(36,6,8),(112,6,8),(113,6,8),(114,6,8),(115,4,8),(116,4,8),(120,6,8),(16,5,8),(121,6,8),(118,4,8),(119,4,8),(117,4,8),
(126,4,8),(127,4,8),(128,4,8),(129,4,8),(130,4,8),(123,4,8),(126,6,8),(133,4,8),(132,4,8),(132,6,8),(134,4,8),(135,10,8),(38,6,8),(136,10,8),(11
0,4,8),(145,4,8),(137,4,8),(144,4,8),(141,4,8),(140,4,8),(146,4,8),(142,4,8),(148,4,8),(147,4,8),(138,4,8),(149,4,8),(141,6,8),(146,6,8),(142,6,
8),(144,6,8),(147,10,8),(75,10,8),(150,4,8),(137,10,8),(156,10,8),(157,4,8),(158,4,8),(159,4,8),(160,4,8),(113,4,8),(165,4,8);
UNLOCK TABLES;
/*!4 ALTER TABLE ABCState ENABLE KEYS */;

the dump was generated from a mysqldump -l --add-locks --extended-insert
--quick --all --disable-keys.  There are millions of records, the
compressed GZ file is 500 megs.  There is a good array of varchar, char,
blob fields across 100+ tables.

Does anybody have any idea why 5.0 is performing so poorly?  Since I am
not using any of the new 4.1 or 5.0 features, I would

Mysql 4.0 always executes case insensitive queries

[Nico Sabbi]

Hi,
my mysql always executes case insensitive queries:


SELECT username FROM workflow.user WHERE username = 'NicO'  LIMIT 1;
+--+
| username |
+--+
| nico |
+--+
1 row in set (0.01 sec)



that field is of varchar(255) type.

I don't understand the reason for this behavior.
What should I check?

Thanks,

Nico


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

Re: Mysql 4.0 always executes case insensitive queries

[Wolfram Kraus]

Nico Sabbi wrote:

Hi,
my mysql always executes case insensitive queries:


SELECT username FROM workflow.user WHERE username = 'NicO'  LIMIT 1;
+--+
| username |
+--+
| nico |
+--+
1 row in set (0.01 sec)



that field is of varchar(255) type.

I don't understand the reason for this behavior.
What should I check?

Thanks,

Nico


You need the binary function:

http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html

HTH,
Wolfram


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

Re: Mysql 4.0 always executes case insensitive queries

[Shen139]

You should use:
SELECT username FROM workflow.user WHERE username LIKE BINARY 'NicO'  LIMIT
1;

reference:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

On 12/12/05, Nico Sabbi [EMAIL PROTECTED]  wrote:

 Hi,
 my mysql always executes case insensitive queries:


 SELECT username FROM workflow.user WHERE username = 'NicO'  LIMIT 1;
 +--+
 | username |
 +--+
 | nico |
 +--+
 1 row in set (0.01 sec)



 that field is of varchar(255) type.

 I don't understand the reason for this behavior.
 What should I check?

Thanks,

 Nico


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




--

http://www.openwebspider.org
http://www.eviltime.com

Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode

[AmirBehzad Eslami]

Mohsen wrote:
  
   But himself solved his problem.
   with : mysql_query(SET NAMES utf8);
   Even 4.0.x
  
  Wrong. 
  
  I decided to prepare two different versions for my software:
  - A MySQL 4.0-friendly version using Romanizing method (Hats off to you, 
Ehsan)
  - A MySQL 4.1-compatible version.
  
  The code you mentioned belongs to the 2nd version.
  
   SET NAMES indicates what is in the SQL statements that the client  sends. 
Thus, SET NAMES 'cp1251' tells the server “future incoming  messages from this 
client are in character set cp1251.” It also  specifies the character set for 
results that the server sends back to  the client. (For example, it indicates 
what character set column values  are if you use a SELECT statement.) 
  
  MySQL Manual 4.1 - 10.3.6. Connection Character Sets and Collations.
  
  Kind Regards,
  Behzad
  



-
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode

[Alec . Cawley]

AmirBehzad Eslami [EMAIL PROTECTED] wrote on 24/11/2005 18:36:25:

 On 24/11/2005, Alec worte:
 
I think this is your problem: MySQL does not properly support 
Unicode 
until version 4.1. I am successfully using FullText with MySQL 
 4.1 to sort 
UTF-8 encoded Japanese text. I see no reason why it should not work 
for 
Arabic - if you upgrade.
 
   Dear Alec,
   Thank you for your prompt reply.
 
   You're right. That's my problem. I admit it.
 
   But I'm really unable to solve this by upgrading.
   Many of the Hosting Companies, which I use their services [even 
 the HostRocket.com] still use MySQL 4.0 !!!

Googling for hosting mysql 4.1 gives a number of companies offering 
MySQL 4.1 and PHP 5. Obviously I cannot comment on their competence.

 
   1) Would you recommend any hosting company with PHP 5 and MySQL 4.1 
support?
 
   2) What about if my client only use MySQL 4.0 for his reasons. In 
 this  case, I really can't use FULL-TEXT search? There is no any 
solution?

No. It is inherent in the Fulltext mechanism that the text indexing engine 
knows which bytes represent indexable characters and which separators. 
Before 4.1, Fulltext was 8-bit only - end of story.

Alec


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

MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode

[AmirBehzad Eslami]

Dear list,
  
  I'm considering programming a simple Search Engine for a website,
  to find Arabic/Persian data within a MySQL database.
  This database contains a huge amount of data, encoded with Unicode (UTF-8). 
  
  
  The big deal is to ** reduce the response time ** to end-users.
  
  My first solution is to create an Index and use the FULL-TEXT Searching 
method.
  
  Luckily, MySQL's provides FULL-TEXT Indexing support in MyISAM tables.
  But unfortunately, it doesn't support multi-byte charsets (e.g. Unicode). [1]
  Technically, MySQL creates Indexes over words.
  A word'' is any sequence of characters consisting of letters and numbers [2].
  
  Assuming this, I tried to save the records as Unicode Character References 
(#;), but the search failed again :-(
  
  Any suggestion?
  I appreciate any solution to solve this problem.
  
  Thanks in Advance,
  Behzad
  
  
  [1] MySQL Manual - 6.8.3 Full-text Search TODO
  [2] MySQL Manual - 6.8 MySQL Full-text Search
  
  
  P.S.
  
  I use MySQL 4.0
  
  1) Table Strucutre
  
  CREATE TABLE `articles` (
`article_id` int(10) unsigned NOT NULL auto_increment,
`article_title` NATIONAL varchar(255) NOT NULL default '',
`article_text` text NOT NULL,
PRIMARY KEY  (`article_id`),
FULLTEXT (`article_title`,`article_text`)
  ) TYPE=MyISAM ;
  
  ALTER TABLE `articles` CHARACTER SET ut8;
  
  2) SQL-Query to Perform a Full-text search
  
  SELECT * FROM articles WHERE MATCH(article_title, article_text) 
AGAINST('#1587;#1608;#1575;#1604;')
  
  


-
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode

[Alec . Cawley]

AmirBehzad Eslami [EMAIL PROTECTED] wrote on 24/11/2005 17:48:29:

 Dear list,
 
   I'm considering programming a simple Search Engine for a website,
   to find Arabic/Persian data within a MySQL database.
   This database contains a huge amount of data, encoded with 
Unicode(UTF-8). 
 
 
   The big deal is to ** reduce the response time ** to end-users.
 
   My first solution is to create an Index and use the FULL-TEXT 
 Searching method.
 
   Luckily, MySQL's provides FULL-TEXT Indexing support in MyISAM tables.
   But unfortunately, it doesn't support multi-byte charsets (e.g. 
 Unicode). [1]
   Technically, MySQL creates Indexes over words.
   A word'' is any sequence of characters consisting of letters and 
 numbers [2].
 
   Assuming this, I tried to save the records as Unicode Character 
 References (#;), but the search failed again :-(
 
   Any suggestion?
   I appreciate any solution to solve this problem.
 
   Thanks in Advance,
   Behzad
 
 
   [1] MySQL Manual - 6.8.3 Full-text Search TODO
   [2] MySQL Manual - 6.8 MySQL Full-text Search
 
 
   P.S.

*** 
   I use MySQL 4.0
***

I think this is your problem: MySQL does not properly support Unicode 
until version 4.1. I am successfully using FullText with MySQL 4.1 to sort 
UTF-8 encoded Japanese text. I see no reason why it should not work for 
Arabic - if you upgrade.

Alec



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

Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode

[AmirBehzad Eslami]

On 24/11/2005, Alec worte:
  
   I think this is your problem: MySQL does not properly support Unicode 
   until version 4.1. I am successfully using FullText with MySQL 4.1 to sort 
   UTF-8 encoded Japanese text. I see no reason why it should not work for 
   Arabic - if you upgrade.
  
  Dear Alec,
  Thank you for your prompt reply.
  
  You're right. That's my problem. I admit it.
  
  But I'm really unable to solve this by upgrading.
  Many of the Hosting Companies, which I use their services [even the 
HostRocket.com] still use MySQL 4.0 !!!
  
  1) Would you recommand any hosting company with PHP 5 and MySQL 4.1 support?
  
  2) What about if my client only use MySQL 4.0 for his reasons. In this  case, 
I really can't use FULL-TEXT search? There is no any solution?
  
  Once again, thank you for your reply.
  Behzad



-
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Re: Mysql 4.0 cpu usage

[Gleb Paharenko]

Hello.



Are you able to connect to the server and found the states of its

threads with SHOW PROCESSLIST statement?





 Mysql 4.1 and 5 will NOT seem to play nice with french characters so I

have tried to

 revert back to 4.0.



 I am trying to install 4.0 on a Win2003 Dell PowerEdge 2850 and when I

run the

 service it uses a constant 25%cpu and seems locked up.  I cannot even

stop the service I

 have to end the process in the task manager. Any ideas?



James Sherwood wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: Mysql 4.0 cpu usage

[James Sherwood]

I have solved this problem, I was missing a slash in the path to the data
directory

Thanks,
James


- Original Message - 
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, October 26, 2005 4:41 AM
Subject: Re: Mysql 4.0 cpu usage


 Hello.

 Are you able to connect to the server and found the states of its
 threads with SHOW PROCESSLIST statement?


  Mysql 4.1 and 5 will NOT seem to play nice with french characters so I
 have tried to
  revert back to 4.0.
 
  I am trying to install 4.0 on a Win2003 Dell PowerEdge 2850 and when I
 run the
  service it uses a constant 25%cpu and seems locked up.  I cannot even
 stop the service I
  have to end the process in the task manager. Any ideas?

 James Sherwood wrote:


 -- 
 For technical support contracts, goto https://order.mysql.com/?ref=ensita
 This email is sponsored by Ensita.NET http://www.ensita.net/
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
 /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
___/   www.mysql.com




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





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

Mysql 4.0 cpu usage

[James Sherwood]

Hello,

Mysql 4.1 and 5 will NOT seem to play nice with french characters so I have 
tried to revert back to 4.0.

I am trying to install 4.0 on a Win2003 Dell PowerEdge 2850 and when I run the 
service it uses a constant 25%cpu and seems locked up.  I cannot even stop the 
service I have to end the process in the task manager. Any ideas?

Thanks,
James

Re: Mysql 4.0 cpu usage

[Jasper Bryant-Greene]

On Tue, 2005-10-25 at 15:39 -0300, James Sherwood wrote:
 Mysql 4.1 and 5 will NOT seem to play nice with french characters so I have 
 tried to revert back to 4.0.
 
 I am trying to install 4.0 on a Win2003 Dell PowerEdge 2850 and when I run 
 the service it uses a constant 25%cpu and seems locked up.  I cannot even 
 stop the service I have to end the process in the task manager. Any ideas?

Logs?

-- 
Jasper Bryant-Greene
General Manager
Album Limited

e: [EMAIL PROTECTED]
w: http://www.album.co.nz/
p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303
a: PO Box 579, Christchurch 8015, New Zealand


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

MySQL 4.0 examples of data with different encodings

[Bastian Balthazar Bux]

Hi all,
Willing to test an upgrading path from 4.0 to 4.1 database of MySQL.
Could someone provide a link or some slice of unload from a 4.0 with
encodings different from latin1 ?

Thanks in advance,
Francesco R.

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

Re: Weird delete behavior on mysql 4.0 - rows not disappearing....

[David Griffiths]

No, with the default transaction isolation level, REPEATABLE READ, 
that's how it is supposed to work. You've started a transaction in 
Window B, so Window B is immune to changes made in Window A until you 
finish the transaction in Window B. See the manual for details


http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;

I haven't explicitly started any transactions in Window B - it's select-only 
(with autocommit set to 0). Are you
saying that even though transactions have happend and been committed in Window 
A, I won't be able to see those
transactions in Window B?

The relevant part of the documentation in the link you sent is,

The query see[s] the changes made by exactly those transactions that committed 
before that point of time, and
no changes made by later or uncommitted transactions. The exception to this 
rule is that the query sees the
changes made by the transaction itself that issues the query.

In otherwords, if you start a query (and it's a long running query), you won't 
see the results of any data
committed by another session during the running of that query. Fine. That's 
expected.

But if I am doing only queries (no transactions) via a connection, and no query 
is running when I commit data in
another session, then the query-window should see the results of those changes.

I suspect that the mysql client is implicitly starting a transaction when you do a 
set autocommit=0. Thus, any
changes made by any other sessions won't be visible till you do a commit or 
rollback. Each time a commit or
rollback is issued in the non-auto-commit session, you can see data changed by 
other sessions.

Regardless, this is not a repeatable-read issue. I think it's a mysql client 
issue, and the fact that the client
is creating transactions for you in the background.

This is not how the Oracle client works - you are always in non-auto-commit 
mode (and I'd love to figure out
how to set that - autocommit is so dangerous), and until you actually start a 
transaction with an update, insert,
delete or select-for-update, no transaction is started, and you can see the 
changes made by other sessions once
they've been committed (I tested SQL*Plus on Oracle 8i to make sure).

David


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

Re: Weird delete behavior on mysql 4.0 - rows not disappearing....

[Michael Stassen]

David Griffiths wrote:


No, with the default transaction isolation level, REPEATABLE READ, 
that's how it is supposed to work. You've started a transaction in 
Window B, so Window B is immune to changes made in Window A until you 
finish the transaction in Window B. See the manual for details

http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;

I haven't explicitly started any transactions in Window B - it's 
select-only (with autocommit set to 0). Are you saying that even though

transactions have happend and been committed in Window A, I won't be able
to see those transactions in Window B?


The key word is explicitly.  You have implicitly started a transaction 
with your first SELECT, precisely because you turned AUTOCOMMIT off.  That 
transaction continues until you COMMIT or ROLLBACK (or perform an action 
that implicitly commits 
http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). 
 That's the point of setting AUTOCOMMIT to off.  If you only want to start 
transactions explicitly (with START TRANSACTION or BEGIN), then you need to 
leave AUTOCOMMIT on.  See the manual for details 
http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html.



The relevant part of the documentation in the link you sent is,

The query see[s] the changes made by exactly those transactions that 
committed before that point of time, and no changes made by later or

uncommitted transactions. The exception to this rule is that the query
sees the changes made by the transaction itself that issues the query. 
In otherwords, if you start a query (and it's a long running query), you 
won't see the results of any data committed by another session during the

running of that query. Fine. That's expected.

But if I am doing only queries (no transactions) via a connection, and no
query is running when I commit data in another session, then the
query-window should see the results of those changes.


From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity 
occurs inside a transaction.



I suspect that the mysql client is implicitly starting a transaction when
you do a set autocommit=0. Thus, any changes made by any other sessions
won't be visible till you do a commit or rollback. Each time a commit or 
rollback is issued in the non-auto-commit session, you can see data 
changed by other sessions.


With AUTOCOMMIT off, the transaction starts, in your case, with your first 
SELECT.


Regardless, this is not a repeatable-read issue. I think it's a mysql 
client issue, and the fact that the client is creating transactions for

you in the background.


It's not the client.  That's how InnoDB works.

This is not how the Oracle client works - you are always in 
non-auto-commit mode (and I'd love to figure out how to set that -

autocommit is so dangerous), and until you actually start a transaction
with an update, insert, delete or select-for-update, no transaction is
started, and you can see the changes made by other sessions once they've
been committed (I tested SQL*Plus on Oracle 8i to make sure). 


I'll make no comments on how Oracle works, but what you seem to be 
describing is effectively what happens with AUTOCOMMIT on in MySQL.  In 
general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for 
example) to behave in exactly the same way will usually get you in trouble.



David


Michael

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

Re: Weird delete behavior on mysql 4.0 - rows not disappearing....

[David Griffiths]

I believe you - I'm just a but surprised. I guess I had a singular view 
of how a session should work based on Oracle. I would have expected that 
until you execute SQL that requires a commit or a rollback, you wouldn't 
be in a transaction. Unfortunately, if you have connections that are 
read and write, and one connection ends up being used for SELECTs only 
(just bad luck) , it's going to have an out-date view of the database.


To me, a transaction is something you commit or rollback. You can't 
commit or rollback a SELECT unless you've done a locking-read. I guess 
Oracle is just smarter about it, only starting a transaction behind the 
scenes if you've actually done something that warrants a transaction.


David



Michael Stassen wrote:


David Griffiths wrote:



No, with the default transaction isolation level, REPEATABLE READ, 
that's how it is supposed to work. You've started a transaction in 
Window B, so Window B is immune to changes made in Window A until you 
finish the transaction in Window B. See the manual for details

http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;

I haven't explicitly started any transactions in Window B - it's 
select-only (with autocommit set to 0). Are you saying that even though
transactions have happend and been committed in Window A, I won't be 
able

to see those transactions in Window B?



The key word is explicitly.  You have implicitly started a 
transaction with your first SELECT, precisely because you turned 
AUTOCOMMIT off.  That transaction continues until you COMMIT or 
ROLLBACK (or perform an action that implicitly commits 
http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). 
 That's the point of setting AUTOCOMMIT to off.  If you only want to 
start transactions explicitly (with START TRANSACTION or BEGIN), then 
you need to leave AUTOCOMMIT on.  See the manual for details 
http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html.



The relevant part of the documentation in the link you sent is,

The query see[s] the changes made by exactly those transactions that 
committed before that point of time, and no changes made by later or

uncommitted transactions. The exception to this rule is that the query
sees the changes made by the transaction itself that issues the 
query. 
In otherwords, if you start a query (and it's a long running query), 
you won't see the results of any data committed by another session 
during the

running of that query. Fine. That's expected.

But if I am doing only queries (no transactions) via a connection, 
and no

query is running when I commit data in another session, then the
query-window should see the results of those changes.



From the AUTOCOMMIT manual page cited above, In InnoDB, all user 
activity occurs inside a transaction.


I suspect that the mysql client is implicitly starting a transaction 
when
you do a set autocommit=0. Thus, any changes made by any other 
sessions
won't be visible till you do a commit or rollback. Each time a commit 
or rollback is issued in the non-auto-commit session, you can see 
data changed by other sessions.



With AUTOCOMMIT off, the transaction starts, in your case, with your 
first SELECT.


Regardless, this is not a repeatable-read issue. I think it's a mysql 
client issue, and the fact that the client is creating transactions for

you in the background.



It's not the client.  That's how InnoDB works.

This is not how the Oracle client works - you are always in 
non-auto-commit mode (and I'd love to figure out how to set that -

autocommit is so dangerous), and until you actually start a transaction
with an update, insert, delete or select-for-update, no transaction is
started, and you can see the changes made by other sessions once they've
been committed (I tested SQL*Plus on Oracle 8i to make sure). 



I'll make no comments on how Oracle works, but what you seem to be 
describing is effectively what happens with AUTOCOMMIT on in MySQL.  
In general, I'd suggest that expecting any two RDBMSs (MySQL and 
Oracle, for example) to behave in exactly the same way will usually 
get you in trouble.



David



Michael




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

Re: Weird delete behavior on mysql 4.0 - rows not disappearing....

[SGreen]

If you are NOT in autocommit mode, your connection (or the server, it 
doesn't matter which) starts a transaction *when you issue your first 
command*. Every command you issue on that connection is in that initial 
transaction until you EXPLICITLY commit or rollback (or do something else 
that commits or rolls-back your transactions like ALTER TABLE) . At that 
point a new transaction is automatically started when you issue your next 
command.  If I remember correctly, closing a connection with a pending 
transaction defaults to a ROLLBACK. That way if a transaction is left 
incomplete due to communications failure, you maintain a consistent 
database.

 If autocommit is enabled (SET autocommit=1) then each command executes 
within it's own mini-transaction (one little, tight transaction wrapped 
around each statement). Each SELECT can see what every other INSERT, 
UPDATE, or DELETE has done (assuming their transactions are committed) 
because it is not already inside a pending transaction. This is the 
default mode for user interaction for nearly every database product I have 
used. With autocommit active, you are required to explicitly issue a START 
TRANSACTION if you want a transaction that includes several commands. 

Are you sure that's not how Oracle operates, too? I ask because MS SQL 
acts the same as MySQL when it comes to autocommits

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM:

 I believe you - I'm just a but surprised. I guess I had a singular view 
 of how a session should work based on Oracle. I would have expected that 

 until you execute SQL that requires a commit or a rollback, you wouldn't 

 be in a transaction. Unfortunately, if you have connections that are 
 read and write, and one connection ends up being used for SELECTs only 
 (just bad luck) , it's going to have an out-date view of the database.
 
 To me, a transaction is something you commit or rollback. You can't 
 commit or rollback a SELECT unless you've done a locking-read. I guess 
 Oracle is just smarter about it, only starting a transaction behind the 
 scenes if you've actually done something that warrants a transaction.
 
 David
 
 
 
 Michael Stassen wrote:
 
  David Griffiths wrote:
 
 
  No, with the default transaction isolation level, REPEATABLE READ, 
  that's how it is supposed to work. You've started a transaction in 
  Window B, so Window B is immune to changes made in Window A until you 

  finish the transaction in Window B. See the manual for details
  http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;
 
  I haven't explicitly started any transactions in Window B - it's 
  select-only (with autocommit set to 0). Are you saying that even 
though
  transactions have happend and been committed in Window A, I won't be 
  able
  to see those transactions in Window B?
 
 
  The key word is explicitly.  You have implicitly started a 
  transaction with your first SELECT, precisely because you turned 
  AUTOCOMMIT off.  That transaction continues until you COMMIT or 
  ROLLBACK (or perform an action that implicitly commits 
  http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-
 rollback.html). 
   That's the point of setting AUTOCOMMIT to off.  If you only want to 
  start transactions explicitly (with START TRANSACTION or BEGIN), then 
  you need to leave AUTOCOMMIT on.  See the manual for details 
  http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html.
 
  The relevant part of the documentation in the link you sent is,
 
  The query see[s] the changes made by exactly those transactions that 

  committed before that point of time, and no changes made by later or
  uncommitted transactions. The exception to this rule is that the 
query
  sees the changes made by the transaction itself that issues the 
  query. 
  In otherwords, if you start a query (and it's a long running query), 
  you won't see the results of any data committed by another session 
  during the
  running of that query. Fine. That's expected.
 
  But if I am doing only queries (no transactions) via a connection, 
  and no
  query is running when I commit data in another session, then the
  query-window should see the results of those changes.
 
 
  From the AUTOCOMMIT manual page cited above, In InnoDB, all user 
  activity occurs inside a transaction.
 
  I suspect that the mysql client is implicitly starting a transaction 
  when
  you do a set autocommit=0. Thus, any changes made by any other 
  sessions
  won't be visible till you do a commit or rollback. Each time a commit 

  or rollback is issued in the non-auto-commit session, you can see 
  data changed by other sessions.
 
 
  With AUTOCOMMIT off, the transaction starts, in your case, with your 
  first SELECT.
 
  Regardless, this is not a repeatable-read issue. I think it's a mysql 

  client issue, and the fact that the client is creating transactions 
for
  you 

Re: Weird delete behavior on mysql 4.0 - rows not disappearing....

[David Griffiths]

Yah, I tested in SQL*Plus - one window could see inserts, updates and 
deletes that had been committed in another window (in which a commit or 
rollback had not been issued). I ran the test again - delete data from a 
table in one window and commit the change, and a select in the other 
window displays the results.


Note that SQL*Plus by default does not auto-commit, but the key elements 
of the test are the same. Data committed in one session is visible in 
another session once committed.


In Oracle/SQL*Plus, data committed in session A will show up in Session 
B if Session B has an open transaction. Here's the example (using 
session A and B).


Session A:

insert into temp_table (col1) values ('a');

Session B:

insert into temp_table (col1) values ('b');

At this point, neither is committed, and neither session can see what's 
the other has done (the left hand doesn't know what the right is doing, 
so to speak).


Session A:

commit;

Session B:

SQL select * from temp_table;

C
-
b
a


Session B has an open transaction, yet can see the data that was 
committed in another transaction. It's view of the data is, Show me all 
the data that has been committed to the database at the point where I 
started my query, plus all changes that I've made yet not committed or 
rolled back.


Oracle runs in READ COMMITTED (the above), while INNODB runs in 
REPEATABLE READ. Big difference. And I (stupidly) assumed they ran as 
the same transaction isolation level.


Learn something new every day.

David



[EMAIL PROTECTED] wrote:



If you are NOT in autocommit mode, your connection (or the server, it 
doesn't matter which) starts a transaction *when you issue your first 
command*. Every command you issue on that connection is in that 
initial transaction until you EXPLICITLY commit or rollback (or do 
something else that commits or rolls-back your transactions like ALTER 
TABLE) . At that point a new transaction is automatically started when 
you issue your next command.  If I remember correctly, closing a 
connection with a pending transaction defaults to a ROLLBACK. That way 
if a transaction is left incomplete due to communications failure, you 
maintain a consistent database.


 If autocommit is enabled (SET autocommit=1) then each command 
executes within it's own mini-transaction (one little, tight 
transaction wrapped around each statement). Each SELECT can see what 
every other INSERT, UPDATE, or DELETE has done (assuming their 
transactions are committed) because it is not already inside a pending 
transaction. This is the default mode for user interaction for nearly 
every database product I have used. With autocommit active, you are 
required to explicitly issue a START TRANSACTION if you want a 
transaction that includes several commands.


Are you sure that's not how Oracle operates, too? I ask because MS SQL 
acts the same as MySQL when it comes to autocommits


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM:

 I believe you - I'm just a but surprised. I guess I had a singular view
 of how a session should work based on Oracle. I would have expected 
that
 until you execute SQL that requires a commit or a rollback, you 
wouldn't

 be in a transaction. Unfortunately, if you have connections that are
 read and write, and one connection ends up being used for SELECTs only
 (just bad luck) , it's going to have an out-date view of the database.

 To me, a transaction is something you commit or rollback. You can't
 commit or rollback a SELECT unless you've done a locking-read. I guess
 Oracle is just smarter about it, only starting a transaction behind the
 scenes if you've actually done something that warrants a transaction.

 David



 Michael Stassen wrote:

  David Griffiths wrote:
 
 
  No, with the default transaction isolation level, REPEATABLE READ,
  that's how it is supposed to work. You've started a transaction in
  Window B, so Window B is immune to changes made in Window A until 
you

  finish the transaction in Window B. See the manual for details
  http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;
 
  I haven't explicitly started any transactions in Window B - it's
  select-only (with autocommit set to 0). Are you saying that even 
though

  transactions have happend and been committed in Window A, I won't be
  able
  to see those transactions in Window B?
 
 
  The key word is explicitly.  You have implicitly started a
  transaction with your first SELECT, precisely because you turned
  AUTOCOMMIT off.  That transaction continues until you COMMIT or
  ROLLBACK (or perform an action that implicitly commits
  http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-
 rollback.html).
   That's the point of setting AUTOCOMMIT to off.  If you only want to
  start transactions explicitly (with START TRANSACTION or BEGIN), then
  you need to leave AUTOCOMMIT on.  See the manual for details
  

Weird delete behavior on mysql 4.0 - rows not disappearing....

[David Griffiths]

I just discovered some weird behaviour with MySQL 4.0 (4.0.24 and 
4.0.18) using InnoDB.


If you have two connections to mysql (I use the mysql client), one of 
which has autocommit turned on, an the other turned off, a row deleted 
from the client with autocommit turned on still shows up in the client 
with autocommit turned off, even after a commit.


That's complicated, so here's an example.

CREATE TABLE bug_find (col1 VARCHAR(10) NOT NULL);

Now open two windows (I'll call them Window A and Window B).

Leave Window A alone (I am assuming your client is in auto-commit mode).

In Window B, type,

SET autocommit = 0;

In Window A, type

INSERT INTO bug_find (col1) VALUES ('a');

This should be committed automatically.


In Window B, type

SELECT * from bug_find;

The column should be there.

In Window A, type,

DELETE FROM bug_find;

Again, this should be committed.

In Window B, type,

SELECT * FROM bug_find;

Whoops - still there, even though it's been removed.

In Window A, type,

commit;

In Window B, type,

SELECT * FROM bug_find;

Still there.

To make it disappear from Window B, type,

commit;

That makes no sense. The changes Window B sees (that are made by Window 
A) should not depend on issuing a commit - it has to see any data 
committed by Window A (unless it's trying to avoid dirty reads, which 
isn't the case here).


If Window B is in autocommit mode, you see the deletion right away. It 
seems to be the autocommit=0 that's screwing stuff up. I haven't tested 
this with the JDBC drivers, or with the Query Browser, or anything else. 
It may just be a MySQL client issue.


This is a big problem with data consistency. Note that this bug also 
exists for updates (any updates made in Window A are not seen by Window 
B until Window B issues a commit). Also, turning autocommit off in a 
session half way, and the same behaviour happens.


Is this a known bug?

David.


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

Re: Weird delete behavior on mysql 4.0 - rows not disappearing....

[Michael Stassen]

David Griffiths wrote:
I just discovered some weird behaviour with MySQL 4.0 (4.0.24 and 
4.0.18) using InnoDB.


If you have two connections to mysql (I use the mysql client), one of 
which has autocommit turned on, an the other turned off, a row deleted 
from the client with autocommit turned on still shows up in the client 
with autocommit turned off, even after a commit.


That's complicated, so here's an example.

CREATE TABLE bug_find (col1 VARCHAR(10) NOT NULL);

Now open two windows (I'll call them Window A and Window B).

Leave Window A alone (I am assuming your client is in auto-commit mode).

In Window B, type,

SET autocommit = 0;

In Window A, type

INSERT INTO bug_find (col1) VALUES ('a');

This should be committed automatically.


In Window B, type

SELECT * from bug_find;

The column should be there.

In Window A, type,

DELETE FROM bug_find;

Again, this should be committed.

In Window B, type,

SELECT * FROM bug_find;

Whoops - still there, even though it's been removed.

In Window A, type,

commit;

In Window B, type,

SELECT * FROM bug_find;

Still there.

To make it disappear from Window B, type,

commit;

That makes no sense. The changes Window B sees (that are made by Window 
A) should not depend on issuing a commit - it has to see any data 
committed by Window A (unless it's trying to avoid dirty reads, which 
isn't the case here).


No, with the default transaction isolation level, REPEATABLE READ, that's 
how it is supposed to work.  You've started a transaction in Window B, so 
Window B is immune to changes made in Window A until you finish the 
transaction in Window B.  See the manual for details 
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html


If Window B is in autocommit mode, you see the deletion right away. It 
seems to be the autocommit=0 that's screwing stuff up. I haven't tested 
this with the JDBC drivers, or with the Query Browser, or anything else. 
It may just be a MySQL client issue.


Each time you commit, you end the transaction and start a new one.  The new 
transaction gets a new snapshot of the data, so it sees previously committed 
changes.  With autocommit on, you are doing that automatically with every 
statement.  With autocommit off, you start a transaction with your first 
select that doesn't end till you commit.


This is a big problem with data consistency. Note that this bug also 
exists for updates (any updates made in Window A are not seen by Window 
B until Window B issues a commit). Also, turning autocommit off in a 
session half way, and the same behaviour happens.


I think you have it backwards.  REPEATABLE READ transactions assure that for 
the duration of a transaction, the only changes to your copy of the data are 
the changes you make.



Is this a known bug?


It's not a bug.


David.


Michael

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

Re: Weird delete behavior on mysql 4.0 - rows not disappearing....

[Jason Martin]

On Wed, Aug 31, 2005 at 11:18:40PM -0400, Michael Stassen wrote:
 No, with the default transaction isolation level, REPEATABLE READ, that's 
 how it is supposed to work.  You've started a transaction in Window B, so 
 Window B is immune to changes made in Window A until you finish the 
 transaction in Window B.  See the manual for details 
 http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html

 Is this a known bug?
 
 It's not a bug.
Oracle works in a similar fashion.

-Jason Martin
-- 
Never eat anything bigger than your head.
This message is PGP/MIME signed.


pgpnWgmFXjYfU.pgp
Description: PGP signature

Migration from MySQL 4.0 to 4.1

[Rafal Kedziorski]

Hi,

we have an J2EE application which ist using MySQL 4.0. There is an 
bug, which was fixed in MySQL 4.1. We are using tracactions and 
InnoDB is don't use query cache. Now we have to migrate our DB to 
MySQL 4.1 for use this feature. In our actual installation we store 
our data in one inndodb file. After migration we wan't use file per table.


What is the best and fastest way to make migration?


Best Regards,
Rafal


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

Re: Migration from MySQL 4.0 to 4.1

[Jason Pyeron]

Would I be correct in assuming you need to minimize down time on a 
productions system? Or are you just asking how to upgrade?


On Tue, 23 Aug 2005, Rafal Kedziorski wrote:


Hi,

we have an J2EE application which ist using MySQL 4.0. There is an bug, which 
was fixed in MySQL 4.1. We are using tracactions and InnoDB is don't use 
query cache. Now we have to migrate our DB to MySQL 4.1 for use this feature. 
In our actual installation we store our data in one inndodb file. After 
migration we wan't use file per table.


What is the best and fastest way to make migration?


Best Regards,
Rafal





--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner  Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.


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

Re: Migration from MySQL 4.0 to 4.1

[Pooly]

AFAIK, to move the data from one InnoDB file to one table per file,
the only option is to dump and restore.
(you may want to remove index, and foreign key checks before the
restore, and put it back afterwards)

2005/8/23, Rafal Kedziorski [EMAIL PROTECTED]:
 Hi,
 
 we have an J2EE application which ist using MySQL 4.0. There is an
 bug, which was fixed in MySQL 4.1. We are using tracactions and
 InnoDB is don't use query cache. Now we have to migrate our DB to
 MySQL 4.1 for use this feature. In our actual installation we store
 our data in one inndodb file. After migration we wan't use file per table.
 
 What is the best and fastest way to make migration?
 
 
 Best Regards,
 Rafal
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Pooly
Webzine Rock : http://www.w-fenec.org/

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

Re: Migration from MySQL 4.0 to 4.1

[Rafal Kedziorski]

At 15:30 23.08.2005, Jason Pyeron wrote:
Would I be correct in assuming you need to minimize down time on a 
productions system? Or are you just asking how to upgrade?


Correct, I wan't minimize downtime :)

Regards,
Rafal



On Tue, 23 Aug 2005, Rafal Kedziorski wrote:


Hi,

we have an J2EE application which ist using MySQL 4.0. There is an 
bug, which was fixed in MySQL 4.1. We are using tracactions and 
InnoDB is don't use query cache. Now we have to migrate our DB to 
MySQL 4.1 for use this feature. In our actual installation we store 
our data in one inndodb file. After migration we wan't use file per table.


What is the best and fastest way to make migration?


Best Regards,
Rafal




--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner  Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you is 
prohibited.


--
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: Migration from MySQL 4.0 to 4.1

[Gleb Paharenko]

Hello.



Use mysqldump, but be aware of incompatibles between 4.0 and 4.1:

  http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html



Rafal Kedziorski [EMAIL PROTECTED] wrote:

 Hi,

 

 we have an J2EE application which ist using MySQL 4.0. There is an 

 bug, which was fixed in MySQL 4.1. We are using tracactions and 

 InnoDB is don't use query cache. Now we have to migrate our DB to 

 MySQL 4.1 for use this feature. In our actual installation we store 

 our data in one inndodb file. After migration we wan't use file per table.

 

 What is the best and fastest way to make migration?

 

 

 Best Regards,

 Rafal

 

 



-- 
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: Migration from MySQL 4.0 to 4.1

[Bruce Dembecki]

Once you decide to use mysqldump, be aware that the quickest way to  
export/import large files is to use the --tab feature on export and  
mysqlimport to load the data...


Essentially:

On the old (4.0) server:

mysqldump --tab=/var/tmp/directory mydatabase

On the new (4.1) server (assuming you have a new empty mysql data  
directory with just your MyISAM based mysql database to ensure your  
permissions files are there):


mysql -e create database mydatabase;
cat /var/tmp/directory/*.sql | mysql mydatabase
mysqlimport mydatabase /var/tmp/directory/*.txt

Essentially you are creating a text .sql file for each table with the  
create table command, and a .txt file with the raw data in tab  
delimitted format... mysqlimport imports the whole data file as one  
SQL command, using traditional mysqldump you get a unique SQL insert  
command for each line of data... doing it once means only writing the  
indexes etc. once and other time saving advantages... it's far  
quicker to insert many rows of data as a single INSERT command, than  
it is to do it row by row. So if you have a large data set and you  
are doing the export/import thing, that is the way to go...


That said there is another option... in theory you can upgrade to 4.1  
keeping your shared table files, then tell each table to ALTER TABLE  
engine=innodb, this will force it to rewrite the table from scratch,  
and if you have innodb_file_per_table set, it will be created  
accordingly... The benefit here is your downtime is minimal but the  
problem is at the end of the day you are still left with your shared  
innodb table space, and even though it may be mostly empty, you can't  
clean it up and make it smaller.


Best Regards, Bruce

On Aug 23, 2005, at 6:19 AM, Rafal Kedziorski wrote:


Hi,

we have an J2EE application which ist using MySQL 4.0. There is an  
bug, which was fixed in MySQL 4.1. We are using tracactions and  
InnoDB is don't use query cache. Now we have to migrate our DB to  
MySQL 4.1 for use this feature. In our actual installation we store  
our data in one inndodb file. After migration we wan't use file per  
table.


What is the best and fastest way to make migration?


Best Regards,
Rafal


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






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

Re: Migration from MySQL 4.0 to 4.1

[Rafal Kedziorski]

hi,

should I use mysqldump from the Mysql 4.0 or 4.1?

Regards,
Rafal

p.s.
Thx for all other tips!

At 21:44 23.08.2005, Bruce Dembecki wrote:

Once you decide to use mysqldump, be aware that the quickest way to
export/import large files is to use the --tab feature on export and
mysqlimport to load the data...

Essentially:

On the old (4.0) server:

mysqldump --tab=/var/tmp/directory mydatabase

On the new (4.1) server (assuming you have a new empty mysql data
directory with just your MyISAM based mysql database to ensure your
permissions files are there):

mysql -e create database mydatabase;
cat /var/tmp/directory/*.sql | mysql mydatabase
mysqlimport mydatabase /var/tmp/directory/*.txt

Essentially you are creating a text .sql file for each table with the
create table command, and a .txt file with the raw data in tab
delimitted format... mysqlimport imports the whole data file as one
SQL command, using traditional mysqldump you get a unique SQL insert
command for each line of data... doing it once means only writing the
indexes etc. once and other time saving advantages... it's far
quicker to insert many rows of data as a single INSERT command, than
it is to do it row by row. So if you have a large data set and you
are doing the export/import thing, that is the way to go...

That said there is another option... in theory you can upgrade to 4.1
keeping your shared table files, then tell each table to ALTER TABLE
engine=innodb, this will force it to rewrite the table from scratch,
and if you have innodb_file_per_table set, it will be created
accordingly... The benefit here is your downtime is minimal but the
problem is at the end of the day you are still left with your shared
innodb table space, and even though it may be mostly empty, you can't
clean it up and make it smaller.

Best Regards, Bruce

On Aug 23, 2005, at 6:19 AM, Rafal Kedziorski wrote:


Hi,

we have an J2EE application which ist using MySQL 4.0. There is an
bug, which was fixed in MySQL 4.1. We are using tracactions and
InnoDB is don't use query cache. Now we have to migrate our DB to
MySQL 4.1 for use this feature. In our actual installation we store
our data in one inndodb file. After migration we wan't use file per
table.

What is the best and fastest way to make migration?


Best Regards,
Rafal


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




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

Re: Migration from MySQL 4.0 to 4.1

[Pooly]

Hi,

why not using the -e otion to mysqldump ? it make an INSERT command as
long as your max_command_packet permit it.


2005/8/23, Bruce Dembecki [EMAIL PROTECTED]:
 Once you decide to use mysqldump, be aware that the quickest way to
 export/import large files is to use the --tab feature on export and
 mysqlimport to load the data...
 
 Essentially:
 
 On the old (4.0) server:
 
 mysqldump --tab=/var/tmp/directory mydatabase
 
 On the new (4.1) server (assuming you have a new empty mysql data
 directory with just your MyISAM based mysql database to ensure your
 permissions files are there):
 
 mysql -e create database mydatabase;
 cat /var/tmp/directory/*.sql | mysql mydatabase
 mysqlimport mydatabase /var/tmp/directory/*.txt
 
 Essentially you are creating a text .sql file for each table with the
 create table command, and a .txt file with the raw data in tab
 delimitted format... mysqlimport imports the whole data file as one
 SQL command, using traditional mysqldump you get a unique SQL insert
 command for each line of data... doing it once means only writing the
 indexes etc. once and other time saving advantages... it's far
 quicker to insert many rows of data as a single INSERT command, than
 it is to do it row by row. So if you have a large data set and you
 are doing the export/import thing, that is the way to go...
 
 That said there is another option... in theory you can upgrade to 4.1
 keeping your shared table files, then tell each table to ALTER TABLE
 engine=innodb, this will force it to rewrite the table from scratch,
 and if you have innodb_file_per_table set, it will be created
 accordingly... The benefit here is your downtime is minimal but the
 problem is at the end of the day you are still left with your shared
 innodb table space, and even though it may be mostly empty, you can't
 clean it up and make it smaller.
 
 Best Regards, Bruce
 
 On Aug 23, 2005, at 6:19 AM, Rafal Kedziorski wrote:
 
  Hi,
 
  we have an J2EE application which ist using MySQL 4.0. There is an
  bug, which was fixed in MySQL 4.1. We are using tracactions and
  InnoDB is don't use query cache. Now we have to migrate our DB to
  MySQL 4.1 for use this feature. In our actual installation we store
  our data in one inndodb file. After migration we wan't use file per
  table.
 
  What is the best and fastest way to make migration?
 
 
  Best Regards,
  Rafal
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?
  [EMAIL PROTECTED]
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Pooly
Webzine Rock : http://www.w-fenec.org/

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

Re: UTF8 support in MySQL 4.0

[Warren Young]

Marco wrote:
So how can I do that? 


There's nothing special you need to do with MySQL itself.  Somehow your 
program obtains UTF-8 data.  Insert said data into database.  That's it.


Perhaps you should read up on UTF-8, to see why this is so.

Again, don't expect the database server to be able to do proper sorting, 
and searching will be tricky.  But it can be made to work.  Ultimately, 
it comes down to whether it's more work to work around these problems, 
or upgrade to v4.1.


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

Re: UTF8 support in MySQL 4.0

[Marco]

So how can I do that? I've already tried SET CHARACTER SET and it 
didn't work since someone said its not available in MySQL 4.0.


You can store UTF-8 in any database in the world.  UTF-8 is 
compatible with any application capable of dealing with 
null-terminated strings of 8-bit characters.  That's why it's 
possible in the Unix/C world, which was designed with 
null-terminated strings of 8-bit characters in mind.


What UTF-8 support gets you is the ability for the database server 
to do things like Unicode-aware collations and such.


But the lack of this feature doesn't prevent you from simply _storing_ UTF-8.



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

Re: UTF8 support in MySQL 4.0

[Warren Young]

Marco wrote:


So is there any solution on how I can properly use foreign characters 
and store them in a MySQL 4.0 database?


You can store UTF-8 in any database in the world.  UTF-8 is compatible 
with any application capable of dealing with null-terminated strings of 
8-bit characters.  That's why it's possible in the Unix/C world, which 
was designed with null-terminated strings of 8-bit characters in mind.


What UTF-8 support gets you is the ability for the database server to do 
things like Unicode-aware collations and such.


But the lack of this feature doesn't prevent you from simply _storing_ 
UTF-8.


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

Re: UTF8 support in MySQL 4.0

[Warren Young]

Warren Young wrote:
That's why it's possible in the Unix/C world, 


Typo: should be That's why it's _popular_...

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

UTF8 support in MySQL 4.0

[Marco]

SET CHARACTER SET ut8; won't work.

My server admin said UTF8 has not been mentioned in 4.0 thus I am 
unable to use it.


So is there any solution on how I can properly use foreign characters 
and store them in a MySQL 4.0 database?



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

Re: mysql 4.0 to 4.1 migration and charset problems

[Gleb Paharenko]

Hello.





It is not clear from you message what's the problem. From your

calculations I can see that the length of int field in a flat

file row (BTW please send a piece of your file) is different

in mysqldump programs with different versions? How is the length

of integer fields related to character set? Please provide

more information about the problem and if it is possible send a test

case. 







Vivian Wang [EMAIL PROTECTED] wrote:

 [-- text/plain, encoding 7bit, charset: us-ascii, 132 lines --]

 

 the version is 4.1.12.

 show variables like this,

 

 | character_set_client| 

 latin1  

 | character_set_connection| 

 latin1  

 | character_set_database  | 

 latin1  

 | character_set_results   | latin1 

 | character_set_server| 

 latin1  

 | character_set_system| 

 utf8

 | character_sets_dir  | 

 /usr/share/mysql/charsets/  

 | collation_connection| 

 latin1_swedish_ci   

 | collation_database  | 

 latin1_swedish_ci   

 | collation_server| latin1_swedish_ci   

 

 If the table is like test(name char(30), id1 int(4), id2 int(4))

 When I use mysqldump mysql version 3.23.??, l have  the flat file row 

 length is 30+4+4=38.

 When I  use mysqldump mysql verson 4.1.12, I have the flat file row 

 length is 30+11+11=52.

 

 I tried mysqldump --set-charset=latin2, I still got a row length=52.

 

 What I should do?

 

 

 Gleb Paharenko wrote:

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: mysql 4.0 to 4.1 migration and charset problems

[Vivian Wang]

the version is 4.1.12.
show variables like this,

| character_set_client| 
latin1  
| character_set_connection| 
latin1  
| character_set_database  | 
latin1  
| character_set_results   | latin1 
| character_set_server| 
latin1  
| character_set_system| 
utf8
| character_sets_dir  | 
/usr/share/mysql/charsets/  
| collation_connection| 
latin1_swedish_ci   
| collation_database  | 
latin1_swedish_ci   
| collation_server| latin1_swedish_ci   


If the table is like test(name char(30), id1 int(4), id2 int(4))
When I use mysqldump mysql version 3.23.??, l have  the flat file row 
length is 30+4+4=38.
When I  use mysqldump mysql verson 4.1.12, I have the flat file row 
length is 30+11+11=52.


I tried mysqldump --set-charset=latin2, I still got a row length=52.

What I should do?


Gleb Paharenko wrote:


Hello.

I've tested your solution. It doesn't work for users which have SUPER
privilege. This mentioned at:
 http://dev.mysql.com/doc/mysql/en/server-system-variables.html

However, it works with with ordinary users which don't have SUPER
privilege. Here are pieces of my my.cnf (the init_connect is one big string
without line breaks):

[client]

default_character_set=latin1

[mysqld]
default_character_set=latin2
init_connect='SET @lchar = IF(@@session.character_set_client =
_utf8latin1, @@global.character_set_client,
@@session.character_set_client); set
@@[EMAIL PROTECTED];  set
@@[EMAIL PROTECTED]; set
@@[EMAIL PROTECTED]; '


When root user connects init_connect doesn't execute and we see:
mysql show variables like '%char%';
+--+---+
| Variable_name| Value   |
+--+---+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin2   |
| character_set_results| latin1   |
| character_set_server | latin2   |
| character_set_system | utf8   |
|


When user without SUPER privilege connects we see:
 | Variable_name| Value   |

+--+---+
| character_set_client | latin2   |
| character_set_connection | latin2   |
| character_set_database   | latin2   |
| character_set_results| latin2   |
| character_set_server | latin2   |
| character_set_system | utf8   |



So it works for me.






 


 http://dev.mysql.com/doc/mysql/en/mysqldump.html
 


Dump  restore was done properly. dump on 4.0, add set names latin2; load i=
nto=20
4.1. The problem is that by default connections from client are as latin1, =
db=20
is latin2 so servers needs to do conversion from latin2-latin1 which can't=
=20
be done and thus I'm getting '?' characters instead of latin2 characters.

The thing I need is how to force default latin2 in all client connections e=
ven=20
if client won't request latin2 by using set names.

Tried doing things like in mysqld.conf:

init-connect =3D SET @lchar =3D IF(@@session.character_set_client =3D _utf8=
latin1,=20
@@global.character_set_client, @@session.character_set_client); SET=20
character_set_client =3D @lchar; SET character_set_results =3D @lchar; SET=
=20
character_set_connection =3D @lchar;

but that doesn't work unfortunately from init-connect (works from mysql=20
cmdline client) ;-(
=2D-=20
Arkadiusz Mi=B6kiewiczPLD/Linux Team
http://www.t17.ds.pwr.wroc.pl/~misiek/  http://ftp.pld-linux.org/

   




 

Re: mysql 4.0 to 4.1 migration and charset problems

[Gleb Paharenko]

Hello.





 Any possible problems with this approach?



It is good, if it solved your problems.







 So it works for me.

 

 I've ended doing this in a different way. I've created a patch which forces=

 a=20

 file to be read - /etc/mysql/mysql-client.conf (which is the same as .my.cn=

 f)=20

 at mysql_init() time. All clients that use libmysqlclient.so should read it=

 =20

 now.

 

 http://cvs.pld-linux.org/cgi-bin/cvsweb/SOURCES/mysql-client-config.patch?r=

 ev=3D1.1

 

 Now I can put defaults in that global config:

 

 [EMAIL PROTECTED] ~]$ more /etc/mysql/mysql-client.conf

 [client]

 default-character-set=3Dlatin2

 

 Now all my clients connect with latin2 as default.

 

 Any possible problems with this approach?

 

 =2D-=20

 Arkadiusz Mi=B6kiewiczPLD/Linux Team

 http://www.t17.ds.pwr.wroc.pl/~misiek/  http://ftp.pld-linux.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: mysql 4.0 to 4.1 migration and charset problems

[Arkadiusz Miskiewicz]

On Thursday 07 of July 2005 00:16, Gleb Paharenko wrote:
 Hello.


 For a pity, I could give explanations only for your query about
 selecting @@global.xxx variables. I think server returns correct
 results, because you're selecting global variables, while
 character_set_client, character_set_connection, character_set_results
 are session  variables. And with SET NAMES you're setting
 @@character_xxx variables which are synonym for @@session.character_xxx.
I see.

 In what way have you done your upgrade? If you haven't used mysqldump
 you could get some problems. Make the dump, and restore it setting
 the correct connection variables for mysql program. Be aware of that
 mysqldump could put SET NAMES at the beginning of the dump file. Use
 set-names=latin2 for it. See:
   http://dev.mysql.com/doc/mysql/en/mysqldump.html
Dump  restore was done properly. dump on 4.0, add set names latin2; load into 
4.1. The problem is that by default connections from client are as latin1, db 
is latin2 so servers needs to do conversion from latin2-latin1 which can't 
be done and thus I'm getting '?' characters instead of latin2 characters.

The thing I need is how to force default latin2 in all client connections even 
if client won't request latin2 by using set names.

Tried doing things like in mysqld.conf:

init-connect = SET @lchar = IF(@@session.character_set_client = _utf8latin1, 
@@global.character_set_client, @@session.character_set_client); SET 
character_set_client = @lchar; SET character_set_results = @lchar; SET 
character_set_connection = @lchar;

but that doesn't work unfortunately from init-connect (works from mysql 
cmdline client) ;-(
-- 
Arkadiusz MiśkiewiczPLD/Linux Team
http://www.t17.ds.pwr.wroc.pl/~misiek/  http://ftp.pld-linux.org/

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

Re: mysql 4.0 to 4.1 migration and charset problems

[Gleb Paharenko]

Hello.



I've tested your solution. It doesn't work for users which have SUPER

privilege. This mentioned at:

  http://dev.mysql.com/doc/mysql/en/server-system-variables.html



However, it works with with ordinary users which don't have SUPER

privilege. Here are pieces of my my.cnf (the init_connect is one big string

without line breaks):



[client]



default_character_set=latin1



[mysqld]

default_character_set=latin2

init_connect='SET @lchar = IF(@@session.character_set_client =

_utf8latin1, @@global.character_set_client,

@@session.character_set_client); set

@@[EMAIL PROTECTED];  set

@@[EMAIL PROTECTED]; set

@@[EMAIL PROTECTED]; '





When root user connects init_connect doesn't execute and we see:

 mysql show variables like '%char%';

 
+--+---+

 | Variable_name| Value   |

 
+--+---+

 | character_set_client | latin1   |

 | character_set_connection | latin1   |

 | character_set_database   | latin2   |

 | character_set_results| latin1   |

 | character_set_server | latin2   |

 | character_set_system | utf8   |

 |





When user without SUPER privilege connects we see:

  | Variable_name| Value   |


+--+---+

| character_set_client | latin2   |

| character_set_connection | latin2   |

| character_set_database   | latin2   |

| character_set_results| latin2   |

| character_set_server | latin2   |

| character_set_system | utf8   |







So it works for me.













   http://dev.mysql.com/doc/mysql/en/mysqldump.html

 Dump  restore was done properly. dump on 4.0, add set names latin2; load i=

 nto=20

 4.1. The problem is that by default connections from client are as latin1, =

 db=20

 is latin2 so servers needs to do conversion from latin2-latin1 which can't=

 =20

 be done and thus I'm getting '?' characters instead of latin2 characters.

 

 The thing I need is how to force default latin2 in all client connections e=

 ven=20

 if client won't request latin2 by using set names.

 

 Tried doing things like in mysqld.conf:

 

 init-connect =3D SET @lchar =3D IF(@@session.character_set_client =3D _utf8=

 latin1,=20

 @@global.character_set_client, @@session.character_set_client); SET=20

 character_set_client =3D @lchar; SET character_set_results =3D @lchar; SET=

 =20

 character_set_connection =3D @lchar;

 

 but that doesn't work unfortunately from init-connect (works from mysql=20

 cmdline client) ;-(

 =2D-=20

 Arkadiusz Mi=B6kiewiczPLD/Linux Team

 http://www.t17.ds.pwr.wroc.pl/~misiek/  http://ftp.pld-linux.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: mysql 4.0 to 4.1 migration and charset problems

[Arkadiusz Miskiewicz]

On Monday 11 of July 2005 16:24, Gleb Paharenko wrote:
 Hello.

 I've tested your solution. It doesn't work for users which have SUPER
 privilege. This mentioned at:
   http://dev.mysql.com/doc/mysql/en/server-system-variables.html

 However, it works with with ordinary users which don't have SUPER
 privilege. Here are pieces of my my.cnf (the init_connect is one big string
 without line breaks):

 [client]

 default_character_set=latin1

 [mysqld]
 default_character_set=latin2
 init_connect='SET @lchar = IF(@@session.character_set_client =
 _utf8latin1, @@global.character_set_client,
 @@session.character_set_client); set
 @@[EMAIL PROTECTED];  set
 @@[EMAIL PROTECTED]; set
 @@[EMAIL PROTECTED]; '
[...]
 So it works for me.

I've ended doing this in a different way. I've created a patch which forces a 
file to be read - /etc/mysql/mysql-client.conf (which is the same as .my.cnf) 
at mysql_init() time. All clients that use libmysqlclient.so should read it 
now.

http://cvs.pld-linux.org/cgi-bin/cvsweb/SOURCES/mysql-client-config.patch?rev=1.1

Now I can put defaults in that global config:

[EMAIL PROTECTED] ~]$ more /etc/mysql/mysql-client.conf
[client]
default-character-set=latin2

Now all my clients connect with latin2 as default.

Any possible problems with this approach?

-- 
Arkadiusz MiśkiewiczPLD/Linux Team
http://www.t17.ds.pwr.wroc.pl/~misiek/  http://ftp.pld-linux.org/

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

mysql 4.0 to 4.1 migration and charset problems

[Arkadiusz Miskiewicz]

Hi,

I have mysql 4.0  db with configuration:
set-variable = default-character-set=latin2
set-variable = character-set=latin2

now I'm trying to migrate to mysql 4.1.12 My current config is:
character-set-server=latin2
collation-server=latin2_general_ci

The problem is connection/reply/client-character set. mysql 4.1 by default 
uses 'latin1' so in all queries (from previously latin2 mysql 4.0 database) I 
don't have latin2 characters just '?' chars. That's obvious because due to 
default latin1 in 4.1.

SET NAMES 'latin2' from client side of coruse cures whole problem but... I 
can't fix every possible application that my users have installed (and if 
there are thousands of users this is simply impossible).

What's the proper way of dealing with this problem? I've tried to use some 
nasty hacks like init-connect=SET NAMES latin2 and this half-works - I see 
latin2 characters in server responses... but it breaks for example phpmyadmin 
(characters are broken in results; when I drop init-connect hack phpmyadmin 
works nicely). So init-connect isn't usable.

Now when connecting with mysql command line client then I by default get 
latin1 but can change easily to latin2 using 
[mysql]
default-character-set=latin2
in .my.cnf file. That's great but this works only for mysql cmd line client 
_only_ while my primary concern is php.

Now is the funny part, there is no .my.cnf file, I connect with mysql cmdline.
mysql \s
--
mysql  Ver 14.7 Distrib 4.1.12, for pld-linux-gnu (powerpc) using readline 4.3

Connection id:  2
Current database:
Current user:   [EMAIL PROTECTED]
SSL:Not in use
Current pager:  stdout
Using outfile:  ''
Using delimiter:;
Server version: 4.1.12-log
Protocol version:   10
Connection: Localhost via UNIX socket
Server characterset:latin2
Db characterset:latin2
Client characterset:latin1
Conn.  characterset:latin1
UNIX socket:/var/lib/mysql/mysql.sock
Uptime: 34 min 35 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 11  Flush tables: 1  Open 
tables: 0  Queries per second avg: 0.006
--

mysql show variables like '%char%';
+--++
| Variable_name| Value  |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin2 |
| character_set_results| latin1 |
| character_set_server | latin2 |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)

As you can see client/connection/results are latin1 BUT:

mysql select @@global.character_set_client, 
@@global.character_set_connection, @@global.character_set_database, 
@@global.character_set_results, @@global.character_set_results, 
@@global.character_set_server;
+---+---+-+++---+
| @@global.character_set_client | @@global.character_set_connection | 
@@global.character_set_database | @@global.character_set_results | 
@@global.character_set_results | @@global.character_set_server |
+---+---+-+++---+
| latin2| latin2| latin2
  
| latin2 | latin2 | latin2  
  
|
+---+---+-+++---+
1 row in set (0.00 sec)

now it tells me that these are latin2!?

Anyway primary question is how to deal with characters in mysql 4.0-4.1 
conversion (how to get latin2 as default in client connections)? I've talked 
with few people on #mysql on freenode but we haven't seen any solution.

-- 
Arkadiusz MiśkiewiczPLD/Linux Team
http://www.t17.ds.pwr.wroc.pl/~misiek/  http://ftp.pld-linux.org/

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

Re: mysql 4.0 to 4.1 migration and charset problems

[Gleb Paharenko]

Hello.





For a pity, I could give explanations only for your query about

selecting @@global.xxx variables. I think server returns correct

results, because you're selecting global variables, while 

character_set_client, character_set_connection, character_set_results

are session  variables. And with SET NAMES you're setting

@@character_xxx variables which are synonym for @@session.character_xxx.

In what way have you done your upgrade? If you haven't used mysqldump

you could get some problems. Make the dump, and restore it setting

the correct connection variables for mysql program. Be aware of that

mysqldump could put SET NAMES at the beginning of the dump file. Use

set-names=latin2 for it. See:

  http://dev.mysql.com/doc/mysql/en/mysqldump.html









Arkadiusz Miskiewicz [EMAIL PROTECTED] wrote:

 Hi,

 

 I have mysql 4.0  db with configuration:

 set-variable =3D default-character-set=3Dlatin2

 set-variable =3D character-set=3Dlatin2

 

 now I'm trying to migrate to mysql 4.1.12 My current config is:

 character-set-server=3Dlatin2

 collation-server=3Dlatin2_general_ci

 

 The problem is connection/reply/client-character set. mysql 4.1 by default=

 =20

 uses 'latin1' so in all queries (from previously latin2 mysql 4.0 database)=

 I=20

 don't have latin2 characters just '?' chars. That's obvious because due to=

 =20

 default latin1 in 4.1.

 

 SET NAMES 'latin2' from client side of coruse cures whole problem but... I=

 =20

 can't fix every possible application that my users have installed (and if=20

 there are thousands of users this is simply impossible).

 

 What's the proper way of dealing with this problem? I've tried to use some=

 =20

 nasty hacks like init-connect=3DSET NAMES latin2 and this half-works - I =

 see=20

 latin2 characters in server responses... but it breaks for example phpmyadm=

 in=20

 (characters are broken in results; when I drop init-connect hack phpmyadmin=

 =20

 works nicely). So init-connect isn't usable.

 

 Now when connecting with mysql command line client then I by default get=20

 latin1 but can change easily to latin2 using=20

 [mysql]

 default-character-set=3Dlatin2

 in .my.cnf file. That's great but this works only for mysql cmd line client=

 =20

 _only_ while my primary concern is php.

 

 Now is the funny part, there is no .my.cnf file, I connect with mysql cmdli=

 ne.

 mysql \s

 =2D-

 mysql  Ver 14.7 Distrib 4.1.12, for pld-linux-gnu (powerpc) using readline =

 4.3

 

 Connection id:  2

 Current database:

 Current user:   [EMAIL PROTECTED]

 SSL:Not in use

 Current pager:  stdout

 Using outfile:  ''

 Using delimiter:;

 Server version: 4.1.12-log

 Protocol version:   10

 Connection: Localhost via UNIX socket

 Server characterset:latin2

 Db characterset:latin2

 Client characterset:latin1

 Conn.  characterset:latin1

 UNIX socket:/var/lib/mysql/mysql.sock

 Uptime: 34 min 35 sec

 

 Threads: 1  Questions: 12  Slow queries: 0  Opens: 11  Flush tables: 1  Ope=

 n=20

 tables: 0  Queries per second avg: 0.006

 =2D-

 

 mysql show variables like '%char%';

 +--++

 | Variable_name| Value  |

 +--++

 | character_set_client | latin1 |

 | character_set_connection | latin1 |

 | character_set_database   | latin2 |

 | character_set_results| latin1 |

 | character_set_server | latin2 |

 | character_set_system | utf8   |

 | character_sets_dir   | /usr/share/mysql/charsets/ |

 +--++

 7 rows in set (0.00 sec)

 

 As you can see client/connection/results are latin1 BUT:

 

 mysql select @@global.character_set_client,=20

 @@global.character_set_connection, @@global.character_set_database,=20

 @@global.character_set_results, @@global.character_set_results,=20

 @@global.character_set_server;

 +---+---+--=

 =2D--++=

 =2D---+---+

 | @@global.character_set_client | @@global.character_set_connection |=20

 @@global.character_set_database | @@global.character_set_results |=20

 @@global.character_set_results | @@global.character_set_server |

 +---+---+--=

 =2D--++=

 =2D---+---+

 | latin2| latin2| latin=

 2 =20

Find records that don't exist in MySQl 4.0

[Andrew Hargreaves]

 In MySQL 4.0, is it possible to find all records that don't exist in one
query. I managed to do it in Access by referencing a query that I had
previously created, but I was wondering if it was possible to do this in one
step.

SELECT C.CustomerID, ([JobStatus]=0 And [JobTypeID]=2) AS KountNA,
Count([KountNA]) AS KountComplete
FROM Workorders AS W INNER JOIN Customers AS C ON W.CustomerID =
C.CustomerID
GROUP BY C.CustomerID, ([JobStatus]=0 And [JobTypeID]=2)
HAVING [JobStatus]=0 And [JobTypeID]=2))=-1));

Would show all Customers that have JobTypeID=2 and JobStatus=0. I realise
that the KountComplete expression is not required.

I then used a RIGHT JOIN to filter out all the records from the Customers
table not in this query, as below:

SELECT C.CustomerID, QC.KountComplete, C.NameNumb, C.BillingAddress, C.City,
C.PostalCode
FROM qryCountCompleteServices AS QC RIGHT JOIN Customers AS C ON
QC.CustomerID = C.CustomerID
WHERE (QC.KountNA) Is Null);

So, can I do that without using a sub-query in MySQL 4.0? 

Thanks

Andrew 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005
 

Re: Find records that don't exist in MySQl 4.0

[Daniel Kasak]

Andrew Hargreaves wrote:

 In MySQL 4.0, is it possible to find all records that don't exist in one
query. I managed to do it in Access by referencing a query that I had
previously created, but I was wondering if it was possible to do this in one
step.
  

Not in one step, no. If you're still using Access you can of course set
up a query chain as in your example. But if you're using 'pure'
MySQL-4.0.x, you'll have to make a temporary table from the 1st query,
and then run your second query against this temp table.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

Collation question: importing DB MySQL 4.0 4.1

[Kaupolikan]

Hi,

I upgraded MySQL 4.0 to 4.1 and imported all data using phpmyadmin.

All accented letters (italian, french, spanish) have been replaced by asian
characters...
and now the default collation for tables and varchar fields is: 
latin1_swedish_ci

I'm only using european languages in the DB, so which collation should I use ?

TIA

K.




Navighi a 2 MEGA e i primi 3 mesi sono GRATIS. 
Scegli Libero Adsl Flat senza limiti su http://www.libero.it



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

Re: Converting Text columns from mysql 4.0 to 4.1

[Gleb Paharenko]

Hello.



Please send us an output of the following statement:

 SHOW VARIABLES LIKE '%char%';



See:

  http://dev.mysql.com/doc/mysql/en/charset-conversion.html







Bruce Dembecki [EMAIL PROTECTED] wrote:

 Hi! We have a problem converting our 4.0 text columns from a Hong Kong

 database to 4.1. In order to get the conversions to work generally speaking

 we build our databases with default character set utf8 - it means the German

 products still work, and the English ones, and the Chinese ones, and the

 

 Anyway, we ran into a problem on the Hong Kong platform where the text

 column imports as a single space to 4.1... If I look at the data in 4.0 I

 see actual text (I suppose, it's mostly jibberish on my screen), while in

 4.1 all I have after the import is a single space character.

 

 If I change the column type to blob (from text) I can get the data imported

 without problem, except that the data is now in a blob column. If I try to

 alter the table to a text column, I am left with the single spaces again.

 

 Looking at the data that does get affected (not all records suffer this

 fate, just some) it appears that they have multiple languages, for example

 Chinese or more often Japanese, together with something like an email

 address which is written in latin type characters. I can post a new entry

 through the webapp with mixed languages, it's just the export/import that

 seems to be be letting us down - or converting the blob to a text in 4.1

 after the fact.

 

 I even tried building a duplicate table format and doing an INSERT SELECT

 where the source is a blob and the target is a text, and that also fails.

 

 Clearly I can't convert the rest of my databases if there is a chance that

 our message bodies will be munged With about 100 databases each with 60

 tables it's not even going to be easy to try and script it in such a way

 that I could do a dump and an import with something changing the table type

 in the .sql file from text to blob, let alone the time it will take us to

 first test the Application and web servers to see if making the change to a

 blob column will affect us in any way.

 

 Do I need to be doing all this work... Is there something I have done

 incorrectly? Is this a bug that someone is fixing and will go away next

 version?

 

 I can provide the dump files if someone wants to test... Let me know.

 

 Best Regards, Bruce

 

 



-- 
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]

Converting Text columns from mysql 4.0 to 4.1

[Bruce Dembecki]

Hi! We have a problem converting our 4.0 text columns from a Hong Kong
database to 4.1. In order to get the conversions to work generally speaking
we build our databases with default character set utf8 - it means the German
products still work, and the English ones, and the Chinese ones, and the

Anyway, we ran into a problem on the Hong Kong platform where the text
column imports as a single space to 4.1... If I look at the data in 4.0 I
see actual text (I suppose, it's mostly jibberish on my screen), while in
4.1 all I have after the import is a single space character.

If I change the column type to blob (from text) I can get the data imported
without problem, except that the data is now in a blob column. If I try to
alter the table to a text column, I am left with the single spaces again.

Looking at the data that does get affected (not all records suffer this
fate, just some) it appears that they have multiple languages, for example
Chinese or more often Japanese, together with something like an email
address which is written in latin type characters. I can post a new entry
through the webapp with mixed languages, it's just the export/import that
seems to be be letting us down - or converting the blob to a text in 4.1
after the fact.

I even tried building a duplicate table format and doing an INSERT SELECT
where the source is a blob and the target is a text, and that also fails.

Clearly I can't convert the rest of my databases if there is a chance that
our message bodies will be munged With about 100 databases each with 60
tables it's not even going to be easy to try and script it in such a way
that I could do a dump and an import with something changing the table type
in the .sql file from text to blob, let alone the time it will take us to
first test the Application and web servers to see if making the change to a
blob column will affect us in any way.

Do I need to be doing all this work... Is there something I have done
incorrectly? Is this a bug that someone is fixing and will go away next
version?

I can provide the dump files if someone wants to test... Let me know.

Best Regards, Bruce


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

MySQL 4.0 and concat

[Alfredo Cole]

Hi:

I have a problem in that all statements that include concat execute very 
slowly. For instance, if I have three fields in string format  that represent 
a year, month and day, and want to issue a select like:

select * from cxcmanpag where contact 
(year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will 
take a long time, againts a table with only around 100,00 records. If I 
rewrite the statement to read:

select * from cxcmanpag where year=stringYear and month=stringMonth and 
day=stringDay, it will execute considerable faster, but will not produce the 
same results.

I have looked in the manual, and also read High Performance MySQL from Zawodny 
and Balling, and MySQL from Paul Dubois, but none of them seem to address 
this issue.

Can somebody point me to a URL or book that I should be reading to improve, 
this, or how to avoid using concat altogether?

Thank you.

-- 
Alfredo J. Cole
Grupo ACyC
www.acyc.com - www.clshonduras.com - SolCom

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

Re: MySQL 4.0 and concat

[SGreen]

Have you considered NOT comparing dates as strings but rather as date 
values?  That will avoid the use of CONCAT() completely.


SELECT * 
FROM sampletable
WHERE datefield = '1999-01-12' and datefield '1999-02-01'

This example query will get all of the records from sampletable that were 
entered after January 11th and before February 1st. It will also be 
**very** fast if the column datefield is indexed.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Alfredo Cole [EMAIL PROTECTED] wrote on 10/11/2004 09:43:14 AM:

 Hi:
 
 I have a problem in that all statements that include concat execute very 

 slowly. For instance, if I have three fields in string format  that 
represent 
 a year, month and day, and want to issue a select like:
 
 select * from cxcmanpag where contact 
 (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it 
will 
 take a long time, againts a table with only around 100,00 records. If I 
 rewrite the statement to read:
 
 select * from cxcmanpag where year=stringYear and month=stringMonth 
and 
 day=stringDay, it will execute considerable faster, but will not 
produce the 
 same results.
 
 I have looked in the manual, and also read High Performance MySQL 
 from Zawodny 
 and Balling, and MySQL from Paul Dubois, but none of them seem to 
address 
 this issue.
 
 Can somebody point me to a URL or book that I should be reading to 
improve, 
 this, or how to avoid using concat altogether?
 
 Thank you.
 
 -- 
 Alfredo J. Cole
 Grupo ACyC
 www.acyc.com - www.clshonduras.com - SolCom
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Fwd: Re: MySQL 4.0 and concat

[Alfredo Cole]

Sorry. This should have gone back to the list.

--  Mensaje reenviado  --

Subject: Re: MySQL 4.0 and concat
Date: Lun 11 Oct 2004 11:37
From: Alfredo Cole [EMAIL PROTECTED]
To: [EMAIL PROTECTED]

El Lun 11 Oct 2004 08:35, escribió:
 Have you considered NOT comparing dates as strings but rather as date
 values?  That will avoid the use of CONCAT() completely.

I will try this. But there will always be times when using concat might be
required. It would be nice to know if there is a solution to the concat
problem.

Thank you, and regards.

--
Alfredo J. Cole
Grupo ACyC
www.acyc.com - www.clshonduras.com - SolCom

---

-- 
Alfredo J. Cole
Grupo ACyC
www.acyc.com - www.clshonduras.com - SolCom

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

Re: MySQL 4.0 and concat

[William R. Mussatto]

A bit of a warning, if the fields are DATETIME rather than DATE, add the
appropriate hours:minutes:seconds to the test
WHERE datetimefield '1999-02-01 23:59:59'. When comparing DATETIME fields
with date strings '00:00:00' is assumed and that can cause problems if one
forgets that.
 Have you considered NOT comparing dates as strings but rather as date
 values?  That will avoid the use of CONCAT() completely.


 SELECT *
 FROM sampletable
 WHERE datefield = '1999-01-12' and datefield '1999-02-01'

 This example query will get all of the records from sampletable that
 were  entered after January 11th and before February 1st. It will also
 be  **very** fast if the column datefield is indexed.

 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine

 Alfredo Cole [EMAIL PROTECTED] wrote on 10/11/2004 09:43:14 AM:

 Hi:

 I have a problem in that all statements that include concat execute
 very

 slowly. For instance, if I have three fields in string format  that
 represent
 a year, month and day, and want to issue a select like:

 select * from cxcmanpag where contact
 (year,month,day)=stringYear+stringMonth+stringDay (simplified), then
 it
 will
 take a long time, againts a table with only around 100,00 records. If
 I  rewrite the statement to read:

 select * from cxcmanpag where year=stringYear and month=stringMonth
 and
 day=stringDay, it will execute considerable faster, but will not
 produce the
 same results.

 I have looked in the manual, and also read High Performance MySQL
 from Zawodny
 and Balling, and MySQL from Paul Dubois, but none of them seem to
 address
 this issue.

 Can somebody point me to a URL or book that I should be reading to
 improve,
 this, or how to avoid using concat altogether?

 Thank you.

 --
 Alfredo J. Cole
 Grupo ACyC
 www.acyc.com - www.clshonduras.com - SolCom


-- 
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



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

RE: views/cursors in mysql 4.0

[SGreen]

Yes. Best of luck with your conversion!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[EMAIL PROTECTED] wrote on 09/30/2004 05:25:22 PM:

 
 Hi, 
 
 Thank you very much for your reply. So for the cursors result 
 set C API's will be suitable ..right?.
 
 thanks,
 
 Narasimha
 
 
 
 
 
 
 
 
 
 
 Confidentiality Notice 
 
 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 
 confidential or privileged information. If
 you are not the intended recipient, please notify the sender at 
 Wipro or [EMAIL PROTECTED] immediately
 and destroy all copies of this message and any attachments.

views/cursors in mysql 4.0

[lakshmi.narasimharao]

Hi,
   How to implement views/cursors in mysql 4.0?. Normally in mysql how the 
selected data is maintained in the recordsets?. Is there any other alternatives to 
implement views/cursors in mysql 4.0?. Please advise me for the better solution.
 
Thanks,
Narasimha



Confidentiality Notice 

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 confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

Re: views/cursors in mysql 4.0

[SGreen]

Views and Cursors are not available in MySQL 4.x.  Usually your scripting 
language handles cursors for you (having them in 4.x would do you little 
good as stored procedures don't exist yet. Look at the current 5.x 
development for cursors and stored procedures). What language do you 
script/program with?

What would you like to have done with your views and/or cursors if they 
_were_ available? Workarounds exist for almost every purpose but some of 
them use language-specific or library-specific options.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[EMAIL PROTECTED] wrote on 09/30/2004 03:01:51 PM:

 
 Hi,
How to implement views/cursors in mysql 4.0?. Normally in 
 mysql how the selected data is maintained in the recordsets?. Is 
 there any other alternatives to implement views/cursors in mysql 4.
 0?. Please advise me for the better solution.
 
 Thanks,
 Narasimha
 
 
 
 Confidentiality Notice 
 
 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 
 confidential or privileged information. If
 you are not the intended recipient, please notify the sender at 
 Wipro or [EMAIL PROTECTED] immediately
 and destroy all copies of this message and any attachments.

RE: views/cursors in mysql 4.0

[lakshmi.narasimharao]

Hi,
   Thank you for your response. Strictly we have to use mysql 4.0 only. Actually 
the source data base is oracle 7.3, we need to migrate that database to mysql 4.0 . In 
oracle 7.3 views/cursors are used. That is why i need an alternative for views/cursors 
in mysql 4.0. We use C/C++ for Programming.  could you please explain me the 
alternatives for views/cursors in mysql 4.0 and how to implement those in mysql 4.0?.
 
Thanks,
Narasimha

-Original Message- 
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Fri 10/1/2004 12:40 AM 
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) 
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: Re: views/cursors in mysql 4.0



Views and Cursors are not available in MySQL 4.x.  Usually your scripting 
language handles cursors for you (having them in 4.x would do you little good as 
stored procedures don't exist yet. Look at the current 5.x development for cursors and 
stored procedures). What language do you script/program with? 

What would you like to have done with your views and/or cursors if they _were_ 
available? Workarounds exist for almost every purpose but some of them use 
language-specific or library-specific options. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

[EMAIL PROTECTED] wrote on 09/30/2004 03:01:51 PM:

 
 Hi,
How to implement views/cursors in mysql 4.0?. Normally in 
 mysql how the selected data is maintained in the recordsets?. Is 
 there any other alternatives to implement views/cursors in mysql 4.
 0?. Please advise me for the better solution.
  
 Thanks,
 Narasimha
 
 
 
 Confidentiality Notice 
 
 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 
 confidential or privileged information. If
 you are not the intended recipient, please notify the sender at 
 Wipro or [EMAIL PROTECTED] immediately
 and destroy all copies of this message and any attachments.





Confidentiality Notice 

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 confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

RE: views/cursors in mysql 4.0

[SGreen]

Views - instead of running queries against views, you will have to run 
your queries against the underlying tables. This may mean lots of changes 
to your SQL statements.  What once appeared as a single query may now have 
to be performed in multiple steps to achieve the same results.  Usually, 
this kind of redesign actually improves application performance as you no 
longer rely on a set of table abstractions which require additional 
overhead to maintain.

Cursors - Any SQL-scripted processing you did using cursors will have to 
be replaced with C/C++ routines that perform the same functions. You 
navigate recordsets according to the methods exposed by whichever library 
you use to interact with the server. Consult the documentation for the 
library you will use for specific details.

Sorry I couldn't be more exact in my descriptions but you were not very 
clear on your requirements. Please respond with more detailed descriptions 
of exactly what you need from the database and I am sure someone on the 
list can help.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


[EMAIL PROTECTED] wrote on 09/30/2004 03:24:52 PM:

 Hi,
Thank you for your response. Strictly we have to use mysql 4.
 0 only. Actually the source data base is oracle 7.3, we need to 
 migrate that database to mysql 4.0 . In oracle 7.3 views/cursors are
 used. That is why i need an alternative for views/cursors in mysql 
 4.0. We use C/C++ for Programming.  could you please explain me the 
 alternatives for views/cursors in mysql 4.0 and how to implement 
 those in mysql 4.0?.
 
 Thanks,
 Narasimha
 -Original Message- 
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
 Sent: Fri 10/1/2004 12:40 AM 
 To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) 
 Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
 Subject: Re: views/cursors in mysql 4.0

 
 Views and Cursors are not available in MySQL 4.x.  Usually your 
 scripting language handles cursors for you (having them in 4.x would
 do you little good as stored procedures don't exist yet. Look at the
 current 5.x development for cursors and stored procedures). What 
 language do you script/program with? 
 
 What would you like to have done with your views and/or cursors if 
 they _were_ available? Workarounds exist for almost every purpose 
 but some of them use language-specific or library-specific options. 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine 
 
 [EMAIL PROTECTED] wrote on 09/30/2004 03:01:51 PM:
 
  
  Hi,
 How to implement views/cursors in mysql 4.0?. Normally in 
  mysql how the selected data is maintained in the recordsets?. Is 
  there any other alternatives to implement views/cursors in mysql 4.
  0?. Please advise me for the better solution.
  
  Thanks,
  Narasimha
  
  
  
  Confidentiality Notice 
  
  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 
  confidential or privileged information. If
  you are not the intended recipient, please notify the sender at 
  Wipro or [EMAIL PROTECTED] immediately
  and destroy all copies of this message and any attachments.
 
 
 
 Confidentiality Notice 
 
 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 
 confidential or privileged information. If
 you are not the intended recipient, please notify the sender at 
 Wipro or [EMAIL PROTECTED] immediately
 and destroy all copies of this message and any attachments.

RE: views/cursors in mysql 4.0

[lakshmi.narasimharao]

Hi,
Thank you very much for your reply. 
 
   The existed system , a network management tool , is developed using C as 
program lanuage and oracle 7.3 as the database. There are 3 layers in the database 
like table layer, view layer and DBview layer. The DBView layer communicates through 
API's with both table and view layers. Views and tables are created dynamically 
through API's. Here Views are created for each table (reason i too donot know). And 
used some stored procedures at module level and some triggers on some table 
before/after updating. 
The above existed system's whole database layer including table layer/views layer and 
DBView layer to be migrated to mysql 4.0 with out innodb. Need to change the API's 
also. So, the views/stored procedurs (some of them used cursors)/ triggers  need to be 
converted to mysql 4.0 from oracle 7.3.  That is the requrement.
 
Views - instead of running queries against views, you will have to run your queries 
against the underlying tables. This may mean lots of changes to your SQL statements.  
What once appeared as a single query may now have to be performed in multiple steps to 
achieve the same results.  Usually, this kind of redesign actually improves 
application performance as you no longer rely on a set of table abstractions which 
require additional overhead to maintain. 

--- If i want to use the same data by querying underlying tables again how to use 
that?. where to store that data for using again?. Shall i need to write the same query 
again when i need the same data?. In the existed system views are created only once at 
runtime. could you please explain me in detail.
 
Cursors - Any SQL-scripted processing you did using cursors will have to be replaced 
with C/C++ routines that perform the same functions. You navigate recordsets according 
to the methods exposed by whichever library you use to interact with the server. 
Consult the documentation for the library you will use for specific details. 

--- Supggest me the best API's to perform SQL- scripted performance?. Could you please 
mention what are all the libraries we needed for the general  cursor processings. 
Given the oracle code which is used the cursors, could you please mention the 
equivalent libraries for those
 
   CREATE procedure pstub(pname varchar2, uname varchar2,
   stubSpec in out varchar2, stubText in out varchar2,
   flags varchar2 := '6') is
  rc varchar2(40);
  ty varchar2(5);
  cursor tub (una varchar2, dbna varchar2, luna varchar2, luty varchar2) is
 select line from sys.pstubtbl 
 where (una is null or username = una) and
   (dbna is null or dbname = dbna) and
   lun = luna and lutype = luty
 order by lineno;
begin -- main
  sys.pstubt(pname, uname, '', flags, rc);
  if rc like '$$$%' then stubText := rc; return; end if;
  if not (rc = 'PKG' or rc = 'SUB') 
then stubText := '$$$ other'; return; 
  end if;
  stubSpec := '';
  stubText := '';
  if rc = 'PKG' then
for s in tub(uname, '', pname, 'PKS') loop
  stubSpec := stubSpec || s.line;
end loop;
  end if;
  if rc = 'PKG' then ty := 'PKB'; else ty := 'SUB'; end if;
  for s in tub(uname, '', pname, ty) loop
stubText := stubText || s.line;
  end loop;
end;
 
 
Could you please let me know the alternatives for stored procedures and Triggers in 
mysql 4.0.
 
 
thanks,
Narasimha
 
 
-Original Message- 
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Fri 10/1/2004 1:16 AM 
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) 
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: RE: views/cursors in mysql 4.0




Views - instead of running queries against views, you will have to run your 
queries against the underlying tables. This may mean lots of changes to your SQL 
statements.  What once appeared as a single query may now have to be performed in 
multiple steps to achieve the same results.  Usually, this kind of redesign actually 
improves application performance as you no longer rely on a set of table abstractions 
which require additional overhead to maintain. 

Cursors - Any SQL-scripted processing you did using cursors will have to be 
replaced with C/C++ routines that perform the same functions. You navigate recordsets 
according to the methods exposed by whichever library you use to interact with the 
server. Consult the documentation for the library you will use for specific details. 

Sorry I couldn't be more exact in my descriptions but you were not very clear 
on your requirements. Please respond with more detailed descriptions of exactly what 
you need from the database and I am sure someone on the list can help. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 


[EMAIL PROTECTED] wrote on 09/30/2004 03:24:52 PM:

 Hi, 
Thank you for your response. Strictly we have to use mysql 4.
 0 only. Actually

Re: views/cursors in mysql 4.0

[Martijn Tonies]

 Could you please let me know the alternatives for stored procedures and
Triggers in mysql 4.0.

The only alternative is application code.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

RE: views/cursors in mysql 4.0

[SGreen]

My comments embedded below

[EMAIL PROTECTED] wrote on 09/30/2004 04:18:02 PM:

 
 Hi,
 Thank you very much for your reply. 
 
The existed system , a network management tool , is developed
 using C as program lanuage and oracle 7.3 as the database. There are
 3 layers in the database like table layer, view layer and DBview 
 layer. The DBView layer communicates through API's with both table 
 and view layers. Views and tables are created dynamically through 
 API's. Here Views are created for each table (reason i too donot 
 know). And used some stored procedures at module level and some 
 triggers on some table before/after updating. 
 The above existed system's whole database layer including table 
 layer/views layer and DBView layer to be migrated to mysql 4.0 with 
 out innodb. Need to change the API's also. So, the views/stored 
 procedurs (some of them used cursors)/ triggers  need to be 
 converted to mysql 4.0 from oracle 7.3.  That is the requrement.

triggers and stored procedures won't exist until MySQL 5.X. All of the 
administration that you automated through triggers will now have to be 
coded into your application. Any action that you had coded in a stored 
procedure will now have to be recoded into your application.

 
 Views - instead of running queries against views, you will have to 
 run your queries against the underlying tables. This may mean lots 
 of changes to your SQL statements.  What once appeared as a single 
 query may now have to be performed in multiple steps to achieve the 
 same results.  Usually, this kind of redesign actually improves 
 application performance as you no longer rely on a set of table 
 abstractions which require additional overhead to maintain. 
 
 --- If i want to use the same data by querying underlying tables 
 again how to use that?. where to store that data for using again?. 

Views are not stored bits of information. They are queries that you query 
against. If you need to store information, use either a table or a 
temporary table.

 Shall i need to write the same query again when i need the same 
 data?. In the existed system views are created only once at runtime.
 could you please explain me in detail.

Where you once were able to query the results of a query (the contents of 
a view), you will have to rewrite your SQL statements so that they get 
their data directly from the tables the views would have abstracted for 
you.  You lose one layer of abstraction by not having views available.



 
 Cursors - Any SQL-scripted processing you did using cursors will 
 have to be replaced with C/C++ routines that perform the same 
 functions. You navigate recordsets according to the methods exposed 
 by whichever library you use to interact with the server. Consult 
 the documentation for the library you will use for specific details. 
 
 --- Supggest me the best API's to perform SQL- scripted 
 performance?. Could you please mention what are all the libraries we
 needed for the general  cursor processings. Given the oracle code 
 which is used the cursors, could you please mention the equivalent 
 libraries for those

According to this page: http://dev.mysql.com/downloads/
You have several options of APIs. Use which one fits your development 
environment. 

 
CREATE procedure pstub(pname varchar2, uname varchar2,
stubSpec in out varchar2, stubText in out varchar2,
flags varchar2 := '6') is
   rc varchar2(40);
   ty varchar2(5);
   cursor tub (una varchar2, dbna varchar2, luna varchar2, luty varchar2) 
is
  select line from sys.pstubtbl 
  where (una is null or username = una) and
(dbna is null or dbname = dbna) and
lun = luna and lutype = luty
  order by lineno;
 begin -- main
   sys.pstubt(pname, uname, '', flags, rc);
   if rc like '$$$%' then stubText := rc; return; end if;
   if not (rc = 'PKG' or rc = 'SUB') 
 then stubText := '$$$ other'; return; 
   end if;
   stubSpec := '';
   stubText := '';
   if rc = 'PKG' then
 for s in tub(uname, '', pname, 'PKS') loop
   stubSpec := stubSpec || s.line;
 end loop;
   end if;
   if rc = 'PKG' then ty := 'PKB'; else ty := 'SUB'; end if;
   for s in tub(uname, '', pname, ty) loop
 stubText := stubText || s.line;
   end loop;
 end;

This stored procedure would need to be recoded. Possibly as a class's 
method or as a stand alone function. No matter where in your code it 
resides, you will have to manually manage the records and values once 
automated for you by the cursor. You would run a query to get

select line from sys.pstubtbl 
  where (una is null or username = una) and
(dbna is null or dbname = dbna) and
lun = luna and lutype = luty
 order by lineno; 

Then step through those results one record at a time until you exhaust 
your results. During each step through your results you would need to 
duplicate the processing that occurred during each step of the cursor 
through its recordset.

 
 
 Could you please let me know

RE: views/cursors in mysql 4.0

[lakshmi.narasimharao]

Hi, 

Thank you very much for your reply. So for the cursors result set C API's will be 
suitable ..right?.

thanks,

Narasimha

 

 

 




Confidentiality Notice 

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 confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

MySQL 4.0 Installation Fault

[CHAN YICK WAI]

Hi,

I've used MySQL 3.23 on Redhat 9 for a long time. Recently, I'm thinking of
using MySQL 4 because some applications need it. I download a rpm from
mysql.com and have it rpm. It can't start and comes up with problem as

-==
terribly wrong...
Bogus stack limit or frame pointer, fp=0xbfffd2b8, stack_bottom=0x5f656c62,
thread_stack=126976, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x61742d2d  is invalid pointer
thd-thread_id=1966159154
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
040901 23:38:39  mysqld ended
===

website provides a detailed information on it. However, I guess it's some
minor and simple thing that makes me stucked here. I tried to install it on
another machine, the same thing comes up. But when I try to use mysqld_safe,
it's okay. But not this machine I want to run it.

I used the same procedure for installation, both systems are Redhat 9
without big changes. The major difference is on the hardware.

1) This one is okay by mysqld_safe: Celeron 2.0, 256Mb DDR,
2) This one is not okay by any means: Pentium II, 128MB RAM

Will you have any experience to share?


Thanks a lot.


Regards, Yw


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

RE: Migration tools/plan from oracle 7.3 to Mysql 4.0

[lakshmi.narasimharao]

Hi,
   I have to migrate oracle 7.3 database to MySql , for oracle used Pro*C.
In the front end used Java, JNI, RMI and Pro*C. 
In the migration process what will be the best equivalent of Pro*C in MySql.
Ans also need to use XML, for the above environment what are the best langauge suit 
for XML handling( c++ or java) and what will be the best parsers for XML (like jaxp 
..etc). Please advise me here.
 
Thanks,
Narasimha
91 98456 82459

-Original Message- 
From: Lakshmi NarasimhaRao (WT01 - TELECOM SERVICE PROVIDER) 
Sent: Tue 8/17/2004 3:08 PM 
To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Cc: 
Subject: RE: Migration tools/plan from oracle 7.3 to Mysql 4.0




Hi,
 Thank you for your response. Could any one give more clarification on 
the alternatives for Views, Stored procedures, triggers and contraints of oracle in 
Mysql 4.0 classic, as MySql 4.0 classic wonot support the InnoDB storage engine.

Thanks,
Narasimha

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tue 8/17/2004 12:59 PM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SERVICE PROVIDER); [EMAIL 
PROTECTED]
Cc:
Subject: Re: Migration tools/plan from oracle 7.3 to Mysql 4.0
   
   

First: please don't hijack threads... And don't quote them completely
if you do.
   
  I am new to MySql. Could you please let me know the 
different
migration tools avialable for the migration of oracle 7.3 database to 
mysql
4.0 classic version.
   
You could try the Schema Migrator tool in Database Workbench
( www.upscene.com ) - it supports ADO and ODBC connectivity
to MySQL. Might not be perfect, but sure gives you a good start.
   
Out of those which one is the best in performance and cost wise. As 
per my
knowledge views, stored procedures and triggers are not supported in 
MySql
4.0. Is it correct?.
   
That is correct. Add to that: no CHECK constraints either.
Foreign Key Constraints and transactions are only supported with
the InnoDB storage engine - read about that in the documentation.
   
If so, what are all the alternatives for converting the views, stored
procedures and triggers in oracle 7.3 to MySql 4.0. I will be very 
happy for
giving me a very good explanation on this.

   
With regards,
   
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  
MS SQL
Server.
Upscene Productions
http://www.upscene.com
   
   
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
   
   





Confidentiality Notice

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 confidential or 
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or 
[EMAIL PROTECTED] immediately
and destroy all copies of this message and any attachments.






Confidentiality Notice 

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 confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

RE: Migration tools/plan from oracle 7.3 to Mysql 4.0

[Karam Chand]

Give a look to SQLyogs ODBC Import Tool. It can be
found at http://www.webyog.com

Regards,
Karam
--- [EMAIL PROTECTED] wrote:

 
 Hi,
I have to migrate oracle 7.3 database to
 MySql , for oracle used Pro*C.
 In the front end used Java, JNI, RMI and Pro*C. 
 In the migration process what will be the best
 equivalent of Pro*C in MySql.
 Ans also need to use XML, for the above environment
 what are the best langauge suit for XML handling(
 c++ or java) and what will be the best parsers for
 XML (like jaxp ..etc). Please advise me here.
  
 Thanks,
 Narasimha
 91 98456 82459
 
   -Original Message- 
   From: Lakshmi NarasimhaRao (WT01 - TELECOM SERVICE
 PROVIDER) 
   Sent: Tue 8/17/2004 3:08 PM 
   To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
   Cc: 
   Subject: RE: Migration tools/plan from oracle 7.3
 to Mysql 4.0
   
   
 
 
   Hi,
Thank you for your response. Could any one
 give more clarification on the alternatives for
 Views, Stored procedures, triggers and contraints of
 oracle in Mysql 4.0 classic, as MySql 4.0 classic
 wonot support the InnoDB storage engine.
   
   Thanks,
   Narasimha
   
   -Original Message-
   From: Martijn Tonies
 [mailto:[EMAIL PROTECTED]
   Sent: Tue 8/17/2004 12:59 PM
   To: Lakshmi NarasimhaRao (WT01 - TELECOM
 SERVICE PROVIDER); [EMAIL PROTECTED]
   Cc:
   Subject: Re: Migration tools/plan from
 oracle 7.3 to Mysql 4.0
  
  
   
   First: please don't hijack threads... And
 don't quote them completely
   if you do.
  
 I am new to MySql. Could you
 please let me know the different
   migration tools avialable for the migration
 of oracle 7.3 database to mysql
   4.0 classic version.
  
   You could try the Schema Migrator tool in
 Database Workbench
   ( www.upscene.com ) - it supports ADO and
 ODBC connectivity
   to MySQL. Might not be perfect, but sure
 gives you a good start.
  
   Out of those which one is the best in
 performance and cost wise. As per my
   knowledge views, stored procedures and
 triggers are not supported in MySql
   4.0. Is it correct?.
  
   That is correct. Add to that: no CHECK
 constraints either.
   Foreign Key Constraints and transactions
 are only supported with
   the InnoDB storage engine - read about that
 in the documentation.
  
   If so, what are all the alternatives for
 converting the views, stored
   procedures and triggers in oracle 7.3 to
 MySql 4.0. I will be very happy for
   giving me a very good explanation on this.
   
  
   With regards,
  
   Martijn Tonies
   Database Workbench - developer tool for
 InterBase, Firebird, MySQL  MS SQL
   Server.
   Upscene Productions
   http://www.upscene.com
  
  
   --
   MySQL General Mailing List
   For list archives:
 http://lists.mysql.com/mysql
   To unsubscribe:   

http://lists.mysql.com/[EMAIL PROTECTED]
  
  
   
   
   
   
   
   Confidentiality Notice
   
   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 confidential or privileged information. If
   you are not the intended recipient, please notify
 the sender at Wipro or [EMAIL PROTECTED]
 immediately
   and destroy all copies of this message and any
 attachments.
   
   
 
 
 
 
 Confidentiality Notice 
 
 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 confidential or privileged information. If
 you are not the intended recipient, please notify
 the sender at Wipro or [EMAIL PROTECTED]
 immediately
 and destroy all copies of this message and any
 attachments.
 




___
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush

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

Mysql 4.0 windows XP Downloads

[Francois A. Atallah]

May someone please send me the link to download mysql Binary distribution
for the first time on windows XP, I have looked into mysql/downloads and
they only have X86.


Thanks,
Francois A 

Re: Mysql 4.0 windows XP Downloads

[jeffrey_n_Dyke]

 May someone please send me the link to download mysql Binary distribution
 for the first time on windows XP, I have looked into mysql/downloads and
 they only have X86.

Windows XP, and every other windows platform, runs on X86 architecture.
That is the download you want.

Jeff








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

Re: Mysql 4.0 windows XP Downloads

[Julian]

Francois,
You didn't reply to the list, just to me. If you reply to the list, more 
people will be able to help you. I'm quite new myself.

You need to set up user accounts. Download an admin program such as 
MySQL Control Center. Maybe there are better ones, but that's what I 
use. You can do a lot of stuff with this and save your self from having 
to type at the command line.

MySql Control Center
http://dev.mysql.com/downloads/other/mysqlcc.html
Julian
Francois A. Atallah wrote:
Y I'm getting this error every time I want to access the mysql database
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
I can only bypass this error if I use -u root ? 

Thanks,
Francois A
-Original Message-
From: Julian [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 20, 2004 12:04 PM
To: [EMAIL PROTECTED]
Subject: Re: Mysql 4.0 windows XP Downloads

That's it. You had it. x86 refers to the Intel chip family not the operating
system. I'm using that quite happily on XP.
Julian
Francois A. Atallah wrote:
May someone please send me the link to download mysql Binary 
distribution for the first time on windows XP, I have looked into 
mysql/downloads and they only have X86.

Thanks,
Francois A


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

Re: Migration tools/plan from oracle 7.3 to Mysql 4.0

[Martijn Tonies]

First: please don't hijack threads... And don't quote them completely
if you do.

  I am new to MySql. Could you please let me know the different
migration tools avialable for the migration of oracle 7.3 database to mysql
4.0 classic version.

You could try the Schema Migrator tool in Database Workbench
( www.upscene.com ) - it supports ADO and ODBC connectivity
to MySQL. Might not be perfect, but sure gives you a good start.

Out of those which one is the best in performance and cost wise. As per my
knowledge views, stored procedures and triggers are not supported in MySql
4.0. Is it correct?.

That is correct. Add to that: no CHECK constraints either.
Foreign Key Constraints and transactions are only supported with
the InnoDB storage engine - read about that in the documentation.

If so, what are all the alternatives for converting the views, stored
procedures and triggers in oracle 7.3 to MySql 4.0. I will be very happy for
giving me a very good explanation on this.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

RE: Migration tools/plan from oracle 7.3 to Mysql 4.0

[lakshmi.narasimharao]

Hi, 
 Thank you for your response. Could any one give more clarification on the 
alternatives for Views, Stored procedures, triggers and contraints of oracle in Mysql 
4.0 classic, as MySql 4.0 classic wonot support the InnoDB storage engine.
 
Thanks,
Narasimha 

-Original Message- 
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Tue 8/17/2004 12:59 PM 
To: Lakshmi NarasimhaRao (WT01 - TELECOM SERVICE PROVIDER); [EMAIL PROTECTED] 
Cc: 
Subject: Re: Migration tools/plan from oracle 7.3 to Mysql 4.0



First: please don't hijack threads... And don't quote them completely
if you do.

  I am new to MySql. Could you please let me know the different
migration tools avialable for the migration of oracle 7.3 database to mysql
4.0 classic version.

You could try the Schema Migrator tool in Database Workbench
( www.upscene.com ) - it supports ADO and ODBC connectivity
to MySQL. Might not be perfect, but sure gives you a good start.

Out of those which one is the best in performance and cost wise. As per my
knowledge views, stored procedures and triggers are not supported in MySql
4.0. Is it correct?.

That is correct. Add to that: no CHECK constraints either.
Foreign Key Constraints and transactions are only supported with
the InnoDB storage engine - read about that in the documentation.

If so, what are all the alternatives for converting the views, stored
procedures and triggers in oracle 7.3 to MySql 4.0. I will be very happy for
giving me a very good explanation on this.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


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







Confidentiality Notice 

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 confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

Re: Migration tools/plan from oracle 7.3 to Mysql 4.0

[Martijn Tonies]

  Thank you for your response. Could any one give more
clarification on the alternatives for Views, Stored procedures, triggers and
contraints of oracle in Mysql 4.0 classic, as MySql 4.0 classic wonot
support the InnoDB storage engine.


Well, what other alternative do you have but application code?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

Re: Migration tools/plan from oracle 7.3 to Mysql 4.0

[Martijn Tonies]

Re: Migration tools/plan from oracle 7.3 to Mysql 4.0With application code, I meant 
that you:

- have to change your queries so that they include the full VIEW sql, because MySQL 
doesn't have an alternative
- have to recreate your stored procedures in application code and do you work at the 
client or a middle tier
- have to recreate all your triggers in application code to do additional work instead 
of the triggers on the database
- have to bring your checks into the application (but they won't be safe anyway, 
'cause MySQL Classic doesn't have transactions)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL Server.
Upscene Productions
http://www.upscene.com


 I had the application code. What will be the eqaulant of views in Mysql?. I 
mean in what way i have to migrate the views, triggers, stored procedures and 
contraints of Oracle to Mysql 4.0 classic version (This does not vave innodb engine).

  Thanks,
  Narasimha
  919845682459


-Original Message- 
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Tue 8/17/2004 3:45 PM 
To: Lakshmi NarasimhaRao (WT01 - TELECOM SERVICE PROVIDER); [EMAIL PROTECTED] 
Cc: 
Subject: Re: Migration tools/plan from oracle 7.3 to Mysql 4.0




  Thank you for your response. Could any one give more
clarification on the alternatives for Views, Stored procedures, triggers and
contraints of oracle in Mysql 4.0 classic, as MySql 4.0 classic wonot
support the InnoDB storage engine.


Well, what other alternative do you have but application code?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com



Confidentiality Notice 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 confidential or privileged information. If you are not 
the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] 
immediately and destroy all copies of this message and any attachments.  

Re: Migration tools/plan from oracle 7.3 to Mysql 4.0

[Martijn Tonies]

Re: Migration tools/plan from oracle 7.3 to Mysql 4.0Hi Narasimha,

Please reply to the MySQL list instead of me personally only.

 How we can write the equivalent code for triggers of
oracle in Mysql?. Shall we do it by using User Defined
Functions?. Can we use database queries inside the user
defined functions?. Please advise.

As far as I can see, User Defined Functions have nothing
to do with it.

If you need Stored Procedures, Triggers and working
constraints, MySQL is not the right database engine for
you. Plain and simple.

The equivalent of triggers in application code highly
depends on the type of trigger.

For example, if you trigger does a couple of additional
INSERTs, you need to copy this code to your application.

It ain't easy. And when it comes to constraints, it's
downright hairy. I wouldn't trust my data to a database
without constraints.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

Re: Migration tools/plan from oracle 7.3 to Mysql 4.0

[Michael Stassen]

Martijn Tonies wrote:
Re: Migration tools/plan from oracle 7.3 to Mysql 4.0With application code, I meant 
that you:
- have to change your queries so that they include the full VIEW sql,
  because MySQL doesn't have an alternative
- have to recreate your stored procedures in application code and do you
  work at the client or a middle tier
MySQL 5 has stored procedures.  Of course, it's still alpha.
- have to recreate all your triggers in application code to do additional
   work instead of the triggers on the database
- have to bring your checks into the application (but they won't be safe
  anyway, 'cause MySQL Classic doesn't have transactions)
Well, if you need transactions, then why not use MySQL 4 Standard (or Pro) 
which does support InnoDB tables and transactions?

With regards,
Martijn Tonies
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Migration tools/plan from oracle 7.3 to Mysql 4.0

[lakshmi.narasimharao]

Hi,
 I am new to MySql. Could you please let me know the different migration tools 
avialable for the migration of oracle 7.3 database to mysql 4.0 classic version. Out 
of those which one is the best in performance and cost wise. As per my knowledge 
views, stored procedures and triggers are not supported in MySql 4.0. Is it correct?. 
If so, what are all the alternatives for converting the views, stored procedures and 
triggers in oracle 7.3 to MySql 4.0. I will be very happy for  giving me a very good 
explanation on this.
 
Thanks,
Narasimha
 

-Original Message- 
From: Bob Afifi [mailto:[EMAIL PROTECTED] 
Sent: Mon 8/16/2004 10:13 PM 
To: [EMAIL PROTECTED] 
Cc: 
Subject: Update MySQL row using URL link?



I currently update MySQL rows using phpMyAdmin.
For example, dropping the following into the
phpMyAdmin GUI:

UPDATE mysql_db SET publish = 1 WHERE Date =
'Sunday, August 15, 2004 21:04:32'

Since I get the update info in an e-mail send
whenever the form is submitted, I'd like to turn
querys like the above encoded into a URL -
bypassing phpMyAdmin - which when clicked, will
update the row.

I found this article last night: Make SQL
Queries over HTTP with XML with VS.NET 
(http://www.aspfree.com/c/a/ASP/Make-SQL-Queries-over-HTTP-with-XML-with-VSNET/
)

SELECT CustomerId, CompanyName FROM Customer


http://localhost/sql?sql=select%20CustomerId,%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO

=

The above looks very much like what I have in
mind, but for MySQL.  Anybody know how to do
this?

Many thanks in advance,

-Bob

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







Confidentiality Notice 

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 confidential or privileged 
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

set up UTF8 for MySQL 4.0.x

[Guofeng Zhang]

Our web site will store  the text in a number of languages in the mysql DB, so that we 
think that MySQL should be configured to store chars in UTF8.

For my requirement, how to configure the MySQL. to store Unicode characters?

Thank you very much.



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

Re: set up UTF8 for MySQL 4.0.x

[Egor Egorov]

Guofeng Zhang [EMAIL PROTECTED] wrote:

 Our web site will store  the text in a number of languages in the mysql =
 DB, so that we think that MySQL should be configured to store chars in =
 UTF8.
 
 For my requirement, how to configure the MySQL. to store Unicode =
 characters?

No need. Just store unicode as binary data. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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: set up UTF8 for MySQL 4.0.x

[Guofeng Zhang]

(BThank you.
(B
(BIn the PDF doc:
(B4.1.0 Server based Character Settings
(BSetting the servers default character set
(Bmysqld ?default-character-set=ujis
(Bor (in my.cnf etc.)
(B[mysqld]
(Bdefault-character-set=ujis
(B
(BThat's what I want by my last mail.
(B
(BFor my case, shall I change it to:
(B[mysqld]
(Bdefault-character-set=utf8
(B
(BAnother question, shall I have to upgrade to MySQL 4.1.x?
(B
(BThank you very much..
(B
(B-Original Message-
(BFrom: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
(BSent: Tuesday, August 03, 2004 6:21 PM
(BTo: Guofeng Zhang
(BCc: [EMAIL PROTECTED]
(BSubject: Re: set up UTF8 for MySQL 4.0.x
(B
(B
(BHello,
(B
(BI have made apresentation at MySQLs UC2004 about using Japanese character
(Bsets with MySQL. You may find this a good starting point. Links are below:
(B
(Bhttp://dev.mysql.com/doc/mysql/en/Charset.html
(Bhttp://www.be-known-online.com/mysql
(B
(B
(BI hope you find this information useful
(B
(BBest regards
(B
(BNils Valentin
(B
(B
(B Our web site will store  the text in a number of languages in the mysql
(B DB, so that we think that MySQL should be configured to store chars in
(B UTF8.
(B
(B For my requirement, how to configure the MySQL. to store Unicode
(B characters?
(B
(B Thank you very much.
(B
(B
(B
(B --
(B MySQL General Mailing List
(B For list archives: http://lists.mysql.com/mysql
(B To unsubscribe:
(B http://lists.mysql.com/[EMAIL PROTECTED]
(B
(B
(B
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: set up UTF8 for MySQL 4.0.x

[Guofeng Zhang]

Does that means that we need to change the column type from varchar to some other 
type? Which type to use?

thank you.


-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 6:24 PM
To: [EMAIL PROTECTED]
Subject: Re: set up UTF8 for MySQL 4.0.x


Guofeng Zhang [EMAIL PROTECTED] wrote:

 Our web site will store  the text in a number of languages in the mysql =
 DB, so that we think that MySQL should be configured to store chars in =
 UTF8.
 
 For my requirement, how to configure the MySQL. to store Unicode =
 characters?

No need. Just store unicode as binary data. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

MySQL 4.0.x charset

[Yves]

Hello,

How can I see what char set is being used as the
default char set on the server or database?

Also, is there a way to change the default setting?

Thanks,

Yves

__ 
Post your free ad now! http://personals.yahoo.ca

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

Re: MySQL 4.0.x charset

[Jean-Marc PULVAR]

You can see it by executing the SHOW VARIABLES query on your server. 
You will find the used charset in the 'character_set' variable.

You can also use the query SHOW VARIABLES LIKE 'character_set' which 
will directly match what you want.

Yves wrote:
Hello,
How can I see what char set is being used as the
default char set on the server or database?
Also, is there a way to change the default setting?
Thanks,
Yves
__ 
Post your free ad now! http://personals.yahoo.ca

--
---
 ___   _
 __ / __\ ___   ___  _ __ | |_ _   _
(__)   /__\/// _ \ / _ \| '_ \| __| | | |
 ||   / \/  \ (_) | (_) | | | | |_| |_| |
 ||   \_/\___/ \___/|_| |_|\__|\__, |
 ___||__.._  |___/
/\
\/~~~ Jean-Marc PULVAR (Web Programmer)
  Boonty SA
  4 bis villa du mont tonnerre
  75015 PARIS
---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 4.0.x charset

[Yves]

Thanks,

As it turns out, I was trying show variables like --

And had a syntax mistake

Does SHOW VARIABLES only show a certain number of
lines? When I ran this command, character_set was not
listed... the list seemed cut off just before it.

Unless it is an issue with SSH only showing x amount
of lines..

thanks,

Yves A

 --- Jean-Marc PULVAR [EMAIL PROTECTED] wrote: 
 You can see it by executing the SHOW VARIABLES
 query on your server. 
 You will find the used charset in the
 'character_set' variable.
 
 You can also use the query SHOW VARIABLES LIKE
 'character_set' which 
 will directly match what you want.
 
 Yves wrote:
 
  Hello,
  
  How can I see what char set is being used as the
  default char set on the server or database?
  
  Also, is there a way to change the default
 setting?
  
  Thanks,
  
  Yves
  
 

__
 
  Post your free ad now! http://personals.yahoo.ca
  
 
 -- 

---
   ___   _
   __ / __\ ___   ___  _ __ | |_ _   _
  (__)   /__\/// _ \ / _ \| '_ \| __| | | |
   ||   / \/  \ (_) | (_) | | | | |_| |_| |
   ||   \_/\___/ \___/|_| |_|\__|\__, |
   ___||__.._  |___/
 /\
 \/~~~ Jean-Marc PULVAR (Web
 Programmer)
Boonty SA
4 bis villa du mont tonnerre
75015 PARIS

---
  

__ 
Post your free ad now! http://personals.yahoo.ca

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

Re: MySQL 4.0.x charset

[Jean-Marc PULVAR]

It's possibly due to your ssh connection but I couldn't answer you about 
this because I only use mysql with phpmyadmin.

Regards
Yves wrote:
Thanks,
As it turns out, I was trying show variables like --
And had a syntax mistake
Does SHOW VARIABLES only show a certain number of
lines? When I ran this command, character_set was not
listed... the list seemed cut off just before it.
Unless it is an issue with SSH only showing x amount
of lines..
thanks,
Yves A
 --- Jean-Marc PULVAR [EMAIL PROTECTED] wrote: 

You can see it by executing the SHOW VARIABLES
query on your server. 
You will find the used charset in the
'character_set' variable.

You can also use the query SHOW VARIABLES LIKE
'character_set' which 
will directly match what you want.

Yves wrote:

Hello,
How can I see what char set is being used as the
default char set on the server or database?
Also, is there a way to change the default
setting?
Thanks,
Yves


__
Post your free ad now! http://personals.yahoo.ca
--
---
 ___   _
 __ / __\ ___   ___  _ __ | |_ _   _
(__)   /__\/// _ \ / _ \| '_ \| __| | | |
 ||   / \/  \ (_) | (_) | | | | |_| |_| |
 ||   \_/\___/ \___/|_| |_|\__|\__, |
 ___||__.._  |___/
/\
\/~~~ Jean-Marc PULVAR (Web
Programmer)
  Boonty SA
  4 bis villa du mont tonnerre
  75015 PARIS
---


__ 
Post your free ad now! http://personals.yahoo.ca

--
---
 ___   _
 __ / __\ ___   ___  _ __ | |_ _   _
(__)   /__\/// _ \ / _ \| '_ \| __| | | |
 ||   / \/  \ (_) | (_) | | | | |_| |_| |
 ||   \_/\___/ \___/|_| |_|\__|\__, |
 ___||__.._  |___/
/\
\/~~~ Jean-Marc PULVAR (Web Programmer)
  Boonty SA
  4 bis villa du mont tonnerre
  75015 PARIS
---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select in Mysql 4.0

[Jocelyn Fournier]

Hi,

AFAIK, date is *not* a reserved keyword, not need to backtick it :)

Regards,
  Jocelyn Fournier
  www.presence-pc.com
- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: fgmmoribe [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 7:11 AM
Subject: Re: select in Mysql 4.0



 fgmmoribe wrote:
  I have a table like this
 
 
+-+---+--+-+-++
  | Field   | Type  | Null | Key | Default | Extra
|
 
+-+---+--+-+-++
  | id  | int(3)|  | PRI | NULL|
auto_increment |
  | idTable | int(3) unsigned   |  | | 0   |
|
  | title   | varchar(150)  | YES  | | NULL|
|
  | description | varchar(150)  | YES  | | NULL|
|
  | date| datetime  | YES  | | NULL|
|
 
+-+---+--+-+-++
 
  Is there anyway to make select command like this in Mysql 4.0:
  select * from #temp where cod in (select max(cod) from #temp
  group by idtable) order by data desc
 
  could someone help me?
 
  thanks
 
  Fernando

 Subqueries require mysql 4.1.

 date is a reserved word, so not the best choice for a column name.  You'll
 always have to quote it with backticks to use it.

 Your query doesn't seem to match your table.

 That said, I think you want
 http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html.

 Michael


 -- 
 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: select in Mysql 4.0

[SGreen]

To get the one record with the MAX() value in a field, I would do:

SELECT @maxcod:=max(cod) from #temp;
SELECT * from #temp where cod = @maxcod;

To get all of the records with the max(cod) value for each idtable value, 
I would use a temp table:

create temporary table tmpMax
SELECT idtable, max(cod) as maxcod
from #temp
group by idtable;

SELECT * 
FROM #temp t
INNER JOIN tmpMax tm
ON t.idtable = tm.idtable
ORDER BY Data DESC;

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

fgmmoribe [EMAIL PROTECTED] wrote on 07/26/2004 10:54:30 PM:

 
 I have a table like this
 
 
+---+-+--+-+-++
 | Field | Type | Null | Key | Default | Extra |
 
+---+-+--+-+-++
 | id | int(3) | | PRI | NULL |
 auto_increment |
 | idTable | int(3) unsigned | | | 0 | |
 | title | varchar(150) | YES | | NULL |
 |
 | description | varchar(150) | YES | | NULL | |
 | date | datetime | YES | | NULL | |
 
+---+-+--+-+-++
 
 
 Is there anyway to make select command like this in Mysql 4.0:
 select * from #temp where cod in (select max(cod) from #temp
 group by idtable) order by data desc
 
 could someone help me?
 
 thanks
 
 Fernando
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: select in Mysql 4.0

[Michael Stassen]

You're absolutely right, date is not a reserved word.  Don't know what I was 
thinking.

Michael
Jocelyn Fournier wrote:
Hi,
AFAIK, date is *not* a reserved keyword, not need to backtick it :)
Regards,
  Jocelyn Fournier
  www.presence-pc.com
- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: fgmmoribe [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, July 27, 2004 7:11 AM
Subject: Re: select in Mysql 4.0


fgmmoribe wrote:
I have a table like this

+-+---+--+-+-++
| Field   | Type  | Null | Key | Default | Extra
|
+-+---+--+-+-++
| id  | int(3)|  | PRI | NULL|
auto_increment |
| idTable | int(3) unsigned   |  | | 0   |
|
| title   | varchar(150)  | YES  | | NULL|
|
| description | varchar(150)  | YES  | | NULL|
|
| date| datetime  | YES  | | NULL|
|
+-+---+--+-+-++
Is there anyway to make select command like this in Mysql 4.0:
select * from #temp where cod in (select max(cod) from #temp
group by idtable) order by data desc
could someone help me?
thanks
Fernando
Subqueries require mysql 4.1.
date is a reserved word, so not the best choice for a column name.  You'll
always have to quote it with backticks to use it.
Your query doesn't seem to match your table.
That said, I think you want
http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html.
Michael
--
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]