Re: PHP or Perl?
In the last episode (Aug 15), Mark Healey said: As part of my own learning mysql project I'm planning to build databases for all my books and DVD's. Stephen Hawking is probably a better typist than I am so I plan to use barcodes to get the info ,taking the information from various web sites. People tell me that PHP is THE way to do database work with mysql. The thing is, I'm familiar with Perl and it has all kinds of neat string manipulation stuff and LWP. The best way is with C, calling the libmysqlclient functions directly, of course :) Once you decide to use an interpreted language, just pick the one you're most comfortable with. PHP is THE way for people that know PHP. If you like Perl instead, use Perl. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: querie assitance
- Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Rob Yelvington [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 15, 2003 4:10 PM Subject: Re: querie assitance At 15:52 -0500 8/15/03, Rob Yelvington wrote: I need some help with a query. I have two tables in one data base that both have a SSN field. The ssn field in one table contains slashes and the ssn field in the other does not. I know how to use CONCAT() and RIGHT(),MID(), and LEFT() on one of the ssn fields to obtain a result with digits only. But, I need to obtain a result set that includes a field from each table matched by ssn with one field from each. For example, let's say that table 'one' looks like this: '111/22/','John Q Public' Table 'two' looks like this: '11122333','somecode' What I'd like to achieve is a result set of: '11122','John Q Public','somecode' I guess what's throughing me off is using CONCAT() with the other substring items. Can this be done with one query? Mucho appreciation for any advice or assistance. Thanks! ~Rob For the table that has the field with dashes, sounds like you want to use REPLACE(ssn,'/','') to remove the slashes. That'd probably be simpler than what it sounds like you're doing now. Without seeing your original query, it's difficult to know for sure, but I'd guess you'll want to do something like this: SELECT whatever-fields-you-want FROM t1, t2 WHERE t1.ssn = REPLACE(t2.ssn,'/','') ... -- Paul DuBois, Senior Technical Writer That's exactly what the issue was, replace() being much simpler than what I was originally trying to do...I haven't used replace() before...have read about it and just shoved it to the back of line! The final query ended up looking like this: mysql select a,b,table1.c from table1,table2 where table1.c = replace(table2.c,'/',''); Thanks so much, Mr. Dubois! Respectfully yours, ~Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How many join do I need for a query on 3 table?
Hello again! I was able to extra the information from the first 2 tables yesterday using following query: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers inner join Orders USING (cust_id) WHERE customers.cust_id = 2 SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers inner join Orders ON Customers.cust_id=Order.cust_id WHERE customers.cust_id = 2 Right now I need credit card details from a third table; and that make the query more complicated for me. I' m not sure weather 2 inner joins could be used. I' ll appreciate any help. I have 2 tables: Customers and orders. The have following structures: CustomersOrders Payment cust_id Product Order_id Name PriceCredit_Card City cust_id Order_id Best Regards Babs
Re: Optimizing imports
Jackson Miller wrote: I need to have a web application be able to import large amounts of data (400,000 rows of 10 columns). I know how to script it and have it running in the background. However I want to know how I can optimize my insert statements to try to speed things up. For extreme speed http://www.mysql.com/doc/en/LOAD_DATA.html Be aware of some sharp edges with replication or foreign keys. Will it help if I insert multiple rows at a time? If so, is there a magic number or range? The same would also go for updates. Depending on your exact needs, consider brutal overwrites: LOAD DATA CONCURRENT INFILE '...' REPLACE INTO TABLE Replaces 1 rows within 3.5 s into Innodb running MySQL-max-4.10 and WinXP on Celeron /400MHz/ for me. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I change the date format...
Hello Is there anyway to change the default date format (possibly in a .ini or .cfg file) from -MM-DD to another format ? (ie, mm/dd/ or any user-defined format). The useage that I'm looking at is to import a CSV file into a table (using Load data infile command) where dates are stored in mm/dd/ format and can change based on the source of the data. I can import the date fields into a char field and then convert it but would like to save the time to transform and copy contents into another table. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I change the date format...
Karam Chand [EMAIL PROTECTED] wrote: Is there anyway to change the default date format (possibly in a .ini or .cfg file) from -MM-DD to another format ? (ie, mm/dd/ or any user-defined format). No, You can't. The useage that I'm looking at is to import a CSV file into a table (using Load data infile command) where dates are stored in mm/dd/ format and can change based on the source of the data. I can import the date fields into a char field and then convert it but would like to save the time to transform and copy contents into another table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Windows 98 shutdown problem
Hi All! Recently I installed mysql on a win98. Some 2 days after the installation the system started experiencing a problem shutting down. When shutting down the hard-disk kept spinning sometimes as long as 10 minutes. The same problem was encountered on another win98 computer. The system is working well with win2000 though. Kindly help on this. TIA Shailesh ___ Meet your old school or college friends from 1 Million + database... Click here to reunite www.batchmates.com/rediff.asp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many join do I need for a query on 3 table?
* B. Fongo I was able to extra the information from the first 2 tables yesterday using following query: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers inner join Orders USING (cust_id) WHERE customers.cust_id = 2 SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers inner join Orders ON Customers.cust_id=Order.cust_id WHERE customers.cust_id = 2 That was two queries, and they both have a typo preventing them from working... are you testing us? ;) Right now I need credit card details from a third table; and that make the query more complicated for me. I' m not sure weather 2 inner joins could be used. I' ll appreciate any help. I can try. :) I have 2 tables: Customers and orders. What about the third table you just mentioned...? The have following structures: CustomersOrders Payment cust_id Product Order_id Name PriceCredit_Card City cust_id Order_id This was not very readable on my screen... When you want to show the structure of a table, use DESC Customers; or even better: SHOW CREATE TABLE Customers;. The last one will also include any index definitions, which is often relevant when you ask questions about query performance on this list. Just a friendly advice. :) I think the above means something like this: Customers: cust_id INT PRIMARY KEY, Name VARCHAR, City VARCHAR Orders: Order_id INT PRIMARY KEY, cust_id INT Product VARCHAR, Price INT, Payment: Order_id INT, Credit_Card VARCHAR (You can only have one product per order, and only full payments are allowed, and you should record the date of order and date of payment, but that's not an issue here, I suppose.) Ok, prepare for a 5 minute MySQL joining crash course. :) We will build on your original query, but I will reformat it a little, just to make it easier to read. The original, two table query with INNER JOIN and ON, reformatted: SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id WHERE Customers.cust_id = 2 Then we can expand it with another table: SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price, Payment.Credit_Card // new line FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id INNER JOIN Payment ON // new line Payment.Order_id=Orders.Order_id // new line WHERE Customers.cust_id = 2 Now we can simplyfy this by removing some table names we don't need (because the column name is unique), and change the ON to USING: SELECT Name, City, Product, Price, Credit_Card FROM Customers INNER JOIN Orders USING(cust_id) INNER JOIN Payment USING(Order_id) WHERE Customers.cust_id = 2 Note that the order of the tables are important when using USING(): the previous table is joined with this table USING the named column(s). For instance, you could not have joined Payment before Orders in the above statement, because ... Orders USING(cust_id) then would have referred to the Payment table, which does not have any cust_id column. We could have used the shortcut alias , instead of INNER JOIN, but then we can not use ON or USING, and must move the join conditions to the WHERE clause: SELECT Name, City, Product, Price, Credit_Card FROM Customers, Orders ,Payment WHERE Customers.cust_id=Orders.cust_id AND Payment.Order_id=Orders.Order_id Customers.cust_id = 2 We could also have used NATURAL JOIN in this case, which is the same as USING naming all columns with the same name in the two joining tables: SELECT Name, City, Product, Price, Credit_Card FROM Customers NATURAL JOIN Orders NATURAL JOIN Payment WHERE Customers.cust_id = 2 The note about the order of the tables when using USING() also goes for NATURAL JOIN. Regarding the question in the subject: when joining 'n' tables, you need 'n-1' JOINS. The JOIN is always placed between two table names, and the first table name is always preceeded with FROM. Remember that , when used between table names in a SELECT is an alias for INNER JOIN. If you wanted to also include customers which have not payed, you would use LEFT JOIN: SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price, IF(Credit_Card,Credit_Card,'*no pay*') // changed line FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id LEFT JOIN Payment ON // changed line Payment.Order_id=Orders.Order_id WHERE Customers.City = Hamburg // changed line ...or simply: SELECT Name, City,
big big integer
Hi, It is noted in the MySQL manual that unsigned integers bigger than 9223372036854775807 (63 bits) shouldn't be used itnarithmetic operations since signed BIGINT/DOUBLE is used in arithmetic functions. Can we somehow tell MySQL (probably during compilation) to use unsigned BIGINT/DOUBLE for arithmetic functions? (I know MySQL wouldn't be able to operate on any signed number but that is a price I would be willing to pay) Doruk -- FISEK INSTITUTE - http://www.fisek.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Permission and ODBC
Hello I am using SQLyog. I want to manage user permissions down to a field level which SQLyog does. Any DDL or DML statements that I attempt to execute within SQLyog support the defined user permissions. However, when I use MySQLCC the persmissions are totally ignored. This is also true when I attempt to access the database through ODBC using a number of third-party tools. My intent is to offer my clients the ability to access their data through ODBC with the option to restrict a specific set of users to sensitive data (Financials or salary amount column for Payroll) using a tool such as SQLyog. Is this possible that one tool has the permission and another not? karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I change the date format...
- Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 16, 2003 11:48 AM Subject: Re: Can I change the date format... Karam Chand [EMAIL PROTECTED] wrote: Is there anyway to change the default date format (possibly in a .ini or .cfg file) from -MM-DD to another format ? (ie, mm/dd/ or any user-defined format). No, You can't. The useage that I'm looking at is to import a CSV file into a table (using Load data infile command) where dates are stored in mm/dd/ format and can change based on the source of the data. I can import the date fields into a char field and then convert it but would like to save the time to transform and copy contents into another table. Victoria's answer is correct. But you can achieve this by using the DATE_FORMAT() and CONCAT() MySQL functions in a SQL query. This would be a manual process. Or as you mention you could just do the manual conversion at the other end. For example, - write SQL queries that will export the table data in CVS format using the DATE_FORMAT and CONCAT() functions - or, add extra string/char columns to your tables which store the date in the required format temporarily for the export / import process. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.510 / Virus Database: 307 - Release Date: 14/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1.1
When will 4.1.1 be officially available in a packaged download? Just curious. Thx, Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ranking student grade ? with subquery/subselect?
Hello, I am a mySQL newbie here and have some problem defining the mySQL 4.0.14 or 3.23 SQL to get student grade ranking where tied grade have the same rank. I used to set it through MS Access 2002 and use this kind of query: SELECT nilai.studentNIS, nilai.studenttestmark, (SELECT COUNT(*) FROM tblStudentGrades WHERE [studenttestmark][Nilai].[studenttestmark];)+1 AS NomorUrut FROM tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC; I've been looking around mySQL documentation and read that subquery can be redefined as INNER JOIN or using two SQL statement via variable? I have no idea on the basics of how to set it out though :( Could one of you please help give a me a sample on how this kind of query should be done on mySQL? Is it possible to do it in single line? And without having to use PHP/Perl scripts? Or maybe I should have approach it differently? Thanks in Advance Regards, Marcello s.
Formatted double value...
Greetings I have a column with double values like - .01 .02 .03 Well...its sounds ridiculous but i do have data like that :) I am using C API to get the resultset, but the data is resturned as ( also with mysql client which uses C API ). |1e-006 | |2e-006 | |3e-006 | |4e-006 | |5e-006 | |6e-006 | | 8 | |8e-006 | Is there any way wherein I can get data like .01, .02 etc. Thanks in advance. karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
images on database
Dear Friends I has images saved on an mysql table as an blob field, but i could not recreate the image as file by selecting an specific record of this field any ideia Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: How many join do I need for a query on 3 table?
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me solve my problem. I'll turn to MySQL doc after getting through this pressing project. Thanks a lot Roger! Babale -Ursprüngliche Nachricht- Von: Roger Baklund [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 16. August 2003 14:32 An: [EMAIL PROTECTED] Cc: B. Fongo Betreff: Re: How many join do I need for a query on 3 table? * B. Fongo I was able to extra the information from the first 2 tables yesterday using following query: SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers inner join Orders USING (cust_id) WHERE customers.cust_id = 2 SELECT Customers.Name, Customers.City, Orders.Product, Order.Price FROM Customers inner join Orders ON Customers.cust_id=Order.cust_id WHERE customers.cust_id = 2 That was two queries, and they both have a typo preventing them from working... are you testing us? ;) Right now I need credit card details from a third table; and that make the query more complicated for me. I' m not sure weather 2 inner joins could be used. I' ll appreciate any help. I can try. :) I have 2 tables: Customers and orders. What about the third table you just mentioned...? The have following structures: CustomersOrders Payment cust_id Product Order_id Name PriceCredit_Card City cust_id Order_id This was not very readable on my screen... When you want to show the structure of a table, use DESC Customers; or even better: SHOW CREATE TABLE Customers;. The last one will also include any index definitions, which is often relevant when you ask questions about query performance on this list. Just a friendly advice. :) I think the above means something like this: Customers: cust_id INT PRIMARY KEY, Name VARCHAR, City VARCHAR Orders: Order_id INT PRIMARY KEY, cust_id INT Product VARCHAR, Price INT, Payment: Order_id INT, Credit_Card VARCHAR (You can only have one product per order, and only full payments are allowed, and you should record the date of order and date of payment, but that's not an issue here, I suppose.) Ok, prepare for a 5 minute MySQL joining crash course. :) We will build on your original query, but I will reformat it a little, just to make it easier to read. The original, two table query with INNER JOIN and ON, reformatted: SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id WHERE Customers.cust_id = 2 Then we can expand it with another table: SELECT Customers.Name, Customers.City, Orders.Product, Orders.Price, Payment.Credit_Card // new line FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id INNER JOIN Payment ON // new line Payment.Order_id=Orders.Order_id // new line WHERE Customers.cust_id = 2 Now we can simplyfy this by removing some table names we don't need (because the column name is unique), and change the ON to USING: SELECT Name, City, Product, Price, Credit_Card FROM Customers INNER JOIN Orders USING(cust_id) INNER JOIN Payment USING(Order_id) WHERE Customers.cust_id = 2 Note that the order of the tables are important when using USING(): the previous table is joined with this table USING the named column(s). For instance, you could not have joined Payment before Orders in the above statement, because ... Orders USING(cust_id) then would have referred to the Payment table, which does not have any cust_id column. We could have used the shortcut alias , instead of INNER JOIN, but then we can not use ON or USING, and must move the join conditions to the WHERE clause: SELECT Name, City, Product, Price, Credit_Card FROM Customers, Orders ,Payment WHERE Customers.cust_id=Orders.cust_id AND Payment.Order_id=Orders.Order_id Customers.cust_id = 2 We could also have used NATURAL JOIN in this case, which is the same as USING naming all columns with the same name in the two joining tables: SELECT Name, City, Product, Price, Credit_Card FROM Customers NATURAL JOIN Orders NATURAL JOIN Payment WHERE Customers.cust_id = 2 The note about the order of the tables when using USING() also goes for NATURAL JOIN. Regarding the question in the subject: when joining 'n' tables, you need 'n-1' JOINS. The JOIN is always placed between two table names, and the first table name is always preceeded with FROM. Remember that , when used between table names in a SELECT is an
Re: Formatted double value...
* Karam Chand I have a column with double values like - .01 .02 .03 Well...its sounds ridiculous but i do have data like that :) I am using C API to get the resultset, but the data is resturned as ( also with mysql client which uses C API ). |1e-006 | |2e-006 | |3e-006 | |4e-006 | |5e-006 | |6e-006 | | 8 | |8e-006 | Is there any way wherein I can get data like .01, .02 etc. Maybe the FORMAT() function will do what you want: URL: http://www.mysql.com/doc/en/Miscellaneous_functions.html#IDX1391 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Double entries
Hi,
I am running the latest MySql on a windows 200 machine. I also use the
control center gui to do all my work with. I have a Perl program that
parses online order data and then using Perl's DBI I write this data to the
database. Now, It all works fine except that each order and orders items
are written twice to the database. I have looked over my code but only see
one insert statement for order information and the ordered items. I guess I
need a fresh set of eyes to see where my error is.
I have included the code below with the database connection information
highlighted in red:
TIA
Trevor
#!/usr/local/bin/perl
#
# Main script
#
use strict;
use MivaOrder;
use Data::Dumper;
#Use Perl's Database Interface (DBI) with the NySQL module to connect the
Maverick database
use DBI;
my %attr = (PrintError = 1, RaiseError = 1);
my $dbname='maverick';
my $dbuser='root';
my $dbpass='';
my
$dbh=DBI-connect('DBI:mysql:database=maverick;host=localhost;port=3306',$d
buser,$dbpass, \%attr) || dieUnable to connect to database maverick on
localhost: $DBI::errstr\n;
my @new_orders;
#my $email = qw(c:\\maverick\\trevor_trial2.txt);
open(ORDER,$ARGV[0]) or die Error opening \$ARGV[0]\: $!\n;
my $order = undef;
while(ORDER) {
# Keep appending to the order string until we reach the end of the order.
unless(/^Site rating\.\.\: \/) {
$order .= $_ and next;
}
# We are done with the order.
my $obj = MivaOrder-new($order);
#print Dumper(\$obj);
push @new_orders,$obj;
$order = undef;
}
my $sth1 =$dbh-prepare(INSERT INTO
miva_retail_orders(wholesale_company,order_number,date,credit_card_type,ship
ped,shiptype,bill_name,bill_company,bill_addr1,bill_addr2,bill_city,bill_sta
te,bill_zip,bill_country,ship_name,ship_addr1,ship_addr2,ship_city,ship_stat
e,ship_zip,ship_country,email,bill_phone_number,bill_phone_number2,ship_phon
e_number,ship_phone_number2,sales_tax,shipping_amount,coupon_type,coupon_amo
unt,total) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?));
foreach my $new_order(@new_orders) {
# Enter the database and write the data
$sth1-execute($new_order-wholesale_company(),$new_order-order_number(),$n
ew_order-date(),$new_order-credit_card_type(),$new_order-shipped(),$new_o
rder-ship_type(),$new_order-bill_name(),$new_order-bill_company_name(),$n
ew_order-bill_to_street(),$new_order-bill_to_street2(),$new_order-bill_to
_city(),$new_order-bill_to_state(),$new_order-bill_to_zip(),$new_order-bi
ll_to_country(),$new_order-ship_name(),$new_order-ship_to_street(),$new_or
der-ship_to_street2(),$new_order-ship_to_city(),$new_order-ship_to_state(
),$new_order-ship_to_zip(),$new_order-ship_to_country(),$new_order-email_
address(),$new_order-bill_phone_number(),$new_order-bill_phone_number2(),$
new_order-ship_phone_number(),$new_order-ship_phone_number2(),$new_order-
sales_tax(),$new_order-shipping_amount(),$new_order-coupon_type(),$new_ord
er-coupon_amount(),$new_order-total());
}
$sth1-finish;
$dbh-disconnect;
#==
# Package MivaOrder
#--
package MivaOrder;
use strict;
use Carp;
use English;
use vars qw($AUTOLOAD);
my %fields = (
wholesale_company =undef,
order_number=undef,
date=undef,
bill_name =undef,
bill_email_address =undef,
bill_phone_number =undef,
bill_phone_number2 =undef,
bill_business_name =undef,
bill_to_street =undef,
bill_to_city=undef,
bill_to_state =undef,
bill_to_zip =undef,
bill_to_country =undef,
ship_name =undef,
ship_email_address =undef,
ship_phone_number =undef,
ship_phone_number2 =undef,
ship_business_name =undef,
ship_to_street =undef,
ship_to_city=undef,
ship_to_state =undef,
ship_to_zip =undef,
ship_to_country =undef,
code=undef,
name=undef,
quantity=undef,
price =undef,
shipping_method =undef,
shipping_amount =undef,
sales_tax =undef,
total =undef,
notes =undef,
coupon_type =undef,
coupon_amount =undef,
credit_card_type=undef,
COD
=undef
);
sub AUTOLOAD {
my ($self,$value) = @_;
$AUTOLOAD =~ /.*::(\w+)/;
$self-{$1} = $value if($value);
return $self-{$1};
}
sub new {
my ($that,$order) = @_;
croak Order file is undefined if(!$order);
RE: Secure Database Design Part II
Hi Firstly if your application is well designed then your data should not be accessible by the wrong users - if you make sure that there is no way the wrong supplier id can be allocated - put an extra 'are you sure this is correct' check, make it so that the user id can only be allocated once two people have checked it or something similar. Another option would be that there is no facility for viewing past quotes at all. If there is no facility at all you can give the application rights to insert data only - no update or select - adding another layer of security. If you do need the users to see some history then don't display the detail - no prices or part ids - whatever you can get away with. HTH Peter -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: 16 August 2003 05:05 To: Lefevre, Steven; [EMAIL PROTECTED] Subject: Re: Secure Database Design Part II Hi Steven, I believe I understand your concerns. I think the only way to reduce the risk of associating the wrong data is tripple checking it by different persons or even better make two tables which you can compare against each other. The second table only readable to you. This way you could always double check the correctness of the distrubutor table (as long as they are in sync ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 16 05:36Lefevre, Steven : Hey folks - Thanks to everyone who gave input to my concerns. Of course, we don't intend to have the mysql port open to the world. We will have Apache/PHP connect on a unix socket, or to another machine with a cross-cable on non-routeable IPs. But now I have another question. We are working on a web database to allow our suppliers to log on and submit information that they would otherwise fax, email, or phone to us. It would reduce work in our office, and reduce errors in duplication of our information. But, we are very concerned about security! We aren't worried so much about outside hackers as we are about legit users trying to gain access to information they shouldn't. Some of our suppliers are overseas and we think they have no qualms about trying to hack the system, knowing the stunts they have pulled in the past. It would be extremely difficult to pursue any problems legally, since it would be international, and the damage would be already done. So, here's my question. Good database design dictates that I normalize my tables. So, in this simplified example, we have a table of supplier quotes: supplier_id part_id quote_price quote_date All of our suppliers would be drawing from the same table, via php. I'm worried that good database design might be more susceptible to information 'spilling over' -- what if I make a simple mistake and put the wrong supplier_id with a new user's logon? That new user would see all the parts that belong to whatever company I mistakenly associate them with. I'm not so worried about, say, suppliers seeing sales data. All the php pages will be protected by Unix filesystem permissions, so I can be reasonably certain that only those belonging to the suppliers group will be able to execute supplier_*.php. Even if they do load some sales_*.php page, then the MySQL user permissions will stop them from actually seeing any data on the page. So there are two layers of security between sales and suppliers, for example. I would have to make two mistakes for them to have access to sales data. But, when all suppliers are accessing the same pages, it's up to my careful hands to make sure they are pulling only their records out of the table. If I make a mistake in a query, it might pull up other records, or even all records! Of course if I design it completely perfectly the first time, I don't have to worry about anything. But I'm not perfect and I don't make perfect things. So, I'm thinking I should violate good design principles, and setup identical tables for each supplier, salesperson, customer, etc. That way, since they share the same PHP pages, they aren't all pulling data from the same table. If there is any mixup in the query, the user doesn't have the MySQL permission to pull data from another suppliers table. Does this make sense? Steve Lefevre Network Administrator IMI International, Inc. 614.839.2500 -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compile mysql meet problem
Hi, On Sat, Aug 16, 2003 at 04:29:01AM +0200, albert wrote: Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# It seems like you are (compiling and) installing _from_ /usr/local/mysql and also _to_ /usr/local/mysql. You should compile and install from any directory you choose, but it shouldn't be the same as the directory you specify as destination with configure --prefix. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP or Perl?
At 12:17 AM 8/16/2003, you wrote: As part of my own learning mysql project I'm planning to build databases for all my books and DVD's. Stephen Hawking is probably a better typist than I am so I plan to use barcodes to get the info ,taking the information from various web sites. People tell me that PHP is THE way to do database work with mysql. The thing is, I'm familiar with Perl and it has all kinds of neat string manipulation stuff and LWP. Does PHP have comprable libraries (especially LWP) and how difficult is it to move from Perl. I hope I don't start a relegious war here I just want some advice. Mark, Perl is a much richer (larger) programming language than PHP. PHP is best suited for designing webpages whereas Perl is a more universal tool (you can use it for just about anything). The only advantage of PHP is that it runs faster than Perl which may be important if a lot of people are accessing your web page. But if you are running this application locally on your hard disk (not as a web page), then probably C++ will give you the fastest possible application. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Better query method?
John, I was intrigued with the given solution so I tried this and here's a version I found to work: Table x1 has fields uid, uname, pid (parent's id) select parent.uname, count( child.uname) as ChildCount from x1 as parent left join x1 as child on ( parent.uID=child.pid) group by parent.uName order by ChildCount desc; Regards, Andy. John Macon wrote: Hi all, Long time reader, first time poster, I hope that I get this right. I am setting up a database that reflects a relationship between two records without using the primary key as the tie between the two. I need to make a list of these based on the number of children each has, and I am having a problem with the query set up. Currently I have the query set up to run at number of users +1, and that doesn't seem to be very efficient. The records are something like this. user ID numberName Parent 1John 0 2Don 1 3Joe 1 4Jack 2 5Jill 4 6Jane 4 okay, now I need to be able to make a list of the names, with the number of children that they have, then sort them in order of how many children they have. Basically, I want to make a list that has in numerical order the names of the parent, and how many children they have. I hope that it makes sense the way that I have described it. I have it working, but if my list of parents and children gets long, then it will kill me i think. The above list would display as NameNumber of Children John2 Jack2 Don 1 Joe 0 Jill 0 Jane0 The way that I have it working right now, is I run a query to find out the number of records, then I run a query based on the first user (1), find all the children that belong to that user and store it in an array, then another query based on the second record to discover how many children that user has, so on and so forth until the end of the table is reached. Then i sort the array based on the number of children, the person with the most children goes at the top, and it descends from there. As you can see, I am running a query to find out the number of records, then a query based on each record. If my table gets to 50,000 users then that will be a nightmare I think. Any help to point me in the right direction would be appreciated, I am using MySql 4.x, my local testing server is a Win2k machine, but my web server is a Linux box. I am mainly just looking for a concept for the better query, not the actual code to write it. I just can't seem to think of a better way to structure it. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double entries
Trevor,
Why not try simplify it to 2 fields and remove most of the validation
code? If that works, then you can binary add/delete code until it fails.
BTW 'Red' I guess only works on certain mail clients, but not mine, so
sorry if I missed something obvious.
Regards,
Andy.
Trevor Morrison wrote:
Hi,
I am running the latest MySql on a windows 200 machine. I also use the
control center gui to do all my work with. I have a Perl program that
parses online order data and then using Perl's DBI I write this data to the
database. Now, It all works fine except that each order and orders items
are written twice to the database. I have looked over my code but only see
one insert statement for order information and the ordered items. I guess I
need a fresh set of eyes to see where my error is.
I have included the code below with the database connection information
highlighted in red:
TIA
Trevor
#!/usr/local/bin/perl
#
# Main script
#
use strict;
use MivaOrder;
use Data::Dumper;
#Use Perl's Database Interface (DBI) with the NySQL module to connect the
Maverick database
use DBI;
my %attr = (PrintError = 1, RaiseError = 1);
my $dbname='maverick';
my $dbuser='root';
my $dbpass='';
my
$dbh=DBI-connect('DBI:mysql:database=maverick;host=localhost;port=3306',$d
buser,$dbpass, \%attr) || dieUnable to connect to database maverick on
localhost: $DBI::errstr\n;
my @new_orders;
#my $email = qw(c:\\maverick\\trevor_trial2.txt);
open(ORDER,$ARGV[0]) or die Error opening \$ARGV[0]\: $!\n;
my $order = undef;
while(ORDER) {
# Keep appending to the order string until we reach the end of the order.
unless(/^Site rating\.\.\: \/) {
$order .= $_ and next;
}
# We are done with the order.
my $obj = MivaOrder-new($order);
#print Dumper(\$obj);
push @new_orders,$obj;
$order = undef;
}
my $sth1 =$dbh-prepare(INSERT INTO
miva_retail_orders(wholesale_company,order_number,date,credit_card_type,ship
ped,shiptype,bill_name,bill_company,bill_addr1,bill_addr2,bill_city,bill_sta
te,bill_zip,bill_country,ship_name,ship_addr1,ship_addr2,ship_city,ship_stat
e,ship_zip,ship_country,email,bill_phone_number,bill_phone_number2,ship_phon
e_number,ship_phone_number2,sales_tax,shipping_amount,coupon_type,coupon_amo
unt,total) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?));
foreach my $new_order(@new_orders) {
# Enter the database and write the data
$sth1-execute($new_order-wholesale_company(),$new_order-order_number(),$n
ew_order-date(),$new_order-credit_card_type(),$new_order-shipped(),$new_o
rder-ship_type(),$new_order-bill_name(),$new_order-bill_company_name(),$n
ew_order-bill_to_street(),$new_order-bill_to_street2(),$new_order-bill_to
_city(),$new_order-bill_to_state(),$new_order-bill_to_zip(),$new_order-bi
ll_to_country(),$new_order-ship_name(),$new_order-ship_to_street(),$new_or
der-ship_to_street2(),$new_order-ship_to_city(),$new_order-ship_to_state(
),$new_order-ship_to_zip(),$new_order-ship_to_country(),$new_order-email_
address(),$new_order-bill_phone_number(),$new_order-bill_phone_number2(),$
new_order-ship_phone_number(),$new_order-ship_phone_number2(),$new_order-
sales_tax(),$new_order-shipping_amount(),$new_order-coupon_type(),$new_ord
er-coupon_amount(),$new_order-total());
}
$sth1-finish;
$dbh-disconnect;
#==
# Package MivaOrder
#--
package MivaOrder;
use strict;
use Carp;
use English;
use vars qw($AUTOLOAD);
my %fields = (
wholesale_company =undef,
order_number=undef,
date=undef,
bill_name =undef,
bill_email_address =undef,
bill_phone_number =undef,
bill_phone_number2 =undef,
bill_business_name =undef,
bill_to_street =undef,
bill_to_city=undef,
bill_to_state =undef,
bill_to_zip =undef,
bill_to_country =undef,
ship_name =undef,
ship_email_address =undef,
ship_phone_number =undef,
ship_phone_number2 =undef,
ship_business_name =undef,
ship_to_street =undef,
ship_to_city=undef,
ship_to_state =undef,
ship_to_zip =undef,
ship_to_country =undef,
code=undef,
name=undef,
quantity=undef,
price =undef,
shipping_method =undef,
shipping_amount =undef,
sales_tax =undef,
total =undef,
notes =undef,
coupon_type =undef,
coupon_amount
Re: MySQL to syslog
Correct me if I'm wrong, but won't this cause it to no longer be a real-time solution? The machine I'm running MySQL on is fairly vulnerable to attack (which, for the time being, cannot be changed...although I have secured it as much as is possible), which is why I'm logging everything remotely. If the solution is not real-time, theoretically someone can gain unauthorized access and remove traces of their entry before the logs are batched up and sent. This is what I want to prevent. Stephen Touset Harald Fuchs wrote: In article [EMAIL PROTECTED], Stephen Touset [EMAIL PROTECTED] writes: However, I have a different need. I'm setting up several machines on a network, and wish for them all to send logs through stunneled syslog connections to a central logging server, where I can run logcheck et al to generate reports of system abuse and ensure that all services are running smoothly. However, with MySQL logging to it's own file, this presents a problem to me. Some solutions I can see are simply rsync'ing the logs over daily or mounting the directory over NFS and copying the logs. However, both of these require me to set up new services, and/or change already-existing firewall rules, and write new scripts to do the fetching/retrieval, all of which takes time and effort--not to mention, needlessly complicates the system. How about making the error log a named pipe and reading from it with logger? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP or Perl?
Hi Mark I can only tell you about my experience from the Perl side of things. The integration to the database is great and the amount of things you can do with the data once you get it in the script is the biggest bonus. My feeling is that a combination of the two would probably be the best. Any pages that just list your records, I would build in PHP. Any that involve handling the data before output, I would use Perl. Mike - Original Message - From: Mark Healey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: August 16, 2003 1:17 AM Subject: PHP or Perl? As part of my own learning mysql project I'm planning to build databases for all my books and DVD's. Stephen Hawking is probably a better typist than I am so I plan to use barcodes to get the info ,taking the information from various web sites. People tell me that PHP is THE way to do database work with mysql. The thing is, I'm familiar with Perl and it has all kinds of neat string manipulation stuff and LWP. Does PHP have comprable libraries (especially LWP) and how difficult is it to move from Perl. I hope I don't start a relegious war here I just want some advice. Mark Healey [EMAIL PROTECTED] This account is only for lists to which I've subscribed. Any spammers invite the worst revenge I think I can get away with. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table is read only after copying table files to new server
I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql insert into test2 values(3); Query OK, 1 row affected (0.00 sec) The files corresponding to test and test2 have the same permissions and ownership, so that's not it: -rw-rw1 mysqlmysql 20 Aug 15 21:22 test.MYD -rw-rw1 mysqlmysql1024 Aug 15 21:22 test.MYI -rw-rw1 mysqlmysql8556 Aug 15 21:22 test.frm -rw-rw1 mysqlmysql 5 Aug 16 19:13 test2.MYD -rw-rw1 mysqlmysql1024 Aug 16 19:13 test2.MYI -rw-rw1 mysqlmysql8556 Aug 16 19:13 test2.frm So how do I get rid of the error that Table 'test' is read only? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table is read only after copying table files to new server
Bennett Haselton wrote: I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: Its probably because you did a hard-copy of your tables. Is it advisable to do such a thing? Does MySql keep track of all the tables and databases in another file? If yes, then this additional file was not modified when you copied your tables. So MySql thinks that those tables don't exist..but in reality it does exist physically. Why the hard-copy? Couldn't you have just done a small dump of the table and sourced it back into the other database? Wouldn't have taken much time either. Also try logging in as root, and try inserting records, and see if that works. -- No, but he says that all Gods are good :w _ Meet the guy at http://www.meetRajesh.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
64-Bit and INNODB
I just want to be 100% sure on something... With an Opteron (or Itanium for that matter) using let's say the RedHat 9 or AS 3 betas available for those CPUs, how will MySQL/INNODB function? Specifically does the ability to allocate a bigger chunk of memory than 2GB just work out of the box? Can I set innodb_buffer_pool_size to 12GB for instance if I have 16GB in a box? Now then, along the same lines and not really MySQL oriented but someone here will likely know. I see lots of Opteron boards with 2 CPUs being advertised that have 4 DIMM slots per CPU. Is it one large flat memory space or do they do SMP in some strange fashion where each CPU has access to it's own memory? Therefore not giving a flat 16GB memory space if 8 slots were populated with 2GB DIMMs? I hope not but the way they're being described it is somewhat confusing. Even more off-topic, anyone know of a good 1U or 2U Opteron or Itanium system with 8 DIMM slots? 64-bit is great but unless you can stuff a _lot_ of memory in it, a lot of the advantage is lost. I wish someone like SuperMicro would offer a high end Opteron SuperServer. I love their dual Xeon systems.. I read a rumor they were going to and emailed them to inquire and was told nothing of value... Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table is read only after copying table files to new server
At 05:54 PM 8/16/2003 -0400, Rajesh Kumar wrote: Bennett Haselton wrote: I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: Its probably because you did a hard-copy of your tables. Is it advisable to do such a thing? Does MySql keep track of all the tables and databases in another file? Since MySQL listed tables for all the files that I copied, and since I was able to do SELECTs on them, I figured that the tables must have been added to the database in some semi-valid state. If yes, then this additional file was not modified when you copied your tables. So MySql thinks that those tables don't exist..but in reality it does exist physically. Why the hard-copy? Couldn't you have just done a small dump of the table and sourced it back into the other database? Wouldn't have taken much time either. Unfortunately, due to the size of the tables, the dump wouldn't have been that small :) And I'm moving the tables between different machines. At this point, it would be much easier to change the one little thing (it's always one little thing) that's probably making the tables read-only. Also try logging in as root, and try inserting records, and see if that works. Thanks; I tried it, but it didn't work. -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing imports
On Saturday 16 August 2003 4:41, Hans van Harten wrote: Depending on your exact needs, consider brutal overwrites: LOAD DATA CONCURRENT INFILE '...' REPLACE INTO TABLE Replaces 1 rows within 3.5 s into Innodb running MySQL-max-4.10 and WinXP on Celeron /400MHz/ for me. Is there a need for CONCURRENT when using InnoDB? Isn't that the default for InnoDB? If not, does it lock the entire InnoDB table while the insert is running? -Jackson HansH -- jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP or Perl?
On Saturday 16 August 2003 8:44, O'K Web Design wrote: Hi Mark I can only tell you about my experience from the Perl side of things. The integration to the database is great and the amount of things you can do with the data once you get it in the script is the biggest bonus. My feeling is that a combination of the two would probably be the best. Any pages that just list your records, I would build in PHP. Any that involve handling the data before output, I would use Perl. Mike I have yet to come upon data handling that I can do in Perl that I can't do in PHP. PHP is pretty robust in that area. The biggest advantage to Perl that I see is that there are more libraries and such available for Perl, but the PHP community is working on that. -Jackson - Original Message - From: Mark Healey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: August 16, 2003 1:17 AM Subject: PHP or Perl? As part of my own learning mysql project I'm planning to build databases for all my books and DVD's. Stephen Hawking is probably a better typist than I am so I plan to use barcodes to get the info ,taking the information from various web sites. People tell me that PHP is THE way to do database work with mysql. The thing is, I'm familiar with Perl and it has all kinds of neat string manipulation stuff and LWP. Does PHP have comprable libraries (especially LWP) and how difficult is it to move from Perl. I hope I don't start a relegious war here I just want some advice. Mark Healey [EMAIL PROTECTED] This account is only for lists to which I've subscribed. Any spammers invite the worst revenge I think I can get away with. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL] Confused about DATETIME compare/subtraction
Hello All, I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds). Any help? FPM
Re: table is read only after copying table files to new server
I searched on Google and I found this post from 2 years ago: http://www.phpbuilder.com/mail/php-db/2001082/0212.php suggesting that the way to do it would be to connect to MySQL as root and run the command: grant all on tracerlock.test to bhaselto identified by password I tried that, and then did flush privileges, then logged out and logged back in to mysql as bhaselto, but I still got the error table 'test' is read only when trying to insert rows into it. Any idea on how to do something differently with GRANT to make it work? -Bennett At 04:16 PM 8/16/2003 -0700, Bennett Haselton wrote: I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql insert into test2 values(3); Query OK, 1 row affected (0.00 sec) The files corresponding to test and test2 have the same permissions and ownership, so that's not it: -rw-rw1 mysqlmysql 20 Aug 15 21:22 test.MYD -rw-rw1 mysqlmysql1024 Aug 15 21:22 test.MYI -rw-rw1 mysqlmysql8556 Aug 15 21:22 test.frm -rw-rw1 mysqlmysql 5 Aug 16 19:13 test2.MYD -rw-rw1 mysqlmysql1024 Aug 16 19:13 test2.MYI -rw-rw1 mysqlmysql8556 Aug 16 19:13 test2.frm So how do I get rid of the error that Table 'test' is read only? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MYSQL] Confused about DATETIME compare/subtraction
PAUL MENARD wrote: Hello All, I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds). Any help? FPM I don't really know if timestamps can be added or substracted. But I still found a way to help you. SELECT UNIX_TIMESTAMP(first_stamp)-UNIX_TIMESTAMP(second_stamp) AS difference FROM table_name WHERE your_join_here HAVING difference=900; I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds) I'm assuming, you were trying to mean that you DON'T values less than 900 seconds. If you meant the opposite, please switch the greater than sign. -- No, but he says that all Gods are good :w _ Meet the guy at http://www.meetRajesh.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
