Re: Database Layout (Design) Question
This isn't exactly what I was looking for, but it works like a charm for both my needs and the sys admins. Go figure everyone is happy now! Many Thanks! -Tyler Kishore Jalleda wrote: The delete would definitely depend upon the size of the record set being deleted, anyway assuming I comprehended your situation correctly ,I would suggest using the Merge storage engine for your needs , and keep every single day of data in a seperate MyISAM table, and merge all those tables together into a single merge table, so when you want to do any maintenance on any data older than x days just alter the table and take that mailxx table out of the merge table , delete it compress it, do anyhting with it and then just add a other one (you could easily run a nightly cron job for this) Please look at http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html Kishore Jalleda On 1/31/07, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I'm trying to wrap my head around a performance problem our institution is having on our Mysql Server which hosts all of our logs from around campus. Specifically our MailLogs tables. What I'm having a hard time with is we have metadata such as: CREATE TABLE mail00 ( host varchar(32) default NULL, fromMTA varchar(44) default NULL, nextMTA varchar(44) default NULL, messageID varchar(44) default NULL, messageID2 varchar(44) default NULL, sender varchar(80) default NULL, recipient varchar(120) default NULL, recipient2 varchar(120) default NULL, date date default NULL, time time default NULL, program varchar(44) default NULL, ACTION varchar(44) default NULL, detail varchar(120) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY sender (sender), KEY recipient (recipient), KEY MessageID (messageID), KEY seq (seq), KEY time (time), KEY date (date), KEY ACTION (ACTION), KEY messageID2 (messageID2), KEY fromMTA (fromMTA) ) TYPE=MyISAM MAX_ROWS=9900; We might end up storing two to three gigs of logging data per day from our mail servers. When we had the process setup to purge data out of this table that is older than 14 days, it would lock and take great deal of time process the request. My question is, is the structure of my table the problem or is it just the size of the data that is just going to take that long due to Disk IO? This isn't a table structure that I came up with, I'm just looking to optimize the performance of the server. Currently around midnight the sysadmin currently drops table 13, then moves 12 - 13, 11 - 12, etc... and creates a 00. All of this is because it takes to long to purge out one days worth of data . This will eventually cause a problem when we try to develop a program to scan the logs, it will need to scan through all 14 tables instead of just one. Is there a better way that mitigates the performance and flexibility? Or just a better way in general? Thanks, -Tyler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Layout (Design) Question
I'm trying to wrap my head around a performance problem our institution is having on our Mysql Server which hosts all of our logs from around campus. Specifically our MailLogs tables. What I'm having a hard time with is we have metadata such as: CREATE TABLE mail00 ( host varchar(32) default NULL, fromMTA varchar(44) default NULL, nextMTA varchar(44) default NULL, messageID varchar(44) default NULL, messageID2 varchar(44) default NULL, sender varchar(80) default NULL, recipient varchar(120) default NULL, recipient2 varchar(120) default NULL, date date default NULL, time time default NULL, program varchar(44) default NULL, ACTION varchar(44) default NULL, detail varchar(120) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY sender (sender), KEY recipient (recipient), KEY MessageID (messageID), KEY seq (seq), KEY time (time), KEY date (date), KEY ACTION (ACTION), KEY messageID2 (messageID2), KEY fromMTA (fromMTA) ) TYPE=MyISAM MAX_ROWS=9900; We might end up storing two to three gigs of logging data per day from our mail servers. When we had the process setup to purge data out of this table that is older than 14 days, it would lock and take great deal of time process the request. My question is, is the structure of my table the problem or is it just the size of the data that is just going to take that long due to Disk IO? This isn't a table structure that I came up with, I'm just looking to optimize the performance of the server. Currently around midnight the sysadmin currently drops table 13, then moves 12 - 13, 11 - 12, etc... and creates a 00. All of this is because it takes to long to purge out one days worth of data . This will eventually cause a problem when we try to develop a program to scan the logs, it will need to scan through all 14 tables instead of just one. Is there a better way that mitigates the performance and flexibility? Or just a better way in general? Thanks, -Tyler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write subqueries?
Hello! another simpler solution : ... Thanks, Mathias! You helped me greatly! Now my program works as desired. -- Good luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to write subqueries?
Hello! MySQL v. 4.1.11. I'm trying to write a subquery, MySQL says Error. select num, theme, intro from vt_(select pnid from vt_partition where pnid regexp '^[0-9]{11}$') order by date desc, timer desc; describe vt_parition: +--+--+--+-+--++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+--++ | num | int(11) | | MUL | NULL | auto_increment | | partname | varchar(255) | | | || | pnid | varchar(11) | | | || | timer| time | | | 00:00:00 || +--+--+--+-+--++ describe vt_24411620611; +---+--+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+--+--+-+++ | num | int(11) | | MUL | NULL | auto_increment | | login | varchar(10) | | ||| | mail | varchar(35) | | ||| | theme | varchar(100) | | ||| | intro | text | | ||| | text | text | | ||| | date | date | | | -00-00 || | timer | time | | | 00:00:00 || +---+--+--+-+++ vt_ is a prefix, pnid is a postfix. And the name of the table is, for expamle, vt_01234567890. Where is/are the mistake(s) in my subquery? Please explain me how to write a subquery in the right way. -- Good luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write subqueries?
Hello! Many thanks to everybody who tried to help me! N.B. you should have only one pnid to make your query work (UNIQUE pnid), or LIMIT the result of the subquery by LIMIT 1. I need to do the following: Get one or more than one pnid from vt_partition and then make queries to the vt_pnid tables. The result (from all the requested vt_pnid tables) should be sorted by date and time. How to do it? The version of MySQL should be 4.1.11 or lower. It would be great if the query works even on v. 3.23.58. First off, you are not writing a subquery. You are attempting to build a SQL query using the results of another query. What you are attempting is dynamic SQL. I am not familiar with a mechanism in 4.1.11 that allow you to execute a string (or a string variable) from within a SQL statement. Damn! You're right!! I only looked at the subquery itself, not the context and I missed that his subquery seems to an expression to get part of his table name! That is definitely not valid in any dialect of SQL that I know ;-) Agree, I was wrong. :-) -- Good luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More Illegal mix of collations trouble.
Last week we upgraded from mysql 4.0 to 4.1.7. Since then some queries on newly created tables (ie created since the upgrade) are failing with the error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' I have been doing a lot of reading. (Until this problem I had never heard of a character set collation.) It looks like some database connections are specifying UTF-8 and over-ridding the default global setting of latin1. See the mysql command line tool log below to see that the variables don't match the global variables. Where do I look to see what is causing this? The /etc/my.cnf already contains this line in the [mysqld] section: default-character-set=latin1 The error message occurs both when I use the mysql command line tool from the local host (mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686)) and also from PHP on a different server (phpinfo() reports Client API version 3.23.56). When I add the following line to /etc/my.cnf on the same machine as the server runs on and connect with the command line tool I don't have the same problem but this does not fix the connections from the PHP client. (Added to the [mysql] section.) default-character-set=latin1 I don't understand how or why the default connection data encoding would be different from the default table encoding. Maybe someone can explain this to me as well. By using SET CHARACTER SET latin1; in the command line tool the problem goes away but it's silly to have to tell all my clients they have to explicitly set the character set every time they connect. What settings should be adjusted to prevent the errors from occuring? Thanks, /Chad mysql SELECT SUM( Credits ) - FROM Acct_Payments - WHERE Void = 'No'; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' mysql show variables like 'char%'; +-- ++ | Variable_name| Value | +-- ++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-max-4.1.7-pc-linux-i686/share/mysql/charsets/ | +-- ++ 7 rows in set (0.00 sec) mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql show global variables like 'char%'; +-- ++ | Variable_name| Value | +-- ++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-max-4.1.7-pc-linux-i686/share/mysql/charsets/ | +-- ++ 7 rows in set (0.00 sec) mysql show global variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql show create table Acct_Payments \G
AntiVir ALERT [your mail: ]
* * * * * * * * * * * * * * * AntiVir ALERT * * * * * * * * * * * * * * * AntiVir has detected the following in a mail from your address: Worm/MyDoom.AE The mail was not delivered. Please remove any potential malicious software from your computer before sending a new mail with attachments. Mail-Info: --8-- Message-Id: [EMAIL PROTECTED] From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Fri, 19 Nov 2004 22:16:30 + Subject: --8-- This version of AntiVir is licensed for private and non-commercial use. -- AntiVir for UNIX Copyright (C) 1994-2003 by H+BEDV Datentechnik GmbH. All rights reserved. For more information see http://www.antivir.de/ or http://www.hbedv.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binary size problem
I compiles a C application which uses mysql library on two RedHat 9.0 machines. Both machines are using the same GCC and the same MySQL 4.0.16 : MySQL-client-4.0.16-0 MySQL-server-4.0.16-0 MySQL-devel-4.0.16-0 MySQL-shared-compat-4.0.16-0 gcc-3.2.2-5 gcc-c++-3.2.2-5 libgcc-3.2.2-5 In the MACHINE_1, the resulting binary size is about 20 KB, and in the MACHINE_2, the resulting binary is about 180 KB. Anyone can explain why in the MACHINE_2, the binary size is very big ? Here is my compile script : #!/bin/sh echo building $1 gcc -o $1 $1.c -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -lz If I do strings to the binary from MACHINE_2 (180 KB), I saw (see below, not all). Is it possible that all the mysql libraries are statically linked ? Please help me, Thanks. /lib/ld-linux.so.2 libz.so.1 uncompress _DYNAMIC _init _fini _GLOBAL_OFFSET_TABLE_ _Jv_RegisterClasses __gmon_start__ libc.so.6 strcpy bcmp stdout vsprintf getlogin connect readdir64 fdopen geteuid memmove getenv __strtol_internal qsort fgets memcpy readlink malloc endpwent socket select fflush bzero strrchr write fprintf strcat inet_addr __stpcpy chdir setsockopt memchr strstr signal read getrusage realloc strtok sscanf __xstat64 symlink localtime inet_ntoa opendir poll strcmp shutdown getpwuid getcwd gethostbyname getpwnam getservbyname __mempcpy fclose getpeername gethostbyname_r stderr error __lxstat64 fputc strftime htons __errno_location exit atoi fileno _IO_stdin_used __libc_start_main strlen strchr fputs realpath closedir fcntl __fxstat64 strpbrk free fopen64 _edata __bss_start _end GLIBC_2.3 GLIBC_2.1.2 GLIBC_2.1 GLIBC_2.2 GLIBC_2.0 PTRh0w 8t+B8tB8t!B 8t+B8tB8t!B RSWV root lik ,WVS j0j0 F [^_ j0h PjnSW [EMAIL PROTECTED](@80t#@ @@+E @ [EMAIL PROTECTED](@80t#@ @@+E [EMAIL PROTECTED](@80t#@ [EMAIL PROTECTED](@80t#@ 8([EMAIL PROTECTED](t(@8(t#@ PRQSV A@;As j0jP z'tF tP n\t* 'tL USER LOGNAME LOGIN UNKNOWN_USER like ' max-allowed-packet ssl-cipher repl-parse-query enable-reads-from-master replication-probe disable-local-infile local-infile connect-timeout interactive-timeout default-character-set character-sets-dir ssl-capath ssl-ca ssl-cert ssl-key return-found-rows debug database host init-command user timeout pipe password compress socket port options client d:t:o,/tmp/client.trace SHOW SLAVE HOSTS SHOW SLAVE STATUS mysql MYSQL_TCP_PORT /var/lib/mysql/mysql.sock MYSQL_UNIX_PORT MYSQL_PWD localhost unknown show databases show tables 4.0.16 %08lx%08lx [EMAIL PROTECTED] packet SSL connection error Error connecting to master: Error connecting to slave: Error on SHOW SLAVE HOSTS: Error on SHOW SLAVE STATUS: Embedded server Got packet bigger than 'max_allowed_packet' Can't initialize character set %-.32s (path: %-.100s) Can't set state of named pipe to host: %-.64s pipe: %-.32s (%lu) Can't open named pipe to host: %-.64s pipe: %-.32s (%lu) Can't wait for named pipe to host: %-.64s pipe: %-.32s (%lu) . .. __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 crashed; Please help
Can someone help me find out what caused this crash? My system is a Red Hat 7.3 on a HP Netserver LC2000, dual PIII 800mhz with 2.5G ram. 3 disks in raid 5. The MySQL database is 4.0.17 040212 19:27:36 read_const: Got error 126 when reading table ./exp4/sql_cache Number of processes running now: 0 040212 19:28:04 mysqld restarted InnoDB: Fatal error: cannot allocate 1073758208 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 24482732 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=1044480 max_used_connections=0 max_connections=800 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 291705 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8450b80 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe718, stack_bottom=0x58575655, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x54535251 is invalid pointer thd-thread_id=1601790316 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040212 19:28:06 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 crashed; Please help
[EMAIL PROTECTED] wrote: Did you perform a stack trace? No, how do I do that? /Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A current row updating counter for a mass Update or similar?
Ah that's sheer genius! Thanks muchly, I never figured in to being able to use SQL with it's own variables so to speak - I'm sure I'll find more than one use for this new ability! Much appreciated Ed :) - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 3:19 PM Subject: RE: A current row updating counter for a mass Update or similar? Sure, try this. I'm using 4.1.2 in case it matters. set @n=0; UPDATE Ranks_table SET Rank= (select @n:[EMAIL PROTECTED]) ORDER BY Score DESC; Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A current row updating counter for a mass Update or similar?
Hi all, I'll just get straight to the problem (simplified): Say I have the following table/columns: ID, Score, Rank 1, 20, 0 2, 50, 0 3, 10, 0 4, 5, 0 5, 97, 0 Is it possible, in a single query (using sub-queries if necessary), to give all of these ID's the correct rank... ie if such a thing existed it might look like: UPDATE table_name SET rank=(current_row_updating+1) ORDER BY score DESC; Which would, you'd hope, make the table data then read: ID, Score, Rank 1, 20, 3 2, 50, 2 3, 10, 4 4, 5, 5 5, 97, 1 But I could find no nice looking commands that would give me this (perhaps that is not possible due to the nature of the database structure and the nature of updating? Though there must be some counter for it to report back with how many rows matched, how many were updated etc.). I also tried with a sub-query which the equivalent for the above example might look something like: UPDATE table_name SET rank=(SELECT COUNT(*) FROM table_name AS t2 WHERE t2.scoretable_name.score); But couldn't seem to make it work - I received MySQL error messages with my attempts, I think possibly because I am trying to use the table I am trying to update to also pull select data from during the update - and it'd need a little adding to it anyway - if scores were equal, it'd want to default to lower ID number wins the higher rank. I can't seem to find any obvious nice way of doing this, other than the poor method of looping/iterating through with an Ordered select, and then sending individual UPDATES for each and every row received by the select (which in the above simplified example would be 6 query calls - 1 to get the select, followed by 5 seperate updates sent by PHP using PHP to know what rank # we were currently on). Thanks in advance, I hope somebody can point me to a possible solution of getting all of this with a single query as I have attempted, I'd appreciate any help on this matter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL log files
Hello, How can I enable MySQL 4.0.16 log ? I just compiled and installed it in my RedHat 9.0 system, but I cant see any log files. I use default /etc/my.cnf from my-large.cnf. Sorry, if this question is too newbie, but I cant understand clearly reading MySQL manual. Thanks for helping me :) __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
After installing MySQL 4.0.16 (rpm), it did not create /etc/my.cnf ?
Hi, My server is using RedHat 9.0. I just CHANGED the mysql from 3.23.58 (rpm) to 4.0.16 (rpm). What I did : # rpm -e all mysql 3.23.58 rpm # rm -rf /var/lib/mysql then install the 4.0.16 (rpm) # rpm -i MySQL-server MySQL-client ... MySQL-devel ... MySQL-shared-compat After this, I can start the server. I can change the root password using mysqladmin. I can create database. But I cant find /etc/my.cnf is this normal ? I verified by issuing: rpm -ql MySQL-server ... But again, I did not see /etc/my.cnf Anyone know what is the 'standard' my.cnf for MySQL 4.0.16 ? Thanks, __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: After installing MySQL 4.0.16 (rpm), it did not create /etc/my.cnf ?
After I copied /usr/share/mysql/my-large.cnf it to /etc/my.cnf Then I restart mysql, and executed ps -ax I got this : 32175 pts/3S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/myserver.pid 32200 pts/3S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/myserver.pid --skip-locking --port=3306 Why there is no reference to /etc/my.cnf there ? Because if I see in other 3.23.58 (rpm), it's like this : 2653 ?S 0:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf 2678 ?S 0:02 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run Something wrong with my 4.0.16 (rpm) installation ? Or should I edit something in the /etc/my.cnf ? it just straight copy from /usr/share/mysql/my-large.cnf Please help, Thanks --- Dathan Vance Pattishall [EMAIL PROTECTED] wrote: Create one. Look in your base directory under support-files Look for my-small.cnf - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Admin-Stress [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 11:44 AM --To: [EMAIL PROTECTED] --Subject: After installing MySQL 4.0.16 (rpm), it did not create --/etc/my.cnf ? -- --Hi, -- --My server is using RedHat 9.0. -- --I just CHANGED the mysql from 3.23.58 (rpm) to 4.0.16 (rpm). -- --What I did : -- -- # rpm -e all mysql 3.23.58 rpm -- # rm -rf /var/lib/mysql -- -- then install the 4.0.16 (rpm) -- -- # rpm -i MySQL-server MySQL-client ... MySQL-devel ... MySQL- --shared-compat -- --After this, -- --I can start the server. --I can change the root password using mysqladmin. --I can create database. -- --But I cant find /etc/my.cnf -- --is this normal ? -- --I verified by issuing: rpm -ql MySQL-server ... --But again, I did not see /etc/my.cnf -- --Anyone know what is the 'standard' my.cnf for MySQL 4.0.16 ? -- --Thanks, -- --__ --Do you Yahoo!? --Protect your identity with Yahoo! Mail AddressGuard --http://antispam.yahoo.com/whatsnewfree -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help on my query statement
I have a table called TRANSACTION, it has some fields : REGDATE - date of the transaction WHAT - a string (NOT UNIQUE) CUSTOMER - customer code I want to query all WHAT that the date of the transaction was 15 days before. So, my sql query is : SELECT WHAT FROM TRANSACTION WHERE REGDATE Now() -INTERVAL 15 DAY But, because of WHAT is not unique, then I could receive some duplicates (customer can send some WHAT in the same REGDATE). How can I query only the unique WHAT ? I need to list all WHAT (uniquely) that the date transaction was 15 days before. Please help, Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple question about updating a table field
I have a numeric table field, named CREDIT. How can I add a number (+50) to that field for all the table record ? is this correct? UPDATE ThisTable SET CREDIT=CREDIT+50 Please help, Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hex search?
Hiya, Is there any way to do a hexadecimal search via MySQL? I've found that the data I uploaded from text files has some end-of-line characters in it, and its causing my data to break in mid-line when spitting out a text file. Would be cool to search the table for offending characters if possible. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL memory usage
Hello, On a P4-2.6Ghz, 1Gb mem server of ours, mysql keeps getting slower and slower because the database gets bigger and bigger. At the moment the database is 5.5Gb big, the biggest table being 1.1Gb. 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? Our keycache hitrate is at 99.6% so I think the key_buffer_size is big enough, but would increasing this setting help? We experimented with doubling this buffer, but mysql got slower from it. Or could someone have a look at our my.cnf, and see if anything could be better? Here's my.cnf: port= 3306 socket = /tmp/mysql.sock set-variable= key_buffer_size = 256M set-variable= max_allowed_packet = 1M #set-variable = thread_stack= 128K set-variable= max_connections = 300 #set-variable = query_buffer_size = 4M set-variable= record_buffer = 32M #set-variable = record_buffer = 64M set-variable= record_rnd_buffer = 32M #set-variable = sort_buffer = 8M set-variable= sort_buffer = 4M set-variable= join_buffer = 16M set-variable= table_cache = 768 What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. Kind regards, Rick Jansen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help with sql query : DATETIME function
I have a table with this structure : username VARCHAR(32) address VARCHAR(128) datereg DATETIME // date of registration classINT How is the sql query for username who are their datereg was 30 days ago and class = 1? thanks __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.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
create temp table
In MySQL is it possible to create a temporary table without having to specify all the properties? On SQL Server (at least via query analyzer) you can create temp tables fairly easily and the properties of each field are inherited from the regular table. Doesn't look like thats the case with MySQL, but it doesn't hurt to ask first ;) chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
multi-table join
In MySQL, I need to do a multi table join in the context of a left join. Have three tables: NAME, ADDRESS, and COUNTRY. Address contains a country code (ie CAN) and COUNTRY contains the translation (ie Canada). What I'd like to do is get all NAMES, all datat from ADDRESS if it exists, and the full country name from COUNTRY if there is an address. I tried: SELECTA.NAME, B.ADDRESS, B.CITY, C.COUNTRY_DESCR FROM NAME A LEFT JOIN ADDRESS B ON A.ID = B.ID JOIN COUNTRY C ON B.COUNTRY = C.CODE but I got a resource doesn't exist type error. Is this even possible? I know left joins can be tricky, but this seems like it should work. Maybe I have the syntax wrong? I even tried using parentheses as in: SELECTA.NAME, B.ADDRESS, B.CITY, C.COUNTRY_DESCR FROM NAME A LEFT JOIN ADDRESS B ON (A.ID = B.ID (JOIN COUNTRY C ON B.COUNTRY = C.CODE)) but that neither worked nor changed the error message. Help? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
updating some records in a table
Hi, I am new to mySQL, so I need some help. I have a table, which has record structure like this: - customer - name - register_date - expired Every day, I add some records, new customers. The field 'expired' is just a flag, either '1' or '0'. If register_date is 3 months ago then expired = 1. The format of register_date is unixtime, or Epoch. How can I update the entire database, for setting the expired field ? now = current_unixtime(); threemonths = 3 * 30 * 24 * 60 * 60; select * from table_name where register_date + threemonths now How to construct the proper select statement ? I use VARCHAR for register_date. select for i = 0 to number_of_rows do update table_name set expired = 1; I just could not think how to implement this. I need some example code how to update some records in table like in my situation. I use C ... or Perl Thanks. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
outer join syntax
I would like to return rows, with specific columns, for each individual in my NAMES table, with address and e-mail if these exist. Thought I had the syntax down but I'm getting NULL results where I know there is data. I'm used to using SQL Server shortcut * for outer joins, guess that's catching up with me now :( I would have thought the syntax would be: SELECT A.LAST_NAME, A.FIRST_NAME, B.CITY, B.STATE, C.EMAIL FROMNAMES_TBL A LEFT JOIN ADDRESS_TBL B ON A.DBID = B.DBID LEFT JOIN EMAIL_TBL C ON A.DBID = C.DBID WHERE A.LAST_NAME = 'Smith' Am I doing it wrong? chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query only returns one row
Hi, I finally established a connection to my db after I realized that it was located in a different directory than that which I kept placing my php files. As some would say, D'OH! The code below is intended to select and display all 1300 names from the NAMES_TBL but is only returning the first row. I'm still a bit new to php so this is probably something simple - a shove in the right direction would be quite helpful. thanks! chris ?php $db = mysql_connect(host, user, pwd); mysql_select_db(dbname,$db); $result = mysql_query(SELECT * FROM NAMES_TBL,$db); printf(DBID: %sbr\n, mysql_result($result,0,DBID)); printf(First Name: %sbr\n, mysql_result ($result,0,FIRST_NAME)); printf(Last Name: %sbr\n, mysql_result($result,0,LAST_NAME)); ? --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
closing database (mysql C API)
Hi, I wrote an application using mysql C API. If I exit my application without closing database, will it be closed automatically? void error(int code) { printf(error: %d\n, code); exit(0); } int main() { ... error(-255); ... } How to pass the mysql database connector to a function? is this correct? : void error(int code, MYSQL db) { mysql_close(db); printf(error: %d\n, code); exit(0); } Because I am not sure this will be correct ... should not it using pointer? Thanks. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
updating a field more than once
Hi, I wrote a simple CGI application using mysql. What it does is really simple : connect to mysql database ... do something ... update a field ... do something again ... close mysql The update is like TOTAL = TOTAL + AMOUNT. If let say, 1000 users accessing my CGI in the same time, will it be OK ? will the update process be corrupted? should I implement LOCKING? I am new to mysql. Thanks, kapot __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
mysql-text-run aborts due to inability to create thread to handle bootstrap
Description: When running mysql-test-run the test aborts with the following message: Installing Test Databases Removing Stale Files Installing Master Databases 021124 1:25:15 Warning: Can't create thread to handle bootstrap 021124 1:25:15 Aborting 021124 1:25:15 ../sql/mysqld: Shutdown Complete Error: Could not install master test DBs How-To-Repeat: On my system, I reproduce the error with the line ./mysql-test-run I am not sure if this is because the architecture is not supported or if cause is somewhere else Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:system PRIVILEGED account Organization: Hamline University MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.51 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: OSF1 jacobi.hamline.edu V3.2 62 alpha Machine: alpha Some paths: /usr/local/bin/perl /sbin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf3.2/2.8.1/specs gcc version 2.8.1 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root system17 Jul 19 1996 /lib/libc.a - ../ccs/lib/libc.a lrwxrwxrwx 1 root system17 Jul 19 1996 /usr/lib/libc.a - ../ccs/lib/libc.a Configure command: ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
restricting mysql
Hi, After I installed mysql, I noticed there is an open port now, port 3306. Can I restrict this so only from localhost can connect? I meant to close port 3306. But without using firewall :( I dont have knowledge about this. If there is no way to do this without firewall, then I will learn it. I tried --skip-networking, but then mysqld could not be started. I got this from google.com. Is it the correct way? Thanks. kapot __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site 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: SQL question
Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.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: creating database error
After playing around with 'test' database, then I would like to create my own database. I logged in into mysql using root account and created a database. But, when I logged in using regular user, I got his error : ERROR 1044: Access denied for user: '@localhost' to database 'card' What necessary to do to create database? I tried to create a database using regular user, still got same error. I did change create_priv='Y' in user table from mysql database for this regular user. Anyone know how the basic steps to create database? or any documentation/website resource would be appreciated. Thanks. kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: load data infile
Hi Everyone, Many of you suggested that, in regards to my problem with LOAD DATA [LOCAL] INFILE ... , I may have permissions problems. I checked with the host and the reply was, You have access to do all commands except for the grant command. So it would appear that I SHOULD be able to load data from my laptop. But as of yet I cannot. To reply to some of your more specific questions: Yes, the file is definitely located in the directory specified. Using \\ instead of a single \ in the path seems to be a good thing, but the file is still not found. I keep getting Errcode: 2 and using perror only tells me that there's an error in my Perror code, so I have no idea what Errcode 2 is (though it is likely something telling me it could not find the file!) Was mysqld started with --local-infile=0? Uh, dunno. The host provides an interface which I started working with but I am wondering if maybe there isn't a bug in it somewhere. Probably best to write my own query window, either way all that I can provide is my user name and password, I have no control over the servers or application beyond logging in and supposedly having all but grant permission. ...have you tried using the UNC path to the file? I'm technical enough to know that UNC doesn't stand for University of North Carolina, but that's about it. I am going to assume that you are asking if I've made sure the path is the actual path and not some pseudo-thingy that windows does. Indeed I believe I have. If your question is in relation to something else, I'm open to being educated :) Thanks to Aaron Foster for the vote of confidence: Give yourself full rights - go on, you know you deserve it! I know I deserve it, but those damn hoohas hosting my site just won't give it up! And I certainly wouldn't insult toads -- they probably don't care which version of MySQL my database is on, and they LIKE bugs! I don't know which version they are running, its not in their documentation. So ... I am going to bypass the hosts administrative interface and find my own way in, see if that works (yea, one more thing to do) and maybe do a positive binary dance or some chanting. If I don't make progress on this in the next day I'm gonna brew up some damn strong coffee and INSERT the data --- gotta have data! Admin (aka chris) BTW thanks for your willingness to help :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 in v4.0.2 not doing UPDATE
Description: I am using MySQL v4.0.2 and I have set up a master database and 2 slave databases, all on separate Solaris machines. The replication works fine when I INSERT or DELETE but does not perform my UPDATE statements. I have used 'mysqlbinlog' on the relay log on one of the slaves and the UPDATE statements are being sent to the slaves but they are not being executed. No errors have been generated to the error log and no errors are shown when a SHOW SLAVE STATUS is run. The slaves use replicate-do-table directives in the config file but the tables that I am trying to update are listed and I can INSERT into and DELETE from these tables that I am trying to update. The UPDATE succeeds on the master. All of my tables are INNODB type. How-To-Repeat: When I run an UPDATE statement on my master database on replicated tables it is not replicated to the slaves (even though the statement is replicated to the relay log on the slave servers). Fix: Unknown Submitter-Id: miguelryan Originator:MySQL admin account Organization: freelance consultant MySQL support: none Synopsis: Replication in v4.0.2 not doing UPDATE Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.2-alpha (Source distribution) Environment: System: SunOS ops-dev5 5.6 Generic_105181-28 sun4u sparc SUNW,UltraSPARC-IIi-cEngine Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1623504 Aug 24 2001 /lib/libc.a lrwxrwxrwx 1 root root 11 Feb 9 2000 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024888 Aug 24 2001 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1623504 Aug 24 2001 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Feb 9 2000 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024888 Aug 24 2001 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-innodb CC=gcc 'CFLAGS=-O3 -fno-omit-frame-pointer' 'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' CXX=gcc Perl: This is perl, version 5.005_03 built for sun4-solaris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
synopsis of the problem (one line)
Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:FR/I/*KO_TBD// Admin Apache Organization: organization of RHODIA F.TOUITOU (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.23.49 (Source distribution) Environment: System: AIX ldousyp3 3 4 0041329A4C00 Some paths: /bin/perl /bin/make /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/powerpc-ibm-aix4.3.2.0/3.0.1/specs Configured with: ./configure --enable-threads=aix --disable-nls Thread model: single gcc version 3.0.1 Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 bin bin 19 19 mai 2001 /lib/libc.a - /usr/ccs/lib/libc.a lrwxrwxrwx 1 bin bin 19 19 mai 2001 /usr/lib/libc.a - /usr/ccs/lib/libc.a Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock --with-low-memory --with-mit-threads=yes --without-perl --enable-thread-safe-client --with-berkeley-db --enable-local-infile --with-innodb Perl: This is perl, version 5.005_03 built for aix-thread - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP4, Mysql , Apache and RedHat Linux 7.x, URGENT! HELP!!
Hi all, I have installed a clean RedHatLinux 7.1, and 7.2, and have selected the PHP and mysqlshare components to install. I also have configured the httpd.conf to use the php modules with Override ALL option. But when I want to run any php, including the one that need to access the mysql server, only the source code are shown, but not exdecute the PHP. Rather I update the apache and PHP or not, still the same result. How to configure the httpd.conf or php.ini or anything else to make the PHP run? Or anyone who have experience on running PHP in RedHat Linux 7.x?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Server cluster?
can MySQL run as cluster under the platform of IBM AS/400 also?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Server cluster?
Can MySQL or PostgreSQL build up a cluster for failover or improve the database performance just like Oracle or Sybase ones? If can, where we can get such setup documentations? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Easy way to insert data in MySQL DB
Hi, Thx for all your help, I can export the tables in exportSQL scripts. But how to make the procedure of inserting data more easy instead of using mysql daemon od MySQL GUI?? Both of them need to use Sql language to insert data. Any software or tools like Microsoft Access or Lotus Approach which can make ppl who are not familar to MySQL can insert data very easy?? And they are free? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
migrate Microsoft Access Data into MySQL
How to migrate Microsoft .mdb Database into MySQL under Unix environment?? Who can tell me?? Any Doc?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: long query on php
Hi. I prefer doing like this $query = CREATE TABLE query ; $query .= tabledef for col a,; $query .= tabledef for col b,; $query .= lots more table defs,; $query .= welcome to line 400; You get the point i guess you can do same things many different ways in php:) /PM\ Alex Aulbach wrote: Yesterday, from Nathan: Since PHP ignores whitespace, I think this is also acceptable: PHP doesn't ignore whitespace in a quote. But your code is of course correct. $query = CREATE TABLE query tabledef for col a, tabledef for col b, lots more table defs, . welcome to line 400; Another way: (without test) $bladefault=24; $query = eoq; CREATE TABLE query ( idchar(2) default 24 # doublequotes work hugo char(2) default '24' # singlequotes work either bla char(2) default '$bladefault' # inline vars work ... ) eoq -- How is possible to pass a long query to MySQL server with php? I mean i.e a create table statement with more than 400 chars. Have I to use shorter create and then alter? Strings in PHP can be really, really long and are binary safe. Perhaps the problem is the editor? -- SSilk - Alexander Aulbach - Herbipolis/Frankonia Minoris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Problems with select
Hi. You forgot around temp example: mysqlselect datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00 and temperatura=15.8; Regards /PM\ Miguel Figueiredo wrote: Hello all, I have a (probably dumb) question. My table temperatura has two fields described bellow. mysql describe temperatura; +-+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | datetime | datetime | YES | | NULL| | | temperatura | float| YES | | NULL| | +-+--+--+-+-+---+ 2 rows in set (0.00 sec) When I try to select rows with floats, the result is an empty set. For example: mysql select datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00; +-+-+ | datetime| temperatura | +-+-+ | 2002-02-08 09:30:00 |15.8 | +-+-+ 1 row in set (0.00 sec) got one row :) mysqlselect datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00 and temperatura=15.8; Empty set (0.00 sec) -- same thing but with the temperatura value, gives me an empy set :( When I try to select another row but the temperatura field has an integer this is what happens: mysql select datetime,temperatura from temperatura where datetime=2002-01-25 09:40:00; +-+-+ | datetime| temperatura | +-+-+ | 2002-01-25 09:40:00 | 16 | +-+-+ 1 row in set (0.00 sec) --- got one row :) mysql select datetime,temperatura from temperatura where datetime=2002-01-25 09:40:00 and temperatura=16; +-+-+ | datetime| temperatura | +-+-+ | 2002-01-25 09:40:00 | 16 | +-+-+ 1 row in set (0.00 sec) -- It works now :/ Does anyone have an idea why this happens? How can I select float numbers? Best wishes, Miguel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: How to access MySql using Perl. Help
Hi. You have to install DBI::DBD module. Then in perl use Mysql; $dbh = Mysql-connect(undef,database,username,'password'); $sql = select blah from blah; $sth = $dbh-query($sql); @arr = $sth-fetchrow; for example if the result is more than one line you have to loop it Ofcoz there is other ways to do it i guess:) /PM\ Andy Cheng wrote: Hello, I am new to MySql. I have MySql installed in my Linux PC. How do I connect to MySql using Perl script and assign the result of a query to an array? Where could I find a sample code? Thank you. Andy _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Problems with select
Hi. You forgot around temp example: mysqlselect datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00 and temperatura=15.8; Regards /PM\ Miguel Figueiredo wrote: Hello all, I have a (probably dumb) question. My table temperatura has two fields described bellow. mysql describe temperatura; +-+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | datetime | datetime | YES | | NULL| | | temperatura | float| YES | | NULL| | +-+--+--+-+-+---+ 2 rows in set (0.00 sec) When I try to select rows with floats, the result is an empty set. For example: mysql select datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00; +-+-+ | datetime| temperatura | +-+-+ | 2002-02-08 09:30:00 |15.8 | +-+-+ 1 row in set (0.00 sec) got one row :) mysqlselect datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00 and temperatura=15.8; Empty set (0.00 sec) -- same thing but with the temperatura value, gives me an empy set :( When I try to select another row but the temperatura field has an integer this is what happens: mysql select datetime,temperatura from temperatura where datetime=2002-01-25 09:40:00; +-+-+ | datetime| temperatura | +-+-+ | 2002-01-25 09:40:00 | 16 | +-+-+ 1 row in set (0.00 sec) --- got one row :) mysql select datetime,temperatura from temperatura where datetime=2002-01-25 09:40:00 and temperatura=16; +-+-+ | datetime| temperatura | +-+-+ | 2002-01-25 09:40:00 | 16 | +-+-+ 1 row in set (0.00 sec) -- It works now :/ Does anyone have an idea why this happens? How can I select float numbers? Best wishes, Miguel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: How to access MySql using Perl. Help
Hi. You have to install DBI::DBD module. Then in perl use Mysql; $dbh = Mysql-connect(undef,database,username,'password'); $sql = select blah from blah; $sth = $dbh-query($sql); @arr = $sth-fetchrow; for example if the result is more than one line you have to loop it Ofcoz there is other ways to do it i guess:) /PM\ Andy Cheng wrote: Hello, I am new to MySql. I have MySql installed in my Linux PC. How do I connect to MySql using Perl script and assign the result of a query to an array? Where could I find a sample code? Thank you. Andy _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: long query on php
Hi. I prefer doing like this $query = CREATE TABLE query ; $query .= tabledef for col a,; $query .= tabledef for col b,; $query .= lots more table defs,; $query .= welcome to line 400; You get the point i guess you can do same things many different ways in php:) /PM\ Alex Aulbach wrote: Yesterday, from Nathan: Since PHP ignores whitespace, I think this is also acceptable: PHP doesn't ignore whitespace in a quote. But your code is of course correct. $query = CREATE TABLE query tabledef for col a, tabledef for col b, lots more table defs, . welcome to line 400; Another way: (without test) $bladefault=24; $query = eoq; CREATE TABLE query ( idchar(2) default 24 # doublequotes work hugo char(2) default '24' # singlequotes work either bla char(2) default '$bladefault' # inline vars work ... ) eoq -- How is possible to pass a long query to MySQL server with php? I mean i.e a create table statement with more than 400 chars. Have I to use shorter create and then alter? Strings in PHP can be really, really long and are binary safe. Perhaps the problem is the editor? -- SSilk - Alexander Aulbach - Herbipolis/Frankonia Minoris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
problem and question
hello, This is my problem and question: I installed mysql-3.23.36 when I installed the RedHat7.1, but now I want to install mysql-3.23.47 to the platform and I hope the two versions are both useful. How should I configure the parameters when I configure? I want mysql supporting the BDB tables as well,is that using '--with-berkeley-db=./bdb' enough? I'm waiting for your replying and thank you for that! admin [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: MySQL and 3NF
Hi. I would type museum: department or likewise in name field if its not so many it would work nicely. But thats just me:) /PM\ David S. Jackson wrote: Hi, I came across a relationship between entities that I hadn't counted on, and I'm trying to adjust my database tables to handle this new relationship. I need some help with visualizing and implementing this relationship into the database design. The database is for an inventory of contributions to be auctioned off for a Montessori school. Here are my tables: mysql show tables; +--+ | Tables in vmscatalog | +--+ | Category | | Contact | | Contributors | | Delivery | | Groups | | Item | | Volunteer| +--+ I've assumed that each contributor (business, individual, whatever) would have only one volunteer from the Montessori school that they would be dealing with. so I've got the following structure for the contributors table: mysql describe Contributors; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Contributor_ID | tinyint(3) | | PRI | 0 | auto_increment | | Name | varchar(100) | | | | | | Street_Address | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(5) | YES | | NULL| | | Zip| mediumint(8) | YES | | NULL| | | Contact_ID | tinyint(3) | YES | | NULL| | | Volunteer_ID | tinyint(3) | YES | | NULL| | ++--+--+-+-++ 8 rows in set (0.01 sec) Likewise, the Volunteer_ID ties in with a Volunteer table and a unique row in the volunteer table. Now I've got a situation where a large Museum actually has two people from the school each talking to different departments in the Museum, each donating a different set of gift certificates. So I have to figure out some way to let the contributors' table handle more than Contact_ID and more than one Volunteer_ID. Don't I want each Contact_ID field for each record to be a single discrete ID number? How would you guys handle this? TIA! -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= It's hard to get ivory in Africa, but in Alabama the Tuscaloosa. -- Groucho Marx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: (Sort of ) replication issue
Hi. I would do a simple read file,search database,input record script in perl and transfer the files using scp (ofcourse i asume this is in linux/unix) put it all under cronjob and it should work fine. On the other hand i am sure others have better ideas:) /PM\ David Shields wrote: Hi, I was wondering if any of you clever people out there can help me. I have a client with a problem I'm sure some of you have fixed in the past ... Client has 3 geographically separate sites, running same application (mine, of course) and generating data on MySQL database. Periodically (probably nightly), we want to push changes occurring on each database into each of other 2 sites. Data volumes are low-ish (est max 5Meg per database absolute worst case). Line cost is minimal (ADSL), and comms issues I can work out just fine. This strikes me as a version of the multi-mysql-database replication pattern, though sort of slow. Is replication setup acceptable, or are the downtimes where databases are separate an issue, and is this using a sledgehammer to crack a nut? The alternative that occurs to me (though it is a bit if a pain) is to change my code slightly so each DB update query is written to a transaction log at each site, and then manually apply each of these logs to the other 2 databases. I can do this fine, but it seems a bit, well, crude. Plus the overhead of protecting the logs, and of clearing them on successful completion. Am I missing something, or is there a better way ? Advice and recommendations solicited and welcomed. David. (By the way, Mysql 3.23.38, app in php 4.0.5) Magic words: sql, database, query, abracadabra, butterscotch, nibble, phong. (some of these are just *nice* words, you know ? ) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: (Sort of ) replication issue
Hi. Not only that you also sent it to the wrong person:) It was david that asked the question anyway i cc it to the list and him. sometime i will experiment with that also but time is to short:) Regards /PM\ Ed Lazor wrote: doh... I should go to bed... I just reread what I wrote and it dawned on me that I didn't really say anything you didn't already know... I do know that replication is possible and it's built into MySQL... I don't remember how I did it... but it wasn't that difficult... had to do with establishing one server as a master and the other two as slaves to the master server and there was a place where you could specify how often replication occurs. Between replication, the main server records logs and uses the logs to track what should be transferred to the clients... so only changed data gets replicated, which is very cool. Hope that helps. -Ed At 09:53 AM 1/28/2002 +0100, you wrote: Hi. I would do a simple read file,search database,input record script in perl and transfer the files using scp (ofcourse i asume this is in linux/unix) put it all under cronjob and it should work fine. On the other hand i am sure others have better ideas:) /PM\ David Shields wrote: Hi, I was wondering if any of you clever people out there can help me. I have a client with a problem I'm sure some of you have fixed in the past ... Client has 3 geographically separate sites, running same application (mine, of course) and generating data on MySQL database. Periodically (probably nightly), we want to push changes occurring on each database into each of other 2 sites. Data volumes are low-ish (est max 5Meg per database absolute worst case). Line cost is minimal (ADSL), and comms issues I can work out just fine. This strikes me as a version of the multi-mysql-database replication pattern, though sort of slow. Is replication setup acceptable, or are the downtimes where databases are separate an issue, and is this using a sledgehammer to crack a nut? The alternative that occurs to me (though it is a bit if a pain) is to change my code slightly so each DB update query is written to a transaction log at each site, and then manually apply each of these logs to the other 2 databases. I can do this fine, but it seems a bit, well, crude. Plus the overhead of protecting the logs, and of clearing them on successful completion. Am I missing something, or is there a better way ? Advice and recommendations solicited and welcomed. David. (By the way, Mysql 3.23.38, app in php 4.0.5) Magic words: sql, database, query, abracadabra, butterscotch, nibble, phong. (some of these are just *nice* words, you know ? ) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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]Can't run mysql client
Hi. Seems the paths are missing so try running locate mysql or find -name mysql from basedirectory. /PM\ Stephane Pinel wrote: I've installed mysql 3.23.47 (Binaries) on a Linux RedHat 7.0 box. Everything seems to be OK (the server runs) but I cannot connect to it using mysql client: When I enter mysql -u etc... I've got a bash: mysql: command not found Any idea ? Thanks -- Stéphane Pinel 39, Rue du Docteur Heulin 75017 Paris (FRANCE) 33 1 53 11 05 77 33 6 08 94 63 16 [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: Porting from MS SQL to MySQL
Jeremy Zawodny wrote: On Fri, Jan 18, 2002 at 03:16:15PM +0200, Markus Lervik wrote: Hello all! We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to know is, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? That's a bit strange. If the app is built with MySQL in mind, porting it should be very, very easy. But if they're going to build the app with MySQL in mind anyway, it doesn't make much sense to do so on a platform other than MySQL, does it? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 15 days, processed 362,696,624 queries (268/sec. avg) I agree. If they are a seriuos company they should build it after the customers wishes i.e if you want mysql the company should build it with mysql. For 18,000 euro i could build the system myself:) My two cents /PM\ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Porting from MS SQL to MySQL
SNIPSNIP:) I agree. If they are a seriuos company they should build it after the customers wishes i.e if you want mysql the company should build it with mysql. For 18,000 euro i could build the system myself:) My two cents /PM\ What about the customer who asks a car company to make the vehicle's tryes out of velvet? Would you go off in a huff if they refused and demand they do it? There are obviously issues here that we are not privy to; there *must* be logic behind the choice of SQLServer. Are they saying that mySQL isn't upto it? Ah but if i say i want a mysql server and costs for it they shouldnt say we can do it in mssql I think they should give me an estimate on the costs for what i want then i can discuss how to get the cost down Porting a DB takes more 'than a couple of hours'. What about the written procedures, the security mappings, the back up and recovery procs, the fallback arrangements, the testing etc. If you think E18k is a lot then ask for a detailed task plan with effort; find out what they are asking you to pay for. The DB was described as 'fairly complicated' whatever that may mean. Perhaps - and we are all guesing - there are remote data issues, views, stored procs, java and god knows what else that all needs to be integrated. Mysql has always filled my need (wich is with perl,java,php) Granted it would be better to know exactly what fairly complicated is Bottom line when you get a quote is find out what they want to do task by task and then cut it down from there. I have to agree with this but i dont think you should pay for getting a workdescription from them (ofcourse i dont know much about administrating papperwork i am a technichian) Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: date_format
Julian, The as field is just setting the name of the field or column. For example in PHP, if you don´t use the as somename, you must use array[2] (I just use 2 as an example) instead of array['datefield'] to get the information. Regards, Ingvar G Web accounting CRM us.logiledger.com - Original Message - From: julian haffegee [EMAIL PROTECTED] To: MySQL General List [EMAIL PROTECTED] Sent: Monday, December 31, 2001 2:55 PM Subject: Re: date_format Thanks this works the difference was the 'AS datefield' what is this bit doing? I searched for AS (in the SELECT description in my book) but it does not mention it; though I have seen it in examples. I like to understand why I use the code I do... I looked at http://www.mysql.com/ but can find no mention. Am I searching for the wrong thing? I searched for AS in the documentation thanks again Jules You may want to try: $result = mysql_query(SELECT title, description, url, author, date_format(datefield, '%M %D %Y') AS datefield FROM documents); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Annoying article about MySql
I was just reading an annoying article from a user who was moving from MySql to Postgres because of stability problems and because of problems restoring large MySql data-dumps. I checked his site and it does not have the appearance of a very heavy loaded site so I am not sure what he means by large. This is the article: http://webmasterbase.com/article/529 I was wondering what size of MySqldump file creates problems when importing the datadumps (is it equipment dependent - size of memory, speed of processor etc ??), also under what circumstances does MySql become unstable (how many concurrent users? what kind of queries etc) or is the stability question something that has changed alot with version 3.23 and is just not relevant any more? I used Oracle8i before (under very heavy load) and did not have any problems there. I am now using MySql under not so heavy load and have not yet had any problems with it, but just want to be prepared when and if they come along. I would be grateful to hear your opions and experiences, good or bad. Regards, Ingvar G. us.logiledger.com Web accounting CRM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Input from those of you with LARGE MySql databases
Thanks Richard and you others for good input concerning Annoying article, I myself have nothing but good experience from using the MySql database but reading articles like this one and another one on the same web talking about when you outgrow MySql, then you must change to the more advanced databases bla bla bla here it is: http://www.webmasterbase.com/article/554/84 makes one worry and I just started thinking what limit they are talking about if (AND ONLY IF) they are correct? Therefore I would love to hear some appraisals from the ones that consider themselves to have big database, (over 1 gig or even a lot more) and preferably a link to your site :) regards, Ingvar G. us.logiledger.com Web accounting CRM - Original Message - From: Richard S. Huntrods [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 08, 2001 5:24 PM Subject: Re: Annoying article about MySql Ingvar, I have not used mysql data dumps directly to restore my databases, but I take the mysqldump output and massage them a bit, then use them for restores. Basically, the mysqldump output is missing the use xxx command to change to the target database. I have a perl script that does that (and extracts the table defs to another file for documentation). I have database mysqldump outputs that were 15 megabytes (now 10 megs, as I removed some report storage from the system). I have restored my system from these dumps many, many times. I have 4 systems in use at present, a Sun E250 as the production server, two Sparc 20's as devel/test servers, and a W2K system as primary development. I have an automated backup running every day on the E250 (just a script to run mysqldump with crontab), and I FTP these to the devel machine and use them to rebuild the development databases every couple of days. I restore from the modified dump by: mysql -u root -p CurrentDbms.mysql where CurrentDbms.mysql is the output of a mysqldump with the use xxx (use database) command added as line 1. I have never had any problem. Now, perhaps 10-15 megs is not considered large. Well, we started in August with a 2 meg database, and we're adding users every day. We currently have 775 users, but may expand to over 1 before this time next year. I'll keep this list posted of any problems, but so far the performance has been exceptional. I chose MySQL based on industry hearsay about its speed. I was looking for anything at the time - as Oracle 8i has a known bug in the Solaris/Sparc install scripts that essentially prevent it from being installed by anyone except an Oracle certified DBA (turns out you have to interrupt the install, manually hack some config files, and then restart the install scripts). However, once I had downloaded and installed MySQL, I became a convert. MySQL is very robust, VERY fast, and has a very tiny footprint compared to other database products. I do miss nested sub-queries and all that, but I've found a good workaround by running the sub-query as a normal query and capturing the output to a file. Then I edit the file to perform the outer query (ususally some form of update). Sorry for the long (gradually off topic) reply - but I've had no problems with large mysqldump restores. Cheers, -Richard == quote I was just reading an annoying article from a user who was moving from MySql to Postgres because of stability problems and because of problems restoring large MySql data-dumps. I checked his site and it does not have the appearance of a very heavy loaded site so I am not sure what he means by large. This is the article: http://webmasterbase.com/article/529 I was wondering what size of MySqldump file creates problems when importing the datadumps (is it equipment dependent - size of memory, speed of processor etc ??), also under what circumstances does MySql become unstable (how many concurrent users? what kind of queries etc) or is the stability question something that has changed alot with version 3.23 and is just not relevant any more? I used Oracle8i before (under very heavy load) and did not have any problems there. I am now using MySql under not so heavy load and have not yet had any problems with it, but just want to be prepared when and if they come along. I would be grateful to hear your opions and experiences, good or bad. Regards, Ingvar G. us.logiledger.com Web accounting CRM == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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,
mysql problem
Dear support officer, The followings are generated by the mysqlbug script: SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: yuzh To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Greg Yu Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.39-max (Official MySQL-max binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.21 Distrib 3.23.39, for pc-solaris2.8 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.39-max Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 2 hours 11 min 0 sec Threads: 1 Questions: 22 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.003 Environment: machine, os, target, libraries (multiple lines) System: SunOS ictest 5.8 Generic_108529-12 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/lib/gcc-lib/i386-pc-solaris2.8/3.0.1/specs Configured with: ../configure --with-as=/usr/local/bin/as --with-ld=/usr/local/bin/ld Thread model: posix gcc version 3.0.1 Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1588040 Nov 17 07:10 /lib/libc.a lrwxrwxrwx 1 root root 11 Nov 20 11:48 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 943052 Nov 17 07:10 /lib/libc.so.1 -rw-r--r-- 1 root bin 1588040 Nov 17 07:10 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Nov 20 11:48 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 943052 Nov 17 07:10 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL-max binary' --with-extra-charsets=complex --with-server-suffix=-max --disable-shared --with-berkeley-db --with-innodb Perl: This is perl, version 5.005_03 built for i86pc-solaris 1. libaray file libncurses.so.5 is missing. yuzh@ictest[/usr/local/mysql/bin] 231# mysql_fix_privilege_tables This scripts updates the mysql.user, mysql.db, mysql.host and the mysql.func table to MySQL 3.22.14 and above. This is needed if you want to use the new GRANT functions or want to use the more secure passwords. If you get Access denied errors, you should run this script again and give the MySQL root user password as a argument! If your tables are already up to date or partially up to date you will get some warnings about 'Duplicated column name' or 'Table 'func' already exists'. You can safely ignore these! ld.so.1: /usr/local/mysql/bin/mysql: fatal: libncurses.so.5: open failed: No such file or directory Killed Creating Grant Alter and Index privileges if they don't exists You can ignore any Duplicate column errors ld.so.1: /usr/local/mysql/bin/mysql: fatal: libncurses.so.5: open failed: No such file or directory Killed Creating the new table and column privilege tables ld.so.1: /usr/local/mysql/bin/mysql: fatal: libncurses.so.5: open failed: No such file or directory Killed Changing name of columns_priv.Type - columns_priv.Column_priv You can ignore any errors from this ld.so.1: /usr/local/mysql/bin/mysql: fatal: libncurses.so.5: open failed: No such file or directory Killed Fixing the func table You can ignore any Duplicate column errors ld.so.1: /usr/local/mysql/bin/mysql: fatal: libncurses.so.5: open failed: No such file or directory Killed 2. Access denied for user defined in mysql group: yuzh@ictest[/home/yuzh] 213# mysqlshow mysql mysqlshow: Access denied for user: '@localhost' to database 'mysql' yuzh@ictest[/etc/rc3.d] 249# mysqlshow mysqlshow: Access denied for user: 'root@localhost' (Using password: NO) Please tell me how to fix the about problems. Thanks! -- Regards Greg Yu -- Zhi Huan Yu
Converting MsSQL7 to MySQL
I am working on a project for a client that got roped into MsSQL7 by her last hosting company. I am looking for a good way to convert a MsSQL7 Database to MySQL. Anyone know of a piece of software that will convert the Database files or atleast suck from one database to the another. Thanks in advance. Sincerely, James Ashton,President Global Internet Tech, Inc. http://www.gitflorida.com 407-367-2165 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: permissions, Help for Newbie, Nicole
Hi Nicole, I guess the first issue is to be certain that you have the correct password and a MySQL account set up. If you have a shell account try something like mysql -u[your user name] -p[your password]. Sometimes ISP's will use different passwords for MySQL than the standard log in, we often do. Aside from that, I just can't help but mention the security risks PHP nuke has. We looked at it but finally settled on eZ publish. In fact we liked it so much we are now a certified eZ publish host. Double check your MySQL user and password and try logging in through the shell, or PHPadmin (you may want to check that out too) or a third party client running locally like netadmin if your ISP allows it. Best regards, -Eric Eric C Williams [EMAIL PROTECTED] E-BUILDERS, LLC CERTIFIED MIVA BUSINESS PARTNER CERTIFIED eZ publish HOST -- HTTP://E-PORTALBUILDER.COM HTTP://E-BUILDERS.COM HTTP://SECURE-SHOPS.NET TELNET|FTP|CGI|SSL|MIVA|PHP|SSH|SSI|PERL|PHP4|MySQL|2.5 Gbps network| -Original Message- From: Nicole Lancaster [mailto:[EMAIL PROTECTED]] Sent: Monday, July 30, 2001 8:37 PM To: [EMAIL PROTECTED] Subject: permissions, Help for Newbie, Nicole Hello, First of all, let me say I am a complete novice when it comes to PHP and MySQL. I am trying to install PHPNuke and I get this error when I try to access it: Warning: MySQL Connection Failed: Access denied for user: 'me@localhost' (Using password: YES) in /home/www/me/nuke/html/mainfile.php on line 33 Unable to select database Line 33 on the main php file is were it calls for access to the data base. My web host says that it is my problem with the program (which of course they do not provide support for). But it seems to me it's their problem, an access issue. My guess would be that they need to set up access/permission for me. Please inform me if my thinking is wrong here. If so, would this script be what I should recommend that they try?? mysql GRANT SELECT, INSERT, UPDATE ON client_db.* TO db_user@localhost IDENTIFIED BY 'password'; Thanks for your time, Nicole - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
EO - New FREE web development engine
Hello, I'm contacting you because we have recently released a new web development platform, EO, that I would like for you to try out for use in building dynamic sites. EO is a portable, flexible yet robust system that you can use to quickly build interactive sites, plus it has features that protect you, the web developer. The Engine behind EO has been in development since 1996 and has been used to build simple to complex systems in many industries including retail, automotive, financial, marine and more. BEST OF ALL IT'S FREE! Please visit http://www.eodeveloper.com/eo/eo.exe/eofree?01 to find out more and download your FREE copy. Thank you! Team EO http://www.eodeveloper.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
socket questions
Hi, I recently had some questions about converting files and haven't had the chance to work further with it or thank everyone for the help. So thanks a lot.I found that my application needed to have a dedicated MySQL installation. There is another version of MySQL running on this machine (RedHat 6.2 MySQL-3.23.6 and 3.23.8). I got it compiled correctly and configured it for a different port and owner. The daemon started. In the configuration options I set a new name for the socket mysql-lpmo.sock . I was thinking that the socket would be automatically created, but that doesn't appear to be the case, Does anyone have any information on the correct procedure for doing this? Thanks Very Much, -Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: specifying a delimiter
Hi Paul, Thanks for the help. I was hoping to accomplish this with MySQl, but in the end a script works just as good and this is just the first of several I am using. For what it's worth I ended up using sed. Here is is if somone else could use it: sed s/ /|/g ^This is a tab, not spaces.. I am now confronted with another problem. I need to also provide a CSV version. I need to use , seperated and protected text. I have a copy of phpMyAdmin that will export to this format, but I need to use this at the shell. I haven't yet dug into the script to se if this done with PHP or MySQL. I am thinking it is probably done with PHP. I noticed a mention of MySQL being able to set a delim in the docs but alas they never actually say how. Finally, I considered using awk for this but I am running into a problem there as well since 's are used to protect text. For instance awk '{print $1,$2} would print the fields correctly if not for the issue. A shove in the right direction would sure be appreciated. Thanks, -Eric -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 02, 2001 9:38 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: specifying a delimiter At 9:11 PM -0500 5/2/01, Paul DuBois wrote: At 10:01 AM -0500 5/2/01, [EMAIL PROTECTED] wrote: Hi, Being new to MySQL, I am having a problem getting output from it to the shell that is formatted the way I need. I need a table to be written to a file with a delimiter. something like : #!/bin/sh mysql -uroot -ptest database sql Then I run that script like this: ./script.sh output.txt Any delimiter is ok except for tab. Is this possible? Thanks, in advance, Not with mysql by itself. You can use sed or tr to change the tab in mysql's output to something else. The syntax for tr varies among systems, but on one of my systems (RedHat), both of these work: mysql sql | tr '\t' : mysql sql | tr '\011' : -- Paul DuBois, [EMAIL PROTECTED] Er, I should have been more specific and said both of these work to change tabs to colons. Substitute whatever other character you wish. Sorry. -- Paul DuBois, [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
specifying a delimiter
Hi, Being new to MySQL, I am having a problem getting output from it to the shell that is formatted the way I need. I need a table to be written to a file with a delimiter. something like : #!/bin/sh mysql -uroot -ptest database sql Then I run that script like this: ./script.sh output.txt Any delimiter is ok except for tab. Is this possible? Thanks, in advance, -Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php