RE: problem with linuxthreads on FreeBSD
hi, try these as flags: CFLAGS=-D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/lin uxthreads CXXFLAGS=${CFLAGS} -felide-constructors -fno-rtti -fno-exceptions and configure it with these options: ./configure --with-named-thread-libs='-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib -llthre ad -llgcc_r' of course alter those to match your paths etc it should help regards, terry SQL -Original Message- From: Sumit Mittal [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 5:10 AM To: [EMAIL PROTECTED] Subject: problem with linuxthreads on FreeBSD Hi, I am running MySQL version 3.23.55 on FreeBSD 4.7, linuxthreads 2.2.3_8 and with gcc 2.95.4 . I use the following configuration line : CFLAGS=-g -I/usr/local/include/pthread/linuxthreads CPPFLAGS=-g -I/usr/local/include/pthread/linuxthreads LDFLAGS=-L/usr/local/lib -llgcc_r ./configure --with-pthread --prefix=/usr/mittal/pthreads/install I am able to configure and compile, but mysql seems to hang on certain queries. This happens most of the times, but sometimes the queries do manage to run. I assume this is because some wierd threading stuff is going on. Can someone please throw some light on it, I am stuck. Thanks, sumit mittal - 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: The host or user argument to GRANT is too long ??!!!
On Thursday 13 March 2003 12:25, boka wrote: I have tried to find a solution to my problem on groups, FAQs, with no effect. GRANT Select,Insert,Update,Delete ON `sprzet`.* TO - 'azsxdcfv_azsxdcfvgb'@'azs.azsxdcfvg.az'; ERROR 1145: The host or user argument to GRANT is too long hmm ... mysql show columns from user; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | Host| char(60) binary | | PRI | | | | User| char(50) binary | | PRI | | | Because you changed length of User column manually. The length of the column User is 16, not 50. -- 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: wrong time in m$office
[EMAIL PROTECTED] schrieb: Bob Ramsey schrieb: At 04:10 PM 3/11/2003, [EMAIL PROTECTED] wrote: m$office (office 2000 - win xp) can not handle the time correct. openoffice (win-xp or redhat-phoebe3) has no problems. I believe that's a known problem with Microsoft products. They only appear to be able to handle datetime objects, not date or time. You might also look up the #DELETED# error on the web for similar problems. Bob PS. Cool. This message initially bounced back to me from the mysql list server because it didn't contain one of the following words: sql,query,queries,smallin. Your message cannot be posted because it appears to be either spam or simply off topic to our filter. Now it should get through. thanks Bob ;-) date, datetime, timestamp all are correct in msoffice time is correctly shown in msquery, but not in access,excel, ... [...] perhaps this onehttp://microsoft.com/data/ from http://www.mysql.com/doc/en/MyODBC_clients.html [...] seems to work with: win-xp and office 2000 out of the box with myodbc-win 3.51 no update needed for ms mdac/jet-service insert/update -- #deleted (german #geloescht) need: primarykey(autoincrement) timestamp 'time'-field shows wrong time or is emptyneed: 'datetime' instead 'time' msaccess insert eg.for 10:00 -- 1899-12-30 10:00:00 shown as 10:00:00 for 2003-01-01 -- 2003-01-01- 00:00:00 shown as 2003-01-01 do not forget relinking the table! mysql insert into af (af_dtime_von,af_dtime_bis) values ('18991230 10:00:00','18991230 13:00:00'); works without any problems msoffice handle the time correct :-) i will give it a chance @work with windows-users :-( -- shrek-m - 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
replication on single server
Hey everybody, I want to replicate several tables from one database to all other databases on the same machine, so to say a table-level-replication Simply said, imagine this scenario: A MySQL server with about 10 databases: A and 1 to 9. Database A contains a 'special' table that is supposed to be replicated to all other databases on the same machine, so it is kinda like the master database. The table has the same name on every database. Is such a thing possible and if yes, how? Thanks very much for your help! Thomas - 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
HEPL: LOAD DATA LOCAL skips recors
i have 2 DBFs and i'm doing the same thing on both of them : (i'm transforming the DBFs in TXTs) (in foxpro) copy to file.txt delimited with '^' with character '#' When : LOAD DATA LOCAL INFILE 'file.TXT' into table table on mysql fields terminated by '#' OPTIONALLY ENCLOSED BY '^'; is executed i get 2 different results: 1. from the 1st .txt file ALL the recors are loded 2. from the 2nd it loads only the records 1,3,5,7,9 etc. are loaded. (and i really need all of them , too :)) ) If the txt files i make are with constant leght and i do LOAD DATA LOCAL INFILE 'file.TXT' into table table on mysql fields terminated by ' ' it works just fine, but i need the first way. Please help me. Thank you. -- Sebastian Stan [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: buffer overflows in MySQL error messages (e.g. in 4.0.10-gamma)
Hi! On Mar 12, Maarten LITMAATH wrote: Description: MySQL (e.g. version 4.0.10-gamma) does not check for buffer overflows when formatting error messages: the code just assumes that no message will ever be larger than SC_MAXWIDTH (256), ERRMSGSIZE (SC_MAXWIDTH) or MYSQL_ERRMSG_SIZE (200). This has been observed to lead to memory corruption when the client e.g. tries to redefine a key with a name whose length is of order 200 (yes, that _is_ a realistic use case for computer-handled keys). How-To-Repeat: Define a key with a length of ~200 or more (the longer, the better) and then try to redefine it; observe the client getting an error message that is truncated and/or has trailing garbage. The client and/or the server may then have corrupted their own memories to such an extent that they become unusable and/or crash (both have been observed at least in a client application). Nope. Fails for me. mysql create table aa (bb int, key ccc(bb)); ERROR 1059: Identifier name 'ccc' is too long mysql create table aa (bb int, key c(bb)); ERROR 1059: Identifier name 'cc Error message gets truncated, no overflow. Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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
Prepared statements. Time Stamps.
We are trying to set a TimeStamp in mysql from Java. We use the set TimeStamp funcion in PreparedStatement, but the date is not logged properly in the database. ( We are using a DateTime in the database ). In other words, does JDBC cope with TimeStamps? It was working fine when we had a TimeStamp in the database. I would appreciate a lot your answer. Thanks in advance. Gustavo. *** Gustavo Cebrian Analyst/Programmer Want to improve the ROI on your EAI project? Download RV Tester and reduce your development and testing timescales by as much as 50%. http://www.greenhatconsulting.com/rvtester Green Hat Consulting Ltd. 107 Fleet Street, London EC4A 2AB DDI +44 (0)20 7936 9495 Mobile +44 (0)7788 922291 http://www.greenhatconsulting.com [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
JOIN on same table
Hi there, I have this table: project_group_idref_project_group_idnameroot_project_group_id node_depth inherit 10 0 ProjectA0 0 0 57 10 SubProjectA 10 1 1 59 57 SubProjectB 10 2 1 60 59 SubProjectC 10 3 0 62 60 SubProjectD 10 4 1 project_group_id (int) = primary key. ref_project_group_id (int) = parent project (if root, then 0) name (char) = name of project root_project_group_id (int) = project_group_id for root project (if root, then 0) node_depth (int) = how far down in the tree this child is (if root, then 0) inherit (int) = determines if this project inherit information from parent (no=0, yes=1) This would look somehting like this in a treeview: ProjectA (10) - node_depth = 0 (root) \ SubProjectA (57)- node_depth = 1 \ SubProjectB (59)- node_depth = 2 \ SubProjectC (60)- node_depth = 3 \ SubProjectD (62)- node_depth = 4 I now wan't all child records (inclusive root) for ProjectA that inherit information. This means that I only wan't records with project_group_id = 10, 57, 59, because 60 is NOT inheriting and it's child project (62) is then not able to inherit. I've made the follwoing query: SELECT PGL1.project_group_id, PGL1.ref_project_group_id, PGL1.name, PGL1.permission_inherit, PGL1.node_depth, PGL1.root_project_group_id FROM project_group_list PGL1 LEFT JOIN project_group_list PGL2 ON PGL1.root_project_group_id = PGL2.root_project_group_id WHERE PGL2.root_project_group_id = 10 AND PGL2.permission_inherit = 0 AND PGL1.node_depth PGL2.node_depth ORDER BY PGL1.node_depth But this only gives me 57 and 59, but not 10 (root). This query also have a problem, if all projects is inheriting and there is no records with inherit=0, then the query returns no results. What can I do to solve this problem? I hope someone can help me... /Jakob - 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: buffer overflows in MySQL error messages (e.g. in 4.0.10-gamma)
Sergei Golubchik wrote: On Mar 12, Maarten LITMAATH wrote: Description: MySQL (e.g. version 4.0.10-gamma) does not check for buffer overflows when formatting error messages: the code just assumes that no message will ever be larger than SC_MAXWIDTH (256), ERRMSGSIZE (SC_MAXWIDTH) or MYSQL_ERRMSG_SIZE (200). This has been observed to lead to memory corruption when the client e.g. tries to redefine a key with a name whose length is of order 200 (yes, that _is_ a realistic use case for computer-handled keys). How-To-Repeat: Define a key with a length of ~200 or more (the longer, the better) and then try to redefine it; observe the client getting an error message that is truncated and/or has trailing garbage. The client and/or the server may then have corrupted their own memories to such an extent that they become unusable and/or crash (both have been observed at least in a client application). Nope. Fails for me. mysql create table aa (bb int, key ccc(bb)); ERROR 1059: Identifier name 'ccc ' is too long mysql create table aa (bb int, key c(bb)); ERROR 1059: Identifier name 'ccc ccc Error message gets truncated, no overflow. OK, let me give a bit more information. I am using MySQL through a server (the Globus RLS, if you care about that), which does all the SQL statements. I did some stress testing of that server, trying to break it, e.g. by supplying ridiculously long names for keys, which caused it to corrupt its own memory and/or crash. Debugging that, I ultimately came to the MySQL client code that it uses, and found that it _evidently_ does not check for overflows in error messages. In the debugger I clearly saw the code scribbling beyond the end of an error message buffer, overwriting adjacent fields in some struct. I then patched various routines dealing with error messages (also in MyODBC) and my problem went away. I got truncated error messages too, but _after_ they had overflowed... Furthermore, whenever such overflow happens, the damage depends on the layout of the data segment or stack, so your program may survive, or _seem_ to survive, having corrupted its own memory. Please download the diffs and have a look at them: http://litmaath.home.cern.ch/litmaath/MyODBC-MySQL-patches/ mysql-4.0.10-gamma-ml-diffs.tgz You will see that the problems can easily be fixed once and for all, by using functions like my_snprintf() instead of plain sprintf(). Best regards, Maarten - 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: MATCH / AGAINST fatal bug
Please help! I tried to do simple search using the longtext field using the the MATCH and AGAINST query but to no success. The funny thing is, I can do it in the earlier MySQL version. I have updated the latest MySQL and PHP using the up2date (RH8.0) without any intervention being done. Previous version: 3.23.52 PHP: 4.1.2 Current version : 3.23.54 PHP: 4.2.2 Can somebody try to duplicate my issue. If possible, please send comments/help directly to my email at [EMAIL PROTECTED] Thanks in advance for your help. best regards, Redzuan - 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
cannot connect to MySQL
I'm having a problem connecting to MySQL database using phpMyAdmin from a remote location. I've had a look at the manual but I'm not too clear on what has to be done. I see that the connection is through a socket. Can I change this to connect using an ip connection? Reason I ask is because the MySQL server actually sits behind a firewall (iptables Forward rule) and I have the port 3306 opened and forwarding packets (at least I think I do) to the SQL server. The following error is what I get: --Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Error MySQL said: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd - 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: Prepared statements. Time Stamps.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gustavo Cebrian wrote: We are trying to set a TimeStamp in mysql from Java. We use the set TimeStamp funcion in PreparedStatement, but the date is not logged properly in the database. ( We are using a DateTime in the database ). In other words, does JDBC cope with TimeStamps? It was working fine when we had a TimeStamp in the database. I would appreciate a lot your answer. Thanks in advance. Gustavo. I've never seen a timestamp _not_ work with PreparedStatements...Can you give a more detailed example demonstrating exactly what your particular problem is? -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+cdX+tvXNTca6JD8RAiaLAKC+B4w7OEo6ENUDwHiiA2Rx+WECQQCfelgn Uz6o5I2TJa2W3nwSqLGNPNI= =tUnr -END PGP SIGNATURE- - 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
Hard, unrecoverable InnoDB crash with MySQL 4.0.11
Hi: I'm running MySQL 4.0.11 (MySQL.com binary RPMs) on Red Hat Linux 7.3, all errata applied (kernel 2.4.18-26.7.xsmp). This morning, our server died hard, with the following error: 030314 2:44:13 InnoDB: Assertion failure in thread 7938074 in file btr0btr.c line 298 When the server then tried to restart itself, it died with: InnoDB: Error: trying to access page number 4294396415 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 030314 2:48:11 InnoDB: Assertion failure in thread 12292 in file fil0fil.c line 1176 The server would not come back up. We had to nuke the InnoDB tablespace and logs and restore from backup. I've placed the resolved stack trace and full error logs online at: http://www.oxyopia.com/~yi/stack.resolved http://www.oxyopia.com/~yi/failure.log TIA, -- Yukari Iwatani [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: Apache/PHP question
The way i do it, i don't put the AddType application/x-httpd-php .php in the httpd.conf as a global entry, since i host sites in VirtualHosts, I only place the AddType application/x-httpd-php .php directive in their respective VirtualHost entry for those sites who needs or pays for php based hosting. .htaccess is also limited so that they wouldn't override the configuration. Am not sure how to implement it on a directory based hosting though. Respectfully yours, Sherwin T. Ang Systems Administrator Tridel Technologies Incorporated 7F Hanston Building Emerald Avenue, Ortigas Center Pasig City 1605 Philippines Phone: 6345140 Local 1024 Web: http://www.tridel.net Email: [EMAIL PROTECTED] - Original Message - From: Defryn, Guy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:38 AM Subject: Apache/PHP question I have set apache 2.0 and PHP 4.3.1 as well as mysql. I was wondering how I can disable PHP per user directory?. At the moment everyone can put php scripts on their personal website. I want to limit it to a few users. Cheers 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 - 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
Connection String?
I am trying to connect to MySQL without using a DSN entry, this is my connection string DRIVER={MySQL ODBC 3.51 Driver};SRVR=159.113.44.49;DB=csapps;UID=user;PWD=password The error I get is Invalid connection string attribute Any ideas? Nelson Tamayo Claims Automation (800) 237-7767 x3520 [EMAIL PROTECTED] The information contained in this transmission is legally privileged and confidential information intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copy of this transmission is strictly prohibited. If you have received this transmission in error, please contact the sender immediately. - 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: translation dictionary
I guess you'd have to use MySQL 4.1 to assign different character sets to different columns. Should be straight-forward at time of table creation. David *** Hello, I'm interested in building a translation dictionary: russian - french. My database would have only one table which contains in each entry a the word and its translation. I intend to put such a dictionary on a web page. I would like to ask for some ideas on how to build such a dictionary given that I can not use more than one set of characters in mysql server. ( the russian words need on set of characters , the french ones another ). I would appreciate any hint. kind regards, Felix - 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: Null values set in tables
NOT NULL does not mean you can not leave the field empty. It means the field can not contain the value NULL All the empty fields get the default values you declared. If you did not declare a default value, standard default values for each data type are used. Steve Holt wrote: I am linking an MS Access front end to MYSQL on the back with ODBC If I open the table I can enter a value in the course name field only which is the primary key and it will save the record even though I have not entered values in the fields set up as NOT NULL I have an ODBC trace file but it seems quite large to post to the list At 11:17 AM 03/12/2003 -0600, gerald_clark wrote: You have to show us. Steve Holt wrote: I am sure I am missing something very simple but I cant see it I am using MS Access 97 as a front end to MYSQL I have a table with the following desc CREATE TABLE `tblcoursesoffered` ( `DateCourseCreated` datetime default NULL, `Specialty` int(25) NOT NULL default '0', `CourseNo` float NOT NULL default '0', `CourseName` char(10) NOT NULL default '', `CourseDesc` char(255) default NULL, `ExamType` char(30) default NULL, `BeginDate` datetime NOT NULL default '-00-00 00:00:00', `EndDate` datetime NOT NULL default '-00-00 00:00:00', `ActualCity` char(30) NOT NULL default '', `ActualState` char(2) NOT NULL default '', `CourseStatus` char(15) default NULL, `CourseCoordinator` float default NULL, `ProjAttendance` float default NULL, `CMEHrsCourse` float default NULL, `CMEHrsAdditional` float default NULL, `CMEFee` double default NULL, `CAT1` tinyint(1) default NULL, `SponsorID` char(6) default NULL, `SponsorContact` char(50) default NULL, `SponsorContactPhone` char(13) default NULL, `SponsorContactFax` char(13) default NULL, `CourseHotel` int(11) default NULL, `FacSleepingRoomsQty` float default NULL, `StaffSleepingRoomsQty` float default NULL, `fldCourseHotel` int(11) default NULL, `fldTransportationInfo` char(255) default NULL, `fldTimestamp` timestamp(14) NOT NULL, PRIMARY KEY (`CourseName`), KEY `Specialty` (`Specialty`), KEY `BeginDate` (`BeginDate`) ) TYPE=MyISAM ROW_FORMAT=FIXED; Even with this description MYSQL allows me to create a new record with null values in the fields listed as not null is there another setting somewhere I am missing?? Thanks Steve Holt - 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
running an mysql client on a linux cluster?
Hello, I wonder if anyone has encountered this problem before and has any ideas. I need to run programs on a linux cluster that make client connections to an mysql database on a different server. this linux cluster is set up where only the main node has an internet connection. so the problem is how make it possible for programs running on the internal nodes to make connections to the mysql server. This may not be a strictly an mysql question. but I am in a little bit of a bind and would greatly appreciate any help if anyone has experience dealing with a similar situation. many thanks Murad - 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: translation dictionary
You can try UTF-8. - Cs. I guess you'd have to use MySQL 4.1 to assign different character sets to different columns. Should be straight-forward at time of table creation. David *** Hello, I'm interested in building a translation dictionary: russian - french. My database would have only one table which contains in each entry a the word and its translation. I intend to put such a dictionary on a web page. I would like to ask for some ideas on how to build such a dictionary given that I can not use more than one set of characters in mysql server. ( the russian words need on set of characters , the french ones another ). I would appreciate any hint. - 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: Two mysql servers on the same database directory
Vlad Shalnev wrote: Hi, All Can two mysql servers work on the same database directory ? It it possible in read only mode or in read and write mode ? With best regards Yes, if you don't disable file locking. Why would you do this though? Each server will have to lock the files, blocking the other server. - 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
How to implement this query - can you help pls?
Hi, Try as I might, I can't seem to work out the SQL for this query. Can you help please? I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice I have the basics; SELECT P.prodID, P.prodName, PLI.itemPrice FROM products P, priceList PL, priceListItems PLI, persons PE, intPerPriceList IPPL, categories C .. I can't figure this bit out WHERE PL.validFrom CURRENT_DATE AND PL.validTo CURRENT_DATE AND C.catID = x AND PE.perID = y But what I keep getting all messed up is the joining up in between. I think I need to INNER JOIN to the intersection table, but how to then add the joins to the other relevant tables. Can you help please? Thanks in advance. Kyle - 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: cannot connect to MySQL
Please read the warning. It attempting a LOCAL connection. Please enter the host ( server) you wish to connect to. Jon Miller wrote: I'm having a problem connecting to MySQL database using phpMyAdmin from a remote location. I've had a look at the manual but I'm not too clear on what has to be done. I see that the connection is through a socket. Can I change this to connect using an ip connection? Reason I ask is because the MySQL server actually sits behind a firewall (iptables Forward rule) and I have the port 3306 opened and forwarding packets (at least I think I do) to the SQL server. The following error is what I get: --Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Error MySQL said: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd - 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: Looking for a bona fide distributed database that is open source
Gelu Gogancea wrote: To anyone that *didn't see the forest because of the trees*. You already find it. Is MySQL really distributed, or just client-server? I've seen no indication of distributed capabilities; I'm new to MySQL, so maybe I just haven't read enough. But a distributed DBMS should be able to execute a single query which transparently accesses multiple database installations on multiple computers, possibly on multiple platforms. For instance, SELECT * from a, b ON a.id = b.id; where table 'a' is in a database on a Windows/2000 computer and table 'b' is on a Linux machine. The DBMS should keep track of which table is in which database on which computer, and its optimizer should be capable of figuring out an efficient way of resolving such queries. Can MySQL do that? Bruce Feist - Original Message - From: james [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:44 PM I am looking for a bona fide distributed database system, like Oracle or SQLServer, but open source running on Linux. - 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: HEPL: LOAD DATA LOCAL skips recors
At 11:50 +0200 3/14/03, Sebastian Stan wrote: i have 2 DBFs and i'm doing the same thing on both of them : (i'm transforming the DBFs in TXTs) (in foxpro) copy to file.txt delimited with '^' with character '#' When : LOAD DATA LOCAL INFILE 'file.TXT' into table table on mysql fields terminated by '#' OPTIONALLY ENCLOSED BY '^'; is executed i get 2 different results: 1. from the 1st .txt file ALL the recors are loded 2. from the 2nd it loads only the records 1,3,5,7,9 etc. are loaded. (and i really need all of them , too :)) ) If the txt files i make are with constant leght and i do LOAD DATA LOCAL INFILE 'file.TXT' into table table on mysql fields terminated by ' ' it works just fine, but i need the first way. This is typically a problem of not indicating correctly what the end of line character sequence is. How do your lines end? Linefeeds? Carriage returns? Carriage-return/linefeed pairs? If it's something other than linefeeds, you'll need a LINES TERMINATED BY clause. Please help me. Thank you. -- Sebastian Stan [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: How to implement this query - Sorry about the receipt folks
Sorry people. I switched the receipt off originally, but the filter on the server sent it back and I just replied forgetting the receipt 2nd time round. Apologies. Kyle -Original Message- From: Kyle Lange [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 15:46 To: [EMAIL PROTECTED] Subject: How to implement this query - can you help pls? Hi, Try as I might, I can't seem to work out the SQL for this query. Can you help please? I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice I have the basics; SELECT P.prodID, P.prodName, PLI.itemPrice FROM products P, priceList PL, priceListItems PLI, persons PE, intPerPriceList IPPL, categories C .. I can't figure this bit out WHERE PL.validFrom CURRENT_DATE AND PL.validTo CURRENT_DATE AND C.catID = x AND PE.perID = y But what I keep getting all messed up is the joining up in between. I think I need to INNER JOIN to the intersection table, but how to then add the joins to the other relevant tables. Can you help please? Thanks in advance. Kyle - 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
FreeBSD + MySQL bottleneck
Howdy list, I run MySQL 3.23.54 with FreeBSD 4.6-RELEASE. We recently had a BBS get hammered by a lot of concentrated traffic. I currently run a 'mysql-optimize.sh' script from cron on Wednesday and Sunday that executes: ${bindir}/myisamchk -i -r --check --sort-index --analyze ${datadir}/*/*.MYI ${bindir}/isamchk -i -r --analyze --sort-index ${datadir}/*/*.ISM This works great to keep my databases lean and mean for normal server load and traffic, but this last hit was just too much. The server was bottlenecked somewhere. Problem was, I couldn't figure out where! I'm running SCSI 160 disks in a Raid config, with a dual 1GHZ PIII and 1G of SDRAM. I'd think that setup would be able to handle some pretty killer loads... Anyway, I ran 'top', and MySQL was turning about 97% processor utilitzation on one processor. It said I still had 128M of free ram left (and my MySQL tables are all under 10M). And I was only using 3% swap, which is normal because I run phpa_accelerator. Also, 'mysql show status;' showed that I only had about 25 threads open at a time. And it also said I had 82 tables open. My T1s were NOT maxed out. They weren't even half full, and besides: I accessed the BBS from our 100Mb switch, and it was still dog slow (20-30 seconds for a page load), which means it was purely a bottleneck in my machine. So, my questions are these: --- Can any experienced MySQL-FreeBSD admins out there give me some pointers for identifying bottlenecks? Specifically, I don't know how to determine if my disks were being maxed out. Could someone give me some pointers? And also, from 'show status', is the number of 'threads' directly related to the specific number of MySQL socket connections? (I have MySQL setup to allow up to 200 and I wasn't even getting over 30 from 'netstat | grep -i mysql') Any help would be appreciated! Thanks! Matthias - 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: Syntax to create admin user for a database
You are mixing table and global grants together here. Use separate grant commands. I am too lazy to look it up right now, but FILE is global. I am not sure about CREATE and DROP. Aso, I believe that should be 'id'@'127.0.0.1' not localhost. localhost always connects to the pipe. SSH connects to an IP socket. DuSTiN KRySaK wrote: Hi there, I am still having issues creating a user that can access a database. The only time the user can access is if I SSH into my linux box (redhat 8) and connect with: # mysql -u id -p It works fine when doing that, and I can create and do everything I need to do. Now - I am trying to connect my database to coldfusion (create the data source) and that is pretty straight forward. So the ONLY thing I can think of is that I am fudging my user id's when I create them in mysql. SO.. I just want to double check this syntax with all of you to make sure I created the user in such a way that I can access mySQL from anyhostname, etc. # mysql -u root -p MysqlGRANT SELECT, INSERT, UPDATE, DELETE, -CREATE, DROP, ALTER, INDEX, FILE ON -database.* TO [EMAIL PROTECTED] -IDENTIFIED BY 'password'; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql diff for mysql
Hi, I'm searching for a mysql diff tool to update my database when changing structure and so. Does anybody outthere knowing such a tool ? Thanks - Florent - 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: translation dictionary
On 14 Mar 2003, at 0:28, felix t wrote: I would like to ask for some ideas on how to build such a dictionary given that I can not use more than one set of characters in mysql server. ( the russian words need on set of characters , the french ones another ). Presumably you're using Unicode, so that you can display both French and Russian on the same page. It might work for you to store the words as plain binary data in your tables, since the character set is relevant mostly for sorting and you won't be doing that unless you're going to allow browsing through the dictionary entries as opposed to looking up individual ones. You'd also have to take care of letter case in your application rather than in MySQL, so that you can store your values as (for example) all lowercase and then convert your search string to lowercase before putting it into your query. I haven't actually done anything with character sets yet, so I could be missing an important point. Maybe someone with more experience will chime in if I have. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: How to implement this query - can you help pls?
Kyle Lange wrote: I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice I have the basics; SELECT P.prodID, P.prodName, PLI.itemPrice FROM products P, priceList PL, priceListItems PLI, persons PE, intPerPriceList IPPL, categories C .. I can't figure this bit out WHERE PL.validFrom CURRENT_DATE AND PL.validTo CURRENT_DATE AND C.catID = x AND PE.perID = y You can use the syntax you started out with, as long as you add some join conditions. It looks to me as though all joins involved are inner joins. Also, I don't think you actually need to retrieve anything from categories -- you have that information in products already. Given that, try: SELECT P.prodID, P.prodName, PLI.itemPrice FROM products P, priceList PL, priceListItems PLI, persons PE, intPerPriceList IPPL WHERE PL.validFrom CURRENT_DATE AND PL.validTo CURRENT_DATE AND P.catID = x AND PE.perID = y AND PE.perID = IPPL.perID /* join persons to intPerPriceList */ AND IPPL.listID = PL.listID /* to priceList */ AND IPPL.listID = PLI.listID /* to priceListItems */ AND PLI.prodID = P.prodID /* to products */ ; Alternatively, if you want to use the newer INNER JOIN syntax, we just rearrange it and modify it a bit: SELECT P.prodID, P.prodName, PLI.itemPrice FROM persons PE INNER JOIN intPerPriceList IPPL ON PE.perID = IPPL.perID INNER JOIN priceList PL ON IPPL.listID = PL.listID INNER JOIN priceListItems PLI ON IPPL.listID = PLI.listID INNER JOIN products P ON PLI.prodID = P.prodID WHERE PL.validFrom CURRENT_DATE AND PL.validTo CURRENT_DATE AND P.catID = x AND PE.perID = y ; Also, I suspect that you wanted = and = in your date comparisons above. Bruce Feist PS: Did you know that if you don't include one of the following words: sql,query,queries,smallint your message won't get posted here? BF - 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: Looking for a bona fide distributed database that is open source
No, it is not distributed. Bruce Feist wrote: Gelu Gogancea wrote: To anyone that *didn't see the forest because of the trees*. You already find it. Is MySQL really distributed, or just client-server? I've seen no indication of distributed capabilities; I'm new to MySQL, so maybe I just haven't read enough. But a distributed DBMS should be able to execute a single query which transparently accesses multiple database installations on multiple computers, possibly on multiple platforms. For instance, SELECT * from a, b ON a.id = b.id; where table 'a' is in a database on a Windows/2000 computer and table 'b' is on a Linux machine. The DBMS should keep track of which table is in which database on which computer, and its optimizer should be capable of figuring out an efficient way of resolving such queries. Can MySQL do that? Bruce Feist - 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: running an mysql client on a linux cluster?
Murad Nayal wrote: Hello, I wonder if anyone has encountered this problem before and has any ideas. I need to run programs on a linux cluster that make client connections to an mysql database on a different server. this linux cluster is set up where only the main node has an internet connection. so the problem is how make it possible for programs running on the internal nodes to make connections to the mysql server. This may not be a strictly an mysql question. but I am in a little bit of a bind and would greatly appreciate any help if anyone has experience dealing with a similar situation. many thanks Murad - 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 Murad, You could write a program that runs on the master node which makes the request on behalf of the slave/secondary nodes and then returns the result back. We use something similar to this that we call a broker. The broker is the only thing that talks to the database. All clients make requests to the broker , the broker gets/updates the info, and returns the result to the client. It's a very simple concept and has worked well. We wrote it to get around Oracle licensing (only one user connected to the db, but we could handle requests from multiple web servers). Hope this helps! walt - 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: HEPL: LOAD DATA LOCAL skips recors
In addition I would suggest to verify that your text file does not end with a CR or LF character. This happens very often on Windows systems when for instance saving an Excel sheet as a text file. If you do not remove this last character(s), you risk inserting a blank row in the DB. Ignatius - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Sebastian Stan [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:02 PM Subject: Re: HEPL: LOAD DATA LOCAL skips recors At 11:50 +0200 3/14/03, Sebastian Stan wrote: i have 2 DBFs and i'm doing the same thing on both of them : (i'm transforming the DBFs in TXTs) (in foxpro) copy to file.txt delimited with '^' with character '#' When : LOAD DATA LOCAL INFILE 'file.TXT' into table table on mysql fields terminated by '#' OPTIONALLY ENCLOSED BY '^'; is executed i get 2 different results: 1. from the 1st .txt file ALL the recors are loded 2. from the 2nd it loads only the records 1,3,5,7,9 etc. are loaded. (and i really need all of them , too :)) ) If the txt files i make are with constant leght and i do LOAD DATA LOCAL INFILE 'file.TXT' into table table on mysql fields terminated by ' ' it works just fine, but i need the first way. This is typically a problem of not indicating correctly what the end of line character sequence is. How do your lines end? Linefeeds? Carriage returns? Carriage-return/linefeed pairs? If it's something other than linefeeds, you'll need a LINES TERMINATED BY clause. Please help me. Thank you. -- Sebastian Stan [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 - 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: Connection String?
Quoting Tamayo, Nelson [EMAIL PROTECTED]: I am trying to connect to MySQL without using a DSN entry, this is my connection string DRIVER={MySQL ODBC 3.51 Driver};SRVR=159.113.44.49;DB=csapps;UID=user;PWD=password The error I get is Invalid connection string attribute Any ideas? Nelson Tamayo Claims Automation (800) 237-7767 x3520 [EMAIL PROTECTED] The information contained in this transmission is legally privileged and confidential information intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copy of this transmission is strictly prohibited. If you have received this transmission in error, please contact the sender immediately. - 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 Nelson, It sounds like you are trying to connect to MySQL via ADO using a connection string. ADO connection in that manner requires an OLE-DB driver for the database platform if you wish to avoid using an ODBC DSN. I don't think that there's an OLE-DB (at least not a free one) for MySQL yet. Therefore, you will have to use a DSN. Regards, -- Barry C. Hawkins Systems Consultant All Things Computed [EMAIL PROTECTED] 404-795-9147 voice/fax - 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: FreeBSD + MySQL bottleneck
So, my questions are these: --- Can any experienced MySQL-FreeBSD admins out there give me some pointers for identifying bottlenecks? I have been running MySQl-FreeBSD for over a year now and we are now doing about 140-150,000 page views a day all dynamic from the database. Running top displayed MySQL running about 40% but we kept getting bottle necks and mysql would hang periodicaly. What we have done is recompiled mysql with linuxthreads which made a difference, but what made the biggest difference was upgrading to MySQL 4 and turning on query caching, that what I would recomend. Cheers John Wards SportNetwork.net - 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: running an mysql client on a linux cluster?
Thanks a lot Walt, do you happen to have any code examples/fragments handy you can share? thanks again walt wrote: Murad Nayal wrote: Hello, I wonder if anyone has encountered this problem before and has any ideas. I need to run programs on a linux cluster that make client connections to an mysql database on a different server. this linux cluster is set up where only the main node has an internet connection. so the problem is how make it possible for programs running on the internal nodes to make connections to the mysql server. This may not be a strictly an mysql question. but I am in a little bit of a bind and would greatly appreciate any help if anyone has experience dealing with a similar situation. many thanks Murad - 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 Murad, You could write a program that runs on the master node which makes the request on behalf of the slave/secondary nodes and then returns the result back. We use something similar to this that we call a broker. The broker is the only thing that talks to the database. All clients make requests to the broker , the broker gets/updates the info, and returns the result to the client. It's a very simple concept and has worked well. We wrote it to get around Oracle licensing (only one user connected to the db, but we could handle requests from multiple web servers). Hope this helps! walt -- Murad Nayal M.D. Ph.D. Department of Biochemistry and Molecular Biophysics College of Physicians and Surgeons of Columbia University 630 West 168th Street. New York, NY 10032 Tel: 212-305-6884 Fax: 212-305-6926 - 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: sql diff for mysql
Hello If you are on Win platform then there is a tool called SQLyog at http://www.webyog.com/sqlyog which has a very powerful Database Synchronization facility. If I am not wrong then you can find a tutorial on Synchronizatin with SQLyog at http://www.phpbuilder.com Karam --- Florent Martineau [EMAIL PROTECTED] wrote: Hi, I'm searching for a mysql diff tool to update my database when changing structure and so. Does anybody outthere knowing such a tool ? Thanks - Florent - 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 __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.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
Newbie Question
How do I change Mysql default database location? Tom Knight Email: [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
Updat not Updating
Can anyone tell me why this is not updating records for this table? mysql_connect($DBHost,$DBUser,$DBPass); mysql($DBName,UPDATE items SET ItemSKU='$ItemSKU', ItemName='$ItemName', ItemDescription='$ItemDescription', PostCode='$PostCode', Category='$Category', CityID='$CityID' CTelephone='$CTelephone' Cfax='$Cfax' Cemail='$Cemail' Caddress='$Caddress' Cfax='$CTown' Cwww='$Cwww' WHERE ItemID='$ItemID'); Andrew - 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
Counting multiple tables
Hi, I'm trying to do a SELECT which then counts records in two separate tables, eg: SELECT a_id, count(b_id) FROM a LEFT JOIN b USING(a_id) merged with SELECT a_id, count(c_id) FROM a LEFT JOIN c USING(a_id) So you would end up with a_id, count(b_id), count(c_id) Showing the number of records linked in each table respectively. Is there a simple way of doing this? For what it's worth, I'm using MySQL 3.23.49 and accessing it from PHP 4. Thanks, James - 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: translation dictionary
At 10:13 -0500 3/14/03, Keith C. Ivey wrote: On 14 Mar 2003, at 0:28, felix t wrote: I would like to ask for some ideas on how to build such a dictionary given that I can not use more than one set of characters in mysql server. ( the russian words need on set of characters , the french ones another ). Presumably you're using Unicode, so that you can display both French and Russian on the same page. It might work for you to store the words as plain binary data in your tables, since the character set is relevant mostly for sorting and you won't be doing that unless you're going to allow browsing through the dictionary entries as opposed to looking up individual ones. You'd also have to take care of letter case in your application rather than in MySQL, so that you can store your values as (for example) all lowercase and then convert your search string to lowercase before putting it into your query. I haven't actually done anything with character sets yet, so I could be missing an important point. Maybe someone with more experience will chime in if I have. One important point is that binary values are not associated with a character set. :-) For example, BLOB columns don't have a charset. TEXT columns do. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: Connection String?
On the contrary, I use MyODBC and NEVER use a DSN, hate the things. Here is the connection string I use: DRIVER={MySQL ODBC 3.51 Driver};SERVER=123.456.789.100;DATABASE=mysqldatabase;UID=sampleuser;PWD=123 45; OPTION=16427 It works great, sometimes I add 2048 to the options value to use the compressed protocol(producing OPTION=18475), it is especially useful when dealing with blobs. Mike Hillyer http://www.dynamergy.com/mike -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 8:43 AM To: Tamayo, Nelson Cc: '[EMAIL PROTECTED]' Subject: Re: Connection String? Quoting Tamayo, Nelson [EMAIL PROTECTED]: I am trying to connect to MySQL without using a DSN entry, this is my connection string DRIVER={MySQL ODBC 3.51 Driver};SRVR=159.113.44.49;DB=csapps;UID=user;PWD=password The error I get is Invalid connection string attribute Any ideas? Nelson Tamayo Claims Automation (800) 237-7767 x3520 [EMAIL PROTECTED] The information contained in this transmission is legally privileged and confidential information intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copy of this transmission is strictly prohibited. If you have received this transmission in error, please contact the sender immediately. - 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 Nelson, It sounds like you are trying to connect to MySQL via ADO using a connection string. ADO connection in that manner requires an OLE-DB driver for the database platform if you wish to avoid using an ODBC DSN. I don't think that there's an OLE-DB (at least not a free one) for MySQL yet. Therefore, you will have to use a DSN. Regards, -- Barry C. Hawkins Systems Consultant All Things Computed [EMAIL PROTECTED] 404-795-9147 voice/fax - 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: Looking for a bona fide distributed database that is opensource
At 9:55 -0500 3/14/03, Bruce Feist wrote: Gelu Gogancea wrote: To anyone that *didn't see the forest because of the trees*. You already find it. Is MySQL really distributed, or just client-server? I've seen no indication of distributed capabilities; I'm new to MySQL, so maybe I just haven't read enough. But a distributed DBMS should be able to execute a single query which transparently accesses multiple database installations on multiple computers, possibly on multiple platforms. For instance, SELECT * from a, b ON a.id = b.id; where table 'a' is in a database on a Windows/2000 computer and table 'b' is on a Linux machine. The DBMS should keep track of which table is in which database on which computer, and its optimizer should be capable of figuring out an efficient way of resolving such queries. Can MySQL do that? Bruce Feist No. A given connection is a connection to a specific server. You cannot access tables managed by different servers within a single query. - Original Message - From: james [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:44 PM I am looking for a bona fide distributed database system, like Oracle or SQLServer, but open source running on Linux. - 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: FreeBSD + MySQL bottleneck
This may help - I just got this from a friend. http://jeremy.zawodny.com/blog/archives/000203.html --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: Matthias Trevarthan [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:16 AM To: [EMAIL PROTECTED] Subject: FreeBSD + MySQL bottleneck Howdy list, I run MySQL 3.23.54 with FreeBSD 4.6-RELEASE. We recently had a BBS get hammered by a lot of concentrated traffic. I currently run a 'mysql-optimize.sh' script from cron on Wednesday and Sunday that executes: ${bindir}/myisamchk -i -r --check --sort-index --analyze ${datadir}/*/*.MYI ${bindir}/isamchk -i -r --analyze --sort-index ${datadir}/*/*.ISM This works great to keep my databases lean and mean for normal server load and traffic, but this last hit was just too much. The server was bottlenecked somewhere. Problem was, I couldn't figure out where! I'm running SCSI 160 disks in a Raid config, with a dual 1GHZ PIII and 1G of SDRAM. I'd think that setup would be able to handle some pretty killer loads... Anyway, I ran 'top', and MySQL was turning about 97% processor utilitzation on one processor. It said I still had 128M of free ram left (and my MySQL tables are all under 10M). And I was only using 3% swap, which is normal because I run phpa_accelerator. Also, 'mysql show status;' showed that I only had about 25 threads open at a time. And it also said I had 82 tables open. My T1s were NOT maxed out. They weren't even half full, and besides: I accessed the BBS from our 100Mb switch, and it was still dog slow (20-30 seconds for a page load), which means it was purely a bottleneck in my machine. So, my questions are these: --- Can any experienced MySQL-FreeBSD admins out there give me some pointers for identifying bottlenecks? Specifically, I don't know how to determine if my disks were being maxed out. Could someone give me some pointers? And also, from 'show status', is the number of 'threads' directly related to the specific number of MySQL socket connections? (I have MySQL setup to allow up to 200 and I wasn't even getting over 30 from 'netstat | grep -i mysql') Any help would be appreciated! Thanks! Matthias - 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
Apparent Write caching slowing performance...
Hi, I'm having some serious performance issues that are stemming from MySQL writing data to tables. MySQL seems to cache writes, then write the block all at once. The major issue I'm having with this is that it when it does this it stops processing other requests until it's finished. I have listed my variables and .cnf file below in hopes that someone can point out what's causing this, or at least point me in the right direction. (I've been Google-ing/reading/testing/experimenting for the past 3 days to no avail). This is all running on a dedicated server running Red Hat 8, with Dual 2.5GHz Xeons, 2GB ram, 50GB SCSI hard drive Variables: +-+ | Variable_name | Value | +-+ | back_log| 50 | | basedir | /usr/local/mysql/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set | latin1 | | character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | | connect_timeout | 5 | | convert_character_set | | | datadir | /usr/local/mysql/var/ | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -()~*:| | | ft_min_word_len | 4 | | ft_max_word_len | 254 | | ft_max_word_len_for_sort| 20 | | ft_stopword_file| (built-in) | | have_bdb
LOAD DATA FILE
I have strange problem with LOAD DATA INFILE $file=base.txt; $conn=mysql_connect('localhost', 'user', 'paswd'); mysql_select_db('realestate', $conn); $query=LOAD DATA INFILE '.$file.' REPLACE INTO TABLE table_name FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'; mysql_query($query, $conn) or print(mysql_error()); mysql_close($conn); Everything seems to be OK, no error messages, but when I open the table I see only new empty row created. What's wrong? - 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: How to implement this query - can you help pls?
* Kyle Lange Try as I might, I can't seem to work out the SQL for this query. Can you help please? I'll try. I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice Something like this: SELECT P.prodID, P.prodName, PLI.itemPrice FROM categories C NATURAL JOIN products P, persons PE NATURAL JOIN intPerPriceList IPPL LEFT JOIN priceList PL ON PL.listID = IPPL.listID AND CURRENT_DATE BETWEEN PL.validFrom AND PL.validTo LEFT JOIN priceListItems PLI ON PLI.prodID = P.prodID AND PLI.listID = PL.listID WHERE C.catID = $catID AND PE.perID = $perID Some comments: NATURAL JOIN means the join is performed on any/all columns with the same name in the two joined tables. Normally in a multitable SELECT, you select FROM one table, join another on some condition, join another on some condition and so on. In this case, two separate threads of joins are made; first categories and products(=P), then persons, intPerPriceList and priceList(=PL), and finally the two are 'glued together' with the priceListItems table, which has a condition to match both P.prodID and PL.listID. URL: http://www.mysql.com/doc/en/JOIN.html HTH, -- 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
Select leaf nodes from tree
Hi, Given a simple tree structure represented in a table shown below: group_id: int parent_group_id: int description: varchar I am trying to select all rows from a table where the group_id does not appear elsewhere as a parent_group_id, hence selecting only the leaf nodes. I have wrestled with the query below but cannot get it to perform as I expected. SELECT DISTINCT A.group_id, A.group_description FROM Product_Group A, Product_Group B WHERE B.parent_group_id != A.group_id ORDER BY A.group_description ASC I would normally perform this using a sub select but obviously using mySQL I must look elsewhere. I could implement using a temp table but would prefer a single query answer. I may have got recursive selects a bit twisted so anyone with superior SQL skill suggest where I am failing? Many Thanks, Andrew. - 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: Looking for a bona fide distributed database that is open source
My experience may be limited but I don't know of any database that will allow a single query to access mutiple database servers In fact, only a few seem to be able to even access mutiple databases on the same server Frankly, I am still looking for a db that provides n way replication (this may solve some of your wishes since a local copy could be maintain on your local server) Paul DuBois ([EMAIL PROTECTED]) wrote: At 9:55 -0500 3/14/03, Bruce Feist wrote: Gelu Gogancea wrote: To anyone that *didn't see the forest because of the trees*. You already find it. Is MySQL really distributed, or just client-server? I've seen no indication of distributed capabilities; I'm new to MySQL, so maybe I just haven't read enough. But a distributed DBMS should be able to execute a single query which transparently accesses multiple database installations on multiple computers, possibly on multiple platforms. For instance, SELECT * from a, b ON a.id = b.id; where table 'a' is in a database on a Windows/2000 computer and table 'b' is on a Linux machine. The DBMS should keep track of which table is in which database on which computer, and its optimizer should be capable of figuring out an efficient way of resolving such queries. Can MySQL do that? Bruce Feist No. A given connection is a connection to a specific server. You cannot access tables managed by different servers within a single query. - Original Message - From: james [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:44 PM I am looking for a bona fide distributed database system, like Oracle or SQLServer, but open source running on Linux. - 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 -- Brian Johnson This is where my witty signature line would be if I bothered to edit this line :) - 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: Updat not Updating
At 16:18 + 3/14/03, Andrew wrote: Can anyone tell me why this is not updating records for this table? mysql_connect($DBHost,$DBUser,$DBPass); mysql($DBName,UPDATE items SET ItemSKU='$ItemSKU', ItemName='$ItemName', ItemDescription='$ItemDescription', PostCode='$PostCode', Category='$Category', CityID='$CityID' CTelephone='$CTelephone' Cfax='$Cfax' Cemail='$Cemail' Caddress='$Caddress' Cfax='$CTown' Cwww='$Cwww' WHERE ItemID='$ItemID'); Andrew We cannot, but MySQL and PHP can. 1) Check the return values of the function calls to make sure they work. As written, you have no way of knowing. 2) When an error occurs, print the value of mysql_error() to see what the error is. -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
Re: Updat not Updating
Hello Andrew, Friday, March 14, 2003, 6:18:15 PM, you wrote: A Can anyone tell me why this is not updating records for this table? A mysql_connect($DBHost,$DBUser,$DBPass); A mysql($DBName,UPDATE items A SET ItemSKU='$ItemSKU', A ItemName='$ItemName', A ItemDescription='$ItemDescription', A PostCode='$PostCode', A Category='$Category', A CityID='$CityID' A CTelephone='$CTelephone' A Cfax='$Cfax' A Cemail='$Cemail' A Caddress='$Caddress' A Cfax='$CTown' A Cwww='$Cwww' A WHERE ItemID='$ItemID'); A Andrew A - A Before posting, please check: Ahttp://www.mysql.com/manual.php (the manual) Ahttp://lists.mysql.com/ (the list archive) A To request this thread, e-mail [EMAIL PROTECTED] A To unsubscribe, e-mail [EMAIL PROTECTED] A Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php There are few simple steps, that everyone should do when using MySQL within PHP. 1. $connection = mysql_connect('host', 'user', 'pass') or die(Error with connect: .mysql_error().); 2. mysql_select_db('db_name', $connection) or die(Error with select: .mysql_error().); 3. $query = ; 4. $result = mysql_query($query, $connection) or die(Error with query: .mysql_error().); This steps will provide you information if anything goes wrong and most import where it goes wrong. From what you wrote, you didn't placed enough commas in the query. Follow the steps described above and paste the error log. www.php.net and www.mysql.com are your friends! -- best wishes, Strahil Minev a.k.a. DLHelper, BuFu TeaM mailto:[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: sql diff for mysql
At 8:02 -0800 3/14/03, Karam Chand wrote: Hello If you are on Win platform then there is a tool called SQLyog at http://www.webyog.com/sqlyog which has a very powerful Database Synchronization facility. If I am not wrong then you can find a tutorial on Synchronizatin with SQLyog at http://www.phpbuilder.com Karam There is an article on the topic here as well: http://www.databasejournal.com/features/mysql/article.php/1584401 --- Florent Martineau [EMAIL PROTECTED] wrote: Hi, I'm searching for a mysql diff tool to update my database when changing structure and so. Does anybody outthere knowing such a tool ? Thanks - Florent -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
Re: FreeBSD + MySQL bottleneck
On Fri, Mar 14, 2003 at 12:03:14PM -0800, Joe Stump wrote: This may help - I just got this from a friend. http://jeremy.zawodny.com/blog/archives/000203.html Ugh. I *really* need to update that. It has become a popular reference. But I have some new information to add. The short version. If you use LinuxThreads and MySQL 4.0.x (where x is 4 or so), MySQL/FreeBSD is readlly damned good. But using LinuxThreads is a key piece there. FreeBSD's threading still sucks and can hamper MySQL. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 39 days, processed 1,287,285,706 queries (379/sec. avg) - 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: Updat not Updating
yes you are right an I am very stupid for not doing this: It is very important for debugging as I now have this error :):) Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in c:\program files\apache group\apache\htdocs\punterspower\admin\updatecompanyresponse.php on line 23 Error with query: You have an error in your SQL syntax near 'WHERE ItemID='01296 483402'' at line 14 I'm not really happy because I dont know why!! Andrew -Original Message- From: nobody [mailto:[EMAIL PROTECTED] Sent: 14 March 2003 17:31 To: Andrew Cc: [EMAIL PROTECTED] Subject: Re: Updat not Updating Hello Andrew, Friday, March 14, 2003, 6:18:15 PM, you wrote: A Can anyone tell me why this is not updating records for this table? A mysql_connect($DBHost,$DBUser,$DBPass); A mysql($DBName,UPDATE items A SET ItemSKU='$ItemSKU', A ItemName='$ItemName', A ItemDescription='$ItemDescription', A PostCode='$PostCode', A Category='$Category', A CityID='$CityID' A CTelephone='$CTelephone' A Cfax='$Cfax' A Cemail='$Cemail' A Caddress='$Caddress' A Cfax='$CTown' A Cwww='$Cwww' A WHERE ItemID='$ItemID'); A Andrew A - A Before posting, please check: Ahttp://www.mysql.com/manual.php (the manual) Ahttp://lists.mysql.com/ (the list archive) A To request this thread, e-mail [EMAIL PROTECTED] A To unsubscribe, e-mail [EMAIL PROTECTED] A Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php There are few simple steps, that everyone should do when using MySQL within PHP. 1. $connection = mysql_connect('host', 'user', 'pass') or die(Error with connect: .mysql_error().); 2. mysql_select_db('db_name', $connection) or die(Error with select: .mysql_error().); 3. $query = ; 4. $result = mysql_query($query, $connection) or die(Error with query: .mysql_error().); This steps will provide you information if anything goes wrong and most import where it goes wrong. From what you wrote, you didn't placed enough commas in the query. Follow the steps described above and paste the error log. www.php.net and www.mysql.com are your friends! -- best wishes, Strahil Minev a.k.a. DLHelper, BuFu TeaM mailto:[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.461 / Virus Database: 260 - Release Date: 10/03/2003 - 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[2]: Updat not Updating
Hello Andrew, Friday, March 14, 2003, 7:41:56 PM, you wrote: A yes you are right an I am very stupid for not doing this: It is very important A for debugging as I now have this error :):) A Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in A c:\program files\apache A group\apache\htdocs\punterspower\admin\updatecompanyresponse.php on line 23 A Error with query: You have an error in your SQL syntax near 'WHERE ItemID='01296 A 483402'' at line 14 A I'm not really happy because I dont know why!! A Andrew :: cut :: A mysql_connect($DBHost,$DBUser,$DBPass); A mysql($DBName,UPDATE items A SET ItemSKU='$ItemSKU', A ItemName='$ItemName', A ItemDescription='$ItemDescription', A PostCode='$PostCode', A Category='$Category', A CityID='$CityID' A CTelephone='$CTelephone' A Cfax='$Cfax' A Cemail='$Cemail' A Caddress='$Caddress' A Cfax='$CTown' A Cwww='$Cwww' A WHERE ItemID='$ItemID'); A Andrew A - A Before posting, please check: Ahttp://www.mysql.com/manual.php (the manual) Ahttp://lists.mysql.com/ (the list archive) A To request this thread, e-mail [EMAIL PROTECTED] A To unsubscribe, e-mail [EMAIL PROTECTED] A Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php There are few simple steps, that everyone should do when using MySQL within PHP. 1. $connection = mysql_connect('host', 'user', 'pass') or die(Error with connect: .mysql_error().); 2. mysql_select_db('db_name', $connection) or die(Error with select: .mysql_error().); 3. $query = ; 4. $result = mysql_query($query, $connection) or die(Error with query: .mysql_error().); This steps will provide you information if anything goes wrong and most import where it goes wrong. From what you wrote, you didn't placed enough commas in the query. Follow the steps described above and paste the error log. www.php.net and www.mysql.com are your friends! :: cut :: Okay, I'm not very sure about this, but try ItemID=$ItemID. If it's not this, then look up your code above. Check if all quotes are closed properly. If it's still not working then, please, paste your code as is, 1:1 :) -- best wishes, Strahil Minev a.k.a. DLHelper, BuFu TeaM mailto:[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: How to implement this query - can you help pls?
-Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 16:48 To: [EMAIL PROTECTED] Cc: Kyle Lange Subject: Re: How to implement this query - can you help pls? * Kyle Lange Try as I might, I can't seem to work out the SQL for this query. Can you help please? I'll try. I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice Something like this: SELECT P.prodID, P.prodName, PLI.itemPrice FROM categories C NATURAL JOIN products P, persons PE NATURAL JOIN intPerPriceList IPPL LEFT JOIN priceList PL ON PL.listID = IPPL.listID AND CURRENT_DATE BETWEEN PL.validFrom AND PL.validTo LEFT JOIN priceListItems PLI ON PLI.prodID = P.prodID AND PLI.listID = PL.listID WHERE C.catID = $catID AND PE.perID = $perID Some comments: NATURAL JOIN means the join is performed on any/all columns with the same name in the two joined tables. Normally in a multitable SELECT, you select FROM one table, join another on some condition, join another on some condition and so on. In this case, two separate threads of joins are made; first categories and products(=P), then persons, intPerPriceList and priceList(=PL), and finally the two are 'glued together' with the priceListItems table, which has a condition to match both P.prodID and PL.listID. URL: http://www.mysql.com/doc/en/JOIN.html HTH, -- 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: LOAD DATA FILE
At 19:58 +0300 3/14/03, [EMAIL PROTECTED] wrote: I have strange problem with LOAD DATA INFILE $file=base.txt; $conn=mysql_connect('localhost', 'user', 'paswd'); mysql_select_db('realestate', $conn); $query=LOAD DATA INFILE '.$file.' REPLACE INTO TABLE table_name FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'; mysql_query($query, $conn) or print(mysql_error()); mysql_close($conn); Everything seems to be OK, no error messages, but when I open the table I see only new empty row created. What's wrong? My guess would be that because \ in strings is special to both MySQL *and* PHP, you'll need to specify \t and \r\n as \\t and \\r\\n. -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
select distinct doesn't coalesce NULL rows
Description: select distinct FOO from BAR reports multiple NULL rows How-To-Repeat: Unknown. It's a large table (1 entries or so). The problem didn't show with a simple test table. The table: -- MySQL dump 10.0 -- -- Host: localhostDatabase: pop - -- Server version 4.1.0-alpha-debug-log -- -- Table structure for table 'person' -- DROP TABLE IF EXISTS person; CREATE TABLE person ( descr int(11) default NULL, id int(11) NOT NULL default '0', kunde int(11) NOT NULL default '0', nameid int(11) default NULL, abtid int(11) default NULL, mailid int(11) default NULL, foneid int(11) default NULL, faxid int(11) default NULL, pagerid int(11) default NULL, isdnid int(11) default NULL, dest varchar(4) default NULL, adrid int(11) default NULL, ausweis int(11) default NULL, zusatz int(11) default NULL, suche int(11) default NULL, username int(11) default NULL, passwort int(11) default NULL, uid int(11) NOT NULL default '0', pwsubdir int(11) default NULL, pwuse bigint(20) NOT NULL default '0', udomain int(11) default NULL, uip int(11) default NULL, proto smallint(6) default NULL, maxconn tinyint(4) default NULL, ulocip int(11) default NULL, uremip int(11) default NULL, prefcall smallint(6) default NULL, tarif int(11) default NULL, satz tinyint(4) default NULL, mperson int(11) default NULL, gebtag smallint(6) default NULL, gebjahr smallint(6) default NULL, funktion int(11) default NULL, ustid varchar(10) default NULL, timestamp timestamp NOT NULL, PRIMARY KEY (id), KEY suche (suche), KEY username (username), KEY uid (uid), KEY mailid (mailid), KEY mperson (mperson), KEY timestamp (timestamp) ) TYPE=InnoDB CHARSET=latin1; The statement: (explain select distinct mperson from person): id select_type table typepossible_keys key key_len ref rows Extra 1 SIMPLE person index NULLmperson 5 NULL8125Using index Fix: None known. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: select distinct reports multiple NULL rows Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Up-to-date Bitkeeper distribution) C compiler:gcc.real (GCC) 3.2.3 20030228 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030308 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - 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 on single server
At 10:49 +0100 3/14/03, Brandl, Thomas wrote: Hey everybody, I want to replicate several tables from one database to all other databases on the same machine, so to say a table-level-replication Simply said, imagine this scenario: A MySQL server with about 10 databases: A and 1 to 9. Database A contains a 'special' table that is supposed to be replicated to all other databases on the same machine, so it is kinda like the master database. The table has the same name on every database. Is such a thing possible and if yes, how? Thanks very much for your help! Thomas I don't understand why you'd bother. Put it in one database and grant everybody permission to access it. Wouldn't that accomplish the same thing, without all the headache of trying to copy it everywhere? -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
Re: Newbie Question
At 8:04 -0800 3/14/03, Thomas Knight wrote: How do I change Mysql default database location? Please explain what you mean by that? Tom Knight Email: [EMAIL PROTECTED] -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
RE: How to implement this query - Can you help pls?
Roger, Thanks for the reply. That certainly doesn't give any errors but 'NATURAL JOIN' also unfortunately doesn't work. If I just take the first section; SELECT P.prodID, P.prodName FROM categories C NATURAL JOIN products P; I get an empty set. However; SELECT P.prodID, P.prodName FROM categories C LEFT JOIN products P ON P.catID = C.catID; I get what I would expect (i.e. a full list of prodNames). It has however given me more or less an idea of what I need to do. If it makes any difference to the NATURAL JOIN, I'm using 3.23.51-nt. Cheers -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 16:48 To: [EMAIL PROTECTED] Cc: Kyle Lange Subject: Re: How to implement this query - can you help pls? * Kyle Lange Try as I might, I can't seem to work out the SQL for this query. Can you help please? I'll try. I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice Something like this: SELECT P.prodID, P.prodName, PLI.itemPrice FROM categories C NATURAL JOIN products P, persons PE NATURAL JOIN intPerPriceList IPPL LEFT JOIN priceList PL ON PL.listID = IPPL.listID AND CURRENT_DATE BETWEEN PL.validFrom AND PL.validTo LEFT JOIN priceListItems PLI ON PLI.prodID = P.prodID AND PLI.listID = PL.listID WHERE C.catID = $catID AND PE.perID = $perID Some comments: NATURAL JOIN means the join is performed on any/all columns with the same name in the two joined tables. Normally in a multitable SELECT, you select FROM one table, join another on some condition, join another on some condition and so on. In this case, two separate threads of joins are made; first categories and products(=P), then persons, intPerPriceList and priceList(=PL), and finally the two are 'glued together' with the priceListItems table, which has a condition to match both P.prodID and PL.listID. URL: http://www.mysql.com/doc/en/JOIN.html HTH, -- 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
Constraint violations don't show which constraint fails
Description: A constraint violation doesn't show which constraint was violated, which is bad for fixing bugs. How-To-Repeat: create table a(b int, index(b)) type=innodb; create table c(d int, index(d)) type=innodb; alter table c add constraint foo foreign key(`d`) references `a` (`b`); insert into c values(123); = ERROR 1216: Cannot add or update a child row: a foreign key constraint fails Fix: Tell me which constraint fails, please. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: icense Synopsis: Constraint violations don't show which constraint fails Severity: serious Priority: low Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (current Bitkeeper source) C compiler:gcc.real (GCC) 3.2.3 20030308 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030308 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - 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: Updat not Updating
I have an idea why this is but not 100% there is a diffeence in order between query and Table the ItemID comes after the Ctelephone column in the DB. So presumably this is the problem, but I realy haven't the foggiest why. mysql($DBName,UPDATE items SET ItemSKU='$ItemSKU', ItemName='$ItemName', ItemDescription='$ItemDescription', PostCode='$PostCode', Category='$Category', CityID='$CityID', CTelephone='$CTelephone', Cfax='$Cfax', Cemail='$Cemail', Caddress='$Caddress', Cfax='$CTown', Cwww='$Cwww', WHERE ItemID='$ItemID'); Andrew -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: 14 March 2003 18:27 To: [EMAIL PROTECTED]; cTpaXo Cc: [EMAIL PROTECTED] Subject: RE: Updat not Updating - 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: Updat not Updating
At 18:49 + 3/14/03, Andrew wrote: I have an idea why this is but not 100% there is a diffeence in order between query and Table the ItemID comes after the Ctelephone column in the DB. So presumably this is the problem, but I realy haven't the foggiest why. mysql($DBName,UPDATE items SET ItemSKU='$ItemSKU', ItemName='$ItemName', ItemDescription='$ItemDescription', PostCode='$PostCode', Category='$Category', CityID='$CityID', CTelephone='$CTelephone', Cfax='$Cfax', Cemail='$Cemail', Caddress='$Caddress', Cfax='$CTown', Cwww='$Cwww', -- no comma here! WHERE ItemID='$ItemID'); Andrew -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: 14 March 2003 18:27 To: [EMAIL PROTECTED]; cTpaXo Cc: [EMAIL PROTECTED] Subject: RE: Updat not Updating See note embedded in query above. To spot these things more easily, you might consider assigning the query to a variable. Then you can pass the variable to the mysql() function, and also print it later if an error occurs. -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
Remote Administration
Hi, How would I go about setting up remote administration of MySQL. MySQL is running on Windows 2000 and I will be connecting from Windows XP. I saw something in the MySQL docs about using an SSH client like Secure CRT, but it loses me at the linking OBDC. Any help on this or another similar way to do remote administration is appreciated.. - 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: Connection String?
Mike, I'm curious, is the value specified for the DRIVER argument the name of the ODBC driver? If so, is this a general trick that can be applied to ADO for use with other ODBC drivers, i.e. PostgreSQL? Thanks, -- Barry C. Hawkins Systems Consultant All Things Computed [EMAIL PROTECTED] 404-795-9147 voice/fax Quoting Mike Hillyer [EMAIL PROTECTED]: On the contrary, I use MyODBC and NEVER use a DSN, hate the things. Here is the connection string I use: DRIVER={MySQL ODBC 3.51 Driver};SERVER=123.456.789.100;DATABASE=mysqldatabase;UID=sampleuser;PWD=123 45; OPTION=16427 It works great, sometimes I add 2048 to the options value to use the compressed protocol(producing OPTION=18475), it is especially useful when dealing with blobs. Mike Hillyer http://www.dynamergy.com/mike -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 8:43 AM To: Tamayo, Nelson Cc: '[EMAIL PROTECTED]' Subject: Re: Connection String? Quoting Tamayo, Nelson [EMAIL PROTECTED]: I am trying to connect to MySQL without using a DSN entry, this is my connection string DRIVER={MySQL ODBC 3.51 Driver};SRVR=159.113.44.49;DB=csapps;UID=user;PWD=password The error I get is Invalid connection string attribute Any ideas? Nelson Tamayo Claims Automation (800) 237-7767 x3520 [EMAIL PROTECTED] The information contained in this transmission is legally privileged and confidential information intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copy of this transmission is strictly prohibited. If you have received this transmission in error, please contact the sender immediately. - 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 Nelson, It sounds like you are trying to connect to MySQL via ADO using a connection string. ADO connection in that manner requires an OLE-DB driver for the database platform if you wish to avoid using an ODBC DSN. I don't think that there's an OLE-DB (at least not a free one) for MySQL yet. Therefore, you will have to use a DSN. Regards, -- Barry C. Hawkins Systems Consultant All Things Computed [EMAIL PROTECTED] 404-795-9147 voice/fax - 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: Newbie Question
On 3/14/03 9:31 AM, Paul DuBois [EMAIL PROTECTED] wrote: At 8:04 -0800 3/14/03, Thomas Knight wrote: How do I change Mysql default database location? Please explain what you mean by that? Maybe you need to look at the commandline options or the my.cnf files. You can specify the data directory (where the database files are located) both ways. The mysql manual is your friend thus check out things like : safe_mysqld --datadir=path http://www.mysql.com/doc/en/safe_mysqld.html mysqld --datadir=path http://www.mysql.com/doc/en/Command-line_options.html my.cnf http://www.mysql.com/doc/en/Option_files.html Cheers/h - 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: Updat not Updating
At 17:41 + 3/14/03, Andrew wrote: yes you are right an I am very stupid for not doing this: It is very important for debugging as I now have this error :):) Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in c:\program files\apache group\apache\htdocs\punterspower\admin\updatecompanyresponse.php on line 23 Error with query: You have an error in your SQL syntax near 'WHERE ItemID='01296 483402'' at line 14 I'm not really happy because I dont know why!! You didn't read Strahil's hint about missing commas below? Andrew -Original Message- From: nobody [mailto:[EMAIL PROTECTED] Sent: 14 March 2003 17:31 To: Andrew Cc: [EMAIL PROTECTED] Subject: Re: Updat not Updating Hello Andrew, Friday, March 14, 2003, 6:18:15 PM, you wrote: A Can anyone tell me why this is not updating records for this table? A mysql_connect($DBHost,$DBUser,$DBPass); A mysql($DBName,UPDATE items A SET ItemSKU='$ItemSKU', A ItemName='$ItemName', A ItemDescription='$ItemDescription', A PostCode='$PostCode', A Category='$Category', A CityID='$CityID' A CTelephone='$CTelephone' A Cfax='$Cfax' A Cemail='$Cemail' A Caddress='$Caddress' A Cfax='$CTown' A Cwww='$Cwww' A WHERE ItemID='$ItemID'); A Andrew A - A Before posting, please check: Ahttp://www.mysql.com/manual.php (the manual) Ahttp://lists.mysql.com/ (the list archive) A To request this thread, e-mail [EMAIL PROTECTED] A To unsubscribe, e-mail [EMAIL PROTECTED] A Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php There are few simple steps, that everyone should do when using MySQL within PHP. 1. $connection = mysql_connect('host', 'user', 'pass') or die(Error with connect: .mysql_error().); 2. mysql_select_db('db_name', $connection) or die(Error with select: .mysql_error().); 3. $query = ; 4. $result = mysql_query($query, $connection) or die(Error with query: .mysql_error().); This steps will provide you information if anything goes wrong and most import where it goes wrong. From what you wrote, you didn't placed enough commas in the query. Follow the steps described above and paste the error log. www.php.net and www.mysql.com are your friends! -- best wishes, Strahil Minev a.k.a. DLHelper, BuFu TeaM mailto:[EMAIL PROTECTED] -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
Re: Updat not Updating
If this is really exactly what your code looks like, you've got an extraneous comma after the Cwww line. But when you're tracking down a bug like this, you may find it helpful to output the exact query that is being executed, because often the problem will lie in the variable you're inserting. Brian McCain - Original Message - From: Andrew [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED]; cTpaXo [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 10:49 AM Subject: RE: Updat not Updating I have an idea why this is but not 100% there is a diffeence in order between query and Table the ItemID comes after the Ctelephone column in the DB. So presumably this is the problem, but I realy haven't the foggiest why. mysql($DBName,UPDATE items SET ItemSKU='$ItemSKU', ItemName='$ItemName', ItemDescription='$ItemDescription', PostCode='$PostCode', Category='$Category', CityID='$CityID', CTelephone='$CTelephone', Cfax='$Cfax', Cemail='$Cemail', Caddress='$Caddress', Cfax='$CTown', Cwww='$Cwww', WHERE ItemID='$ItemID'); Andrew -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: 14 March 2003 18:27 To: [EMAIL PROTECTED]; cTpaXo Cc: [EMAIL PROTECTED] Subject: RE: Updat not Updating - 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: FreeBSD + MySQL bottleneck
Matthias Trevarthan wrote: Howdy list, I run MySQL 3.23.54 with FreeBSD 4.6-RELEASE. We recently had a BBS get hammered by a lot of concentrated traffic. I currently run a 'mysql-optimize.sh' script from cron on Wednesday and Sunday that executes: ${bindir}/myisamchk -i -r --check --sort-index --analyze ${datadir}/*/*.MYI ${bindir}/isamchk -i -r --analyze --sort-index ${datadir}/*/*.ISM This works great to keep my databases lean and mean for normal server load and traffic, but this last hit was just too much. The server was bottlenecked somewhere. Problem was, I couldn't figure out where! I'm running SCSI 160 disks in a Raid config, with a dual 1GHZ PIII and 1G of SDRAM. I'd think that setup would be able to handle some pretty killer loads... Anyway, I ran 'top', and MySQL was turning about 97% processor utilitzation on one processor. It said I still had 128M of free ram left (and my MySQL tables are all under 10M). And I was only using 3% swap, which is normal because I run phpa_accelerator. Also, 'mysql show status;' showed that I only had about 25 threads open at a time. And it also said I had 82 tables open. My T1s were NOT maxed out. They weren't even half full, and besides: I accessed the BBS from our 100Mb switch, and it was still dog slow (20-30 seconds for a page load), which means it was purely a bottleneck in my machine. So, my questions are these: --- Can any experienced MySQL-FreeBSD admins out there give me some pointers for identifying bottlenecks? Specifically, I don't know how to determine if my disks were being maxed out. Could someone give me some pointers? And also, from 'show status', is the number of 'threads' directly related to the specific number of MySQL socket connections? (I have MySQL setup to allow up to 200 and I wasn't even getting over 30 from 'netstat | grep -i mysql') Any help would be appreciated! Thanks! Matthias - 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 Matthias, I'm not sure on BSD (never used it) but with Linux if your processor load is high and load avg is low, you are not suffering from an I/O bottleneck. If your load avg was high and cpu was low, then you have lots of I/O. It sounds like you have a query or two that needs to be optimized. I'd run show full processlist next time it bogs down and test all running queries on another system and see if you can tweak them or move the logic out of the database into the actual code that's making the query. Hope this helps! walt - 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: Remote Administration
There are a few tools you can use to remotely administer MySQL graphically, if that's what you're looking for. You might be interested in MySQL Control Center http://www.mysql.com/products/mysqlcc/index.html although I'm not entirely certain about it's support for secure data transmission. Brian McCain - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, March 14, 2003 10:49 AM Subject: Remote Administration Hi, How would I go about setting up remote administration of MySQL. MySQL is running on Windows 2000 and I will be connecting from Windows XP. I saw something in the MySQL docs about using an SSH client like Secure CRT, but it loses me at the linking OBDC. Any help on this or another similar way to do remote administration is appreciated.. - 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 distinct doesn't coalesce NULL rows
Smurf wrote: Description: select distinct FOO from BAR reports multiple NULL rows How-To-Repeat: Unknown. It's a large table (1 entries or so). The problem didn't show with a simple test table. The table: -- MySQL dump 10.0 -- -- Host: localhostDatabase: pop - -- Server version 4.1.0-alpha-debug-log -- -- Table structure for table 'person' -- DROP TABLE IF EXISTS person; CREATE TABLE person ( descr int(11) default NULL, id int(11) NOT NULL default '0', kunde int(11) NOT NULL default '0', nameid int(11) default NULL, abtid int(11) default NULL, mailid int(11) default NULL, foneid int(11) default NULL, faxid int(11) default NULL, pagerid int(11) default NULL, isdnid int(11) default NULL, dest varchar(4) default NULL, adrid int(11) default NULL, ausweis int(11) default NULL, zusatz int(11) default NULL, suche int(11) default NULL, username int(11) default NULL, passwort int(11) default NULL, uid int(11) NOT NULL default '0', pwsubdir int(11) default NULL, pwuse bigint(20) NOT NULL default '0', udomain int(11) default NULL, uip int(11) default NULL, proto smallint(6) default NULL, maxconn tinyint(4) default NULL, ulocip int(11) default NULL, uremip int(11) default NULL, prefcall smallint(6) default NULL, tarif int(11) default NULL, satz tinyint(4) default NULL, mperson int(11) default NULL, gebtag smallint(6) default NULL, gebjahr smallint(6) default NULL, funktion int(11) default NULL, ustid varchar(10) default NULL, timestamp timestamp NOT NULL, PRIMARY KEY (id), KEY suche (suche), KEY username (username), KEY uid (uid), KEY mailid (mailid), KEY mperson (mperson), KEY timestamp (timestamp) ) TYPE=InnoDB CHARSET=latin1; The statement: (explain select distinct mperson from person): id select_type table typepossible_keys key key_len ref rows Extra 1 SIMPLE person index NULLmperson 5 NULL8125Using index Fix: None known. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: select distinct reports multiple NULL rows Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Up-to-date Bitkeeper distribution) C compiler:gcc.real (GCC) 3.2.3 20030228 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030308 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - 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 Smurf, It should return a row for each id that you have since id is the key and
Finding an ID between two dates
Hey folks! I am stumpedbeen reading the online manual, google groups, to no avail. I have a very simple table: time_period_id int(11) NOT NULL auto_increment, time_period_start date NOT NULL default '-00-00', time_period_end date NOT NULL default '-00-00', PRIMARY KEY (time_period_id) I am trying to get the ID that relates to a date falling between the range of time_period_start(2003-03-01) and time_period_end(2003-03-15): $sql = SELECT time_period_id FROM time_periods WHERE 2003-03-14 BETWEEN time_period_start AND time_period_end; I should get one result form the query, the id, but I get no results. Advice? Helpful hints always welcome.. Many thanks! Cory -- Cory Hicks Texas Research International, Inc. [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: Finding a record BETWEEN two dates
Duh! I need '' around my datesorry for the traffic. sql, query Duh! -- Cory Hicks Texas Research International, Inc. [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
please rename dbug.h
Hi, I've hit a problem on AIX 4.3 with mysql client include files. My app needs to include xcoff.h which includes dbug.h. The problem is that I also need to access mysql. It turns out that the mysql include directory also has a dbug.h which get pulled in instead of the systems file. The fix is simple, rename the mysql dbug.h to my_dbug.h and change the include in my_global.h to #include my_dbug.h instead. Rather than my having to fix this with every new mysql release I download, could you please consider making this change in the master codelines? (ideally it would be nice if you prefixed all your header files with my_to avoid future clashes with system header files) Thanks, muchly. Jo 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
Re: Finding an ID between two dates
Quotes missing around dates. WHERE 2003-03-14 ... Ignatius - Original Message - From: Cory Hicks [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:05 PM Subject: Finding an ID between two dates Hey folks! I am stumpedbeen reading the online manual, google groups, to no avail. I have a very simple table: time_period_id int(11) NOT NULL auto_increment, time_period_start date NOT NULL default '-00-00', time_period_end date NOT NULL default '-00-00', PRIMARY KEY (time_period_id) I am trying to get the ID that relates to a date falling between the range of time_period_start(2003-03-01) and time_period_end(2003-03-15): $sql = SELECT time_period_id FROM time_periods WHERE 2003-03-14 BETWEEN time_period_start AND time_period_end; I should get one result form the query, the id, but I get no results. Advice? Helpful hints always welcome.. Many thanks! Cory -- Cory Hicks Texas Research International, Inc. [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 - 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: Looking for a bona fide distributed database that is open source
Hi, IMHO: The distributed database is a concept which is can not be assign to the SQL engine standards(AFAIK).Stored procedures,triggers are described in the SQL 92 or SQL 99 standards. It's quite right, some SQL DataBase engines(like Oracle,DB2,msSQL) have implemented facilities for this. Distributed database can be achieved,like example, using DISTRIBUTED OBJECTS(let's say CORBA).In my understanding that means that MySQL already can be used like distributed database. Finally,What i wish to say is that distributed databases depend on us to be achieved and not by the RDBMS. Paul have right and i read very carefully him message:He said (very explicit and without any doubt)that MySQL can not manage multiple tables which are hosted on different servers with one single query.For me this is not a reason to not use MySQL for distributed database system. If i remember well, on the MySQL website is a link to a german company which has develop a modules which treat multiple MySQL database(hosted on different machine of course) to a logical one. Best Regards, Gelu P.S.This is my opinion...if you consider that i'm wrong. __ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Bruce Feist [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Friday, March 14, 2003 5:33 PM Subject: Re: Looking for a bona fide distributed database that is open source No, it is not distributed. Bruce Feist wrote: Gelu Gogancea wrote: To anyone that *didn't see the forest because of the trees*. You already find it. Is MySQL really distributed, or just client-server? I've seen no indication of distributed capabilities; I'm new to MySQL, so maybe I just haven't read enough. But a distributed DBMS should be able to execute a single query which transparently accesses multiple database installations on multiple computers, possibly on multiple platforms. For instance, SELECT * from a, b ON a.id = b.id; where table 'a' is in a database on a Windows/2000 computer and table 'b' is on a Linux machine. The DBMS should keep track of which table is in which database on which computer, and its optimizer should be capable of figuring out an efficient way of resolving such queries. Can MySQL do that? Bruce Feist - 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: Finding an ID between two dates
Cory, time_period_id int(11) NOT NULL auto_increment, time_period_start date NOT NULL default '-00-00', time_period_end date NOT NULL default '-00-00', PRIMARY KEY (time_period_id) I am trying to get the ID that relates to a date falling between the range of time_period_start(2003-03-01) and time_period_end(2003-03-15): $sql = SELECT time_period_id FROM time_periods WHERE 2003-03-14 BETWEEN time_period_start AND time_period_end; SELECT time_period_id FROM time_periods WHERE time_period_start = '2003-03-01' AND time_period_end = '2003-03-15' ; should do the job. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - 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
Paul, do you address this in any of your books?
I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel - 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
forcing mysql client to use TCP socket?
Hello, how can I force mysql interactive client (actually the client library as well) to use a TCP port on the local host if I do: mysql -h localhost -u user -p --port=2000 I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' what might be a problem is: it seems, for reasons I don't entirely understand possibly related to the network configuration on this node, I need to use the name 'localhost' rather than the actual name of the node! thanks in advance for the help Murad - 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: Looking for a bona fide distributed database that is open source
Hi, - Original Message - From: Brian Johnson [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:14 PM Subject: Re: Looking for a bona fide distributed database that is open source My experience may be limited but I don't know of any database that will allow a single query to access mutiple database servers Yes, it is.But when you must save some money, you are pushed to find solutions with what it is on the market and which is optimal like price/performance. Some friends of mine was hasty to use like database support for their application,Oracle9i...and was very disappointed. Regards, Gelu In fact, only a few seem to be able to even access mutiple databases on the same server Frankly, I am still looking for a db that provides n way replication (this may solve some of your wishes since a local copy could be maintain on your local server) Paul DuBois ([EMAIL PROTECTED]) wrote: At 9:55 -0500 3/14/03, Bruce Feist wrote: Gelu Gogancea wrote: To anyone that *didn't see the forest because of the trees*. You already find it. Is MySQL really distributed, or just client-server? I've seen no indication of distributed capabilities; I'm new to MySQL, so maybe I just haven't read enough. But a distributed DBMS should be able to execute a single query which transparently accesses multiple database installations on multiple computers, possibly on multiple platforms. For instance, SELECT * from a, b ON a.id = b.id; where table 'a' is in a database on a Windows/2000 computer and table 'b' is on a Linux machine. The DBMS should keep track of which table is in which database on which computer, and its optimizer should be capable of figuring out an efficient way of resolving such queries. Can MySQL do that? Bruce Feist No. A given connection is a connection to a specific server. You cannot access tables managed by different servers within a single query. - Original Message - From: james [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:44 PM I am looking for a bona fide distributed database system, like Oracle or SQLServer, but open source running on Linux. - 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 -- Brian Johnson This is where my witty signature line would be if I bothered to edit this line :) - 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
mySQL activity logging on a dual prozessor maschine
Hi list, few days ago, I have installed a 2nd CPU in my ProLiant380. I recompiled a new kernel (2.4.17) for Debian 3.0. On the machine there is mySQL 3.23.52-2 installed (deb). Every thing works fine, just the logging of mySQL looks a little funny to me, especially the time: 030314 19:31:189745 Query SELECT de FROM b... 9745 Query SELECT gid FROM ... 030314 19:31:209745 Query SELECT gid FROM ... 030314 19:31:189745 Query SELECT gid FROM ... 9745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 9745 Query SELECT de FROM b... 9745 Query SELECT gid FROM ... 030314 19:31:209745 Query SELECT gid FROM ... The websites still grow up without errors and there is also nothing in the errorlogs. Did someone of you ever had this interesting thing? I could not find anything about that with Google or in the mailarchive here at mySQL. Thanks for your help, 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
bug?? in MySQL Control Center 0.8.10-beta
Hello, MySQL database connecting to: 3.22.32 when I select * from a table (with no primary key), the results are displayed. I attempt to use the GRID to update a field of a record. When I try to change it, I get the following error: ERROR 1064: You have an error in your SQL syntax near 'LIMIT 1' at line 4 here is the sql from the History tab: UPDATE preferred_retailer SET prr_inca_code='N-1001083' WHERE prr_user_regid=10339 AND prr_offer_name='ADDV2002' AND prr_user_key IS NULL AND prr_sequence=3 AND prr_inca_code=' N-1001083' LIMIT 1 I assume that the mysql server version I'm using doesn't recognize the sql syntax with LIMIT 1 at the end...can MySQL Control Center be modified so that it knows if a server version doesn't support LIMIT, then the sql generated doesn't contain LIMIT? or does this have to do with my table not having a primary key? thanks... Kevin * Kevin Weslowski Application Developer MBD (McNair Business Development Inc.) Direct: 789-0019 Fax: 789-7630 Email: [EMAIL PROTECTED] www.mcnairbd.com A head for business. * This email and any associated attachments is intended only for the use of the addresses named. The information herein is private and confidential and may contain legally privileged information. If you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this email and associated attachments by any means is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender or MBD at 306 789 5258 and permanently delete the original email and any copy, either electronic or printout. We honour similar requests relating to the privacy of email. - 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: FreeBSD + MySQL bottleneck
On Friday 14 March 2003 12:34, Jeremy Zawodny wrote: On Fri, Mar 14, 2003 at 12:03:14PM -0800, Joe Stump wrote: This may help - I just got this from a friend. http://jeremy.zawodny.com/blog/archives/000203.html Ugh. I *really* need to update that. It has become a popular reference. But I have some new information to add. What about the DNS issue. Is that still a problem on FreeBSD 4? Thanks! The short version. If you use LinuxThreads and MySQL 4.0.x (where x is 4 or so), MySQL/FreeBSD is readlly damned good. But using LinuxThreads is a key piece there. FreeBSD's threading still sucks and can hamper MySQL. Jeremy - 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: Paul, do you address this in any of your books?
Hi, You can try to use IF(),CASE() function in the SQL statement. Eg. select USERID,IF(DAY='2003-01-02',COUNT(*),0) as 2003-01-02,IF(DAY='2003-01-03',COUNT(*),0) as 2003-01-03...how_many_days_you_wishfrom YOUR_TABLES where YOUR_CONDITIONS GROUP BY DAY; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: DANIEL GADDIS [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 10:40 PM Subject: Paul, do you address this in any of your books? I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel - 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: forcing mysql client to use TCP socket?
mysql -h 127.0.0.1 -u user -p --port=2000 Murad Nayal wrote: Hello, how can I force mysql interactive client (actually the client library as well) to use a TCP port on the local host if I do: mysql -h localhost -u user -p --port=2000 I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' what might be a problem is: it seems, for reasons I don't entirely understand possibly related to the network configuration on this node, I need to use the name 'localhost' rather than the actual name of the node! thanks in advance for the help Murad - 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: Finding an ID between two dates
Add quotes to you date. SELECT time_period_id FROM time_periods WHERE '2003-03-14' BETWEEN time_period_start AND time_period_end; Worked for me. Roger -Original Message- From: Cory Hicks [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 3:05 PM To: [EMAIL PROTECTED] Subject: Finding an ID between two dates Hey folks! I am stumpedbeen reading the online manual, google groups, to no avail. I have a very simple table: time_period_id int(11) NOT NULL auto_increment, time_period_start date NOT NULL default '-00-00', time_period_end date NOT NULL default '-00-00', PRIMARY KEY (time_period_id) I am trying to get the ID that relates to a date falling between the range of time_period_start(2003-03-01) and time_period_end(2003-03-15): $sql = SELECT time_period_id FROM time_periods WHERE 2003-03-14 BETWEEN time_period_start AND time_period_end; I should get one result form the query, the id, but I get no results. Advice? Helpful hints always welcome.. Many thanks! Cory -- Cory Hicks Texas Research International, Inc. [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 - 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: forcing mysql client to use TCP socket?
In the last episode (Mar 14), Murad Nayal said: how can I force mysql interactive client (actually the client library as well) to use a TCP port on the local host if I do: mysql -h localhost -u user -p --port=2000 I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' localhost is a special hostname to mysql: it tells it to use the local Unix socket. To talk to a local mysqld server through TCP, use 127.0.0.1, or any other IP address configured on the machine. It'll be a bit slower than using a Unix socket, though. -- Dan Nelson [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: Paul, do you address this in any of your books?
i correct my example : select USERID,IF(DAY='2003-01-02',SUM(LIS_QUANTITY),0) as 2003-01-02,IF(DAY='2003-01-03',SUM(LIS_QUANTITY),0) as 2003-01-03...how_many_days_you_wishfrom YOUR_TABLES where YOUR_CONDITIONS GROUP BY DAY; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Gelu Gogancea [EMAIL PROTECTED] To: DANIEL GADDIS [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:05 PM Subject: Re: Paul, do you address this in any of your books? Hi, You can try to use IF(),CASE() function in the SQL statement. Eg. select USERID,IF(DAY='2003-01-02',COUNT(*),0) as 2003-01-02,IF(DAY='2003-01-03',COUNT(*),0) as 2003-01-03...how_many_days_you_wishfrom YOUR_TABLES where YOUR_CONDITIONS GROUP BY DAY; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: DANIEL GADDIS [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 10:40 PM Subject: Paul, do you address this in any of your books? I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel - 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 activity logging on a dual prozessor maschine
Hi, Maybe it's funny...but this is MySQL date/time format which is used in log files.On my MySQL servers date/time are formated in the same way. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Peter Stoehr [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 10:52 PM Subject: mySQL activity logging on a dual prozessor maschine Hi list, few days ago, I have installed a 2nd CPU in my ProLiant380. I recompiled a new kernel (2.4.17) for Debian 3.0. On the machine there is mySQL 3.23.52-2 installed (deb). Every thing works fine, just the logging of mySQL looks a little funny to me, especially the time: 030314 19:31:189745 Query SELECT de FROM b... 9745 Query SELECT gid FROM ... 030314 19:31:209745 Query SELECT gid FROM ... 030314 19:31:189745 Query SELECT gid FROM ... 9745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 030314 19:31:209745 Query SELECT de FROM b... 030314 19:31:189745 Query SELECT de FROM b... 9745 Query SELECT de FROM b... 9745 Query SELECT gid FROM ... 030314 19:31:209745 Query SELECT gid FROM ... The websites still grow up without errors and there is also nothing in the errorlogs. Did someone of you ever had this interesting thing? I could not find anything about that with Google or in the mailarchive here at mySQL. Thanks for your help, 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 - 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: FreeBSD + MySQL bottleneck
Jeremy Zawodny wrote: On Fri, Mar 14, 2003 at 12:03:14PM -0800, Joe Stump wrote: This may help - I just got this from a friend. http://jeremy.zawodny.com/blog/archives/000203.html Ugh. I *really* need to update that. It has become a popular reference. But I have some new information to add. The short version. If you use LinuxThreads and MySQL 4.0.x (where x is 4 or so), MySQL/FreeBSD is readlly damned good. But using LinuxThreads is a key piece there. FreeBSD's threading still sucks and can hamper MySQL. Jeremy The OP didn't indicate where the files are being kept, and under FreeBSD this can be significant. I know for example, that using UFS file systems, are very slow compared to newer file systems. However, I suppose, they have the trade off that they're perhaps safer in the event of a system crash. But the disk I/O is much much heavier on a UFS file system, than on some of the newer systems, like ext3 file systems. UFS seems to insist on doing writes as soon as possible, to prevent accidental loss of information due to a crash (just my guess, based upon the experience). I tried to speed up a very slow PostgreSQL database on UFS once.. I turned off fsync(2) for the database, and that helped some, but the disk I/O was still enormously extensive for what was going on. The same database under Linux flies (ext3) (with significantly less disk I/O activity). I was glad to see the MySQL supports the InnoDB in a partition (raw disk space). This can eliminate the file system from the equation, assuming that the necessary caching occurs in either the block device or in the database engine (using a raw char device). -- Warren W. Gay VE3WWG http://home.cogeco.ca/~ve3wwg - 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: Paul, do you address this in any of your books?
At 14:40 -0600 3/14/03, DANIEL GADDIS wrote: I'm running MySql 4.0.10-gamma-max-nt-log I have 1 table like the one below... ++-+--+ | DAY| USERID | LIS_QUANTITY | ++-+--+ | 2003-01-02 | H0850A1 | 539 | | 2003-01-02 | LBBSWJR |7 | | 2003-01-02 | O0600B3 | 21 | | 2003-01-03 | H0850A1 |6 | | 2003-01-03 | H2610A1 | 51 | | 2003-01-03 | O0600B3 | 19 | | 2003-01-04 | H0850A1 |8 | | 2003-01-04 | H2610A1 | 13 | | 2003-01-04 | LBBSWJR |3 | ++-+--+ I would like a sql query to produce output like the following... +-++++ | USERID | 2003-01-02 | 2003-01-03 | 2003-01-04 | +-++++ | H0850A1 |539 | 6 | 8 | | H2610A1 || 51 | 13 | | LBBSWJR | 7 || 3 | | O0600B3 | 21 | 19 || +-++++ Can I do this just by using sql and not adding php, perl, or any other language? If mysql can't handle this with sql only, would it be able to do it once subselects or other features are available in future releases? Anyone used postgresql? I wonder if it could handle it. Any thoughts? Thanks, Daniel Actually, if I remember correctly, I don't cover this anywhere. I recommend reading the cross-tabulation article on mysql.com that an earlier response referred to. -- Paul DuBois, [EMAIL PROTECTED] http://www.kitebird.com/ 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
Re: forcing mysql client to use TCP socket?
Hi, I think you have at least two ways: 1.To put the corect path to the sock file, in my.cnf configuration file. 2.To find where MySQL daemon create this file and add the argument -S to the mysql client application. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Murad Nayal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 10:42 PM Subject: forcing mysql client to use TCP socket? Hello, how can I force mysql interactive client (actually the client library as well) to use a TCP port on the local host if I do: mysql -h localhost -u user -p --port=2000 I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' what might be a problem is: it seems, for reasons I don't entirely understand possibly related to the network configuration on this node, I need to use the name 'localhost' rather than the actual name of the node! thanks in advance for the help Murad - 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
comment with foreign key text causes innodb CREATE TABLE failure
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: comment with foreign key text causes innodb CREATE TABLE failure Description: If an innodb table is created with a comment with the words foreign key, the table creation fails. The syntax used to work with MySQL-4.0.4-beta-win and earlier, running on both Linux and Windows. This query (missing the comment) executes successfully: CREATE TABLE a(a INT) TYPE=innodb; I consider this bug pretty important since many of our existing scripts use this style of comments. How-To-Repeat: Execute this query: CREATE TABLE a(a INT /* foreign key */) TYPE=innodb; You'll get this output: ERROR 1005: Can't create table '.\a\a.frm' (errno: 150) Fix: Change the /* foreign key */ comment in all database scripts. foreign-key works. Synopsis: comment with foreign key text causes innodb table creation failure Submitter-Id: Originator: Brandon Bird Organization: Pacer/CATS MySQL support: none Severity: serious Priority: Category: mysqld Class: sw-bug Release:mysql-4.0.11 Exectutable: mysqld-max-nt Environment: dell 4400; ntfs System:WinXP Compiler: Architecture: i - 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: Hard, unrecoverable InnoDB crash with MySQL 4.0.11
Yakari, - Original Message - From: Yukari Iwatani [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, March 14, 2003 3:36 PM Subject: Hard, unrecoverable InnoDB crash with MySQL 4.0.11 Hi: I'm running MySQL 4.0.11 (MySQL.com binary RPMs) on Red Hat Linux 7.3, all errata applied (kernel 2.4.18-26.7.xsmp). This morning, our server died hard, with the following error: 030314 2:44:13 InnoDB: Assertion failure in thread 7938074 in file btr0btr.c line 298 0x8073e0a handle_segfault + 450 0x8285738 pthread_sighandler + 184 0x8157b39 btr_page_alloc_for_ibuf + 241 0x815edda btr_page_split_and_insert + 854 0x816e9e4 btr_cur_pessimistic_insert + 1824 0x8127e40 ibuf_insert_low + 4332 0x81288ca ibuf_insert + 726 0x816b3f1 btr_cur_search_to_nth_level + 2025 0x812f423 row_ins_index_entry_low + 179 0x812fd6a row_ins_index_entry_step + 302 0x8130050 row_ins + 696 0x813018c row_ins_step + 252 0x8131787 row_insert_for_mysql + 507 0x80cfd32 write_row__11ha_innobasePc + 1122 0x80a7bfc write_record__FP8st_tableP12st_copy_info + 528 0x80a754d mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates + 1141 0x807fed9 mysql_execute_command__Fv + 6837 0x8082178 mysql_parse__FP3THDPcUi + 560 0x807d90d dispatch_command__F19enum_server_commandP3THDPcUi + 1545 0x80836ae do_command__FP3THD + 154 0x807cabf handle_one_connection + 631 0x8282eec pthread_start_thread + 220 0x82b65fa thread_start + 4 the algorithm should make sure there always are pages in the free list of the insert buffer tree. In this case the list was empty (page number 0x) , whence the assertion. When the server then tried to restart itself, it died with: InnoDB: Error: trying to access page number 4294396415 in space 0 Hmm... that is 2^32 - 570881. Looks like corruption. InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 030314 2:48:11 InnoDB: Assertion failure in thread 12292 in file fil0fil.c line 1176 The server would not come back up. We had to nuke the InnoDB tablespace and logs and restore from backup. You could have tried in my.cnf set-variable=innodb_force_recovery=4 to bring it up. If the problem recurs, you could try upgrading to Linux-2.4.20. If the corruption is a Linux kernel problem that might help. I've placed the resolved stack trace and full error logs online at: http://www.oxyopia.com/~yi/stack.resolved http://www.oxyopia.com/~yi/failure.log TIA, -- Yukari Iwatani [EMAIL PROTECTED] Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com 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
Refusing connections?
Ok, I have posted some things like this yesterday, but I have some new things to add... Yesterday I made a posting about having issues connecting my coldfusion to mySQL. Well I am pretty sure I have narrowed it down to mySQL being the issue... (NOTE - mySQL and CF are on the same server) The reason I think that MySQL is the issue is because when I start mySQL with: # cd /usr/bin/ # ./safe_mysqld --skip-grant-tables (I know this is a security hole, but this was just for testing.) I am able to connect to the database no problem (through coldfusion) . So now - (being new to mysql) I assume I have an issue with the way I configured MySQL or the user. So to confirm - Here is how I created the user: MysqlGRANT SELECT, INSERT, UPDATE, DELETE, -CREATE, DROP, ALTER, INDEX, FILE ON -addressbook.* TO [EMAIL PROTECTED] -IDENTIFIED BY 'password'; This was taken direct from a mysql book, and I have recreated the user over 15 times, so the chances of me fudging that every time are slim... BUT if you see an error in the syntax, maybe my book is wrong. I also created one like this too: MysqlGRANT SELECT, INSERT, UPDATE, DELETE, -CREATE, DROP, ALTER, INDEX, FILE ON -addressbook.* TO user@'%' -IDENTIFIED BY 'password'; The other thing to add is that when I am SSH'd into the box, and I can connect with mysqladmin, mysql and mysqlcc no problem. And since CF is located on the same box, and there is no firewall enabled on itself (IE Port 3306 being blocked), I do not see what could be keeping it from connecting.. And like I said - when I start with that mysql command and disable user id's it connects fine Oh please help me get some rest! ;-) .::d::. --- THEbeatingsWILLcontinueUNTILmoraleIMPROVES --- - 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: Looking for a bona fide distributed database that is open source
Gelu Gogancea wrote: It's quite right, some SQL DataBase engines(like Oracle,DB2,msSQL) have implemented facilities for this. Distributed database can be achieved,like example, using DISTRIBUTED OBJECTS(let's say CORBA).In my understanding that means that MySQL already can be used like distributed database. Can be used like is not the same as is. Similarly, by writing software in C, you can achieve SQL and use OS files like a relational database -- that doesn't mean that flat files *are* a relational database. Finally,What i wish to say is that distributed databases depend on us to be achieved and not by the RDBMS. Yes, it can be done manually... but there are many advantages to having the distributed capabilities built into the RDBMS itself. I've used such systems (Computer Associates' OpenIngres product), and when distributed capabilities are needed it's far superior to use a product designed for it. Some examples of the improvements are: 1) Distributed optimizer automatically takes advantage of changes to distribution structure 2) Applications can be written which are independant of distribution structure 3) Complexity of applications is reduced, resulting in lower development costs and fewer bugs 4) Transaction management spanning databases on multiple computers Paul have right and i read very carefully him message:He said (very explicit and without any doubt)that MySQL can not manage multiple tables which are hosted on different servers with one single query.For me this is not a reason to not use MySQL for distributed database system. It's one factor, though. If we relax the definition of a distributed database system to be a client-server RDBMS which can be used to build an application which can combine information from queries to multiple databases on multiple computers, as it seems you wish to do, then yes, MySQL qualifies, and has its usual advantages of low cost, open source, and speed. Depending on the project's needs, these may or may not be enough to counterbalance built-in distribution capabilities of true distributed RDBMSs. If i remember well, on the MySQL website is a link to a german company which has develop a modules which treat multiple MySQL database(hosted on different machine of course) to a logical one. If so, then using it in conjunction with MySQL would indeed create a distributed database management system. Bruce Feist - 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: Looking for a bona fide distributed database that is open source
Sorry, here's the link: http://www.emicnetworks.com/products/products_eac_mysql.html And the link to the MySQL newsletter issue: http://www.mysql.com/newsletter/2003-02/a000125.html Brian McCain - Original Message - From: Bruce Feist [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, March 14, 2003 5:26 PM Subject: Re: Looking for a bona fide distributed database that is open source Gelu Gogancea wrote: It's quite right, some SQL DataBase engines(like Oracle,DB2,msSQL) have implemented facilities for this. Distributed database can be achieved,like example, using DISTRIBUTED OBJECTS(let's say CORBA).In my understanding that means that MySQL already can be used like distributed database. Can be used like is not the same as is. Similarly, by writing software in C, you can achieve SQL and use OS files like a relational database -- that doesn't mean that flat files *are* a relational database. Finally,What i wish to say is that distributed databases depend on us to be achieved and not by the RDBMS. Yes, it can be done manually... but there are many advantages to having the distributed capabilities built into the RDBMS itself. I've used such systems (Computer Associates' OpenIngres product), and when distributed capabilities are needed it's far superior to use a product designed for it. Some examples of the improvements are: 1) Distributed optimizer automatically takes advantage of changes to distribution structure 2) Applications can be written which are independant of distribution structure 3) Complexity of applications is reduced, resulting in lower development costs and fewer bugs 4) Transaction management spanning databases on multiple computers Paul have right and i read very carefully him message:He said (very explicit and without any doubt)that MySQL can not manage multiple tables which are hosted on different servers with one single query.For me this is not a reason to not use MySQL for distributed database system. It's one factor, though. If we relax the definition of a distributed database system to be a client-server RDBMS which can be used to build an application which can combine information from queries to multiple databases on multiple computers, as it seems you wish to do, then yes, MySQL qualifies, and has its usual advantages of low cost, open source, and speed. Depending on the project's needs, these may or may not be enough to counterbalance built-in distribution capabilities of true distributed RDBMSs. If i remember well, on the MySQL website is a link to a german company which has develop a modules which treat multiple MySQL database(hosted on different machine of course) to a logical one. If so, then using it in conjunction with MySQL would indeed create a distributed database management system. Bruce Feist - 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: Looking for a bona fide distributed database that is open source
This product was mentioned recently in the MySQL newsletter and in a MySQL press release. From the company's description of it: EAC uses clustering technology with a unique group communication replication technology, which provides the consistency of synchronous replication at the speed of asynchronous replication, with no decrease of overall performance. EAC combines multiple physical SQL databases, working as one logical, highly available database. It takes full advantage of available resources and balances the workload between clustered databases. It's not free, but they've got a free evaluation version, and it looks like it might do what you're looking for. If anyone has used this product, I for one would be really interested in hearing any feedback on it. Brian McCain - Original Message - From: Bruce Feist [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, March 14, 2003 5:26 PM Subject: Re: Looking for a bona fide distributed database that is open source Gelu Gogancea wrote: It's quite right, some SQL DataBase engines(like Oracle,DB2,msSQL) have implemented facilities for this. Distributed database can be achieved,like example, using DISTRIBUTED OBJECTS(let's say CORBA).In my understanding that means that MySQL already can be used like distributed database. Can be used like is not the same as is. Similarly, by writing software in C, you can achieve SQL and use OS files like a relational database -- that doesn't mean that flat files *are* a relational database. Finally,What i wish to say is that distributed databases depend on us to be achieved and not by the RDBMS. Yes, it can be done manually... but there are many advantages to having the distributed capabilities built into the RDBMS itself. I've used such systems (Computer Associates' OpenIngres product), and when distributed capabilities are needed it's far superior to use a product designed for it. Some examples of the improvements are: 1) Distributed optimizer automatically takes advantage of changes to distribution structure 2) Applications can be written which are independant of distribution structure 3) Complexity of applications is reduced, resulting in lower development costs and fewer bugs 4) Transaction management spanning databases on multiple computers Paul have right and i read very carefully him message:He said (very explicit and without any doubt)that MySQL can not manage multiple tables which are hosted on different servers with one single query.For me this is not a reason to not use MySQL for distributed database system. It's one factor, though. If we relax the definition of a distributed database system to be a client-server RDBMS which can be used to build an application which can combine information from queries to multiple databases on multiple computers, as it seems you wish to do, then yes, MySQL qualifies, and has its usual advantages of low cost, open source, and speed. Depending on the project's needs, these may or may not be enough to counterbalance built-in distribution capabilities of true distributed RDBMSs. If i remember well, on the MySQL website is a link to a german company which has develop a modules which treat multiple MySQL database(hosted on different machine of course) to a logical one. If so, then using it in conjunction with MySQL would indeed create a distributed database management system. Bruce Feist - 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
foreign key constraint problem
foreign key constraint problem: --- create table stud(rno integer not null, name char(10), primary key(rno)); create table mark(rno integer not null references stud, mark integer); mysql insert into stud values(1,'a'); Query OK, 1 row affected (0.22 sec) mysql insert into stud values(2,'b'); Query OK, 1 row affected (0.00 sec) mysql select * from stud; +-+--+ | rno | name | +-+--+ | 1 | a| | 2 | b| +-+--+ 2 rows in set (0.06 sec) mysql insert into mark values(3,5); Query OK, 1 row affected (0.00 sec) mysql select * from mark; +-+--+ | rno | mark | +-+--+ | 3 |5 | +-+--+ 1 row in set (0.05 sec) how is it possible. there is no rollno 3 in the stud table, then how does it accept the rollno 3 in the mark table. does foreign key really working, or i am doing something wrong. please help me, thank you in advance, vishnu. how can i convert the default date format to the format 'dd-mm-'. or how can i insert the date in the format 'dd-mm-'. __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.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