RE: help!
I know i have to connect first to the server. I tried issuing the command like this: mysql mysql -h localhost -u root -p; but it says error in syntax. if i take out the semi colon, the shell would just add a '-' below. You are already running mysql by typing that. You should have read the last line when starting mysql: Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql expects you to type in one of the commands listed by help; or some SQL-statements. There are some gui-tools for mysql-administration, e.g. http://phpmyadmin.net http://www.mysql.com/products/mysqlcc/ another that might be of interest, when my winmysqladmin runs at startup, in the environment tab says 'Not Found Driver 3.51 Not Found'. Is this related? Not really. winmysqladmin looks for the MyODBC-driver. I don't think you currently need that. P.S.: http://www.catb.org/~esr/faqs/smart-questions.html Regards, TomH -- PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121 Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.3 won't autostart on Mac OS X?
I've installed mysql-max-4.1.3-beta-apple-darwin7.4.0-powerpc to my PowerBook and have executed the MySQLStartupItem.pkg. The /Library/StartupItems/MySQLCOM exists and the /etc/hostconfig file includes the string MYSQLCOM=-YES-. However, the only way I can get MySQL to start after boot up is to open a terminal and kick it off manually. sudo /Library/StartupItems/MySQLCOM/MySQL start Does anyone have any idea what I've done wrong? Thanks in advance and please excuse me if there is a better list for a question of this nature. I'm new here. Regards, Bob __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query plan
Hi all, How can I get the query plan output? thanks alot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: special order
that will work..
or if you can guarantee that it always prefixed with '#' you can try
select id from mydata
order by if(left(id,1)='#',concat('z',id),id)
or if you didnt sure.. you can try the similiar way..
select id from mydata
order by if(left(id,1)'A',concat('z',id),id)
just make sure that you change the first letter to something greater
than 'z' (ps: that if you want it ordered character first followed by
anything else)
select id from mydata
order by if(left(id,1)'A',concat('z',id),id)
thats just a theory, i have not try it my self :)
and you should considered that it will processed all row (in other
word.. slooowww.. if you have many records)
HTH
Leo
On Sun, 22 Aug 2004 21:03:42 -0400, Michael J. Pawlowsky
[EMAIL PROTECTED] wrote:
select id from mydata order by name.
If that puts the #125 first etc. simply created a field called
'order_field' or whatever and put numeric values in them.
The SELECT id, name FROM mytable ORDER by order_field.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Root password lost?; can't manage existing database...
Hi the usual syntax is mysql -u root -p if that does not work look into skip grant tables option http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html HTH Peter -Original Message- From: DBS [mailto:[EMAIL PROTECTED] Sent: 23 August 2004 03:50 To: [EMAIL PROTECTED] Subject: Root password lost?; can't manage existing database... Hi, I have a problem, It's been months since I used MySQL and (I believe) I had set it up with a root password. Now I can't log on to MySQL as root MySQL user and create a new user or manage an existing user (I can log onto server as root of course). I'm running MySQL 3.23 and RH Linux 8. The one database that is running is running fine and no problems with it. It has run so well with no problems I never tinker with it. When logged onto server as root, I enter the command mysql -p -u root I get the error message: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) Also when I type in: 'mysql -p -u userone' I also get the same error so apparently I can't access the userone either. I know what the password is for userone but I don't understand why MySQL is not allowing me to enter it in. Actually I'm not even sure if I created a root password for MySQL. I do know that I created a few users and a database which functions correctly and has been for months - it's linked to a discussion forum and works fine. However now I can't create any new users, and if I wanted to change the password of the existing user - database, or root pw, I can't, or don't know how. Can anyone give me a hint as to what I need to do to either - a. reset the password for the root MySQL user (or log on as MySQL root without a MySQL pw) so I can create new databases, manage the existing database which is associated with a user. b. create a new user whether logged on as root or as the user name of which I want to create the new MySQL database and tables for. Thanks for any advice, DBS -- 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]
Table needs to be repaired once a week
Hi,
we are experiencing a strange problem with a table of our MySQL-Server.
About every week this table gets corrupt and we have to repair it
manually. Besides a couple other tables and databases there is another
table who gets corrupted but not that often. Both tables are the
biggest on the system (15MB and 45MB). We did some stress-tests but
we could not reproduce the error. We do not know where the problem is
(hardware, software) and would appreciate any hints or links...
So far, we got down to the following:
- A repair table always works in seconds
- No heavy traffic (two insert/minute) on the table
- The table has an average rowlength of 2KB.
We enabled the logging and got the following output:
General Query Log:
040707 10:01:39 18922 Connect [EMAIL PROTECTED] on
18922 Init DB xxx
18922 Query select id, nickname, email, blocked
from user where session='1234' and confirmed='1' and
blocked='0'
18922 Query select id from email_block where
email='[EMAIL PROTECTED]'
18922 Query select id, nickname, blocked from
user where email='[EMAIL PROTECTED]' and confirmed='1' and blocked='0'
18922 Query select id from game where
gameid='1234'
18922 Query insert into game
(gameid,insel,startpunkt,nacht,spieler1,spieler2,farbe1,farbe2,model1,model2,zeit1,zeit2,rennen1,rennen2,time1,time2,hide1,hide2,gmail,nachricht,zzeiten1,
zzeiten2) values
('1234',2,4,'0',3952,0,12,0,8,0,32249,0,'-6000,-5996,-5983,-5961,-5931,-5890,-5862,-5840,-5830,-5845,-5874,-5884,-5858,-5788,-5677,-5544,-5375,-5178,-4977,-4790,-4649,-4520,-4370,-4190,-3995,-3785,-3568,-3339,-3104,-2869,-2645,-2427,-2247,-2153,-2123,-2124,-2156,-2240,-2379,-2544,-2661,-2682,-2684,-2714,-2820,-2990,-3190,-3406,-3615,-3796,-3943,-4077,-4176,-4220,-4240,-4253,-4262,-4245,-4217,-4186,-4154,-4133,-4120,-4109,-4098#1060,1053,1027,969,874,744,582,394,183,-46,-282,-520,-756,-983,-1193,-1391,-1557,-1689,-1818,-1966,-2155,-2355,-2539,-2695,-2832,-2947,-3049,-3105,-3095,-3058,-2981,-2882,-2734,-2528,-2297,-2059,-1824,-1603,-1414,-1244,-1045,-927,-825,-697,-576,-504,-447,-371,-265,-114,72,270,486,718,955,1195,1434,1673,1911,2149,2387,2625,2865,3105,3344','',1089187299,0,'0','0','[EMAIL PROTECTED]','','11916,19000,24999','')
18922 Quit
040707 10:01:53 18923 Connect [EMAIL PROTECTED] on
18923 Init DB xxx
18923 Query select id, email, points, lose,
wins, blocked from user where nickname='xxx' and password='xxx' and
confirmed='1' and blocked='0'
18923 Query update user set
session_date=1089187313, session='1234' where id=6436
18923 Query select count(distinct points) as
anzahl from user where points87 and blocked='0'
18923 Query select count(id) as anzahl from game
where gmail='[EMAIL PROTECTED]' and spieler2=0 and hide2='0'
18923 Query select count(id) as anzahl from game
where spieler1=6436 and spieler2=0 and hide1='0'
## This (could be better programmed) request fails about once a day
and the table has been repaired by an automatically repair-skript:
18923 Query select count(distinct g.id) as
anzahl from game g where (g.spieler1=6436 and g.spieler20 and
hide1='0') or (g.spieler2=6436 and g.spieler10 and hide2='0')
18923 Init DB xxx
18923 Query SHOW TABLES
18923 Query REPAIR TABLE `game`
040707 10:01:54 18924 Connect [EMAIL PROTECTED] on
18924 Init DB xxx
18924 Query select id, email, points, lose,
wins, blocked from user where session='12345' and
confirmed='1' and blocked='0'
18924 Query update user set
session_date=1089187314, session='12345' where
id=3952 18924 Query select count(distinct
points) as anzahl from user where points477 and blocked='0'
18924 Query select count(id) as anzahl from game
where gmail='[EMAIL PROTECTED]' and spieler2=0 and hide2='0'
040707 10:01:56 18924 Query select count(id) as anzahl from game
where spieler1=3952 and spieler2=0 and hide1='0'
18924 Query select count(distinct g.id) as
anzahl from game g where (g.spieler1=3952 and g.spieler20 and
hide1='0') or (g.spieler2=3952 and g.spieler10 and
hide2='0') 18924 Query select count(distinct
g.id) as anzahl from game g where g.spieler1=3952 and g.spieler20
18924 Quit
040707 10:01:57 18923 Quit
/var/log/mysql/mysqld.log:
040707 10:01:56 Note: Found 25256 of 25257 rows when repairing
'./xxx/game'
Errors:
MySQL-Check-Statement: e.g. Wrong bytesec 0-0-0 at linkstart 43473336
MySQL-Repair-Statement: Number of rows changed from ...
Error-Log:
040528 22:30:56 mysqld started
040528 22:30:56 InnoDB:
Re: select count(*)
Eko Budiharto [EMAIL PROTECTED] wrote: I am trying to find how to use command select count(*). I tried it in mysql query console, it shows the number of my rows in my table. But when I tried it in perl with $sth-do(select count(*) from mytable) and run the perl script from DOS console, I always gets 1 in value. What is the right way to do it in order to get the number of rows in my table in return? I am looking forward to a favorable reply from you. Thank you. print $dbh-selectrow_arrayref(SELECT COUNT(*) FROM mytable)-[0], \n; -- 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: Table needs to be repaired once a week
Jens Bierkandt [EMAIL PROTECTED] wrote: we are experiencing a strange problem with a table of our MySQL-Server. [] System: Software: Installiertes OS: Fedora Core release 1 (Yarrow) Installierter Kernel: 2.4.22-1.2188.nptlsmp Filesystem: Software-Raid with ext3fs MySQL-Version: 4.0.18 Hardware: CPU:1 x Intel(R) Xeon(TM) CPU 2.80GHz Hyperthreading RAM:1 GB HDD:2 x MAXTOR ATLAS10K4_73SCA Is there anything we can also try to catch this problem? First, try to upgrade to current version (4.0.20) downloaded in binary RPM packages from www.mysql.com. This may or may not fix the problem, but worst a try. Jens Bierkandt -- 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: about creation error of full text index
°í ¿ìÁ¾ [EMAIL PROTECTED] wrote: mysql select count(*) from pages; ERROR 1016 (HY000): Can't open file: 'pages.MYI' (errno: 144) mysql repair tables pages; +---++--+-+ | Table | Op | Msg_type | Msg_text | +---++--+-+ | search2.pages | repair | error| 121 when fixing table | | search2.pages | repair | error| Can't copy datafile-header to tempfile, error 9 | | search2.pages | repair | status | Operation failed | +---++--+-+ [EMAIL PROTECTED] egor]$ perror 9 Error code 9: Bad file descriptor Are you sure this is not a problem with your OS and/or MySQL build? This may be a MySQL error in which case I will ask you to prepare a testcase for us to fix the bug. -- 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]
distributed queries
Is there any way to issue a distibute query with mysql, such as SELECT * from mytab JOIN (SELECT * FROM another_server_on_another_machine.MYTAB2) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Root password lost?; can't manage existing database...
DBS [EMAIL PROTECTED] wrote: I have a problem, It's been months since I used MySQL and (I believe) I had set it up with a root password. Now I can't log on to MySQL as root MySQL user and create a new user or manage an existing user (I can log onto server Familiar situation. :) Do so: service mysql stop wait until MySQL shuts down. Then run mysqld_safe --skip-grant-tables then you will be able to login as root with no password. mysql -uroot mysql In MySQL command line prompt issue the following command: UPDATE user SET password=PASSWORD(abcd) WHERE user=root; FLUSH PRIVILEGES; At this time your root password is reset to abcd and MySQL will now know the privileges and you'll be able to login with your new password: mysql -uroot -pabcd mysql -- 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: Dependency problem with MySQL rpm's (Fedora Core 1)
Frank Perez [EMAIL PROTECTED] wrote: I am trying to install MySQL 5.0.0 on a fresh install of Fedora Core 1, however I am running into a little trouble and I was hoping someone could give me a little insight on what I might be doing wrong. I am able to install the 'server', 'clients', and 'devel' packages with no problems, but if I try to install either the 'shared' or the 'Max' packages I get the following dependency errors: error: Failed dependencies: libcrypto.so.0.9.6 is needed by MySQL-shared-5.0.0-0 libssl.so.0.9.6 is needed by MySQL-shared-5.0.0-0 Download 5.0.1a. 5.0.0 is linked with SSL while 5.0.1a is not (which is right). -- 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: Re-installing db/tables from dumpfile
Joseph E. Maxwell [EMAIL PROTECTED] wrote: I am moving my database(s) from one server to another, did a full dump of the data and have a large file with all the create instruction sets etc. My question, what is the best way to recreate these db, tables etc. on the new server. MySQL is already installed running. run mysql -uroot -ppassword this_file.sql -- 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: LOAD DATA
[EMAIL PROTECTED] wrote: I use following code use databasea load data infileabcd.txt' into table databasea.tablename; Data gets loaded in table, however at the end of each record I see a square symbol, the symbol usually we see in compiled code. This may be a linefeed or carriage-return character. See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html note the LINES TERMINATED BY clause. -- 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: A new Machine
Info [EMAIL PROTECTED] wrote: After 2 days in Microsoft HELL with my SQLsvr databases, I'm ready to = rob the piggy bank and build a new linux mysql server. This seem to be a good fruit of your repentance. :) I'm not rich, but what hardware and distro do you experts suggest? (My = current Win2K server is a dual p3-650, 1gb with the databases on 2 36gb = U160 10K drives. ) I've got no problem moving the drives out of that = system (especially since I just bought a new one...)-- (I'd put my = redhat 8 on it this afternoon, except it also runs my exchange server = and that's a different migration...)=20 Classical Red Hat distro is a dead end, because their Red Hat 9 distro is the last one and there are only commercial versions now. You can use any Linux distro you like because we suggest you to run binary versions downloaded from www.MySQL.com, and they are linked statically. The hardware is fine, disks are the bottleneck of any database, not the CPU. If the performance of the current system is fast enough for you - then newer system will not give you significant enhancement. -- 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]
How to get the last record from the slected record set
I am having more than 10 records in a table. I want to select only first top 10 records (depending on one field score) and then want to select 10th position record. select * from table1 order by score desc LIMIT 10 This will give me 10 records but then how to get the last record ? Thanks in advance, regards Manisha
Re: query plan
Cao Duy [EMAIL PROTECTED] wrote: How can I get the query plan output? See http://dev.mysql.com/doc/mysql/en/EXPLAIN.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]
LEFT JOIN table USING ..
SELECT associates.shift, concat(associates.firstname,',',associates.lastname) as Operator, mrdaily.kronoshours, mrdaily.pto LEFT JOIN mrcategories USING ( mrcategories.category='1', mrcategories.category='2', mrcategories.category='3' ) FROM associates, mrdaily WHERE associates.id=mrdaily.idassociates, mrdaily.id=mrentries.idddaily AND entrydate='2004-08-03' ORDER BY associates.shift, associates.lastname, associates.firstname; What I wanted is to put each category on a column on their own. I seem to misunderstand the usage of left join can anyone help me out here? and do mysql utilitize as follows in bash: LEFT JOIN mrcategories USING for i in mrcategories.category[MAX], do mrcategories.category=mrcategories.category[$i] done -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
--On 22 August 2004 20:31 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I am having more than 10 records in a table. I want to select only first top 10 records (depending on one field score) and then want to select 10th position record. select * from table1 order by score desc LIMIT 10 This will give me 10 records but then how to get the last record ? order by score asc limit 1 [i.e. turn it around and pick the 1st (which will be the last because it's ordered the other way)] :-) -Kp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt table, mysqld crashes, server crashes. Looking for a solution
Feedback [EMAIL PROTECTED] wrote: Well.. I had the same issue 2 weeks ago with another server from my DC. I told them it was their hardware, they didnt believe it, so i had to order a new server, and ... The same problem occurs... The axioma is: a user-land binary CANNOT hang the kernel. And if the whole server crashes, it's not related to software it runs. Maybe the kernel (and kernel options), but not the daemons. Thats like one on a million that its the hardware... -- 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: Fulltext performance problem.
Hi Fredrik,
a LEFT JOIN could be faster (I'm not sure, try it). And you don't have a
fulltext index on msg_header.list.
What about this?
SELECTmsg_header.bodyid, msg_header.id, msg_header.subject,
msg_header.mfrom, msg_header.date, msg_header.list
FROM msg_header
LEFT JOIN msg_body ON msg_header.bodyid = msg_body.id
WHERE msg_header.list LIKE 'LISTNAME%'
AND MATCH(msg_body.body) AGAINST('WORD');
Regards, Thomas Spahni
On Fri, 20 Aug 2004, Fredrik Carlsson wrote:
Hi all,
I'm running a small mail archive and have a little problem with the
fulltext search performance.
I really appreciate any tips/design suggestions (even if it dont have to
do with the search problem ;) ).
Database schema:
mysql describe msg_header;
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| id | int(11) | | PRI | NULL| auto_increment |
| parent | int(11) | YES | MUL | NULL||
| bodyid | int(11) | YES | | NULL||
| list| varchar(80) | YES | MUL | NULL||
| mfrom | varchar(80) | YES | | NULL||
| mto | varchar(80) | YES | | NULL||
| subject | varchar(200) | YES | MUL | NULL||
| mcc | varchar(80) | YES | | NULL||
| sdate | varchar(45) | YES | | NULL||
| batch | varchar(80) | YES | MUL | NULL||
| msgid | varchar(90) | YES | | NULL||
| date| datetime | YES | MUL | NULL||
+-+--+--+-+-++
12 rows in set (0.00 sec)
mysql describe msg_body;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| id| int(11) | | PRI | NULL| auto_increment |
| body | text| YES | MUL | NULL||
+---+-+--+-+-++
2 rows in set (0.00 sec)
index from msg_body;
*** 1. row ***
Table: msg_body
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*** 2. row ***
Table: msg_body
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*** 3. row ***
Table: msg_body
Non_unique: 1
Key_name: body
Seq_in_index: 1
Column_name: body
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
3 rows in set (0.00 sec)
The search querys using fulltext indexes takes around 1minute and no
one want to use a search that slow :/
The Query is the following:
SELECT msg_header.bodyid,msg_header.id,
msg_header.subject,msg_header.mfrom, msg_header.date, msg_header.list FROM
msg_header,msg_body WHERE msg_header.bodyid=msg_body.id AND
match(msg_header.list) against('LISTNAME')
AND match(msg_body.body) AGAINST('WORD');
For a couple of month ago the msg-body and msg-headers parts where in
the same table and the fulltext search was really fast 1 sec, but
everything else just became slower so i splitted it upp in two tables.
But now i need to match msg_header.bodyid against msg_body.id to be able
to now witch body that belongs to with header and i think thats where
things get slow..
I ran an explain select query and the following result turned upp
++--+-+-+-+---+--+-+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
++--+-+-+-+---+--+-+
| msg_header | fulltext | list_4 | list_4 | 0
| |1 | Using where |
| msg_body | eq_ref | PRIMARY,id,body | PRIMARY | 4 |
msg_header.bodyid |1 | Using where |
++--+-+-+-+---+--+-+
Does this means that the fulltext index on msg_body.body is'nt being used?
The machine is an Intel PIII 500Mhz, 512MB memory and IDE disks running
NetBSD 1.6.3, mysql 4.0.20.
The database contains about 300 000 rows and the size is ~1GB.
// Fredrik Carlsson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL
Re: Innodb table definitions
Mayuran Yogarajah [EMAIL PROTECTED] wrote: Each |MyISAM| table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition. The data file has an `.MYD' (MYData) extension. The index file has an `.MYI' (MYIndex) extension. My question is, why does MySQL create a .frm (table definition) for a table if that table is of type InnoDB. For example, consider the following: The answer is there, right in your posting: An `.frm' file stores the table definition. -- 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: How to get the last record from the slected record set
I am having more than 10 records in a table. I want to select only first top 10 records (depending on one field score) and then want to select 10th position record. select * from table1 order by score desc LIMIT 10 This will give me 10 records but then how to get the last record ? Cycle them all until you hit the last one? 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: problem with tables crashing
Johan Jonkers [EMAIL PROTECTED] wrote: and my table is marked crashed :-( Please please upgrade to latest version (4.0.20) in binary format (download from www.mysql.com) and check on it. Good chances are that everything will run fine. -- 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: Mysqlcheck -r very slow on big tables
Konstantin Yotov [EMAIL PROTECTED] wrote: mysqlcheck -r is very slow when repair big tables (over 4GB data - repair it 1h and 40m). Is there any config option to fasten it. Check your hard drives setup and throughput, it's not related to MySQL. -- 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: how to increase max allowed keys?
íÉÈÁÉÌ íÏÎÁÛ£× [EMAIL PROTECTED] wrote: Mihail, suda ne nado pisat' s russkim From :-) How i can increase max allowed keys in myisam tables? I find this sting in myisam.h: #define MI_MAX_KEY 32 /* Max allowed keys */ Is it so simple? Or I must correct somthing more? Yes, you can just enlarge this value and rebuild MySQL. -- 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: Kernel panic when mysql stop command issued
Demetrios Stavrinos [EMAIL PROTECTED] wrote: Has any one heard or seen anything like it? Sure. A hardware or kernel problem. Get a stock kernel from ftp.kernel.org and compile a clean kernel for your server if you know how to do it. -- 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: Can't start mysql server automatically
Michael Stassen [EMAIL PROTECTED] wrote: O Well, clearly LD_LIBRARY_PATH isn't set at startup. That's one of the reasons LD_LIBRARY_PATH isn't really the Solaris way, and is not recommended. With a proper setup, you should never need LD_LIBRARY_PATH in Solaris. In any version of Solaris, you can build with -R /usr/local/lib to create executables which know to look in /usr/local/lib to find libraries at run time. Yes and also do ldd /usr/local/lib/libstdc++.so.5 to check if everything else is found there. -- 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]
second write process hangs if another one is writing already,!!bu t in another database!!] bug???
From: root To: Subject: [second write process hangs if another one is writing already,!!but in another database!!] Description: We have this mysql-server with two databases internet and intranet.There are two data-collectors server1 and server2.Each writes in one database. Server1 in internet, server 2 in intranet.The problem is, if writing process(f.e.:Server1) is active and second one(server2) starts then the process of (server2) hangs.The only way to get this hanging process out of the system is to kill the process(id) in servername.pid.Afterwards killall -15 mysqld, then stop server and start it again.If I don´t do this way I have to reboot because the pidd of other msql processes block the restart of the server.(strange,isn`t it). Write -and- Read is possible without problems. But writing two processes in different databases is not possible at same time How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: more than one writeprocess impossible Severity: [ critical ] Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.20-standard (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.20, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.20-standard Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 7 days 1 hour 31 min 24 sec Threads: 2 Questions: 104795562 Slow queries: 7 Opens: 12664 Flush tables: 1 Open tables: 62 Queries per second avg: 171.716 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux malz.erlm.siemens.de 2.4.22-1.2197.nptlsmp #1 SMP Thu Jul 1 15:05:23 EDT 2004 i686 i686 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.3.2/specs Konfiguriert mit: ../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.3.2 20031022 (Red Hat Linux 3.3.2-1) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='gcc' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 22. Jul 16:34 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x 1 root root 1570384 7. Jan 2004 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2445156 27. Okt 2003 /usr/lib/libc.a -rw-r--r-- 1 root root 204 27. Okt 2003 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' 'CXX=gcc' mit freundlichem Gruß / kind regards Daniel Beuter SIEMENS IS IT PS 221 OP4 Network Solutions Werner von Siemens Str. 60 91052 Erlangen Fon: +49 (9131) -6303-211 Fax: +49 (9131) 7-42234 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
RE: A new Machine
Well Actually the current Red Hat Release is Fedora Core 2 , which works very well. You can find it here http://www.redhat.com/fedora/ Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Monday, August 23, 2004 7:24 AM To: [EMAIL PROTECTED] Subject: Re: A new Machine Info [EMAIL PROTECTED] wrote: After 2 days in Microsoft HELL with my SQLsvr databases, I'm ready to = rob the piggy bank and build a new linux mysql server. This seem to be a good fruit of your repentance. :) I'm not rich, but what hardware and distro do you experts suggest? (My = current Win2K server is a dual p3-650, 1gb with the databases on 2 36gb = U160 10K drives. ) I've got no problem moving the drives out of that = system (especially since I just bought a new one...)-- (I'd put my = redhat 8 on it this afternoon, except it also runs my exchange server = and that's a different migration...)=20 Classical Red Hat distro is a dead end, because their Red Hat 9 distro is the last one and there are only commercial versions now. You can use any Linux distro you like because we suggest you to run binary versions downloaded from www.MySQL.com, and they are linked statically. The hardware is fine, disks are the bottleneck of any database, not the CPU. If the performance of the current system is fast enough for you - then newer system will not give you significant enhancement. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? regards Manisha - Original Message - From: Karl Pielorz [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 23, 2004 8:34 PM Subject: Re: How to get the last record from the slected record set --On 22 August 2004 20:31 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I am having more than 10 records in a table. I want to select only first top 10 records (depending on one field score) and then want to select 10th position record. select * from table1 order by score desc LIMIT 10 This will give me 10 records but then how to get the last record ? order by score asc limit 1 [i.e. turn it around and pick the 1st (which will be the last because it's ordered the other way)] :-) -Kp -- 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 VARCHAR TO CHAR automatically when the length less than 4.
Emi Lu [EMAIL PROTECTED] wrote: I have a questions about varchar columns change to CHAR columns automatically. See http://dev.mysql.com/doc/mysql/en/Silent_column_changes.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]
Re: release all mysql locks
Mayuran Yogarajah [EMAIL PROTECTED] wrote: Is there any command I can issue to release ALL locks held by any/all transactions ? I know that restarting the server does this, but is there a way to do this without restarting ? No. -- 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: How to get the last record from the slected record set
yes, but is there any better way of doing it ? regards Manisha - Original Message - From: Karl Pielorz [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 23, 2004 8:34 PM Subject: Re: How to get the last record from the slected record set --On 22 August 2004 20:31 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I am having more than 10 records in a table. I want to select only first top 10 records (depending on one field score) and then want to select 10th position record. select * from table1 order by score desc LIMIT 10 This will give me 10 records but then how to get the last record ? order by score asc limit 1 [i.e. turn it around and pick the 1st (which will be the last because it's ordered the other way)] :-) -Kp -- 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 VARCHAR TO CHAR automatically when the length less than 4.
I have a questions about varchar columns change to CHAR columns automatically. See http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html In addition to that - it doesn't really matter as the CHAR datatype isn't properly implemented in MySQL and behaves the same as the VARCHAR datatype. 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: How to get the last record from the slected record set
* Manisha Sathe I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? Try this: order by score desc LIMIT 9,1 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
miving dbs etc from one machine to another
have MySQL 4.0.20 on my mac os x 10.3.x client machine. My question is, how do I go about getting my MySQL databases that I created to another computer. I have been told my databases are in /usr/local/mysql/data. When I try to get into the sub-directory /data it says I do not have permission. How do I give myself this permission? Then how do I get my databases on cd, floppy disk, or whatever media I choose to send to another place across state? Thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert data from text file
Should mysql version 4.0.20 support the Insert data from a textfile into table which is shown at the bottom of the page while in phpmyadmin with a database and table selected? I am getting this error: #1148 - The used command is not allowed with this MySQL version. Thanks for any help, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
Manisha Sathe wrote: I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? regards Manisha order by score desc limit 9,1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
showing dbs in terminal window opposed to phpmyadmin
How come when I am using the terminal on my mac os x 10.3.x and give the command show databases I only see one database (the 'test' database that is included in the install') as compared to viewing databases with my browser and phpmyadmin I can see 6 databases (that same 'test' database, a 'mysql' database that came with the install, and 4 other databases I created myself while in phpmyadmin)? Shouldn't I have access to all of them from the terminal as well? Thanks again, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
--On 22 August 2004 21:05 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? I'm not quite sure I follow what you're aiming for, but have you tried... limit 10,1 i.e. return 1 record, starting at row 10 in the result set. Beyond that you'd have to explain what 'score' is, as it's getting a little confusing... :) -Kp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about [mysql defunct] processes
Ken Sorensen [EMAIL PROTECTED] wrote: I am stumped. I have a very busy MySQL db that we've run for about 3 years with no problems. A couple of months ago we upgraded the system to RH-3AS with RedHat's distro mysql-server 3.23.58-1.9. Recently I've started getting zombie processes ([mysql defunct]). Remove the RedHat's distro mysql-server and install properly built MySQL binary version in RPM format. Download it from www.mysql.com. -- 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: showing dbs in terminal window opposed to phpmyadmin
Are you loggin into the terminal and PHPmyadmin as the same user? -Original Message- From: Hull, Douglas D To: Note To MySQL List (E-mail) Sent: 8/23/04 8:30 AM Subject: showing dbs in terminal window opposed to phpmyadmin How come when I am using the terminal on my mac os x 10.3.x and give the command show databases I only see one database (the 'test' database that is included in the install') as compared to viewing databases with my browser and phpmyadmin I can see 6 databases (that same 'test' database, a 'mysql' database that came with the install, and 4 other databases I created myself while in phpmyadmin)? Shouldn't I have access to all of them from the terminal as well? Thanks again, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL query designer for *nix?
Hello. I'm looking for a schema designer / relations editor which is GUI, supports MySQL and runs on *nix, either commercial or freeware. Can you suggest me something? -- 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: How to get the last record from the slected record set
Hi, I don'e know if this will help as I am probably only one step ahead of you here, but if it does great. If not you can just delete it. How are you going to access the data? Via a program, PHP, using queries? I am a newbie to this area myself, and use Delphi. I know that using Delphi with the DB components I can simply tell the Navigator to get the last record. Fairly straight forward. I have recently found that using the DB componensts (so I am told) creates a local dataset which means that ALL rows selected are transferred to a local dataset for you to have a simple goto last record. i.e. If we have 1 million rows, each row contains 10 integers then opening a table to the data and telling it to go to last record will cause 10 million integers on the server to transfer to a local dataset on your PC/workstation for you to process. This is not really an issue if the datasets are created on the same computer as the Server. (I am learning that) if data transfer is an issue and could cause problems then a little bit of SQL is more than useful. I continue to learn about the SQL formats. With the help of another newish MySQL writer (he is one step ahead of me) Tom gave me some help that I have turned into this. For workstations needing to gain access to 1 row at a time and NOT wanting to create a large dataset on my workstation, which may have a slow connection I do the following (in pseudo code): Get First Record SQL.Text = SELECT * FROM MyTable Where MyField ORDER BY MyIndex LIMIT 1; ExecuteSQL.Text; Get Last Record SQL.Text = SELECT * FROM MyTable ORDER BY MyIndex DESC LIMIT 1; ExecuteSQL.Text; When I get a record I remember the unique value of the index that I am scrolling through. Let us say I store the value of MyField of the current row in a variable called MyKey then the next record is where MyField MyKey and you limit the number of rows to return by 1. Get Next Record SQL.Text = SELECT * FROM MyTable Where MyField MyKey ORDER BY MyIndex LIMIT 1; ExecuteSQL.Text; if Dataset.IsEmpty then GetLastRecord(MyMatter,MyIndex); Get Previous Record SQL.Text = SELECT * FROM MyTable Where MyField MyKey ORDER BY MyIndex DESC LIMIT 1; ExecuteSQL.Text; if Dataset.IsEmpty then GetFirstRecord(MyMatter,MyIndex); The above constructs work if the column you are scrolling through is unique. The issue is when you have a non unique order e.g. Surname. This is where Tom's more advanced knowledge of SQL helped be get over the problem. SELECT * FROM table WHERE MyField = '' AND UniqueId -1 ORDER BY MyField,UniqueId LIMIT 0,1 You will need to have a column containing a Unigue ID to do this (indexing this will also give you extra performance) The clever bit is that we are creating a temporary sort order which is a combination of the required field sequence and the unique ID sequence which will, by definition, give us a order with a Unique sort sequence. Now you will need to know the values from the current Row for the columns MyField and UnigueID, let us say OldMyField and OldUniqueID. We will then get Get the next record: SELECT * FROM table WHERE MyField = OldMyField AND UniqueId OldUniqueID ORDER BY MyField,UniqueId LIMIT 1 The other Get Record types are then derivations, but you should be able to write generic function/subtroutines based upon what you want. This is what I am doing at the moment and the performance over PC's using slow connection links to not so fast servers is proving to be quite successful. For my not very big tables I will probably not bother to implement the calls as I can use the generic components to write quick interfaces as the local dataset issue won't be a problem. I hope this makes sense! Kerry -Original Message- From: Manisha Sathe [mailto:[EMAIL PROTECTED] Sent: 22 August 2004 14:08 To: [EMAIL PROTECTED] Subject: Re: How to get the last record from the slected record set yes, but is there any better way of doing it ? regards Manisha - Original Message - From: Karl Pielorz [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 23, 2004 8:34 PM Subject: Re: How to get the last record from the slected record set --On 22 August 2004 20:31 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I am having more than 10 records in a table. I want to select only first top 10 records (depending on one field score) and then want to select 10th position record. select * from table1 order by score desc LIMIT 10 This will give me 10 records but then how to get the last record ? order by score asc limit 1 [i.e. turn it around and pick the 1st (which will be the last because it's ordered the other way)] :-) -Kp -- 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:
Re: MySQL query designer for *nix?
Hi, you might want to try this: DBDesigner from FabForce. http://www.fabforce.net/dbdesigner4/ Regards, Jens Egor Egorov wrote: Hello. I'm looking for a schema designer / relations editor which is GUI, supports MySQL and runs on *nix, either commercial or freeware. Can you suggest me something? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy
Hi, I'm trying to use mysqlhotcopy to make a backup of some mysql databases. But when i try to run mysqlhotcopy I'm getting this error, [EMAIL PROTECTED] Temp]# mysqlhotcopy -u root dbxpto /root/Temp DBD::mysql::db do failed: You have an error in your SQL syntax near '` READ, `dbxpto`.``certificate`` READ, `dbxpto`.``crl`` READ, `dbxpto`.``c' at line 1 at /usr/bin/mysqlhotcopy line 438. Can some one tell me where is the problem ? My system is a Fedora Core 1, the mysql is mysql-3.23.58-4 and is the rpm version of Fedora. Thank's ND -- Nuno Dias [EMAIL PROTECTED] LIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set - Thanks
Thanks to all of, I could get it Thanks Manisha - Original Message - From: Karl Pielorz [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 23, 2004 9:52 PM Subject: Re: How to get the last record from the slected record set --On 22 August 2004 21:05 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? I'm not quite sure I follow what you're aiming for, but have you tried... limit 10,1 i.e. return 1 record, starting at row 10 in the result set. Beyond that you'd have to explain what 'score' is, as it's getting a little confusing... :) -Kp -- 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]
super-smack installation problem
I'm trying to configure and install super-smack-1.2 and get a problem on configure. it's looking for mysql_real_connect and can't find it...where would this be located? I'm assuming it is in libmysqlclient.so, and I have that file in my /usr/lib/mysql folder, among other places...but it doesn't find it... the error it actually gives is this: checking for libmysqlclient... checking for mysql_real_connect in -lmysqlclient... no configure: error: Could not find libmysqlclient in ' /usr/lib /usr/lib/mysql /usr/local/lib /usr/local/lib/mysql/usr/local/mysql/lib' and when I do a find: [EMAIL PROTECTED] super-smack-1.2]# find / -name libmysqlclient* /usr/lib/mysql/libmysqlclient.a /usr/lib/mysql/libmysqlclient.la /usr/lib/mysql/libmysqlclient_r.a /usr/lib/mysql/libmysqlclient_r.la /usr/lib/libmysqlclient.so.10 /usr/lib/libmysqlclient.so /usr/lib/libmysqlclient.so.10.0.0 /usr/lib/libmysqlclient.so.12 /usr/lib/libmysqlclient.so.12.0.0 /usr/lib/libmysqlclient_r.so /usr/lib/libmysqlclient_r.so.10 /usr/lib/libmysqlclient_r.so.10.0.0 /usr/lib/libmysqlclient_r.so.12 /usr/lib/libmysqlclient_r.so.12.0.0 /usr/local/mysql/lib/mysql/libmysqlclient.so.12.0.0 /usr/local/mysql/lib/mysql/libmysqlclient.so.12 /usr/local/mysql/lib/mysql/libmysqlclient.so /usr/local/mysql/lib/mysql/libmysqlclient.la /usr/local/mysql/lib/mysql/libmysqlclient.a /mysqlsource/mysql-4.0.20/libmysql/.libs/libmysqlclient.so.12.0.0 /mysqlsource/mysql-4.0.20/libmysql/.libs/libmysqlclient.so.12 /mysqlsource/mysql-4.0.20/libmysql/.libs/libmysqlclient.so /mysqlsource/mysql-4.0.20/libmysql/.libs/libmysqlclient.a /mysqlsource/mysql-4.0.20/libmysql/.libs/libmysqlclient.lai /mysqlsource/mysql-4.0.20/libmysql/.libs/libmysqlclient.la /mysqlsource/mysql-4.0.20/libmysql/libmysqlclient.la I have installed the MySQL-compat and the MySQL-devel for 4.0.20 any ideas? thanks, -L Luke Crouch 918-461-5326 [EMAIL PROTECTED]
into outfile
SELECT foo INTO OUTFILE '\\l030k12\qcda\2004-08-03.html' WHATEVER foo; Is that possible? I seem to be doing it wrong. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: into outfile
Scott, At 11:58 AM 8/23/2004, you wrote: SELECT foo INTO OUTFILE '\\l030k12\qcda\2004-08-03.html' WHATEVER foo; Is that possible? I seem to be doing it wrong. select * into outfile 'p:\win2ktmp\mytest.txt' from mytable where order by Make sure the output directory exists and make sure the into Outfile filename appears before the from statement. BTW, you have .html as a file extension. MySQL only outputs in text (Into OutFile) or binary (Into DumpFile) files, not HTML. If you want HTML then you need to format the thml table it yourself in whatever language you prefer. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: into outfile
If you wanted html output you could do this mysql -uuser -p -H -equery_to_run webFile.html ... The -H switch produces HTML output. -Original Message- From: mos To: [EMAIL PROTECTED] Sent: 8/23/04 12:11 PM Subject: Re: into outfile Scott, At 11:58 AM 8/23/2004, you wrote: SELECT foo INTO OUTFILE '\\l030k12\qcda\2004-08-03.html' WHATEVER foo; Is that possible? I seem to be doing it wrong. select * into outfile 'p:\win2ktmp\mytest.txt' from mytable where order by Make sure the output directory exists and make sure the into Outfile filename appears before the from statement. BTW, you have .html as a file extension. MySQL only outputs in text (Into OutFile) or binary (Into DumpFile) files, not HTML. If you want HTML then you need to format the thml table it yourself in whatever language you prefer. Mike -- 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: into outfile
At 12:59 PM 8/23/2004, you wrote: If you wanted html output you could do this mysql -uuser -p -H -equery_to_run webFile.html ... The -H switch produces HTML output. It would be real nice if they added this feature to the Select statement. :-) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Reset a field
Hello I have a db with table usuarios I erased all data in this table but Id is a autoinc and he have the value of the last value. How I can Reset the field ID thanks --- Hola Yo tengo una db con una tabla usuarios, yo borre todos los campos de la db pero el campo id que un autonumerico que se incrementa automaticamente se queda con el valor del ultimo campo, como puedo restablecer el valor de Id a 0 cada vez que limpie la db??? gracias Yusdaniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to Reset a field
Try alter table usuarios auto_increment = 1; -Original Message- From: Yusdaniel Rodriguez Espinosa To: [EMAIL PROTECTED] Sent: 8/23/04 9:15 AM Subject: How to Reset a field Hello I have a db with table usuarios I erased all data in this table but Id is a autoinc and he have the value of the last value. How I can Reset the field ID thanks --- Hola Yo tengo una db con una tabla usuarios, yo borre todos los campos de la db pero el campo id que un autonumerico que se incrementa automaticamente se queda con el valor del ultimo campo, como puedo restablecer el valor de Id a 0 cada vez que limpie la db??? gracias Yusdaniel -- 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 Reset a field
You could also have done this: TRUNCATE TABLE usarios; That will drop the table completely and then recreate it anew (and empty). It will give you a fresh start. --V Victor Pendleton wrote: Try alter table usuarios auto_increment = 1; -Original Message- From: Yusdaniel Rodriguez Espinosa To: [EMAIL PROTECTED] Sent: 8/23/04 9:15 AM Subject: How to Reset a field Hello I have a db with table usuarios I erased all data in this table but Id is a autoinc and he have the value of the last value. How I can Reset the field ID thanks --- Hola Yo tengo una db con una tabla usuarios, yo borre todos los campos de la db pero el campo id que un autonumerico que se incrementa automaticamente se queda con el valor del ultimo campo, como puedo restablecer el valor de Id a 0 cada vez que limpie la db??? gracias Yusdaniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Testing for the existence of an index
I have a database where, most of the time, I'm bulk-loading data into new tables from an external source, several million rows at a time. For this purpose, I create the tables without indexes, and then add all my indexes after the load is done, for efficiency purposes. I'd now like to add the possibility of adding some data to the existing database. In this case, the indexes exist, and then the new data will be indexed as it goes in (which is OK given the relatively small amount of data to be processed this way). I'd like to be able to test for the existence of an index, so that after the bulk-load, I can see if there are indexes, there won't be any, and I can create them; but after an addition to an existing database, there will be indexes, and I won't create them. How do I do this? It wasn't clear from the manual, and I'm away from my books now so can't look there for advice. I'm using Perl to process the data, if there's a Perlish way of doing things that would be easier than SQL. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about creation error of full text index
I think that it is not a OS problem or mysql built. I tested it in many version of mysql and I created the records from web by crawling. so whenever I test, the records are NEW. but the same error must occur to. I think it is so curious that it makes no error in small scale tables. I think there is a specific problem-make record. but I can't distinguish it from a gigabytes table. http://bugs.mysql.com/bug.php?id=5141 in this page, I write the url which can download the sample table. thanks for your answering. Woojong Koh From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: about creation error of full text index Date: Mon, 23 Aug 2004 14:57:43 +0300 [EMAIL PROTECTED] wrote: mysql select count(*) from pages; ERROR 1016 (HY000): Can't open file: 'pages.MYI' (errno: 144) mysql repair tables pages; +---++--+-+ | Table | Op | Msg_type | Msg_text | +---++--+-+ | search2.pages | repair | error| 121 when fixing table | | search2.pages | repair | error| Can't copy datafile-header to tempfile, error 9 | | search2.pages | repair | status | Operation failed | +---++--+-+ [EMAIL PROTECTED] egor]$ perror 9 Error code 9: Bad file descriptor Are you sure this is not a problem with your OS and/or MySQL build? This may be a MySQL error in which case I will ask you to prepare a testcase for us to fix the bug. -- 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] _ ... MSN ! http://groups.msn.com/?pgmarket=ko-kr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Testing for the existence of an index
Why don't you disable the indexes before the load and enable the indexes after the data load? -Original Message- From: Jesse Sheidlower To: [EMAIL PROTECTED] Sent: 8/23/04 3:33 PM Subject: Testing for the existence of an index I have a database where, most of the time, I'm bulk-loading data into new tables from an external source, several million rows at a time. For this purpose, I create the tables without indexes, and then add all my indexes after the load is done, for efficiency purposes. I'd now like to add the possibility of adding some data to the existing database. In this case, the indexes exist, and then the new data will be indexed as it goes in (which is OK given the relatively small amount of data to be processed this way). I'd like to be able to test for the existence of an index, so that after the bulk-load, I can see if there are indexes, there won't be any, and I can create them; but after an addition to an existing database, there will be indexes, and I won't create them. How do I do this? It wasn't clear from the manual, and I'm away from my books now so can't look there for advice. I'm using Perl to process the data, if there's a Perlish way of doing things that would be easier than SQL. Jesse Sheidlower -- 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: Testing for the existence of an index
Why don't you download navicat or use php admin and then just open the table up in design view? You can then see if it has an index or not and add one if you need? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Reset a field
Donde estas tratando hacer eso, en el command line cierto? O estas trabajando con mysql front o con algun otra programa? Perdon estoy muy nuevo no entiendo donde hacer alter table usuarios auto_increment = 1, mil gracias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection pooling with C?
I have an app I'm using (I have source, but I'm not the programmer), that works pretty good, but it seems to be speed-limited because of the time to connect to the mysql server. I run with IP's only, no DNS lookups and such, and things are better, but I think it could improve. Is there a C based connection pooling thingamabob I can use, like mysql_pconnect, and some external demon of some kind to assist? Thanks in advance for any help. I can find tons of stuff for perl/web based stuff, but I need it for C. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help for a query
Hi, Here's a table of mine namedate changeDate n1d1 cd1 n2d1 cd3 n2d2 cd1 n4d1 cd2 n1d2 cd5 n5d1 cd4 n6d2 cd2 I need to select every name for which the changeDate corresponding to d1 is greater than the changeDate corresponding to d2. Any way I can use one statement to do this? Thank you very much. Claire __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
anonymous localhost user in mysql user grant table
What's the purpose of the entry in the user table where it has host='localhost' and user='' (blank) and all of the permissions set to N? In other words, is this some sort of security safety net and it would be dangerous to delete it? Or is it an example of setting up an anonymous locahost user and it's safe to delete it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing for the existence of an index
On Mon, Aug 23, 2004 at 03:45:28PM -0500, Victor Pendleton wrote: Why don't you disable the indexes before the load and enable the indexes after the data load? If I'm bulk-loading a fresh install of data, then I'll be using un-indexed tables and index them afterwards. Otherwise, I want to keep the indexes on the table, and index as I go along (so that when I'm done I don't have to re-index from scratch). In response to the other poster, who suggested opening the table up in some utility: it's not a problem for _me_ to determine if I'm bulk-loading a new batch, or adding to an existing database. I want my loading program to be able to determine this without any input from me. Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL query designer for *nix?
Hi Jens and Egor, This is a very nice little product unfortunately it only appears to compile on Linux not other *nixes. If anybody can get it to compile can they please let me know? It works great on shudder Windows shudder. However, it certainly connects with MySQL (is inbuilt) and is easy to use. Thanks David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Jens Bierkandt [mailto:[EMAIL PROTECTED] Sent: Tuesday, 24 August 2004 12:12 AM To: Egor Egorov Cc: [EMAIL PROTECTED] Subject: Re: MySQL query designer for *nix? Hi, you might want to try this: DBDesigner from FabForce. http://www.fabforce.net/dbdesigner4/ Regards, Jens Egor Egorov wrote: Hello. I'm looking for a schema designer / relations editor which is GUI, supports MySQL and runs on *nix, either commercial or freeware. Can you suggest me something? -- 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: Can't start mysql server automatically
Also you can also use the crle command to build a permanent set of library paths similar to ldconfig in Linux and other unices. There should be a man page for it on your system. You can simply add /usr/local/lib to there in the correct order and this resolves having to use LD_LIBRARY_PATH. Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Monday, 23 August 2004 10:26 PM To: [EMAIL PROTECTED] Subject: Re: Can't start mysql server automatically Michael Stassen [EMAIL PROTECTED] wrote: O Well, clearly LD_LIBRARY_PATH isn't set at startup. That's one of the reasons LD_LIBRARY_PATH isn't really the Solaris way, and is not recommended. With a proper setup, you should never need LD_LIBRARY_PATH in Solaris. In any version of Solaris, you can build with -R /usr/local/lib to create executables which know to look in /usr/local/lib to find libraries at run time. Yes and also do ldd /usr/local/lib/libstdc++.so.5 to check if everything else is found there. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Figuring out the ranking position of an item on a table given its partnumber
Hello All, I have a table that has a PartNumber and the Quantity of Items sold for each partNumber, that is: PartNumber Qty I need to get its sales raking given its PartNumber, that is. So if I order the table by Qyt in descending order the first record will be the partNumber that sold the most. If I want to know what is 123 raking position according to that. Is there any easy way to do it? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dynamic Queries followup
Thanks again for a wonderful detailed response. The part that was still bothering me was the multiple-same-category fields. I get locked into this thinking and well, it's difficult to let go. Then I tried something on a table that had MemID, Doc1, Doc2, Doc3 I had put Doc1 in a few hours earlier. I went back to the form, not an update but thought a regular insertion on Doc2 would work, but mysql spit back a duplicate record error. Then I started thinking, well even if I had an update form, would I be able to just enter Doc2, and it retained Doc1 from the first insertion. Then the lightbulb went off in my head and it all made sense to me. Everything you said and all the examples. Plus I realized I was getting into a horrible habit early on. So to confirm before proceeding. I have a form where I allow 10 categories of certifications. Now to correct it I'm thinking of creating a certifications table. Then if ID 15 wants to enter 10 certifications it would look like the following in the certifications table (one column to search from): ID Certification 15 C# 15 DCOM 15 MCS 15 Cisco 15 Red Hat 15 MySQL 15 Oracle 9 I think this is right. Please let me know what you think. Stuart --- Rhino [EMAIL PROTECTED] wrote: This note is a followup to the thread entitled Dynamic Queries which was initiated yesterday by Stuart Felenstein. Stuart, I am deliberately replying to your private followup question on the list rather than privately because this thread could potentially benefit other MySQL users who might be having the same question today or some time in the future. Having this discussion on the list means that others can benefit from it; if we have this discussion privately, you and I are the only ones that benefit. Also, having the discussion on the list is a good sanity check: if I say something that is unclear or even flat-out wrong, there is a chance that others reading this thread will see it and correct us both. Otherwise, I could inadvertenly mislead you and cause you all kinds of grief. First things first, Stuart. On re-reading my reply to your question, I came across some typos that could cause confusion so let me take the liberty of repeating my reply, including the relevant bits of your questions, with my amendments in square brackets: -- Not sure what to call what I'm attempting to do, decided on dynamic queries. I should mention that I've been working with databases for just a little over a month. Example could be many web sites, but let's say Expedia (the travel site..booking flights, cars, etc) My understaning is that the SQL statements are contained in a recordset, but variables would be passed back into the sql statement based on the particulars of the user's input? Is that correct, is that the only way ? I've been working with relational databases, mostly DB2, since 1985 and I'm not entirely sure of the precise meaning of the term recordset. I've never seen a formal definition of that term. However, I'm pretty sure that a recordset is not the query that you pass to the database but the result that you get back. It's always been my practice to refer to the result of the query as a result set, not a recordset. I'm not trying to tell you not to use the term recordset - it might be widely used by some groups of people for all I know - I'm just trying to explain where I'm coming from. As to your specific question, yes, SQL statements can be written to contain variables. For example, if you were searching on Expedia, you could have a query that tells the database to return hotel names and addresses given a specific city and date, which the user supplies at execution time. The result set will then contain all of the rows that represent the prices of hotel rooms in that city on that date. In this case, the query would typically contain placeholders for the variables and the query would be prepared (compiled) without knowing the values of those placeholders. In other words, when the statement was prepared, the computer wouldn't know what city or date the user wanted. For example, the query might look like this: String query = select hotel_name, hotel_address, hotel_cost_per_night from hotels where city = ? and date = ?; That's how I would do it in Java. The question marks are the placeholders representing the variables for city name and date. It would also be possible to do it via string concatenation, although this can be bit problematic when the variables contain quotes or other special characters. For example: String query = select hotel_name, hotel_address, hotel_cost_per_night from hotels where city = + city_name + and date = + stay_date; You can also combine the two approaches. For example, String query = select
` question
Hit all, I have a question. I am working on a part inventory program for work. I'm writing it in PHP4. I got the connection ok, the form ok and all that. My problem came when I went to do the insert. I did INSERT INTO table VALUES 'val1', 'val2' etc well when I ran the page, it gave me a query error, no worries, I took the query and went to the db server, and it pitched a fit about it there. It's putting the ` instead of the ' We found I had a row called ID which was what we thought the problem was, so I dropped the table and made a new one, and on the insert is demanding the ` I want to know what. There is another couple of pages we have running that is using the ' like every php and mysql book I have say to use. Any Ideas or suggestions will be appreciated. Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why VARCHAR TO CHAR automatically when the length less than 4.
On Mon, 23 Aug 2004 15:25:21 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: I have a questions about varchar columns change to CHAR columns automatically. See http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html In addition to that - it doesn't really matter as the CHAR datatype isn't properly implemented in MySQL and behaves the same as the VARCHAR datatype. I don't believe that is correct. We recently had a very large disk space problem that was solved when we discovered that a column that should have been varchar was instead char, and was taking up excess storage because of it (which a datatype acting like varchar wouldn't do). -- Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help for a query
Hi Claire, On Mon, 23 Aug 2004 14:52:29 -0700 (PDT), Claire Lee [EMAIL PROTECTED] wrote: Hi, Here's a table of mine namedate changeDate n1d1 cd1 n2d1 cd3 n2d2 cd1 n4d1 cd2 n1d2 cd5 n5d1 cd4 n6d2 cd2 I need to select every name for which the changeDate corresponding to d1 is greater than the changeDate corresponding to d2. Any way I can use one statement to do this? Yes, of course. You seem to be suggesting that there will only be two records with the same name in the table. In that case, something like this (although this is untested): select name from mytable a where changedate (select changedate from mytable b where a.name=b.name and a.changedate != b.changedate); or: select name from mytable a where exists (select * from mytable b where a.name=b.name and a.changedate b.changedate); I personally like the latter, form-wise. I suspect it might be marginally faster, too. If my above assumption isn't the case, we need more information on what happens when there are 3 records (do you want any record which has changedate greater than another record of the same name? or only the highest? etc.). Also, mind your NULLs. You might need to edit the above query depending on how you want to handle NULLs in changedate. Good luck, -- Matt Warden Miami University Oxford, OH http://mattwarden.com This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Figuring out the ranking position of an item on a table given its partnumber
You can use LIMIT. For the 123rd position: SELECT PartNumber, Qty FROM your table ORDER BY Qty DESC LIMIT 122, 1; With 2 arguments, LIMIT treats the first as the offset to the starting row (0 = first row, so 122 = position 123) and the second as the number of rows to show. This is documented in the manual http://dev.mysql.com/doc/mysql/en/SELECT.html. Michael C.F. Scheidecker Antunes wrote: Hello All, I have a table that has a PartNumber and the Quantity of Items sold for each partNumber, that is: PartNumber Qty I need to get its sales raking given its PartNumber, that is. So if I order the table by Qty in descending order the first record will be the partNumber that sold the most. If I want to know what is 123 ranking position according to that. Is there any easy way to do it? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ` question
If your insert really looks like what you have here, it's not complaining
about the quote character. You have a syntax error. If you are inserting a
value in every column, you need
INSERT INTO table VALUES ('val1', 'val2', ...);
You left out the parentheses. See the manual for correct syntax
http://dev.mysql.com/doc/mysql/en/INSERT.html.
If that isn't the problem, please post the real query and the exact error
message so we don't have to guess.
Finally, ID is a perfectly good column name. See the manual for the details
on allowable names http://dev.mysql.com/doc/mysql/en/Legal_names.html.
Michael
Paul Metzger wrote:
Hi all, I have a question. I am working on a part inventory program for
work. I'm writing it in PHP4. I got the connection ok, the form ok and
all that. My problem came when I went to do the insert. I did INSERT
INTO table VALUES 'val1', 'val2' etc well when I ran the page, it gave
me a query error, no worries, I took the query and went to the db
server, and it pitched a fit about it there. It's putting the ` instead
of the ' We found I had a row called ID which was what we thought the
problem was, so I dropped the table and made a new one, and on the
insert is demanding the ` I want to know what. There is another couple
of pages we have running that is using the ' like every php and mysql
book I have say to use. Any Ideas or suggestions will be appreciated.
Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication eats 99% CPU
Hello everyone, The master is moderately busy with load average of 2-3. This has worked very well for a long time before I started replication (few months). When replication is started, both master and slave appear to work fine with no significant increase of load on either side. But, after a few (random) hours, the master mysqld process will start to use 99% CPU and the load will go to 3, 4, 6, 15, 24, 56, 312... in a matter of seconds! The master then stops responding and needs a reboot. My replication setup is as follows: Master Slave DB A == DB A DB B The master is setup with binlog-do-db=A and slave with replicate-do-db=A. Queries to A are mostly SELECT while to B are mostly INSERT, UPDATE DELETE. The queries that cause 99% CPU, as stated in slow.log, are not really CPU intensive. (slow.log indicates these queries took HOURS to run where they would normally finish in 1 or 2 seconds). These are the queries we run every minute and second, mysql handles these with no problem, why suddenly mysqld eats all CPU? Can anyone shed some light? Thanks in advanced. Regards, CK ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help me for my problem please
Hi All, Here's my table structure Table 1: STM (Store Master) STR_CODE Desc part_number Table 2 : MRC(Master Received) Ref_No Rec_date Table 3 : DRC (Detail Recived) Ref_no PO_No STR_CODE qty price Table 4: MPO (Master PO) PO_No PO_Date Table 5: OPO (OUT Standing PO) STR_CODE PO_No PO_date PO_QTY PP_date PP_No how to the query format, if i want to create the report, below: No. REF_NO REC_DATE PO_NO PO_DATEItem STR_CODE DESC PART_NUMBER PP_NO PP_DATE RCV.QTY ___ ___ __ __ __ _ __ ___ thanks before,
