Re: Importing Text Files in phpMyAdmin vs the Infamous
I have a php website connecting to mysql database.
How can i have a user export his database through
the webpage to csv format?
I would like the user to have a button he can press
that would pop up a
save as screen so he can save his database.
The PHP code to do this would look something like this:
// -- Do not send any HTML before this point -- //
header(Content-type: application/vnd.ms-excel);
header('Content-Disposition: attachment; filename=myfile.xls');
header(Content-Transfer-Encoding: binary);
$r1 = // -- do query here -- //;
$nf = mysql_num_fields($r1);
for ($i=0; $i$nf; $i++) {
$x = mysql_field_name($r1,$i);
echo '' . str_replace('','',htmlspecialchars($x)) . '\t';
}
echo \n;
while ($a1 = mysql_fetch_array($r1)) {
for ($i=0; $i$nf; $i++) {
$x = $a1[$i];
echo '' . str_replace('','',htmlspecialchars($x)) . '\t';
}
echo \n;
}
Hope this helps.
- seb
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Saving PDF's as Blobs
What SQL statement can I use to insert a PDF into a BLOB column? I don't want a pointer to the file, I want the actual file stored as a BLOB. Thanks! Lou
Re: Saving PDF's as Blobs
Lou Olsten wrote:
What SQL statement can I use to insert a PDF into a BLOB column? I don't want a pointer to the file, I want the actual file stored as a BLOB.
If the file is on the server already -
insert into pdfs (content) values(load_file('/path/to/file.pdf'))
If the file is on the client, use some client language (C,Perl,PHP), call
mysql_real_escape_string() on the contents of the file and insert the escaped
string.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting to db via win32 (Word or Excel)
Thomas Nyman wrote: Hi all I'm a mysql beginner. I have my database up and running and can query it via php and apache. I would like to set things up so that MSExcel and or MSWord can query the database and extract info from it. For this reason I downloaded the and installed MyODBC-standard-3.51.04-win.exe. No errors where generated upon install on my W2K machine. But when I look in Datasources ODBC under administrative tools there is no mysql-driver installed. What am I missing? Is there something else that needs to be installed? Thanks Thomas You need to configure a datasource. All you've done so far is install the drivers ( and old ones at that ). In the administrative tools, create a new System DSN. If you need help with this, have a look at my page on it: http://enthalpy.homelinux.org/MySQL/setup.html Also note that using MS Query from Word or Excel ( or anything else ) will *not* work well with MySQL. MS Query seems to be quite buggy. The only reliable way of talking to MySQL from Word or Excel is to use ADO. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fastest filesystem for MySQL
I've heard and read that the Reiser filesystem should be better for MySQL than Ext3. Is this still true? We will be running MySQL on either Red Hat ES 3, Suse or Debian. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: questions about bind_param and mysql trace data included
Try this:
On Tue, May 11, 2004 at 12:51:28PM -0700, [EMAIL PROTECTED] wrote:
below I have a snippet from a trace file read out for a section of code below. The
problem seems to be with '$sth-bind_param (2,$rh_row-{prop_str_addr});' The first
Bind works fine however the next one in the line listed produces a null entry per
the trace file. I have verified that the data is correct in
'$rh_row-{prop_str_addr}' and that it is quoted but for some reason myql isn't
recieving the data any help would be appreciated.
- dbd_st_execute 0 rows
- execute= '0E0' at db_load_1-2.pl line 128
- bind_param for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c 1
''310-673-5515'')
- bind_param= 1 at db_load_1-2.pl line 122
- execute for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c)
- dbd_st_execute for 08104e48
Binding parameters: UPDATE own_info
SET own_phone_home = '\'310-673-5515\''
WHERE own_str_addr = NULL
###CODE SNIPPETTE
$dbh = connect_try(**,**);
foreach $k (keys (%{$ar_info-[1]})){ # retrieves a generic set of fields and
uses them to assign values for each row.
if ($table eq prop_info){ # checks which table is being used and assigns
the correct SQL statement
$sth = $dbh-prepare (UPDATE prop_info
SET $k = ?
WHERE prop_str_addr = ?) or
err_trap(failed to prepare statement\n);
}elsif ($table eq own_info){
$sth = $dbh-prepare (UPDATE own_info
SET $k = ?
WHERE own_str_addr = ?) or
err_trap(failed to prepare statement\n);
}
$sth-trace(2, ./trace_data.txt);
foreach $rh_row (@$ar_info) { # iterates through the list of rows and
assigns the correct value to the field
print ::$k=$rh_row-{$k}; # this is an internal check to verify what
values are being inserted
$sth-bind_param (1,$rh_row-{$k});
if($table eq 'prop_info') {
if ($table eq prop_str_addr) {
$sth-bind_param (2,$rh_row-{prop_str_addr});
}elsif ($table eq own_str_addr) {
$sth-bind_param (2,$rh_row-{own_str_addr});
}
$sth-execute() or
err_trap(failed to execute statement\n);
}
print \n===\n;
}
$sth-finish();
$dbh-disconnect or
err_trap(failed to disconnect statement\n);
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response
--
Brad Lhotsky [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
Completely depends on your situation -- big files, small files? Lots of writes or reads or both? Growing tables/files or lots of large dropped tables? If you're into small files, go Reiser. Big data, JFS or XFS. EXT3 is slow, but, IIRC, it also is a true data journaling filesystem while some of the others aren't (and that's as deep as I go without a life-vest, if you know what I mean!). Just saw this on LWN.net: Benchmarks of EXT2, EXT3, JFS, XFS in various unreal stressful scenarios. Interesting, anyway: http://209.81.41.149/~jpiszcz/index.html Robert J Taylor [EMAIL PROTECTED] JFL wrote: I've heard and read that the Reiser filesystem should be better for MySQL than Ext3. Is this still true? We will be running MySQL on either Red Hat ES 3, Suse or Debian. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
I've heard and read that the Reiser filesystem should be better for MySQL than Ext3. Is this still true? We will be running MySQL on either Red Hat ES 3, Suse or Debian. Completely depends on your situation -- big files, small files? Lots of writes or reads or both? Growing tables/files or lots of large dropped tables? It's for a community site. Lots of small data reads, less writes, many updates, and few deletes. If you're into small files, go Reiser. Big data, JFS or XFS. EXT3 is slow, but, IIRC, it also is a true data journaling filesystem while some of the others aren't (and that's as deep as I go without a life-vest, if you know what I mean!). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
Robert J Taylor wrote: Completely depends on your situation -- big files, small files? Lots of writes or reads or both? Growing tables/files or lots of large dropped tables? If you're into small files, go Reiser. Big data, JFS or XFS. EXT3 is slow, but, IIRC, it also is a true data journaling filesystem while some of the others aren't (and that's as deep as I go without a life-vest, if you know what I mean!). Based on what I've seen so far, JFS and XFS do not yet have a solid track record of stability with MySQL. This does not mean they could not be good - I just do not trust them yet. I do vaguely remember a support case when a very strange corruption happened on either one of them - now cannot recall which one. In 3 years of handling MySQL supoprt (2000-2003), I do not recall reports of table corruption on a ReiserFS file system. I do remember corruption cases in the early ext3. If you are using InnoDB, the choice of the filesystem should not be that critical because of full caching. For MyISAM, having a good file system is very important - unlike InnoDB, MyISAM does not cache the data itself, and relies on the OS cache. So you do have a lot of read/write syscalls. To illustrate the difference - I recall a case when performance on MyISAM was terrible over NFS (well, that is to be expected), but once the table was changed to InnoDB, it improved drastically. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting Varchar
How do I set up a column (cost) that contains numbers and text so that the numbers will sort the numbers accurately? Using varchar results in a sort based on the first digit, so that I get e.g. 1, 10, 100, 3. . . when the command is order by cost. Almost all of the text is By County. Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Varchar
[EMAIL PROTECTED] wrote: How do I set up a column (cost) that contains numbers and text so that the numbers will sort the numbers accurately? Using varchar results in a sort based on the first digit, so that I get e.g. 1, 10, 100, 3. . . when the command is order by cost. Almost all of the text is By County. Ken: Consider having two columns - num_val, and text_val, and then order by num_val, text_val -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow login
Jiri, Gerald said it could be one of two things. You've just said it's not the first. Think some more about the second (reverse DNS)... If you don't have the authority/ability to configure DNS in your environment, add the systems to each other's hosts files (C:\WINNT\system32\drivers\etc\hosts on W2K) and try again - a reboot may be required; hey, this is M$. Connecting by IP addresses does not mean that the underlying mechanisms won't go through a host resolution process. If that doesn't eleviate things, consider checking the network statistics for errors (netstat -e on W2K) and approach it from that angle; verifying proper speed/duplex configuration between your hosts and networking equipment. As a last resort, you can look through your OS (eventvwr on W2K) and/or MySQL's log files for indications of the problem. Good luck, Roy -- Date: Tue, 11 May 2004 08:47:13 +0200 To: [EMAIL PROTECTED] From: Jiri Matejka [EMAIL PROTECTED] Subject: Re: Slow login Message-ID: [EMAIL PROTECTED] Unfortunatelly it isn't true in my case. I connect to database server in local network and I use IP address, so there is no DNS usage... Jiri Matejka - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Jiri Matejka [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, May 10, 2004 3:36 PM Subject: Re: Slow login Slow connections are ususally a DNS or reverse DNS problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
check for certain characters
hi, I have a field which is a genome sequence and I need to check if each of the entries made for the sequence field contains only a,t,c or g in the string and no other characters. how will i give the query??? thanks, liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: check for certain characters
Off of the top of my head you can basically do a combination of all letters in big or (use IN) list. It should be pretty fast. I'm personally leaning to using REGEXP in mySQL yet, that would match the letters in a string and not exclude others, unless explicitly told to. Using a REGEXP is slow. -- DVP -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 5:42 PM To: [EMAIL PROTECTED] Subject: check for certain characters hi, I have a field which is a genome sequence and I need to check if each of the entries made for the sequence field contains only a,t,c or g in the string and no other characters. how will i give the query??? thanks, liz -- 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: check for certain characters
At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote: hi, I have a field which is a genome sequence and I need to check if each of the entries made for the sequence field contains only a,t,c or g in the string and no other characters. how will i give the query??? It depends. You can find matching values with WHERE col_name REGEXP '^[atcg]+$' but you don't say what you want for output, so beyond that it's difficult to say what the rest of the query should look like. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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: check for certain characters
the output of the query should be: all the records that contain even one letter other than a,t,c or g. Liz Quoting Paul DuBois [EMAIL PROTECTED]: At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote: hi, I have a field which is a genome sequence and I need to check if each of the entries made for the sequence field contains only a,t,c or g in the string and no other characters. how will i give the query??? It depends. You can find matching values with WHERE col_name REGEXP '^[atcg]+$' but you don't say what you want for output, so beyond that it's difficult to say what the rest of the query should look like. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: check for certain characters
Then you could add NOT to Paul's query: SELECT * FROM your table WHERE sequence NOT REGEXP '^[atcg]+$'; or, equivalently, SELECT * FROM your table WHERE sequence REGEXP '[^atcg]'; I suspect the latter may be faster, but you'd have to try them to be sure. Note that pattern matching in mysql is case-insensitive by default. If that matters to you, then you would need to add the BINARY keyword to the WHERE clause: WHERE sequence NOT REGEXP BINARY '^[atcg]+$'; or WHERE sequence REGEXP BINARY '[^atcg]'; Michael [EMAIL PROTECTED] wrote: the output of the query should be: all the records that contain even one letter other than a,t,c or g. Liz Quoting Paul DuBois [EMAIL PROTECTED]: At 21:41 -0400 5/11/04, [EMAIL PROTECTED] wrote: hi, I have a field which is a genome sequence and I need to check if each of the entries made for the sequence field contains only a,t,c or g in the string and no other characters. how will i give the query??? It depends. You can find matching values with WHERE col_name REGEXP '^[atcg]+$' but you don't say what you want for output, so beyond that it's difficult to say what the rest of the query should look like. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: check for certain characters
At 23:51 -0400 on 05/11/2004, Michael Stassen wrote about Re: check for certain characters: Then you could add NOT to Paul's query: SELECT * FROM your table WHERE sequence NOT REGEXP '^[atcg]+$'; or, equivalently, SELECT * FROM your table WHERE sequence REGEXP '[^atcg]'; I suspect the latter may be faster, but you'd have to try them to be sure. Note that pattern matching in mysql is case-insensitive by default. If that matters to you, then you would need to add the BINARY keyword to the WHERE clause: WHERE sequence NOT REGEXP BINARY '^[atcg]+$'; or WHERE sequence REGEXP BINARY '[^atcg]'; Michael The need to go BINARY to detect case also requires that sequence be a BLOB not a TEXT field (I might have the case-sensitive/case-insensitive types reversed), -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow login
Unfortunatelly it isn't true in my case. I connect to database server in local network and I use IP address, so there is no DNS usage... Jiri Matejka - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Jiri Matejka [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, May 10, 2004 3:36 PM Subject: Re: Slow login Slow connections are ususally a DNS or reverse DNS problem. Jiri Matejka wrote: Hi, I'm using MySQL 3.23.53 on W2000 and I have a following problem: when I restart the database server then the first attempt to connect from any client program last cca 30 seconds (too long!!), each next attempt to connect lasts less than one second. The traffic is usually very small, so it can't be caused by it. I tried several client applications and all behave in the same way, so I guess the problem is inside the database. Can anybody help me? Thanks Jiri Matejka, [EMAIL PROTECTED] -- 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: NDB Cluster configuration
Hi, It sounds as if you if you have some problem with the configuration file. Have you introduced the second computer and its hostname into the configuration file? Also the node on the second computer need to refer to this second computer. If your problem continue please attach the configuration file. Rgrds Mikael 2004-05-11 kl. 02.43 skrev J Brian Ismay: I am trying to setup an NDB Cluster database for testing purposes. I have no problems getting things to work when all of my DB and API nodes are located on one machine. I run into trouble when I try to have one machine run the management server, a db node, and an API node, while the second machine runs another db node. The db node on the second machine appears to talk to the management node and seems to start properly, but the mgmtclient never shows it as actually being a part of the cluster. The same behavior is observed when trying to start an API node on a second machine. Can anyone help? J Brian Ismay [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Mikael Ronström, Senior Software Architect MySQL AB, www.mysql.com Clustering: http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html http://www.eweek.com/article2/0,1759,1567546,00.asp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange DBI error
Hi,
I am using DBI from mod_perl, and sometimes get the following error:
You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near '-63, 20' at line 1
This is given me right at:
eval {
$dbh =
DBI-connect(DBI:mysql:database=$db;host=$host:$port, $user, $pass,
{'RaiseError' = 1} );
};
($db, $host, $user, $port and $pass are constants.)
I am using mysql Ver 12.22 Distrib 4.0.17, for pc-linux (i686) on
RedHat 9.
Any ideas?
Thank you,
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to dump data in db.table syntax?
Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design.. Asking again
Man 3 times same thread ! What I could consider to you, I don't really understand what you are getting at, what is wrong with 1000 users firstly ? And in the entry table store their userID which is stored in a session when they login ? So when they enter in data it stores their userID into a column , is that right ? userID = autoinc userID in the user table -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 2:50 PM To: MySql Subject: Database design.. Asking again Sorry for the post again, I hijacked a thread and wanted to get this on the correct track. I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. If users become day 1000 and each of those users has 70,000 user_contacts, that would be 70,000 * 1000 total records in one table, as users grow, this becomes perhaps too many records in one table. Or at least the potential for it. My next option would be to make a new table, user_contact-userid and make one for each user, would would then mean, rather than one table with a lot of records in it, there would be many tables with a max of 100,000 records in it. Can someone share with me their thoughts and suggestions on this? If anyone thinks I should just allow one table to store all this, with that table having 70 million records in it, then I can of course go that road as well. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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]
timestamps and timezones
I see that LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW() when I use now() I get the database time but I have a webhost based in US (Pacific Time), however most database events will be generated from BST and this is the time zone I want to use to populate audit trail tables etc. I could programmatically compensate the US time zone by adding the hours to make up the BSTis there any neater way of doing this, as this solution would involve some hard coding. Kind regards Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connecting to db via win32 (Word or Excel)
Hi all I'm a mysql beginner. I have my database up and running and can query it via php and apache. I would like to set things up so that MSExcel and or MSWord can query the database and extract info from it. For this reason I downloaded the and installed MyODBC-standard-3.51.04-win.exe. No errors where generated upon install on my W2K machine. But when I look in Datasources ODBC under administrative tools there is no mysql-driver installed. What am I missing? Is there something else that needs to be installed? Thanks Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design.. Asking again
Scott Haneda wrote: I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. If users become day 1000 and each of those users has 70,000 user_contacts, that would be 70,000 * 1000 total records in one table, as users grow, this becomes perhaps too many records in one table. Or at least the potential for it. My next option would be to make a new table, user_contact-userid and make one for each user, would would then mean, rather than one table with a lot of records in it, there would be many tables with a max of 100,000 records in it. Can someone share with me their thoughts and suggestions on this? If anyone thinks I should just allow one table to store all this, with that table having 70 million records in it, then I can of course go that road as well. Another thing you may want to also consider when using huge databases (10M+); is the amount of time for searching and the overhead that would also cause. A with any search, regardless of the size of the database, it will take a small amount of time. Sure that amount of time would be around a couple seconds if even that. While a 10M record database could take significantly more. Which most likely that amount, may be even closer to about a minute or more. Now I don't know if a delay for searching the database would be a critical thing or not for you. Now of course, these time amounts won't be the same for every machine; nor are these time amounts be exact. Now if your are not in a time critical area, then you most likely won't care about this. Though, you may want to just do a simple query through a table, on your worst case environment; which from what you say may be up to about 70M records,and get an idea how much time it would take. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max
Hi, A field of type VarChar() with following syntax: ABA1. How to get the Max value. Bearing in mind that value ABA10 comes before ABA2. regards Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? No, mysqldump doesn't have such option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design
I'm not sure what the maximum number of tables are per database (512? 1024?), but I wouldn't split your data unless you really have to. 70 million records may seem like a lot for a table, but it's really not. Especially if the physical size of your data is small. Tables with billions of records is not unusual for MySQL. The physical size of the table is limited by the operating system you are running it on, which may mean 4GB. MySQL itself can handle terabyte sized tables. You can also look into using InnoDB as your table type, which would allow you to split your database into separate files without the need to change any of your programming to reference the data. On May 10, 2004, at 7:30 PM, Scott Haneda wrote: I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. If users become day 1000 and each of those users has 70,000 user_contacts, that would be 70,000 * 1000 total records in one table, as users grow, this becomes perhaps too many records in one table. Or at least the potential for it. My next option would be to make a new table, user_contact-userid and make one for each user, would would then mean, rather than one table with a lot of records in it, there would be many tables with a max of 100,000 records in it. Can someone share with me their thoughts and suggestions on this? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? No, mysqldump doesn't have such option. it's a pity :( Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem running MySQL on MacOS X.3
If you don't have a process mysqld running, then mysql isn't running. You may try starting up mysql directly (just type mysqld), rather than through the startup script or through mysqld_safe, since they suppress the error messages. You should then see some error message that may help determine why mysql isn't running. Also, to access mysql, you should provide user name and password. You won't be able to access it if you don't, unless it's configured that way. mysql -u username -p Keep in mind that the username and password are not related to the username and password in the OS. Many people trying to run it on a Mac miss this point. You may also try running the repair disk permissions from Apple's Disk Utility and then installing MySQL again. In the past there had been privilege issues on certain folders that prevented MySQL from running or installing correctly. That said, I've been running MySQL on a Mac for quite some time without a problem. So if will run. There are some known issues with running 4.1 on a Mac. On May 10, 2004, at 5:21 PM, Tim Jarman wrote: I have an iBook G4 running MacOS X.3 on Darwin 7.0. I downloaded and ran the binary installer (mysql-standard-4.0.18.pkg and it appeared to work fine; I have /usr/local/mysql and so on as per the docs. I also installed MySQLStartupItem. However, I don't actually appear to have a functional installation. If I do: /usr/local/mysql/bin/mysql or even sudo /usr/local/mysql/bin/mysql I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) although /tmp/mysql.sock does exist, which seems to indicate the server is running. But then again ps -a -x doesn't seem to show it, so maybe it isn't; if I do ps -A |fgrep mysql on my Linux box, where MySQL is running happily, I get a hit for mysqld_safe plus ten others for mysqld. I tried starting the server manually as suggested in the docs: sudo /Library/StartupItems/MySQL/MySQL start Starting MySQL database server but when I try running mysql I get error 2002 as before. I found mention of this problem on FAQTS and in the MySQL mailing lists archive, the latter of which suggested this email address. Any clues would be most welcome! Please cc any replies to me as I am not currently subscribed to any of the MySQL lists. Thanks in advance, Tim Jarman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Mysqld Stability and maintanability
We have been running two 3.23.47 and one 4.0.4 (innodb) on a single machine the past 2 years. We haven't had stability or any ongoing problems. If something happens to one instance, it has not affected any other. The main issue is resource allocation. Also be sure that the different instances do not share the same directories, tmp space as well. John Winner H Manurung wrote: Dear All, I was an Oracle dba, now my new company want to use Mysql 4.0.18. Does anybody here has experience of running multiple mysqld (i.e. multiple instance on one machine). Is it stable and totally independent to each other? Thank you for your answer Winner Mau dapat hadiah jutaan rupiah, ikuti game dan quiz-nya di http://www.m-stars.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Validation/Linking Table Question
Bart, One other thing... based on your description of your needs, you seem to have a one-to-many relationship between computers and users. In that case, there wouldn't be a need for the intersection table comp_user_link. You could simply add a user_id column to your computers table, make it allow nulls (for computers that don't have users), then your query would look like this and you would have eliminated the overhead of an extra table. select * from users left join computers on (users.user_id = computers.user_id) where computers.user_id is NULL; Lou - Original Message - From: Bart Nessux [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 10, 2004 8:07 PM Subject: Re: Validation/Linking Table Question Thanks Lou, I'll give this a go! From: Lou Olsten [EMAIL PROTECTED] Reply-To: Lou Olsten [EMAIL PROTECTED] To: Bart Nessux [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: Validation/Linking Table Question Date: Mon, 10 May 2004 17:52:38 -0400 Assuming that your pre 4.1, meaning that you can't use a subquery this will do it, BUT comp_id must be set to allow NULLS for this to work otherwise the optimizer will handle it differently (http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimisation.html). It is legal to create a primary key on a column that allows nulls, so it's just a question of whether or not you want to do that. select * from users left join comp_user_link on (users.user_id = comp_user_link.user_id) where comp_user_link.comp_id is null; - Original Message - From: Bart Nessux [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 10, 2004 1:38 PM Subject: Validation/Linking Table Question I have three tables in a MySQL DB... among others: 1. computers 2. users 3. comp_user_link This particular DB is used for inventory and tracking purposes. I think the table names are self explanatory. Computers has 'comp_id'... users has 'user_id' as their primary indexed keys. These keys are linked together in the comp_user_link table. A user may have more than one computer (For example, Bob may have a desktop and a laptop at the same time), but no computer may be linked to more than one user simultaneously (Bob and Tom should never have the same laptop at the same time). Anyway, what I'd like to do is to find all 'user_ids' that aren't linked to a computer. Any tips on how to arrange a SELECT statement to do that? Thanks, Bart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Best Restaurant Giveaway Ever! Vote for your favorites for a chance to win $1 million! http://local.msn.com/special/giveaway.asp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database structure
Hi, I´m working in a project of a database that should be grow to more than 25,000,000 of clients. For all I´ve read in MySQL manual pages it´s too much records to place in only one table. So, my main doubt is how to divide it. I divide the client table in few tables according with the different kinds of clients. Even getting some duplicated records and getting some difficulties importing and exporting clients from one table to another it should take the database load cooler. But, I think I´ll need to place all logins and access levels in the same table. Would it be a problem? Any idea how can I deal with it? I´m thinking to use InnoDB tables. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql on redhat9
I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted machine I lost access to mysql as root user. Maybe its a stupid question but I'm new at this... Did I have to do anything 'persistent' to db before rebooting. I am testing mysqld with ser (SIP Proxy) Steve
Re: How to dump data in db.table syntax?
Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on redhat9
Stephen Camilleri [EMAIL PROTECTED] wrote: I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted machine I lost access to mysql as root user. Maybe its a stupid question but I'm new at this... Did I have to do anything 'persistent' to db before rebooting. I am testing mysqld with ser (SIP Proxy) Did you get access denied error or what? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database structure
Where did you read that 25 million records would be a problem? I've heard of people with billions of records in one table. The only question would be performance, but indexes would largely take care of that. You may run into issues with the physical size of the table and the underlying OS not being able to create a large enough file (i.e. 4GB). This is a problem with the OS, not MySQL. InnoDB would allow you to work around file size limitation in the OS by splitting the database into separate files. On May 11, 2004, at 10:10 AM, Ronan Lucio wrote: Hi, I´m working in a project of a database that should be grow to more than 25,000,000 of clients. For all I´ve read in MySQL manual pages it´s too much records to place in only one table. So, my main doubt is how to divide it. I divide the client table in few tables according with the different kinds of clients. Even getting some duplicated records and getting some difficulties importing and exporting clients from one table to another it should take the database load cooler. But, I think I´ll need to place all logins and access levels in the same table. Would it be a problem? Any idea how can I deal with it? I´m thinking to use InnoDB tables. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql on redhat9
Thanks Egor.. Yes I did. I've been trying to create grant tables using /usr/bin/mysqladmin but I keep getting access denied for user root. Stephen -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: 11 May 2004 16:52 To: [EMAIL PROTECTED] Subject: Re: mysql on redhat9 Stephen Camilleri [EMAIL PROTECTED] wrote: I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted machine I lost access to mysql as root user. Maybe its a stupid question but I'm new at this... Did I have to do anything 'persistent' to db before rebooting. I am testing mysqld with ser (SIP Proxy) Did you get access denied error or what? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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: mysql on redhat9
Silly question. Are you sure it is running? as expected? P Stephen Camilleri [EMAIL PROTECTED] 05/11/2004 11:10 AM To: Mysql List [EMAIL PROTECTED] cc: Subject:RE: mysql on redhat9 Thanks Egor.. Yes I did. I've been trying to create grant tables using /usr/bin/mysqladmin but I keep getting access denied for user root. Stephen -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: 11 May 2004 16:52 To: [EMAIL PROTECTED] Subject: Re: mysql on redhat9 Stephen Camilleri [EMAIL PROTECTED] wrote: I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted machine I lost access to mysql as root user. Maybe its a stupid question but I'm new at this... Did I have to do anything 'persistent' to db before rebooting. I am testing mysqld with ser (SIP Proxy) Did you get access denied error or what? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Row level security in MySQL
Hello All, I have a requirement where I have to implement the Oracle fine grain access control functionality in MySQL 4.0.19 Any ideas will be highly appreciable that how can I implement this in MySQL. I know how to do it in Oracle but not sure if MySQL can do this. For details about Oracle fine grain access or row level security, see below mention link http://www.unix.org.ua/orelly/oracle/guide8i/ch08_01.htm http://www.unix.org.ua/orelly/oracle/guide8i/ch08_01.htm Best Regards, Sagar, Sanjeev.vcf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database structure
Here are a few examples of my tables. Table name, # of records, type, and size. The database currently has 898 million records in it and it's right over 100 gigs. Phrase49,769,178 MyISAM5.3 GB Volume9,671,996 MyISAM1.1 GB Word7,790,076 MyISAM942.2 MB WordMagic128,881,167 MyISAM6.0 GB WordMagicScores111,060,572 MyISAM7.4 GB WordWatcher44,270,528 MyISAM4.3 GB WordPhrases11,154,414 MyISAM450.9 MB WordRelated13,685,867 MyISAM2.7 GB WordRelated213,194,313 MyISAM2.6 GB WordScore68,437,613 MyISAM12.7 GB WordScoreTemp118,723,375 MyISAM25.3 GB WordSearch188,769,835 MyISAM11.5 GB WordStem15,623,221 MyISAM417.3 MB Donny -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 10:09 AM To: Ronan Lucio Cc: [EMAIL PROTECTED] Subject: Re: Database structure Where did you read that 25 million records would be a problem? I've heard of people with billions of records in one table. The only question would be performance, but indexes would largely take care of that. You may run into issues with the physical size of the table and the underlying OS not being able to create a large enough file (i.e. 4GB). This is a problem with the OS, not MySQL. InnoDB would allow you to work around file size limitation in the OS by splitting the database into separate files. On May 11, 2004, at 10:10 AM, Ronan Lucio wrote: Hi, I´m working in a project of a database that should be grow to more than 25,000,000 of clients. For all I´ve read in MySQL manual pages it´s too much records to place in only one table. So, my main doubt is how to divide it. I divide the client table in few tables according with the different kinds of clients. Even getting some duplicated records and getting some difficulties importing and exporting clients from one table to another it should take the database load cooler. But, I think I´ll need to place all logins and access levels in the same table. Would it be a problem? Any idea how can I deal with it? I´m thinking to use InnoDB tables. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Row level security in MySQL
At 10:16 -0500 5/11/04, Sagar, Sanjeev wrote: Hello All, I have a requirement where I have to implement the Oracle fine grain access control functionality in MySQL 4.0.19 Any ideas will be highly appreciable that how can I implement this in MySQL. I know how to do it in Oracle but not sure if MySQL can do this. It cannot. You must implement this in your application. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
[PATCH] for mysqldump: full insert statements (db.table) with -c
Hi,
with this patch if you use mysqldump with -c the db name will precede the
table name.
Hopefully this will solve the replication problem I described earlier.
ico
--- mysqldump.c.orig 2004-02-10 19:15:59.0 +0100
+++ mysqldump.c 2004-05-11 17:33:16.407884792 +0200
@@ -670,7 +670,7 @@
}
if (cFlag)
- sprintf(insert_pat, INSERT %sINTO %s (, delayed, opt_quoted_table);
+ sprintf(insert_pat, INSERT %sINTO %s.%s (, delayed, db, opt_quoted_table);
else
{
sprintf(insert_pat, INSERT %sINTO %s VALUES , delayed,
@@ -732,7 +732,7 @@
fprintf(sql_file, CREATE TABLE %s (\n, result_table);
}
if (cFlag)
- sprintf(insert_pat, INSERT %sINTO %s (, delayed, result_table);
+ sprintf(insert_pat, INSERT %sINTO %s.%s (, delayed, db, result_table);
else
{
sprintf(insert_pat, INSERT %sINTO %s VALUES , delayed, result_table);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Row level security in MySQL
Thanks ! -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 10:28 AM To: Sagar, Sanjeev; [EMAIL PROTECTED] Subject: Re: Row level security in MySQL At 10:16 -0500 5/11/04, Sagar, Sanjeev wrote: Hello All, I have a requirement where I have to implement the Oracle fine grain access control functionality in MySQL 4.0.19 Any ideas will be highly appreciable that how can I implement this in MySQL. I know how to do it in Oracle but not sure if MySQL can do this. It cannot. You must implement this in your application. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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: mysql on redhat9
Stephen Camilleri [EMAIL PROTECTED] wrote: Thanks Egor.. Yes I did. I've been trying to create grant tables using /usr/bin/mysqladmin but I keep getting access denied for user root. mysqladmin doesn't create grant tables. Do you mean setting password for root user? If so, use -p option for command-line clients to specify password. For example: mysql -uroot -p -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: 11 May 2004 16:52 To: [EMAIL PROTECTED] Subject: Re: mysql on redhat9 Stephen Camilleri [EMAIL PROTECTED] wrote: I was running mysql 0.8.12 on a Redhat9 installation. After I rebooted machine I lost access to mysql as root user. Maybe its a stupid question but I'm new at this... Did I have to do anything 'persistent' to db before rebooting. I am testing mysqld with ser (SIP Proxy) Did you get access denied error or what? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row level security in MySQL
Sagar, Sanjeev [EMAIL PROTECTED] wrote on 11/05/2004 16:16:19: Hello All, I have a requirement where I have to implement the Oracle fine grain access control functionality in MySQL 4.0.19 Any ideas will be highly appreciable that how can I implement this in MySQL. I know how to do it in Oracle but not sure if MySQL can do this. Use the InnoDB table type, which is closely modelled on Oracle structurea and has row-level locking. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how do i encrypt the .frm file with a password
I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.cnf setup
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 7:42 AM To: Dathan Vance Pattishall Subject: RE: my.cnf setup Thanks for the feedback! I have made the changes you suggested. I do have a question about the slow query log though. I added it to my my.cnf file as = [mysqld] port= 3306 log-slow-queries = /usr/local/mysql/slowlog socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 Increase your tmp_table_size to 32M Additionally ensure that your indexes on your tables are correct. If you need some help with that send the query + table structure to the list. = Is this correct? Will it just make the file called slowlog? So far it hasnt done anything. And i did restart the server fyi. Anything you can clear up? Conner -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 10, 2004 2:23 PM To: [EMAIL PROTECTED] Subject: my.cnf setup I am running a mysql server off a Dell 2650. Dual 2.8Ghz Intel Xeon processors 1 Gig of RAM The MySQL data comes up rather slowly. Would like some suggestions on my my.cnf file if you had a minute. here is what I have | Handler_read_first | 2| | Handler_read_key | 9080 | | Handler_read_next | 35 | | Handler_read_prev | 1764 | | Handler_read_rnd | 723 | | Handler_read_rnd_next | 63575| You are not indexing your table right. The read random next is to high. Turn on the slow query log to find out what query is the problem. | Handler_rollback | | | Handler_update | | | Handler_write | 154 | | Key_blocks_used| 51 | | Key_read_requests | 13370| | Key_reads | 41 | | Key_write_requests | 67 | | Key_writes | | | Max_used_connections | 4| | Not_flushed_key_blocks | | | Not_flushed_delayed_rows | | | Open_tables| 18 | | Open_files | 38 | | Open_streams | | | Opened_tables | 24 | | Questions | 9971 | | Qcache_queries_in_cache| 173 | | Qcache_inserts | 173 | | Qcache_hits| 9665 | | Qcache_lowmem_prunes | | | Qcache_not_cached | 8| | Qcache_free_memory | 32908680 | | Qcache_free_blocks | 1| | Qcache_total_blocks| 365 | | Rpl_status | NULL | | Select_full_join | 8| | Select_full_range_join | | | Select_range | 66 | | Select_range_check | | | Select_scan| 84 | | Slave_open_temp_tables | | | Slave_running | OFF | | Slow_launch_threads| | | Slow_queries | | | Sort_merge_passes | | | Sort_range | | | Sort_rows | 723 | | Sort_scan | 16 | | Ssl_accepts| | | Ssl_finished_accepts | | | Ssl_finished_connects | | | Ssl_accept_renegotiates| | | Ssl_connect_renegotiates | | | Ssl_callback_cache_hits| | | Ssl_session_cache_hits | | | Ssl_session_cache_misses | | | Ssl_session_cache_timeouts | | | Ssl_used_session_cache_entries | | | Ssl_client_connects| | | Ssl_session_cache_overflows| | | Ssl_session_cache_size | | | Ssl_session_cache_mode | NONE | | Ssl_sessions_reused| | | Ssl_ctx_verify_mode| | | Ssl_ctx_verify_depth | | | Ssl_verify_mode| | | Ssl_verify_depth | | | Ssl_version| | | Ssl_cipher | | | Ssl_cipher_list| | | Ssl_default_timeout| | | Table_locks_immediate | 239 | | Table_locks_waited | |
RE: how do i encrypt the .frm file with a password
David I suggest you need to go back to first principles on Unix security, applications, and Mysql users. Firstly and Mysql user is not necessarily a Unix user and vica-versa. The simplest way to prevent anyone being able to access a file in Unix is simply to remove the relevant permissions from the file. If you have Mysql running under the user mysql and you set the permissions as 700 (rwx --- ---) on the database directories and files then only the mysql user, which the mysqld deamon runs as has permission to access them. If it's simply a case of protecting tables then you just need to grant the appropriate permissions to that Mysql user. Removing update privilege means they can't change anything, removing delete privilege means they can't remove anything, removing insert privilege means they can't add anything, and removing select privilege means they can't access the table. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: David Crane [mailto:[EMAIL PROTECTED] Sent: 11 May 2004 17:45 To: [EMAIL PROTECTED] Subject: how do i encrypt the .frm file with a password I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how do i encrypt the .frm file with a password
At 12:45 -0400 5/11/04, David Crane wrote: I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. As far as I know, this was done long ago for one specific customer and is not generally implemented. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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: how do i encrypt the .frm file with a password
This will be distributed on Windows computers and not unix/linux. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Querying rows between two strings
Hello I would like to make a question: I'm creating a list with a scripting lenguage with MySql database. The list presents username, first name and last name of persons. It has three pairs of filters for username, first name and last name. For each of them the user of the list can type a text as limit for his field with a from and until manner. I would like to create this query with regexp, but i can't make it work. An example of working in this: UsernameFirst nameLast name - alberto alberto gomez ariel ariel ramirez leandro lean123 pass123 with this filters: username from 'a' until 'le' first name from 'al' until 'lean' there should be the hole list but with this filters: username from 'a' until 'la' first name from 'al' until 'lean' there should be this list: UsernameFirst nameLast name - alberto alberto gomez ariel ariel ramirez I hope i was clear and you could help me with this. Thanks Lionel Pitaru
RE: how do i encrypt the .frm file with a password
Which OS? Doesn't 2000/2000 pro allow you to run a service as one user with similar access restrictions but allow other users to connect to the service? Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: David Crane [mailto:[EMAIL PROTECTED] Sent: 11 May 2004 18:21 To: [EMAIL PROTECTED] Subject: Re: how do i encrypt the .frm file with a password This will be distributed on Windows computers and not unix/linux. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how do i encrypt the .frm file with a password
Since this database will be distributed, our users will be administrators. If they are able to copy the files to another computer and set up mysql themselves, they could get access to the entire database directly and export any or all data. Configuring the service to run as a different user would not prevent this. And, they would have access to the files as administrator. As I see it, the passwords for users are only used by the service itself and passwords are not applied to the files themselves. As for operating systems, we are going to support Windows NT, 2000, XP. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how do i encrypt the .frm file with a password
if the data is the concern, not the data structure, why not encrypt the data itself? -Original Message- From: David Crane [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Re: how do i encrypt the .frm file with a password Since this database will be distributed, our users will be administrators. If they are able to copy the files to another computer and set up mysql themselves, they could get access to the entire database directly and export any or all data. Configuring the service to run as a different user would not prevent this. And, they would have access to the files as administrator. As I see it, the passwords for users are only used by the service itself and passwords are not applied to the files themselves. As for operating systems, we are going to support Windows NT, 2000, XP. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- 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: how do i encrypt the .frm file with a password
Exactly. Think HIPAA and the methods used to secure data from the priying eyes of DBAs (supposedly) under HIPAA/Security. Dan Greene wrote: if the data is the concern, not the data structure, why not encrypt the data itself? -Original Message- From: David Crane [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Re: how do i encrypt the .frm file with a password Since this database will be distributed, our users will be administrators. If they are able to copy the files to another computer and set up mysql themselves, they could get access to the entire database directly and export any or all data. Configuring the service to run as a different user would not prevent this. And, they would have access to the files as administrator. As I see it, the passwords for users are only used by the service itself and passwords are not applied to the files themselves. As for operating systems, we are going to support Windows NT, 2000, XP. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- 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: how do i encrypt the .frm file with a password
I was considering encrypting the data itself. However, that would impact performance and our ability to compress it. We are using access now and it is a 5 cd install. I was hoping I could get away with password protecting the files to provide some security. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem running MySQL on MacOS X.3
Did you configure MySQL and run the /usr/local/mysql/scripts/mysql_install_db to initialize your data directory? - Gabriel On May 10, 2004, at 5:21 PM, Tim Jarman wrote: I have an iBook G4 running MacOS X.3 on Darwin 7.0. I downloaded and ran the binary installer (mysql-standard-4.0.18.pkg and it appeared to work fine; I have /usr/local/mysql and so on as per the docs. I also installed MySQLStartupItem. However, I don't actually appear to have a functional installation. If I do: /usr/local/mysql/bin/mysql or even sudo /usr/local/mysql/bin/mysql I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) although /tmp/mysql.sock does exist, which seems to indicate the server is running. But then again ps -a -x doesn't seem to show it, so maybe it isn't; if I do ps -A |fgrep mysql on my Linux box, where MySQL is running happily, I get a hit for mysqld_safe plus ten others for mysqld. I tried starting the server manually as suggested in the docs: sudo /Library/StartupItems/MySQL/MySQL start Starting MySQL database server but when I try running mysql I get error 2002 as before. I found mention of this problem on FAQTS and in the MySQL mailing lists archive, the latter of which suggested this email address. Any clues would be most welcome! Please cc any replies to me as I am not currently subscribed to any of the MySQL lists. Thanks in advance, Tim Jarman -- 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: how do i encrypt the .frm file with a password
How would password protecting without encrypting it be meaningful? (Answer: It wouldn't) Look at PGP/GPG encryption, as an example of private/public key encryption. Feed the encryption program uncompressed data and get either keyed or password protected data that is encrypted and compressed. This isn't a MySQL issue, strictly speaking, but I hope this helps you find an answer. references: http://www.pgp.com http://gnupg.org Robert J Taylor [EMAIL PROTECTED] David Crane wrote: I was considering encrypting the data itself. However, that would impact performance and our ability to compress it. We are using access now and it is a 5 cd install. I was hoping I could get away with password protecting the files to provide some security. David Crane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I need to provide some security to a database that I am working on. This database will be distributed and I need to prevent users from being able to simply copy the files and being able to have complete access to it. I want to do this: Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html) I have recompiled mysql to enable 64 indexes on a table. So, recompiling it is not a problem. Do I need a custom version or MaxDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Set path variable on Windows 2000
What is the correct syntax to set the path variable on Windows 2000 so I can type mysql instead of c:\mysql\bin at the command prompt? - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Set path variable on Windows 2000
Globally: Add c:\mysql\bin to path in the environment variables under the My computer -- properties window Session: set path=%path%;c:\mysql\bin -Original Message- From: MightyData To: MySQL Sent: 5/11/04 1:50 PM Subject: Set path variable on Windows 2000 What is the correct syntax to set the path variable on Windows 2000 so I can type mysql instead of c:\mysql\bin at the command prompt? - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.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]
Re: Problem running MySQL on MacOS X.3
ps should show you something like the following: Jupiter:~/desktop hcir$ ps uax | grep mysql root 291 0.0 0.118644 1072 ?? S 3May04 0:00.06 sh ./bin/safe_mysqld --user=mysql mysql 338 0.0 2.551720 19872 ?? S 3May04 10:17.86 /usr/local/mysql-standard-4.1.0-alpha-apple-darwin6.4-powerpc/bin/ mysqld hcir20399 0.0 0.018172344 std S+ 10:52AM 0:00.01 grep mysql if you dont see this i would check the error log after you try to start the server with something like: #/usr/local/mysql/bin/mysqld_safe -uuser - hcir On May 11, 2004, at 10:29 AM, Gabriel Ricard wrote: Did you configure MySQL and run the /usr/local/mysql/scripts/mysql_install_db to initialize your data directory? - Gabriel On May 10, 2004, at 5:21 PM, Tim Jarman wrote: I have an iBook G4 running MacOS X.3 on Darwin 7.0. I downloaded and ran the binary installer (mysql-standard-4.0.18.pkg and it appeared to work fine; I have /usr/local/mysql and so on as per the docs. I also installed MySQLStartupItem. However, I don't actually appear to have a functional installation. If I do: /usr/local/mysql/bin/mysql or even sudo /usr/local/mysql/bin/mysql I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) although /tmp/mysql.sock does exist, which seems to indicate the server is running. But then again ps -a -x doesn't seem to show it, so maybe it isn't; if I do ps -A |fgrep mysql on my Linux box, where MySQL is running happily, I get a hit for mysqld_safe plus ten others for mysqld. I tried starting the server manually as suggested in the docs: sudo /Library/StartupItems/MySQL/MySQL start Starting MySQL database server but when I try running mysql I get error 2002 as before. I found mention of this problem on FAQTS and in the MySQL mailing lists archive, the latter of which suggested this email address. Any clues would be most welcome! Please cc any replies to me as I am not currently subscribed to any of the MySQL lists. Thanks in advance, Tim Jarman - hcir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow login
Jiri Matejka wrote: Unfortunatelly it isn't true in my case. I connect to database server in local network and I use IP address, so there is no DNS usage... Jiri Matejka If I remember correctly, this is an old bug Windows-specific that has been fixed in the latest release. Try upgrading to 3.23.58 or 4.0.18. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
questions about bind_param and mysql trace data included
below I have a snippet from a trace file read out for a section of code below. The
problem seems to be with '$sth-bind_param (2,$rh_row-{prop_str_addr});' The first
Bind works fine however the next one in the line listed produces a null entry per the
trace file. I have verified that the data is correct in '$rh_row-{prop_str_addr}' and
that it is quoted but for some reason myql isn't recieving the data any help would be
appreciated.
- dbd_st_execute 0 rows
- execute= '0E0' at db_load_1-2.pl line 128
- bind_param for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c 1
''310-673-5515'')
- bind_param= 1 at db_load_1-2.pl line 122
- execute for DBD::mysql::st (DBI::st=HASH(0x8104f14)~0x8104e3c)
- dbd_st_execute for 08104e48
Binding parameters: UPDATE own_info
SET own_phone_home = '\'310-673-5515\''
WHERE own_str_addr = NULL
###CODE SNIPPETTE
$dbh = connect_try(**,**);
foreach $k (keys (%{$ar_info-[1]})){ # retrieves a generic set of fields and uses
them to assign values for each row.
if ($table eq prop_info){ # checks which table is being used and assigns the
correct SQL statement
$sth = $dbh-prepare (UPDATE prop_info
SET $k = ?
WHERE prop_str_addr = ?) or
err_trap(failed to prepare statement\n);
}elsif ($table eq own_info){
$sth = $dbh-prepare (UPDATE own_info
SET $k = ?
WHERE own_str_addr = ?) or
err_trap(failed to prepare statement\n);
}
$sth-trace(2, ./trace_data.txt);
foreach $rh_row (@$ar_info) { # iterates through the list of rows and assigns
the correct value to the field
print ::$k=$rh_row-{$k}; # this is an internal check to verify what
values are being inserted
$sth-bind_param (1,$rh_row-{$k});
if ($table eq prop_str_addr) {
$sth-bind_param (2,$rh_row-{prop_str_addr});
}elsif ($table eq own_str_addr) {
$sth-bind_param (2,$rh_row-{own_str_addr});
}
$sth-execute() or
err_trap(failed to execute statement\n);
}
print \n===\n;
}
$sth-finish();
$dbh-disconnect or
err_trap(failed to disconnect statement\n);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Huge query on MERGE tables
Hi everyone, I have monthly data from 4 years, this means 48 MyISAM tables with identical structure, all defined and indexed properly (I guess) with 650,000 rows (approx.) and 99 columns. The tables are stored in a dedicated partition with 80 Gb of free space. The server is running Windows 2000 with 696 megs of RAM, pentium 4 processor and a 7200 rpm hard disk. Mysql version used is 4.0.17 Now from my database, I have the following: mysql show fields from fun199801; One of the 48 tables. +---+---+--+ -+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+ -+-+---+ | id| int(10) unsigned | | MUL | 0 | | | comn | int(11) | | MUL | 0 | | | actc | int(11) | | MUL | 0 | | | ind | int(10) unsigned | | MUL | 0 | | | origin| char(1) | YES | | NULL| | | period| varchar(6)| YES | | NULL| | | pres | varchar(8)| YES | | NULL| | | c20 | bigint(20) unsigned | YES | | NULL| | | c27 | bigint(20) unsigned | YES | | NULL| | | c28 | bigint(20) unsigned | YES | | NULL| | | c30 | bigint(20) unsigned | YES | | NULL| | | c31 | bigint(20) unsigned | YES | | NULL| | | c32 | bigint(20) unsigned | YES | | NULL| | | c33 | bigint(20) unsigned | YES | | NULL| | | c39 | bigint(20) unsigned | YES | | NULL| | | c41 | bigint(20) unsigned | YES | | NULL| | | c42 | bigint(20) unsigned | YES | | NULL| | ... 99 rows in set (0.02 sec) I made a MERGE table for each year, so I have 4 MERGE tables named fx1998,fx1999,fx2000 and fx2001 Then I wrote the following query: SELECT IFNULL(fx1998.comn,0) as idcomn, IFNULL(fx1998pt.actc,0) as idactc, IFNULL( (CASE WHEN fx1998.id=stat.id THEN 5 WHEN fx1998.id=soc1998.id THEN 10 ELSE ( CASE WHEN fx1998.id BETWEEN 1 AND 4999 THEN 1 WHEN fx1998.id BETWEEN 5000 AND 5299 THEN 2 WHEN fx1998.id BETWEEN 5300 AND 5899 THEN 4 WHEN fx1998.id BETWEEN 5900 AND 5999 THEN 3 WHEN (fx1998.id BETWEEN 7000 AND 7699) OR (fx1998.id BETWEEN 7900 AND 7949) THEN 7 WHEN (fx1998.id BETWEEN 7700 AND 7899) OR (fx1998.id BETWEEN 7950 AND 8699) OR (fx1998.id BETWEEN 8750 AND 8999) THEN 8 WHEN fx1998.id BETWEEN 8700 AND 8749 THEN 9 WHEN fx1998.id=9000 THEN 11 ELSE 6 END) END),0) as idsoc, COUNT(DISTINCT fx1998.id) as num, SUM(c108+c111+c112+c154) as sales, SUM(c109) as cost, SUM(c39+c42) as retenc, 1998 as year FROM ipt.fx1998 LEFT JOIN utils.soc1998 ON fx1998.id=soc1998.id LEFT JOIN utils.stat ON fx1998.id=stat.id GROUP BY idcomn,idactc,idsoc UNION ALL (the same syntax as the above, but for the remaining 3 years) Here is the output of the EXPLAIN command for the query: ++--+---+--+-+-- --+-+-+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+-- --+-+-+ | fx1998 | ALL | NULL | NULL |NULL | NULL | 8079209 | Using temporary; Using filesort | | soc1998| ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | stat | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | fx1999 | ALL | NULL | NULL |NULL | NULL | 8222017
