LOAD DATA INFILE Problem
Hello, after switching from MySQL 5.0.67 to 5.1.59 we get the following problem: We want to import data from a textfile example of the textfile: t...@test.com$yes$432145$xyz The command is: LOAD DATA LOCAL INFILE 'textfile.txt' INTO TABLE TESTTABLE FIELDS TERMINATED BY '$'; Selecting the data from the TESTTABLE by select * from TESTTABLE; the data in the first field looks like |test.com, if I do a select * from TESTTABLE\G I can see t...@test.com in the first field. Are there any changes between MySQL 5.0 and 5.1 ? Best Regards Spiker -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
impact of altering ft_min_word_len
Hello, we want to set the global variable ft_min_word_len from the default four to the value three. This is needed because we want to search for words with the max. length of three characters in one application. I've read that after setting ft_min_word_len to the new value, a REPAIR TABLE tablename QUICK; is required. Now the question: The MySQL holds more than one database but I only want to take affect the modification to one special database. Is there a problem with indizes in other databases or can I ensure that the affect only regards to one database? Regards Spiker -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3.5 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Data import problem with duplicate datasets
Hello, we have two tables associated with a foreign key constraint. Table A with the primary key and table B with an on delete cascade constraint. We want to delete datasets in Table B if the related dataset in Table A is deleted - that works. Now the Problem: There is a weekly import defined on table A using a load data infile FILE replace into table A command. The problem is the implicit delete/insert from the replace keyword. Therefore the sets in Table B are deleted. We are using replace because some datasets have to be modified in some columns in table A. Is there a possibility to avoid the delete in Table B by importing data in Table A? Regards, Spiker -- Neu: GMX Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://portal.gmx.net/de/go/dsl02 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
problem with mutated vowels (öäü)
Hello, I've a problem with mutated vowels like öäü by using the mysql client. Nothing happens if I type ö,ä or ü. The input seems to be ignored. OS is Solaris 9 Sparc 32 Bit. MySQL Version 5.0.45 (I tried the source and binary version.) By typing in the shell (csh, ksh) or using the vi there is no problem. Is there a simple solution like setting a LANG variable or changing a character set? Thanks. Regards, Spiker -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with mutated vowels (öäü)
Hello, I found the solution by my own: set meta-flag on set convert-meta off set output-meta on Regards, Spiker -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
declaration of constants in MySQL functions
Hello, is there a possibility to declare constants in MySQL functions? declare test_name varchar(50) DEFAULT 'test'; The variable test_name here ist changeable but it should not be. Oracle syntax: test_name CONSTANT VARCHAR2(50) := 'test'; Regards, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN migration from Oracle to MySQL
Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), ... - doesnt' work. Exits a solution for this example? Other syntax possibilities? regards, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN migration from Oracle to MySQL
Hello, thank you - now it works. d_parenttype PT1 LEFT OUTER JOIN t_booklists bl ON (PT1.ID = bl.PARENTTYPE_1), d_parenttype PT2 LEFT OUTER JOIN t_booklists bk ON (PT2.ID = bk.PARENTTYPE_2) I had to put the alias to all listet fields in the select. regards, Spiker Original-Nachricht Datum: Fri, 31 Aug 2007 09:30:13 -0400 Von: Baron Schwartz [EMAIL PROTECTED] An: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Betreff: Re: JOIN migration from Oracle to MySQL [EMAIL PROTECTED] wrote: Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), The exact error message would be helpful, but I'm seeing at least two problems: 1) you're aliasing two tables as 'bl'. The aliases need to be unique. 2) The second ON clause shouldn't start with AND. Otherwise you should have no problem doing this. -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
consitent backup of MyISAM and Innodb tables
Hello, we have a MySQL DBMS with a lot of databases. Most of them are using MyISAM tables but three databases use InnoDB and MyISAM tables. What is the best method to get a consitent ONLINE backup of both table types? Thanks, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem MySQL 4.0.20
Hello, Explain where the Statement is very fast: *** 1. row *** table: Stuecke type: ALL possible_keys: PRIMARY,St_Autor key: NULL key_len: NULL ref: NULL rows: 694 Extra: Using where; Using temporary; Using filesort *** 2. row *** table: StueckBilder type: ref possible_keys: StB_StueckID key: StB_StueckID key_len: 5 ref: Stuecke.St_ID rows: 3 Extra: Using where *** 3. row *** table: St_Aufg_Pers_V type: index possible_keys: St_Aufg_Pers_V_StueckID key: St_Aufg_Pers_V_StueckID key_len: 15 ref: NULL rows: 863 Extra: Using where; Using index; Distinct 3 rows in set (0.00 sec) explain from the slow machine: *** 1. row *** table: Stuecke type: ALL possible_keys: PRIMARY,St_Autor key: NULL key_len: NULL ref: NULL rows: 694 Extra: Using where; Using temporary; Using filesort *** 2. row *** table: StueckBilder type: ref possible_keys: StB_StueckID key: StB_StueckID key_len: 5 ref: Stuecke.St_ID rows: 3 Extra: Using where *** 3. row *** table: St_Aufg_Pers_V type: index possible_keys: St_Aufg_Pers_V_StueckID key: St_Aufg_Pers_V_StueckID key_len: 15 ref: NULL rows: 863 Extra: Using where; Using index; Distinct 3 rows in set (0.00 sec) In my opinion there are no differences. How does the cpu power influence the speed of a query? Regards, Spiker -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem MySQL 4.0.20
Hello Ananda, yes, the testmachine has the same data. Regards, Spiker -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance problem MySQL 4.0.20
Hello, I've a performance problem with our database: Some select statements take about 20 seconds. The same statements on an equal testmachine take less than 1 second. Server: CPU: 2 x 440 MHz sparcv9 RAM: 2GB (top: Memory: 2048M real, 931M free, 732M swap in use, 2839M swap free) OS: SunOS 5.9 Database: Version: MySQL 4.0.20 (build from source) Traffic: averaged 4985 Bytes/sec sent averaged 41 questions/sec averaged 0,3 connections/sec Some options in my.cnf: [mysqld] key_buffer = 16M max_allowed_packet = 1M table_cache = 96 sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M query_cache_size = 16M max_connections = 125 Is there just to much traffic on the machine or could help tuning some parameters? Thanks. Best regards, Spiker -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlbinlog problem
Hello, I have hab problem with mysqlbinlog. I'm testing point in time recovery. The Server Version of MySQL is 5.0.22. Statement: ./mysqlbinlog --stop-date=2006-12-29 08:40:00 logfile | mysql -u user -p The error: ERROR at line 12: Unknown command '\C'. By piping the output in a file line 12 looks like: /*!\C latin1 */; Where is the fault? regards Spiker -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Renaming the root user - problems.
Hello, my question refers to the user root in MySQL 5.0.22. Is the standard MySQL root user really required with the name root or can I rename the root user for example to myroot? Our software vendor affirms that MySQL need the User root always but I argue the convers. The application of this vendor doesn't work by renaming the root user to myroot. In my opinion the application causes the fault and not the MySQL DBMS. Who is right? Regards Spiker -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
