Re: is it possible?
Eugene R. Miller wrote: This is kind of a silly question is there an easy way to ... SELECT Status, rating, COUNT(*) FROM song GROUP BY pldupldqd, rating ORDER BY Status, rating DESC This gives me all the information I need ... What I would like to do is something... like SELECT rating, count(WHERE pldupldqd = 0), count (WHERE pldupldqd = 1), COUNT (WHERE pldupldqd = 2) FROM song GROUP BY rating The idea is to get... 3 different columns counting what it equals. Yes, it's possible, the solution is very easy: SELECT rating, sum(CASE pldupldqd WHEN 0 THEN 1 ELSE 0 END), sum(CASE pldupldqd WHEN 1 THEN 1 ELSE 0 END), sum(CASE pldupldqd WHEN 2 THEN 1 ELSE 0 END), Alternatively, you case use function IF(), instead of CASE. Erm --- www.the-erm.com -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: archive data
Copy database directory in another location (ex. /db/data). This path must contains your databases directories... /db --- /data --- /mysql --- data file... --- data file... --- data file... --- data file... --- /test --- /YourDB1 --- data file... --- data file... --- data file... --- /YourDB2 --- data file... --- data file... --- data file... ecc ecc ecc... Then change the base data directory to /db/data. Settings are in the mysql.server script file, used for starting and stopping the service, usually placed in your rc.d directory... RedHat has a complex service management policies and you will find the script in rc.3, rc.4 or rc.5 directory in /etc/rc.d... IMPORTANT: when you change the base data dir into file script you must create /db/data directories first and set them correct permission. You simply change the owner of directories to mysql user. Regards... :-) hi, i am using linux redhat 8 to power mysql. i installed mysql version 4.0.13 using rpm package. by default, the data file of mysql is alocated at /var/lib/mysql, but i have a limited of diskspace of /var dir (it is about 1G), so, it was easily full loaded with data. now, i want to archive the data into another dir. how can i switch the dir such that, i can have a spacious diskspace to store my data. now, i am facing a problem that i cant write into the db because of the no diskspace left. how can i archive my data, then, switch the storing location to another dir (previously it was /var/lib/mysql) which has bigger diskspace. then, restore the data. how can i do this? pls, advise. thanks Cheers, yenonn -- 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: using temporary / using filesort and disk tables
Hi Matt, thanks for answezring! A disk-based temp table is used if you're SELECTing a column [that can be] longer than 255 characters (BLOB/TEXT to MySQL). This is because the in memory HEAP tables don't currently support variable length rows. Using something like LEFT(text_col, 255), if feasible, will get around this problem. OK I'll try this out. My SELECT Looks like : SELECT *, CONCAT(someField, 'µZ') As myTemporaryField FROM blablabla... Could it be that mysql prefers to assume that myTemporaryField could be more than 255 characters, event if it is never the case? (the myTemporaryField is there to order my results ascendently, with Null values beeing placed at the end, but maybe there is a better solution for this?) Also could be disk based if the query examines many rows (large temp table), but your tmp_table_size would probably cover that. I think it does. The explain gave me less than 2000 rows scanned (2000 * 1 * 1 * 1) BTW, 512M is very, very high for tmp_table_size! Do you have enough memory for 512M * number of connections? :-) Yes I know this is some crazy setting, but it is just there for testing purpose. I am the only user of this server, and since I couldn't find the reason for this disk tables, I tried some 'huge' settings... ;-) Thanks again for helping! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot find an index that will be used for SELECT
Hi Ed, May you could join the table to itself, using a join criteria of index and position : SELECT * FROM ROWS AS R1 LEFT JOIN ROWS AS R1 ON (R1.index=R2.index AND R2.X=Right) LEFT JOIN ROWS AS R3 ON (R1.index=R3.index AND R3.X=Left) LEFT JOIN etc etc... Does that help? Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.16 on SCO OpenServer 5.0.6
Hi, I'm trying to install (compile, since there are no binaries for this system) MySQL on an SCO OPenServer 5.0.6 system. Prior to compiling MySQL, I installed the following packages for SCO : FSU Pthreads (ver 3.9) GNU Development Tools (ver 5.0.7Kj) Netscape Communicator (ver 4.7.0e) OSS631B - Supplemental Graphics, Web and X11 Libraries (ver 1.2.1) SCO OpenServer Enterprise System (ver 5.0.6j) SCO OpenServer Linker and Application Development Libraries (ver 5.1.2A) SCO SendMail (ver 8.11.0) OSS635A: Graphical Environment Supplement for OpenServer 5.0.6 (ver 1.0.0) OSS646B - Execution Environment Supplement (ver 1.1.0j) RS506A: Release Supplement for SCO OpenServer Release 5.0.6 (ver rs506a) RS506A: Software Manager Supplement (ver rs506a) USB Supplement (ver 1.0a) I followed the manual for compiling and installing MySQL. MySQL compiles just fine. I can use the compiled client to connect to databases running on W2K and linux servers. When I start the server (mysqld-safe --user=mysql --log ), it seems to start fine : Starting mysqld daemon with databases from /usr/local/mysql/var Err log : 031120 10:35:55 mysqld started Log : /var/opt/K/SCO/Unix/5.0.6Ga/usr/local/mysql/libexec/mysqld, Version: 4.0.16-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument The socket is there, but when I start mysql it just hangs there forever When I try msqladmin status it also hangs, but when I stop it, I get # mysqladmin status mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (4) ' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! It's there : # l /tmp/mysq* prwxrwxrwx 1 mysqlmysql 0 Nov 20 10:35 /tmp/mysql.sock # Any help would be appreciated, Stefaan Van Dooren .--. Technical Support |o_o | Kompas Automatisering |:_/ | // \ \ Tel : +32 3 2350084(| | ) Fax : +32 3 2359792 /'\_ _/`\ Email : [EMAIL PROTECTED] \___)=(___/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOWing temporary tables
H?ctor Villafuerte D. [EMAIL PROTECTED] wrote: Hi all, How can I see the temporary tables in a database? You can't. Is there something like SHOW TEMPORARY TABLES? -- 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]
Errors with MySQL
Dear MySQL Firstly, let me say, I am very new to this. Secondly, I have been reading Larry Ullman's book PHP and MySQL to get started. I have installed PHP fine, and it is working well. I have followed the instructions for MySQL and it has installed a folder in my: usr/local/ folder called mysql-standard-4.0.15-apple-darwin6.4-powerpc with a shortcut to this folder called mysql. The book I'm reading states I should do the following to set permissions: cd /usr/local/mysql sudo echo sudo bin/mysqld_safe I do this and I get this result: Last login: Thu Nov 20 10:44:51 on ttyp1 Welcome to Darwin! [Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo Password: [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe [1] 3254 [Rob-Snows-Computer:/usr/local/mysql] robsnow% Starting mysqld daemon with databases from /usr/local/mysql/data 031120 11:01:23 mysqld ended [1]Done sudo bin/mysqld_safe [Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root password '' bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! [Rob-Snows-Computer:/usr/local/mysql] robsnow% The doesn't have any work arounds to this problem, and I cannot go any further with the tutorials without mySQL running/working. I do a test and I get this result in a browser: Warning : Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) in /Users/robsnow/Sites/mysql_test.php on line 2 Warning : MySQL Connection Failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) in /Users/robsnow/Sites/mysql_test.php on line 2 Larry Ullman's books states it should have the following result, and he uses the same installer I did: Last login: Thu Nov 20 10:44:51 on ttyp1 Welcome to Darwin! [Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo Password: [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe [1] 3254 [Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root password '' [Rob-Snows-Computer:/usr/local/mysql] robsnow% For some reason my run states that the mysql has ended, should this be so? Can you please give me any advise on how to check that mySQL is running, how to check the version, and any answers to why I'm getting these errors? I would be most grateful for your assistance. Rob Snow Rob Snow Space may be the final frontier But its made in a Hollywood basement RHCP 'Californiacation - Californiacation 1999' [EMAIL PROTECTED] http://homepage.mac.com/robsnow
Errors with MySQL
Dear MySQL Firstly, let me say, I am very new to this. Secondly, I have been reading Larry Ullman's book PHP and MySQL to get started. I have installed PHP fine, and it is working well. I have followed the instructions for MySQL and it has installed a folder in my: usr/local/ folder called mysql-standard-4.0.15-apple-darwin6.4-powerpc with a shortcut to this folder called mysql. The book I'm reading states I should do the following to set permissions: cd /usr/local/mysql sudo echo sudo bin/mysqld_safe I do this and I get this result: Last login: Thu Nov 20 10:44:51 on ttyp1 Welcome to Darwin! [Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo Password: [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe [1] 3254 [Rob-Snows-Computer:/usr/local/mysql] robsnow% Starting mysqld daemon with databases from /usr/local/mysql/data 031120 11:01:23 mysqld ended [1]Done sudo bin/mysqld_safe [Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root password '' bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! [Rob-Snows-Computer:/usr/local/mysql] robsnow% The doesn't have any work arounds to this problem, and I cannot go any further with the tutorials without mySQL running/working. I do a test and I get this result in a browser: Warning : Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) in /Users/robsnow/Sites/mysql_test.php on line 2 Warning : MySQL Connection Failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) in /Users/robsnow/Sites/mysql_test.php on line 2 Larry Ullman's books states it should have the following result, and he uses the same installer I did: Last login: Thu Nov 20 10:44:51 on ttyp1 Welcome to Darwin! [Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo Password: [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe [1] 3254 [Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root password '' [Rob-Snows-Computer:/usr/local/mysql] robsnow% For some reason my run states that the mysql has ended, should this be so? Can you please give me any advise on how to check that mySQL is running, how to check the version, and any answers to why I'm getting these errors? I would be most grateful for your assistance. Rob Snow Rob Snow Space may be the final frontier But its made in a Hollywood basement RHCP 'Californiacation - Californiacation 1999' [EMAIL PROTECTED] http://homepage.mac.com/robsnow
Re: Optimizing Custom Full Text Index
Why You don't use another schema:
Table A: (content table...currently nearly 40,000 rows and 62 MB)
id UNSIGNED INT PRIMARY
status VARCHAR 10 INDEXED
category VARCHAR 20 INDEXED
content LONGTEXT
wordstemmed LONGTEXT (fulltext index)
+ other fields
You have to fill wordstemmed field with content stemmed words.
Table C dropped
Your query:
SELECT stem_word FROM B WHERE stem_word IN ('truck','piano','move');
SELECT A.id, COUNT(A.id) as rows, A.category FROM A WHERE
A.status='Active' and
match( wordstemmed) against ( 'truck piano move')
group by A.id HAVING rows=3;
or
SELECT A.id, COUNT(A.id) as rows, A.category FROM A WHERE
A.status='Active' and
match( wordstemmed) against ( 'truck piano move' in boolean mode)
group by A.id HAVING rows=3;
In this manner there is no need to search in a 4.5 MRows Table and
the fulltext engine does a lot of work for You and it will improved
in the future.
I handle a DB with 5+ rows of newspaper news and the full-text works fine:
about 3 seconds per query if I sort by date/time
about 0.5 second per query if I don't sort
There is only a small problem: delete are slow but I have a cron
script that start at 1:00AM
(nobody uses the DB at that time) that stop indexes, perform
deletes, restart index.
(There is a bug in MySql 4.0.15 that corrupts indexes after a
restart index so I do an alter table to rebuild indexes). This script
takes about 6 Minutes.
Santino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restore table error: wrong number of deleted records
Hi! Has anybody run into this? We backed up a MyISAM Table using BACKUP TABLE while other processes created a larger load of INSERTs, DELETEs, UPDATEs, and SELECTs against this table. BACKUP TABLE returned with OK. We generated several backups of the same table at different times. Upon restoring them, for some of these backups we got following error messages: | sqltest1.sim | repair | error| Couldn't fix table with quick recovery: Found wrong number of deleted records | | sqltest1.sim | repair | error| Run recovery again without -q | | sqltest1.sim | restore | status | OK After that, the table seemed to be usable again without further action from our side. Anyway, we wonder why the messages occur. We are pretty sure that we did all the Locking and Flushing correctly according to the Manual. This occurred with MySQL Versions 3.23.52 and 4.0.13 on SuSE Linux 8.1 Thanks in advance for any help! Thomas -- SIGOS Systemintegration GmbH - TESTING IS OUR COMPETENCE - Fon +49 911 95168-0 www.sigos.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using temporary / using filesort and disk tables
Hi All! I think I am getting close to the solution. In fact, mysql creates tmp tables beacause I GROUP BY a query that joins tables. According to the manual, group bys on joins always creates a temporary table. Now the question is, why is this table a temporary disk table??? Thanks in advance. Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I get Winows 4.0.15 binary..
One of the my client is using the above version and he is getting an error with my app so I would like to test my app against this version.. Can I get a link to 4.0.15 Karam __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using spatial extensions
Hi is there an easy way (through e.g. VCL components) to use the spatial features which will be offerd by MySQL 4.1 from Delphi? Rainer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using temporary / using filesort and disk tables
Probably cause your table ends up being bigger than: SHOW VARIABLES LIKE 'tmp_table%'; I don't think so. As I stated before (sorry I didn't include my first post), I have : tmp_table_size = 512M max_heap_table_size=512M From my calculation, I would have ~2000 rows in the result of my query, and that would definitively not make 512M ! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I get Winows 4.0.15 binary..
Karam Chand [EMAIL PROTECTED] wrote: One of the my client is using the above version and he is getting an error with my app so I would like to test my app against this version.. Can I get a link to 4.0.15 You can find this version at: http://downloads.mysql.com/archives.php?p=mysql-4.0 -- 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]
Re: Can I get Winows 4.0.15 binary..
http://downloads.mysql.com/archives.php?p=mysql-4.0v=4.0.15 On Thursday 20 November 2003 12.15, Karam Chand wrote: One of the my client is using the above version and he is getting an error with my app so I would like to test my app against this version.. Can I get a link to 4.0.15 Karam __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL connection
Hi all, I would like to know how to set up the ssl parameters in the my.cnf file. Where can I find a complete example for this, or where is the detailed description of it in the MySQL documentation? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SQL]how to delete record where I reference to other table
Hi Has look alot around , and tried a lot without success how to do this : Delete from pproductMix PM where PM.mixID in (select ID from pmix where mixno=72000) I would like a solution with and without subqueries thanks Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSL connection
Hai Daniel See this link http://www.mysql.com/doc/en/Secure_requirements.html . Hope it wil help you. Create mysql.pem and start dameon with # ./mysqld_safe --user=mysql --ssl-key=/tmp/mysql.pem --ssl-cert=/tmp/mysql.pe m --ssl-ca=/tmp/mysql.pem and connect mysql with the following option # ./mysql --ssl-key=/tmp/mysql.pem --ssl-cert=/tmp/mysql.pem --ssl-ca=/tmp/my sql.pem Thanks Mathan www.visolve.com - Original Message - From: Daniel Kiss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 5:29 PM Subject: SSL connection Hi all, I would like to know how to set up the ssl parameters in the my.cnf file. Where can I find a complete example for this, or where is the detailed description of it in the MySQL documentation? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy (?) SELECT questions
[EMAIL PROTECTED] wrote:
Mark Wilson [EMAIL PROTECTED] wrote:
Two related questions.
1. I have a table with a date field. (Ex: '04/13/2002'). I want to get
a list
of all UNIQUE values for that field (many entries from the same day),
i.e., all
days with entries.
**
CREATE TABLE metrics_events_power {
mep_id int(11) NOT NULL auto_increment,
mep_date text,
mep_time time DEFAULT '00:00:00' NOT NULL
}
INSERT INTO metrics_events_power VALUES ('1', '04/13/2002', '10:41:19');
INSERT INTO metrics_events_power VALUES ('1', '04/13/2002', '10:46:19');
INSERT INTO metrics_events_power VALUES ('1', '04/14/2002', '11:51:19');
**
I want a query that returns for this data '04/13/2002' and '04/14/2002'.
Use WHERE clause to set retrieval conditions for rows, f.e WHERE
mep_date='04/13/2002' OR mep_date='04/14/2002', and GROUP BY mep_date.
Not exactly. I want the retrieval to be automatic. I don't want to know in
advance what the unique dates are; I want the query to tell me which
unique dates exist. So for this data, two dates would be returned. For
other data, more or fewer dates.
SELECT DISTINCT mep_date FROM metrics_events_power;
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SQL]how to delete record where I reference to other table
Kim G. Pedersen [EMAIL PROTECTED] wrote: Has look alot around , and tried a lot without success how to do this : Delete from pproductMix PM where PM.mixID in (select ID from pmix where mixno=72000) I would like a solution with and without subqueries DELETE FROM pproductMix USING pproductMix, pmix WHERE pproductMix.mixID=pmix.ID AND pmix.mixno=72000; http://www.mysql.com/doc/en/DELETE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting files related to mysql
Hello, in my /var/db/mysql there is a whole lot of files that have spiderman-bin.001 all the way to 038 Can I safely delete these files ? I also have these 25088 Sep 12 23:41 ib_arch_log_00 5242880 Nov 19 16:18 ib_logfile0 5242880 Sep 12 23:41 ib_logfile1 10485760 Nov 19 16:16 ibdata1 208758 Nov 19 16:18 log.01 can any of these be deleted ? Mark DeWar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting files related to mysql
These are the InnoDB data and log files. They are there because probably you have the InnoDB feature switched on. You can delete them (all of them) safely, if you do not use InnoDB tables, but if you don't swicth off the InnoDB feature (in my.cnf for example), the system will recreate them after restarting the MySQL server. Bye, Hello, in my /var/db/mysql there is a whole lot of files that have spiderman-bin.001 all the way to 038 Can I safely delete these files ? I also have these 25088 Sep 12 23:41 ib_arch_log_00 5242880 Nov 19 16:18 ib_logfile0 5242880 Sep 12 23:41 ib_logfile1 10485760 Nov 19 16:16 ibdata1 208758 Nov 19 16:18 log.01 can any of these be deleted ? Mark DeWar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN problem
Hi List I am currently running a query that reads records from one table (Table 1) links these records to an id value in another table (Table 2) and returns the result. The tables are as follows, with sample data: Table 1in DB 1: line_number | category_name | category_value | line_type 1 | Rent | 100.00| 13 2 | Usage |50.00| 13 3 | Services |75.00| 13 Table 2 in DB 2: parameter_ID | parameter_trigger | parameter_value 1 | 1| Rent 1 | 2| Usage The returned records will be line_number, category_value from Table 1 and Parameter_Trigger from Table 2 (Table 1 and Table 2 are joined on table 1.Category Name = Table 2.Parameter_Value). As you can see from the sample the third record in table 1 does not have a matching entry in Table 2. In this case, I want the returned record to still show the line_number and category_value, except in place of the parameter_trigger the field should be set to zero (so that I can see that I have a category name that I haven't accounted for). So I am using the following query: SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0) FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON a.category_name = b.parameter_value WHERE a.line_type = 13 AND b.parameter_ID = 13 The only problem is that the query only returns the first two records and not the third record - contrary to my expectations. I am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3. Does anybody know what I am doing wrong? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Re: deleting files related to mysql
M.D. DeWar [EMAIL PROTECTED] wrote: Hello, in my /var/db/mysql there is a whole lot of files that have spiderman-bin.001 all the way to 038 Can I safely delete these files ? These are binary log files. If you don't need them, you can delete them and start server without --log-bin option. I also have these 25088 Sep 12 23:41 ib_arch_log_00 5242880 Nov 19 16:18 ib_logfile0 5242880 Sep 12 23:41 ib_logfile1 10485760 Nov 19 16:16 ibdata1 208758 Nov 19 16:18 log.01 can any of these be deleted ? These files are related to the InnoDB. If you don't use InnoDB tables you can delete these files and start server with --skip-innodb option. -- 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]
Re: LEFT JOIN problem
* Rory McKinley [...] SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0) FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON a.category_name = b.parameter_value WHERE a.line_type = 13 AND b.parameter_ID = 13 The only problem is that the query only returns the first two records and not the third record - contrary to my expectations. I am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3. Does anybody know what I am doing wrong? You have a criteria on the B table in the WHERE clause. Move it to the ON clause: SELECT ... AS b ON a.category_name = b.parameter_value AND b.parameter_ID = 13 WHERE a.line_type = 13 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SOLVED]Re: LEFT JOIN problem
Thanks Roger That fixed the problem.I was under the impression that I only had to put the linked columns n the ON and everything else in the WHERElive an learn I guess. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Rory McKinley [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 4:23 PM Subject: Re: LEFT JOIN problem * Rory McKinley [...] SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0) FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON a.category_name = b.parameter_value WHERE a.line_type = 13 AND b.parameter_ID = 13 The only problem is that the query only returns the first two records and not the third record - contrary to my expectations. I am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3. Does anybody know what I am doing wrong? You have a criteria on the B table in the WHERE clause. Move it to the ON clause: SELECT ... AS b ON a.category_name = b.parameter_value AND b.parameter_ID = 13 WHERE a.line_type = 13 -- Roger -- 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: [SOLVED]Re: LEFT JOIN problem
Thanks Roger That fixed the problem.I was under the impression that I only had to put the linked columns n the ON and everything else in the WHERElive an learn I guess. Ehm, I'm not completely sure, but isn't this invalid behaviour? Can you post the DDL and INSERT INTO data for these tables? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com * Rory McKinley [...] SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0) FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON a.category_name = b.parameter_value WHERE a.line_type = 13 AND b.parameter_ID = 13 The only problem is that the query only returns the first two records and not the third record - contrary to my expectations. I am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3. Does anybody know what I am doing wrong? You have a criteria on the B table in the WHERE clause. Move it to the ON clause: SELECT ... AS b ON a.category_name = b.parameter_value AND b.parameter_ID = 13 WHERE a.line_type = 13 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL [Q] how to migrate 4.0 - 4.1
. Thank you. But what is about binary fields? When I start mysqld-4.0.. field in table are char(x) binary. But when I start mysql-4.1.0 in the same table the same field is not binary. Is this bug? Nope. From the v4.1 BINARY means that no collation is applicable to the column. I understand this and I want this. But my binary fields under 4.1.0 loss binary flag. But under 4.0.12 they have it (the fields in same tables). I head the 4.1.1 version will fix this Or is there right way to restore binary flag on field. Use collation with _bin at the end of collation name. It is fine for new tables, but I say about tables created under 4.0.X and after migration to 4.1.0 they loss binary flags on their fields. -- 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] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data in file
Hi, We have a 2 server setup. 1 server runs the web server with a PHP application, the second server is the mysql database server. The php application needs to connect to the mysql server, to import data from a text file. For this we use Load Data Infile. While everything else in the application works with regard to connecting to tge mysql database, when we try to import data we are getting the following error: Can't get stat of '/upload/wachtbak_cu.txt' (Errcode: 2) We have checked, and the file is indeed uploaded to the /upload directory on the web server. However, after the connection is established with mysql database, mysql tries to find the file on the mysql server. Question is this. Is it possible to import a file into mysql, using Load Data Infile, when you have 1 web server that runs the php apllication that imports the data, and 1 mysql database server? If that's not possible, is there a way to achieve this? rgds Hans Kind -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Re: LEFT JOIN problem
* Martijn Tonies * Rory McKinley Thanks Roger That fixed the problem.I was under the impression that I only had to put the linked columns n the ON and everything else in the WHERElive an learn I guess. Ehm, I'm not completely sure, but isn't this invalid behaviour? Huh? What would be invalid about it? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Re: LEFT JOIN problem
* Martijn Tonies * Rory McKinley Thanks Roger That fixed the problem.I was under the impression that I only had to put the linked columns n the ON and everything else in the WHERElive an learn I guess. Ehm, I'm not completely sure, but isn't this invalid behaviour? Huh? What would be invalid about it? That moving parts of the WHERE clause to the JOIN clause returns a different result set. But as I said: I don't have the full story ... oh wait, I found the first message in my mailbox somewhere. I guess OUTER is optional, and INNER is default - right? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data in file
LOAD DATA LOCAL INFILE But it must be enabled on both the server and client. See the docs. Hans Kind wrote: Hi, We have a 2 server setup. 1 server runs the web server with a PHP application, the second server is the mysql database server. The php application needs to connect to the mysql server, to import data from a text file. For this we use Load Data Infile. While everything else in the application works with regard to connecting to tge mysql database, when we try to import data we are getting the following error: Can't get stat of '/upload/wachtbak_cu.txt' (Errcode: 2) We have checked, and the file is indeed uploaded to the /upload directory on the web server. However, after the connection is established with mysql database, mysql tries to find the file on the mysql server. Question is this. Is it possible to import a file into mysql, using Load Data Infile, when you have 1 web server that runs the php apllication that imports the data, and 1 mysql database server? If that's not possible, is there a way to achieve this? rgds Hans Kind -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Re: LEFT JOIN problem
* Martijn Tonies * Rory McKinley Thanks Roger That fixed the problem.I was under the impression that I only had to put the linked columns n the ON and everything else in the WHERElive an learn I guess. Ehm, I'm not completely sure, but isn't this invalid behaviour? Huh? What would be invalid about it? That moving parts of the WHERE clause to the JOIN clause returns a different result set. But as I said: I don't have the full story ... oh wait, I found the first message in my mailbox somewhere. I guess OUTER is optional, and INNER is default - right? Right... been doing some more checking. It makes totally sense - I misread the first report. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Re: LEFT JOIN problem
* Martijn Tonies * me Huh? What would be invalid about it? That moving parts of the WHERE clause to the JOIN clause returns a different result set. But as I said: I don't have the full story ... oh wait, I found the first message in my mailbox somewhere. I guess OUTER is optional, and INNER is default - right? This was a LEFT JOIN... :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data in file
Are you placing the file in the same directory as the MySQL data files? Are the databases and the location you placed the text file on the same physical drive? -Original Message- From: Hans Kind [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 7:22 AM To: [EMAIL PROTECTED] Subject: load data in file Hi, We have a 2 server setup. 1 server runs the web server with a PHP application, the second server is the mysql database server. The php application needs to connect to the mysql server, to import data from a text file. For this we use Load Data Infile. While everything else in the application works with regard to connecting to tge mysql database, when we try to import data we are getting the following error: Can't get stat of '/upload/wachtbak_cu.txt' (Errcode: 2) We have checked, and the file is indeed uploaded to the /upload directory on the web server. However, after the connection is established with mysql database, mysql tries to find the file on the mysql server. Question is this. Is it possible to import a file into mysql, using Load Data Infile, when you have 1 web server that runs the php apllication that imports the data, and 1 mysql database server? If that's not possible, is there a way to achieve this? rgds Hans Kind -- 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]
temporary table always created on disk
Hi all. I alreaedy posted about this, but I get more and more confused ! My temporary tables are ALWAYS created as temporary disk tables ! Here is the query : SELECT commerces.enseigne FROM cellules INNER JOIN commerces ON (cellules.indexCelluleCommerce=commerces.indexCelluleCommerce) WHERE cellules.CodeSite = SIT7401011 GROUP BY cellules.indexcellulecommerce the fields commerces.enseigne and cellules.CodeSite are CHAR(255), cellules.indexcellulecommerce is INT(11) Here is the explain : EXPLAIN SELECT commerces.enseigne FROM cellules INNER JOIN commerces ON (cellules.indexCelluleCommerce=commerces.indexCelluleCommerce) WHERE cellules.CodeSite = SIT7401011 GROUP BY cellules.indexcellulecommerce Result : | cellules | ref | CodeSite,IndexCelluleCommerce | CodeSite | 21 | const | 34 | where used; Using temporary | | commerces | ref | IndexCelluleCommerce | IndexCelluleCommerce | 5 | cellules.IndexCelluleCommerce | 20 | where used | All the fields in this query are indexed. (not unique) Now my server variables : tmp_table_size = 384M max_heap_table_size = 384M version = 3.23.54-nt And the status (the annoying part!!!) : created_tmp_disk_tables 1 created_disk_tables 1 (I checked before and after the query, they both get increased by one) I understand that the temporary table is created because of the GROUP BY clause, but why on disk ??? How could I work around this? Thanks a lot if anyone can help! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding mysql to the subject line would improve clarity and ease of classification.
It would be great if we could put 'mysql' in the subject of our questions. I receive mail from many different places and it would help classification. Thanks! -Original Message- From: adburne [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 2:47 PM To: [EMAIL PROTECTED] Subject: Execute shell script There is a command or function to call a shell script through mysql? _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
How to move database to new server
Hi I have a mysql database with 20 tables containing data whch i need to transfer to a new hosting server (i access this server via webmin.) What's the easiest way to move a database from one server to the other?. Can you export full databases from mysql (this would be the easiest), or do i need to create the database and all the tables on the new server first and then find a way to just export and import the data?. TIA
Why does -1 show up as 18446744073709551613?
I've been running this query for quite some time that basically says: SELECT (A + B + C) - (X + Y + Z) AS Variance FROM . Up until now, this has been working correctly and showing up as anything from -100 to +100. Now all of a sudden, it's showing up as 18446744073709551613 instead of -1, 18446744073709551614 instead of -2, etc. Now, A, B, C, X, Y Z are all defined as UNSIGNED in the database table. But again, this WAS working as of MySQL 3.23, and we've been running it for well over a year. Now, on version 4.x, I started getting the big numbers. I can only assume that there is some sort of precedence thing that changed. Can anyone shed some light on this? Or maybe tell me what I need to do differently to make this work now? Thanks, Mark As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax ** This e-mail and any of its attachments may contain Brandywine Senior Care, Inc. proprietary information, which is privileged, confidential, or subject to copyright belonging to Brandywine Senior Care, Inc. This e-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this e-mail is strictly prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately and permanently delete the original and any copy of this e-mail and any printout. Thank You. **
Strange behavior on insert
I have a PHP page that takes data from a form and inserts it into a
table:
Show columns:
+++--+-+-+--
--+
| Field | Type | Null | Key | Default | Extra
|
+++--+-+-+--
--+
| ID | mediumint(10) | | PRI | NULL|
auto_increment |
| userid | varchar(20) binary | | | |
|
| name | varchar(20) binary | | | |
|
| tier | int(2) | | | 0 |
|
| price | double | | | 0 |
|
| tierNumber | int(2) | | | 0 |
|
| Min| double | | | 0 |
|
+++--+-+-+--
--+
I have a while loop that insterts the records in the correct order (by
tierNumber).
INSERT INTO TarifBuilder SET
name='{$_POST'name']}',tier='$tier',price='$price',tierNumber='$count',M
in='{$_POST'min']}';
However when I go to the database and do a select * from tablename;
the records are in the table in the reverse order!!
Even the auto increment is in reverse order...
| 65 || 2-gaf | 0 | 0.0004688 | 0 | 0.3
|
| 66 || 2-gaf | 1536 | 0.0002917 | 1 | 0.3
|
| 67 || 2-gaf | 6144 | 0.0002344 | 2 | 0.3
|
| 68 || 2-gaf | 15360 | 0.0001172 | 3 | 0.3
|
| 69 || 3-gaf | 0 | 0.0001172 | 0 |0
|
| 70 || 3-gaf | 0 | 0.0001172 | 1 |0
|
| 122 || test-decreasing | 200 | 1.001e-05 | 2 | 0.51
|
| 121 || test-decreasing | 100 | 2.002e-05 | 1 | 0.51
|
| 120 || test-decreasing | 0 | 3.003e-05 | 0 | 0.51
|
+-++-+---+---++-
-+
Why is this?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior on insert
On 20 Nov 2003 at 11:12, Jeff McKeon wrote: However when I go to the database and do a select * from tablename; the records are in the table in the reverse order!! Even the auto increment is in reverse order... If you wish to retrieve the data in a particular order you must use order by because the database itself does not depend on a sequential storage of the records. Dan Wilterding [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange difference between a != b and (a b OR a b)
Description:
a simple select on a large table does not use an indexed column when the WHERE
clause uses a != b
The same query using as WHERE a b OR a b (which of course does the same)
*does* use an index.
How-To-Repeat:
mysql END_OF_FILE
create database unequalproblem;
use unequalproblem;
-- MySQL dump 8.22
--
-- Host: localhostDatabase: unequalproblem
-
-- Server version 3.23.56
--
-- Table structure for table 'test'
--
CREATE TABLE test (
id int(11) NOT NULL auto_increment,
number int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY k_number (number)
) TYPE=MyISAM;
--
-- Dumping data for table 'test'
--
INSERT INTO test VALUES (1,2);
INSERT INTO test VALUES (2,4);
INSERT INTO test VALUES (3,9);
select('EXPLAIN SELECT id from test WHERE number != 1; *** uses NO index');
EXPLAIN SELECT id from test WHERE number != 1;
select('EXPLAIN SELECT id from test WHERE number 1 OR number 1; *** actually
same query, index on number');
EXPLAIN SELECT id from test WHERE number 1 OR number 1;
drop database unequalproblem
END_OF_FILE
Fix:
the work around is in the prblem description
Submitter-Id: submitter ID
Originator:[EMAIL PROTECTED]
Organization:
European Design Centre b.v.
MySQL support: none
Synopsis: WHERE a != b evaluates different from WHERE a b OR a b
Severity: non-critical
Priority: low
Category: mysql
Class: sw-bug
Release: mysql-3.23.58 (Source distribution)
Environment:
System: Linux archie 2.4.20-20.9 #1 Mon Aug 18 11:27:43 EDT 2003 i686 athlon i386
GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
--with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux
Thread model: posix
gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -march=i386
-mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'
CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686
-D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions'
LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 May 15 2003 /lib/libc.so.6 - libc-2.3.2.so
-rwxr-xr-x1 root root 1557296 Apr 8 2003 /lib/libc-2.3.2.so
-rw-r--r--1 root root 2331360 Apr 8 2003 /usr/lib/libc.a
-rw-r--r--1 root root 204 Apr 8 2003 /usr/lib/libc.so
Configure command: ./configure '--host=i386-redhat-linux' '--build=i386-redhat-linux'
'--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr'
'--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc'
'--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib'
'--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com'
'--mandir=/usr/share/man' '--infodir=/usr/share/info' '--without-readline'
'--without-debug' '--enable-shared' '--with-extra-charsets=complex' '--with-bench'
'--localstatedir=/var/lib/mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--with-mysqld-user=mysql' '--with-extra-charsets=all' '--with-innodb'
'--enable-local-infile' '--enable-large-files=yes' '--enable-largefile=yes'
'--with-berkeley-db-includes=/usr/include' '--with-berkeley-db-libs=/usr/lib'
'--enable-thread-safe-client' 'CFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686
-D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' '!
CXXFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64
-D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' 'build_alias=i386-redhat-linux'
'host_alias=i386-redhat-linux' 'target_alias=i386-redhat-linux-gnu'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding mysql to the subject line would improve clarity and ease of classification.
On Thursday 20 November 2003 16.52, Hassan Farha wrote: It would be great if we could put 'mysql' in the subject of our questions. I receive mail from many different places and it would help classification. Thanks! All of these headers (and more) are in mails on the mailinglist: Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com List-Help: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] The header List-ID is great to filter on, perhaps also for you? I suggest you check out your mailclient's filter options. You are ofcourse still welcome to include mysql in your subject if you want! Regards, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange behavior on insert
I understand how to use the Order By clause on a select, I'm trying to better understand why does this happen on the insert. Jeff -Original Message- From: Dan Wilterding [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:39 AM To: [EMAIL PROTECTED] Subject: Re: Strange behavior on insert On 20 Nov 2003 at 11:12, Jeff McKeon wrote: However when I go to the database and do a select * from tablename; the records are in the table in the reverse order!! Even the auto increment is in reverse order... If you wish to retrieve the data in a particular order you must use order by because the database itself does not depend on a sequential storage of the records. Dan Wilterding [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does -1 show up as 18446744073709551613?
This is in the Upgrading from 3.23 manual: http://www.mysql.com/doc/en/Upgrading-from-3.23.html Note: when you use subtraction between integer values where one is of type UNSIGNED, the result will be unsigned. In other words, before upgrading to MySQL 4.0, you should check your application for cases where you are subtracting a value from an unsigned entity and want a negative answer or subtracting an unsigned value from an integer column. You can disable this behaviour by using the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. See section 6.3.5 Cast Functions. In order to get your selects to work without changing column types look at the cast functions: http://www.mysql.com/doc/en/Cast_Functions.html ...If you are using numerical operations (like +) and one of the operands is unsigned integer, the result will be unsigned. You can override this by using the SIGNED and UNSIGNED cast operators... Mike On Thursday 20 November 2003 17.10, Mark Marshall wrote: I've been running this query for quite some time that basically says: SELECT (A + B + C) - (X + Y + Z) AS Variance FROM . Up until now, this has been working correctly and showing up as anything from -100 to +100. Now all of a sudden, it's showing up as 18446744073709551613 instead of -1, 18446744073709551614 instead of -2, etc. Now, A, B, C, X, Y Z are all defined as UNSIGNED in the database table. But again, this WAS working as of MySQL 3.23, and we've been running it for well over a year. Now, on version 4.x, I started getting the big numbers. I can only assume that there is some sort of precedence thing that changed. Can anyone shed some light on this? Or maybe tell me what I need to do differently to make this work now? Thanks, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange behavior on insert
As far as I know, DELETE's make gaps in the table (you could remove these by optimizing). If you INSERT into a table with gaps, your INSERTed row will try to fill the gaps created by that. Maybe it works backwards in filling the gaps? -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 8:44 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Strange behavior on insert I understand how to use the Order By clause on a select, I'm trying to better understand why does this happen on the insert. Jeff -Original Message- From: Dan Wilterding [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:39 AM To: [EMAIL PROTECTED] Subject: Re: Strange behavior on insert On 20 Nov 2003 at 11:12, Jeff McKeon wrote: However when I go to the database and do a select * from tablename; the records are in the table in the reverse order!! Even the auto increment is in reverse order... If you wish to retrieve the data in a particular order you must use order by because the database itself does not depend on a sequential storage of the records. Dan Wilterding [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does -1 show up as 18446744073709551613?
That would be it! Not sure how I missed that. Thank you! Mark Mikael Fridh [EMAIL PROTECTED] 11/20/03 11:44AM This is in the Upgrading from 3.23 manual: http://www.mysql.com/doc/en/Upgrading-from-3.23.html Note: when you use subtraction between integer values where one is of type UNSIGNED, the result will be unsigned. In other words, before upgrading to MySQL 4.0, you should check your application for cases where you are subtracting a value from an unsigned entity and want a negative answer or subtracting an unsigned value from an integer column. You can disable this behaviour by using the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. See section 6.3.5 Cast Functions. In order to get your selects to work without changing column types look at the cast functions: http://www.mysql.com/doc/en/Cast_Functions.html ...If you are using numerical operations (like +) and one of the operands is unsigned integer, the result will be unsigned. You can override this by using the SIGNED and UNSIGNED cast operators... Mike On Thursday 20 November 2003 17.10, Mark Marshall wrote: I've been running this query for quite some time that basically says: SELECT (A + B + C) - (X + Y + Z) AS Variance FROM . Up until now, this has been working correctly and showing up as anything from -100 to +100. Now all of a sudden, it's showing up as 18446744073709551613 instead of -1, 18446744073709551614 instead of -2, etc. Now, A, B, C, X, Y Z are all defined as UNSIGNED in the database table. But again, this WAS working as of MySQL 3.23, and we've been running it for well over a year. Now, on version 4.x, I started getting the big numbers. I can only assume that there is some sort of precedence thing that changed. Can anyone shed some light on this? Or maybe tell me what I need to do differently to make this work now? Thanks, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax ** This e-mail and any of its attachments may contain Brandywine Senior Care, Inc. proprietary information, which is privileged, confidential, or subject to copyright belonging to Brandywine Senior Care, Inc. This e-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this e-mail is strictly prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately and permanently delete the original and any copy of this e-mail and any printout. Thank You. **
RE: Cannot find an index that will be used for SELECT
Arnaud - Thanks very much for a suggestion! Unfortunately, when I do that (I'm not much of a JOIN expert g) I end up selecting ALL the rows in the table. - Ed -Original Message- From: Arnaud [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 3:19 AM To: Ed McNierney; mysql List Subject: Re: Cannot find an index that will be used for SELECT Hi Ed, May you could join the table to itself, using a join criteria of index and position : SELECT * FROM ROWS AS R1 LEFT JOIN ROWS AS R1 ON (R1.index=R2.index AND R2.X=Right) LEFT JOIN ROWS AS R3 ON (R1.index=R3.index AND R3.X=Left) LEFT JOIN etc etc... Does that help? Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installing 3.23.57 with InnoDB
I want to install mysql.3.23.57 (open source) in Linux, and i need to use innoDB tables i do the configure; ./configure --with-innodb and when i do the make, i get a lot of errors in ha_innobase.cc one of them is: . . . ha_innobase.o(.text 0x554): In function `innobase_commit_low(trx_struct*)': : undefined reference to `trx_commit_for_mysql' . . . why do i get this error? what should i do, apart from the ./configure ...? thanks http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need location and how to configure logs for 4.0.15-nt-log
Hey I've been looking for the log files on a mysql version 4.0.15 install on a windows 2000 box. I've looked in the config file and there is somethign for called log-bin. How do i turn on and configure logging? Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange difference between a != b and (a b OR a b)
Hi,
From the MySQL Manual:
MySQL normally uses the index that finds the least number of rows. An
index is used for columns that you compare with the following operators:
=, , =, , =, BETWEEN, and a LIKE with a non-wildcard prefix like
'something%'.
http://www.mysql.com/doc/en/MySQL_indexes.html
The != does not belong to that list of comparision operators...
Hope this helps!
--
Diana Soares
On Thu, 2003-11-20 at 16:12, [EMAIL PROTECTED] wrote:
Description:
a simple select on a large table does not use an indexed column when the WHERE
clause uses a != b
The same query using as WHERE a b OR a b (which of course does the same)
*does* use an index.
How-To-Repeat:
mysql END_OF_FILE
create database unequalproblem;
use unequalproblem;
-- MySQL dump 8.22
--
-- Host: localhostDatabase: unequalproblem
-
-- Server version 3.23.56
--
-- Table structure for table 'test'
--
CREATE TABLE test (
id int(11) NOT NULL auto_increment,
number int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY k_number (number)
) TYPE=MyISAM;
--
-- Dumping data for table 'test'
--
INSERT INTO test VALUES (1,2);
INSERT INTO test VALUES (2,4);
INSERT INTO test VALUES (3,9);
select('EXPLAIN SELECT id from test WHERE number != 1; *** uses NO index');
EXPLAIN SELECT id from test WHERE number != 1;
select('EXPLAIN SELECT id from test WHERE number 1 OR number 1; *** actually
same query, index on number');
EXPLAIN SELECT id from test WHERE number 1 OR number 1;
drop database unequalproblem
END_OF_FILE
Fix:
the work around is in the prblem description
Submitter-Id:submitter ID
Originator: [EMAIL PROTECTED]
Organization:
European Design Centre b.v.
MySQL support: none
Synopsis:WHERE a != b evaluates different from WHERE a b OR a b
Severity:non-critical
Priority:low
Category:mysql
Class: sw-bug
Release: mysql-3.23.58 (Source distribution)
Environment:
System: Linux archie 2.4.20-20.9 #1 Mon Aug 18 11:27:43 EDT 2003 i686 athlon i386
GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
--with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux
Thread model: posix
gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -march=i386
-mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'
CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686
-D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions'
LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 May 15 2003 /lib/libc.so.6 -
libc-2.3.2.so
-rwxr-xr-x1 root root 1557296 Apr 8 2003 /lib/libc-2.3.2.so
-rw-r--r--1 root root 2331360 Apr 8 2003 /usr/lib/libc.a
-rw-r--r--1 root root 204 Apr 8 2003 /usr/lib/libc.so
Configure command: ./configure '--host=i386-redhat-linux'
'--build=i386-redhat-linux' '--target=i386-redhat-linux-gnu' '--program-prefix='
'--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin'
'--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include'
'--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var'
'--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info'
'--without-readline' '--without-debug' '--enable-shared'
'--with-extra-charsets=complex' '--with-bench' '--localstatedir=/var/lib/mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-mysqld-user=mysql'
'--with-extra-charsets=all' '--with-innodb' '--enable-local-infile'
'--enable-large-files=yes' '--enable-largefile=yes'
'--with-berkeley-db-includes=/usr/include' '--with-berkeley-db-libs=/usr/lib'
'--enable-thread-safe-client' 'CFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686
-D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'!
'!
CXXFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64
-D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' 'build_alias=i386-redhat-linux'
'host_alias=i386-redhat-linux' 'target_alias=i386-redhat-linux-gnu'
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: batch files usage
At 23:12 -0500 11/19/03, r.schacht wrote: Hi, I'm new to MySQL. I want to try to create a new database, but I type in the same commands all the time. What I want to be able to do is type the commands into text files, then run them from inside MySQL. This way if I want to change something I can just drop the database and recreate. Any help would be appreciated. This is described in the MySQL Reference Manual: http://www.mysql.com/doc/en/Batch_mode.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql server time setting.
On 19-Nov-2003 Jeff McKeon wrote: I'm running mysql on a redhat system starting it from a script in /etc/init.d/ as is the default with the rpm install. I can't seem to set the timezone environmental variable to change nomatter what I put in the /etc/my.cnf file. Can anyone help me with this? In your init script (before the section that calls safe_mysqld) put: TZ=EST; export TZ Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
devcom , gcc g++ win32
Hello how compiling file: mysql/sql/udf_example.cpp in devcom or mingw32 ? - error in struct UDF_ARGS and UDF_INIT; this file compilations in VC6 non error regard Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Timeout Setting?
Is there anyway to tell MySQL to abort queries that take over X seconds, or something similar? - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Turn off read only in tables
I am trying to learn PHP using the tutorial for DWMX on macromedia.com. I was doing pretty good until it came time to edit a record. When I tried the page, it said the 'album' table was read only. I had copied the database files from my computer to the server so I went back and checked all the permissions. I made them identical to the tables in a database that was working (owner = mysql with 660 permissions). Then I double checked the privileges in the mysql db table itself for the connection user. I had Y's for select, insert, update, and delete for the database in question so that checked. I have googled for guidance and RTFM but can't find anything that tells me how to turn off the read only for the tables. This is probably something very simple to solve but my brain is starting to fail me. *help* Carol Andrejak Webmaster Delaware State University Grossley Hall Rm. 1 302-857-7045
Re: Errors with MySQL
Hi Rob, My guess would be you did not see this in the Fine Manual http://www.mysql.com/doc/en/Post-installation.html or this http://www.mysql.com/doc/en/Starting_server.html Hope this helps, Ken - Original Message - From: Rob Snow [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 6:15 AM Subject: Errors with MySQL Dear MySQL Firstly, let me say, I am very new to this. Secondly, I have been reading Larry Ullman's book PHP and MySQL to get started. I have installed PHP fine, and it is working well. I have followed the instructions for MySQL and it has installed a folder in my: usr/local/ folder called mysql-standard-4.0.15-apple-darwin6.4-powerpc with a shortcut to this folder called mysql. The book I'm reading states I should do the following to set permissions: cd /usr/local/mysql sudo echo sudo bin/mysqld_safe I do this and I get this result: Last login: Thu Nov 20 10:44:51 on ttyp1 Welcome to Darwin! [Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo Password: [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe [1] 3254 [Rob-Snows-Computer:/usr/local/mysql] robsnow% Starting mysqld daemon with databases from /usr/local/mysql/data 031120 11:01:23 mysqld ended [1]Done sudo bin/mysqld_safe [Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root password '' bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! [Rob-Snows-Computer:/usr/local/mysql] robsnow% The doesn't have any work arounds to this problem, and I cannot go any further with the tutorials without mySQL running/working. I do a test and I get this result in a browser: Warning : Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) in /Users/robsnow/Sites/mysql_test.php on line 2 Warning : MySQL Connection Failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) in /Users/robsnow/Sites/mysql_test.php on line 2 Larry Ullman's books states it should have the following result, and he uses the same installer I did: Last login: Thu Nov 20 10:44:51 on ttyp1 Welcome to Darwin! [Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo Password: [Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe [1] 3254 [Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root password '' [Rob-Snows-Computer:/usr/local/mysql] robsnow% For some reason my run states that the mysql has ended, should this be so? Can you please give me any advise on how to check that mySQL is running, how to check the version, and any answers to why I'm getting these errors? I would be most grateful for your assistance. Rob Snow Rob Snow Space may be the final frontier But its made in a Hollywood basement RHCP 'Californiacation - Californiacation 1999' [EMAIL PROTECTED] http://homepage.mac.com/robsnow -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.015/4.016 doesn't store special chars, converts to '?'
Hi, does anyone know how to solve the problem with MySQL 4.015/4.016 (on Win and Linux) that converts special characters like n-dash (if you hold down ALT and press 0150 on num keys) or typographic quotes (ALT+0132, ALT+0147) to question marks? Example: INSERT INTO test SET column = '\Hello there\'- '?Hello there?' It's got nothing to do with escaping quotes or whatever - it mus be something else. This worked without problems in MySQL 3.x but doesn't work anymore in MySQL 4. Strangely those chars are inserted via LOAD DATAFILE but not with INSERT or UPDATE statements. Does anyone have this problem as well? Thanks in advance! Torsten Roehr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
suddenly started crashing
Since yesterday I have noticed that mysql keeps crashing on me. At first I thought it was related to RAM, so I replaced the RAM. Problem persists, so now I am perplexed. HELP!! I am running a dual XEON 2.6ghz with 2GB ram. Linux 2.4.18-6mdkenterprise #1 SMP Fri Mar 15 02:28:20 CET 2002 i686 unknown All the db apps have been running for MONTHS and had no problems until yesterday. The query pointer in the .err log did not point out anything. Here is an output from my stack trace on two different crashes both occured today: 1st Crash: --- 0x8070670 handle_segfault + 420 0x8288b08 pthread_sighandler + 184 0x826c1b9 find_key_block + 369 0x826bd2e key_cache_read + 134 0x824c16a _mi_fetch_keypage + 58 0x8253f2c w_search + 96 0x8254093 w_search + 455 0x8254093 w_search + 455 0x8254093 w_search + 455 0x8254093 w_search + 455 0x8253d82 _mi_ck_write_btree + 142 0x8253ce9 _mi_ck_write + 65 0x825392f mi_write + 591 0x80c521d write_row__9ha_myisamPc + 101 0x80a3c25 write_record__FP8st_tableP12st_copy_info + 513 0x80a3544 mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates + 1168 0x807c47a mysql_execute_command__Fv + 6566 0x807ea75 mysql_parse__FP3THDPcUi + 153 0x8079fe3 dispatch_command__F19enum_server_commandP3THDPcUi + 1435 0x8079a3d do_command__FP3THD + 165 0x8079229 handle_one_connection + 641 0x82862bc pthread_start_thread + 220 0x82bba7a thread_start + 4 2nd Crash: 0x8070670 handle_segfault + 420 0x8288b08 pthread_sighandler + 184 0x80a36e2 mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates + 1582 0x807c51f mysql_execute_command__Fv + 6731 0x807ea75 mysql_parse__FP3THDPcUi + 153 0x8079fe3 dispatch_command__F19enum_server_commandP3THDPcUi + 1435 0x8079a3d do_command__FP3THD + 165 0x8079229 handle_one_connection + 641 0x82862bc pthread_start_thread + 220 0x82bba7a thread_start + 4 --- Ian Rubado 321.956.0019 OFFICE 321.956.0048 FAX 321.960.4264 GSM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to 'customize' GROUP BY?
Hi again... yet another question to this list that maybe someone can easily answer me... When I do a GROUP BY on a column of my query, I'll get one random row from the entire group. But how can I tell MySQL to, i.e., give me the row with the highest value of another column or so? I mean something like SELECT id, grp FROM tbl GROUP BY grp ORDER BY id but with the ORDER BY relating to the GROUP... I don't know how to express this in SQL since it doesn't seem to be possible? -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installing 3.23.57 with InnoDB
Fernando, maybe you are using a compiler which does not understand the inline defs in InnoDB. You can try CFLAGS=-DUNIV_MUST_NOT_INLINE ./configure --with-innodb make If the CFLAGS do not propagate to the /innobase subdir in the source tree, try to do a separate call CFLAGS=-DUNIV_MUST_NOT_INLINE ./configure make in the /innobase subdir, so that the Makefile's get that flag in the compilation line. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Fernando Alonso Renault [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, November 20, 2003 7:38 PM Subject: installing 3.23.57 with InnoDB I want to install mysql.3.23.57 (open source) in Linux, and i need to use innoDB tables i do the configure; ./configure --with-innodb and when i do the make, i get a lot of errors in ha_innobase.cc one of them is: . . . ha_innobase.o(.text 0x554): In function `innobase_commit_low(trx_struct*)': : undefined reference to `trx_commit_for_mysql' . . . why do i get this error? what should i do, apart from the ./configure ...? thanks http://webmail.wanadoo.es. Tu correo gratuito, rpido y en espaol -- 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: Adding mysql to the subject line would improve clarity and ease of classification.
I second the idea. Bao Hassan Farha wrote: It would be great if we could put 'mysql' in the subject of our questions. I receive mail from many different places and it would help classification. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk Error 22 WinServer 2003 Large table
Hello, Further searching for this problem reveals that it was reported as a bug: http://bugs.mysql.com/bug.php?id=779 but for Version 3. Does this mean that the fix was not implemented in Version 4? What is the workaround for this? Do I have to load the table while creating the indices? This runs for a VERY long time. The table I loaded is marked as broken - do I need to start over or can I fix it? The recommendation from myisamchk (use the -f to fix the table) resulted in the same Error 22. Unless I have missed something (likely g), this seems like a pretty major bug - is MySQL just not the right RDB to be using for tables this size? -Jim Gallagher Hello, I am following the advice of the manual when bulk loading a large table by turning off index builds before LOAD DATA INFILE. The load ends normally (73 GB). The mysamchk build of the indices fails: myisamchk: warning: Can't change size of indexfile, error: 22 myisamchk: error: 22 for record at pos 121201294124 myISAM-table 'mytable' is not fixed because of errors This process works successfully on small tables (37 GB) on another 2003 machine. What am I doing wrong? I'm new to MySQL, and am just finding my way. The command I'm issuing is: myisamchk -O sort_buffer_size=512M -O key_buffer_size=768M -O read_buffer_size=512M -O write_buffer_size=512M -rq d:\mysql\data\mydb\mytable I have 4GB of memory on the machine. I searched for this problem with Google and found several others with the same problem, but didn't find a solution. Thanks, Jim Gallagher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding mysql to the subject line would improve clarity and ease of classification.
Hassan Farha wrote: It would be great if we could put 'mysql' in the subject of our questions. I receive mail from many different places and it would help classification. Thanks! -Original Message- From: adburne [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 2:47 PM To: [EMAIL PROTECTED] Subject: Execute shell script There is a command or function to call a shell script through mysql? _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí If you're so worried about the ease of viewing messages in the list, why not start your own thread instead of piggy-backing onto a completely unrelated thread? -- 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]
Re: Adding mysql to the subject line would improve clarity and ease of classification.
At 10:52 -0500 11/20/03, Hassan Farha wrote: It would be great if we could put 'mysql' in the subject of our questions. I receive mail from many different places and it would help classification. Thanks! That's what mail filters are for. Here's how to set one up: http://lists.mysql.com/faq.php#subjectprefix -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transaction newbie question
OK, I don't seem to be getting a response. Let me try to simplify: I'm running a PHP application with MySQL. I'm in a transaction involving only BDB tables. Actually the whole database has nothing but BDB tables. I attempt a query which fails with error number 1213. The full text message is something like: Can't get lock because of a deadlock. My questions: 1. When the query fails am I still inside the transaction, or has it already been rolled back ? 2. If the transaction has not been rolled back, how many times should I re-try the query before giving up and re-starting the transaction ? 3. If the transaction has been rolled back, what is a reasonable number of times to re-start the transaction ? - Mike. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding mysql to the subject line would improve clarity and ease of classification.
Wasn't this addressed already? http://lists.mysql.com/faq.php#subjectprefix -will Real-time Chat: irc.freenode.net - #mysql ( http://www.mysql.com/doc/en/IRC.html ) - Original Message - From: BAO RuiXian [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 3:00 PM Subject: Re: Adding mysql to the subject line would improve clarity and ease of classification. I second the idea. Bao Hassan Farha wrote: It would be great if we could put 'mysql' in the subject of our questions. I receive mail from many different places and it would help classification. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding mysql to the subject line would improve clarity and ease of classification.
Be it addressed or not in the link, there is a difference between self-configuration and system-configuration. Best Bao William Fong wrote: Wasn't this addressed already? http://lists.mysql.com/faq.php#subjectprefix -will Real-time Chat: irc.freenode.net - #mysql ( http://www.mysql.com/doc/en/IRC.html ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to 'customize' GROUP BY?
Hi Yves, You can use a having clause to work with groups: select grp, max(id) as maxid from tbl group by grp having maxid = max(id) Regards, Peter Sap - Original Message - From: Yves Goergen [EMAIL PROTECTED] To: List: MySQL [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:28 PM Subject: How to 'customize' GROUP BY? Hi again... yet another question to this list that maybe someone can easily answer me... When I do a GROUP BY on a column of my query, I'll get one random row from the entire group. But how can I tell MySQL to, i.e., give me the row with the highest value of another column or so? I mean something like SELECT id, grp FROM tbl GROUP BY grp ORDER BY id but with the ORDER BY relating to the GROUP... I don't know how to express this in SQL since it doesn't seem to be possible? -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE optimization?
Hello guys, Let say: UPDATE some_table SET some_field=1 WHERE id = some_id and UPDATE some_table SET some_field=0 WHERE id some_id what I can do to merge these queries? The first thing that came up in my mind was something like that: UPDATE some_table SET some_field=1 WHERE id = some_id; SET some_field=0 WHERE id some_id; so we can walk-through table only once... What do you think? Regards, Julya href='http://mail.bg/ads/adclick.php?bannerid=860amp;zoneid=13amp;source=amp;ismap=' target='_blank'p - http://www.stantek.combr/ ,br/ ,br/ /p/adiv id=beacon_860 style=position: absolute; left: 0px; top: 0px; visibility: hidden;img src='http://mail.bg/ads/adlog.php?bannerid=860amp;clientid=273amp;zoneid=13amp;source=amp;block=0amp;capping=0amp;cb=522e3492978f772735809616c5e107b2' width='0' height='0' alt='' style='width: 0px; height: 0px;'/div
Update optimization?
Hello guys, Let say: UPDATE some_table SET some_field=1 WHERE id = some_id and UPDATE some_table SET some_field=0 WHERE id some_id what I can do to merge these queries? The first thing that came up in my mind was something like that: UPDATE some_table SET some_field=1 WHERE id = some_id; SET some_field=0 WHERE id some_id; so we can walk-through table only once... What do you think? Regards, Julya href='http://mail.bg/ads/adclick.php?bannerid=875amp;zoneid=13amp;source=amp;ismap=' target='_blank'br !br 3000 br http://www.femalelife.bgbrbr /adiv id=beacon_875 style=position: absolute; left: 0px; top: 0px; visibility: hidden;img src='http://mail.bg/ads/adlog.php?bannerid=875amp;clientid=289amp;zoneid=13amp;source=amp;block=0amp;capping=0amp;cb=ceb8e5e897af6979fb06b546b520fb00' width='0' height='0' alt='' style='width: 0px; height: 0px;'/div
Re: Transaction newbie question
Hi Mike, 1. It has already been rolled back, so do a start transaction again. 2. - 3. It depends, 3 to 5 times would be reasonable. Before resubmitting the transaction wait some time (let's say half a second) to let the conflicting transaction finish. When you keep getting deadlocks, try switching to InnoDB (row locks in stead of page locks) and take another look at the tranactions themselve like the sequence of insert/delete/update/select. Hope this helps. Peter Sap. - Original Message - From: Mike Gollub [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:38 AM Subject: Re: Transaction newbie question OK, I don't seem to be getting a response. Let me try to simplify: I'm running a PHP application with MySQL. I'm in a transaction involving only BDB tables. Actually the whole database has nothing but BDB tables. I attempt a query which fails with error number 1213. The full text message is something like: Can't get lock because of a deadlock. My questions: 1. When the query fails am I still inside the transaction, or has it already been rolled back ? 2. If the transaction has not been rolled back, how many times should I re-try the query before giving up and re-starting the transaction ? 3. If the transaction has been rolled back, what is a reasonable number of times to re-start the transaction ? - Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE optimization?
Hi, You can combine those 2 UPDATEs like this: UPDATE some_table SET some_field=IF(id=some_id, 1, 0); Or, the standard SQL syntax: UPDATE some_table SET some_field=CASE id WHEN some_id THEN 1 ELSE 0 END; Hope that helps. Matt - Original Message - From: [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 6:39 PM Subject: UPDATE optimization? Hello guys, Let say: UPDATE some_table SET some_field=1 WHERE id = some_id and UPDATE some_table SET some_field=0 WHERE id some_id what I can do to merge these queries? The first thing that came up in my mind was something like that: UPDATE some_table SET some_field=1 WHERE id = some_id; SET some_field=0 WHERE id some_id; so we can walk-through table only once... What do you think? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to 'customize' GROUP BY?
That won't do what it looks like he wants. GROUP BY can't do that :( I wish it could. This issue recently came up for me, and I initially was using Temporary table, but then had to switch to a 'pregrouped' table so I could retrieve data properly. -Original Message- From: Peter Sap [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 4:25 PM To: List: MySQL Subject: Re: How to 'customize' GROUP BY? Hi Yves, You can use a having clause to work with groups: select grp, max(id) as maxid from tbl group by grp having maxid = max(id) Regards, Peter Sap - Original Message - From: Yves Goergen [EMAIL PROTECTED] To: List: MySQL [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:28 PM Subject: How to 'customize' GROUP BY? Hi again... yet another question to this list that maybe someone can easily answer me... When I do a GROUP BY on a column of my query, I'll get one random row from the entire group. But how can I tell MySQL to, i.e., give me the row with the highest value of another column or so? I mean something like SELECT id, grp FROM tbl GROUP BY grp ORDER BY id but with the ORDER BY relating to the GROUP... I don't know how to express this in SQL since it doesn't seem to be possible? -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- 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: Update optimization?
UPDATE some_table SET some_field=IF(id = some_id,1,0) Have fun, Stephen - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 21, 2003 11:41 AM Subject: Update optimization? Hello guys, Let say: UPDATE some_table SET some_field=1 WHERE id = some_id and UPDATE some_table SET some_field=0 WHERE id some_id what I can do to merge these queries? The first thing that came up in my mind was something like that: UPDATE some_table SET some_field=1 WHERE id = some_id; SET some_field=0 WHERE id some_id; so we can walk-through table only once... What do you think? Regards, July -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
search engine
Hi All,
say i would like to search for customer, entrying some text
then i would like mysql to return all the customer which id and full name consist of
the text
ex:
IDName
John01John The Junior
Abe01 Abe The Senior
Jo01 Johns The Best
if i enter the criteria 'john' i would like the result of John01 and Jo01
if i enter the criteria 'be' i would like the result of Abe01 and Jo01
if i use the fulltext index, then i could only match a full word, eg:
match(id,name) against('John') only resulting the field John01
i can use the clause
where id like '%text%'
or name like '%text%'
but it's wayyy to slow :)
anyone have a better idea.. i appreciate it so much
regards
-leo-
MYSQL suddenly started crashing
Sorry, I forgot to include the version of mysql. I am running 4.0.16 from the binary archive(tar.gz) on the mysql.com download page. I did have the same problem on 4.0.15 and upgraded yesterday to 4.0.16. I was running 4.0.15 since it came out before this problem suddenly reared its ugly head yesterday. If anyone has any input on the crash I am dealing with I will be eternally grateful. Ian Rubado -Original Message- From: Ian Rubado [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 5:07 PM To: [EMAIL PROTECTED] Subject: suddenly started crashing Since yesterday I have noticed that mysql keeps crashing on me. At first I thought it was related to RAM, so I replaced the RAM. Problem persists, so now I am perplexed. HELP!! I am running a dual XEON 2.6ghz with 2GB ram. Linux 2.4.18-6mdkenterprise #1 SMP Fri Mar 15 02:28:20 CET 2002 i686 unknown All the db apps have been running for MONTHS and had no problems until yesterday. The query pointer in the .err log did not point out anything. Here is an output from my stack trace on two different crashes both occured today: 1st Crash: --- 0x8070670 handle_segfault + 420 0x8288b08 pthread_sighandler + 184 0x826c1b9 find_key_block + 369 0x826bd2e key_cache_read + 134 0x824c16a _mi_fetch_keypage + 58 0x8253f2c w_search + 96 0x8254093 w_search + 455 0x8254093 w_search + 455 0x8254093 w_search + 455 0x8254093 w_search + 455 0x8253d82 _mi_ck_write_btree + 142 0x8253ce9 _mi_ck_write + 65 0x825392f mi_write + 591 0x80c521d write_row__9ha_myisamPc + 101 0x80a3c25 write_record__FP8st_tableP12st_copy_info + 513 0x80a3544 mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates + 1168 0x807c47a mysql_execute_command__Fv + 6566 0x807ea75 mysql_parse__FP3THDPcUi + 153 0x8079fe3 dispatch_command__F19enum_server_commandP3THDPcUi + 1435 0x8079a3d do_command__FP3THD + 165 0x8079229 handle_one_connection + 641 0x82862bc pthread_start_thread + 220 0x82bba7a thread_start + 4 2nd Crash: 0x8070670 handle_segfault + 420 0x8288b08 pthread_sighandler + 184 0x80a36e2 mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates + 1582 0x807c51f mysql_execute_command__Fv + 6731 0x807ea75 mysql_parse__FP3THDPcUi + 153 0x8079fe3 dispatch_command__F19enum_server_commandP3THDPcUi + 1435 0x8079a3d do_command__FP3THD + 165 0x8079229 handle_one_connection + 641 0x82862bc pthread_start_thread + 220 0x82bba7a thread_start + 4 --- Ian Rubado 321.956.0019 OFFICE 321.956.0048 FAX 321.960.4264 GSM -- 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: How to move database to new server
i think this can be done by dumping your database to a file and then fetch it to mysql cli i make a batch script to do this daily... i have not use replication, so i just copy the entire database (in windows version): c:\mysql\bin\mysqldump.exe -a -c -C -e --add-drop-table --add-locks -F -f -Q -h theSourceHost --user=theUser --password=thePassword -l -n -r c:\dumpFile.sql -B theDatabase c:\mysql\bin\mysql.exe -h theTargetHost --user=theUser --password=thePassword c:\dumpfile.sql i hope this help... cmiiw regards -leo- ps: you can see the online help for all the parameter - Original Message - From: Andrew Simpson To: [EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:06 PM Subject: How to move database to new server Hi I have a mysql database with 20 tables containing data whch i need to transfer to a new hosting server (i access this server via webmin.) What's the easiest way to move a database from one server to the other?. Can you export full databases from mysql (this would be the easiest), or do i need to create the database and all the tables on the new server first and then find a way to just export and import the data?. TIA
Re: Adding mysql to the subject line would improve clarity and ease of classification.
You can sort using the TO: field. Try filtering mail containing '@lists.mysql.com' in the recipient fields. At 17:52 Thursday 20/11/03, Hassan Farha wrote: It would be great if we could put 'mysql' in the subject of our questions. I receive mail from many different places and it would help classification. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
