Re: filename-safe conversion of database-/tablenames
- Original Message - > From: "Simon Fromme" <fro...@tralios.de> > Subject: filename-safe conversion of database-/tablenames > > I need to convert both the names of databases and tables in a > filename-safe way (escaping "/" and other characters as in [1]). The I don't know what strange table names you're expecting, but under *nix almost anything short of / (directory separator) is valid in a filename, even the wildcard characters ? and *. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
filename-safe conversion of database-/tablenames
Hello, In order to do MySQL-dumps to a file on a Linux system under $BACKUP_DIR/$DB_NAME/$TABLE_NAME.sql I need to convert both the names of databases and tables in a filename-safe way (escaping "/" and other characters as in [1]). The mapping of MySQL database/table name to the according filenames should preferably be the same that MySQL (or the particular DB engine) uses. If that's not possible the mapping should at least be injective and preferably be human readable. I found out that MySQL is using the C-function tablename_to_filename(...) [2] internally but didn't find a way in which it exposes this conversion function to the outside. Did I overlook some way this could be done? If not, would this be a feature that a future version of MySQL should provide? Best regards Simon Fromme [1]: https://dev.mysql.com/doc/refman/5.7/en/identifier-mapping.html [2]: http://osxr.org:8080/mysql/source/sql/sql_table.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Wrong conversion to timestamp from character string
2011/08/18 18:30 +0300, Marius Feraru Thanks for your time reading my message, but I don't understand what is the result context that you are talking about. Could you please elaborate? Well, if an expression is an argument to, say, CONCAT, the expression s result is character string. An argument to, say, POW is number. But the second and third arguments to IF have the same type, the type of the IF s context, and an expression that is an operand to SELECT may have any type: the result context does not require anything. Now, your expression IF( DATE(d) = some-date, TIME(d), d ) is an operand to SELECT, and no type is required of it--but the types are not the same, wherefore there is at least one conversion, surely that the bare d is made character string. But it seems that instead TIME(d), a character string, is converted to some timestamp, a date. I wrote that I believe this an optimizer error because the least characterward tweak to this is enough to make it that which you seek: either concatenating empty string to TIME(d), thereby overriding any tendency the optimizer has to consider it other than a character string, or by concatenating empty string to the whole expression, thereby making the IF s result context character string, not any type. Consider this: -- 1) create test table containing a single DATETIME column CREATE TABLE dt (d DATETIME); -- 2) insert two test records INSERT INTO dt VALUES (2011-08-07 10:11:12),(1234-05-06 07:08:09); SELECT d, ADDTIME(IF( DATE(d)='2011-08-07', TIME(d), d), '1 1:1:1.02') as x FROM dt; The outcome is really screwy: +-++ | d | x | +-++ | 2011-08-07 10:11:12 | 2035-12-13 02:00:00| | 1234-05-06 07:08:09 | 1234-05-07 08:09:10.02 | +-++ Surely there is a noteworthy bug here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is conversion required?
I just converted (reinstalled) a FreeBSD system from i386 to amd64. Of course; I missed the memo. I have been struggling to get everything back online. I just finished exporting a few Gigs of RRD's to XML so that I could use them :| My question: I was s/rushing/stupid so I just moved /var/mysql to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? Thanks! -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
Re: Is conversion required?
On Sep 29, 2010, at 5:15 PM, Paul Halliday wrote: I just converted (reinstalled) a FreeBSD system from i386 to amd64. Of course; I missed the memo. I have been struggling to get everything back online. I just finished exporting a few Gigs of RRD's to XML so that I could use them :| My question: I was s/rushing/stupid so I just moved /var/mysql to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? If you're talking about the data, I wouldn't expect this change to cause issues, unless perhaps you're also updating to a different version of MySQL. That could be a problem, depending on how different the old and new versions are. Consult the upgrading section of the manual to see. http://dev.mysql.com/doc/refman/5.1/en/upgrading.html -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Speeding up character set conversion
Hi, I just tried this on a local copy of the table with ~500.000 rows: execute 'ALTER TABLE users MODIFY email VARCHAR(255) CHARACTER SET latin1 NOT NULL' The old character set was UTF8. We're doing this to make the index smaller. This took around 45 minutes to complete. In production, we have about 1.000.000 rows. While the production servers are dedicated DB servers in comparison to my MacBook, I'm still concerned that this is going to literally take hours. How can this be speeded up if at all? Dropping the index first and then recreate? Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Speeding up character set conversion
Hi Morten, You might want to read this post on MySQL Performance Blog that was posted a few days ago: http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ Have a nice day, - Martijn On Wed, Mar 18, 2009 at 11:50, Morten my.li...@mac.com wrote: Hi, I just tried this on a local copy of the table with ~500.000 rows: execute 'ALTER TABLE users MODIFY email VARCHAR(255) CHARACTER SET latin1 NOT NULL' The old character set was UTF8. We're doing this to make the index smaller. This took around 45 minutes to complete. In production, we have about 1.000.000 rows. While the production servers are dedicated DB servers in comparison to my MacBook, I'm still concerned that this is going to literally take hours. How can this be speeded up if at all? Dropping the index first and then recreate? Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql to mysql conversion tool
Hi, I'm looking an application which let me convert one mysql to another db. I need this to port my website from one cms (Vivvo) to another (Drupal). -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and I are the initiator.
RE: mysql to mysql conversion tool
Check out DBConvert variants.. http://www.dbconvert.com/ -Original Message- From: Sharique uddin Ahmed Farooqui [mailto:[EMAIL PROTECTED] Sent: 29 January 2008 18:16 To: mysql@lists.mysql.com Subject: mysql to mysql conversion tool Hi, I'm looking an application which let me convert one mysql to another db. I need this to port my website from one cms (Vivvo) to another (Drupal). -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and I are the initiator. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql to mysql conversion tool
http://drupal.org/project/convert2drupal NB: no pending tasks AND no developers working on any part of the project the author claims a release in january (but has'nt published anything yet..) Looks like you there is some work ahead of you.. Oi Vay! - Original Message - From: Sharique uddin Ahmed Farooqui [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 29, 2008 1:16 PM Subject: mysql to mysql conversion tool Hi, I'm looking an application which let me convert one mysql to another db. I need this to port my website from one cms (Vivvo) to another (Drupal). -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and I are the initiator. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime type conversion problem
I am having issues with type conversion not working as expected per the
documentation.
I am using in MySQL 5.0.27 for x86/Windows.
The documentation at
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html states that for
comparison operators If one of the arguments is a TIMESTAMP or
DATETIMEcolumn and the other argument is a constant, the constant is
converted to a
timestamp before the comparison is performed.
So the following SQL statement should convert the last string to a timestamp
then perform date-time comparison:
SELECT '2007-05-24 00:00:00' BETWEEN
('2007-3-23' - INTERVAL 24 HOUR) AND '2007-3-23 23:59:59';
Thus it should return 0 as 24th May is not in the given range. I get the
result 1 however...
If I perform an explicit conversion using CAST('2007-3-23 23:59:59' AS
DATETIME) I get 0 as expected.
Can anyone enlighten me - have I misunderstood something somewhere?
Thanks,
--rob
Re: datetime type conversion problem
I'm not sure specifically why it's acting the way it is, but it looks
like it's converting your date strings slightly differently depending on
how you use them.
If you properly pad the month out in the two strings it seems to work.
SELECT '2007-05-24 00:00:00' BETWEEN ('2007-03-23' - INTERVAL 24 HOUR)
AND '2007-03-23 23:59:59';
Chris
Rob Desbois wrote:
I am having issues with type conversion not working as expected per the
documentation.
I am using in MySQL 5.0.27 for x86/Windows.
The documentation at
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html states
that for
comparison operators If one of the arguments is a TIMESTAMP or
DATETIMEcolumn and the other argument is a constant, the constant is
converted to a
timestamp before the comparison is performed.
So the following SQL statement should convert the last string to a
timestamp
then perform date-time comparison:
SELECT '2007-05-24 00:00:00' BETWEEN
('2007-3-23' - INTERVAL 24 HOUR) AND '2007-3-23 23:59:59';
Thus it should return 0 as 24th May is not in the given range. I get the
result 1 however...
If I perform an explicit conversion using CAST('2007-3-23 23:59:59' AS
DATETIME) I get 0 as expected.
Can anyone enlighten me - have I misunderstood something somewhere?
Thanks,
--rob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime type conversion problem
Hi Rob,
Rob Desbois wrote:
I am having issues with type conversion not working as expected per the
documentation.
I am using in MySQL 5.0.27 for x86/Windows.
The documentation at
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html states that for
comparison operators If one of the arguments is a TIMESTAMP or
DATETIMEcolumn and the other argument is a constant, the constant is
converted to a
timestamp before the comparison is performed.
So the following SQL statement should convert the last string to a
timestamp
then perform date-time comparison:
SELECT '2007-05-24 00:00:00' BETWEEN
('2007-3-23' - INTERVAL 24 HOUR) AND '2007-3-23 23:59:59';
Thus it should return 0 as 24th May is not in the given range. I get the
result 1 however...
If I perform an explicit conversion using CAST('2007-3-23 23:59:59' AS
DATETIME) I get 0 as expected.
Can anyone enlighten me - have I misunderstood something somewhere?
Thanks,
No, I think the problem is the conversion when using BETWEEN is not clearly documented.
Here is more on that:
http://www.xaprb.com/blog/2006/09/12/type-conversion-semantics-of-mysqls-between-operator/
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: datetime type conversion problem
Everything seems to go fine for me if I change the two 2007-3-23
to 2007-03-23.
Fred
-Original Message-
From: Rob Desbois [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 5:02 AM
To: mysql@lists.mysql.com
Subject: datetime type conversion problem
I am having issues with type conversion not working as expected per the
documentation.
I am using in MySQL 5.0.27 for x86/Windows.
The documentation at
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html states that for
comparison operators If one of the arguments is a TIMESTAMP or
DATETIMEcolumn and the other argument is a constant, the constant is
converted to a
timestamp before the comparison is performed.
So the following SQL statement should convert the last string to a timestamp
then perform date-time comparison:
SELECT '2007-05-24 00:00:00' BETWEEN
('2007-3-23' - INTERVAL 24 HOUR) AND '2007-3-23 23:59:59';
Thus it should return 0 as 24th May is not in the given range. I get the
result 1 however...
If I perform an explicit conversion using CAST('2007-3-23 23:59:59' AS
DATETIME) I get 0 as expected.
Can anyone enlighten me - have I misunderstood something somewhere?
Thanks,
--rob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime type conversion problem
Thanks for the replies all (and for the blog link - one to add to my feeds I
think).
Yes I spotted that adding the leading zero to the month yields the correct
result here, but I think I know why.
If you imagine BETWEEN using a string comparison here then the results for
with and without leading zero are both correct; but the documentation
explicitly states that for a comparison, timestamp conversions are used and
this appears not to be the case :-(
Perhaps I should pose this to the internals list...?
Rob
On 5/29/07, Fred Ballard [EMAIL PROTECTED] wrote:
Everything seems to go fine for me if I change the two 2007-3-23
to 2007-03-23.
Fred
-Original Message-
From: Rob Desbois [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 5:02 AM
To: mysql@lists.mysql.com
Subject: datetime type conversion problem
I am having issues with type conversion not working as expected per the
documentation.
I am using in MySQL 5.0.27 for x86/Windows.
The documentation at
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html states that
for
comparison operators If one of the arguments is a TIMESTAMP or
DATETIMEcolumn and the other argument is a constant, the constant is
converted to a
timestamp before the comparison is performed.
So the following SQL statement should convert the last string to a
timestamp
then perform date-time comparison:
SELECT '2007-05-24 00:00:00' BETWEEN
('2007-3-23' - INTERVAL 24 HOUR) AND '2007-3-23 23:59:59';
Thus it should return 0 as 24th May is not in the given range. I get the
result 1 however...
If I perform an explicit conversion using CAST('2007-3-23 23:59:59' AS
DATETIME) I get 0 as expected.
Can anyone enlighten me - have I misunderstood something somewhere?
Thanks,
--rob
--
Rob Desbois
Eml: [EMAIL PROTECTED]
Tel: 01452 760631
Mob: 07946 705987
There's a whale there's a whale there's a whale fish he cried, and the
whale was in full view.
...Then ooh welcome. Ahhh. Ooh mug welcome.
Re: datetime type conversion problem
Ah ok I rechecked the documentation for BETWEEN which includes additional
information not mentioned on the type conversion page:
For best results when using BETWEEN with date or time values, you should use
CAST() to explicitly convert the values to the desired data type. Examples:
If you compare a DATETIME to two DATE values, convert the DATE values to
DATETIME values. If you use a string constant such as '2001-1-1' in a
comparison to a DATE, cast the string to a DATE.
Doesn't necessarily explain why it doesn't work but does suggest the fix.
Thanks all for your input -- I've added a note to the type conversion page
to help other distraught users :-)
--rob
On 5/29/07, Rob Desbois [EMAIL PROTECTED] wrote:
Thanks for the replies all (and for the blog link - one to add to my feeds
I think).
Yes I spotted that adding the leading zero to the month yields the correct
result here, but I think I know why.
If you imagine BETWEEN using a string comparison here then the results for
with and without leading zero are both correct; but the documentation
explicitly states that for a comparison, timestamp conversions are used and
this appears not to be the case :-(
Perhaps I should pose this to the internals list...?
Rob
On 5/29/07, Fred Ballard [EMAIL PROTECTED] wrote:
Everything seems to go fine for me if I change the two 2007-3-23
to 2007-03-23.
Fred
-Original Message-
From: Rob Desbois [mailto:[EMAIL PROTECTED] ]
Sent: Tuesday, May 29, 2007 5:02 AM
To: mysql@lists.mysql.com
Subject: datetime type conversion problem
I am having issues with type conversion not working as expected per the
documentation.
I am using in MySQL 5.0.27 for x86/Windows.
The documentation at
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html states that
for
comparison operators If one of the arguments is a TIMESTAMP or
DATETIMEcolumn and the other argument is a constant, the constant is
converted to a
timestamp before the comparison is performed.
So the following SQL statement should convert the last string to a
timestamp
then perform date-time comparison:
SELECT '2007-05-24 00:00:00' BETWEEN
('2007-3-23' - INTERVAL 24 HOUR) AND '2007-3-23 23:59:59';
Thus it should return 0 as 24th May is not in the given range. I get the
result 1 however...
If I perform an explicit conversion using CAST('2007-3-23 23:59:59' AS
DATETIME) I get 0 as expected.
Can anyone enlighten me - have I misunderstood something somewhere?
Thanks,
--rob
--
Rob Desbois
Eml: [EMAIL PROTECTED]
Tel: 01452 760631
Mob: 07946 705987
There's a whale there's a whale there's a whale fish he cried, and the
whale was in full view.
...Then ooh welcome. Ahhh. Ooh mug welcome.
--
Rob Desbois
Eml: [EMAIL PROTECTED]
Tel: 01452 760631
Mob: 07946 705987
There's a whale there's a whale there's a whale fish he cried, and the
whale was in full view.
...Then ooh welcome. Ahhh. Ooh mug welcome.
Re: string to timestamp conversion
Like:
str_to_date('Thu May 17 09:15:47 2007','%a %b %e %T %Y')
On Mon, May 21, 2007 21:10, [EMAIL PROTECTED] wrote:
Have you considered using the string to time function?
Sent via BlackBerry from T-Mobile
-Original Message-
From: Bryan Cantwell [EMAIL PROTECTED]
Date: Mon, 21 May 2007 12:08:11
To:MySQL General mysql@lists.mysql.com
Subject: string to timestamp conversion
I have a table with a varchar column that contains a timestamp like
this: 'Thu May 17 09:15:47 2007'
I need to grab this and include it in an insert sql that puts that value
in a table as a timestamp...
--
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]
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
string to timestamp conversion
I have a table with a varchar column that contains a timestamp like this: 'Thu May 17 09:15:47 2007' I need to grab this and include it in an insert sql that puts that value in a table as a timestamp... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: string to timestamp conversion
Have you considered using the string to time function? Sent via BlackBerry from T-Mobile -Original Message- From: Bryan Cantwell [EMAIL PROTECTED] Date: Mon, 21 May 2007 12:08:11 To:MySQL General mysql@lists.mysql.com Subject: string to timestamp conversion I have a table with a varchar column that contains a timestamp like this: 'Thu May 17 09:15:47 2007' I need to grab this and include it in an insert sql that puts that value in a table as a timestamp... -- 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: string to timestamp conversion
I see no string to date function that does this conversion... -Original Message- From: Bryan Cantwell Sent: Monday, May 21, 2007 2:08 PM To: MySQL General Subject: string to timestamp conversion I have a table with a varchar column that contains a timestamp like this: 'Thu May 17 09:15:47 2007' I need to grab this and include it in an insert sql that puts that value in a table as a timestamp... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: string to timestamp conversion
Take a look at the following: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#func tion_unix-timestamp That looks like exactly what you need... thnx, Chris -Original Message- From: Bryan Cantwell [mailto:[EMAIL PROTECTED] Sent: Monday, May 21, 2007 4:45 PM To: MySQL General Subject: RE: string to timestamp conversion I see no string to date function that does this conversion... -Original Message- From: Bryan Cantwell Sent: Monday, May 21, 2007 2:08 PM To: MySQL General Subject: string to timestamp conversion I have a table with a varchar column that contains a timestamp like this: 'Thu May 17 09:15:47 2007' I need to grab this and include it in an insert sql that puts that value in a table as a timestamp... -- 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]
Character Set Conversion Scenarios / Detection
Hi, We are deploying a software upgrade and need to convert the MySQL database of all installations from their previous character set (default) to UTF-8. I have read the instructions at http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html and the discussion at http://bugs.mysql.com/bug.php?id=22719. According to the documentation, there are 3 different scenarios: Case A: old tables from MySQL 4.0 or earlier where a non-binary column contains values that actually are encoded in a character set different from the server's default character set. - Upgrade / conversion path: 1. convert VARCHAR to VARBINARY 2. convert VARBINRARY to VARCHAR UTF8 Case B: same but starting with MySQL 4.1 or later - Upgrade path: 1. convert VARCHAR to VARCHAR UTF8 directly Case C: starting with case A but there have been INSERTs/UPDATEs after the MySQL upgrade to 4.1 - Upgrade path: what to do here to minimize data loss? We tried to store UTF8 in the database even in pre MySQL 4.1 databases. What we Did until now is assume case A. But this leads to string truncation in case B (see the above mentioned bug report) and that's why we need to improve the upgrade path. Questions: 1. How can we detect the 3 different cases? We don't know the history of the specific installations since all we do is offer the software. The software should auto-detect the scenario and run the appropriate upgrade code. 2. Do you have any advice for case C? Thanks in advance, - Andy Staudacher, Gallery.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM to InnoDB conversion help
Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY `accession_number` (`accession_number`), KEY `fofkdx` (`form_fkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Procedure used to execute conversion: root 27686 0.0 0.2 5840 3224 ?S 14:08:23 0:00 mysql -pxx xxx -e insert into DAILY_EDGAR_INNODB select * from DAILY_EDGAR my.cnf InnoDB section: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /export/home/mysqldata/ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /export/home/mysqldata/ibdata innodb_log_arch_dir = /export/home/mysqldata/ibdata # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 50M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Best, Mikhail Berman
Re: MyISAM to InnoDB conversion help
If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY `accession_number` (`accession_number`), KEY `fofkdx` (`form_fkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Procedure used to execute conversion: root 27686 0.0 0.2 5840 3224 ?S 14:08:23 0:00 mysql -pxx xxx -e insert into DAILY_EDGAR_INNODB select * from DAILY_EDGAR my.cnf InnoDB section: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /export/home/mysqldata/ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /export/home/mysqldata/ibdata innodb_log_arch_dir = /export/home/mysqldata/ibdata # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 50M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Best, Mikhail Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB conversion help
Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY `accession_number` (`accession_number`), KEY `fofkdx` (`form_fkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Procedure used to execute conversion: root 27686 0.0 0.2 5840 3224 ?S 14
Re: MyISAM to InnoDB conversion help
Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx
Re: MyISAM to InnoDB conversion help
I just noticed your innodb_data_file_path You have a shared InnoDB tablespace That can be murder on a MySQL Server You may want to separate each InnoDB into a separate file Here are the steps needed to separate InnoDB tables. 1) Do a mysqldump on your database to mydata.sql. 2) Shutdown MySQL 3) Goto my.cnf and add 'innodb_file_per_table' in the [mysqld] section 4) Change in my.cnf : innodb_data_file_path to ibdata1:10M:autoextend Note: You may want add this too : bulk_insert_buffer_size = 256M 5) Delete ibdata1, ibdata2, and the ib_logfile* files 6) Restart MySQL (the innodb data files and log will regenerate) 7) Run MySQL using the script mydata.sql All InnoDB data will be sitting in separate .ibd files in the database folder. Only the data dictionary info for all InnoDB tables will be sitting in the ibdata1 file. Give it a try. - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Mikhail Berman [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:24:00 AM GMT-0500 US/Eastern Subject: Re: MyISAM to InnoDB conversion help Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables
RE: MyISAM to InnoDB conversion help
Great, Thank you for your help Rolando, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:41 AM Cc: mysql@lists.mysql.com; Mikhail Berman Subject: Re: MyISAM to InnoDB conversion help I just noticed your innodb_data_file_path You have a shared InnoDB tablespace That can be murder on a MySQL Server You may want to separate each InnoDB into a separate file Here are the steps needed to separate InnoDB tables. 1) Do a mysqldump on your database to mydata.sql. 2) Shutdown MySQL 3) Goto my.cnf and add 'innodb_file_per_table' in the [mysqld] section 4) Change in my.cnf : innodb_data_file_path to ibdata1:10M:autoextend Note: You may want add this too : bulk_insert_buffer_size = 256M 5) Delete ibdata1, ibdata2, and the ib_logfile* files 6) Restart MySQL (the innodb data files and log will regenerate) 7) Run MySQL using the script mydata.sql All InnoDB data will be sitting in separate .ibd files in the database folder. Only the data dictionary info for all InnoDB tables will be sitting in the ibdata1 file. Give it a try. - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Mikhail Berman [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:24:00 AM GMT-0500 US/Eastern Subject: Re: MyISAM to InnoDB conversion help Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected
Re: Data truncated during character set conversion
On Mon, Jul 03, 2006 at 01:55:26PM +0400, Timur Izhbulatov wrote: I'm following the instructions [1] to convert character set. Unfortunately I get warnings about truncated data for some rows in several columns. All the truncated columns are text type. Assuming the `col' column is text type and actually contains correct utf8 data but has wrong character set I use the following queries to fix it: ALTER TABLE table MODIFY col BLOB; ALTER TABLE table MODIFY col TEXT CHARACTER SET utf8; After the second query I get warnings about truncated data for some rows and I can actually see the rows truncated. I played a bit with different data types (LONGBLOB/LONGTEXT) but wasn't successful. So my question is what can be the cause of the data loss and how to avoid it? After some additional investigations I found the cause. It was some exotic non-ASCII characters like '–' (long dash). At the same time Russian letters don't cause any problems. Seems the problem arises because the table itself also has wrong default character set (latin1). Setting default character set to utf8 with ALTER TABLE doesn't solve the problem. Bug if I create a new table with utf8 as default character set convertion works fine. What's happening? Please see the testcase attached. My character set settings are: SHOW VARIABLES LIKE '%char%'; +--+--+ | Variable_name| Value| +--+--+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--+--+ Cheers, -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL dump 10.9 -- -- Host: localhostDatabase: tizhbulatov_aquarium -- -- -- Server version 4.1.19 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stories` -- DROP TABLE IF EXISTS `test_table`; CREATE TABLE `test_table` ( `overview` text ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ALTER TABLE `test_table` DEFAULT CHARACTER SET utf8; INSERT INTO `test_table` SET `overview` = '–'; SELECT * FROM `test_table`; ALTER TABLE `test_table` MODIFY `overview` BLOB; ALTER TABLE `test_table` MODIFY `overview` text CHARACTER SET utf8; SHOW WARNINGS; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data truncated during character set conversion
On Tue, Jul 04, 2006 at 01:55:30PM +0400, Timur Izhbulatov wrote: After some additional investigations I found the cause. It was some exotic non-ASCII characters like '–' (long dash). At the same time Russian letters don't cause any problems. Sorry, I was wrong concerning Russian letters. Acutally *any* non-ASCII character causes the problem. The problem seems to be limited only to TEXT fields, non-ASCII characters (including Russian) in CHAR/VARCHAR fields are OK. -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data truncated during character set conversion
Hi all, I'm following the instructions [1] to convert character set. Unfortunately I get warnings about truncated data for some rows in several columns. All the truncated columns are text type. Assuming the `col' column is text type and actually contains correct utf8 data but has wrong character set I use the following queries to fix it: ALTER TABLE table MODIFY col BLOB; ALTER TABLE table MODIFY col TEXT CHARACTER SET utf8; After the second query I get warnings about truncated data for some rows and I can actually see the rows truncated. I played a bit with different data types (LONGBLOB/LONGTEXT) but wasn't successful. So my question is what can be the cause of the data loss and how to avoid it? Thanks in advance. [1] http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html Cheers, -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
XML - DB Conversion
Hi all, I'm currently looking for a tool that will take XML and produce a database from it. Not really looking for anything in particular at this point, just sort of scoping around. Anyone know of such a tool? -- Chris White PHP Programmer/DB Sloth Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: XML - DB Conversion
[snip] I'm currently looking for a tool that will take XML and produce a database from it. Not really looking for anything in particular at this point, just sort of scoping around. Anyone know of such a tool? [/snip] MySQL kinda' has a built-in tool http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UTF8 conversion
Having done my upgrade from version 3 to 5, I'm looking forward to the benefits of language support beyond the confines of Western Europe. However, it seems I need to convert the database I have now. We have material in the database at the moment in English, Dutch, Italian, Portuguese and Spanish. All the pages and PHP scripts are in UTF8, and I've been using the PHP function utf8_decode() before recording strings in the db because MySQL 3 doesn't support UFT8. Similarly I've been using utf8_encode() after reading them and outputting them to the web. It's become clear (from removing utf8_encode() from a script that reads the data) that simply setting the table's collation to utf8_general_ci isn't enough, and that the data itself is not utf8 encoded. I've looked in the docs but can't find a procedure to follow to convert my existing data to utf8. Can anyone point me to the right page? Or is the only way simply to knock up a utility script in php to read it all out, encode it, then write it back? Easy enough to do, but a bit of a pain! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I used to think I was indecisive, but now I'm not so sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk conversion of 3.23 latin1 to 4.1 utf-8
I also had problems converting latin1 to utf-8. For whatever reason, accents and stuff did not convert. The solution was not to convert to utf-8. If you create your tables with the character set of latin1, you should be fine (that's what we did). After all, you are already upgrading -- why change the table type while you're at it? You want to make sure any problems you encounter are upgrade-related, not related to the changing of the table type. if you're not getting that in your mysqldump, use the --create-options flag to mysqldump to get it. (make sure you're not running --compatible=[something not mysql]). Hope this helps! -Sheeri On 2/18/06, David Spidley [EMAIL PROTECTED] wrote: -Original Message- From: [EMAIL PROTECTED] Sent: Sat, 18 Feb 2006 00:12:19 +0100 To: [EMAIL PROTECTED] Subject: Re: Bulk conversion of 3.23 latin1 to 4.1 utf-8 On 17.02.2006 15:34 (+0100), David Spidley wrote: What is the official way to take this latin1 3.23 dump and load it into the brand new 4.1 utf-8 database, with the characters being converted during the load? I'm not sure if the official way will help you. I've tried to copy a 4.0 database (latin1) to 5.0 (utf-8) and couldn't make it work with MySQL Administrator's tools. phpMyAdmin made a good impression though, and it got a lot faster since couple of years ago! You could export the database as latin1 and re-import it as utf-8 or so. Just give it a try... Great! How can I do this so that it works? Everything I have tried has failed. (I would like to stick to command line stuff if possible.) -- 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: Bulk conversion of 3.23 latin1 to 4.1 utf-8
Thank you for your reply, but I'm afraid it doesn't help :( The new setup we are moving to is utf-8 only (Apache, mysql). Making some of the data latin1 and some of it utf-8 will complicate things, we want to move away from different character encodings, which is why we are using unicode in the new setup. -Original Message- From: [EMAIL PROTECTED] Sent: Mon, 20 Feb 2006 13:01:20 -0500 To: [EMAIL PROTECTED] Subject: Re: Bulk conversion of 3.23 latin1 to 4.1 utf-8 I also had problems converting latin1 to utf-8. For whatever reason, accents and stuff did not convert. The solution was not to convert to utf-8. If you create your tables with the character set of latin1, you should be fine (that's what we did). After all, you are already upgrading -- why change the table type while you're at it? You want to make sure any problems you encounter are upgrade-related, not related to the changing of the table type. if you're not getting that in your mysqldump, use the --create-options flag to mysqldump to get it. (make sure you're not running --compatible=[something not mysql]). Hope this helps! -Sheeri On 2/18/06, David Spidley [EMAIL PROTECTED] wrote: -Original Message- From: [EMAIL PROTECTED] Sent: Sat, 18 Feb 2006 00:12:19 +0100 To: [EMAIL PROTECTED] Subject: Re: Bulk conversion of 3.23 latin1 to 4.1 utf-8 On 17.02.2006 15:34 (+0100), David Spidley wrote: What is the official way to take this latin1 3.23 dump and load it into the brand new 4.1 utf-8 database, with the characters being converted during the load? I'm not sure if the official way will help you. I've tried to copy a 4.0 database (latin1) to 5.0 (utf-8) and couldn't make it work with MySQL Administrator's tools. phpMyAdmin made a good impression though, and it got a lot faster since couple of years ago! You could export the database as latin1 and re-import it as utf-8 or so. Just give it a try Great! How can I do this so that it works? Everything I have tried has failed. (I would like to stick to command line stuff if possible.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk conversion of 3.23 latin1 to 4.1 utf-8
-Original Message- From: [EMAIL PROTECTED] Sent: Sat, 18 Feb 2006 00:12:19 +0100 To: [EMAIL PROTECTED] Subject: Re: Bulk conversion of 3.23 latin1 to 4.1 utf-8 On 17.02.2006 15:34 (+0100), David Spidley wrote: What is the official way to take this latin1 3.23 dump and load it into the brand new 4.1 utf-8 database, with the characters being converted during the load? I'm not sure if the official way will help you. I've tried to copy a 4.0 database (latin1) to 5.0 (utf-8) and couldn't make it work with MySQL Administrator's tools. phpMyAdmin made a good impression though, and it got a lot faster since couple of years ago! You could export the database as latin1 and re-import it as utf-8 or so. Just give it a try... Great! How can I do this so that it works? Everything I have tried has failed. (I would like to stick to command line stuff if possible.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bulk conversion of 3.23 latin1 to 4.1 utf-8
Hello, I have a few hundred databases, with lots of tables and data, German and English characters. I'd like to upgrade to 4.1, but am concerned at losing or corrupting data in the process. What is the official way to take this latin1 3.23 dump and load it into the brand new 4.1 utf-8 database, with the characters being converted during the load? Thanks in advance! David Spidley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk conversion of 3.23 latin1 to 4.1 utf-8
On 17.02.2006 15:34 (+0100), David Spidley wrote: What is the official way to take this latin1 3.23 dump and load it into the brand new 4.1 utf-8 database, with the characters being converted during the load? I'm not sure if the official way will help you. I've tried to copy a 4.0 database (latin1) to 5.0 (utf-8) and couldn't make it work with MySQL Administrator's tools. phpMyAdmin made a good impression though, and it got a lot faster since couple of years ago! You could export the database as latin1 and re-import it as utf-8 or so. Just give it a try... -- 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]
latin1 -utf8 conversion
Hi list, I guess this is a classic problem...! I found that on the web: http://www.oreillynet.com/pub/wlg/9022?wlg=yes, where basically the guy did dump data, change the charset in the table definition and reinsert the records into an utf8 database and ended up with some problems... I saw somewhere in the mysql doc (unable to find the link back though) that converting between charsets can be tricky, especially if you're not sure of what you actually have. Which is my problem: the tables are latin1, but some people may have executed queries from the command line (utf8) and inserted data (which are then utf8 right?), some may have used an utf8 phpmyadmin (producing utf8 data?) and some a old isolatin one.(which would then give latin1 data?) I think the majority of the data are latin1, but there may be some utf8 at some places. I have mostly basic characters, and a few names with accents. I saw somewhere that you can convert to binary before to be sure of keeping things right. From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) I strongly suspect that I'm kind of intolerant to encodings and how to manage them, I just don't get it. Does anybody knows what is the best way to do? Would a dump be enough? Does the dump itself need to be utf8 encoded to be loaded properly? Do I need to load it through an utf8 interface? I have an old iso latin shell, an utf8 one, and 2 versions of phpmyadmin, one latin1 and one utf8: does it matter where I will load the dump from? Any help more than welcome! Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: latin1 -utf8 conversion
Hello. Start from reading this part of the manual: http://dev.mysql.com/doc/refman/5.0/en/charset.html From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) Database does do conversion. If fields in a table have latin1 character set, all characters that are not present in latin1, will be converted most probably to '?'. utf8 can held all symbols which are in latin1, and in my opinion there shouldn't be any problems. mel list_php wrote: Hi list, I guess this is a classic problem...! I found that on the web: http://www.oreillynet.com/pub/wlg/9022?wlg=yes, where basically the guy did dump data, change the charset in the table definition and reinsert the records into an utf8 database and ended up with some problems... I saw somewhere in the mysql doc (unable to find the link back though) that converting between charsets can be tricky, especially if you're not sure of what you actually have. Which is my problem: the tables are latin1, but some people may have executed queries from the command line (utf8) and inserted data (which are then utf8 right?), some may have used an utf8 phpmyadmin (producing utf8 data?) and some a old isolatin one.(which would then give latin1 data?) I think the majority of the data are latin1, but there may be some utf8 at some places. I have mostly basic characters, and a few names with accents. I saw somewhere that you can convert to binary before to be sure of keeping things right. From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) I strongly suspect that I'm kind of intolerant to encodings and how to manage them, I just don't get it. Does anybody knows what is the best way to do? Would a dump be enough? Does the dump itself need to be utf8 encoded to be loaded properly? Do I need to load it through an utf8 interface? I have an old iso latin shell, an utf8 one, and 2 versions of phpmyadmin, one latin1 and one utf8: does it matter where I will load the dump from? Any help more than welcome! Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- 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]
Type conversion from Mysql to Oracle
Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type conversion from Mysql to Oracle
Hi, I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? What types do you need to convert? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type conversion from Mysql to Oracle
Xiaobo Chen [EMAIL PROTECTED] wrote on 01/31/2006 09:45:00 AM: Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada Here are links to nearly everything there is to know about MySQL data types: http://dev.mysql.com/doc/refman/4.1/en/data-types.html http://dev.mysql.com/doc/refman/5.0/en/data-types.html Since I know next to nothing about Oracle data types, I am unqualified to help you to draw any comparisons between the system you are migrating from and whichever version of MySQL you may be migrating into. If you had been more specific about the versions you are using, the techniques you are using to migrate the data, the errors you are having and the results you expected, you could have received a much more detailed answer. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Type conversion from Mysql to Oracle
Hello. In case you want to automate the process of migration have a look here: http://dev.mysql.com/doc/migration-toolkit/en/index.html There is a specific forum as well: http://forums.mysql.com/list.php?61 Xiaobo Chen wrote: Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- 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: Type conversion from Mysql to Oracle
Hi, folks
First thank all you who replied to my question. I should be more specific
with my question.
In mysql database table, there is some thing like in the 'create table
syntax':
fieldname enum('yes','no') default null
and
fieldname datetime default null
I have no idea about the first one. For the second, I am not familiar with
the 'DATE' type in Oracle and its formatting. In the Mysql table, the
format is like '-MM-DD HH:MN:SS'.
What I am doing is trying to duplicate all those tables from Mysql
database into the Oracle database and I am trying to do that by writing a
small java program. The issue I am facing now is that I need to get
correctly the 'create table syntax' for Oracle and trying to keep the
formats of each column as close as possible to that in the Mysql table.
I couldn't find a FREE tool to do this from Mysql to Oracle (not the other
direction). So I decided to write it up.
Thanks again.
Xiaobo
Hi,
I am trying to convert some Mysql database into Oracle. But their types
are not exactly match. Could any one give a hint?
What types do you need to convert?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type conversion from Mysql to Oracle
Hi,
First thank all you who replied to my question. I should be more specific
with my question.
In mysql database table, there is some thing like in the 'create table
syntax':
fieldname enum('yes','no') default null
and
fieldname datetime default null
I have no idea about the first one.
ENUM is a MySQL specific type. I suggest you use a single
char in Oracle: CHAR(1) CHECK (VALUE IN ('Y', 'N') )
or a 3-char, value in 'YES', 'NO'.
For the second, I am not familiar with
the 'DATE' type in Oracle and its formatting. In the Mysql table, the
format is like '-MM-DD HH:MN:SS'.
Formatting is - largely - a client side issue.
What I am doing is trying to duplicate all those tables from Mysql
database into the Oracle database and I am trying to do that by writing a
small java program. The issue I am facing now is that I need to get
correctly the 'create table syntax' for Oracle and trying to keep the
formats of each column as close as possible to that in the Mysql table.
I couldn't find a FREE tool to do this from Mysql to Oracle (not the other
direction). So I decided to write it up.
Well, our database developer IDE Database Workbench can do
a lot of this, for you - www.upscene.com - but it doesn't come for
free.
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure Conversion
I am converting a MS SQL Server ASP application over to use MySQL. I have two simple stored procedures that I need to convert. I have very little experience with MS SQL stored procedures, and none-what-so-ever with stored procedures in MySQL, so I really don't know what this should look like. I'll post the first one, and if I'm able to figure it out, I'll attempt my second one by myself. Here's the stored procedure converted as much as I can get it. CREATE Procedure sp_InsertNewCamper ( in cFirstName NVarChar(30), in cLastName NVarChar(30), in cUserName NVarChar(30), in cPassword NVarChar(30) ) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Statement Conversion
I'm converting an application from SQL Server to MySQL. I have the following query that I need to convert: SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END FROM Activities a ORDER BY Activity What is the proper syntax for this in MySQL? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Statement Conversion
If your MySQL server is a *nix system than table names are case sensitive. SELECT A.*, CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS CO FROM Activities A ORDER BY Activity I also just noticed, remove the CO = and add AS CO following the END of the case statement. -Original Message- From: Jesse Castleberry [mailto:[EMAIL PROTECTED] Sent: Friday, November 04, 2005 3:54 PM To: MySQL List Subject: SQL Statement Conversion I'm converting an application from SQL Server to MySQL. I have the following query that I need to convert: SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END FROM Activities a ORDER BY Activity What is the proper syntax for this in MySQL? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Conversion
Jesse, VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. A stored procedure cannot return a value. PB - Jesse Castleberry wrote: I am converting a MS SQL Server ASP application over to use MySQL. I have two simple stored procedures that I need to convert. I have very little experience with MS SQL stored procedures, and none-what-so-ever with stored procedures in MySQL, so I really don't know what this should look like. I'll post the first one, and if I'm able to figure it out, I'll attempt my second one by myself. Here's the stored procedure converted as much as I can get it. CREATE Procedure sp_InsertNewCamper ( in cFirstName NVarChar(30), in cLastName NVarChar(30), in cUserName NVarChar(30), in cPassword NVarChar(30) ) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. Thanks, Jesse -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Datatype conversion
hi all, i have a strange problem here. I have around 600 tables in Oracle DB(On 8i, so i am not able to use mysql migration tool kit!!). Now i want move all of these to mysql. I am initially trying to convert the schema from oracle to mysql. My problem is, in the existing oracle DB, for storing both integers as well as decimal values i am using the same dataype 'number'. But during migration i want to distinguish if that field 'number' in oracle is actually holding an integer or decimals. Is this possible to do or i have to manually do it only. Thank you sujay
Re: Datatype conversion
Sujay Koduri [EMAIL PROTECTED] wrote on 10/17/2005 06:25:58 AM: hi all, i have a strange problem here. I have around 600 tables in Oracle DB(On 8i, so i am not able to use mysql migration tool kit!!). Now i want move all of these to mysql. I am initially trying to convert the schema from oracle to mysql. My problem is, in the existing oracle DB, for storing both integers as well as decimal values i am using the same dataype 'number'. But during migration i want to distinguish if that field 'number' in oracle is actually holding an integer or decimals. Is this possible to do or i have to manually do it only. Thank you sujay Unless you can somehow extract some additional information about your fields, you will not be able to automate such a conversion. How were you planning to make the decision by hand? You could probably write something to dump your Oracle schema as a MySQL-ready script complete with the numeric data type conversions (based on how you wanted to decide which columns were integers and which ones were not). Then it becomes a simple matter of playing that script through the CLI (use the source command) to actually create your destination tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Datatype conversion
yes..exactly thats what i am planning to do now. Thank you sujay _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 17, 2005 7:35 PM To: Sujay Koduri Cc: mysql@lists.mysql.com Subject: Re: Datatype conversion Sujay Koduri [EMAIL PROTECTED] wrote on 10/17/2005 06:25:58 AM: hi all, i have a strange problem here. I have around 600 tables in Oracle DB(On 8i, so i am not able to use mysql migration tool kit!!). Now i want move all of these to mysql. I am initially trying to convert the schema from oracle to mysql. My problem is, in the existing oracle DB, for storing both integers as well as decimal values i am using the same dataype 'number'. But during migration i want to distinguish if that field 'number' in oracle is actually holding an integer or decimals. Is this possible to do or i have to manually do it only. Thank you sujay Unless you can somehow extract some additional information about your fields, you will not be able to automate such a conversion. How were you planning to make the decision by hand? You could probably write something to dump your Oracle schema as a MySQL-ready script complete with the numeric data type conversions (based on how you wanted to decide which columns were integers and which ones were not). Then it becomes a simple matter of playing that script through the CLI (use the source command) to actually create your destination tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
cast and numeric conversion
Hi, I've got two columns, one is 'integer' (and store negative value), the other one 'tinyint unsigned' (only 1 stored in it), when I multiply these columns, quantity with negative value does not show up. (I expect -50*10 to give : -500, but the field stay blank.) I've look a bit at the documentation, but can't find anything on type promotion. Where can I find this ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cast and numeric conversion
I've got two columns, one is 'integer' (and store negative value), the other one 'tinyint unsigned' (only 1 stored in it), when I multiply these columns, quantity with negative value does not show up. (I expect -50*10 to give : -500, but the field stay blank.) I've look a bit at the documentation, but can't find anything on type promotion. Where can I find this ? http://dev.mysql.com/doc/mysql/en/cast-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cast and numeric conversion
Scott Noyes wrote: I've got two columns, one is 'integer' (and store negative value), the other one 'tinyint unsigned' (only 1 stored in it), when I multiply these columns, quantity with negative value does not show up. (I expect -50*10 to give : -500, but the field stay blank.) I've look a bit at the documentation, but can't find anything on type promotion. Where can I find this ? http://dev.mysql.com/doc/mysql/en/cast-functions.html thank you ;) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
free MySQL conversion to MSSQL tool
Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free MySQL conversion to MSSQL tool
Am Thursday 28 July 2005 11:19 schrieb [EMAIL PROTECTED]: Does anyone know a free tool to convert MySQL to MSSQL mysqldump? Seriously, what do you expect? A tool which transforms mysql files on disk to mssql files? I don't think this exists and would probably be hardly possible. Perhaps there are tools reading from a (mysql) db and writing to another (mssql) database via a programming language. But conversion of database files? I don't think this exists. Stefan Thank you Andrew -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free MySQL conversion to MSSQL tool
So all I can do is copy tables as they already exist? I obviously need to keep the structure in place Andrew Am Thursday 28 July 2005 11:19 schrieb [EMAIL PROTECTED]: Does anyone know a free tool to convert MySQL to MSSQL mysqldump? Seriously, what do you expect? A tool which transforms mysql files on disk to mssql files? I don't think this exists and would probably be hardly possible. Perhaps there are tools reading from a (mysql) db and writing to another (mssql) database via a programming language. But conversion of database files? I don't think this exists. Stefan Thank you Andrew -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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: free MySQL conversion to MSSQL tool
[EMAIL PROTECTED] wrote: Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew phpMyAdmin (http://www.phpmyadmin.net/) version 2.6.3-pl1 has the option to export the data in mssql format (and much others). You need a php enabled web server able to connect to your mysql database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: free MySQL conversion to MSSQL tool
Although finding a tool that will automatically transfer files from MySQL to MS SQL format will be hard to do, both will accept txt files that have the CREATE statements and data in SQL. mysqldump will do this for you. http://dev.mysql.com/doc/mysql/en/mysqldump.html Just export the files to an .SQL file and load it into MS SQL. So your command will be something like: shell /path/to/mysql/bin/mysqldump --opt -u [username] -p [password] File_Name.SQL If you want a tool to do it for you, try dbTools software (http://www.dbtools.com.br). It lets you copy tables from one database to another, is PHP/ASP/JSP independent (as it is 3rd party), and has other data management tools. However, you must have a Windows box in order to use it. I do not believe they make a Linux version. HTH, J.R. -Original Message- From: Bastian Balthazar Bux [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 6:13 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: free MySQL conversion to MSSQL tool [EMAIL PROTECTED] wrote: Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew phpMyAdmin (http://www.phpmyadmin.net/) version 2.6.3-pl1 has the option to export the data in mssql format (and much others). You need a php enabled web server able to connect to your mysql database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: avoiding conversion during insert?
Jacek Becla [EMAIL PROTECTED] wrote on 07/21/2005 02:47:20 PM: Hi, Is there a way to insert binary data representing numbers directly into (preferably MyISAM) table. We are trying to avoid conversion from ASCII to float/double/int... Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] It depends, are you dealing with floating point or integer values? For floating point (real) values, I think you are stuck going through ASCII to get the data into MySQL. For integer values, what are you worried about? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
avoiding conversion during insert?
Hi, Is there a way to insert binary data representing numbers directly into (preferably MyISAM) table. We are trying to avoid conversion from ASCII to float/double/int... Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle to MySQl conversion
Good evening all, I've got an Oracle text dump from a client for conversion to MySQL. Before I dive into SED or Perl does anyone know of a script to convert the dump file? I did the dev site by hand, not much to it, the site is fairly simple. Mostly NUMBER to INT, VARCHAR2 to VARCHAR, and CLOB to BLOB. Just don't want to duplicate the effort. Thanks, DAve -- Dave Goodrich Systems Administrator http://www.tls.net Get rid of Unwanted Emails...get TLS Spam Blocker! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Premature InnoDB conversion.
A, - Original Message - From: A Z [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, February 01, 2005 2:14 PM Subject: Premature InnoDB conversion. MySql 4.0.14 We tried to convert MyISAM table format to INNODB format, it took forever to finish the process, someone intervened and killed the process through Task Manager. Now can't run Mysqld-nt, running it with the --console reports the followings. Your help is appreciated. Microsoft Windows [Version 5.2.3790] (C) Copyright 1985-2003 Microsoft Corp. c:\MySql\mysqld-nt --console 050201 11:26:22 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 799702164 050201 11:26:22 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7 3 74 75 76 77 78 050201 11:26:24 InnoDB: Assertion failure in thread 1344 in fi le ../innobase/include\page0page.ic line 482 InnoDB: Failing assertion: offs UNIV_PAGE_SIZE InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com the tablespace is corrupt. If you do not have valuable data in ibdata files, follow the advice at http://dev.mysql.com/doc/mysql/en/error-creating-innodb.html, and recreate the whole InnoDB installation. Tablespace corruption on Windows is rare. It could be an unknown InnoDB bug, an OS bug, or a hardware fault. New MySQL versions have better diagnostics. An upgrade to 4.0.23 would be good. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Premature InnoDB conversion.
MySql 4.0.14 We tried to convert MyISAM table format to INNODB format, it took forever to finish the process, someone intervened and killed the process through Task Manager. Now can't run Mysqld-nt, running it with the --console reports the followings. Your help is appreciated. Microsoft Windows [Version 5.2.3790] (C) Copyright 1985-2003 Microsoft Corp. c:\MySql\mysqld-nt --console 050201 11:26:22 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 799702164 050201 11:26:22 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7 3 74 75 76 77 78 050201 11:26:24 InnoDB: Assertion failure in thread 1344 in fi le ../innobase/include\page0page.ic line 482 InnoDB: Failing assertion: offs UNIV_PAGE_SIZE InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Conversion on Mysql 4.0.2
Hi, Can some help me with a date conversion problme that I am having. I have a date data in the following format: 'Month Days Year Hour:Minute AM/PM' Example: 'Aug 21, 2004 2:00 PM' So far the hours that I have spent have been in vain. I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? TIA. Minh = Minh La __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion on Mysql 4.0.2
Minh La wrote: So far the hours that I have spent have been in vain. Next time a couple of minutes with the Fine Manual instead? :-) I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? Quoting the FM: STR_TO_DATE() is available as of MySQL 4.1.1. FWIW, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion on Mysql 4.0.2
Minh La wrote: Hi, Can some help me with a date conversion problme that I am having. I have a date data in the following format: 'Month Days Year Hour:Minute AM/PM' Example: 'Aug 21, 2004 2:00 PM' So far the hours that I have spent have been in vain. I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? Minh: Your options are: * upgrade to 4.1 * parse and convert the date in your applications * use an ugly combination of SUBSTRING() and CASE to parse out the date * write a UDF implementing STR_TO_DATE() * backport STR_TO_DATE() to 4.0 -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data conversion question
Thanks Konrad, Yves On Thu, 23 Dec 2004 23:28:31 +0100, Konrad Kieling [EMAIL PROTECTED] wrote: Does MySQL have commands that would allow me to convert Base64 data to Binary and then convert that Binary to a string format? have a look at the attached file (hope the attachment did not get stripped). it contains some udf-functions for base64 en/de-coding. a little description is included. ciao, konrad -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data conversion question
Hello, I was looking throught the manual a bit, I haven't found the answer I'm looking for... Does MySQL have commands that would allow me to convert Base64 data to Binary and then convert that Binary to a string format? Thanks, -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data conversion question
Does MySQL have commands that would allow me to convert Base64 data to Binary and then convert that Binary to a string format? have a look at the attached file (hope the attachment did not get stripped). it contains some udf-functions for base64 en/de-coding. a little description is included. ciao, konrad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double conversion error
Hello. Update works fine on my 4.1.7-debug-log MySQL instance on Linux. May be you should switch to the latest release. Berry, Brett C [EMAIL PROTECTED] wrote: Hello, The create table statement is as follows: CREATE TABLE `call` ( `call_id` int(10) unsigned NOT NULL auto_increment, `init_lband` int(10) unsigned NOT NULL default '0', `chan_assign_sec` tinyint(3) unsigned default NULL, `adn_time_sec` tinyint(3) unsigned default NULL, `setup` char(3) NOT NULL default '---', `setup_sec` tinyint(3) unsigned default NULL, `completion` char(3) NOT NULL default '---', `duration_sec` int(10) unsigned default NULL, `ab_term` char(3) default '---', `session_id` int(10) unsigned NOT NULL default '0', `lgc_x` int(11) default NULL, `lgc_y` int(11) default NULL, `lgc_z` int(11) default NULL, PRIMARY KEY (`call_id`), KEY `FK_session_id` (`session_id`), CONSTRAINT `call_ibfk_4` FOREIGN KEY (`session_id`) REFERENCES = `call_session` (`session_id`) ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 COMMENT=3D'InnoDB free: 4096 = kB' The row I'm trying to match on is an auto incremented row in an InnoDB = table. The SQL Query is as follows: update call set CHAN_ASSIGN_SEC =3D 3, ADN_TIME_SEC =3D 4, SETUP_SEC =3D = 7, DURATION_SEC =3D 45, AB_TERM =3D 'N', COMPLETION =3D 'Y', SETUP =3D = 'Y' where (CALL_ID =3D 2.37000e+002); There is indeed a row with call_id=3D237. Regards, -Brett Berry --- Hello. Can you send complete test for your problem (i.e SHOW CREATE TABLE on = your tables, buggy sql statement...)? On my 4.1.7 instance of MySQL = everything=20 looks fine: mysql desc v1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | v | int(11) | YES | | NULL| | +---+-+--+-+-+---+ select * from v1; +--+ | v| +--+ | 237 | +--+ mysql select * from v1 where v=3D2.37000e+002; +--+ | v| +--+ | 237 | +--+ update v1 set v=3D11 where v=3D2.37000e+002; select * from v1; +--+ | v| +--+ | 11 | +--+ I have a query where I perform an update where (CALL_ID =3D 2.37000e+002); This query updates nothing, even though my CALL_ID column has an id = of 237. If I change the end of this query to read: where (CALL_ID =3D 237);, = then the row with CALL_ID=3D237 is updated. Is there a reason why the double value 2.37000e+002 is = not evaluating to 237? Regards, -Brett Berry -- 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: Double conversion error
Hello, The create table statement is as follows: CREATE TABLE `call` ( `call_id` int(10) unsigned NOT NULL auto_increment, `init_lband` int(10) unsigned NOT NULL default '0', `chan_assign_sec` tinyint(3) unsigned default NULL, `adn_time_sec` tinyint(3) unsigned default NULL, `setup` char(3) NOT NULL default '---', `setup_sec` tinyint(3) unsigned default NULL, `completion` char(3) NOT NULL default '---', `duration_sec` int(10) unsigned default NULL, `ab_term` char(3) default '---', `session_id` int(10) unsigned NOT NULL default '0', `lgc_x` int(11) default NULL, `lgc_y` int(11) default NULL, `lgc_z` int(11) default NULL, PRIMARY KEY (`call_id`), KEY `FK_session_id` (`session_id`), CONSTRAINT `call_ibfk_4` FOREIGN KEY (`session_id`) REFERENCES `call_session` (`session_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB' The row I'm trying to match on is an auto incremented row in an InnoDB table. The SQL Query is as follows: update call set CHAN_ASSIGN_SEC = 3, ADN_TIME_SEC = 4, SETUP_SEC = 7, DURATION_SEC = 45, AB_TERM = 'N', COMPLETION = 'Y', SETUP = 'Y' where (CALL_ID = 2.37000e+002); There is indeed a row with call_id=237. Regards, -Brett Berry --- Hello. Can you send complete test for your problem (i.e SHOW CREATE TABLE on your tables, buggy sql statement...)? On my 4.1.7 instance of MySQL everything looks fine: mysql desc v1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | v | int(11) | YES | | NULL| | +---+-+--+-+-+---+ select * from v1; +--+ | v| +--+ | 237 | +--+ mysql select * from v1 where v=2.37000e+002; +--+ | v| +--+ | 237 | +--+ update v1 set v=11 where v=2.37000e+002; select * from v1; +--+ | v| +--+ | 11 | +--+ I have a query where I perform an update where (CALL_ID = 2.37000e+002); This query updates nothing, even though my CALL_ID column has an id of 237. If I change the end of this query to read: where (CALL_ID = 237);, then the row with CALL_ID=237 is updated. Is there a reason why the double value 2.37000e+002 is not evaluating to 237? Regards, -Brett Berry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double conversion error
Hello. Can you send complete test for your problem (i.e SHOW CREATE TABLE on your tables, buggy sql statement...)? On my 4.1.7 instance of MySQL everything looks fine: mysql desc v1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | v | int(11) | YES | | NULL| | +---+-+--+-+-+---+ select * from v1; +--+ | v| +--+ | 237 | +--+ mysql select * from v1 where v=2.37000e+002; +--+ | v| +--+ | 237 | +--+ update v1 set v=11 where v=2.37000e+002; select * from v1; +--+ | v| +--+ | 11 | +--+ I have a query where I perform an update where (CALL_ID = 2.37000e+002); This query updates nothing, even though my CALL_ID column has an id of 237. If I change the end of this query to read: where (CALL_ID = 237);, then the row with CALL_ID=237 is updated. Is there a reason why the double value 2.37000e+002 is not evaluating to 237? Regards, -Brett Berry Berry, Brett C [EMAIL PROTECTED] 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]
Double conversion error
Title: Double conversion error I have a query where I perform an update where (CALL_ID = 2.37000e+002); This query updates nothing, even though my CALL_ID column has an id of 237. If I change the end of this query to read: where (CALL_ID = 237);, then the row with CALL_ID=237 is updated. Is there a reason why the double value 2.37000e+002 is not evaluating to 237? Regards, -Brett Berry Boeing Software Tools Engineer
Re: table conversion problems
Sergei Skarupo wrote: Hi Donny, Thanks for your reply. This table only uses ints and floats, but the floats are allowed to be null, which means, as far as I understand, that it's not a fixed row length... What makes you think that? VARCHAR, TEXT, and BLOB are the variable-length column types http://dev.mysql.com/doc/mysql/en/Storage_requirements.html. By the way, Paul DuBois writes about the 4G limit in MySQL, second edition, and does not mention that fixed rows make a difference as far as the size limit is concerned. The 4Gb limit for MyISAM tables is a matter of pointer size. It is easily overcome with the AVG_ROW_LENGTH and MAX_ROWS options. Practically, MyISAM tables can be as large as your OS/filesystem can handle. This is documented in the manual http://dev.mysql.com/doc/mysql/en/Table_size.html, which nakes no mention, that I can see, of variable-length rows changing the situation. In fact, AVG_ROW_LENGTH would be pointless if only tables with fixed-length rows need apply. Could you please point me to a page about copying data in chunks on InnoDB website? http://dev.mysql.com/doc/mysql/en/Converting_tables_to_InnoDB.html In any case, it's kind of too late now -- my alter command is already running :) True. -- Sergei Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table conversion problems
Hi all, Started a conversion from MyISAM to InnoDB; it's been almost two days and the statement is still executing... The (MyISAM) data table size is almost 4G. There were two reasons for this conversion: to start supporting transactions and to avoid the 4G limit of MyISAM tables; this table has been created without explicitly specifying MAX_ROWS and AVG_ROW_LENGTH. The avg row length is 28 bytes, there's only a primary key comprised of 3 integers. The state of this thread that's performing the conversion is Copy to tmp table. We need to start updating the table as soon as possible... Is there a way to monitor the progress? Is the tmp table allocated in InnoDB tablespace? What are the consequences of killing the thread? Will it waste whatever InnoDB tablespace has been already used for this conversion? I'm using Mysqlcc. How long may it take to cancel this statement by pushing Cancel execution and clear results button? Sometimes it takes a while... What does this button actually do? Thanks in advance for your help! -- Sergei
RE: table conversion problems
Sergei, I don't know much about innodb, but myisam doesn't have a 4 gig limit unless you are using a dynamic type of table. If you are using a fixed table which is by using int, char, etc... Not text, varchar, blobs. As long as you don't use the last ones, you don't have a 4 gig limit. As far as your questions about innodb, can't help you there. Except for in my case when I have switched tables to innodb, I copy them in chunks to speed up the process. That's what it says on the innodb website, so that's what we did. Donny -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 8:36 PM To: Mysql List (E-mail) Subject: table conversion problems Hi all, Started a conversion from MyISAM to InnoDB; it's been almost two days and the statement is still executing... The (MyISAM) data table size is almost 4G. There were two reasons for this conversion: to start supporting transactions and to avoid the 4G limit of MyISAM tables; this table has been created without explicitly specifying MAX_ROWS and AVG_ROW_LENGTH. The avg row length is 28 bytes, there's only a primary key comprised of 3 integers. The state of this thread that's performing the conversion is Copy to tmp table. We need to start updating the table as soon as possible... Is there a way to monitor the progress? Is the tmp table allocated in InnoDB tablespace? What are the consequences of killing the thread? Will it waste whatever InnoDB tablespace has been already used for this conversion? I'm using Mysqlcc. How long may it take to cancel this statement by pushing Cancel execution and clear results button? Sometimes it takes a while... What does this button actually do? Thanks in advance for your help! -- Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table conversion problems
Hi Donny, Thanks for your reply. This table only uses ints and floats, but the floats are allowed to be null, which means, as far as I understand, that it's not a fixed row length... By the way, Paul DuBois writes about the 4G limit in MySQL, second edition, and does not mention that fixed rows make a difference as far as the size limit is concerned. Could you please point me to a page about copying data in chunks on InnoDB website? In any case, it's kind of too late now -- my alter command is already running :) -- Sergei -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 6:37 PM To: Sergei Skarupo; 'Mysql List (E-mail)' Subject: RE: table conversion problems Sergei, I don't know much about innodb, but myisam doesn't have a 4 gig limit unless you are using a dynamic type of table. If you are using a fixed table which is by using int, char, etc... Not text, varchar, blobs. As long as you don't use the last ones, you don't have a 4 gig limit. As far as your questions about innodb, can't help you there. Except for in my case when I have switched tables to innodb, I copy them in chunks to speed up the process. That's what it says on the innodb website, so that's what we did. Donny -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 8:36 PM To: Mysql List (E-mail) Subject: table conversion problems Hi all, Started a conversion from MyISAM to InnoDB; it's been almost two days and the statement is still executing... The (MyISAM) data table size is almost 4G. There were two reasons for this conversion: to start supporting transactions and to avoid the 4G limit of MyISAM tables; this table has been created without explicitly specifying MAX_ROWS and AVG_ROW_LENGTH. The avg row length is 28 bytes, there's only a primary key comprised of 3 integers. The state of this thread that's performing the conversion is Copy to tmp table. We need to start updating the table as soon as possible... Is there a way to monitor the progress? Is the tmp table allocated in InnoDB tablespace? What are the consequences of killing the thread? Will it waste whatever InnoDB tablespace has been already used for this conversion? I'm using Mysqlcc. How long may it take to cancel this statement by pushing Cancel execution and clear results button? Sometimes it takes a while... What does this button actually do? Thanks in advance for your help! -- Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date Conversion Function
Well thanks to everyone that replied to this message, here are the main details. I was looking for this as help for a DBA here that is enslaved by MSSQL and was asking me what the equivalent would be, as I do not know what mm() does in mssql myself I cannot answer that question either, I sent her the link for the MySQL page that has all of the date/time functions listed and told her to figure it out. So anyway thanks a million, Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 29, 2004 12:27 PM To: Christopher L. Hood; '[EMAIL PROTECTED] ' Subject: RE: Date Conversion Function I do not know of an MM() date function in MS SQL, only mm used for the date part. What are you attempting to accomplish? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 7/29/04 11:17 AM Subject: Date Conversion Function M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Conversion Function
M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion Function
Not being an MSSQL expert, I don't know what MM does, and since you did not specify what type of conversions you wish to perform, I can't say exactly which function would suit your needs, but here is a link to the manual page that lists all date and time functions -- http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html -- Original message from [EMAIL PROTECTED]: -- M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date Conversion Function
I do not know of an MM() date function in MS SQL, only mm used for the date part. What are you attempting to accomplish? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 7/29/04 11:17 AM Subject: Date Conversion Function M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- 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]
Display of ? and Hex conversion
All: We discovered a rather odd situation where some space characters where being displayed as ?. In tracking this down, it was determined that the server had stored the hex value A0 rather than 20 by using this query: select LocationId, LevelId, Hex(NameLn),NameLn from Location where LevelId = 3 order by NameLn; So, I issued this statement to fix it (using 4.0.16) update Location set NameLn = replace(hex(NameLn), 'A0' , '20') where hex(NameLn) like '%A0%' Now for the NameLn field I have the hex string (arrg my data has been hexed!!! :-) So, my questions are: 1. How do I go back from the hex string to characters? 2. Has anyone else seen this problem? At this point I can say the diplay issue only appears on some browsers. Thanks Boyd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display of ? and Hex conversion
On Fri, Jul 02, 2004 at 01:52:06PM -0500, Boyd E. Hemphill wrote:
We discovered a rather odd situation where some space characters where
being displayed as ?.
In tracking this down, it was determined that the server had stored the
hex value A0 rather than 20 by using this query:
'A0' is the code for a non-breaking space, assuming you're using the
iso-8859-1 (or related) character encoding.
update Location
set NameLn = replace(hex(NameLn), 'A0' , '20')
where hex(NameLn) like '%A0%'
Better would have been:
UPDATE Location
SET NameLn = REPLACE(NameLn, CHAR(0xA0), ' ')
WHERE NameLn LIKE CONCAT('%',CHAR(0xA0),'%')
Now for the NameLn field I have the hex string (arrg my data has
been hexed!!! :-)
So, my questions are:
1. How do I go back from the hex string to characters?
You can use the UNHEX() function.
2. Has anyone else seen this problem? At this point I can say the
diplay issue only appears on some browsers.
The likely culprit for this sort of thing, in my experience, is users
using cut-and-paste from an application like Microsoft Word into their
browser. That often introduces similar issues with characters like
curly-quotes and em-dashes.
Jim Winstead
MySQL AB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GMT-DST time conversion in (My)SQL ?
Hello, i have a table that contains datetime entries which are stored in GMT. Now I would like to convert these datetime values in a select statement to local time. I first thought of someting like this (in germany DST starts at 2004-03-29 02:00:00 and ends 2004-10-31 03:00:00 this year. The difference from GMT to local time is 1h and from GMT to local DST time 2h): SELECT if(datetime between 2004-03-29 02:00:00 AND 2004-10-31 03:00:00, DATE_ADD(datetime, INTERVAl 2 HOUR), DATE_ADD(datetime, INTERVAL 1 HOUR) ) FROM table ... ... This basically works but of course only if the select statement return rows with datetime entries in the year 2004. Eg if there are rows with a datetime field of 2003-xx-xx then the statement from above will fail. Is there any more general way to do such a timezone conversion or is the only solution to let the application to this work that reads the MYSQL output? Thanks Rainer -- --- Rainer Krienke, Universitaet Koblenz, Rechenzentrum, Raum A022 Universitaetsstrasse 1, 56070 Koblenz, Tel: +49 261287 -1312, Fax: -1001312 Mail: [EMAIL PROTECTED], Web: http://www.uni-koblenz.de/~krienke Get my public PGP key: http://www.uni-koblenz.de/~krienke/mypgp.html --- pgp0.pgp Description: signature
Mysql 4.0 -Oracle Stored Procedure Trigger Conversion
Hi Everybody, Is there any way we can tranform the Stores procedure and Triggers present under Oracle Custom Application to mysql. Please tell ways or any tools which can help us rewrite the logic in Mysql. Thanks and regards, Piyush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.0 -Oracle Stored Procedure Trigger Conversion
Garg, Piyush (EM, GECIS) wrote: Hi Everybody, Is there any way we can tranform the Stores procedure and Triggers present under Oracle Custom Application to mysql. Please tell ways or any tools which can help us rewrite the logic in Mysql. Thanks and regards, Piyush Stored Procedures are only supported in version 5.0.x and above ( which is currnetly a development release ). Triggers will be longer still. You can get trigger-like functionality by creating functions in your application to manage the updating of your DB, and always using those functions instead of talking directly to the DB server. This is more flexible, but slower. Anyway, it's the only option currently. Dan -- signature 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]
InnoDB Conversion
Hi. I have a query that has been running for 10.7 hours. It is converting a 3.6GB MyISAM to Innodb. 38448 | copy to tmp table | alter table `MESSAGES` type=innodb The innodb data file has increased 8.6GB in size since the command started. How do I tell how far through the conversion the process is? Can I stop the process (without incurring a huge rollback penalty) and resume it in a more efficient manner (e.g. with different my.cnf startup options)? Regards, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL SQL Conversion
Logan McKinley [EMAIL PROTECTED] wrote: I am using MySQL (4.0.17) and am getting errors with the following SQL statement originally writen for MS products. SELECT survey_questions.Question_ID, First(survey_questions.Question_Text) AS FirstOfQuestion_Text, == this line returns errors i believe it is the AS Avg(survey_responses.Response) AS AvgOfResponse, Count(survey_responses.Response) AS CountOfResponse, survey_questions.survey_num FROM survey_questions INNER JOIN survey_responses ON survey_questions.Question_ID = survey_responses.QuestionID GROUP BY survey_questions.Question_ID, survey_questions.survey_num HAVING (((Avg(survey_responses.Response))-1) AND ((survey_questions.survey_num)=0)); There is no FIRST() function in MySQL. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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 SQL Conversion
I am using MySQL (4.0.17) and am getting errors with the following SQL statement originally writen for MS products. SELECT survey_questions.Question_ID, First(survey_questions.Question_Text) AS FirstOfQuestion_Text, == this line returns errors i believe it is the AS Avg(survey_responses.Response) AS AvgOfResponse, Count(survey_responses.Response) AS CountOfResponse, survey_questions.survey_num FROM survey_questions INNER JOIN survey_responses ON survey_questions.Question_ID = survey_responses.QuestionID GROUP BY survey_questions.Question_ID, survey_questions.survey_num HAVING (((Avg(survey_responses.Response))-1) AND ((survey_questions.survey_num)=0)); Thanks in advance, ~Logan
Stability of MyISAM-Ststis vs. Dynamic - (was: Automatic conversion from `char` TO `varchar`)
As to the discussion about char vs. varchar fields, I am now into a problem which is near by the above one: We are about to design a new database with high importance to stability and crash recovery. So I found, that two types of MyISAM tables exist (static and dynamic). If you have only fixed length fields, the table will be static. But when you add just one field of variable length, the table will be dynamic. Therefore, I tried to move any i.e. varchar type fields to char, but the type of the table does not change when I am using the ALTER statement. Right now, I only can do this conversion by dumping data and structure, removing the table, changing the fields and then inserting structure and data again. * Is there another way to do this ? * Has anybody ever had to rebuild a corrupted table, and is rebuiling a static table really easier for software than rebuilding a dynamic one? Greetinx, Chris SZM Studios * Dipl.Ing. Christian Merten * Ingenieur Broadcast-Support Oberwallstraße 6 * 10117 Berlin Tel. [030] 2090-3167 * Fax [030] 2090-3092 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.szm-studios.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stability of MyISAM-Ststis vs. Dynamic - (was: Automatic conversion from `char` TO `varchar`)
Hi, If all your fields have a fixed length, you can change the type of the table by doing : ALTER TABLE your_table ROW_FORMAT=fixed; Regards, Jocelyn - Original Message - From: Merten Christian [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, February 09, 2004 10:01 AM Subject: Stability of MyISAM-Ststis vs. Dynamic - (was: Automatic conversion from `char` TO `varchar`) As to the discussion about char vs. varchar fields, I am now into a problem which is near by the above one: We are about to design a new database with high importance to stability and crash recovery. So I found, that two types of MyISAM tables exist (static and dynamic). If you have only fixed length fields, the table will be static. But when you add just one field of variable length, the table will be dynamic. Therefore, I tried to move any i.e. varchar type fields to char, but the type of the table does not change when I am using the ALTER statement. Right now, I only can do this conversion by dumping data and structure, removing the table, changing the fields and then inserting structure and data again. * Is there another way to do this ? * Has anybody ever had to rebuild a corrupted table, and is rebuiling a static table really easier for software than rebuilding a dynamic one? Greetinx, Chris SZM Studios * Dipl.Ing. Christian Merten * Ingenieur Broadcast-Support Oberwallstraße 6 * 10117 Berlin Tel. [030] 2090-3167 * Fax [030] 2090-3092 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.szm-studios.de -- 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: Automatic conversion from `char` TO `varchar`
OK, this will be my last post on this topic. Mainly I want to point out that I was taking the wrong conclusions from the results of my tests. I followed the advice of Michael Stassen and ran some more tests using int columns for joins and got pretty much the same trend. So I guess the real answer is that joins between static tables are faster than joins between dynamic tables (by about 20% for the int column join). I guess this is what the MySQL manual is talking about (and other people who posted the same thing to this thread). Matt Fagan [EMAIL PROTECTED] http://greetings.yahoo.com.au - Yahoo! Greetings Send your love online with Yahoo! Greetings - FREE! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatic conversion from `char` TO `varchar`
I'm having the same problem. I did a performance test, and CHAR columns are significantly faster than VARCHAR (at least on my platform - MySQL 4.1.1a on Win32). I setup a webpage with my sample code (VB) so that you can run the test yourself: http://au.geocities.com/m_fagan/VARCHARvsCHAR.html The results were that table join on CHAR-CHAR was about 15% faster than join on VARCHAR-VARCHAR. Does anyone know how to stop the auto-conversion of CHAR to VARCHAR ? - Original Message - From: Hassan Shaikh [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 7:06 AM Subject: Automatic conversion from `char` TO `varchar` Hi, It's really strange but when I execute the following statement, all my char(10) columns turn into varchar(10). My other tables are ok and I've tried create dummy table also. Problem seems to be associated with this table only. ... Matt Fagan http://greetings.yahoo.com.au - Yahoo! Greetings Send your love online with Yahoo! Greetings - FREE! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatic conversion from `char` TO `varchar`
As I understand it, fixed-length *rows* are faster than variable-length rows, as mysql knows exactly where each fixed-length row starts in the file. Once your table has any variable-length column, however, you have variable-length rows. In that case, the smaller the rows the better, speedwise. So, once your table has a variable-length column, mysql changes the CHARs to VARCHARS to save space and improve efficiency. See http://www.mysql.com/doc/en/Silent_column_changes.html for more. ... After writing the above, I went and looked at your test results. Interesting. I imagine you've already read about silent column changes. I have a couple questions about your test. - I notice that in tables test2 and test4, the CHAR and VARCHAR columns are indexed, but they are not indexed in tables test1 and test3. This means that each of your LEFT JOINS has an index on the right, but not on the left. Was that on purpose? Usually, you'd want an index on both sides of the join condition. I don't really expect that to make any difference here, though, as you are selecting more than 30% of the rows (all of them, in fact). - Have you tried the equivalent test joining on the int columns? That would control for any difference between fixed/variable length rows, as oppposed to the relative efficiency of CHAR vs. VARCHAR. Michael Matt Fagan wrote: I'm having the same problem. I did a performance test, and CHAR columns are significantly faster than VARCHAR (at least on my platform - MySQL 4.1.1a on Win32). I setup a webpage with my sample code (VB) so that you can run the test yourself: http://au.geocities.com/m_fagan/VARCHARvsCHAR.html The results were that table join on CHAR-CHAR was about 15% faster than join on VARCHAR-VARCHAR. Does anyone know how to stop the auto-conversion of CHAR to VARCHAR ? - Original Message - From: Hassan Shaikh [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 7:06 AM Subject: Automatic conversion from `char` TO `varchar` Hi, It's really strange but when I execute the following statement, all my char(10) columns turn into varchar(10). My other tables are ok and I've tried create dummy table also. Problem seems to be associated with this table only. ... Matt Fagan http://greetings.yahoo.com.au - Yahoo! Greetings Send your love online with Yahoo! Greetings - FREE! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatic conversion from `char` TO `varchar`
Thanks for reply Micahel, Basically, my primary concern is speed (or actually, cpu loads). I don't care about space - I have a fairly small database but its going to be linked to a webserver that will be getting a lot of hits. I'm trying to squeeze as much performance out of the DB as I can so that I don't need to spend more money on hardware for the website. I started writing the whole thing using Access as the DB, and was going to move it to an SQL server when things got closer to release on the website. So the whole thing is structured using the Access ReplicationID datatype (fixed 16 byte binary data) for primary keys on almost all of my tables. It would take a fair bit of work now to go through the ISAPI and change everything to use autoincrement integer fields (and besides, there are a few other good features of using GUIDs for primary keys that I don't want to give up unless I have to). I didn't index the columns in the table on the left side of the join because I didn't think it mattered for LEFT JOINs. I'll add an index and see what happens. However, in the meantime I ran some more tests. The original post was on MyISAM tables (I chose these because I thought they would be faster - at least that's the impression I got from the MySQL documentation). However, when I changed to InnoDB tables, times for all of the SELECTs more than halved, and the difference between the CHAR and VARCHAR columns dropped significantly (down to about 2% difference). So I guess I will just go with InnoDB tables and not worry too much about the autoconversion. Anyway, I'll do some more tests using the information you've given me (i.e. what happens for SELECTSs of less than 30% of table, index on left side of join, and speed comparison to using integers as primary keys). If anyone's interested in the results, I'll put them up on a webpage (http://au.geocities.com/m_fagan/VARCHARvsCHAR.html) in a couple of days (or whenever I have the time). --- Michael Stassen [EMAIL PROTECTED] wrote: As I understand it, fixed-length *rows* are faster than variable-length rows, as mysql knows exactly where each fixed-length row starts in the file. Once your table has any variable-length column, however, you have variable-length rows. In that case, the smaller the rows the better, speedwise. So, once your table has a variable-length column, mysql changes the CHARs to VARCHARS to save space and improve efficiency. See http://www.mysql.com/doc/en/Silent_column_changes.html for more. ... After writing the above, I went and looked at your test results. Interesting. I imagine you've already read about silent column changes. I have a couple questions about your test. - I notice that in tables test2 and test4, the CHAR and VARCHAR columns are indexed, but they are not indexed in tables test1 and test3. This means that each of your LEFT JOINS has an index on the right, but not on the left. Was that on purpose? Usually, you'd want an index on both sides of the join condition. I don't really expect that to make any difference here, though, as you are selecting more than 30% of the rows (all of them, in fact). - Have you tried the equivalent test joining on the int columns? That would control for any difference between fixed/variable length rows, as oppposed to the relative efficiency of CHAR vs. VARCHAR. Michael Matt Fagan wrote: I'm having the same problem. I did a performance test, and CHAR columns are significantly faster than VARCHAR (at least on my platform - MySQL 4.1.1a on Win32). I setup a webpage with my sample code (VB) so that you can run the test yourself: http://au.geocities.com/m_fagan/VARCHARvsCHAR.html The results were that table join on CHAR-CHAR was about 15% faster than join on VARCHAR-VARCHAR. Does anyone know how to stop the auto-conversion of CHAR to VARCHAR ? - Original Message - From: Hassan Shaikh [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 7:06 AM Subject: Automatic conversion from `char` TO `varchar` Hi, It's really strange but when I execute the following statement, all my char(10) columns turn into varchar(10). My other tables are ok and I've tried create dummy table also. Problem seems to be associated with this table only. ... Matt Fagan http://greetings.yahoo.com.au - Yahoo! Greetings Send your love online with Yahoo! Greetings - FREE! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LONGBLOB datatype conversion to text
Does anyone know of a function to employ when retrieving a LONGBLOB column - Im creating an ad hoc query and would like to see the data in text format.
