Re: MS SQL to MySQL select
Hey, In order for me to achive the goal of migrating a MS SQL based application to web based php / mysql I need to understand a little bit MS SQL syntax and MySQL counterpart expression. If is not too much to ask one of you guys could translate this MS SQL select to MySQL? Here's the challenge: Yes, that is too much. This is a very basic query, if you cannot translate it yourself, I wish you good luck on your conversion project. Is there anything -specific- that you want to have translated here? Would it help if I said that this query prefixes each object with its owner (dbo) and for MySQL you should remove that? eg: create view equipos ... select ... OBJETOSPATRIMONIO.objpatCodigo ... from `01 LOCALIZACIONES OBJETOSPATRIM` INNER JOIN ... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com CREATE VIEW dbo.equipos AS SELECT TOP 100 PERCENT dbo.OBJETOSPATRIMONIO.objpatCodigo AS Expr1, dbo.OBJETOSPATRIMONIO.objpatDenom AS Expr2, dbo.OBJETOSPATRIMONIO.objpatEstadoTec AS Expr3, dbo.ESTRUCTURAPATRIMONIO.clsnodopatID, dbo.[01 LOCALIZACIONES OBJETOSPATRIM].Localizacion FROM dbo.[01 LOCALIZACIONES OBJETOSPATRIM] INNER JOIN dbo.OBJETOSPATRIMONIO ON dbo.[01 LOCALIZACIONES OBJETOSPATRIM].objpatID = dbo.OBJETOSPATRIMONIO.objpatID INNER JOIN dbo.OBJETOSPATRIMEQUIPOS ON dbo.OBJETOSPATRIMONIO.objpatID = dbo.OBJETOSPATRIMEQUIPOS.equipID INNER JOIN dbo.ESTRUCTURAPATRIMONIO ON dbo.OBJETOSPATRIMONIO.objpatNodoEnlazID = dbo.ESTRUCTURAPATRIMONIO.nodopatrimID ORDER BY dbo.OBJETOSPATRIMONIO.objpatCodigo Really appreciate your help! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS SQL to MySQL select
Thanks Martijn! That I understood, sorry I wasn't clear enough, I was more confused about the TOP 100 PERCENT and this was a bad example anyways, but after posting I did some deeper research and found an excellent migration white paper describing this type of expressions and mysql counterparts. Tomorrow I'll post the url as I don't have it right now on my laptop, I saved the link on my desktop. Anyways I thank you for your comments. :handshake: Martijn Tonies wrote: Hey, In order for me to achive the goal of migrating a MS SQL based application to web based php / mysql I need to understand a little bit MS SQL syntax and MySQL counterpart expression. If is not too much to ask one of you guys could translate this MS SQL select to MySQL? Here's the challenge: Yes, that is too much. This is a very basic query, if you cannot translate it yourself, I wish you good luck on your conversion project. Is there anything -specific- that you want to have translated here? Would it help if I said that this query prefixes each object with its owner (dbo) and for MySQL you should remove that? eg: create view equipos ... select ... OBJETOSPATRIMONIO.objpatCodigo ... from `01 LOCALIZACIONES OBJETOSPATRIM` INNER JOIN ... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com CREATE VIEW dbo.equipos AS SELECT TOP 100 PERCENT dbo.OBJETOSPATRIMONIO.objpatCodigo AS Expr1, dbo.OBJETOSPATRIMONIO.objpatDenom AS Expr2, dbo.OBJETOSPATRIMONIO.objpatEstadoTec AS Expr3, dbo.ESTRUCTURAPATRIMONIO.clsnodopatID, dbo.[01 LOCALIZACIONES OBJETOSPATRIM].Localizacion FROM dbo.[01 LOCALIZACIONES OBJETOSPATRIM] INNER JOIN dbo.OBJETOSPATRIMONIO ON dbo.[01 LOCALIZACIONES OBJETOSPATRIM].objpatID = dbo.OBJETOSPATRIMONIO.objpatID INNER JOIN dbo.OBJETOSPATRIMEQUIPOS ON dbo.OBJETOSPATRIMONIO.objpatID = dbo.OBJETOSPATRIMEQUIPOS.equipID INNER JOIN dbo.ESTRUCTURAPATRIMONIO ON dbo.OBJETOSPATRIMONIO.objpatNodoEnlazID = dbo.ESTRUCTURAPATRIMONIO.nodopatrimID ORDER BY dbo.OBJETOSPATRIMONIO.objpatCodigo Really appreciate your help! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/MS-SQL-to-MySQL-select-tp17846568p17859366.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Getting crashed, Any Help Comments?
How large is your total memory? There parameter seems too large. On Sun, Feb 10, 2008 at 5:42 PM, VeeJay [EMAIL PROTECTED] wrote: Hello I am running a Freebsd server: 1. Software: Apache 1.3 mysql 5.0.27 php 4.4 2. Hardware: 2 intel procerssors 4 gb ram RAID 10 with hard drives 15K rpm I am having this problem quite often now. Apache stops responding due to mysql (my guess). And I cannot connect to mysql server: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space' There is following configuration for mysql: # The following options will be passed to all MySQL clients [client] port= 3306 socket = /tmp/mysql.sock default-character-set=utf8 # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-networking skip-name-resolve server-id=1 max_connections=1000 key_buffer = 1024M max_allowed_packet = 16M table_cache = 512K sort_buffer_size = 4M net_buffer_length = 64K read_buffer_size = 4M read_rnd_buffer_size = 4M join_buffer_size=4M myisam_sort_buffer_size = 128M # increase until threads_created doesnt grow anymore thread_cache=512 query_cache_type=1 query_cache_limit=2M query_cache_size=64M # Try number of CPU's*2 for thread_concurrency thread_concurrency=4 set-variable=local-infile=0 init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' default-character-set=utf8 character-set-server=utf8 collation-server=utf8_unicode_ci ft_min_word_len=2 ft_max_word_len=15 log-bin=mysql-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set=utf8 [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout ANY COMMENTS WILL BE WELCOMED, thanks!!! -- Thanks! BR / vj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Server Getting crashed, Any Help Comments?
How large is your total memory? There parameters' value seem too large. On Mon, Jun 16, 2008 at 4:49 PM, Moon's Father [EMAIL PROTECTED] wrote: How large is your total memory? There parameter seems too large. On Sun, Feb 10, 2008 at 5:42 PM, VeeJay [EMAIL PROTECTED] wrote: Hello I am running a Freebsd server: 1. Software: Apache 1.3 mysql 5.0.27 php 4.4 2. Hardware: 2 intel procerssors 4 gb ram RAID 10 with hard drives 15K rpm I am having this problem quite often now. Apache stops responding due to mysql (my guess). And I cannot connect to mysql server: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space' There is following configuration for mysql: # The following options will be passed to all MySQL clients [client] port= 3306 socket = /tmp/mysql.sock default-character-set=utf8 # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-networking skip-name-resolve server-id=1 max_connections=1000 key_buffer = 1024M max_allowed_packet = 16M table_cache = 512K sort_buffer_size = 4M net_buffer_length = 64K read_buffer_size = 4M read_rnd_buffer_size = 4M join_buffer_size=4M myisam_sort_buffer_size = 128M # increase until threads_created doesnt grow anymore thread_cache=512 query_cache_type=1 query_cache_limit=2M query_cache_size=64M # Try number of CPU's*2 for thread_concurrency thread_concurrency=4 set-variable=local-infile=0 init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' default-character-set=utf8 character-set-server=utf8 collation-server=utf8_unicode_ci ft_min_word_len=2 ft_max_word_len=15 log-bin=mysql-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set=utf8 [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout ANY COMMENTS WILL BE WELCOMED, thanks!!! -- Thanks! BR / vj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: enable and disable keys
Hi Krishna, Can u please give me example of csplit. regards anandkl On 6/16/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: try csplit to break the files into small chunks On Fri, Jun 13, 2008 at 12:21 PM, Ananda Kumar [EMAIL PROTECTED] wrote: How do i split file during ENABLE KEYS On 6/13/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: increase bulk_insert_buffer_size and if possible split the bigger file into small chunks. It will help you. On Thu, Jun 12, 2008 at 1:51 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, We are doing load data into a table using LOAD DATA INFILE process. Below is the method we are following. 1. create empty table with all the indexes. 2. disable keys 3. Load data using LOAD DATA INFILE, close to 99 Million records which takes around 3 hrs 4 . Enable keys Table size is around 19.5 gb There is one primary key, 2 non unique indexes and one FULLTEXT INDEX. Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. please let me know, this problem is causing lot of dealy for other process to run. regards anandkl -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Re: enable and disable keys
man csplit
try
csplit filename 10 {10}
On Mon, Jun 16, 2008 at 2:22 PM, Ananda Kumar [EMAIL PROTECTED] wrote:
Hi Krishna,
Can u please give me example of csplit.
regards
anandkl
On 6/16/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote:
try csplit to break the files into small chunks
On Fri, Jun 13, 2008 at 12:21 PM, Ananda Kumar [EMAIL PROTECTED] wrote:
How do i split file during ENABLE KEYS
On 6/13/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote:
increase bulk_insert_buffer_size and if possible split the bigger file
into small chunks. It will help you.
On Thu, Jun 12, 2008 at 1:51 PM, Ananda Kumar [EMAIL PROTECTED]
wrote:
Hi All,
We are doing load data into a table using LOAD DATA INFILE process.
Below is
the method we are following.
1. create empty table with all the indexes.
2. disable keys
3. Load data using LOAD DATA INFILE, close to 99 Million records which
takes
around 3 hrs
4 . Enable keys
Table size is around 19.5 gb
There is one primary key, 2 non unique indexes and one FULLTEXT INDEX.
Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then
switches to REPAIR BY KEYCACHE and writes a log in the error log
file Warning:
Enabling keys got errno 28, retrying
What could be the problem.
System has 8 cpu and 16GB RAM
I have set myisam_max_sort_file_size=98GB
myisam_sort_buffer_size=750MB.
/tmp folder has 16GB free space.
please let me know, this problem is causing lot of dealy for other
process
to run.
regards
anandkl
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]
Re: Server Getting crashed, Any Help Comments?
trying reducing session related parameters like sort_buffer_size,read_buffer_size , read_rnd_buffer_size to 1MB On 6/16/08, Moon's Father [EMAIL PROTECTED] wrote: How large is your total memory? There parameters' value seem too large. On Mon, Jun 16, 2008 at 4:49 PM, Moon's Father [EMAIL PROTECTED] wrote: How large is your total memory? There parameter seems too large. On Sun, Feb 10, 2008 at 5:42 PM, VeeJay [EMAIL PROTECTED] wrote: Hello I am running a Freebsd server: 1. Software: Apache 1.3 mysql 5.0.27 php 4.4 2. Hardware: 2 intel procerssors 4 gb ram RAID 10 with hard drives 15K rpm I am having this problem quite often now. Apache stops responding due to mysql (my guess). And I cannot connect to mysql server: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space' There is following configuration for mysql: # The following options will be passed to all MySQL clients [client] port= 3306 socket = /tmp/mysql.sock default-character-set=utf8 # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-networking skip-name-resolve server-id=1 max_connections=1000 key_buffer = 1024M max_allowed_packet = 16M table_cache = 512K sort_buffer_size = 4M net_buffer_length = 64K read_buffer_size = 4M read_rnd_buffer_size = 4M join_buffer_size=4M myisam_sort_buffer_size = 128M # increase until threads_created doesnt grow anymore thread_cache=512 query_cache_type=1 query_cache_limit=2M query_cache_size=64M # Try number of CPU's*2 for thread_concurrency thread_concurrency=4 set-variable=local-infile=0 init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' default-character-set=utf8 character-set-server=utf8 collation-server=utf8_unicode_ci ft_min_word_len=2 ft_max_word_len=15 log-bin=mysql-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set=utf8 [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout ANY COMMENTS WILL BE WELCOMED, thanks!!! -- Thanks! BR / vj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
RE: RE: what is the proper way to store timezone information?
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2008 6:23 AM To: mysql@lists.mysql.com Subject: RE: what is the proper way to store timezone information? Boyd, Todd M. wrote: When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. My concern is whether the time_zone_id is a fixed reference of the timezone. If the id might (for whatever reason) change in the future, I'd have to store the timezone name. http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html You can convert back and forth using the system's time zone table. Read the MySQL manual I've linked to above for more information. Apparently, you can even reference them by offset from UTC (i.e., -6:00 for US Central). The article warns against using the time zone's text description, but I saw nothing about dangers of offsets or time_zone_id. Todd Boyd Web Programmer
MySQL University session on June 19: Checking Performance with Kchachegrind
Hi, this Thursday, Stewart Smith will give a MySQL University session: http://forge.mysql.com/wiki/Checking_Performance_with_Kchachegrind (topic: Checking Performance with Kchachegrind) Please register for this session by filling in your name on the session Wiki page. Registering is not required but appreciated. That Wiki page also contains a section to post questions. Please use it! MySQL University sessions normally start at 13:00 UTC (summer) or 14:00 UTC (winter); see: http://forge.mysql.com/wiki/MySQL_University for more time zone information. Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. Next MySQL University sessions before the summer semester break: June 26, 2008: Patch Management With Quilt (Stewart Smith) July 3, 2008: Advanced LUA Scripting (Giuseppe Maxia) See http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the complete list. -Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.demailto: [EMAIL PROTECTED] Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.25-rc has been released
Dear MySQL users, We are proud to present to you the MySQL Server 5.1.25-rc release, a new release candidate version of the popular open source database. Bear in mind that this is still a candidate release, and as with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, we would like to direct your attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.25-rc release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes from version to version in the MySQL source code since the latest released version of MySQL 5.1, the MySQL 5.1.24-rc release. It can also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-25.html Sincerely, Timothy Smith The MySQL build team at Sun Microsystems News from the ChangeLog: Functionality added or changed: * Incompatible Change: A change has been made to the way that the server handles prepared statements. This affects prepared statements processed at the SQL level (using the PREPARE statement) and those processed using the binary client-server protocol (using the mysql_stmt_prepare() C API function). Previously, changes to metadata of tables or views referred to in a prepared statement could cause a server crash when the statement was next executed, or perhaps an error at execute time with a crash occurring later. For example, this could happen after dropping a table and recreating it with a different definition. Now metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Repreparation also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to FLUSH TABLES. Repreparation is automatic, but to the extent that it occurs, performance of prepared statements is diminished. Table content changes (for example, with INSERT or UPDATE) do not cause repreparation, nor do SELECT statements. An incompatibility with previous versions of MySQL is that a prepared statement may now return a different set of columns or different column types from one execution to the next. For example, if the prepared statement is SELECT * FROM t1, altering t1 to contain a different number of columns causes the next execution to return a number of columns different from the previous execution. Older versions of the client library cannot handle this change in behavior. For applications that use prepared statements with the new server, an upgrade to the new client library is strongly recommended. Along with this change to statement repreparation, the default value of the table_definition_cache system variable has been increased from 128 to 256. The purpose of this increase is to lessen the chance that prepared statements will need repreparation due to referred-to tables/views having been flushed from the cache to make room for new entries. A new status variable, Com_stmt_reprepare, has been introduced to track the number of repreparations. (Bug#27420: http://bugs.mysql.com/27420, Bug#27430: http://bugs.mysql.com/27430, Bug#27690: http://bugs.mysql.com/27690) * Important Change: Some changes were made to CHECK TABLE ... FOR UPGRADE and REPAIR TABLE with respect to detection and handling of tables with incompatible .frm files (files created with a different version of the MySQL server). These changes also affect mysqlcheck because that program uses CHECK TABLE and REPAIR table, and thus also mysql_upgrade because that program invokes mysqlcheck. + If your table was created by a different version of the MySQL server than the one you are currently running, CHECK TABLE ... FOR UPGRADE indicates that the table has an .frm file with an incompatible version. In this case, the result set
