Re: Queries inside UDF
sguazt sguazt wrote: Thanks for answering! Your solution would be right if I can modify the database (and I have no control on software that populate tables). Unfortunately I can do only queries on that db; so the due date has to be recalculated every time I want to perform the main report (that use the ending date). An alternative way is to created a snapshot of main db with the added fields, but the report may contains not so updated information (e.g. if the snapshot is synch one time at day in theory the report may display info older up to a day). This violates the requirements of my boss who wants a very updated data ... :'-| So the only possible ways are: * do that via high-level language, like C, Perl, Java; I've done this but is very slow ... especially the client running the code is remote (with respect to DB server) ... so in addition to the load generated by the number of queries there's the load of network communication: (Send Queries + Receive Results) * N (where N 1 in general); * do that via SQL+UDF; the advantage of this solution is the load of computation is on DB server ... the load due to the network communication a very minimal: Send Query One Time + Receive Result When I tried UDF I'm said WOW this could be the solution to my problems Unfortunaly, I found out there's no way (at least for me) to access to current DBMS thread or connection; i.e.: SELECT foobar( ... ) FROM ...; I believed from foobar FUNCTION there would have been a way (through parameters passed by MySQL, like UDF_INIT* initid) to access to current DBMS connection object. I this is true ... what a pity! I hope MySQL staff will think to extend UDF to include this feature unless there're hidden trickly I don't see. -- Marco You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ms Sql unique identifier field
Hi! i have a question: FIELD IN MS SQL SERVER FiledName: uiAutID DataType: unique identifier DefaultValue: newid() Is RowGuid: Yes how i can describe the some column in MySQL? if isn't possible, how can i solve my problem? P.S.: is necessary for us, use unique identifier data type because the datas must be exchanged with others companies and we need garantee unique identification of all the informations. Maurizio, Sponda Italy
Re: Queries inside UDF
Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + ... You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. I can't use stored procedure because the target DB is the 4.0.21 Currently I'm working on MySQL 4.1.10a on my devel station ... but the production station is 4.0.21 (all installed as RPM x86). So please look at the C-code and tell me if it's wrong ... as alternative you can post me a working example of UDF containing MySQL query execution. Thanks! -- Marco -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Personalizza MSN Messenger con sfondi e fotografie! http://www.ilovemessenger.msn.it/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with default-collation
Hi, I am using mysql Ver 14.7 Distrib 4.1.8, for pc-linux (i686) When running a certain query I get the following error: DBD::mysql: t execute failed: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set' I do not understand why I have a mix of collations since in the my.cnf I indicate: [mysqld] default-character-set = utf8 default-collation = utf8_general_ci Where is the latin1_swedish_ci coming from? why isn't default-collation overwriting it? Appreciate any help. Thanks Dana This message has been scanned for viruses and dangerous content by Ex Libris Ltd, and is believed to be clean.
Re: upgrading problems
Hello. Please, search in MySQL archives (at lists.mysql.com/mysql) about successful solutions to your problem. You may use --focre --nodeps flags for the rpm command. [snip] I want to upgrade mysql client 3.23. to mysql 4.1.10 But when I type rpm -Uvh mysql-client-4.1...rpm It's says that some file from install mysqlrpm conflict with file from package mysql3.23 I try to uninstall package mysql3.23 with the rpm on the cd, but it's doesn't work I have Red hat Enterprise Linus AS How can I fix itGuillaume Chartrand [EMAIL PROTECTED] wrote: [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To extend InnoDB table size
MyISAM tables files have MYI suffix. See: http://dev.mysql.com/doc/mysql/en/full-table.html Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen Naveen C Joshi [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stuck with older version of MySQL on RH9, want to install latest, but cant
Hello. Hi, I am trying to install the latest version of MySQL(i am not sure 4.0.23? Use 4.1.10a. See: http://dev.mysql.com/downloads/ Hi, I am trying to install the latest version of MySQL(i am not sure 4.0.23? currently in the office). The RH9 comes with some 3.2.xx version, I try to run rpm -U to upgrade but that doesnt work, I try to install it doesnt work and I try to remove mysql that doesnt work either. I went to Add and Remove programs removed anything to do with SQL server and still it shows on command line that mysql 3.2.xx is installed. When I try and remove using rpm command line it gives me PHP DBD dependency error. I basically want to remove and upgrade my MySQL, but have not found a simple way of doing it. Any help would be great Thanks Ankur -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbug
Hello. Please let me know how to install MySQL successfully on Linux. You may use official binaries. See: http://dev.mysql.com/downloads/ [snip] I was installing MySQL on Linux. At that time it was giving some problem when I did ./configure. Your libc libraries are not 100% compatible with MySQL version. Mysqld should work normally with the exception that hostname resolving will not work. This means that you should use IP address instead of host name when specifying Mysql privileges. Please let me know how to install MySQL successfully on Linux. Waiting for your valuable reply. Regards Sheela. Sheela_Belagutti [EMAIL PROTECTED] wrote: [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Why are my tables read only?
Hi, After upgrading to mysql 4.1.10nt I am being told that some of my tables are read-only I cannot find where or how you might specify that a table is read/write to whatever -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with default-collation
H... It looks like you upgraded from a MySQL that doesn't know about collations... All the tables created with a version of MySQL that doesn't know about collation are considered by a version that does know... being of collation latin1_swedish_ci... (on columns where collation makes sense... I hope this is implied...) If you created the tables under 4.1.8... then ... then there is a problem... are you sure MySQL is reading your CNF ? Is there another CNF that overrides the one that has the desired settings ? If not... then you will need to ALTER the table(s)... but be carefull... ALTER them in 2 steps... ALTER all the columns in the table to a BINARY data type... then ALTER the table's collation to the desired one... finaly ALTER all columns back but now using the desired collation !!! Hope this helps ! Gabriel PREDA www.amr.ro www.lgassociations.info dev.falr.ro - Original Message - Hi, I am using mysql Ver 14.7 Distrib 4.1.8, for pc-linux (i686) When running a certain query I get the following error: DBD::mysql: t execute failed: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set' I do not understand why I have a mix of collations since in the my.cnf I indicate: [mysqld] default-character-set = utf8 default-collation = utf8_general_ci Where is the latin1_swedish_ci coming from? why isn't default-collation overwriting it? Appreciate any help. Thanks Dana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stuck with older version of MySQL on RH9, want to install latest, but cant
Ankur G35 Saxena wrote: All I had to do was rpm -Uvh --nodeps pkg_name I have the MySQL AB RPMs running on Red Hat 9 just fine. I didn't have to force them at all. I suspect you had to force it because you had MySQL installed already. The problem is, the Red Hat package is called mysql, whereas the MySQL AB package is called MySQL -- they are different names in RPM's view, so files named the same in both packages cause a conflict. If you had uninstalled the old MySQL RPMs and then installed the new ones, they wouldn't have required the --nodeps forcing argument. did the server first, then the libraries and then client You can give multiple RPM files to the rpm command: $ rpm -ivh MySQL-*.rpm You don't have to give the names in any particular order. The rpm program will figure out the correct order to install them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUOTE() function, what happens here?
Hi everyone,
I'm following up on my own question. There appears to be a bug in the way
how QUOTE() interacts with the mysql client.
Using --raw mode can apparently solve the problem reported in my original
post:
shell mysql --raw -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Pitt\'s Place'
That seems about right, but it doesn't solve the whole problem. Consider
this:
shell mysql -e INSERT INTO foo VALUES('Joe\'s place\tthat\'s nice'); test
(note: there is a TAB between 'place' and 'that')
Let's retrieve this from the db, with and without --raw:
shell mysql --raw -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Joe\'s place that\'s nice'
shell mysql -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Joe\\'s place\tthat\\'s nice'
None of those results can be re-inserted as is. The only possibility to
fix this would probably be to add a new option (how about --medium-raw ?)
to the mysql client. All else would break existing scripts.
Thomas Spahni
On Tue, 15 Mar 2005, Thomas Spahni wrote:
Dear list,
I don't understand what happens here:
shell mysql -N -e SHOW VARIABLES LIKE 'version'; test
+-++
| version | 4.0.14-log |
+-++
shell mysql -e CREATE TABLE foo (sometext VARCHAR(255)); test
shell mysql -e INSERT INTO foo VALUES('Pitt\\'s Place'); test
shell mysql -N -e SELECT QUOTE(sometext) FROM foo; test
+-+
| 'Pitt\'s Place' |
+-+
So far so good; exactly what I would expect. The string is nicely escaped
with ONE backslash. But now, look at this:
shell mysql -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Pitt\\'s Place'
Double backslash in batch mode. Same result if I pipe the query into
mysql. Why? This can't be fed into any INSERT query. Bug or feature?
Any comments from the list are very welcome.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.10a packaging for Solaris
Hi Jonathan, all! Am Mi, den 16.03.2005 schrieb Jonathan Stockley um 22:33: I just downloaded the 4.1.10a release for Solaris 8 and 9 (32bit). It seems that it is no longer in a tar archive. Was this change intentional? Both yes and no: Yes, it was intentional to create and offer PKG format. No, it was not intended to block tar.gz. In fact, tar.gz is still being built and offered, but due to some mishandling it is not listed on the download page. Please access some mirror directly that offers a list - for example: ftp://ftp.gwdg.de/pub/misc/mysql/Downloads/MySQL-4.1/ I cant find any mention of it on the web site. How do I unpack the new format into a given directory? I assume this is possible, but I lack detailed Solaris / PKG knowledge to answer. Maybe some Solaris manual does tell? But probably you will get along better by downloading tar.gz. HTH, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.10a packaging for Solaris
man pkgadd -J - Original Message - From: Joerg Bruehe [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Jonathan Stockley [EMAIL PROTECTED] Sent: Thursday, March 17, 2005 9:16 AM Subject: Re: 4.1.10a packaging for Solaris Hi Jonathan, all! Am Mi, den 16.03.2005 schrieb Jonathan Stockley um 22:33: I just downloaded the 4.1.10a release for Solaris 8 and 9 (32bit). It seems that it is no longer in a tar archive. Was this change intentional? Both yes and no: Yes, it was intentional to create and offer PKG format. No, it was not intended to block tar.gz. In fact, tar.gz is still being built and offered, but due to some mishandling it is not listed on the download page. Please access some mirror directly that offers a list - for example: ftp://ftp.gwdg.de/pub/misc/mysql/Downloads/MySQL-4.1/ I cant find any mention of it on the web site. How do I unpack the new format into a given directory? I assume this is possible, but I lack detailed Solaris / PKG knowledge to answer. Maybe some Solaris manual does tell? But probably you will get along better by downloading tar.gz. HTH, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
guess_table utility
Is there a free service where I can upload my CSV file to let the guess_table script go through and suggest create table statement? I did recently downloaded and tried the guess_table.pl script that is mentioned in Mysql cookbook. (10.36 Guessing Table Structure) I could not run the script due to some server incompatibility. Shantanu Oak [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUOTE() function, what happens here?
For those of you watching but still not getting his point: He is saying
that QUOTE() improperly escapes a string where a \ and a ' appear together
in the unquoted text. the sequence \' should look like \\\' when
QUOTE()-ed. It should escape \ as \\ and ' as \'.\\+\' = \\\' .
QUOTE() seems to be leaving out the 3rd \.
http://dev.mysql.com/doc/mysql/en/string-syntax.html
Thomas, I think you should file a bug report on this. That way they FIX
the QUOTE() function. No middle-ground needed.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
[EMAIL PROTECTED] wrote on 03/17/2005 09:14:29 AM:
Hi everyone,
I'm following up on my own question. There appears to be a bug in the
way
how QUOTE() interacts with the mysql client.
Using --raw mode can apparently solve the problem reported in my
original
post:
shell mysql --raw -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Pitt\'s Place'
That seems about right, but it doesn't solve the whole problem. Consider
this:
shell mysql -e INSERT INTO foo VALUES('Joe\'s place\tthat\'s nice');
test
(note: there is a TAB between 'place' and 'that')
Let's retrieve this from the db, with and without --raw:
shell mysql --raw -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Joe\'s place that\'s nice'
shell mysql -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Joe\\'s place\tthat\\'s nice'
None of those results can be re-inserted as is. The only possibility to
fix this would probably be to add a new option (how about --medium-raw
?)
to the mysql client. All else would break existing scripts.
Thomas Spahni
On Tue, 15 Mar 2005, Thomas Spahni wrote:
Dear list,
I don't understand what happens here:
shell mysql -N -e SHOW VARIABLES LIKE 'version'; test
+-++
| version | 4.0.14-log |
+-++
shell mysql -e CREATE TABLE foo (sometext VARCHAR(255)); test
shell mysql -e INSERT INTO foo VALUES('Pitt\\'s Place'); test
shell mysql -N -e SELECT QUOTE(sometext) FROM foo; test
+-+
| 'Pitt\'s Place' |
+-+
So far so good; exactly what I would expect. The string is nicely
escaped
with ONE backslash. But now, look at this:
shell mysql -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Pitt\\'s Place'
Double backslash in batch mode. Same result if I pipe the query into
mysql. Why? This can't be fed into any INSERT query. Bug or feature?
Any comments from the list are very welcome.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server won't stop...
Okay, I was able to get the MySQL to shut down via command line. However, not being a Unix person, I'm not sure where I would go to change the .bash_profile. I've never had to change it for anything up until now. I will be curious to see if adding the path affects the problem in any way with both the MySQL preference pane and the Administrator utility. Jeff On Mar 16, 2005, at 10:56 PM, Michael Stassen wrote: It should be in mysql's bin directory, typically /usr/local/mysql/bin. You either need to add that to your PATH, export PATH=$PATH:/usr/local/mysql/bin or use the full path when executing the command /usr/local/mysql/bin/mysqladmin -u root -p shutdown I'd recommend the former, and I'd suggest making it permanent by adding that to your .bash_profile. I haven't used the OSX MySQL Administrator, so I can't be sure, but it *may* be that it isn't working because of the PATH problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Consultancy for MySQL database design against substantial financial remuneration
Hi, We are developing an application that needs to use a massive back-end database. The database will contain around 75 million rows with around 80 columns per row. We would prefer to use MySQL as the database platform as it is free. The MySQL database would be hosted on a dedicated server that we will purchase from a web hosting company. This database would be used both by our customers and by our own employees. The first column will contain some text which will be unique in each row. 90% of the remaining columns will containing numbers and the other columns will contain text. The second column will contain numbers and it needs to be updated on a monthly basis. But, we also need to store historical data regarding the value of the second column for each row for the last 24 months, on a rolling basis. This can either be done by adding more columns to the same table, or by putting this historical data in a separate table, depending on your recommendations. Users will make 2 types of queries on this database: i) The first type of query is what can be called a mission-critical query - these queries will be made by our customers and the results of these queries must be returned within 30 seconds at the most; otherwise, customers are not going to want to use the application. This query would basically involve asking the customer for a search string, searching the FIRST column (and ONLY the first column) of the entire database to find out each row that contains that search string (either in whole or in part) and then returning all such rows to the user sorted in descending order of the SECOND column. Only the information in the first 2 columns will be returned to the customers - the information in the other 78 columns will not be returned to the customers. Customers will also have the option of specifying negative matches - i.e. if the first column of a particular row contains any one of a list of banned words or phrases, then that row will not be returned even if it contained the primary search string. ii) The second type of queries are non-mission-critical; these would be run by our employees and it is ok if these queries take as much as 10 minutes to return results. However, the queries that our employees will run are also much more complex - they will specify multiple search criteria - for instance, return all rows for which the 60th column has a value 2000 and the minimum value for the columns 40, 41, ... 50 for that row is 20 and the 35th column of that row is 5 etc. It is quite possible that as many as 20 - 30 users will be querying the database at the same time. Furthermore, there will be 5 - 6 different PHP scripts that are going to constantly update the different columns and rows of the database with the values. Here are my questions: i) Is MySQL a realistic option for this kind of database? ii) What should be the hardware configuration (processor type, number of processors, RAM etc.) for the dedicated server that will host this database. We are interested in the most cost-effective option; i.e. the least powerful hardware configuration that is suitable for running this system. iii) What is the best way of designing the database architecture in order to ensure that we are able to meet our targets regarding the query times? I might need to ask some follow-up questions based on your initial response. Please be extremely detailed and specific in answering the questions, especially the third question. For the third question, please suggest the table structure including whether we should keep all the data in a single table or whether we should use multiple tables. If we use multiple tables, how many such tables should we use, and which columns should be present in each table? Also, we need to know what should be the primary key, the unique key etc. for each table and how the indexes should be defined. Alternatively, if you recommend that we break up the database into multiple tables not in terms of columns but in terms of rows (i.e. keeping a fixed number of rows per table), then what is the ideal number of rows that should be present in each table? And, depending on your recommendation regarding the database structure, please advise us regarding how we should build the queries for the 2 types of queries that we need to support. I would prefer that someone who has actual experience designing a MySQL database with similar or even more demanding characteristics and requirements answer my query. We are looking for the right persion who can come up with a consultancy. Remunarations will not be any constraint for some body who can provide the right solution. If you need any clarifications from us before you can answer the question, feel free to ask. Suryya
Re: Server won't stop...
Jeff Justice wrote: I'm not sure where I would go to change the .bash_profile. It's in your home directory. You land there just by opening a new Terminal window. I suppose if you had to wimp out, you could probably say: $ TextEdit .bash_profile But real Unix users don't use GUI text editors. :) (I say probably because I don't have an OS X box in front of me at the moment.) You'll probably have to log out and back in for this to take effect. Or reboot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert was working
Hi!
I was able to insert data with the following:
sub insert_sql {
my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total,
$DEBUG ) = @_;
print [EMAIL PROTECTED] if $DEBUG;
( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total ) =
clean_sql( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total, $DEBUG );
print \ninsert_sql -
*$group_name*$me*$daily*$item*$unit*$qty*$amount*$tax*$total*\n if $DEBUG;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) || die
\n$DBI::errstr\n;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?, ?,
?, ? );
my $sth = $dbh-prepare( $sql ) || die $dbh-errstr;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute insert_sql!!\n$DBI::errstr;
}1;
I am no longer able to insert any data at all. No errors, nothing. It acts
like it works. But I am able to select existing data with the following:
sub select_sql {
my ( $me, $daily, $DEBUG ) = @_;
my %data = ();
print [EMAIL PROTECTED] if $DEBUG;
$me =~ s/\s+//;
$daily =~ s/\s+//;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) or die
\n$DBI::errstr\n;
my $sql = select group_name, me, daily, item, unit, qty, amount, tax,
total
from wolfies
where me= '$me'
anddaily = '$daily'
;
print sql *$sql*\n if $DEBUG;
my $sth = $dbh-prepare( $sql ) || die $DBI::errstr\n;
my $rv = $sth-execute() || die Cannot execute
select_sql!!br$DBI::errstr;
while( my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total ) = $sth-fetchrow() ) {
print \nselect_sql $group_name, $me, $daily, $item, $unit, $qty,
$amount, $tax, $total*\n if $DEBUG;
push @{ $data{ $group_name }}, $item, $unit, $qty, $amount, $tax,
$total;
}
return \%data;
}1;
I have done the following setup options:
GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'localhost';
SET PASSWORD FOR 'gjwpp88'@'localhost' = PASSWORD('password');
UPDATE mysql.user SET Password = OLD_PASSWORD('password')
WHERE Host = 'localhost' AND User = 'gjwpp88';
SET PASSWORD FOR 'gjwpp88'@'localhost' = OLD_PASSWORD('password');
SELECT 'localhost', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('password') 16;
FLUSH PRIVILEGES;
Any ideas?
Thanks,
Jerry
Re: insert was working
Gerald!
Do you have an auto_increment field?
If so, what's it set as?
Richard
Quoting Gerald Preston [EMAIL PROTECTED]:
Hi!
I was able to insert data with the following:
sub insert_sql {
my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total,
$DEBUG ) = @_;
print [EMAIL PROTECTED] if $DEBUG;
( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total ) =
clean_sql( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total, $DEBUG );
print \ninsert_sql -
*$group_name*$me*$daily*$item*$unit*$qty*$amount*$tax*$total*\n if $DEBUG;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) || die
\n$DBI::errstr\n;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?, ?,
?, ? );
my $sth = $dbh-prepare( $sql ) || die $dbh-errstr;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute insert_sql!!\n$DBI::errstr;
}1;
I am no longer able to insert any data at all. No errors, nothing. It acts
like it works. But I am able to select existing data with the following:
sub select_sql {
my ( $me, $daily, $DEBUG ) = @_;
my %data = ();
print [EMAIL PROTECTED] if $DEBUG;
$me =~ s/\s+//;
$daily =~ s/\s+//;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) or die
\n$DBI::errstr\n;
my $sql = select group_name, me, daily, item, unit, qty, amount, tax,
total
from wolfies
where me= '$me'
anddaily = '$daily'
;
print sql *$sql*\n if $DEBUG;
my $sth = $dbh-prepare( $sql ) || die $DBI::errstr\n;
my $rv = $sth-execute() || die Cannot execute
select_sql!!br$DBI::errstr;
while( my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total ) = $sth-fetchrow() ) {
print \nselect_sql $group_name, $me, $daily, $item, $unit, $qty,
$amount, $tax, $total*\n if $DEBUG;
push @{ $data{ $group_name }}, $item, $unit, $qty, $amount, $tax,
$total;
}
return \%data;
}1;
I have done the following setup options:
GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'localhost';
SET PASSWORD FOR 'gjwpp88'@'localhost' = PASSWORD('password');
UPDATE mysql.user SET Password = OLD_PASSWORD('password')
WHERE Host = 'localhost' AND User = 'gjwpp88';
SET PASSWORD FOR 'gjwpp88'@'localhost' = OLD_PASSWORD('password');
SELECT 'localhost', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('password') 16;
FLUSH PRIVILEGES;
Any ideas?
Thanks,
Jerry
R. Whitney
Transcend Development
Producing the next phase of your internet presence
http://xend.net
Premium Quality Web Hosting
http://xendhosting.com
rw AT xend.net
Net Binder http://netbinder.net
310-943-6498
602-288-5340
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server won't stop...
You can't call TextEdit from the command line like that, you have to launch it in the GUI. That doesn't really matter, though, as you do not want to use TextEdit, because it saves files as RTF! Launch a Terminal window. You'll be in your home directory. Enter ls .bash* to see if you already have a .bash_profile or .bashrc. You don't get one by default, so you probably won't see one. (You should see a .bash_history.) If you don't have one, you can create one with the new PATH setting by entering echo 'export PATH=$PATH:/usr/local/mysql/bin' .bash_profile If you do have one, but it doesn't have a PATH line (check with `cat .bash_profile`), you can add one with echo 'export PATH=$PATH:/usr/local/mysql/bin' .bash_profile ( means append). You can edit your .bash_profile with emacs, which comes standard with OS X. emacs .bash_profile (Ctl-x Ctl-s to save, Ctl-x Ctl-c to quit). Once you've modified your .bash_profile, it will take effect in every Terminal window launched afterwards. Simply choose New Shell from the File Menu (CMD-n). No need to quit Terminal or log out/in or reboot! I'm doubtful this will affect the preference pane, but it won't hurt to try, and it will make it easier to use all the command line tools which come with mysql. Michael P.S. I'm assuming you have OS X 10.3, where bash is the standard shell. You can enter echo $SHELL in Terminal to verify which shell you have. Warren Young wrote: Jeff Justice wrote: I'm not sure where I would go to change the .bash_profile. It's in your home directory. You land there just by opening a new Terminal window. I suppose if you had to wimp out, you could probably say: $ TextEdit .bash_profile But real Unix users don't use GUI text editors. :) (I say probably because I don't have an OS X box in front of me at the moment.) You'll probably have to log out and back in for this to take effect. Or reboot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server won't stop...
Okay...now we're getting somewhere. Thank you. Adding the file wasn't the problem, I just wasn't sure WHERE it should be added. Yes, I am using 10.3. Just trying to pin down why the GUI tools can't seem to start and stop this thing reliably. As I stated before, I'm not a Unix geek :) so for me, it is time well spent to try and get the GUI working. Jeff On Mar 17, 2005, at 10:31 AM, Michael Stassen wrote: You can't call TextEdit from the command line like that, you have to launch it in the GUI. That doesn't really matter, though, as you do not want to use TextEdit, because it saves files as RTF! Launch a Terminal window. You'll be in your home directory. Enter ls .bash* to see if you already have a .bash_profile or .bashrc. You don't get one by default, so you probably won't see one. (You should see a .bash_history.) If you don't have one, you can create one with the new PATH setting by entering echo 'export PATH=$PATH:/usr/local/mysql/bin' .bash_profile If you do have one, but it doesn't have a PATH line (check with `cat .bash_profile`), you can add one with echo 'export PATH=$PATH:/usr/local/mysql/bin' .bash_profile ( means append). You can edit your .bash_profile with emacs, which comes standard with OS X. emacs .bash_profile (Ctl-x Ctl-s to save, Ctl-x Ctl-c to quit). Once you've modified your .bash_profile, it will take effect in every Terminal window launched afterwards. Simply choose New Shell from the File Menu (CMD-n). No need to quit Terminal or log out/in or reboot! I'm doubtful this will affect the preference pane, but it won't hurt to try, and it will make it easier to use all the command line tools which come with mysql. Michael P.S. I'm assuming you have OS X 10.3, where bash is the standard shell. You can enter echo $SHELL in Terminal to verify which shell you have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.2 alpha crashes with Query Browser
Maybe it's my setup... but: whenever I try to login to use the mySQL query browser, it instantlly turns off the 5.0.2 alpha mySQL instance (stops running). the error I get is: mysqld-max-nt.exe - Application Error the instruciton at 0x00538d34 referenced memory at 0x007f9000. The memory could not be read. Running Win2k pro w/ 512mb of ram. -- -Francisco http://pcthis.blogspot.com | PC news with out the jargon! http://sqlthis.blogspot.com | Tsql and More... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server won't stop...
On Thu, 17 Mar 2005 11:31:32 Michael Stassen wrote: You can't call TextEdit from the command line like that, you have to launch it in the GUI. This will do the trick: open -a /Applications/TextEdit.app .bash_profile That doesn't really matter, though, as you do not want to use TextEdit, because it saves files as RTF! This is true only if Rich text mode is selected as the default in TextEdit preferences. Select Plain text and it will read and write plain text. Certainly I agree, though, that an emacs command line is more appropriate here! Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server won't stop...
Interestingly enough, adding the PATH to the .bash_profile has made the preference pane 100% stable now (the Admin GUI is another story). So I'm wondering if the pref pane simply calls a shell script or command. If that is the case, shouldn't the PATH be added or created during installation of MySQL for OSX? Jeff On Mar 17, 2005, at 10:46 AM, Jeff Justice wrote: Okay...now we're getting somewhere. Thank you. Adding the file wasn't the problem, I just wasn't sure WHERE it should be added. Yes, I am using 10.3. Just trying to pin down why the GUI tools can't seem to start and stop this thing reliably. As I stated before, I'm not a Unix geek :) so for me, it is time well spent to try and get the GUI working. Jeff On Mar 17, 2005, at 10:31 AM, Michael Stassen wrote: You can't call TextEdit from the command line like that, you have to launch it in the GUI. That doesn't really matter, though, as you do not want to use TextEdit, because it saves files as RTF! Launch a Terminal window. You'll be in your home directory. Enter ls .bash* to see if you already have a .bash_profile or .bashrc. You don't get one by default, so you probably won't see one. (You should see a .bash_history.) If you don't have one, you can create one with the new PATH setting by entering echo 'export PATH=$PATH:/usr/local/mysql/bin' .bash_profile If you do have one, but it doesn't have a PATH line (check with `cat .bash_profile`), you can add one with echo 'export PATH=$PATH:/usr/local/mysql/bin' .bash_profile ( means append). You can edit your .bash_profile with emacs, which comes standard with OS X. emacs .bash_profile (Ctl-x Ctl-s to save, Ctl-x Ctl-c to quit). Once you've modified your .bash_profile, it will take effect in every Terminal window launched afterwards. Simply choose New Shell from the File Menu (CMD-n). No need to quit Terminal or log out/in or reboot! I'm doubtful this will affect the preference pane, but it won't hurt to try, and it will make it easier to use all the command line tools which come with mysql. Michael P.S. I'm assuming you have OS X 10.3, where bash is the standard shell. You can enter echo $SHELL in Terminal to verify which shell you have. -- 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]
long PHP mysql_connect times
I'm not sure if it's a OS X, PHP or MySQL issue so I apologize in advance if this is taken as too off topic. I have a PHP5.x site under development with MySQL 4.1.10. I had the site running fine and there were no speed issues at all. Everything is on the same system (a dual G5 Xserve). Today however the PHP pages with MySQL calls started to take forever to load. Static html and php files still loaded instantly. I tracked the issue down to mysql_connect. Initial calls to this function are taking 5 to 10 seconds each. Subsequent calls missing a mysql_close call do not have the latency issue. Normally I try have each call to mysql_connect followed by a mysql_close to keep my mysql tables in order. I have tried: - loading the pages remotely and locally (both slow) - checked the Apache / MySQL logs - manually ran the mysql queries to check for slow queries (all 0.0x seconds duration) - restarted apache / mysql - used host and ip for mysql_connect function call (i.e. not a DNS look-up issue) - full reboot mysql_pconnect still stalls on the first connect but subsequent requests have no delay. Manual CLI connections do not show the connection delay. I am stumped. What is the best practice for using connect vs. pconnect? Is it not best to open a connection, run a query and then close the connection? Thanks, Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert was working
Richard,
Explain auto_increment field please.
Thanks,
Jerry
-Original Message-
From: Richard Whitney [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 17, 2005 10:31 AM
To: Gerald Preston
Cc: mysql@lists.mysql.com
Subject: Re: insert was working
Gerald!
Do you have an auto_increment field?
If so, what's it set as?
Richard
Quoting Gerald Preston [EMAIL PROTECTED]:
Hi!
I was able to insert data with the following:
sub insert_sql {
my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total,
$DEBUG ) = @_;
print [EMAIL PROTECTED] if $DEBUG;
( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total )
=
clean_sql( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total, $DEBUG );
print \ninsert_sql -
*$group_name*$me*$daily*$item*$unit*$qty*$amount*$tax*$total*\n if
$DEBUG;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) || die
\n$DBI::errstr\n;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?,
?,
?, ? );
my $sth = $dbh-prepare( $sql ) || die $dbh-errstr;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute insert_sql!!\n$DBI::errstr;
}1;
I am no longer able to insert any data at all. No errors, nothing. It
acts
like it works. But I am able to select existing data with the following:
sub select_sql {
my ( $me, $daily, $DEBUG ) = @_;
my %data = ();
print [EMAIL PROTECTED] if $DEBUG;
$me =~ s/\s+//;
$daily =~ s/\s+//;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) or die
\n$DBI::errstr\n;
my $sql = select group_name, me, daily, item, unit, qty, amount, tax,
total
from wolfies
where me= '$me'
anddaily = '$daily'
;
print sql *$sql*\n if $DEBUG;
my $sth = $dbh-prepare( $sql ) || die $DBI::errstr\n;
my $rv = $sth-execute() || die Cannot execute
select_sql!!br$DBI::errstr;
while( my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total ) = $sth-fetchrow() ) {
print \nselect_sql $group_name, $me, $daily, $item, $unit, $qty,
$amount, $tax, $total*\n if $DEBUG;
push @{ $data{ $group_name }}, $item, $unit, $qty, $amount, $tax,
$total;
}
return \%data;
}1;
I have done the following setup options:
GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'localhost';
SET PASSWORD FOR 'gjwpp88'@'localhost' = PASSWORD('password');
UPDATE mysql.user SET Password = OLD_PASSWORD('password')
WHERE Host = 'localhost' AND User = 'gjwpp88';
SET PASSWORD FOR 'gjwpp88'@'localhost' = OLD_PASSWORD('password');
SELECT 'localhost', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('password') 16;
FLUSH PRIVILEGES;
Any ideas?
Thanks,
Jerry
R. Whitney
Transcend Development
Producing the next phase of your internet presence
http://xend.net
Premium Quality Web Hosting
http://xendhosting.com
rw AT xend.net
Net Binder http://netbinder.net
310-943-6498
602-288-5340
--
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: 5.0.2 alpha crashes with Query Browser
Lots of users report such instabilities in the 5.0.2-alpha build for Windows. We went back to 5.0.1. PB - Francisco Tapia wrote: Maybe it's my setup... but: whenever I try to login to use the mySQL query browser, it instantlly turns off the 5.0.2 alpha mySQL instance (stops running). the error I get is: mysqld-max-nt.exe - Application Error the instruciton at 0x00538d34 referenced memory at 0x007f9000. The memory could not be read. Running Win2k pro w/ 512mb of ram. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 3/15/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select all fields plus total children
I have 2 tables, category product. product contains category_id which points back to category.id I'm trying to get all fields for each category plus the total no. of products in that category. A simple query for this: SELECT c.*, COUNT(p.id) AS total FROM category AS c, product AS p WHERE c.parent_id = 0 AND p.category_id = c.id GROUP BY c.name unfortunately will not show any categories which currently have no products assigned. I'm sure i need a join in there but haven't found it. I'm also sure i'll slap my forehead when i see the proer way to do this. Any help appreciated. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select all fields plus total children
brian ally [EMAIL PROTECTED] wrote on 03/17/2005 02:28:46 PM: I have 2 tables, category product. product contains category_id which points back to category.id I'm trying to get all fields for each category plus the total no. of products in that category. A simple query for this: SELECT c.*, COUNT(p.id) AS total FROM category AS c, product AS p WHERE c.parent_id = 0 AND p.category_id = c.id GROUP BY c.name unfortunately will not show any categories which currently have no products assigned. I'm sure i need a join in there but haven't found it. I'm also sure i'll slap my forehead when i see the proer way to do this. Any help appreciated. brian I hate to be the bearer of bad new but you ALREADY have a join in there You created an implicit INNER JOIN when you said: FROM category AS c, product AS p WHERE c.parent_id = 0 AND p.category_id = c.id That phrase is semantically equivalent to: FROM category AS c INNER JOIN product AS p ON c.parent_id = 0 AND p.category_id = c.id You tell us that you need all category records but only those product records that exist, that's the purpose of the two outer JOIN predicates (to give you all of one table and any that match from another). The DIRECTION of the JOIN determines which table is the all table and which table is the optional. Change your query to use FROM category AS c LEFT JOIN product AS p ON c.parent_id = 0 AND p.category_id = c.id and you will get the results you wanted because your category table is on the left side of the JOIN. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: 5.0.2 alpha crashes with Query Browser
When will 5.0.3 come out? Thanks, Lily -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 10:17 AM To: Francisco Tapia Cc: mysql@lists.mysql.com Subject: Re: 5.0.2 alpha crashes with Query Browser Lots of users report such instabilities in the 5.0.2-alpha build for Windows. We went back to 5.0.1. PB - Francisco Tapia wrote: Maybe it's my setup... but: whenever I try to login to use the mySQL query browser, it instantlly turns off the 5.0.2 alpha mySQL instance (stops running). the error I get is: mysqld-max-nt.exe - Application Error the instruciton at 0x00538d34 referenced memory at 0x007f9000. The memory could not be read. Running Win2k pro w/ 512mb of ram. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 3/15/2005 -- 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: select all fields plus total children
At 01:28 PM 3/17/2005, you wrote: I have 2 tables, category product. product contains category_id which points back to category.id I'm trying to get all fields for each category plus the total no. of products in that category. A simple query for this: SELECT c.*, COUNT(p.id) AS total FROM category AS c, product AS p WHERE c.parent_id = 0 AND p.category_id = c.id GROUP BY c.name unfortunately will not show any categories which currently have no products assigned. I'm sure i need a join in there but haven't found it. I'm also sure i'll slap my forehead when i see the proer way to do this. Any help appreciated. brian Brian, You need to do a Left Join as in: SELECT c.*, sum(1-ISNULL(p.id)) AS total FROM category AS c left join product AS p on c.id=p.category_id WHERE c.parent_id = 0 GROUP BY c.name The left join will join the tables and if there is no corresponding row on the right, it fills it with null. The sum(1-ISNULL(p.id)) will sum all occurrences of p.id that are not null as 1, and sums 0 if they are null. You need to do that because p.id is NULL when there is no corresponding product row. Ok, slap away! g Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copying of information into a table from another with conditions (using where)
Hello, I imported tow tables from an Access database to MySql. Call them table1 and table2. Both tables came with unique identifiers (ID1 for table1 and ID2 for table2)which were generated automatically as a regular integer index(0,1,2...N-1) for an N set of data. I would like to get rid this type of unique identifier. I created a primary key in table1 called mainID. I then created a new column in table2 also called mainID which will contain the information of the new primary key which I created. I then issued the following mySql command: update table2 set table2.mainID=table1.mainID where table2.ID1=table1.ID1; I get the following error ERROR 1054 (42S22): Unknown column 'ID1' in 'where clause' Somehow it doesn't recognize any columns from table1. Your help is greatly apreciated... Further explanation for my query: My reasoning for the condition is : for each row in table2 with a certain value for ID1, find the matching value in table1for ID1. Then take the corresponding mainID value on that row and stick it in that row in table2. I hope that was enlightening rather than more confusing. -- -Mahmoud Badreddine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copying of information into a table from another with conditions (using where)
Mahmoud Badreddine [EMAIL PROTECTED] wrote on 03/17/2005 03:16:47 PM: Hello, I imported tow tables from an Access database to MySql. Call them table1 and table2. Both tables came with unique identifiers (ID1 for table1 and ID2 for table2)which were generated automatically as a regular integer index(0,1,2...N-1) for an N set of data. I would like to get rid this type of unique identifier. I created a primary key in table1 called mainID. I then created a new column in table2 also called mainID which will contain the information of the new primary key which I created. I then issued the following mySql command: update table2 set table2.mainID=table1.mainID where table2.ID1=table1.ID1; I get the following error ERROR 1054 (42S22): Unknown column 'ID1' in 'where clause' Somehow it doesn't recognize any columns from table1. Your help is greatly apreciated... Further explanation for my query: My reasoning for the condition is : for each row in table2 with a certain value for ID1, find the matching value in table1for ID1. Then take the corresponding mainID value on that row and stick it in that row in table2. I hope that was enlightening rather than more confusing. -- -Mahmoud Badreddine You didn't tell your UPDATE statement anything about table1 as a source of data but you tried to use it in your WHERE clause. This will do what you wanted: update table2 INNER JOIN table1 on table2.ID1=table1.ID1 set table2.mainID=table1.mainID; For more information on forming UPDATE statements: http://dev.mysql.com/doc/mysql/en/update.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Bug? Date insert comes out zero on Solaris using Python MySQLdb?
I have Python code that I run on Windows and Solaris. It works properly on Windows. However, on Solaris, when it inserts records, datetime columns end up with a value of zero. I suspect that this began happening after we upgraded the MySQL server to 4.1.10 from a 4.0.x version (I think we were on 4.0.18). I see some changes to datetime handling in the 4.1.x releases, but nothing that seems immediately obvious. I hope to fix this is by re-building MySQLdb based on the 4.1.10 libraries. Any other suggestions? I'm using MySQLdb.times to format the datetimes before inserting. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ROW_SIZE or something alike
oix ppl, Nobody have any sugestion that can me help with this issue? thanks mpneves On Wednesday 16 March 2005 14:09, Marco Neves wrote: oix ppl, sorry for the question, but I looked in the Manual online and didn't find nothing that do this that I'm looking for, so I'm hopping someone can help me. I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). The way I was thinking this could be got was with something like: SELECT ROW_SIZE(),* FROM tablename WHERE somefield=somevalue; or SELECT group__id, SUM(ROW_SIZE()) grpsize FROM tablename GROUP BY group__id; This is what I thought would be great to have, but any other way to get a row size would be good enought. This could be both Data and Index size for each row, but data would be good enought. There is anyway to get this information? Thanks, mpneves -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ROW_SIZE or something alike
Marco Neves wrote: oix ppl, Nobody have any sugestion that can me help with this issue? thanks mpneves On Wednesday 16 March 2005 14:09, Marco Neves wrote: Look under storage requirements in the manual. You are MySQL core certified? oix ppl, sorry for the question, but I looked in the Manual online and didn't find nothing that do this that I'm looking for, so I'm hopping someone can help me. I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). The way I was thinking this could be got was with something like: SELECT ROW_SIZE(),* FROM tablename WHERE somefield=somevalue; or SELECT group__id, SUM(ROW_SIZE()) grpsize FROM tablename GROUP BY group__id; This is what I thought would be great to have, but any other way to get a row size would be good enought. This could be both Data and Index size for each row, but data would be good enought. There is anyway to get this information? Thanks, mpneves -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.2 alpha crashes with Query Browser
Francisco, Maybe it's my setup... but: whenever I try to login to use the mySQL query browser, it instantlly turns off the 5.0.2 alpha mySQL instance (stops running). the error I get is: mysqld-max-nt.exe - Application Error the instruciton at 0x00538d34 referenced memory at 0x007f9000. The memory could not be read. Running Win2k pro w/ 512mb of ram. 5.0.2 will crash on pretty much everything. It does seem that starting the server works, but that's about it :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ROW_SIZE or something alike
Marco Neves wrote: I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). You might try using the Data_length and Index_length values from SHOW TABLE STATUS, divided by the number of rows. http://dev.mysql.com/doc/mysql/en/show-table-status.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
use of 'if()' function in 'order by' clause...
Hi all, Using mysqld (mysql Ver 12.22 Distrib 4.0.18, for pc-linux (i686)) and have come across some funky SQL code that appears to work occassionally - maybe, depending on the sql client I'm using, but I'm not sure why/not. I'm using mysql from the command line client, as well as a jdbc client (Netbeans), and MySQL Control Center Center client. I read you can use 'order by null' if you don't want MySQL to do its default ordering of 'group by' results, but this 'order by if( some_condition, scalar1, scalar2 )' stuff doesn't seem correct to me. I understand that the following works, and it makes sense to me: mysql select fname, lname - from employee - order by 2; Works. This will order by the 2nd column, lname. Great. But should the following work? I would think not, and it doesn't seem to, but not everyone is convinced: mysql select fname, lname - from employee - order by if(fname is not null, 1, 2); Works. Well, it executes, but it does not order by the first or second column, just the natural order of the table it seems. One added feature is that our 'order by if' function looks like this: - order by if(fname is null, 0, 1); Doing an 'order by 0' produces an error when not inside an 'if' function, but inside, it seems to be effectively ignored. I'm a bit confused right now on what the behavior of this thing is, but it *seems* to me like this function is *not* working at all - that is, the use of the if(whatever_you_want,blah,blah)' seems to have no effect whatsoever on the ordering of the query results. Can someone shed some light? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ROW_SIZE or something alike
oix ppl, thanks a lot for the answers, but if it was this simple I wouldn't ask... Yes, I am Core Certificate, and yes I did read the show-table-status page. BUT, that don't satisfy my needs. The sugestion to use data_length divided by the number of rows is good if all rows have a fixed length, that don't happen if there is one or more text/blob field in the table, in which case the row size is diferent depending in that field(s) content. Any other ideia? Thanks, mpeves On Thursday 17 March 2005 21:37, Keith Ivey wrote: Marco Neves wrote: I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). You might try using the Data_length and Index_length values from SHOW TABLE STATUS, divided by the number of rows. http://dev.mysql.com/doc/mysql/en/show-table-status.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- Merlin, the Mage www.code.online.pt www.cultodavida.online.pt Carpe Vitam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem: Slow LOAD FILE performance with innodb
Hi list, I'm trying to import some data into a MySQL database. The data-file is 5.5G, and consist of about 132 million rows. The machine is a P4 3GHz with 1G RAM and a single 250 GB ATA-disk for data. The operating system is Debian GNU Linux testing with kernel 2.6.8 with hyperthreading support. The version of MySQL is 4.1.10. The problem is that the import takes forever. I aborted the import after about 8 hours. When the import starts, the performance is about 20.000 rows/sec. After about 20 million rows, the performance has decreased to around 2000 rows/sec. The CPU is 40 - 50% idle. Is there a way to boost the performance? The database is idle, and while importing, performance is the key priority (record-locking, transaction-rollbacks and file-integrety/crash-recovery are not required until the data are imported). I've done some googling, and I've seen the problem described, but I've not found any solutions. The performance improves a litte with innodb_flush_method = O_DSYNC; but an initial import of a database does not really need to flush until done. DROP TABLE IF EXISTS `TestTable`; CREATE TABLE `TestTable` ( `KeyId`BIGINT AUTO_INCREMENT NOT NULL, `Id` bigint NOT NULL default '0', `XId` int NOT NULL DEFAULT '1', `YId` int NOT NULL default '0', `Date` datetime NOT NULL default '-00-:00:00', `Dtm` int(11) NOT NULL default '0', `Ct` char(3) default NULL, PRIMARY KEY (`KeyId`), KEY ix_id(Id), KEY ix_anlegg(`XId`, `YId`, `Date`), KEY ix_dato(`Date`, `XId`, `YId`), KEY ix_kw(`XId`, `YId`, `Dtm`) ) ENGINE=InnoDB MAX_ROWS=100 ROW_FORMAT=FIXED DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; -- Import data LOAD DATA INFILE '/home/jgaa/data/x.txt' INTO TABLE TestTable FIELDS TERMINATED BY '\t' enclosed by '' LINES TERMINATED by '\r\n' IGNORE 1 Lines (Id, XId, Date, Dtm, Ct); From my.cnf: innodb_data_home_dir=/data001/innodb innodb_data_file_path=innodata001:128G:autoextend set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=120M set-variable = innodb_log_file_size=1G set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=0 innodb_flush_method = O_DSYNC Jarle -- Jarle Aase email: [EMAIL PROTECTED] Author of freeware. http://www.jgaa.com news:alt.comp.jgaa War FTP Daemon: http://www.warftp.org War FTP Daemon FAQ: http://www.warftp.org/faq/warfaq.htm Jgaa's PGP key: http://war.jgaa.com/pgp NB: If you reply to this message, please include all relevant information from the conversation in your reply. Thanks. no need to argue - just kill'em all! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem: Slow LOAD FILE performance with innodb
My first guess is the indexes. Maybe create them after the import. It will nonetheless take a bit of time! --Original Message-- From: Jarle Aase To: MySQL list Sent: Mar 17, 2005 11:53 PM Subject: Problem: Slow LOAD FILE performance with innodb Hi list, I'm trying to import some data into a MySQL database. The data-file is 5.5G, and consist of about 132 million rows. The machine is a P4 3GHz with 1G RAM and a single 250 GB ATA-disk for data. The operating system is Debian GNU Linux testing with kernel 2.6.8 with hyperthreading support. The version of MySQL is 4.1.10. The problem is that the import takes forever. I aborted the import after about 8 hours. When the import starts, the performance is about 20.000 rows/sec. After about 20 million rows, the performance has decreased to around 2000 rows/sec. The CPU is 40 - 50% idle. Is there a way to boost the performance? The database is idle, and while importing, performance is the key priority (record-locking, transaction-rollbacks and file-integrety/crash-recovery are not required until the data are imported). I've done some googling, and I've seen the problem described, but I've not found any solutions. The performance improves a litte with innodb_flush_method = O_DSYNC; but an initial import of a database does not really need to flush until done. DROP TABLE IF EXISTS `TestTable`; CREATE TABLE `TestTable` ( `KeyId`BIGINT AUTO_INCREMENT NOT NULL, `Id` bigint NOT NULL default '0', `XId` int NOT NULL DEFAULT '1', `YId` int NOT NULL default '0', `Date` datetime NOT NULL default '-00-:00:00', `Dtm` int(11) NOT NULL default '0', `Ct` char(3) default NULL, PRIMARY KEY (`KeyId`), KEY ix_id(Id), KEY ix_anlegg(`XId`, `YId`, `Date`), KEY ix_dato(`Date`, `XId`, `YId`), KEY ix_kw(`XId`, `YId`, `Dtm`) ) ENGINE=InnoDB MAX_ROWS=100 ROW_FORMAT=FIXED DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; -- Import data LOAD DATA INFILE '/home/jgaa/data/x.txt' INTO TABLE TestTable FIELDS TERMINATED BY '\t' enclosed by '' LINES TERMINATED by '\r\n' IGNORE 1 Lines (Id, XId, Date, Dtm, Ct); From my.cnf: innodb_data_home_dir=/data001/innodb innodb_data_file_path=innodata001:128G:autoextend set-variable = innodb_buffer_pool_size=512M set-variable = inno - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem: Slow LOAD FILE performance with innodb
Jarle Aase schrieb: Is there a way to boost the performance? The database is idle, and while importing, performance is the key priority (record-locking, transaction-rollbacks and file-integrety/crash-recovery are not required until the data are imported). - Disable the foreign key checks (SET FOREIGN_KEY_CHECKS = 0) - Create the indices at the end of the import Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installation
Hi: I have installed MySQL on two Windows XP PC systems. One system, the one at school is not thoroughly tested but it appears to work. The other one at home yields an error when the Administrator is run. I downloaded the zip version of MySQL It is 36773 KB and all evidence indicates it is version 4.1.10, the directories etc. I then install the msi version of Administrator version 1.0.19, 4815 KB and I receive an error stating that the Administrator is not compatible with version 3.2.3 of MySQL. I am wondering if the Administrator is packaged with version 3.2.3 of MySQL or where this version is coming from. If version 3.2.3 is installed directories and add/delete programs should also have the verion number displayed. Directories should indicate what version was unzipped. I have tried this 2 or 3 times.In addition the Control Panel Add/Remove programs indicates version 4.1 of MySQL. Ken Hundzinski
bdb-no-sync
What are the implications of setting the --bdb-no-sync flag when using the BDB storage engine. I haven't found alot of documentation on this in the docs or via google. Does this mean that after every transaction, sync will not be called? When will sync be called then? Will setting this flag lead to ACI semantics, ie no durability? If so, when will transactions become durable? Will setting this flag increase the likelyhood of DB corruption? As happens in Postgresql with fsync off. I know in BDB (non MySQL storage engine), setting DB_TXN_NOSYNC on a BDB transaction leads to ACI semantics. It is then up to the app to call DB-sync when it wants durability. How does this map to the BDB/MSQL storage engine? Thanks for any help, Jaco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem: Slow LOAD FILE performance with innodb
Hi! Creating the indexes after the import will only slow down the operation. MySQL recreates the whole table at CREATE INDEX. We are working on a fast index create in MySQL-5.1. Until then, the fastest way is to import the rows to a table where indexes have already been created, just like you have been doing. If you want to import 132 million rows, and the speed is 2000 rows / second, then the import takes 64 000 seconds = 18 hours. Why not wait over the night? Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: Peter J Milanese [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, March 18, 2005 1:00 AM Subject: Re: Problem: Slow LOAD FILE performance with innodb My first guess is the indexes. Maybe create them after the import. It will nonetheless take a bit of time! --Original Message-- From: Jarle Aase To: MySQL list Sent: Mar 17, 2005 11:53 PM Subject: Problem: Slow LOAD FILE performance with innodb Hi list, I'm trying to import some data into a MySQL database. The data-file is 5.5G, and consist of about 132 million rows. The machine is a P4 3GHz with 1G RAM and a single 250 GB ATA-disk for data. The operating system is Debian GNU Linux testing with kernel 2.6.8 with hyperthreading support. The version of MySQL is 4.1.10. The problem is that the import takes forever. I aborted the import after about 8 hours. When the import starts, the performance is about 20.000 rows/sec. After about 20 million rows, the performance has decreased to around 2000 rows/sec. The CPU is 40 - 50% idle. Is there a way to boost the performance? The database is idle, and while importing, performance is the key priority (record-locking, transaction-rollbacks and file-integrety/crash-recovery are not required until the data are imported). I've done some googling, and I've seen the problem described, but I've not found any solutions. The performance improves a litte with innodb_flush_method = O_DSYNC; but an initial import of a database does not really need to flush until done. DROP TABLE IF EXISTS `TestTable`; CREATE TABLE `TestTable` ( `KeyId`BIGINT AUTO_INCREMENT NOT NULL, `Id` bigint NOT NULL default '0', `XId` int NOT NULL DEFAULT '1', `YId` int NOT NULL default '0', `Date` datetime NOT NULL default '-00-:00:00', `Dtm` int(11) NOT NULL default '0', `Ct` char(3) default NULL, PRIMARY KEY (`KeyId`), KEY ix_id(Id), KEY ix_anlegg(`XId`, `YId`, `Date`), KEY ix_dato(`Date`, `XId`, `YId`), KEY ix_kw(`XId`, `YId`, `Dtm`) ) ENGINE=InnoDB MAX_ROWS=100 ROW_FORMAT=FIXED DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; -- Import data LOAD DATA INFILE '/home/jgaa/data/x.txt' INTO TABLE TestTable FIELDS TERMINATED BY '\t' enclosed by '' LINES TERMINATED by '\r\n' IGNORE 1 Lines (Id, XId, Date, Dtm, Ct); From my.cnf: innodb_data_home_dir=/data001/innodb innodb_data_file_path=innodata001:128G:autoextend set-variable = innodb_buffer_pool_size=512M set-variable = inno - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Authenticating a User to MySQL
I just installed MySQL 4.1 on Mac OS X Panther Client. I am now having trouble authenticating users(root, admin) to MySQL. I am able to start MySQL but not using a specific user name and password. When I type ./mysql in /usr/local/mysql/bin, I am able to access MySQL. However, I have no privileges this way. However, when I try the following: ./mysql -u root -p password I get an error that states ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Any help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
