AW: MySQL 3.23.40 and 4.0.20 parallely on the same data-source
Hi, Is it possible (stable and without data loss :-)) to run e.g. mysql-3.23.40 on Port 3306 and mysql-4.0.20 on Port 3307 on the same machine (SuSE Linux with 2.4.4-kernel) yes !AND! on the same data-directory without the two versions conflicting (e.g. when both version would try to change some database value within the same time)? How good is mysql's locking in that constellation? no Locking doesn't work with this, because server 1 doesn't know what server 2 does (locks) and vice versa. And what shall the poor reading server do when the writing server just changes the data ? Maybe, if you build all aplications round this, to always fully lock a table on both servers before changing data, might work. But even then, cached data might be a problem. Why do you want to do this ? prosit Klaus Antworten in Deutsch tun's auch :o) -Ursprüngliche Nachricht- Von: Oliver Neumann [New Identity AG] [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juni 2004 10:50 An: [EMAIL PROTECTED] Betreff: MySQL 3.23.40 and 4.0.20 parallely on the same data-source Hi there, just one quick question I didn't find an answer in the docu: Is it possible (stable and without data loss :-)) to run e.g. mysql-3.23.40 on Port 3306 and mysql-4.0.20 on Port 3307 on the same machine (SuSE Linux with 2.4.4-kernel) !AND! on the same data-directory without the two versions conflicting (e.g. when both version would try to change some database value within the same time)? How good is mysql's locking in that constellation? Thanks for your support in advance. Greetz, Oliver Neumann -- 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]
AW: ORDER BY problem
Hi, it is not possible to handle all cases proper. You can just handle all cases you know with the REPLACE-function, so you simply delete the prefixes in the WHERE-clause. But that only works for all prefixes you know. If you do like ORDER BY REPLACE(REPLACE(surname,'du',''),'de','') you get all 'du Sautoy' and 'de Contes' exactly like you wanted it but 'de la Tour' will still apear at the wrong place. I don't think you can be sure to remove all prefixes like this, because you can't be sure to know all of them. A different trick would be to say allways take the last 'word' in the surname, which is much more efficent, but will unfortunally not work with double names like 'Schwarzenegger Schriver' (Maybe they are written with a '-' in it, it's just to show the principle). So, there is not lot the world can learn from germany, but we treat all these prefixes like they belong ti the name, which means 'von Hohenzollern' is correctly ordered among the the v's and not the h's. prosit Klaus -Ursprüngliche Nachricht- Von: andy thomas [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 8. Juni 2004 13:51 An: [EMAIL PROTECTED] Betreff: ORDER BY problem In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy -- 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]
AW: C compared to C++/Java; Was: Re: InnoDB Hot Backup + MySQL em bedded?
Hi, this discussion is useless, object or procedure is not realy the question. You need to know how to build a good programm, if you cannot create a good programm, no matter what language. The amount of realy bad java-programs (90% i have seen were realy bad) shows, that it is maybe not a good idea, to make programming to easy :o) . There are a lot of people, thinkink a complex task is better done with an oo-language. My boss is this opinion and had already 2 memleaks in C++, he searched for one 6 weeks. So the truth seems to be, that an oo-language (especially java) makes it easy to programm complex tasks, but what comes out in the end is worth. I prefer TCL because on my opinion it is the best of both worlds ( i never had a memleak except with a bad API written in C). Complex tasks should be done from skilled programmers - thats all. mfg Klaus -Ursprngliche Nachricht- Von: Heikki Tuuri [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 21. Februar 2004 09:30 An: [EMAIL PROTECTED] Betreff: C compared to C++/Java; Was: Re: InnoDB Hot Backup + MySQL embedded? Jochem, - Original Message - From: Jochem van Dieten [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 21, 2004 2:10 AM Subject: Re: InnoDB Hot Backup + MySQL embedded? Sasha Pachev wrote: Heikki Tuuri wrote: C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In Java is it easy to write a program that wastes large amounts of memory, which is worse than a leak. In C, you are full from the start, and then you leak a drop at a time until you are empty. In Java , you are empty from the start, and you have nothing to leak anyway even if you could :-) http://citeseer.nj.nec.com/shah01java.html here is a .pdf version of the paper: http://gist.cs.berkeley.edu/~mashah/java-paper/paper.pdf The authors used a 2 x Pentium III 667 MHz, Linux-2.2.16, Sun JDK 1.3, and Java HotSpot Server JVM 1.3.0. to implement a 'data-flow' query processor. Their conclusion is that the memory management and the garbage collection of Java is inefficient. The graph that they present shows an up to 2.5-fold performance degradation with the Java garbage collector, compared to their own tailored memory management system. I worked with Entity Systems Oy in the 1980s. We developed a Lisp interpreter and a compiler, and a Prolog interpreter. At that time, the inefficiency of the garbage collection in Lisp and Prolog was a serious problem. I am not familiar with more modern garbage collection algorithms, but the paper of Shah et al. suggests that there are still problems today. In the 1980s, the research group of Mike Stonebraker initially started implementing Postgres in a mixture of Lisp and C, but they later abandoned Lisp. Jochem Regards, Heikki -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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]
how setting default for SQL_SELECT_LIMIT ?
Dear Ladies and Sirs, is ist possible to set a default for SQL_SELECT_LIMIT ? I tried to put this in my.cnf but it didn't work (server didn't start). It's MySQL 4.017 on LINUX. thank you Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pending results blocking mysql-server
Dear Sirs and Ladies, we are porting an application from ORACLE to MySQL. The application does lot of queries and has and times out a query, if no result is back after 10 seconds. It handles queries parrallel and rasies or lowers the amount of connection variable (the programm is a server itself). After a while, there is very big load on the MySQL-server (up to 90) and 'show processlist' shows very many connections with queries that are in 'sending data'-state. But after they are timed out, this results will never be fetched, but the threads for them are kept a very long time. With lot of queries, it even happens, that a connection to the mysql-server is blocked. i think it would be the best to get rid of hte pending results on the server, that are not fetched after let's say 20 seconds. Which variable do i need to set to achieve this (if possible). The program uses the C-API. The serve is 4.017 with MyISAM-Tables on SUSE-LINUX SLES 8.0, Ext3-filesystem, a 3Ghz xeleron, 1.5G MEM. Please help me, after i convienced my boss to use MySQL for this duty (which took about 18 month), this might be very dangerous for my carreer. PLEASE HELP ME (or give me a new job :-]) Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Pending results blocking mysql-server
Hi Stefan, thanks for your fast reply ! Would it be possible to call mysql_free_result() from your application? You might be able to set a timer in your app, calling that function after, for example, 20 seconds. I am not the of developer this programm ... But i'm sure I can convince him if it helps :0) I read about mysql_free_result() already, but i thought it would clear the local buffer for an already fetched result. Are you sure it erases a pending result on the server? mfg Klaus Would it be possible to call mysql_free_result() from your application? You might be able to set a timer in your app, calling that function after, for example, 20 seconds. -Ursprüngliche Nachricht- Von: Stefan Hinz [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 17. Februar 2004 12:15 An: Franz, Fa. PostDirekt MA Cc: [EMAIL PROTECTED] Betreff: Re: Pending results blocking mysql-server Klaus, we are porting an application from ORACLE to MySQL. The application does lot of queries and has and times out a query, if no result is back after 10 seconds. It handles queries parrallel and rasies or lowers the amount of connection variable (the programm is a server itself). After a while, there is very big load on the MySQL-server (up to 90) and 'show processlist' shows very many connections with queries that are in 'sending data'-state. But after they are timed out, this results will never be fetched, but the threads for them are kept a very long time. With lot of queries, it even happens, that a connection to the mysql-server is blocked. i think it would be the best to get rid of hte pending results on the server, that are not fetched after let's say 20 seconds. Which variable do i need to set to achieve this (if possible). The program uses the C-API. The serve is 4.017 with MyISAM-Tables on SUSE-LINUX SLES 8.0, Ext3-filesystem, a 3Ghz xeleron, 1.5G MEM. Please help me, after i convienced my boss to use MySQL for this duty (which took about 18 month), this might be very dangerous for my carreer. Would it be possible to call mysql_free_result() from your application? You might be able to set a timer in your app, calling that function after, for example, 20 seconds. http://www.mysql.com/doc/en/mysql_free_result.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: AW: Pending results blocking mysql-server
Hi Sacha, Thx a lot for your reply you wrote: in this case, I suspect, is that the disconnect happens in the pre-data-sending stage, which is possibly quite lengthy. Exactly ! It takes hours till these results are erased and i even don't know where the period when they are erased come from. I still hope this is set in a server-variable. I would also check to see if there might be some simple fixes , such as adding appropriate keys to the tables, that would optimize the slow queries. The problem is, that i can't prevent queries like SELECT * FROM TABLE WHERE COLUMN LIKE 'a%' where an index will not help on a Table 100.000.000 records. It's not that we have to answer hese queries (therefore the timeout), but it's impossible to prevent dumm users. The solution I would propose is to maintain a separate connection on which the KILL commands will be sent for the long queries. I did this (by reading processlist and kiliing any process that lasts longer then 20 seconds) and it works fine, but it is not such a 'proper' solution. I still hope there is a way to get that by setting up the server. Thx a lot again Klaus Ursprüngliche Nachricht- Von: Sasha Pachev [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 17. Februar 2004 14:19 An: Franz, Fa. PostDirekt MA Cc: Stefan Hinz; [EMAIL PROTECTED] Betreff: Re: AW: Pending results blocking mysql-server Franz, Fa. PostDirekt MA wrote: Hi Stefan, thanks for your fast reply ! Would it be possible to call mysql_free_result() from your application? You might be able to set a timer in your app, calling that function after, for example, 20 seconds. I am not the of developer this programm ... But i'm sure I can convince him if it helps :0) I read about mysql_free_result() already, but i thought it would clear the local buffer for an already fetched result. Are you sure it erases a pending result on the server? mysql_free_result() frees only the client memory. However, a disconnect on the socket will tell the server to stop sending the data and clean up. The problem in this case, I suspect, is that the disconnect happens in the pre-data-sending stage, which is possibly quite lengthy. The solution I would propose is to maintain a separate connection on which the KILL commands will be sent for the long queries. And, I would also check to see if there might be some simple fixes , such as adding appropriate keys to the tables, that would optimize the slow queries. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating index performance problem
Dear Sirs and Ladies, i'm on the way to create some big indexes on a huge MyISAM-table(13G), using the 'ALTER TABLE ADD INDEX ..., ADD INDEX ..., ...' -Statement. After mysql copied all data to the temporary #-Tables it slowes down very much. A top shows my that it mostly runs in 'D' (uninterruptible sleep) state with very low CPU-consumption (~3%). After i thought it was an I/O-thing, i changed disks to scsi, that helped, but not that much. I tuned the key_buffer_size to 3000M in my.cnf - that also helped, but after it produced about 2.7 G in the #...MYI-File it slowes down and runs into D-states again. The engine is a 3 Ghz dualprocessor Pentium 3 with 4G memory. and 5 scsi drives (striped raid) ext2. MySQL is 4.017 on SUSE 7.2. The mysld's are the only processes on this engine and creating these indexes is the only job they have to do. The server is not ment to send queries to, just for creating the tables files. I'd likke to know, if the above mentioned behaviour is normal and if there is a chance to speed up by tuning other parameters or anything else? I create all indexes in one 'ALTER TABLE'-statement, would it be faster, to create the indexes one after the other ? Is there maybe a 'dirty trick' ? E.g. creating indexes only half way, kille the process and then repair via myisamchk, which seems to repair much faster then the mysqld itself. any help wouldbe very welcome Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
repair with keycache during ADD INDEX
Dear Sirs and Ladies,
I tried to put some keys on a large table (more than 100.000.000 in 13G)
with the Statement:
ALTER TABLE mytable
ADD INDEX i1 (COL1(4)),
ADD INDEX i2 (COL2(4)),
ADD INDEX i3 (COL3(12)),
ADD INDEX i4 (COL4(12)),
ADD INDEX i5 (COL5(5)),
ADD INDEX i5 (COL6(11));
After about 7 hours, a 'SHOW PROCESSLIST' doesn't say 'copy to temp-table'
anymore but 'repair with keycache'.
Watching the the directory for this database, it seems to happpen at the
time when all data from .MYD is copied to
the temp-table.
The 'repair with keycache' now lasts about 12 hours and I don' know when it
comes to an end.
Is this a usual thing or did I do something wrong?
I thought 'repair with keycache' just happens while recovering of a crashed
table.
How can I speed up this process ?
Is innodb on MySQL 4.x faster doing this ?
In order ro increase speed of creating an index, I increased the
key_buffer-varibale to 1024M,
which is 60% of the memory on that engine.
OS is SUSE-LINUX 8.0 (Kernel 2.4.4)
MySQL is 2.23.52.
FS is Reiser.
The Table is myisam and looks like this:
Field TypeNullKey Default Extra
COL_A char(2)
COL1varchar(4)
COL2varchar(4)
COL6varchar(11)
COL_B enum('-1','0','1','2') YES (NULL)
COL3varchar(35)
COL4varchar(55)
COL_C varchar(35)
COL_D varchar(55)
COL_E varchar(61)
COL_F varchar(30)
COL_5 varchar(5)
COL_G varchar(50)
COL_H varchar(50)
COL_I varchar(50)
COL_J varchar(14)
COL_K int(11) 0
COL_O int(11) 0
To make it a bit easier to read, i named all collumns with an index with a
number (like COL_1).
This table will be filled once a week and there will be no further updates
or inserts till one wwek later,
when it will be dropped and rebuild with new data.
It is the only table in this database and (except mysql's privilege-tables)
the only table on this engine at all.
Please Help me
Klaus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timeout with mysql
Hi There, i tried to load a big file into a MySQL table using LOAD DATA LOCAL INFILE ... Everything worked fine but after about 2 hours it say: Lost connection to MySQL- server during query The server was allwaiys up and I don't have a clue what happend. I thought, that maybe our intranet was down for a moment or it is something with timeouts. I rewrote the /etc/my.cnf file and raised the connect_timeout-variable to 1000. After restartign the server, the variable was still 5, so I guess I did something wrong. So I have 3 questions: How canI raise connect_timeout (if necessary) ? Is it possible to get transparent reconnects with MySQL for a LOAD DATA INFILE statement ? Is there anything else, that I need to do. MySQL is 3.23.52-log Client api is mysqltcl 2.11 (based on C-api) OS is SUSE-LINUX SES 8.0 Kernel 2.4 FS is Reiser up to 64 GB tia Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Funny behaviour with 'ü' - SQL, SELECT
Dear Ladies and Sirs, I tried SELECT * FROM Table WHERE Column LIKE '%ü%'; to find out if german umlauts (öäü) are imported. In the resultset where some ds with 'ü' and some with 'y'. So the charset itself seems to be OK but why did I get 'y'. They sound sometimes the same, but they are not the same. How can I fix this? MySQL-version is 3.23 , same with 4.1. tia Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Funny behaviour with 'ü' - SQL, SELECT
Dear Ladies and Sirs, I tried SELECT * FROM Table WHERE Column LIKE '%ü%'; to find out if german umlauts (öäü) are imported. In the resultset where some ds with 'ü' and some with 'y'. So the charset itself seems to be OK but why did I get 'y'. They sound sometimes the same, but they are not the same. How can I fix this? MySQL-version is 3.23 , same with 4.1. tia Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Can't open...
Hi There, errno 145 means: 'Table was marked as crashed and should be repaired' You can Type on the commandline (dosbox, bash, etc-): perror ErrorNumber to get information about errors. prosit Klaus P.S.:REPAIR TABLE (manual chapter 4.4.5) might help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: IF, THEN ELSE statement
Hi Rob, i think to use REPLACE would do, if there is a unique index on myid: REPACE mytable (myid, myname) VALUES (myid, mynewname); The Manual say in chapter 6.4.8: REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. prosit Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: How can I select the column names?
Hi, I am trying to select the column names from a table to be displayed in a web page. Is there anyway to select column names without desc? I don't now, if it makes a difference to 'DESCRIBE' but you can do: SHOW COLUMNS FROM TABL_NAME [LIKE ...]; prosit Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Help with #1111 - Invalid use of group function
Hi, SELECT TD.project_id, P.project_manager FROM time_daily TD INNER JOIN projects P ON P.project_id = TD.time_project_id WHERE TD.time_user_id = 'xpt' AND ( SUM( TD.time_hours_worked ) '0.00' an Paul Dibois said: You cannot use aggregate functions like SUM() in the WHERE clause. And He is right (of curse). But you can use an alias of a formarly selected SUM() in a HAVING clause. If i understood your query, this migth help: SELECT TD.project_id, P.project_manager, SUM( TD.time_hours_worked ) as a_sum FROM time_daily TD INNER JOIN projects P ON P.project_id = TD.time_project_id WHERE TD.time_user_id = 'xpt' GROUP BY TD.project_id , P.project_manager HAVING a_sum '0.00' I hope I understood the query the right way. prosit Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL LOAD zipped DATA from File
Dear Ladies and Sirs, can anyone give me a hint please, if it is possible toimport data data from a zipped File, without unzipping it before. I'm using MySQL 3.23 on a LINUX System. I guess it is possible using a 'named pipe' and 'funzip' , but I don't lnow how. Thank you Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Exists anything like Oracle TO_DATE('31/12/2003','dd/mm/yyyy' ) ? sql
Hi Marcos,
you can use simple string-functions to manage this.
For example:
dd/mm/ - -mm-dd
CONCAT_WS('-',RIGHT('dd/mm/',4),SUBSTRING('dd/mm/',4,2),LEFT('dd/mm/',2))
CONCAT_WS(separator, string_1 ,string_2 ...)puts strings together with separato
RIGTH(string,N) gives the first N chars of string
LEFT(string,N) gives the last N chars of string
SUBSTRING(string,pos,N) gives N chars of string from pos
prosit
Klaus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Remove -?
Hi Doug, try: UPDATE table_name SET ProdKeyword = REPLACE(ProdID,-,); prosit Klaus MySQL, Query, whatever, be a keyword :o) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Nearly solved - Strange Bug with a working table - Silent col-def-change !
OK, I found out, why the table grows that extraordinary. After 'CREATE TABLE new_1 SELECT * FROM output_1'; All columns of new_1 are CHAR(255) instead of VARCHAR(255) like in output_1 ( see below). Now my simple questions are: 1. Why , I saw something about silent changes in the docs, but there it says VARCHAR columns with a length less than four are changed to CHAR. If any column in a table has a variable length, the entire row is variable-length as aresult. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. Which doesn't fit here. Did I miss something in the docs ? 2. Is there a way to prevent a table from being changed that way without 'altering' all Columns aftwerwards. I thought of the dirty thing to insert a BLOB-Type as first column and delete it, which works fine but is there a better way ? And again Did I miss something in the docs ? Just point me there please I am usally a rtfm-freak. nearly happy but not really satisfied Klaus The table output_1 has this structure: Field TypeNullKey af_1int(11) YES MUL af_3varchar(255)YES af_2varchar(255)YES af_23 varchar(255)YES af_10 varchar(255)YES af_11 varchar(255)YES af_10001varchar(255)YES af_16 varchar(255)YES af_9varchar(255)YES af_8varchar(255)YES af_30 varchar(255)YES af_24 varchar(255)YES af_52 varchar(255)YES af_300 varchar(255)YES af_53 varchar(255)YES af_54 varchar(255)YES af_18 varchar(255)YES af_5varchar(255)YES af_25 varchar(255)YES af_26 varchar(255)YES af_27 varchar(255)YES af_28 varchar(255)YES af_55 varchar(255)YES af_56 varchar(255)YES af_57 varchar(255)YES af_58 varchar(255)YES dub_flagint(11) YES MUL dub_kopfint(11) YES dub_purge int(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K. I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange BUG? with a working table
Dear Ladies and Sirs, I have a problem to create a tables duplicate with: CREATE TABLE new_1 SELECT * FROM output_1; The table output_1 has this structure: Field TypeNullKey af_1int(11) YES MUL af_3varchar(255)YES af_2varchar(255)YES af_23 varchar(255)YES af_10 varchar(255)YES af_11 varchar(255)YES af_10001varchar(255)YES af_16 varchar(255)YES af_9varchar(255)YES af_8varchar(255)YES af_30 varchar(255)YES af_24 varchar(255)YES af_52 varchar(255)YES af_300 varchar(255)YES af_53 varchar(255)YES af_54 varchar(255)YES af_18 varchar(255)YES af_5varchar(255)YES af_25 varchar(255)YES af_26 varchar(255)YES af_27 varchar(255)YES af_28 varchar(255)YES af_55 varchar(255)YES af_56 varchar(255)YES af_57 varchar(255)YES af_58 varchar(255)YES dub_flagint(11) YES MUL dub_kopfint(11) YES dub_purge int(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K ! I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Strange BUG? with a working table
Hi Chiu, It seems that you have reached the 2G file limit with many Linux system. I am not familiar with SuSe. As I know, you may try to use raid option. You may refer to MySQL doc. on 'create' for details. Yes I know, that I can just manage files to 2 GB. The question was, why is the new tables, that should just be the copy of output_1, more than 3 times bigger. I didn't add any data, neither did I change structure. may refer to MySQL doc. on 'create' for details. I tried without CEATE ... SELECT (via LOAD DATA INFILE) and it didn't work either. Did I miss something in the docs ? thanks and greetings Klaus KH Dear Ladies and Sirs, I have a problem to create a tables duplicate with: CREATE TABLE new_1 SELECT * FROM output_1; The table output_1 has this structure: FieldTypeNullKey af_1 int(11) YES MUL af_3 varchar(255)YES af_2 varchar(255)YES af_23varchar(255)YES af_10varchar(255)YES af_11varchar(255)YES af_10001 varchar(255)YES af_16varchar(255)YES af_9 varchar(255)YES af_8 varchar(255)YES af_30varchar(255)YES af_24varchar(255)YES af_52varchar(255)YES af_300 varchar(255)YES af_53varchar(255)YES af_54varchar(255)YES af_18varchar(255)YES af_5 varchar(255)YES af_25varchar(255)YES af_26varchar(255)YES af_27varchar(255)YES af_28varchar(255)YES af_55varchar(255)YES af_56varchar(255)YES af_57varchar(255)YES af_58varchar(255)YES dub_flag int(11) YES MUL dub_kopf int(11) YES dub_purgeint(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K ! I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Checking existence of a table. query
Hi Ed, What query would I use to check for an existence of a table within a database? SHOW TABLES LIKE 'table_name'; might help. prosit Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: stuck , mysql on SUSE
Hi , if you used SUSE , why didn't you just install the mysql-packages distibuted there with yast. It's very easy and I never had a problem with it. It is also possible to install foreign packages with yast later , if you want to udate your mysql-server later. The problem is , that SUSE is not very clean in it's directory-structure , so that makefiles often don't work without beeing edited. prosit Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
locking tables , mysql 3.23.41
Dear Ladies and Gentlemen, in order to backup databases or better the whole server file-based , I'd like to know , if there is a way to get a read-Lock for all tables in a database or even better for all tables in all databases. Something like: LOCK TABLES db_name.% READ; which I tried cause I'm fooolish but it didn't work :o) If not , is there a nice tool (best would be command-line) to do a full online-backup of all data on a server. Linux 2.4.10 , mysql 3.23.41 If I missed anything in the docs , just tell me where , please. Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Some Problems with MySQL 3.23.54
Hi Shripal, your requests were: I can't able to do following things with MySQL 3.23.54. Sub-selects Thats right , subselects come with version 4.1 (out for a view days). About 99% of all subselects are can be done with the right JOIN. SELECT INTO TABLE Try: INSERT INTO Table_Name SELECT ... Transactions You have to compile with mysql-max to get them Stored Procedures and Triggers There aren't any. Foreign Keys There is a support for this I heard , but don't know if for 3.23.54 Views There aren't any , but a view is (I guess it is ORACLE you come from) just a stored 'SELECT'-statement. After you said , that you are using a programming-language , (I heard VB is something like this - just kidding) this should not be a problem. But, what can I do if I can't do anything without Transaction Support, Stored Procedures Triggers. Please suggest me, if there is any alternative. And hopefully in which version I can able to get this. For your information I am using MySQL with Visual Basic. It depends on what you want to achieve. You can work around a lot of these things by planning your jobs in a different way saving triggers , but sometimes there is no clean solution without triggers. If you describe a certain problem on that list , a lot of real experts (not me I guess) will try to help. prosit Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Socket Error
Good Morning Anthony , your question was: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (38) I've been reading and searching the documentation for hours and I know this is simple to fix but I can't figure it out. I've been at it for our hours. The mysql directory above does not exist but if I create it and make a file called mysql.sock that does not work either. Could someone point me in the right direction? Please Looks like your mysql-daemon doesn't run at all or/and isn't installed in the directory your client expected it. In order to help you there's some information needed: Which operating system do you use ? How did you install (e.g. rpm) ? Which version of mysql did you try to install ? How did you start the server ? If you don't know the answers to some of that questions , describe what you did. prosit Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: query problem
Hi Jon, you can : DELETE FROM db_test.tbl_dbaddr; This would delete all data in db_test.tbl_dbaddr by first dropping the whole table and then rebuild it again. You can of curse add a 'WHERE CLAUSE' to determin which rows shall be deleted. This is from the HTML-manual: Duplicates indicates the number of rows that couldn't be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions: Inserting NULL into a column that has been declared NOT NULL. The column is set to its default value. Setting a numeric column to a value that lies outside the column's range. The value is clipped to the appropriate endpoint of the range. Setting a numeric column to a value such as '10.34 a'. The trailing garbage is stripped and the remaining numeric part is inserted. If the value doesn't make sense as a number at all, the column is set to 0. Inserting a string into a CHAR, VARCHAR, TEXT, or BLOB column that exceeds the column's maximum length. The value is truncated to the column's maximum length. Inserting a value into a date or time column that is illegal for the column type. The column is set to the appropriate zero value for the type. I think the warnings occur in the binary log-files (under the MySQL-Server-root) , but I think , you must set a Server-variable to get this. I'd recommend to read the online-manual , which is really fine to read or better - buy the fantastiv book of Paul DuBois , which is nice and easy to read. I think there are some people on the list (not me my dear) , that don't like you to ask questions , that aren't difficult to find in the manual or the faq's. Just think of everyone on the list has more than 100 mails every day. I miss Ed Carp for his ultimate statements when his blood-sugar was low ;-) , his comments about people that didn't read the manual where extreme. Prosit Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
my.cnf , query , order
Ladies and Gentlemen, after a bigger Query with a SORT BY statement aborted , i found out , that my 'sort-buffer'-variable had to be bigger. Everything worked fine then , but i now read in Paul DuBois great book , that it is not recommended to make this variable to big. Please advise me , what is a propper size for that variable , to sort tables with up to 4 million rows doing this: SELECT * FROM T1 ORDER BY ZIP_code , Last_Name , First_Name I think I overtuned a bit with 10M. thank you Klaus P.S. Thank you for correcting my misunderstandings with that funny spam-filter , that was a bit confusing for my rather simple brains. Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
myisampack , sql , admin
Good Morning Ladies and Gentlemen, there is a real bad problem with myisampack , that never occured before. I wanted to pack some of my Tables via myisampack and at any condition you i can imagin (server up , server down , database directory , different directory , running myisamchk with mostly any recovering parameter , big table , small table , on finger where the sun never shines , etc. ) I got 'Segmentation fault'. The tables , that I tried to compress worked fine , and gladly still do. Is that a known problem. The version of mysql is 3.23.41-log. The whole stuff installed from RPMs distributed on SUSE 7.3. Kernel is 2.4.10-4GB. FS for databases is Reiser. Root-FS is ext2 (tried it there too). help me please Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
WG: ABUSE? , myisampack , sql , admin [T2002091901XL]
-Ursprüngliche Nachricht- Von: Franz, Fa. PostDirekt MA Gesendet am: Donnerstag, 19. September 2002 13:00 An: '[EMAIL PROTECTED]' Betreff: AW:ABUSE? , myisampack , sql , admin [T2002091901XL] Hi there, i received this message , after sending a request to mysql-mailing-list: Thank you for bringing this matter to our attention. We are sorry for any inconvenience it has caused you. Because we receive a large number of complaints each day at [EMAIL PROTECTED], regretfully, a personalized response to each message is not possible. Please be assured that Verizon investigates each reported occurrence of unsolicited e-mail or spamming. We maintain a zero-tolerance policy in regard to spamming and will take the appropriate action as permitted by Verizon's Acceptable Use Policy. To view our policy, please refer to one of the two following links: Former Bell Atlantic users: http://www.bellatlantic.net/help/faqs/#faqpolicies Former GTE users: http://www.gte.net/hotlinks/policies/agreement.html To better understand the problems with unsolicited e-mail, we have provided information about filtering Spam with your e-mail software, answers to several frequently asked questions and links to some useful online information about Spam at the following link: http://www.gte.net/announcements/spam.html You may also link directly to our page about unsolicited e-mail: http://www.gte.net/contact/spam.html If you are reporting an issue of hacking or other security issues not related to e-mail abuse, please submit your report to [EMAIL PROTECTED] for investigation. Sincerely, Verizon Internet Services [EMAIL PROTECTED] It wasn't my intention to abuse , offend or spam anyone or anything and i think i didn't do something like that at all. If i did , i am very sorry about it , but I'd like to know : What went wrong? mfg Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql , myisampack
Hi, after being mailed from a spam filter (and i still don't know why) , i am sorry , if my question my appear a second time. Myisampack crashes with 'segmentation fault' anytime i try to use it. MySQL-Version is 2.23-41-log. Linux-kernel is 2.4.10-4GB. Everything installed from SUSE 7.3-distribution. The databases are on Reiser-FS. The system on ext2. Anyone with a clue? greetings Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
