Re: date problem
-do you have any index covering this query? -this is not a good practice to have function call(s) in the where clause, since mysql will make the fucntion call for every rows scanned. select count(*) as c from users_table where user_regdate 2002-06-01 00:00:00 -furthermore, in the where clause, = is always better than . select count(*) as c from users_table where user_regdate = 2002-06-01 00:00:01 andy wrote: Hi there, I would like to count the users out of a mysql db who registered after a certain date. The column I have in the db is a char and I do not want to change this anymore. This is how a typical entry looks like: May 29, 2002 This is how I tryed it: // while '10...' is unix timestamp june 1, 02 SELECT COUNT(*) AS c FROM users_table WHERE UNIX_TIMESTAMP( user_regdate ) '1022882400' Thanx for any help on that, andy query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Sammy Lau mailto: [EMAIL PROTECTED] - Tell me what you want and I'll tell you how you can live without it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MYSQL Relation n-m
I have a database on MySql divided into 3 tables books (idbook, and other fields) author (idauthor, and other fields) bookauthor (idauthor,idbook) This is to have more than 1 author for every book How can I make a query to select on one row all the authors of abook? Thanks Antonio - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problem
Hi, I already had a similar problem. I did the following things: I zipped my master database to unzip it on the slave and then I did a reset master,that's it. The reason why you have this problem it's certainly due to the fact that your master and slave database are not identical. you didn't talk about it, but I suppose that you granted the slave to make file access on master. http://www.mysql.com/doc/R/e/Replication_HOWTO.html Frederic - Original Message - From: Terry [EMAIL PROTECTED] To: Mysql@Lists. Mysql. Com [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 9:29 AM Subject: replication problem i am sending it again, maybe this time someone would help ... :) hello, i have major problem with setting up replication. both ends are running mysql 3.23.49 i have set up proper entries in my.cnf on both sides and have replicated the dbs. slave's data directory is exactly the same as master's data dir. then, i start master, then slave now, on master: show master status reads: ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | bdnotebook-bin.001 | 758 | | | ++--+--+--+ 1 row in set (0.00 sec) show processlist reads: | 4 | root | linux.compfort | NULL | Binlog Dump | 1212 | Slave connection: wa iting for binlog update | NULL | now on slave: show slave status reads: | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +-+-+-+---+- -+ -+---+-+ -+ ++--+ | 10.0.0.111 | rep | 3306| 30| | 35 | Yes | | | 0 || 0| +-+-+-+---+- -+ -+---+-+ -+ ++--+ show processlist: | 1 | system user | none | NULL | Connect | 294 | Waiting to reconnect after a failed read | NULL | in slave's logfiles i see these errors: 020531 14:04:08 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 35 020531 14:04:08 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 35 020531 14:04:08 Slave: received 0 length packet from server, apparent master shutdown: (0) and the databases are not being updated. what am doing possibly wrong ? have i missed something ? please help : terry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL Relation n-m
I have a database on MySql divided into 3 tables books (idbook, and other fields) author (idauthor, and other fields) bookauthor (idauthor,idbook) This is to have more than 1 author for every book How can I select with a query on one row all the authors of abook? Thanks Antonio - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: support UTF-8
Hi Shahzad, I also want to know whether mySQL support Unicode, utf-8 or not. If you know anything about this please answer me as soon as possible, Sincerely, Mehdi Zare -Original Message- From: shahzad sarwar [mailto:[EMAIL PROTECTED]] Sent: Monday, June 03, 2002 8:35 PM To: [EMAIL PROTECTED] Subject: support UTF-8 Hi , Do mysql ver 2.23.X support UTF-8 . If yes , to what extends. I have tried UTF-8 with mysql ver 3.23.37 on windows. It seems as that it support UTF-8 , but tools (utilities ) for mysql does not fully support utf . Have any body have tried that. Regards, shahzad sarwar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select DATE, help ...
* Tom Jones I have a table with 2 columns StartDate and EndDate. I can't seem to figure out how to write a select statement that will tell me if the date I'm searching on is between the Start and the End date. Like, I want to know if 7/4/2002 is between 6/1/2002 (startDate) and 8/1/2002 (endDate). Take a look at the BETWEEN operator: URL: http://www.mysql.com/doc/C/o/Comparison_Operators.html Something like this: SELECT * FROM MyTable WHERE '2002-07-04' BETWEEN startDate AND endDate; -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Maximum JOINED tables
* Harald Fuchs In article [EMAIL PROTECTED], Roger Baklund [EMAIL PROTECTED] writes: Yes... that is strange. There could be a bug in the crashme report, or the limit may have been lowered after 3.23.39, but neither of those seems very likely (to me, anyway)... anyone got a clue? I just tried a multiple self join on 4.0.1 and got the following DBD::mysql::st execute failed: Too many tables. MySQL can only use 31 tables in a join at ./x2.pl line 47. Me too, running 3.23.30-gamma on w2k: mysql desc select * from - tab1 t1,tab1 t2,tab1 t3,tab1 t4... ERROR 1116: Too many tables. MySQL can only use 31 tables in a join So... is the crashme report wrong? Or was the limit really 32 in 3.22.39, got lowered to 31 in later versions (like 3.23.30-gamma), raised to 63 in version 3.23.39, and then lowered to 31 again (at least in versions 3.23.47 and 4.0.1)...? URL: http://www.mysql.com/crashme (Select both mysql versions, submit, and search for tables in join on the result page.) -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I have problem with a Cyrillic
Anelia, Thursday, June 06, 2002, 12:25:25 AM, you wrote: AT Description: AT I have problem with a Cyrillic when I use clauses ORDER BY AT and LIKE. AT They didn't work correctly. What character set do you use? Change you charset to cp1251 or win1251 or koi8_ru. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Tinytext vs. char()
Hi! I store firstname, lastname, telephone etc in a table Is there any drawback with using tinytext? As I can see when I store strings that differ in length, the best would be to use tinytext, or? http://www.mysql.com/doc/n/o/node_369.html In terms of fulltextsearch/index etc. What is the difference if I use a tinytext as column type instead of a fixed char length? Best regards /Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: support UTF-8
Mehdi, Thursday, June 06, 2002, 11:27:24 AM, you wrote: MZ Hi Shahzad, MZ I also want to know whether mySQL support Unicode, utf-8 or not. MZ If you know anything about this please answer me as soon as possible, Unicode support will come in 4.1. Unicode in 4.1 will use UTF-8 and UCS2 with a possibility to recode between them (as well as between any supported character set). MZ Sincerely, MZ Mehdi Zare -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: More normalization
I think what you're getting at is that you're trying to consider both 'contactInfo' and 'entity' to be superclasses, with subclasses like 'phoneContactInfo', 'addressContactInfo', etc. and you'd like other tables to have fields which reference the superclass. Inheritance relationships are a common problem in relational databases. (In fact, these representational problems are not specific to databases, but to any data store which needs to cope with object-oriented data.) There are at least three common ways of approaching the subclassing problem, each with its own stengths and weaknesses: 1) Keep one table named after the superclass, add a 'type' field, and have lots of redundant fields. In your case, you'd have one table called 'contactInfo' and one table called 'entities', each with a type field and then lots and lots of fields: the union of the fields in your current tables. Joins between the superclasses are trivial: just use a single join table as you would for any many-to-many join. Joining just a specific subclass (like joining against just the 'postal' contact info) requires including the type field in the join. This approach is by far the simplest and often provides the best speed in relational databases, particularly when you only care about joins to the superclass, however you could potentially waste a lot of storage and it is certainly far from being 'normalized'. (This seems like a good time to point out that while normalization of your data model is almost always a good idea, full normalization of the RDBMS implementation of that data model is seldom crucial and is often sacrificed in favor of speed and/or storage optimizations.) This design occurs most often as an entity in the data model migrates from being a single type with a few variations to a set of distinct subtypes. 2) Keep one table for each of the subclasses, duplicating the superclass fields in each of those tables. This seems to be the situation you've described: tables like 'address', 'phone', and 'email'. If there were fields common to all subclasses, you would add them to each and every table; i.e if you wanted to add a 'date_entered' field to all contact information, then you'd add it to each table. One 'invisible' field which always needs to be duplicated is the primary key: if you consider all three tables members of a common superclass, then that superclass (with no actual representation in the database) must have a primary key, and of course that primary key must be unique across all three tables. You can make this implicitly true by making the primary key a combination of the particular subtype (which is stored implicitly in each table) and a type-unique identifier (a common auto-increment field). Joins to the superclass are accomplished by referencing not just the row's id in the table, but also the table type (it's a two-column primary key), joining against all the subclass tables with checks against type. Joins to the subclass are trivial joins against the row id. This system has very different performance characteristics from the first approach: there is very little wasted space (beyond the extra column in superclass foreign keys), however processing is substantially more complex (multiple left joins against constant comparision and row comparisions and overall nullity checks...) although indices will still provide equivalent algorithmic performance, assuming your number of subclasses is bounded. This technique is most appropriate when most of the joins are to specific subclasses. While this is a very popular approach, I've always felt it was messy and complicated to require everyone who references the superclass to store information that exists nowhere else in the database. At the very least, good documentation of your data model (with strong emphasis on the fact that these separate tables are in fact subclasses of an invisible superclass, and their common fields must be unioned to create the virtual superclass table) is essential. 3) Keep a superclass table and multiple subclass tables. In this case, all the information common to all subclasses is stored in a single table, like 'contactInfo', including some kind of primary key. There is then an additional table for each subclass with subclass-specific info: the 'phone' table would have area code, number, and extension, etc. There is a one-to-one relationship between each row in the subclass table and each row in the superclass table, and this is most commonly implemented by having the subclass table duplicate the superclass primary key, usually using it as its own primary key in the subclass domain. (Whether each superclass must be related to a row in one of the subclass tables is up to you, depending on whether or not you consider the superclass 'pure virtual' or not.) Joins against the superclass (and all superclass-type fields) are trivial by joining against the superclass table on the primary key, and joins against subclasses are joins against both the
Re: mysql client
Jetaime, Wednesday, June 05, 2002, 11:45:56 PM, you wrote: J how can i setup a mysql server on a machine that is on the network to accept J connections from my machine. I downloaded the client and there is currently J a server running on a nother machine on the LAN. I inputed it's ip address J and username:root password: password for root and it says can't coonnec to J the ip. The ip is correct. Do i have to configure the mysql server to accept J the coonnecting, is it by default setup to disallow such connections? How to connect to the remote MySQL server look at: http://www.mysql.com/doc/C/o/Connecting-disconnecting.html You should also set up privileges to allow connection from your box. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Insert into longblob column, it's limitation
Good Day
I will attempt to give you as much information about the software as I can
before I ask the question.
OS : RedHat 7.1
MySQL : Version 3.23.49a for Linux - binary distribution for pc-linux
I want to insert into longblob column very big large data.
but.. I have strange Errors...
Example..
mysql create table test (aa longblob);
mysql insert into test values(load_file('testfile.exe'));
ERROR 1030 : Got error 139 from table handler
mysql
./perror 139 - Too big row ( = 16M)
The size of testfile.exe is 16.3M
The table type of test is MYISAM
max_allowed_packet = 6700(64M?)
I don't know about this error...
So..
I want to know the limitation of longblob column for each table Type,
InnoDB, MYISAM.
And. How do insert into longblob column 4G data?
Thank you
sql,query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Is View supported?
Is View supported by MySQL? if not supported, is there any other way to achieve it? I have a table of history data because the table is growing too much so I will divide it into 12 but I therefore have to modify all the programs if I can have a view of the original name that select from all these 12 tables I can save a lots of program change thankx Borus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is View supported?
* Borus.Kung Is View supported by MySQL? no. if not supported, is there any other way to achieve it? I have a table of history data because the table is growing too much so I will divide it into 12 but I therefore have to modify all the programs if I can have a view of the original name that select from all these 12 tables I can save a lots of program change Take a look at MERGE tables: URL: http://www.mysql.com/doc/M/E/MERGE.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Users
Hi all How do i create users in mysql.. After installing i just start mysql and connect to it.. Whats the default username and password it uses.. -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Users
Hi! A little effort please http://www.mysql.com/doc/A/d/Adding_users.html and http://www.mysql.com/doc/D/e/Default_privileges.html EG Hi all How do i create users in mysql.. After installing i just start mysql and connect to it.. Whats the default username and password it uses.. -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql newbie show table question
* marc malacarme I can¹t seem to make any of the describe or show queries work when I apply a search statement: Query=show table status from demo; Works fine. Now I¹m trying to get info from only one table: Query=show table status from demo where name like user_access; Does no work. The manual is your friend: URL: http://www.mysql.com/doc/S/H/SHOW_TABLE_STATUS.html Syntax: SHOW TABLE STATUS [FROM db_name] [LIKE wild] In other words: remove where name from your query. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
New additions on MySQL++ page
Hi! There have been some new additions on the MySQL++ page. First of all, thanks to the efforts of Clint Savage [[EMAIL PROTECTED]] and Arturs Aboltins [[EMAIL PROTECTED]] we have two new files : * mysql++1_7_1win32_borland_1_2.zip a new version of MySQL++ for Borland C++ with many bug fixes * Documentation for installing, configuring and using MySQL++ on Windows operating systems Beside that a patch that enables usage of MySQL++ with GNU 3.* series compiler has been updated with a proper one. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL or MS Access ?
[snip] Lastly only a few people will be able to use you application before MS Access fails... Therefore limiting access to your mission critical applicaiton I do not really think MS Access is for Mission-Critical databases...but I guess that depends on ther definition of mission critical. Most MCP will agree that MS Access does not scale well and offers little advantages for Mission-Critical Enterprise Class Solutions. ... but this is a mission-critical commercial purpose database. Any comments, would be much appreciated. Thank you. [/snip] MS Access is NOT SUITABLE FOR MISSION-CRITICAL COMMERCIAL PURPOSES! Very limited, will slow to a crawl with 4 users connected, will likely crash with 6 connected. Does not support replication, does not have fail-safe mechanisms. Jay mysql, sql, query It's hip to snip! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql text search and fulltext
(first some info and 'things that I want', then a few questions) Hi I'm actually using MySQL 4.0.1-alpha in a production system now. Only using MyISAM tables. ( http://www.gnist.no ) The only problem I have is that sometimes the database crash with some fulltext searches... And with only some words (combination of words), not all. I have around 1-2 crashes every 24 hour. Queries per second avg: around 35-40 I'm really looking forward to the 4.0.2 release. I hope we will get some more options to tweak and customize the fulltext search/indexing. One other thing. I ran a Solid Database earlier, and they had support for indexed '%text%' search. Wildecard at start/end of search string. Why haven't MySQL support for this? Just wondering... But.. I can run resolve stackdump etc. from the #.err log, but the #.log does not show where the database went down and restarted. So I'm not able to see which query most probably caused the crash. Is there some other way? I'm not able to provide a 'test-case' before I'm able to find an example of a query which crash the database... Erlend Stromsvik PS. Any date on 4.0.2 release? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: processlist
If you include the following flag in the startup command, the logfile will be generated in the given location: --log=/var/log/mysqld.log Eivind sql, query - Original Message - From: Ritu Singla [EMAIL PROTECTED] To: Eivind A. Sivertsen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 6:52 AM Subject: Re: processlist Hi, Is this mysqld.log file created automatically or we need to do some settings in my.cnf to enable this logging of mysqld? Thanx in advance.. Ritu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Users
Grant select, insert, delete ... on db.tb || db || * to username@host identified by password Alexander -- Mensagem Original -- Hi! A little effort please http://www.mysql.com/doc/A/d/Adding_users.html and http://www.mysql.com/doc/D/e/Default_privileges.html EG Hi all How do i create users in mysql.. After installing i just start mysql and connect to it.. Whats the default username and password it uses.. -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php A busca mais veloz e precisa da internet. Acesse agora: http://www.zoom.com.br. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Merging Databases (Newbie)
Dear all, it's our first time to realize a project, based on mySQL Database and now we have a problem i hope we can solve easily. There are different partners in this project and the actual solution is based on a MS-Access Fronten (arrrggh!). Now the partner have to do some input and they all have their own mySQL server running. This must be done for a short time period until our appserver based solution is ready. So what i wanna know is, if there is a easy possibility to merge the data in one single database. Important in this situation maybe that every partner does only insert and update their own data and they do not manipulate data of others. Thanks for any hints... Frank - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: processlist
If you include the following flag in the startup command, the logfile will be generated in the given location: --log=/var/log/mysqld.log Eivind sql, query - Original Message - From: Ritu Singla [EMAIL PROTECTED] To: Eivind A. Sivertsen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 6:52 AM Subject: Re: processlist Hi, Is this mysqld.log file created automatically or we need to do some settings in my.cnf to enable this logging of mysqld? Thanx in advance.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Users
Arul, Thursday, June 06, 2002, 1:35:26 PM, you wrote: A How do i create users in mysql.. A After installing i just start mysql and connect to it.. A Whats the default username and password it uses.. Check our manual: http://www.mysql.com/doc/A/d/Adding_users.html http://www.mysql.com/doc/G/R/GRANT.html A -Arul -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql text search and fulltext
Erlend, Thursday, June 06, 2002, 2:53:02 PM, you wrote: EHS I'm actually using MySQL 4.0.1-alpha in a production system now. Only using EHS MyISAM tables. ( http://www.gnist.no ) EHS The only problem I have is that sometimes the database crash with some EHS fulltext searches... And with only some words (combination of words), not EHS all. I have around 1-2 crashes every 24 hour. EHS Queries per second avg: around 35-40 EHS I'm really looking forward to the 4.0.2 release. I hope we will get some EHS more options to tweak and customize the fulltext search/indexing. There is a lot of bugs fixed in full-test search in 4.0.2: http://www.mysql.com/doc/N/e/News-4.0.2.html EHS One other thing. I ran a Solid Database earlier, and they had support for EHS indexed '%text%' search. Wildecard at start/end of search string. Why EHS haven't MySQL support for this? Just wondering... MySQL doesn't use indexes in this case, because search word begins with wildcard. MySQL will use indexes in the following statement: SELECT * FROM table WHERE column LIKE text%; It will look for all words that =text and texu. for more details how MySQL uses indexes look at: http://www.mysql.com/doc/M/y/MySQL_indexes.html EHS Erlend Stromsvik -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tinytext vs. char()
Peter, Thursday, June 06, 2002, 12:29:53 PM, you wrote: PE I store firstname, lastname, telephone etc in a table PE Is there any drawback with using tinytext? PE As I can see when I store strings that differ in length, the PE best would be to use tinytext, or? PE http://www.mysql.com/doc/n/o/node_369.html PE In terms of fulltextsearch/index etc. PE What is the difference if I use a tinytext as column type PE instead of a fixed char length? CHAR column type has fixed length, use CHAR() if you know in advance how many characters are going to be in that field. CHAR will be slightly faster then tinytext. Don't forget that if the table contains variable-length fields (varchar, text, blob) then all char columns longer than 3 characters are silently changed to VARCHAR columns: http://www.mysql.com/doc/S/i/Silent_column_changes.html PE Best regards PE /Peter -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql query help
I need some help... The query below runs fine in access, when running it in mysql it tells me the column wrainfo.wuc doesn't exist in the having clause.. can't figure out why its giving me this error. the column is in the select statement... any ideas? The column does exist.. I'm looking at the database right now. Query: SELECT wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, min(updates.updates) as udate, wrainfo.wuc as srawuc, wucpartsinfo.notea, wucpartsinfo.noteb FROM (((wucpartsinfo LEFT JOIN parts ON wucpartsinfo.parts_id =parts.parts_id) LEFT JOIN updates on wucpartsinfo.wucpn_id=updates.wucpart_id) RIGHT JOIN wucinfo on wucpartsinfo.wuc_id=wucinfo.wuc_id) RIGHT JOIN wrainfo on wucinfo.wuc5 = wrainfo.wuc5 where wrainfo.WUC5 = '46X1B' group by wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, wrainfo.wuc, wucpartsinfo.notea, wucpartsinfo.noteb having (wucinfo.wuc wrainfo.wuc) order by wucinfo.wuc, wucpartsinfo.seq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
on update problem?
When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql query help
I've not looked over your query in-depth so I may be off-base here. Try removing the parentheses from the FROM clause and see if that makes it work. =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Scott Raley [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 7:23 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: mysql query help I need some help... The query below runs fine in access, when running it in mysql it tells me the column wrainfo.wuc doesn't exist in the having clause.. can't figure out why its giving me this error. the column is in the select statement... any ideas? The column does exist.. I'm looking at the database right now. Query: SELECT wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, min(updates.updates) as udate, wrainfo.wuc as srawuc, wucpartsinfo.notea, wucpartsinfo.noteb FROM (((wucpartsinfo LEFT JOIN parts ON wucpartsinfo.parts_id =parts.parts_id) LEFT JOIN updates on wucpartsinfo.wucpn_id=updates.wucpart_id) RIGHT JOIN wucinfo on wucpartsinfo.wuc_id=wucinfo.wuc_id) RIGHT JOIN wrainfo on wucinfo.wuc5 = wrainfo.wuc5 where wrainfo.WUC5 = '46X1B' group by wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, wrainfo.wuc, wucpartsinfo.notea, wucpartsinfo.noteb having (wucinfo.wuc wrainfo.wuc) order by wucinfo.wuc, wucpartsinfo.seq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: on update problem?
Hi! don't think ON UPDATE is supported. The manual : http://www.innodb.com/ibman.html doesn't mention anything about it except : Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. EG When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANN: EMS MySQL Manager 1.76 released
EMS HiTech company is announcing the next version (1.76) of MySQL Manager -- A Powerful MySQL Administration and Development Tool for Windows95/98/ME/NT/2000/XP. You can download the latest version from http://www.mysqlmanager.com/download.phtml What's new in version 1.76? 1. An ability to log all the SQL commands executed within MySQL Manager was added. Set the SQL Log options on the SQL Monitor page of the Environment Options dialog to enable or disable this kind of logging. 2. Sorting modes were added to the Code Completion popup list. Now you can choose how the items should be sorted in the list: alphabetically by name or by the item scope (SQL keywords, function, table, etc.). Just right-click on the list to call the local menu and switch the sorting mode. Coloring of the scope categories was also added to the Code Completion list. To enable or disable this feature just check or uncheck the Color Scope Categories option on the Quick Code page of the Editor Options dialog. 3. The list of external tools is stored in the Windows registry now. The proper option was added to the Save Settings wizard. 4. Some bugs in the Project View were fixed. Now you have an ability to rename the project folders and drag database objects into these folders. 5. Export As INSERT dialog: now the INSERT script is generated properly for the table with the name changed in the Table Name field. (*) 6. Fixed bug with editing the BIGINT fields in Grid View. Now you are able to change the field values using the spin buttons as well as direct typing. 7. Fixed bug with permanent Access Violation exception rise while MySQL Manager is running by Windows XP user, which has no administrator rights. 8. Some small improvements and minor bugfixes. (*) - Professional Edition only What is the EMS MySQL Manager? EMS MySQL Manager provides you powerful and effective tools for MySQL Server administration and objects management. Its Graphical User Interface (GUI) allows you to create/edit all MySQL database objects in a most easy and simple way, run SQL scripts, manage users and administrate user privileges, visually build SQL queries, extract or print metadata, export/import data, view/edit BLOBs and many more services that will make your work with the MySQL server as easy as it can be... Best regards, EMS HiTech development team. http://www.ems-hitech.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql query help
Actually it was the alias, but the query is producing the wrong results. It should produce 3 results which I get in Access I cut and paste this query into mysql and get 12371 results. Same databases. Cal Evans [EMAIL PROTECTED] on 06/06/2002 08:50:51 AM Please respond to [EMAIL PROTECTED] To: Scott Raley/LPEC/ASD/SEMCORINC@SEMCOR INC., [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject: RE: mysql query help I've not looked over your query in-depth so I may be off-base here. Try removing the parentheses from the FROM clause and see if that makes it work. =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Scott Raley [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 7:23 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: mysql query help I need some help... The query below runs fine in access, when running it in mysql it tells me the column wrainfo.wuc doesn't exist in the having clause.. can't figure out why its giving me this error. the column is in the select statement... any ideas? The column does exist.. I'm looking at the database right now. Query: SELECT wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, min(updates.updates) as udate, wrainfo.wuc as srawuc, wucpartsinfo.notea, wucpartsinfo.noteb FROM (((wucpartsinfo LEFT JOIN parts ON wucpartsinfo.parts_id =parts.parts_id) LEFT JOIN updates on wucpartsinfo.wucpn_id=updates.wucpart_id) RIGHT JOIN wucinfo on wucpartsinfo.wuc_id=wucinfo.wuc_id) RIGHT JOIN wrainfo on wucinfo.wuc5 = wrainfo.wuc5 where wrainfo.WUC5 = '46X1B' group by wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, wrainfo.wuc, wucpartsinfo.notea, wucpartsinfo.noteb having (wucinfo.wuc wrainfo.wuc) order by wucinfo.wuc, wucpartsinfo.seq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
installing mysql++-1.7.9 on Suse-Linux 8.0
hello, I hav compiled and installed mysql++ without automake or autoconf just by typing ./configure make make install (Typing auto... bevore compiling makes everithing worse and make does not work - I don't know if that is the reason for following issues...) I want to build a program with KDevelop connecting to a mysql-server. Wehen I now #include sqlplus.hh ond try to build, I get several messages that mysql.h, included in some other headers is not found. I 'solved' this problem by changing alle the headers's lines from #include mysql.h to #include mysql/mysql.h. Now all Files seem to be found but I get many other serious errormessages *g* gmake-START gmake all-recursive gmake[1]: Entering directory `/home/holger/devel/log_nw_status' Making all in log_nw_status gmake[2]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status' Making all in docs gmake[3]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs' Making all in en gmake[4]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs/en' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs/en' gmake[4]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[4]: Nothing to be done for `all-am'. gmake[4]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[3]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[3]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status' source='main.cpp' object='main.o' libtool=no \ depfile='.deps/main.Po' tmpdepfile='.deps/main.TPo' \ depmode=gcc /bin/sh ../admin/depcomp \ g++ -DHAVE_CONFIG_H -I. -I. -I.. -O2 -O0 -g3 -Wall -fno-exceptions -fno-check-new -c -o main.o `test -f main.cpp || echo './'`main.cpp In file included from /usr/local/include/coldata1.hh:9, from /usr/local/include/sqlplus.hh:12, from main.cpp:26: /usr/local/include/const_string1.hh: In method `const char const_string::at(unsigned int) const': /usr/local/include/const_string1.hh:50: exception handling disabled, use -fexceptions to enable /usr/local/include/const_string1.hh:52: warning: control reaches end of non-void function `const_string::at(unsigned int) const' In file included from /usr/local/include/sqlplus.hh:50, from main.cpp:26: /usr/local/include/sql_query2.hh: In method `class SQLString SQLQueryParms::operator [](const char *)': /usr/local/include/sql_query2.hh:9: warning: control reaches end of non-void function `SQLQueryParms::operator [](const char *)' /usr/local/include/sql_query2.hh: In method `const class SQLString SQLQueryParms::operator [](const char *) const': /usr/local/include/sql_query2.hh:13: warning: control reaches end of non-void function `SQLQueryParms::operator [](const char *) const' gmake[3]: *** [main.o] Error 1 gmake[3]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status' gmake[2]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status' gmake[1]: Leaving directory `/home/holger/devel/log_nw_status' gmake[2]: *** [all-recursive] Error 1 gmake[1]: *** [all-recursive] Error 1 gmake: *** [all] Error 2 *** failed *** gmake-END --compiler-info-- linux:/ # gmake -v GNU Make version 3.79.1, by Richard Stallman and Roland McGrath. Built for i686-pc-linux-gnu Copyright (C) 1988, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 2000 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Report bugs to [EMAIL PROTECTED]. linux:/ # gcc -dumpversion 2.95.3 linux:/ # g++ -dumpversion 2.95.3 linux:/ # cpp -dumpversion 2.95.3 --compiler-info-END-- I Would be happy if someone could help ;-( cu, H.S. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
I KNOW one of you LISTers has experienced this
I've asked this question once before with no response (and have asked on other lists), I'll give it one more try before giving in to semi-Gates domination (my last holdout is the Apache server I'm running): Upon installing MySQL as a service, why will it not start for all users? Only my admin account allows the MySQL service to start. I cannot start the service manually or automatically from any regular (power user) account. MySQL was installed as a service using mysqld-nt --install with no problems. I've come across a vague reference in the manual for a manual installation of MySQL as a service if problems occur, but have no idea if that is the course of action to take since I don't really know why there is a problem. Putting MySQL in the startup items makes no difference. HELP! -Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Are FOREIGN KEYs used?
I studied the MySQL and InnoDB manual, but I did not find anything about the internal usage of foreign keys. I mean if I define a foreign key it is good for me, because the InnoDB engine does not let the database to get integrity errors. But I think the foreign key definitions could be used in query optimizations, but I did not find anything about it. So, are foreign keys used in query optimizations in case of InnoDB databases to speed up performance? Thanks, Daniel Kiss - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: I KNOW one of you LISTers has experienced this
[snip] I've asked this question once before with no response (and have asked on other lists), I'll give it one more try before giving in to semi-Gates domination (my last holdout is the Apache server I'm running): Upon installing MySQL as a service, why will it not start for all users? Only my admin account allows the MySQL service to start. I cannot start the service manually or automatically from any regular (power user) account. MySQL was installed as a service using mysqld-nt --install with no problems. I've come across a vague reference in the manual for a manual installation of MySQL as a service if problems occur, but have no idea if that is the course of action to take since I don't really know why there is a problem. Putting MySQL in the startup items makes no difference. HELP! [/snip] Kirk, Are you starting MySQL on individual platforms? Are you starting it on a server and then attempting to connect to it from another user's computer? If the latter is the case you do not need to start MySQL, you just need to connect to it on the server it resides on. I think the lack of answer is due to the vaguery of the question. Maybe we can get going the right direction here. Thanks! Jay mysql, query, sql It's hip to snip! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Installation problems on AIX 4.3.3
Borus, Thanks for responding. I am attempting to install a binary release not build from source. Do you have any information regarding the errors in my original post? Thanks Morris Ford --- é¾?æf æ¨?(Borus.Kung) [EMAIL PROTECTED] wrote: FYI this may help Borus - Original Message - å¯?件è??: Morris Ford [EMAIL PROTECTED] æ?¶ä»¶è??: [EMAIL PROTECTED] å,³é??æ-¥æ?Y: 2002å¹´6æ?^6æ-¥ AM 11:17 主æ-¨: Installation problems on AIX 4.3.3 I am having a problem installing version 3.23.50 on AIX 4.3.3. When I execute mysql_install_db or try to start mysqld I get errors like 0509-036 Cannot load program ./bin/my_print_defaults because of the following errors: 0509-130 Symbol resolution failed for my_print_defaults because: 0509-136 Symbol stpcpy (number 45) is not exported from dependent module /usr/lib/libc.a(shr.o) 0509-136 Symbol strnlen (number 46) is not exported from dependent module /usr/lib/libc.a(shr.o) I have chased through the mail archives and found messages with errors like this but found no fix but to try 2.23.50 (the one I am loading). Has this been resolved? Any help would be greatly appreciated. Morris Ford __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ATTACHMENT part 2 message/rfc822 name=Re_ Bug_ -_ AIX 4.3.3 ML10, IBM C_C++ 5.0.2.eml From: é¾?æf æ¨?(Borus.Kung) [EMAIL PROTECTED] To: morgan henning [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Bug? - AIX 4.3.3 ML10, IBM C/C++ 5.0.2 Date: Tue, 21 May 2002 09:17:14 +0800 I encountered the same problem, then I simply commented the line in the system header file curses.h. but the error I got is a bit different, it said: declaration is not declaring anything something like this... another header I modified is the pthread.h but there is a problem with the AIX version, MySQL cannot shutdown I must use kill -9 [PID] to kill the safe_mysqld script and mysql processes. my AIX version is 4.3.3 but the good news is the binary compiled in 4.3.3 machine can run in 4.3.1 my way is absolutely not good, but it seems work. I used this to configure: CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/mysql/mysql \ --with-extra-charsets=complex --with-low-memory and you? Borus - Original Message - å¯?件è??: morgan henning [EMAIL PROTECTED] æ?¶ä»¶è??: [EMAIL PROTECTED] å,³é??æ-¥æ?Y: 2002å¹´5æ?^21æ-¥ AM 08:49 主æ-¨: Bug? - AIX 4.3.3 ML10, IBM C/C++ 5.0.2 Hi. I am trying to build MySQL 3.23.49, and I am having some problems. If anyone has any suggestions, they would be **much** appreciated :) I have tried to compile MySQL 3.23.49 a couple of ways under AIX, and I have gotten this twice (more info below): xlC -DUNDEF_THREADS_HACK -I./../include -I../include -I./.. -I.. -I.. -O3 -DDBUG_OFF -Wa,-many -DUNDEF_HAVE_INITGROUPS -DSIGNALS_DONT_BREAK_READ -c mysql.cc /usr/include/curses.h, line 1751.14: 1540-0063 (S) The text bool is unexpected. gmake[2]: *** [mysql.o] Error 1 gmake[2]: Leaving directory `/home/morgan/mysql-3.23.49/client' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/home/morgan/mysql-3.23.49' gmake: *** [all-recursive-am] Error 2 I got this on both AIX 4.3.3 (maintenance level 10 applied), and AIX 5.1.0 (maintenance level 2 applied). Both times I got the error, I was using the IBM C Compiler v5.0.2 and IBM C++ Compiler v5.0.2 (Visual Age C++ packages). The configure command line was CC=xlc CXX=xlC ./configure --prefix=/opt/mysql. I am using GNU make 3.79.1. The machine both times was an RS6000 43P-132 (type 7248-132). I can provide all kinds of more information if you like :) I tried gcc-3.0.4, but that failed also, although I forget the exact error. I am build gcc-2.95.3 in an effort to try the recommended gcc now. Nothing on that front yet. Am I just missing something with IBM C/C++ ?? The compiler is not patched to the latest level, although I can patch it. It's just a big download ( 500 MB). Please advise if you think that's the solution. Thanks!! :) -- Morgan Henning [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To
Re: still not-empty set
Maybe
if (!($result)) {
Try
print STDERR RESULT=$result\n;
to see what you are comparing to.
Chris Hohimer wrote:
thanks for all the replies!! but it is not working.
here is the loop:
if($result == EMPTY SET){
$Scheduled = NULL;
}
else{
$Tech = mysql_result($result, 0, tech);
$SNumber = mysql_result($result, 0, snumber);
$Scheduled = mysql_result($result, 0, scheduled);
}
here is what i've tried and the results
#1.
if ($result == NULL){
Warning Unable to jump to row 0 on MySQL result index#
in Location-of-file.php line 158
#2.
if (is_null($result)){
SAME
#3.
if ($result == 'NULL'){
SAME
#4.
if($result){
WORKS but it executes the $Scheduled= NULL code on everything.
even when there are actual results that are not
empty set.
Any more suggestions?
thanks
Chris Hohimer
Sandia National Labs
CSU821 ORG9623
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possibile to have something similar?
No you can't, but check out LIMIT. Marco Stagno wrote: IMHO, The Right Thing to do this, is to create the additional data in your application (as you said you won't change the table). (...) thank you! If you want this with SQL for sure, using user-defined variables will work: cool. sorry, but I'm still learning, since I'm into MySQL from a few time only! mysql select @a := @a + 1 AS pos, gid from config where gid30 limit 10; +--++ | pos | gid| +--++ |1 | 31 | |2 | 32 | |3 | 33 | ...can I use the pos data in a WHERE statment? something like.. select @a := @a + 1 AS pos, gid from config where pos 100 and pos 200 ? (or there is a better way to have a particular subset of data WITHOUT HAVING ANY INFO ON THE DB STRUCTURE?! I'm trying to have a tool to paging the query result in a gui structure) Another approach would be to select the data into a TEMPORARY TABLE. how can I do that? thank you very much again! bye bye! MAS! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[MyODBC]Access denied for user: 'ODBC@localhost'
Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = S1000 (General error) [TCX][MyODBC]Access denied for user: 'ODBC@localhost' (Using password: NO) The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (4:39). Date/Time: 06/06/02 06:34:43 Browser: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529 it was working fine yesterday... what is probably the problem? also i can't use phpMyAdmin anymore from the same machine. i can use the command line mysql just fine though. its running off of a win2k machine - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SV: building tree view in mysql?
Hello
I think, there's a better solution:
Use one database - field for sorting (for example SORT). Then you need to fill in
values in this field, that satisfy your needs:
Root - Item
1. Hello You
1.1. test1
1.2. test2
2. cool
2.1 cool i
2.2 cool ii
...
The values of the sort - fields will be:
1. 0001:0
1.1 0001:0001:0
1.2 0001:0002:0
2. 0002:0
2.1. 0002:0001:0
2.2 0002:0002:0
now you only need something like that:
select * from ... where ... order by SORT
that's very fast, witch is far more important that the overhead you have to fill in
the SORT - values. There are other advantages:
If you count the :, denn you can see how deep you are in the Tree. If your user
clicked on the tree and you know the value of the sort - fields the user clicked on,
you know, whether the item you are outputting is on the way to your selected item.
If it is, then it is a prefix of the item the user clicked on (you need to remove
0 for that).
hope that helps?
greetings from Switzerland
Patrick
Carsten Gehling wrote:
Sorry I wasn't really awake, and didn't notice which list you'd posted on
:-) Here's an example in PHP (not tested):
==
$cat_list = array();
$sql = select id, parent_id, category from your_table where order by
parent_id, category ;
$res = mysql_db_query($dbName, $sql) or die(Fejl ved query: $sqlhr .
mysql_error());
while ($row = mysql_fetch_assoc($res))
$cat_list[] = $row;
showcattree(0, 0, $cat_list);
function showcattree($parent_id, $niveau, $cat_list) {
$tabStr = str_repeat(nbsp;, $niveau*3);
for ($i=0; $icount($cat_list); $i++) {
$local_id = $cat_list[$i][id];
$local_parent_id = $cat_list[$i][parent_id];
$local_navn = $cat_list[$i][titel];
if ($local_parent_id == $parent_id) {
echo $tabStr$local_navnbr;
showcattree($local_id, $niveau+1, $cat_list);
$count++;
}
}
}
==
Please note that cat_list is given as a by-reference parameter. Otherwise
you'd be copying the whole array for each recursive function call.
The trick is, that to make a tree you always need to use recursive
functions. The performance is gained by loading the complete list from
database at once (instead of calling a mysql SELECT statement for each
branch of the tree).
- Carsten
-Oprindelig meddelelse-
Fra: Carsten Gehling [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:33
Til: [EMAIL PROTECTED]
Emne: SV: building tree view in mysql?
Well you'll neew to do some coding in your application language.
What are you using to connect to MySQL? Perl? PHP? Java?
Tell me, and I'll give you an example where you only query the
database once (for optimum performance).
- Carsten
-Oprindelig meddelelse-
Fra: Sagi Bashari [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:50
Til: olinux; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Emne: Re: building tree view in mysql?
Yeah, thats exactly what I wanted to do. But how do I make MySQL sort it
like that? Show the parent first, and then all its childs, so the
application can determite if it's subcategory by checking if
the parent is
was changed.
Sagi
From: olinux [EMAIL PROTECTED]
create a table like this:
id | parent_id | category
parent_id points to the parent categories id. Top
level categories get parent_id of '0'
ex:
1 | 0 | auto
2 | 1 | repair
3 | 2 | body
4 | 2 | windshields
5 | 1 | detailing
6 | 2 | tires
Tree would look like:
auto
repair
body
windshields
tires
detailing
olinux
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
Installing MySQL++
hello, I hav compiled and installed mysql++ without automake or autoconf just by typing ./configure make make install (Typing auto... bevore compiling makes everithing worse and make does not work - I don't know if that is the reason for following issues...) I want to build a program with KDevelop connecting to a mysql-server. Wehen I now #include sqlplus.hh ond try to build, I get several messages that mysql.h, included in some other headers is not found. I 'solved' this problem by changing alle the headers's lines from #include mysql.h to #include mysql/mysql.h. Now all Files seem to be found but I get many other serious errormessages *g* gmake-START gmake all-recursive gmake[1]: Entering directory `/home/holger/devel/log_nw_status' Making all in log_nw_status gmake[2]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status' Making all in docs gmake[3]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs' Making all in en gmake[4]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs/en' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs/en' gmake[4]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[4]: Nothing to be done for `all-am'. gmake[4]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[3]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[3]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status' source='main.cpp' object='main.o' libtool=no \ depfile='.deps/main.Po' tmpdepfile='.deps/main.TPo' \ depmode=gcc /bin/sh ../admin/depcomp \ g++ -DHAVE_CONFIG_H -I. -I. -I.. -O2 -O0 -g3 -Wall -fno-exceptions -fno-check-new -c -o main.o `test -f main.cpp || echo './'`main.cpp In file included from /usr/local/include/coldata1.hh:9, from /usr/local/include/sqlplus.hh:12, from main.cpp:26: /usr/local/include/const_string1.hh: In method `const char const_string::at(unsigned int) const': /usr/local/include/const_string1.hh:50: exception handling disabled, use -fexceptions to enable /usr/local/include/const_string1.hh:52: warning: control reaches end of non-void function `const_string::at(unsigned int) const' In file included from /usr/local/include/sqlplus.hh:50, from main.cpp:26: /usr/local/include/sql_query2.hh: In method `class SQLString SQLQueryParms::operator [](const char *)': /usr/local/include/sql_query2.hh:9: warning: control reaches end of non-void function `SQLQueryParms::operator [](const char *)' /usr/local/include/sql_query2.hh: In method `const class SQLString SQLQueryParms::operator [](const char *) const': /usr/local/include/sql_query2.hh:13: warning: control reaches end of non-void function `SQLQueryParms::operator [](const char *) const' gmake[3]: *** [main.o] Error 1 gmake[3]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status' gmake[2]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status' gmake[1]: Leaving directory `/home/holger/devel/log_nw_status' gmake[2]: *** [all-recursive] Error 1 gmake[1]: *** [all-recursive] Error 1 gmake: *** [all] Error 2 *** failed *** gmake-END --compiler-info-- linux:/ # gmake -v GNU Make version 3.79.1, by Richard Stallman and Roland McGrath. Built for i686-pc-linux-gnu Copyright (C) 1988, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 2000 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Report bugs to [EMAIL PROTECTED]. linux:/ # gcc -dumpversion 2.95.3 linux:/ # g++ -dumpversion 2.95.3 linux:/ # cpp -dumpversion 2.95.3 --compiler-info-END-- I Would be happy if someone could help ;-( cu, H.S. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MYISAM Compressed table corruption
Hi, I've observed some table corruption: How-To-Repeat: no idea, but maybe you might have one ;) $ mysql -hxxx Escherichia_coli_O157_H7 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'intergenome' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 327 to server version: 3.23.49a Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql check table intergenome; +--+---+--+---+ | Table| Op| Msg_type | Msg_text | | +--+---+--+---+ | Escherichia_coli_O157_H7.intergenome | check | warning | Table is marked as crashed |and last repair failed | | Escherichia_coli_O157_H7.intergenome | check | error| Can't read indexpage from |filepos: -1 | | Escherichia_coli_O157_H7.intergenome | check | error| Corrupt | | +--+---+--+---+ 3 rows in set (1.20 sec) mysql flush tables; Query OK, 0 rows affected (0.03 sec) mysql lock table intergenome write; ERROR 1016: Can't open file: 'intergenome.MYD'. (errno: 144) mysql Oh yes, it was compressed before the crash. :( # /usr/local/mysql/bin/myisamchk -dvv intergenome MyISAM file: intergenome Record format: Compressed Character set: latin1 (8) File-version:1 Creation time: 2002-03-23 19:38:55 Recover time:2002-03-26 0:03:32 Status: crashed Checksum: 1999381211 Data records: 1712335 Deleted blocks: 0 Datafile parts:1712335 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):3 Datafile length: 200889622 Keyfile length: 12134400 Max datafile length:4294967294 Max keyfile length: 17179868159 Recordlength: 1019 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 3 unique uint24 1 12133376 1024 2 211 255 multip. char packed stripped 02048 3 753 255 multip. char packed stripped 02048 4 111 100 multip. char packed stripped 01024 5 5 3 multip. uint24 01024 6 8 3 multip. uint24 01024 Field Start Length Nullpos Nullbit Type Huff tree Bits 1 1 1 constant 1 0 2 2 3 2 9 3 5 3 zerofill(1) 3 9 4 8 3 always zero 2 9 5 11100constant 4 0 6 111 100no endspace 5 5 7 211 255no endspace 6 9 8 466 4 table-lockup 7 0 9 470 255no endspace 8 6 10725 4 no zeros, zerofill(3)9 1 11729 4 table-lockup10 0 12733 4 table-lockup11 0 13737 4 zerofill(2) 12 9 14741 2 table-lockup13 0 15743 2 table-lockup14 0 16745 4 zerofill(2) 15 9 17749 4 zerofill(2) 16 9 18753 255no endspace 17 9 191008 10 1 1 blob 2 9 201018 1 1 2 always zero 2 9 211019 1 1 4 always zero 2 9 # /usr/local/mysql/bin/myisamchk -r -v intergenome.MYI - recovering (with sort) MyISAM-table 'intergenome.MYI' Data records: 1712335 - Fixing index 1 - Searching for keys, allocating buffer for 1525193 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 63069 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys,
UDF problems
Hi all, I have tried several times to integrate the lookup functiction into mysql but with no luck. :( I have REDHAT 7.3 with gcc 2.96 and with mysql-3.23.49 I compiled udf_example.so and copied into /usr/lib folder. When I try to do CREATE FUNCTION lookup RETURNS STRING SONAME udf_example.so; I get ERROR 1126: Can't open shared library 'udf_example.so' (errno: 0 /usr/lib/udf_example.so: ELF file's phentsize not the expected s) - What should I do with this? How can I made this work? any help is apreciated.. th zoli - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SV: building tree view in mysql?
Hello
I think, there's a better solution:
Use one database - field for sorting (for example SORT). Then you need
to fill in
values in this field, that satisfy your needs:
Root - Item
1. Hello You
1.1. test1
1.2. test2
2. cool
2.1 cool i
2.2 cool ii
...
The values of the sort - fields will be:
1. 0001:0
1.1 0001:0001:0
1.2 0001:0002:0
2. 0002:0
2.1. 0002:0001:0
2.2 0002:0002:0
now you only need something like that:
select * from ... where ... order by SORT
that's very fast, witch is far more important that the overhead you have
to fill in
the SORT - values. There are other advantages:
If you count the :, denn you can see how deep you are in the Tree. If
your user
clicked on the tree and you know the value of the sort - fields the user
clicked on,
you know, whether the item you are outputting is on the way to your
selected item.
If it is, then it is a prefix of the item the user clicked on (you need
to remove
0 for that).
hope that helps?
greetings from Switzerland
Patrick
Carsten Gehling wrote:
Sorry I wasn't really awake, and didn't notice which list you'd posted
on
:-) Here's an example in PHP (not tested):
==
$cat_list = array();
$sql = select id, parent_id, category from your_table where order by
parent_id, category ;
$res = mysql_db_query($dbName, $sql) or die(Fejl ved query: $sqlhr
.
mysql_error());
while ($row = mysql_fetch_assoc($res))
$cat_list[] = $row;
showcattree(0, 0, $cat_list);
function showcattree($parent_id, $niveau, $cat_list) {
$tabStr = str_repeat(nbsp;, $niveau*3);
for ($i=0; $icount($cat_list); $i++) {
$local_id = $cat_list[$i][id];
$local_parent_id = $cat_list[$i][parent_id];
$local_navn = $cat_list[$i][titel];
if ($local_parent_id == $parent_id) {
echo $tabStr$local_navnbr;
showcattree($local_id, $niveau+1, $cat_list);
$count++;
}
}
}
==
Please note that cat_list is given as a by-reference parameter.
Otherwise
you'd be copying the whole array for each recursive function call.
The trick is, that to make a tree you always need to use recursive
functions. The performance is gained by loading the complete list from
database at once (instead of calling a mysql SELECT statement for each
branch of the tree).
- Carsten
-Oprindelig meddelelse-
Fra: Carsten Gehling [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:33
Til: [EMAIL PROTECTED]
Emne: SV: building tree view in mysql?
Well you'll neew to do some coding in your application language.
What are you using to connect to MySQL? Perl? PHP? Java?
Tell me, and I'll give you an example where you only query the
database once (for optimum performance).
- Carsten
-Oprindelig meddelelse-
Fra: Sagi Bashari [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:50
Til: olinux; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Emne: Re: building tree view in mysql?
Yeah, thats exactly what I wanted to do. But how do I make MySQL
sort it
like that? Show the parent first, and then all its childs, so the
application can determite if it's subcategory by checking if
the parent is
was changed.
Sagi
From: olinux [EMAIL PROTECTED]
create a table like this:
id | parent_id | category
parent_id points to the parent categories id. Top
level categories get parent_id of '0'
ex:
1 | 0 | auto
2 | 1 | repair
3 | 2 | body
4 | 2 | windshields
5 | 1 | detailing
6 | 2 | tires
Tree would look like:
auto
repair
body
windshields
tires
detailing
olinux
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
[EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe,
How to rotate replication logs
I have a question about how to rotate replication logs in MySQL V 3.23.49. I've tried to use the command purge master logs to 'logname', however, I'm unclear what to specify for 'logname'. On my master, I have several old log files which are no longer being used (by issuing the command show slave status on the slave machine. When I try to use the purge command to delete the log, I get the MySQL error: A purgable log is in use, will not purge. When I issue the command on the active log file, I don't get any errors, but the old logs are not deleted. What I'd like to do is to set up a mechanism where every week I rotate my replication log files and delete (or archive) the ones I no longer need. Can anyone point me to some docs where I can learn how to do this? Much thanks, -- Dave Swindell C. David SwindellSimUtility, Inc 68 Tadmuck Road Westford, MA 01886 Tel: (978) 692-1008x229 Fax: (978) 692-8177 [EMAIL PROTECTED] www.simutility.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MyODBC]Access denied for user: 'ODBC@localhost'
ok, rebooting didn't fix it, reinstalling myodbc didn't fix it, creating a user ODBC@localhost fixed it. all off a sudden i need a user named ODBC@localhost, and last i checked, i'm the only one here that knows anything about how to use mysql here at the office. so what might have been changed that would cause this. MyODBC has the correct username/password for the databases it uses. MS Access can open the ODBC databases without any problems (before i created the ODBC@localhost) its only the web server that seems to need the user. (ColdFusion PHP) and how do i change it back to not need the odbc@localhost user anymore (again)? and it turns out (just to mislead me even more...) Mozilla 1.0 running on linux isn't working correctly with phpmyadmin using cookie auth. (don't know if its just me, or just mozilla...) after login in, it shows me 2 frames, both with login screens. On Thursday 06 June 2002 8:39, you wrote: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = S1000 (General error) [TCX][MyODBC]Access denied for user: 'ODBC@localhost' (Using password: NO) The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (4:39). Date/Time: 06/06/02 06:34:43 Browser: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529 it was working fine yesterday... what is probably the problem? also i can't use phpMyAdmin anymore from the same machine. i can use the command line mysql just fine though. its running off of a win2k machine - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- To one large turkey add one gallon of vermouth and a demijohn of Angostura bitters. Shake. -- F. Scott Fitzgerald, recipe for turkey cocktail. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Installation problems on AIX 4.3.3
Morris, Thursday, June 06, 2002, 6:17:57 AM, you wrote: MF I am having a problem installing version 3.23.50 MF on AIX 4.3.3. When I execute mysql_install_db or MF try to start mysqld I get errors like MF 0509-036 Cannot load program MF ./bin/my_print_defaults because of the following MF errors: [skip] MF I have chased through the mail archives and found MF messages with errors like this but found no fix MF but to try 2.23.50 (the one I am loading). Has MF this been resolved? Any help would be greatly MF appreciated. It's because of our wrong build. It's fixed in 3.23.51 which should be out soon. MF Morris Ford -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Maximum JOINED tables
Roger, Thursday, June 06, 2002, 12:08:13 PM, you wrote: I just tried a multiple self join on 4.0.1 and got the following DBD::mysql::st execute failed: Too many tables. MySQL can only use 31 tables in a join at ./x2.pl line 47. RB Me too, running 3.23.30-gamma on w2k: mysql desc select * from RB - tab1 t1,tab1 t2,tab1 t3,tab1 t4... RB ERROR 1116: Too many tables. MySQL can only use 31 tables in a join RB So... is the crashme report wrong? Or was the limit really 32 in 3.22.39, RB got lowered to 31 in later versions (like 3.23.30-gamma), raised to 63 in RB version 3.23.39, and then lowered to 31 again (at least in versions 3.23.47 RB and 4.0.1)...? RB URL: http://www.mysql.com/crashme RB (Select both mysql versions, submit, and search for tables in join on the RB result page.) If you have 32 bit CPU you can use 31 tables in join. 63 tables with 64 bit CPU. RB -- RB Roger -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MyODBC]Access denied for user: 'ODBC@localhost'
Ray, Thursday, June 06, 2002, 4:39:11 PM, you wrote: R Error Occurred While Processing Request R Error Diagnostic Information R ODBC Error Code = S1000 (General error) R [TCX][MyODBC]Access denied for user: 'ODBC@localhost' (Using password: NO) R The error occurred while processing an element with a general identifier of R (CFQUERY), occupying document position (3:1) to (4:39). R Date/Time: 06/06/02 06:34:43 R Browser: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529 R it was working fine yesterday... what is probably the problem? R also i can't use phpMyAdmin anymore from the same machine. i can use the R command line mysql just fine though. If you can connect with command line client but connection fails with phpMyAdmin and MyODBC it's probably wrong connection parameters. Please, check you DSN. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert into longblob column, it's limitation
¼ÛÅÂÈ£,
Thursday, June 06, 2002, 1:08:07 PM, you wrote:
¼ I will attempt to give you as much information about the software as I can
¼ before I ask the question.
¼ OS : RedHat 7.1
¼ MySQL : Version 3.23.49a for Linux - binary distribution for pc-linux
¼ I want to insert into longblob column very big large data.
¼ but.. I have strange Errors...
¼ Example..
mysql create table test (aa longblob);
mysql insert into test values(load_file('testfile.exe'));
¼ ERROR 1030 : Got error 139 from table handler
mysql
./perror 139 - Too big row ( = 16M)
¼ The size of testfile.exe is 16.3M
¼ The table type of test is MYISAM
¼ max_allowed_packet = 6700(64M?)
¼ I don't know about this error...
Current limit is 16M, but the data is escaped. It means that the real size
is about 10-12M. It will be changed in future versions ...
--
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
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SV: building tree view in mysql?
This problem is already solved in Oracle. It it the CONNECT BY PRIOR
statement which recursively queries a table.
The good news is that I read in the MySQL manual, that this feature will be
implemented in MySQL in the Real Near Future :-).
(MySQL manual: 1.8.2 Things That Must be Done in the Real Near Future,
page 47 in the PDF manual.)
At 15:57 2002.06.06. +0200, you wrote:
Hello
I think, there's a better solution:
Use one database - field for sorting (for example SORT). Then you need
to fill in
values in this field, that satisfy your needs:
Root - Item
1. Hello You
1.1. test1
1.2. test2
2. cool
2.1 cool i
2.2 cool ii
...
The values of the sort - fields will be:
1. 0001:0
1.1 0001:0001:0
1.2 0001:0002:0
2. 0002:0
2.1. 0002:0001:0
2.2 0002:0002:0
now you only need something like that:
select * from ... where ... order by SORT
that's very fast, witch is far more important that the overhead you have
to fill in
the SORT - values. There are other advantages:
If you count the :, denn you can see how deep you are in the Tree. If
your user
clicked on the tree and you know the value of the sort - fields the user
clicked on,
you know, whether the item you are outputting is on the way to your
selected item.
If it is, then it is a prefix of the item the user clicked on (you need
to remove
0 for that).
hope that helps?
greetings from Switzerland
Patrick
Carsten Gehling wrote:
Sorry I wasn't really awake, and didn't notice which list you'd posted
on
:-) Here's an example in PHP (not tested):
==
$cat_list = array();
$sql = select id, parent_id, category from your_table where order by
parent_id, category ;
$res = mysql_db_query($dbName, $sql) or die(Fejl ved query: $sqlhr
.
mysql_error());
while ($row = mysql_fetch_assoc($res))
$cat_list[] = $row;
showcattree(0, 0, $cat_list);
function showcattree($parent_id, $niveau, $cat_list) {
$tabStr = str_repeat(nbsp;, $niveau*3);
for ($i=0; $icount($cat_list); $i++) {
$local_id = $cat_list[$i][id];
$local_parent_id = $cat_list[$i][parent_id];
$local_navn = $cat_list[$i][titel];
if ($local_parent_id == $parent_id) {
echo $tabStr$local_navnbr;
showcattree($local_id, $niveau+1, $cat_list);
$count++;
}
}
}
==
Please note that cat_list is given as a by-reference parameter.
Otherwise
you'd be copying the whole array for each recursive function call.
The trick is, that to make a tree you always need to use recursive
functions. The performance is gained by loading the complete list from
database at once (instead of calling a mysql SELECT statement for each
branch of the tree).
- Carsten
-Oprindelig meddelelse-
Fra: Carsten Gehling [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:33
Til: [EMAIL PROTECTED]
Emne: SV: building tree view in mysql?
Well you'll neew to do some coding in your application language.
What are you using to connect to MySQL? Perl? PHP? Java?
Tell me, and I'll give you an example where you only query the
database once (for optimum performance).
- Carsten
-Oprindelig meddelelse-
Fra: Sagi Bashari [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:50
Til: olinux; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Emne: Re: building tree view in mysql?
Yeah, thats exactly what I wanted to do. But how do I make MySQL
sort it
like that? Show the parent first, and then all its childs, so the
application can determite if it's subcategory by checking if
the parent is
was changed.
Sagi
From: olinux [EMAIL PROTECTED]
create a table like this:
id | parent_id | category
parent_id points to the parent categories id. Top
level categories get parent_id of '0'
ex:
1 | 0 | auto
2 | 1 | repair
3 | 2 | body
4 | 2 | windshields
5 | 1 | detailing
6 | 2 | tires
Tree would look like:
auto
repair
body
windshields
tires
detailing
olinux
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
[EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the
mysqlhotcopy broken ???
It appears the v4.0.1 mysqlhotcopy perl script has been modified and broken.
When using cp for the backup it's passing a null value for the file(s) to be
copied. Below is a session clipping of an execution with --debug turned on:
# /u02/mysql/mysql-4.0.1/bin/mysqlhotcopy test_myisam --allowold --debug
Using copy suffix '_copy'
Filtering tables with '(?-xism:.*)'
$VAR1 = [
{
'tables' = [
'test_myisam.incidents'
],
't_regex' = '.*',
'src' = 'test_myisam',
'raid_dirs' = [],
'index' = [],
'files' = [
'incidents.MYD',
'incidents.MYI',
'incidents.frm'
],
'target' = '/u02/mysql/data/test_myisam_copy'
}
];
Existing hotcopy directory renamed to '/u02/mysql/data/test_myisam_copy_old'
Locked 1 tables in 0 seconds.
Flushed tables (test_myisam.incidents) in 0 seconds.
Copying 3 files...
Executing 'cp -p /u02/mysql/data/test_myisam_copy'
cp: missing destination file
Try `cp --help' for more information.
Burp ('scuse me). Trying backtick execution...
cp: missing destination file
Try `cp --help' for more information.
Copying indices for 0 files...
Unlocked tables.
Deleting previous copy in /u02/mysql/data/test_myisam_copy_old
mysqlhotcopy copied 1 tables (3 files) in 0 seconds (0 seconds overall).
Any fixes for this?
Steve Orr
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie MySql
Hi Everyone. I need to run an extract of some data from our Oracle DB's. One of the restrictions is that I need to write a SQL script that will parse through a particular field for a / and if found, I have to remove it and output the data without the slash. I have minor experience with selecting data that I need based on a where clause and spooling the info to a file. But I haven't had any experience parsing through a field for invalid characters. Any ideas on how I should approach this? Michele Memon Developer (212) 290-8848 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlshow incompatibility (3.23.39 -- 4.0.1-alpha) ?
Dear listmembers, I've got 2 hosts running Linux: hostnames are 'hegel' and 'fidel' me@hegel:~mysqladmin version mysqladmin Ver 8.21 Distrib 3.23.39, for pc-linux-gnu on i586 me@fidel:~mysqladmin version mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686 When logged in to 'hegel' I can't mysqlshow the databases on 'fidel'. It works, but gives an error. me@fidel:~ mysqlshow -u me -h fidel +---+ | Databases | +---+ | bge | | mysql | | something | | test | +---+ me@hegel:~ mysqlshow -u me -h fidel mysqlshow: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqlshow: Character set '#31' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file +---+ | Databases | +---+ | bge | | mysql | | something | | test | +---+ However, it runs fine the other way round (4.0.1 client, 3.23.39 server): me@fidel:~ mysqlshow -u me -h hegel +--+ | Databases | +--+ | casino | | mitteilungen | | mysql| | ssl | | test | +--+ What happens? Any insights what to look for? Thomas Spahni -- sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
faster update
Hello,
I was wondering if there was a better way to speed up
my update statement in my perl script. It goes through all the
entries in the database , which is around 800, 000, and then updates the email
field. The problem is that it takes to long.
thanks in advance for any help
here the code:
open(A, $ktjpath/$ktjlist) || death(Can't open $ktjlist: um121);
my $sql_statement = UPDATE ktj SET ktjnews = 'N' WHERE email = ?;
my $sth = $dbh-prepare($sql_statement) || die Error1 preparing update
statement on track:\n . $dbh-errstr;
while (A){
print $_\n;
$sth-execute($_) || die Error2 executing update statement on
track:\n . $sth-errstr;
}
close A;
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL back-up methodology
Hello, I am trying to develop a back-up procedure for our MySQL database. The database is housed on our production Web server. My thought is to simply make a slave on our file server that is backed up. I use InnoDB type tables and my application uses transactions. I am wondering how table/row locking works on the slave. Do I need to be concerned about disconnecting the salve from the master when back-ups are performed? Or does all the locking happen only on the master. Thanks a lot for any help! Regards, Aaron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: faster update
On Thursday 06 June 2002 9:51 am, Greg D. wrote:
I was wondering if there was a better way to speed up
my update statement in my perl script. It goes through all the
entries in the database , which is around 800, 000, and then updates the
email field. The problem is that it takes to long.
thanks in advance for any help
here the code:
open(A, $ktjpath/$ktjlist) || death(Can't open $ktjlist: um121);
my $sql_statement = UPDATE ktj SET ktjnews = 'N' WHERE email = ?;
my $sth = $dbh-prepare($sql_statement) || die Error1 preparing update
statement on track:\n . $dbh-errstr;
while (A){
print $_\n;
$sth-execute($_) || die Error2 executing update statement on
track:\n . $sth-errstr;
}
close A;
Please note: If you are using this to maintain a list of emails that you
will send spam to (an 'opt-in' mailing list does NOT count but an 'opt-out'
does), you may not read any further in this message and may not use any
advice I give. :)
Are you indexing on email? If not, you should be. That should reduce this
from a table scan to an index scan provided that the emails are mostly unique.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: faster update
On Thursday 06 June 2002 08:53 am, you wrote:
On Thursday 06 June 2002 9:51 am, Greg D. wrote:
I was wondering if there was a better way to speed up
my update statement in my perl script. It goes through all the
entries in the database , which is around 800, 000, and then updates the
email field. The problem is that it takes to long.
thanks in advance for any help
here the code:
open(A, $ktjpath/$ktjlist) || death(Can't open $ktjlist: um121);
my $sql_statement = UPDATE ktj SET ktjnews = 'N' WHERE email = ?;
my $sth = $dbh-prepare($sql_statement) || die Error1 preparing update
statement on track:\n . $dbh-errstr;
while (A){
print $_\n;
$sth-execute($_) || die Error2 executing update statement on
track:\n . $sth-errstr;
}
close A;
Please note: If you are using this to maintain a list of emails that you
will send spam to (an 'opt-in' mailing list does NOT count but an 'opt-out'
does), you may not read any further in this message and may not use any
advice I give. :)
Are you indexing on email? If not, you should be. That should reduce this
from a table scan to an index scan provided that the emails are mostly
unique.
I tried indexing email already but then when i went back to check if the
update was working a lot of the fields were not updated.
thanks though
greg
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: answer to MySQL service problems
[snip] I've asked this question once before with no response (and have asked on other lists), I'll give it one more try before giving in to semi-Gates domination (my last holdout is the Apache server I'm running): Upon installing MySQL as a service, why will it not start for all users? Only my admin account allows the MySQL service to start. I cannot start the service manually or automatically from any regular (power user) account. MySQL was installed as a service using mysqld-nt --install with no problems. I've come across a vague reference in the manual for a manual installation of MySQL as a service if problems occur, but have no idea if that is the course of action to take since I don't really know why there is a problem. Putting MySQL in the startup items makes no difference. HELP! [/snip] Kirk, Are you starting MySQL on individual platforms? Are you starting it on a server and then attempting to connect to it from another user's computer? If the latter is the case you do not need to start MySQL, you just need to connect to it on the server it resides on. I think the lack of answer is due to the vaguery of the question. Maybe we can get going the right direction here. Thanks! Jay Jay, All of this is occuring on the same W2K machine (with Apache, MySQL, etc). When I log off admin and log on as a power user (same machine) I have the aforementioned problems. -Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MyODBC]Access denied for user: 'ODBC@localhost'
in the DSN settings, each datasource has a different username and password. and looking over the DSNs, none of them should be using the username ODBC@localhost. On Thursday 06 June 2002 9:18, you wrote: Ray, Thursday, June 06, 2002, 4:39:11 PM, you wrote: R Error Occurred While Processing Request R Error Diagnostic Information R ODBC Error Code = S1000 (General error) R [TCX][MyODBC]Access denied for user: 'ODBC@localhost' (Using password: NO) R The error occurred while processing an element with a general identifier of R (CFQUERY), occupying document position (3:1) to (4:39). R Date/Time: 06/06/02 06:34:43 R Browser: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529 R it was working fine yesterday... what is probably the problem? R also i can't use phpMyAdmin anymore from the same machine. i can use the R command line mysql just fine though. If you can connect with command line client but connection fails with phpMyAdmin and MyODBC it's probably wrong connection parameters. Please, check you DSN. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
installing mysql++-1.7.9 on Suse-Linux 8.0
hello, I hav compiled and installed mysql++ without automake or autoconf just by typing ./configure make make install (Typing auto... bevore compiling makes everithing worse and make does not work - I don't know if that is the reason for following issues...) I want to build a program with KDevelop connecting to a mysql-server. Wehen I now #include sqlplus.hh ond try to build, I get several messages that mysql.h, included in some other headers is not found. I 'solved' this problem by changing alle the headers's lines from #include mysql.h to #include mysql/mysql.h. Now all Files seem to be found but I get many other serious errormessages *g* gmake-START gmake all-recursive gmake[1]: Entering directory `/home/holger/devel/log_nw_status' Making all in log_nw_status gmake[2]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status' Making all in docs gmake[3]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs' Making all in en gmake[4]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs/en' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs/en' gmake[4]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[4]: Nothing to be done for `all-am'. gmake[4]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[3]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status/docs' gmake[3]: Entering directory `/home/holger/devel/log_nw_status/log_nw_status' source='main.cpp' object='main.o' libtool=no \ depfile='.deps/main.Po' tmpdepfile='.deps/main.TPo' \ depmode=gcc /bin/sh ../admin/depcomp \ g++ -DHAVE_CONFIG_H -I. -I. -I.. -O2 -O0 -g3 -Wall -fno-exceptions -fno-check-new -c -o main.o `test -f main.cpp || echo './'`main.cpp In file included from /usr/local/include/coldata1.hh:9, from /usr/local/include/sqlplus.hh:12, from main.cpp:26: /usr/local/include/const_string1.hh: In method `const char const_string::at(unsigned int) const': /usr/local/include/const_string1.hh:50: exception handling disabled, use -fexceptions to enable /usr/local/include/const_string1.hh:52: warning: control reaches end of non-void function `const_string::at(unsigned int) const' In file included from /usr/local/include/sqlplus.hh:50, from main.cpp:26: /usr/local/include/sql_query2.hh: In method `class SQLString SQLQueryParms::operator [](const char *)': /usr/local/include/sql_query2.hh:9: warning: control reaches end of non-void function `SQLQueryParms::operator [](const char *)' /usr/local/include/sql_query2.hh: In method `const class SQLString SQLQueryParms::operator [](const char *) const': /usr/local/include/sql_query2.hh:13: warning: control reaches end of non-void function `SQLQueryParms::operator [](const char *) const' gmake[3]: *** [main.o] Error 1 gmake[3]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status' gmake[2]: Leaving directory `/home/holger/devel/log_nw_status/log_nw_status' gmake[1]: Leaving directory `/home/holger/devel/log_nw_status' gmake[2]: *** [all-recursive] Error 1 gmake[1]: *** [all-recursive] Error 1 gmake: *** [all] Error 2 *** failed *** gmake-END --compiler-info-- linux:/ # gmake -v GNU Make version 3.79.1, by Richard Stallman and Roland McGrath. Built for i686-pc-linux-gnu Copyright (C) 1988, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 2000 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Report bugs to [EMAIL PROTECTED]. linux:/ # gcc -dumpversion 2.95.3 linux:/ # g++ -dumpversion 2.95.3 linux:/ # cpp -dumpversion 2.95.3 --compiler-info-END-- I Would be happy if someone could help ;-( cu, H.S. --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: answer to MySQL service problems
[snip] All of this is occuring on the same W2K machine (with Apache, MySQL, etc). When I log off admin and log on as a power user (same machine) I have the aforementioned problems. [/snip] Kirk, OK, this makes sense. The power-user(s) needs to be given permissions to start and stop services on the machine. Admin is, by default, the only user allowed to start/stop services. Can you start the service as admin, log-off, log back on, and have access to MySQL? Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Size of BLOB and TEXT columns
In SQL DDL, what are the maximum sizes of the various BLOB and TEXT column types? TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB and the corresponding TEXT types? I've searched the manual but couldn't find these specs. At what point should I choose BLOB over TINYBLOB, and so on? TIA, Arthur - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems starting MySQL as a Service for all users
Kirk, I am somewhat confused by your question. I must misunderstand what it is that you are trying to ask, because what it appears that you are asking makes no sense. Once MySQL was installed as a service, why does it matter which account starts it? Are you not the admin on the box? Are you trying to start multiple instances of the MySQL service? Logging off of the admin account will not stop a service on a Windows 2000 box. Even though you log off the admin account, MySQL should still be running. Or, when you say started do you mean connect to the running server? What is the output of the following command on the server running MySQL? netstat -a -p tcp Respectfully, Charles Q. At 08:23 PM 6/5/2002 -0500, you wrote: I've installed MySQL as a service using mysqld-nt --install on W2K. No problem there, says service successfully installed. BUT, when I try to log off the admin account and on to the regular power user account MySQL will not start automagically and cannot be started manually. Pop back over to the admin account and MySQL acts like nothing was ever wrong. Is this an issue that can be overcome by manually installing MySQL as a service instead of using the above? And how do you accomplish that? If you know or have heard of this before please help me out; I'd really like to have MySQL running for all users (that would enable the Apache-based site I'm running on the same box to access the database at all times). Thanks! Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: faster update
I'm a bit confused by what you mean here; are you just updating the
'ktjnews' column, or are you attempting to reset the 'email' column as well?
(The source says the first, your comments suggest the second.)
Further, exactly which part of this is too slow? Obviously, each query will
require scanning the entire table unless you've got an index with 'email' as
the first field. (If there is some reason you're not doing that, you should
address that reason, not try to work around not having the index.)
Again, I can't entirely reconcile your comments with the source: where are
you getting the number 800,000? Is that the number of entries in the
database, or is it the number of lines in the file read through A? If you
are really doing 800,000 separate updates against the database, it doesn't
surprise me much if it bogs down the machine a bit.
If this is the case, then I would suggest taking a look at where this A file
is coming from. If instead of getting these 800,000 addresses in file form
you get get them shoved into the database in their own table, you can kind
of hack around the problem by copying your entire table to a temporary one
with a join against your (new) email table and a spiffy test to compute the
new ktjnews field, then replace the contents of the original table with this
temporary table. If the email index on ktj is really a problem, then you can
just index the new email table to optimize the join, and it should take only
one traversal of the ktj table to compute all the values. Further, if you do
actually intend to change the email field (or if the ktjnews field is
indexed) by replacing all the changed rows at once you only have to re-index
the table once. (Is this actually an optimization in MySQL, or is the system
smart enough to make this equally efficient either way?)
Just a few ideas.
-rob
On 6/6/02 at 8:51 am, Greg D. [EMAIL PROTECTED] wrote:
Hello,
I was wondering if there was a better way to speed up
my update statement in my perl script. It goes through all the
entries in the database , which is around 800, 000, and then updates the
email
field. The problem is that it takes to long.
thanks in advance for any help
here the code:
open(A, $ktjpath/$ktjlist) || death(Can't open $ktjlist: um121);
my $sql_statement = UPDATE ktj SET ktjnews = 'N' WHERE email = ?;
my $sth = $dbh-prepare($sql_statement) || die Error1 preparing update
statement on track:\n . $dbh-errstr;
while (A){
print $_\n;
$sth-execute($_) || die Error2 executing update statement on
track:\n . $sth-errstr;
}
close A;
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Size of BLOB and TEXT columns
[snip] In SQL DDL, what are the maximum sizes of the various BLOB and TEXT column types? TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB and the corresponding TEXT types? I've searched the manual but couldn't find these specs. At what point should I choose BLOB over TINYBLOB, and so on? [/snip] http://www.mysql.com/doc/n/o/node_369.html HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: WHERE-ing an alias...
[snip] WHERE ap_type='Original Movie Poster' ORDER BY sort_title ASC HAVING sort_title LIKE 'G%' ORDER BY sort_title ASC You may have to swap the HAVING and ORDER BY lines of the query, [/snip] Thanks so much. There was a slight syntax change, replacing your first ORDER BY with a GROUP BY, but it worked. SELECT posterdata.*, IF (aptitle LIKE 'The %',SUBSTRING(aptitle,5), IF (aptitle LIKE 'A %',SUBSTRING(aptitle,3), IF (aptitle LIKE 'An%',SUBSTRING(aptitle,4),aptitle))) AS sort_title FROM posterdata WHERE ap_type='Original Movie Poster' GROUP BY sort_title ASC HAVING sort_title LIKE 'g%' ORDER BY sort_title ASC; Many thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Maximum JOINED tables
* Victoria Reznichenko [...] RB So... is the crashme report wrong? Or was the limit really 32 RB in 3.22.39, got lowered to 31 in later versions (like 3.23.30- RB gamma), raised to 63 in version 3.23.39, and then lowered to RB 31 again (at least in versions 3.23.47 and 4.0.1)...? RB URL: http://www.mysql.com/crashme RB (Select both mysql versions, submit, and search for tables RB in join on the result page.) If you have 32 bit CPU you can use 31 tables in join. 63 tables with 64 bit CPU. ok... thanks. :) But... is this the only feature of mysql which is dependant on the processor architecture? I'm kind of nervous about this, because I'm currently working on a 'very normalized' database schema, and it is possible I will need more than 31 tables in a single select... including multiple joins to the same tables, of course. It would be very bad if my design turned out to be 64-bit dependant... -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
make of mysql 3.23.37 --with-debug fails on AIX 4.3.3 with xlc_r/xlC_r compiler
Summary: On AIX 4.3.3, using xlc_r/xlC_r compiler, configured with --with-debug, make all in client directory fails with: ld: 0711-317 ERROR: Undefined symbol: ._db_enter_ ld: 0711-317 ERROR: Undefined symbol: ._db_return_ ld: 0711-317 ERROR: Undefined symbol: _db_on_ ld: 0711-317 ERROR: Undefined symbol: ._db_pargs_ ld: 0711-317 ERROR: Undefined symbol: ._db_doprnt_ ld: 0711-317 ERROR: Undefined symbol: _db_process_ ld: 0711-317 ERROR: Undefined symbol: ._db_push_ MySql 3.23.37 source distribution Machine: IBM RS/6000 % uname -M IBM,7044-170 OS: 4.3.3 % oslevel -r 4330-09 VisualAge C++ C Compiler 5.0 I modified include/*global.h to comment out #define _AIX32_CURSES. I run: CC=xlc_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192 \ CXX=xlC_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192 \ CFLAGS=-DHAVE_GETHOSTBYNAME_R_RETURN_INT \ LDFLAGS=-L/usr/local/lib \ CPPFLAGS=$CFLAGS \ CXXFLAGS=$CFLAGS \ ./configure --prefix=$INSTALL_PATH \ --libexecdir=$INSTALL_PATH/bin \ --datadir=$INSTALL_PATH/etc \ --with-unix-socket-path=$INSTALL_PATH/bin/.ardbd.sock \ --program-transform-name='s/mysql/ardb/g' \ --with-debug \ --without-docs \ --without-bench \ --enable-thread-safe-client \ --enable-large-files /usr/bin/make all In the client directory, make fails with: /bin/sh ../libtool --mode=link xlC_r -ma -O3 -qstrict -qoptimize=3 -qmax mem=8192 -g -O -DSAFE_MUTEX -DHAVE_GETHOSTBYNAME_R_RETURN_INT -Wa,-many -DUND EF_HAVE_INITGROUPS -L/usr/local/lib -o mysql mysql.o readline.o sql_string.o co mpletion_hash.o ../readline/libreadline.a -lcurses ../libmysql/libmysqlclient.la -lcrypt -lm mkdir .libs xlC_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192 -g -O -DSAFE_MUTEX -DHAVE_GETH OSTBYNAME_R_RETURN_INT -Wa,-many -DUNDEF_HAVE_INITGROUPS -L/usr/local/lib -o .li bs/mysql mysql.o readline.o sql_string.o completion_hash.o ../readline/libreadli ne.a -lcurses -L../libmysql/.libs -lmysqlclient -lcrypt -lm -lcrypt -lm -b nolib path -b libpath:/mysql/3.23.37/lib/mysql:/usr/lib:/lib ld: 0711-224 WARNING: Duplicate symbol: p_xargc ld: 0711-224 WARNING: Duplicate symbol: p_xargv ld: 0711-224 WARNING: Duplicate symbol: p_xrcfg ld: 0711-224 WARNING: Duplicate symbol: p_xrc ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. ld: 0711-317 ERROR: Undefined symbol: ._db_enter_ ld: 0711-317 ERROR: Undefined symbol: ._db_return_ ld: 0711-317 ERROR: Undefined symbol: _db_on_ ld: 0711-317 ERROR: Undefined symbol: ._db_pargs_ ld: 0711-317 ERROR: Undefined symbol: ._db_doprnt_ ld: 0711-317 ERROR: Undefined symbol: _db_process_ ld: 0711-317 ERROR: Undefined symbol: ._db_push_ make: 1254-004 The error code from the last command is 8. It looks like ld is not finding dbug.o, which then needs int2str.c Any suggestions on what needs to be changed? More system information: Fileset Level State Description bos.adt.base 4.3.3.75CBase Application Development Toolkit bos.adt.data 4.3.0.0CBase Application Development Toolkit Data bos.adt.debug 4.3.3.75CBase Application Development Debuggers bos.adt.include 4.3.3.78CBase Application Development Include Files bos.adt.lib 4.3.3.10CBase Application Development Libraries bos.adt.libm 4.3.3.50CBase Application Development Math Library bos.adt.prof 4.3.3.80CBase Profiling Support bos.adt.samples 4.3.3.75CBase Operating System Samples bos.adt.syscalls 4.3.3.50CSystem Calls Application Development Toolkit bos.adt.utils 4.3.3.50CBase Application Development Utilities - lex and yacc bos.net.nfs.adt 4.3.3.25CNetwork File System Development Toolkit bos.rte 4.3.3.75CBase Operating System Runtime bos.rte.Dt 4.3.0.0CDesktop Integrator bos.rte.ILS 4.3.3.75CInternational Language Support bos.rte.SRC 4.3.3.75CSystem Resource Controller bos.rte.X114.3.0.0CAIXwindows Device Support bos.rte.aio 4.3.3.75CAsynchronous I/O Extension bos.rte.archive 4.3.3.75CArchive Commands bos.rte.bind_cmds 4.3.3.75CBinder and Loader Commands bos.rte.boot 4.3.3.75CBoot Commands bos.rte.bosinst 4.3.3.75CBase OS Install Commands bos.rte.commands 4.3.3.75CCommands bos.rte.compare 4.3.3.75CFile Compare
Re: I KNOW one of you LISTers has experienced this
Only users of the Administrators group on NT can start system services by default. Have a look in your user manager for domains, policies, user rights Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 3:28 PM Subject: RE: I KNOW one of you LISTers has experienced this [snip] I've asked this question once before with no response (and have asked on other lists), I'll give it one more try before giving in to semi-Gates domination (my last holdout is the Apache server I'm running): Upon installing MySQL as a service, why will it not start for all users? Only my admin account allows the MySQL service to start. I cannot start the service manually or automatically from any regular (power user) account. MySQL was installed as a service using mysqld-nt --install with no problems. I've come across a vague reference in the manual for a manual installation of MySQL as a service if problems occur, but have no idea if that is the course of action to take since I don't really know why there is a problem. Putting MySQL in the startup items makes no difference. HELP! [/snip] Kirk, Are you starting MySQL on individual platforms? Are you starting it on a server and then attempting to connect to it from another user's computer? If the latter is the case you do not need to start MySQL, you just need to connect to it on the server it resides on. I think the lack of answer is due to the vaguery of the question. Maybe we can get going the right direction here. Thanks! Jay mysql, query, sql It's hip to snip! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possibile to have something similar?
No you can't, but check out LIMIT. Ops, but I did it! grin Using a variable and a temporary table as Benjamin and Keith wrote in the prev. messages ciao ciao! MAS! -- I love MySQL :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: faster update
Hello Greg,
maybe it can be of some help:
- Try ( if memory is not a problem ) to read the entire file in memory,
inside an array, and use foreach() in the array. It is quicker than
reading line by line from the HD;
- And you may try to mount your UPDATE statement to update a bunch
of emails at once, using 'WHERE email IN( email1, email2, ..., emailN )'.
You can try with 1000 or 1 at a time, mounting the statement and
executing after you have a list of emails, instead of using bind values.
This may or may not be of some help.
On Thursday 06 June 2002 12:51, Greg D. wrote:
Hello,
I was wondering if there was a better way to speed up
my update statement in my perl script. It goes through all the
entries in the database , which is around 800, 000, and then updates the
email field. The problem is that it takes to long.
thanks in advance for any help
here the code:
open(A, $ktjpath/$ktjlist) || death(Can't open $ktjlist: um121);
my $sql_statement = UPDATE ktj SET ktjnews = 'N' WHERE email = ?;
my $sth = $dbh-prepare($sql_statement) || die Error1 preparing update
statement on track:\n . $dbh-errstr;
while (A){
print $_\n;
$sth-execute($_) || die Error2 executing update statement on
track:\n . $sth-errstr;
}
close A;
--
João Paulo Vasconcellos
Gerente de Tecnologia - NetCard
Tel. 21 3852-9008 Ramal 31
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problems starting MySQL as a Service for all users
I don't think this makes sense either, but you will see what I mean: FOR MY ADMIN ACCOUNT C:\netstat -a -p tcp Active Connections Proto Local Address Foreign AddressState TCPdynamic:http dynamic:0 LISTENING TCPdynamic:epmap dynamic:0 LISTENING TCPdynamic:microsoft-ds dynamic:0 LISTENING TCPdynamic:1025 dynamic:0 LISTENING TCPdynamic:1027 dynamic:0 LISTENING TCPdynamic:1031 dynamic:0 LISTENING TCPdynamic:3306 dynamic:0 LISTENING TCPdynamic:1031 dynamic:3306 ESTABLISHED TCPdynamic:3306 dynamic:1031 ESTABLISHED TCPdynamic:netbios-ssndynamic:0 LISTENING FOR THE REGULAR USER ACCOUNT C:\netstat -a -p tcp Active Connections Proto Local Address Foreign AddressState TCPdynamic:http dynamic:0 LISTENING TCPdynamic:epmap dynamic:0 LISTENING TCPdynamic:microsoft-ds dynamic:0 LISTENING TCPdynamic:1025 dynamic:0 LISTENING TCPdynamic:1027 dynamic:0 LISTENING TCPdynamic:3306 dynamic:0 LISTENING TCPdynamic:netbios-ssndynamic:0 LISTENING I was under the impression that once something is installed as a service, it is available to the system for all users. This is not the case here. In fact, once I log off admin and onto the other accounts the winmysqladmin manager (which I put in startup for all users) asks if I want to install the service!!! When I click OK it says install failed and I actually have to shut down the tool in order to log off (or else the system hangs). My web site can connect to the server using PHP scripts to grab data with no problems when the admin account is running, but as soon as I log off and on to another account the dynamic part of the site dies. Again, when I was on my admin account I installed the MySQL service from the command prompt using: mysqld-nt --install and the result was service has been installed (paraphrasing). I have uninstalled MySQL completely and reinstalled only to run into the same problem. I'm using the 3.23.49 binary package. -Kirk -Original Message- From: Charles Quesenberry [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 11:25 AM To: Kirk Brannan Babb Cc: [EMAIL PROTECTED] Subject: Re: Problems starting MySQL as a Service for all users Kirk, I am somewhat confused by your question. I must misunderstand what it is that you are trying to ask, because what it appears that you are asking makes no sense. Once MySQL was installed as a service, why does it matter which account starts it? Are you not the admin on the box? Are you trying to start multiple instances of the MySQL service? Logging off of the admin account will not stop a service on a Windows 2000 box. Even though you log off the admin account, MySQL should still be running. Or, when you say started do you mean connect to the running server? What is the output of the following command on the server running MySQL? netstat -a -p tcp Respectfully, Charles Q. At 08:23 PM 6/5/2002 -0500, you wrote: I've installed MySQL as a service using mysqld-nt --install on W2K. No problem there, says service successfully installed. BUT, when I try to log off the admin account and on to the regular power user account MySQL will not start automagically and cannot be started manually. Pop back over to the admin account and MySQL acts like nothing was ever wrong. Is this an issue that can be overcome by manually installing MySQL as a service instead of using the above? And how do you accomplish that? If you know or have heard of this before please help me out; I'd really like to have MySQL running for all users (that would enable the Apache-based site I'm running on the same box to access the database at all times). Thanks! Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: on update problem?
It would seem strange to implement one and not the other? The help does say that you can use it and does keep the create options now. Does anyone know if this will be implemented in the next .5x release? Ross Me writes: Hi! don't think ON UPDATE is supported. The manual : http://www.innodb.com/ibman.html doesn't mention anything about it except : Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. EG When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems starting MySQL as a Service for all users
FOR MY ADMIN ACCOUNT C:\netstat -a -p tcp TCPdynamic:3306 dynamic:0 LISTENING FOR THE REGULAR USER ACCOUNT C:\netstat -a -p tcp TCPdynamic:3306 dynamic:0 LISTENING ^ The port is available under both accounts - the server is running. I was under the impression that once something is installed as a service, it is available to the system for all users. This is not the case here. In fact, once I log off admin and onto the other accounts I just proove you wrong... Again, when I was on my admin account I installed the MySQL service from the command prompt using: mysqld-nt --install and the result was service has been installed (paraphrasing). I have uninstalled MySQL completely and reinstalled only to run into the same problem. I'm using the 3.23.49 binary package. In your service manager (in the control panel), is the service listed there, and is it started? I personally, don't use the GUI mysqladmin utility. mysql-nt --install installs the service, then I use service manager just like I would for any other service (like IIS) on NT to be started. When you're as a default non admin user account, did you ever try mysql to connect to the server, or even better, telnet to port 3306 ? If so, what errors did you receive back from mysql or the telnet? If you didn't maybe it's time that you do. I doubt this is a service related problem, but I may be wrong... Using the right usernames / passwords / host addresses will also help your cause when you have trouble connecting to the mysql server. -Original Message- From: Charles Quesenberry [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 11:25 AM To: Kirk Brannan Babb Cc: [EMAIL PROTECTED] Subject: Re: Problems starting MySQL as a Service for all users Kirk, I am somewhat confused by your question. I must misunderstand what it is that you are trying to ask, because what it appears that you are asking makes no sense. Once MySQL was installed as a service, why does it matter which account starts it? Are you not the admin on the box? Are you trying to start multiple instances of the MySQL service? Logging off of the admin account will not stop a service on a Windows 2000 box. Even though you log off the admin account, MySQL should still be running. Or, when you say started do you mean connect to the running server? What is the output of the following command on the server running MySQL? netstat -a -p tcp Respectfully, Charles Q. At 08:23 PM 6/5/2002 -0500, you wrote: I've installed MySQL as a service using mysqld-nt --install on W2K. No problem there, says service successfully installed. BUT, when I try to log off the admin account and on to the regular power user account MySQL will not start automagically and cannot be started manually. Pop back over to the admin account and MySQL acts like nothing was ever wrong. Is this an issue that can be overcome by manually installing MySQL as a service instead of using the above? And how do you accomplish that? If you know or have heard of this before please help me out; I'd really like to have MySQL running for all users (that would enable the Apache-based site I'm running on the same box to access the database at all times). Thanks! Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQLPrepare problems
Problem Solved. Thx! Mateus Begossi - Original Message - From: Mateus Begossi [EMAIL PROTECTED] To: [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Wednesday, June 05, 2002 10:27 PM Subject: SQLPrepare problems Hello everyone, I´m writing a VC++ 6.0 App that among many other things manages a simple mySQL database, using myODBC v3.51. Righ now I´m trying to send a test simple statement to the database, using SQLPrepare: retcode = SQLPrepare(hstmt,INSERT INTO ger(manuf,model,location) VALUES(?,?,?), SQL_NTS); This is quite similar to one of the myODBC manual´s example. However, the VC++ 6.0 compiler gives me the following error: error C2664: 'SQLPrepare' : cannot convert parameter 2 from 'char [52]' to 'unsigned char *' Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast I tried to typecast the INSERT.. string with (unsigned char*): retcode = SQLPrepare(hstmt,(unsigned char*)INSERT INTO ger(manuf,model,location) VALUES(?,?,?), SQL_NTS); Which seemed to be fine, but despite compiling and linking alright, it really doesnt work and retcode returns an error. I know that myODBC is working because I already tested Direct Execution (SQLExecDirect) with sucess. So, what may I be doing wrong? Thanks in advance, Mateus Begossi - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sub-queries
Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
Fair enough (and also what I thought) Does anyone have any idea how I can implement the below in a similar fashion then? I have a list of items, and a list of groups. I want to retrieve all the items from a table that is not in a specific group... - Original Message - From: Kiss Dániel [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 9:47 PM Subject: Re: sub-queries I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
SELECT monitorhosts.HostID FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1'); Gruss Sabine Chris Knipe wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sub-queries
Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 2:52 PM To: [EMAIL PROTECTED] Subject: Re: sub-queries Fair enough (and also what I thought) Does anyone have any idea how I can implement the below in a similar fashion then? I have a list of items, and a list of groups. I want to retrieve all the items from a table that is not in a specific group... - Original Message - From: Kiss Dániel [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 9:47 PM Subject: Re: sub-queries I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
*MWAH*!!! Thanks a million, tested and working beautifully Can't believe in two days I didn't think of this... -- me - Original Message - From: Sabine Richter [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:14 PM Subject: Re: sub-queries SELECT monitorhosts.HostID FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1'); Gruss Sabine Chris Knipe wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Normilazation and Optimization
Hey all, I've been searching for references on the web and even been to stores ;) regarding the above subject. But I am reluctant to purchase ATM because there are so many books out there on the subject. So I figured I'd go to the list and ask the question. What are peoples opinion's on the best book(s) covering the following topics? 1. Creation 2. Normilazation 3. Optimization 4. Managing 5. Modeling Paul DuBois, MySQL book was amazing one of the best refernces I've ever gone through. Now I'm finding as the databases I'm building are becoming more complex, careful and concise palnning, before implementation, is needed. TIA, CM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A design question
Hi, I have a design question for mysql database that I am wanting to create a my movies collection. I was going to do as one-to-one database, but I see now that I need to do as a one-to-many. I have never done a one-to-many but I see that is the way to go. I guess then is can someone show me where I can see one-to-many example, and how you do sql statements? To make it clear, I know that I have to do table for the movie, basic information. Then a table for actor/actress. I know I need a field in both tables that are the same so they are related. I am sorry if I don't make sense, but my head is spinning with trying to understand on one-to-many works. Chuck Payne Magi Design and Support - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
Ok, I spoke to soon... And this is starting to drive me up the walls now... i.e. getting REALLY irritating and frustrating. mysql DESCRIBE monitorhosts; +--+--+--+-+-+-- --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+-- --+ | HostID | tinyint(4) | | PRI | NULL| auto_increment | | CompanyID| tinyint(4) | | MUL | 0 | | +--+--+--+-+-+-- --+ 2 rows in set (0.29 sec) mysql DESCRIBE monitorhostgroupdetails; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | HostGroupDetailID | mediumint(9) | | PRI | NULL| auto_increment | | HostGroupID | mediumint(9) | | MUL | 0 || | HostID| mediumint(9) | | MUL | 0 || +---+--+--+-+-++ 3 rows in set (0.02 sec) mysql Now, here's the catch The HostID field from monitorhosts, is a itemID for a server entry. This server entry is unique, can be in one, multiple or even in no group AT ALL. monitorhostgroupdetails maps a hostID to a groupID, and consist of a unique ID (i.e. only one unique hostID is allowed in one GroupID) All the statements I got so far, list only the hostID if they are in a group... Not if they aren't in a group AT ALL. monitorhosts table data: | 1 | 1 | | 7 | 1 | From this, I can see that I have HostID 1 7, assigned to CompanyID 1. monitorhostgroupdetails table data: |51 | 1 | 1 | Here, I can see HostID 1 belongs to HostGroup 1. The results on all the various queries I tried and that's been suggested... Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; This returns no data from the monitorhosts table, even through we have HostID which isn't in a group. When HostGroupID is changed to 2, HostID 1 is returned (because it is in the hostgroup table), but hostid 7 which isn't in a group, is still left out of the equasion. SELECT monitorhosts.HostID, monitorhosts.HostDescription FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1' AND monitorhosts.CompanyID='1'; Nothing is returned. My gut tells me the same happens as with the previous query. In otherwords, the queries will *only* return any hostID data, if the host is in the monitorhostgroupdetails table, which, isn't going to be right. A host is registered in the database, and only certain hosts, under certain curcumstances is grouped. Basically, what I want to do now, is that when a client modifies the servers assigned in a group, I only want to list the servers which is NOT allready in that specific group. Is this possible, or am I really going to have to use PHP arrays and compare arrays with hundreds of thousands of values in them?? *deep sigh* - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:16 PM Subject: Re: sub-queries *MWAH*!!! Thanks a million, tested and working beautifully Can't believe in two days I didn't think of this... -- me - Original Message - From: Sabine Richter [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:14 PM Subject: Re: sub-queries SELECT monitorhosts.HostID FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1'); Gruss Sabine Chris Knipe wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail
Thanks
Thanks Charles for your patience and help. I removed winmysqladmin from startup and ALL problems went away! When winmysqladmin started up on the other user accounts it would somehow block connections to MySQL (since before I uninstalled, on normal [non-admin] user accounts, I could not connect to the db - attention Chris - and the stopped service could not be started from the console - even though it showed up as listening on the correct port no connection could be made). Now I can connect to the db on all user accounts. I don't really know why this happened or how, but thanks for walking me through it. It was a new install on a new machine, and I appreciate all the help from everybody. cheers, Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Thanks
Strange indeed... Might be worth checking if this can be reproduced... This can perhaps be something worth mentioning in winmysqladmin (possible bug?) - Original Message - From: Kirk Brannan Babb [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:59 PM Subject: Thanks Thanks Charles for your patience and help. I removed winmysqladmin from startup and ALL problems went away! When winmysqladmin started up on the other user accounts it would somehow block connections to MySQL (since before I uninstalled, on normal [non-admin] user accounts, I could not connect to the db - attention Chris - and the stopped service could not be started from the console - even though it showed up as listening on the correct port no connection could be made). Now I can connect to the db on all user accounts. I don't really know why this happened or how, but thanks for walking me through it. It was a new install on a new machine, and I appreciate all the help from everybody. cheers, Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB Hot Backups... ALL OR NOTHING ???
Can you backup/restore just one MySQL database with InnoDB hot backup? From what I gather it's an all or nothing proposition. As I understand it, there's only one tablespace (with any number of data files) and all database tables of type 'InnoDB' are comingled in the one tablespace. Therefore, if a single datafile becomes corrupt, all the databases with InnoDB type tables are down and you have to restore everything. Is that right? If so are there any plans to have multiple named tablespaces? We have a single server with 150+ databases (one for each hosted customer). If one customer database goes down then we can restore the MYISAM type tables without affecting the 24X7 availability for the other 149 customers. However, if we convert tables to type InnoDB and a data file is corrupted or lost, then all databases are down and we have to restore EVERYTHING. Is this correct? Sincere thanks in advance... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thread based optimization?
Dear list, After doing a good bit of reading, the details are still hazy. I have a web-based application (about 20 tables, with up to 100,000 records per table - but most tables are much smaller). 99% of select queries are very very fast. However, there is the occasional select query that requires a good amount of work on the database. It may take a few seconds to return. This is okay, however, these queries drastically affect the performance of other users. Is there a smart way to limit system resources per thread? I'd much rather have the long-query person wait an extra few seconds than have several short-query people wait. I've tried tuning all the typical parameters, but I don't think the logjam is memory-based (the hard disk doesn't move). The database server is Win2k, 512Mb memory, PIII @ 450mhz. Variables are as below. Thanks in advance, Matt back_log| 50 basedir | C:\mysql\ binlog_cache_size | 32768 character_set | latin1 character_sets | concurrent_insert | ON connect_timeout | 5 datadir | C:\mysql\data\ delay_key_write | ON delayed_insert_limit| 100 delayed_insert_timeout | 300 delayed_queue_size | 1000 flush | OFF flush_time | 0 have_bdb| NO have_gemini | NO have_innodb | NO have_isam | YES have_raid | NO have_openssl| NO init_file | interactive_timeout | 28800 join_buffer_size| 131072 key_buffer_size | 67104768 language| C:\mysql\share\english\ large_files_support | ON log | OFF log_update | OFF log_bin | ON log_slave_updates | OFF log_long_queries| ON long_query_time | 1 low_priority_updates| OFF lower_case_table_names | 1 max_allowed_packet | 1047552 max_binlog_cache_size | 4294967295 max_binlog_size | 1073741824 max_connections | 100 max_connect_errors | 10 max_delayed_threads | 20 max_heap_table_size | 16777216 max_join_size | 4294967295 max_sort_length | 1024 max_user_connections| 0 max_tmp_tables | 32 max_write_lock_count| 4294967295 myisam_max_extra_sort_file_size | 256 myisam_max_sort_file_size | 2047 myisam_recover_options | 0 myisam_sort_buffer_size | 67108864 net_buffer_length | 16384 net_read_timeout| 30 net_retry_count | 10 net_write_timeout | 60 open_files_limit| 0 pid_file| C:\mysql\data\oceanbeach.pi port| 3306 protocol_version| 10 record_buffer | 1044480 record_rnd_buffer | 1044480 query_buffer_size | 0 safe_show_database | OFF server_id | 1 slave_net_timeout | 3600 skip_locking| ON skip_networking | OFF skip_show_database | OFF slow_launch_time| 2 socket | MySQL sort_buffer | 1048568 sql_mode| 0 table_cache | 100 table_type | MYISAM thread_cache_size | 8 thread_stack| 65536 transaction_isolation | READ-COMMITTED timezone| Pacific Daylight Time tmp_table_size | 33554432 tmpdir | C:\WINNT\TEMP\ version | 3.23.49-nt-log wait_timeout| 28800 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Thanks
You are very welcome. I am glad I was able to help. Charles Q. At 03:58 PM 6/6/2002 -0500, k wrote: Thanks Charles for your patience and help. I removed winmysqladmin from startup and ALL problems went away! When winmysqladmin started up on the other user accounts it would somehow block connections to MySQL (since before I uninstalled, on normal [non-admin] user accounts, I could not connect to the db - attention Chris - and the stopped service could not be started from the console - even though it showed up as listening on the correct port no connection could be made). Now I can connect to the db on all user accounts. I don't really know why this happened or how, but thanks for walking me through it. I appreciate all the help from everybody. cheers, Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql binding to a port
We have a situation that I was wondering if anyone had an answer to. We have a situation where we have multiple mysql boxes being used in random order for load balancing and fault tolerance. Basically the app chooses one of the machines at random, and if it can't get a connection, it tries another. Here is the situation that happens to us. When we bring one mysql down, no big deal, any request that would have been destined for that machine goes to another machine. This part works well. The problem occurs when we start up mysql. Mysql binds to the port, then it takes about 15 seconds for innodb to startup and get ready to start serving requests. Problem is that in this 15 seconds a few hundred connections have queued up to mysql. When innodb is ready to start serving data, all of these query's hit the base and we have a backlog of requests to serve. This normally takes quite a bit of time before it is able to recover. My question is this. Is there a way to have innodb get ready and do everything it needs to do to serve data before mysql binds to the port? This way until the base is ready to serve requests, incoming clients cannot get a connection. Thanks... --shak -- Shakeel Sorathia Systems Administrator (213) 739-5348 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql binding to a port
On Thu, Jun 06, 2002 at 02:49:05PM -0700, Shakeel Sorathia wrote: we start up mysql. Mysql binds to the port, then it takes about 15 seconds for innodb to startup and get ready to start serving requests. Problem is that in this 15 seconds a few hundred connections have queued up to mysql. When innodb is ready to start serving data, all of these query's hit the base and we have a backlog of requests to serve. This normally takes quite a bit of time before it is able to recover. I don't know of any clean solutions, but how about this for a hack? In your mysql startup scripts, before mysql starts, configure a firewall rule to reject mysql connections from over the network. Then after mysql is started, run a utility that connects to mysql via UNIX domain sockets, selects a row from an innodb table, and then removes the firewall rule. The select will block until innodb startup is complete, and in the meantime clients will keep trying other servers. Ick, I feel unclean just writing that. /me washes his hands. -- Michael Bacarella | Netgraft Corporation | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
full text with union pb
Hi all,
I ve got some errors while making a query that managing match (full text)
and union,
both on boolean mode and the other mode.
When I m working with like i get no error.
I m working with mysql 4.01 4.02.
Tables have the sames number of fields ...
Indexes seems to have been created ...
See what type of query i make :
SELECT 'FR' as lang,* FROM `Linux-FR`.Stories WHERE match
(Summary,Content,Heading) against ('words' IN BOOLEAN MODE) AND Verified
= 'Y'
union SELECT 'US' as lang,* FROM `Linux-US`.Stories WHERE match
(Summary,Content,Heading) against ('words' IN BOOLEAN MODE) AND Verified
= 'Y'
I get :
Got error -1 from table handler
here is the create statement
CREATE TABLE `Stories` (
`Rid` varchar(16) NOT NULL default '',
`Verified` char(1) default NULL,
`Score` int(11) default NULL,
`Heading` varchar(96) default NULL,
`Summary` text,
`Content` text,
`Author` varchar(32) default NULL,
`AuthorEmail` varchar(96) default NULL,
`AuthorURL` varchar(96) default NULL,
`Birthstamp` datetime default NULL,
PRIMARY KEY (`Rid`),
FULLTEXT KEY `Summary` (`Summary`,`Content`,`Heading`,`Author`),
FULLTEXT KEY `Summary_2` (`Summary`,`Content`,`Heading`,`Author`),
FULLTEXT KEY `Summary_3` (`Summary`,`Content`,`Heading`,`Author`),
FULLTEXT KEY `Summary_4` (`Summary`,`Content`,`Heading`,`Author`),
FULLTEXT KEY `Summary_5` (`Summary`,`Content`,`Heading`,`Author`)
FULLTEXT KEY `Summary_6` (`Summary`,`Content`,`Heading`,`Author`)
FULLTEXT KEY `Summary_7` (`Summary`,`Content`,`Heading`,`Author`)
) TYPE=MyISAM;
Thanks.
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sub-queries
Chris, sounds like you're looking for an exclusive left outer join. You want to see records from monitorhosts, where there is no corresponding record in monitorhostgroupdetails, right? Use: Select * from monitorhosts left join monitorhostgroupdetails on monitorhosts.HostID = monitorhostgroupdetails.HostID where monitorhostgroupdetails.HostID is null ; ++---+---+-++ | hostID | CompanyID | HostGroupDetailID | HostGroupID | HostID | ++---+---+-++ | 7 | 1 | NULL |NULL | NULL | ++---+---+-++ The trick is the is null which trims the result set to only show those join-results which could find no right-hand record to join to. hth, Kevin -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 1:52 PM To: [EMAIL PROTECTED] Subject: Re: sub-queries Ok, I spoke to soon... And this is starting to drive me up the walls now... i.e. getting REALLY irritating and frustrating. mysql DESCRIBE monitorhosts; +--+--+--+-+-+ -- --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+ -- --+ | HostID | tinyint(4) | | PRI | NULL| auto_increment | | CompanyID| tinyint(4) | | MUL | 0 | | +--+--+--+-+-+ -- --+ 2 rows in set (0.29 sec) mysql DESCRIBE monitorhostgroupdetails; +---+--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+--- -+ | HostGroupDetailID | mediumint(9) | | PRI | NULL| auto_increment | | HostGroupID | mediumint(9) | | MUL | 0 | | | HostID| mediumint(9) | | MUL | 0 | | +---+--+--+-+-+--- -+ 3 rows in set (0.02 sec) mysql Now, here's the catch The HostID field from monitorhosts, is a itemID for a server entry. This server entry is unique, can be in one, multiple or even in no group AT ALL. monitorhostgroupdetails maps a hostID to a groupID, and consist of a unique ID (i.e. only one unique hostID is allowed in one GroupID) All the statements I got so far, list only the hostID if they are in a group... Not if they aren't in a group AT ALL. monitorhosts table data: | 1 | 1 | | 7 | 1 | From this, I can see that I have HostID 1 7, assigned to CompanyID 1. monitorhostgroupdetails table data: |51 | 1 | 1 | Here, I can see HostID 1 belongs to HostGroup 1. The results on all the various queries I tried and that's been suggested... Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; This returns no data from the monitorhosts table, even through we have HostID which isn't in a group. When HostGroupID is changed to 2, HostID 1 is returned (because it is in the hostgroup table), but hostid 7 which isn't in a group, is still left out of the equasion. SELECT monitorhosts.HostID, monitorhosts.HostDescription FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1' AND monitorhosts.CompanyID='1'; Nothing is returned. My gut tells me the same happens as with the previous query. In otherwords, the queries will *only* return any hostID data, if the host is in the monitorhostgroupdetails table, which, isn't going to be right. A host is registered in the database, and only certain hosts, under certain curcumstances is grouped. Basically, what I want to do now, is that when a client modifies the servers assigned in a group, I only want to list the servers which is NOT allready in that specific group. Is this possible, or am I really going to have to use PHP arrays and compare arrays with hundreds of thousands of values in them?? *deep sigh* - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:16 PM Subject: Re: sub-queries *MWAH*!!! Thanks a million, tested and working beautifully Can't believe in two days I didn't think of this... -- me - Original Message - From: Sabine Richter [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:14 PM Subject: Re:
Re: sub-queries
- Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'Chris Knipe' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, June 07, 2002 12:38 AM Subject: RE: sub-queries Chris, sounds like you're looking for an exclusive left outer join. You want to see records from monitorhosts, where there is no corresponding record in monitorhostgroupdetails, right? Yes - and no. If a HostID 1, assigned to GroupID 1, 3, and 4, then I want to issue a query where I specify the GroupID to be 2, and HostID 1 must be returned. All the join statements I got so far, will only include the hostID if it is not in monitorhostgroupdetails AT ALL. Use: Select * from monitorhosts left join monitorhostgroupdetails on monitorhosts.HostID = monitorhostgroupdetails.HostID where monitorhostgroupdetails.HostID is null 1) I can't specify which GroupID I want to exclude, and 2) I can't limit the hostID on a CompanyID basis (only show hosts belonging to a certain company). 3) This now, shows all the hosts that is not in a group at all. I need to specify which hostID, is NOT IN a GroupID. I can't believe that this is so difficult ?!?!?!?!? Alternative measures Can this be done in two or three different queries?? I really need to find a solution for this... :-(( ++---+---+-++ | hostID | CompanyID | HostGroupDetailID | HostGroupID | HostID | ++---+---+-++ | 7 | 1 | NULL |NULL | NULL | ++---+---+-++ The trick is the is null which trims the result set to only show those join-results which could find no right-hand record to join to. hth, Kevin -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 1:52 PM To: [EMAIL PROTECTED] Subject: Re: sub-queries Ok, I spoke to soon... And this is starting to drive me up the walls now... i.e. getting REALLY irritating and frustrating. mysql DESCRIBE monitorhosts; +--+--+--+-+-+ -- --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+ -- --+ | HostID | tinyint(4) | | PRI | NULL| auto_increment | | CompanyID| tinyint(4) | | MUL | 0 | | +--+--+--+-+-+ -- --+ 2 rows in set (0.29 sec) mysql DESCRIBE monitorhostgroupdetails; +---+--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+--- -+ | HostGroupDetailID | mediumint(9) | | PRI | NULL| auto_increment | | HostGroupID | mediumint(9) | | MUL | 0 | | | HostID| mediumint(9) | | MUL | 0 | | +---+--+--+-+-+--- -+ 3 rows in set (0.02 sec) mysql Now, here's the catch The HostID field from monitorhosts, is a itemID for a server entry. This server entry is unique, can be in one, multiple or even in no group AT ALL. monitorhostgroupdetails maps a hostID to a groupID, and consist of a unique ID (i.e. only one unique hostID is allowed in one GroupID) All the statements I got so far, list only the hostID if they are in a group... Not if they aren't in a group AT ALL. monitorhosts table data: | 1 | 1 | | 7 | 1 | From this, I can see that I have HostID 1 7, assigned to CompanyID 1. monitorhostgroupdetails table data: |51 | 1 | 1 | Here, I can see HostID 1 belongs to HostGroup 1. The results on all the various queries I tried and that's been suggested... Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; This returns no data from the monitorhosts table, even through we have HostID which isn't in a group. When HostGroupID is changed to 2, HostID 1 is returned (because it is in the hostgroup table), but hostid 7 which isn't in a group, is still left out of the equasion. SELECT monitorhosts.HostID, monitorhosts.HostDescription FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1' AND monitorhosts.CompanyID='1'; Nothing is returned. My gut tells me the same happens as with the previous query. In otherwords, the queries will *only* return any
mysql database quota problems.
Hello, I want to set quota to mysql database by every database or by every user, I can not find any issues from mysql official website, and I don't think the Linux or FreeBSD File System quota solution is effective, who can help me? Thank you in adanced. Regards, Asong. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
where i can write my script
i want to know where a can write my script in running and use mysql. recently i save in a file a two statement and it work what i expect to be done, i'm wondering what is the most easiest programming language could i use to write my script. for ur info i'm using win98 mysql 3.29.49 __ www.edsamail.com __ www.edsamail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
