RE: Needing help about PHP+mysql on RedHat 9.
You could try http://us2.php.net/ , http://us3.php.net/ , or http://us4.php.net/ . This isn't a PHP mailing list, but I'll answer your question briefly. The php.ini setting register_globals is set to 'off', so the $PHP_SELF variable isn't getting created. Either change $PHP_SELF to $_SERVER['PHP_SELF'] (recommended) or turn register_globals on. http://us3.php.net/manual/en/reserved.variables.php Chris -Original Message- From: Mario Miyojim [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 10:18 PM To: [EMAIL PROTECTED] Subject: Needing help about PHP+mysql on RedHat 9. I am trying to revive an existing LAMP system. I recently installed RedHat9. I had several LAMP applications working under RedHat 7.1, but now they don't function properly. For instance, I have many scripts that use the FORM ACTION=?php print($PHP_SELF) METHOD=post construct, but they are not working as they should. I tried to get help from the PHP home site, but www.php.net has been unreacheable to me for several days. I am considering a desperate attempt to go back to RedHat 7.1 because that configuration supported my PHP scripts, but now I can't even read the updated PHP manual. Please, if anyone knows how to circumvent this problem, help me! Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] TABLE_PRIV
And the columns: Grantor, Timestamp Table_priv, Column_priv what is up to? Thanx again - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Nikos Gatsis' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, November 17, 2003 3:37 PM Subject: RE: [PHP-DB] TABLE_PRIV table_privs is going to assign privileges at the individual table level grant select on database.tablename to user@'192.168.0.10' IDENTIFIED BY 'password' -Original Message- From: Nikos Gatsis [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 7:20 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] TABLE_PRIV Hello List. Does anybody knows how to configure table_priv table of Mysql? I didn't find something in manual I have v. 3.23.41 in Linux Thanx Nikos -- 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: Birthday calendar
Hi, This can be done using 3.23.49a, but it won't work, because the birthday year is somthing like 1981 and now() returns 2003. That is exactly the problem which I try to work out. \Olaf Peter Sap wrote: Hi Olaf, try something like this: AND gebdatum between now() and date_add(now(),interval 7 day) However, I have no idea if this can be done with 3.23.49a. -- Peter Sap. - Original Message - From: Olaf van Zandwijk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 17, 2003 5:39 PM Subject: Birthday calendar Hello, I want to have a query that returns the names from people that are having their birthday one of the coming 7 days. I currently have a buggy query that deals with this, but that query fails when the birthday is the next month, and we're now in the last week of the month before. Can anyone give me a hint in the right direction to do this? I'm using MySQL 3.23.49a, and it's not (yet) possible to use MySQL 4 Grtz, \Olaf SELECT month(gebdatum) AS maand, dayofmonth(gebdatum) AS dag, achternaam, CONCAT(roepnaam, ' ', COALESCE(tussenvoegsel, ' '), ' ', achternaam) AS naam FROM persoon, WHERE YEAR(gebdatum) IS NOT NULL AND This part of the query needs to be redone AND CONCAT(roepnaam, ' ', COALESCE(tussenvoegsel, ' '), ' ', achternaam) IS NOT NULL ORDER BY maand, dag, achternaam -- __XXX__ (0-0) +--ooO--(_)--Ooo---+ | | | Olaf van Zandwijk| | | | ICQ# 30231605| | PGP Public Key: http://www.vanzandwijk.net/pgp.txt | +--+ -- __XXX__ (0-0) +--ooO--(_)--Ooo---+ | | | Olaf van Zandwijk| | | | ICQ# 30231605| | PGP Public Key: http://www.vanzandwijk.net/pgp.txt | +--+
How to select records based on timestamp field (using BETWEEN and AND sql clause
I have a table with one timestamp field named timedate (Unix_timestamp format). I used the follow command but it's not working correctly: mysql select count(*) from tablename where date_format(timedate,%Y-%m-%d) between date_sub(now(), interval 1 DAY) and now(); Empty Set This result is not right since I know there are records in the table that got inserted in the past 24 hours. Does anyone know why? Also it seems unix_timestamp() function only return results in GMT timezone. I don't know how it can return results in US central timezone. Thanks, Hai _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Column type
I think it would depend on what you are going to do with the data once it is in the database. If it will only ever be used as a pure text field, then leaving it as a varchar would not be bad. On the other hand, if you want to be able to use any of the date fucntions or use it as a date in a where clause (i.e. WHERE begin some date here ), then I would convert it to one of the date types. This way it could be indexed. Also, if you only need the date, use a DATE field, as it would be smaller and thus faster. If you need the time also, use DATETIME. -Original Message- From: Ron McKeever [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 10:31 PM To: [EMAIL PROTECTED] Subject: Column type Hi I have a .txt file that is loaded into my table. This is done using LOAD DATA INFILE One of the columns data is a unix timestamp from the .txt file. The column is called begin. I have created the column as a varchar before and then converted the date using FROM_UNIXTIME(), so it can be readable. My question is: Should I create this column begin as DATETIME, DATE, or TIMESTAMP or since I'm converting the unix time leave it as a varchar??? Thanks, Ron -- 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]
Needing help for JDBC issue
Hi, I'm getting the following error while connecting to a MySQL databases using JDBC. Any clues?? java.net.SocketException: errno: 48, error: Address already in use Thanks, Ram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Audio / Video Files
Can audio or video files be attached to a mysql table? VH
Re: Audio / Video Files
Can audio or video files be attached to a mysql table? VH Very short question, but personally, i'd store the filenames and its information into a media table rather than importing the binary data or it would get huge ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Audio / Video Files
[EMAIL PROTECTED] wrote: Can audio or video files be attached to a mysql table? You can store them in the BLOB columns: http://www.mysql.com/doc/en/BLOB.html -- 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: Needing help about PHP+mysql on RedHat 9.
Thank you. It was the king of advice that I needed, because I was unaware of the existence of this parameter. I was struggling with the lack of mysql support when I tried to use the new RedHat 9. When I finally solved that problem, I became confused with the inability to transmit variable values through the POST method, that is why I asked for help. Now I can help others with the same dilemma. Thanks, indeed. p --- John Nichel [EMAIL PROTECTED] wrote: Mario Miyojim wrote: I am trying to revive an existing LAMP system. I recently installed RedHat9. I had several LAMP applications working under RedHat 7.1, but now they don't function properly. Chances are, register_globals was on with your old installation, and now it is off by default in php. Either turn it on in the php.ini (not-recommended), and restart your webserver, or rewrite your code to work with globals off (recommended). Things like $PHP_SELF would now be, $_SERVER['PHP_SELF']. Look here http://us3.php.net/register_globals Also, you'll get more help with an item such as this from a php mailing list. -- By-Tor.com It's all about the Rush http://www.by-tor.com __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
false negatives problem in 3 table query
I have 3 tables and want data from the first 2, but only want data from the third on the rare times that there is a match in tables 2 3. If the conditions are not met in table 3, I don't get the results i still want from table 1 table 2. Is there any way to do this in one query, without having dummy records in table 3? I think of this problem as false negatives. (I guess this actually breaks the fundamental concept of a Query) Thanks, Simon
Re: Backup question.
Is there any problem executing rsync /var/lib/mysql/data/ from one host to another host and therefore having same file in obth machines?? Is it unsafe this method ?? Could be crashed meanwhile transferring ?? Thanks !!! - Original Message - From: Christensen, Dave [EMAIL PROTECTED] To: 'Richard Reina' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:20 PM Subject: RE: Backup question. Yes, you can do it like this: Prompt mysqldump --add-drop-table --host=source.IP.addr.spec -uuser -ppassword databasename | mysql -uuser -ppassword I've found that it helps things if you add --no-data to the source side on the first pass, then remove that clause and run it again. Dave -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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]
RE: Needing help about PHP+mysql on RedHat 9.
Thank you. Now I read tutorials at the new PHP web site, so my old scripts are refurbished and are functional. I initialize every variable like this: $var1 = $_POST['var1']; for example, and values are correctly passed. Mario p --- Chris [EMAIL PROTECTED] wrote: You could try http://us2.php.net/ , http://us3.php.net/ , or http://us4.php.net/ . This isn't a PHP mailing list, but I'll answer your question briefly. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup question.
Has any one use LVM to take a snapshot in MySQL and use this to back up data? Simon -Original Message- From: Paco Martinez [mailto:[EMAIL PROTECTED] Sent: 18 November 2003 10:00 To: Christensen, Dave; 'Richard Reina'; [EMAIL PROTECTED] Subject: Re: Backup question. Is there any problem executing rsync /var/lib/mysql/data/ from one host to another host and therefore having same file in obth machines?? Is it unsafe this method ?? Could be crashed meanwhile transferring ?? Thanks !!! - Original Message - From: Christensen, Dave [EMAIL PROTECTED] To: 'Richard Reina' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:20 PM Subject: RE: Backup question. Yes, you can do it like this: Prompt mysqldump --add-drop-table --host=source.IP.addr.spec -uuser -ppassword databasename | mysql -uuser -ppassword I've found that it helps things if you add --no-data to the source side on the first pass, then remove that clause and run it again. Dave -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Feature wish
Hi, I wonder if a small enhancement to the mysql planner/optimizer could be made. The current (mysql4.0) optimizer only uses one index per SELECT. Because of this some queries result in a slow fulltable scan even if all used columns are indexed. Example: SELECT * FROM table1 WHERE columnA='x' OR columnB='x' (Slow table scan even if columnA and columnB are indexd! Other Databases like PostgreSQL don't have this problem.) Since Mysql 4.0 a UNION can be used to speed this up. Example: SELECT * FROM table1 WHERE columnA='x' UNION SELECT * FROM table1 WHERE columnB='x' (Now we have two SELECT with one index each.) Couldn't the planner/optimizer be easely changed to do this UNION automaticly ? I think this would be a big improvement to mysql. If I port applications from other databases to mysql there are often queries like this. Either those queries run terrible slow an mysql or I have to manually convert them into UNIONs. Thanks in advance Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup question.
Just me has access to that machine... And there's no LVM. - Original Message - From: Simon Green [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 11:12 AM Subject: RE: Backup question. Has any one use LVM to take a snapshot in MySQL and use this to back up data? Simon -Original Message- From: Paco Martinez [mailto:[EMAIL PROTECTED] Sent: 18 November 2003 10:00 To: Christensen, Dave; 'Richard Reina'; [EMAIL PROTECTED] Subject: Re: Backup question. Is there any problem executing rsync /var/lib/mysql/data/ from one host to another host and therefore having same file in obth machines?? Is it unsafe this method ?? Could be crashed meanwhile transferring ?? Thanks !!! - Original Message - From: Christensen, Dave [EMAIL PROTECTED] To: 'Richard Reina' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:20 PM Subject: RE: Backup question. Yes, you can do it like this: Prompt mysqldump --add-drop-table --host=source.IP.addr.spec -uuser -ppassword databasename | mysql -uuser -ppassword I've found that it helps things if you add --no-data to the source side on the first pass, then remove that clause and run it again. Dave -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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] -- 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: Backup question.
rsynch will do the job correctly only and only if the mysql server on both sides are not running. Rsynch does not deal with files opened for writing and my guess is that MySQL is opening the database (i.e. files) for writing. As for LVM (AKA Logical Volume Manager) I really do not see how it could be used to take a snapshot in MySQL. However, I could be wrong here. Bernard On Tuesday 18 November 2003 05:12, Simon Green wrote: Has any one use LVM to take a snapshot in MySQL and use this to back up data? Simon -Original Message- From: Paco Martinez [mailto:[EMAIL PROTECTED] Sent: 18 November 2003 10:00 To: Christensen, Dave; 'Richard Reina'; [EMAIL PROTECTED] Subject: Re: Backup question. Is there any problem executing rsync /var/lib/mysql/data/ from one host to another host and therefore having same file in obth machines?? Is it unsafe this method ?? Could be crashed meanwhile transferring ?? Thanks !!! - Original Message - From: Christensen, Dave [EMAIL PROTECTED] To: 'Richard Reina' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:20 PM Subject: RE: Backup question. Yes, you can do it like this: Prompt mysqldump --add-drop-table --host=source.IP.addr.spec -uuser -ppassword databasename | mysql -uuser -ppassword I've found that it helps things if you add --no-data to the source side on the first pass, then remove that clause and run it again. Dave -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup question.
I think that it is more than backing up the database you want to do. If I am right you want to synchronize the 2 databases in which case the answer is replication. Replication is described in the reference manual of MySQL. Bernard On Tuesday 18 November 2003 04:59, Paco Martinez wrote: Is there any problem executing rsync /var/lib/mysql/data/ from one host to another host and therefore having same file in obth machines?? Is it unsafe this method ?? Could be crashed meanwhile transferring ?? Thanks !!! - Original Message - From: Christensen, Dave [EMAIL PROTECTED] To: 'Richard Reina' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:20 PM Subject: RE: Backup question. Yes, you can do it like this: Prompt mysqldump --add-drop-table --host=source.IP.addr.spec -uuser -ppassword databasename | mysql -uuser -ppassword I've found that it helps things if you add --no-data to the source side on the first pass, then remove that clause and run it again. Dave -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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]
Re: Backup question.
Bernard Clement wrote: rsynch will do the job correctly only and only if the mysql server on both sides are not running. Rsynch does not deal with files opened for writing and my guess is that MySQL is opening the database (i.e. files) for writing. It will work without stopping mysql server if you make sure that all data in memory are flushed to disc and mysql server does not modify these files while you read them. This can be done with: FLUSH TABLES WITH READ LOCK NB: I assume that you run mysql server on main server only (not on backup server). If you have mysql server on both systems you can also use mysql built-in replication : set your main server as master and the backup server as a slave. As for LVM (AKA Logical Volume Manager) I really do not see how it could be used to take a snapshot in MySQL. However, I could be wrong here. MySQL manual describes how to make a snapshot with Veritas filesystem (vxfs). I am sure that it could be done in the same way with LVM (but I haven't tried it myself): If you are using a Veritas filesystem, you can do: 1. From a client (or Perl), execute: FLUSH TABLES WITH READ LOCK. 2. From another shell, execute: mount vxfs snapshot. 3. From the first client, execute: UNLOCK TABLES. 4. Copy files from snapshot. 5. Unmount snapshot. (from : http://www.mysql.com/doc/en/Backup.html) Bernard Hope this helps, Joseph Bueno On Tuesday 18 November 2003 05:12, Simon Green wrote: Has any one use LVM to take a snapshot in MySQL and use this to back up data? Simon -Original Message- From: Paco Martinez [mailto:[EMAIL PROTECTED] Sent: 18 November 2003 10:00 To: Christensen, Dave; 'Richard Reina'; [EMAIL PROTECTED] Subject: Re: Backup question. Is there any problem executing rsync /var/lib/mysql/data/ from one host to another host and therefore having same file in obth machines?? Is it unsafe this method ?? Could be crashed meanwhile transferring ?? Thanks !!! - Original Message - From: Christensen, Dave [EMAIL PROTECTED] To: 'Richard Reina' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:20 PM Subject: RE: Backup question. Yes, you can do it like this: Prompt mysqldump --add-drop-table --host=source.IP.addr.spec -uuser -ppassword databasename | mysql -uuser -ppassword I've found that it helps things if you add --no-data to the source side on the first pass, then remove that clause and run it again. Dave -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table crash (error127) using v4.0.15 JDBC
Hi I'm working with mysql 4.0.15 for some software of ours and we are experiencing a random table crash. we have 30 MyISAM tables in our database and we're using JDBC to connect to them. Sometimes, very rarely, one of the tables crashes. It has nothing special compared to the other tables (int, varchars, datetimes and a blob), and it's one of the least accessed. We checked all the requests, they all work separately. I may add that 6 processes are accessing the base at the same time but only one of them is accessing the table that crashes, and there are always at least 5 seconds between each request on this table. for the whole base, there are 1 to 5 requests per second. It seems it's a very random bug. Never crashed buring the development, twice during the tests, then not during some time, then once during some tests at the custommers', and until now, never during the production phase, whereas there are far more requests than during the tests... I know how to repair the tables, but our software being realtime, it's a bit annoying to have to stop everything, repair and restart. Even dropping and creating the table again can lead to a further crash. Well so here is my question : i'm wondering whether the crashes could come from our version of the ConnectorJ : mysql-connector-java-2.0.14-bin.jar ?? nota bene : we couldn't use the 3.0.8 version at the moment we began coding because it had an annoying java access-rights bug (that seems corrected on the 3.0.9). Another possibility is some issue with using some toad-like mysql manager at the same time as the 6 processes...could be. If any of you has experienced a compatibility issue related to mysql 4 and connector 2, please be so kind as telling me. Thanks Fabien
How to enlarge number of keys per table up to 64?
Dear all, The documentation for 4.0.xx states that for MyISAM tables: Maximum number of keys/table is 32 as default. This can be enlarged to 64 without having to recompile myisamchk. HOW TO DO THIS ?? Have someone done this? I am using MySQL 4.0.16 precompiled binaries for Solaris. Thanks already, Dmitry Kuznetsov |Dmitry A. Kuznetsov, PhD | |Office of Information Technology | |Ludwig Institute for Cancer Research | |WWW: http://www.licr.org/ | |Ch. Des Boveresses 155 | |CH-1066 EPALINGES Switzerland | |Tel: +41-21-692-5991 FAX: +41-21-692-5945| |E-mail: [EMAIL PROTECTED] | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems retrieving int unsigned / Re: QVariant::toUInt problem
On Mon, 2003-11-17 at 13:42, Harald Fernengel wrote: Hello John, ... We had some reports about uint-int problems with MySQL, but I believe we fixed them all in Qt 3.3, so there should not be any problems. Best regards, Harald I can confirm that the problem is fixed in 3.3. Thanks - John -- John A. Sullivan III Chief Technology Officer Nexus Management +1 207-985-7880 [EMAIL PROTECTED] --- If you are interested in helping to develop a GPL enterprise class VPN/Firewall/Security device management console, please visit http://iscs.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing Field Type
G'Day All; MySQL on RedHat 9 working completely from the command prompt. I am so newbie I am embareassed. What command do I use to change the field type in my client_info table from: referral_percent | tinyint(4) | YES | NULL to referral_percent | double(3,2) unsigned zerofill | YES | NULL Thanks much -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: MySQL NAT
Someone can make nat with mysql? I can do it with apache without problems, but mysql client freeze trying to connect. Thanx, Alejandro. _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Re: Changing field type
What command do I use to change the field type in my client_info table from: referral_percent | tinyint(4) | YES | NULL to referral_percent | double(3,2) unsigned zerofill | YES | NULL ALTER TABLE yourtable CHANGE referral_percent referral_percent double(3,2) unsigned zerofill default null; or: ALTER TABLE yourtable MODIFY referral_percent double(3,2) unsigned zerofill default null; See: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#ALTER_TABLE _ Send a QuickGreet with MSN Messenger http://www.msnmessenger-download.com/tracking/cdp_games -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing field type
Jeremy, Thanks very much. 'preciate it. -Original Message- From: Jeremy March [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 8:46 AM To: [EMAIL PROTECTED] Subject: Re: Changing field type What command do I use to change the field type in my client_info table from: referral_percent | tinyint(4) | YES | NULL to referral_percent | double(3,2) unsigned zerofill | YES | NULL ALTER TABLE yourtable CHANGE referral_percent referral_percent double(3,2) unsigned zerofill default null; or: ALTER TABLE yourtable MODIFY referral_percent double(3,2) unsigned zerofill default null; See: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html #ALTER_TABLE _ Send a QuickGreet with MSN Messenger http://www.msnmessenger-download.com/tracking/cdp_games -- 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: table crash (error127) using v4.0.15 JDBC
Are the crashes occurring on the same machines? Or random machines? Can you track the disk activity at the time the table is being marked as corrupt? Are you tracking the database activity? -Original Message- From: fabien marsaud [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:52 AM To: [EMAIL PROTECTED] Subject: table crash (error127) using v4.0.15 JDBC Hi I'm working with mysql 4.0.15 for some software of ours and we are experiencing a random table crash. we have 30 MyISAM tables in our database and we're using JDBC to connect to them. Sometimes, very rarely, one of the tables crashes. It has nothing special compared to the other tables (int, varchars, datetimes and a blob), and it's one of the least accessed. We checked all the requests, they all work separately. I may add that 6 processes are accessing the base at the same time but only one of them is accessing the table that crashes, and there are always at least 5 seconds between each request on this table. for the whole base, there are 1 to 5 requests per second. It seems it's a very random bug. Never crashed buring the development, twice during the tests, then not during some time, then once during some tests at the custommers', and until now, never during the production phase, whereas there are far more requests than during the tests... I know how to repair the tables, but our software being realtime, it's a bit annoying to have to stop everything, repair and restart. Even dropping and creating the table again can lead to a further crash. Well so here is my question : i'm wondering whether the crashes could come from our version of the ConnectorJ : mysql-connector-java-2.0.14-bin.jar ?? nota bene : we couldn't use the 3.0.8 version at the moment we began coding because it had an annoying java access-rights bug (that seems corrected on the 3.0.9). Another possibility is some issue with using some toad-like mysql manager at the same time as the 6 processes...could be. If any of you has experienced a compatibility issue related to mysql 4 and connector 2, please be so kind as telling me. Thanks Fabien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Needing help for JDBC issue
Can you post what you are trying to do? -Original Message- From: Prasad Budim Ram [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 2:17 AM To: [EMAIL PROTECTED] Subject: Needing help for JDBC issue Hi, I'm getting the following error while connecting to a MySQL databases using JDBC. Any clues?? java.net.SocketException: errno: 48, error: Address already in use Thanks, Ram -- 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]
50 000 tables - table_cache
I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Any ideas? Regards - Jorgen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table crash (error127) using v4.0.15 JDBC
it happened on 3 different machines (2*win2000 1*win2003) we have a big logger running at the same time and it tells nothing partiular about the disk activity. else i have had a mysql 4.0.x + 4.1.0 beta at home taking a lot of requests for a quite a long time now (requests are performed thru php, not java this time), and no crash ever occured. that's why i was asking for a possible ConnectorJ issue. I'm gonna read its changelog, who knows... f. - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'fabien marsaud' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 2:51 PM Subject: RE: table crash (error127) using v4.0.15 JDBC Are the crashes occurring on the same machines? Or random machines? Can you track the disk activity at the time the table is being marked as corrupt? Are you tracking the database activity? -Original Message- From: fabien marsaud [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:52 AM To: [EMAIL PROTECTED] Subject: table crash (error127) using v4.0.15 JDBC Hi I'm working with mysql 4.0.15 for some software of ours and we are experiencing a random table crash. we have 30 MyISAM tables in our database and we're using JDBC to connect to them. Sometimes, very rarely, one of the tables crashes. It has nothing special compared to the other tables (int, varchars, datetimes and a blob), and it's one of the least accessed. We checked all the requests, they all work separately. I may add that 6 processes are accessing the base at the same time but only one of them is accessing the table that crashes, and there are always at least 5 seconds between each request on this table. for the whole base, there are 1 to 5 requests per second. It seems it's a very random bug. Never crashed buring the development, twice during the tests, then not during some time, then once during some tests at the custommers', and until now, never during the production phase, whereas there are far more requests than during the tests... I know how to repair the tables, but our software being realtime, it's a bit annoying to have to stop everything, repair and restart. Even dropping and creating the table again can lead to a further crash. Well so here is my question : i'm wondering whether the crashes could come from our version of the ConnectorJ : mysql-connector-java-2.0.14-bin.jar ?? nota bene : we couldn't use the 3.0.8 version at the moment we began coding because it had an annoying java access-rights bug (that seems corrected on the 3.0.9). Another possibility is some issue with using some toad-like mysql manager at the same time as the 6 processes...could be. If any of you has experienced a compatibility issue related to mysql 4 and connector 2, please be so kind as telling me. Thanks Fabien -- 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]
Easy (?) SELECT questions
Two related questions.
1. I have a table with a date field. (Ex: '04/13/2002'). I want to get a list
of all UNIQUE values for that field (many entries from the same day), i.e., all
days with entries.
**
CREATE TABLE metrics_events_power {
mep_id int(11) NOT NULL auto_increment,
mep_date text,
mep_time time DEFAULT '00:00:00' NOT NULL
}
INSERT INTO metrics_events_power VALUES ('1', '04/13/2002', '10:41:19');
INSERT INTO metrics_events_power VALUES ('1', '04/13/2002', '10:46:19');
INSERT INTO metrics_events_power VALUES ('1', '04/14/2002', '11:51:19');
**
I want a query that returns for this data '04/13/2002' and '04/14/2002'.
2. Now I have X tables with those date fields. How do I get a list from all
specified tables with the unique dates from them? For instance, 2 more tables:
**
CREATE TABLE arf_events_power {
aep_id int(11) NOT NULL auto_increment,
aep_date text,
aep_time time DEFAULT '00:00:00' NOT NULL
}
INSERT INTO arf_events_power VALUES ('1', '05/13/2002', '10:41:19');
INSERT INTO arf_events_power VALUES ('1', '05/24/2002', '10:46:19');
INSERT INTO arf_events_power VALUES ('1', '06/21/2002', '11:51:19');
CREATE TABLE blah_events_power {
bep_id int(11) NOT NULL auto_increment,
bep_date text,
bep_time time DEFAULT '00:00:00' NOT NULL
}
INSERT INTO blah_events_power VALUES ('1', '04/13/2002', '10:41:19');
INSERT INTO blah_events_power VALUES ('1', '04/13/2002', '10:46:19');
INSERT INTO blah_events_power VALUES ('1', '07/28/2002', '11:51:19');
**
Should return 6 dates:
'04/13/2002'
'04/14/2002'
'05/13/2002'
'05/24/2002'
'06/21/2002'
'07/28/2002'
--
Mark Wilson, Computer Programming Unlimited (cpuworks.com)
Web : http://cpuworks.com Tel: 410-549-6006
Email: [EMAIL PROTECTED] Fax: 410-549-4408
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[jp] Unions in mysql 3.22
CAN I USE UNION cluse in mysql 3.22?? thank you .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [jp] Unions in mysql 3.22
-Original Message- From: Jorge Paiva (f2) [mailto:[EMAIL PROTECTED] Sent: 18 November 2003 15:14 To: [EMAIL PROTECTED] Subject: [jp] Unions in mysql 3.22 CAN I USE UNION cluse in mysql 3.22?? No! UNION is implemented in MySQL 4.0.0 and later. - Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Needing help for JDBC issue
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Prasad Budim Ram wrote: Hi, I'm getting the following error while connecting to a MySQL databases using JDBC. Any clues?? java.net.SocketException: errno: 48, error: Address already in use Thanks, Ram You don't say what OS you're running on (Netware???). This is most likely due to you running out of originating ports to make a TCP/IP connection from. Are you creating a large number of connections when this happens (or is any other process on your system doing so?) -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/ujpItvXNTca6JD8RAqRLAKCbcoseLiNd27xbpkAtAiW+rgCB3wCfTWDv NUmvYZHVTQvB+8Mv5lGRYqQ= =MW7L -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 50 000 tables - table_cache
Hi! Looking through the docs, MySQL's internals don't seem to lend themselves well to having this many tables. Is there any chance you could use MERGE tables to chuck a few of them together (admittedly I'm guessing here). Regards, Chris Jörgen Winqvist wrote: I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Any ideas? Regards - Jorgen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
db maint
Hello WARNING::Newbie. I have snort running and putting alerts into a mysql database. I see that its starting to get big. How does one go about cleaning the db ? or deleting old data ? Thanks Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db maint
delete from TABLENAME where CONDITION - Original Message - From: M.D. DeWar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 3:50 PM Subject: db maint : Hello : WARNING::Newbie. : : I have snort running and putting alerts into a mysql database. : I see that its starting to get big. : How does one go about cleaning the db ? : or deleting old data ? : : Thanks : Mark : : : : -- : 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: db maint
There recently was a thread discussing this with a very nice summary by the person who had the issue... do a search on the archives for 'maintaining the size of a db' to find the thread -Original Message- From: M.D. DeWar [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 10:51 AM To: [EMAIL PROTECTED] Subject: db maint Hello WARNING::Newbie. I have snort running and putting alerts into a mysql database. I see that its starting to get big. How does one go about cleaning the db ? or deleting old data ? Thanks Mark -- 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: 50 000 tables - table_cache
Hi Chris, I know its not good to have that many tables but that's the way its done. I can't see why a merge would help. It still has to open all the tables. regards /jorgen Chris Nolan wrote: Hi! Looking through the docs, MySQL's internals don't seem to lend themselves well to having this many tables. Is there any chance you could use MERGE tables to chuck a few of them together (admittedly I'm guessing here). Regards, Chris Jörgen Winqvist wrote: I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Any ideas? Regards - Jorgen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 50 000 tables - table_cache
I can't believe I'm saying this, but MySQL may not be the db of choice for your particular app... I figure you have few options: 1- keep db structure same, keep mysql, suffer performance issues 2- keep db structure, switch db, suffer migration costs 3- change db structure, keep mysql, suffer app modification 4- change db structure, switch db, suffer migration and app mod I know it sounds like nihlistic (sp?), that all of your options involve suffering, but I'm guessing that this is an organically grown app architecture, that it didn't start off with 50,000 tables. IMHO, your app is at a classic architecture inflection point, where you need to either redesign it, or truss it up with hardware changes. PS What about the idea of setting up a MySQL cluster, so that hopefully, various access will be spread out, and therefore file access... don't know about this one, but it's a though -Original Message- From: Jörgen Winqvist [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 11:02 AM To: Chris Nolan Cc: [EMAIL PROTECTED] Subject: Re: 50 000 tables - table_cache Hi Chris, I know its not good to have that many tables but that's the way its done. I can't see why a merge would help. It still has to open all the tables. regards /jorgen Chris Nolan wrote: Hi! Looking through the docs, MySQL's internals don't seem to lend themselves well to having this many tables. Is there any chance you could use MERGE tables to chuck a few of them together (admittedly I'm guessing here). Regards, Chris Jörgen Winqvist wrote: I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Any ideas? Regards - Jorgen -- 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: 50 000 tables - table_cache
In the last episode (Nov 18), Jrgen Winqvist said: I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Switching to InonDB may help you here, since all tables are stored in just a few tablespace files and the table cache is unused. Also, the table cache is just a linked list, so if you're thinking about rebuilding mysql with a higher hard table_cache limit, consider whether you want it to be scanning a 50k-element list every time it needs a table :) 16384 is probably too high as it stands. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql-standard-4.0.15 Library problem
Description: I have loaded mysql version 4.0.15 onto my G4 Mac running Darwin 6.8 Kernel. We are running an application that interfaces with mysql in saving its data. When I try to start up the application, I receive the following error message: dyld: /usr/local/monetra/bin/monetra can't open library: /usr/local/mysql/lib/mysql/libmysqlclient.10.dylib (No such file or directory, errno = 2) Trace/BPT trap I am told by the providers of this application that accesses the mysql database that this file existed in mysql3.23. Now, why should I have to revert to a previous version of mysql when I've already insalled 4.0.15? Please advise. Thank you, -- Jack Gorski Parallel Software (630) 420-9929x12 mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
State 'Statistics' BIG problem, please help
hi folks A query become with STATE set to STATISTICS and all queries after this query are LOCKED. What is the decision please. Best Regards Bogomil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sleeping connections
is there a way to close aging sleeeping connections after they have slept for a certain amount of time? a setting or something in mysql? dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile confusion
Hello Robert ,, I have no explanation , just a dummy question , 1) Are all values u try to put into column 5 a float with . ? 2) what if u try with integer ?? without . ? 3) can u input float values in column 3 ? 4) are u running mysqlcc from same pc ? 5 could it be some settings with decimal point is set to something diff than . depending of ur enviroment ? Not much help , only a kind of maybe list :) HTH Kim G. Pedersen This one has had me scratching my head all afternoon. I have a comma delimited text file that I am using to import data from a legacy system into a MyISAM table. My server is 4.0.16 as is my client. Both run on Redhat 8.0. An example line of imput looks like 041502f,1,25.00,$,25.00,,2003-1-15T17:47:50,O with my table format being CREATE TABLE IF NOT EXISTS FeeSplit( FileNumber VARCHAR( 50 ) BINARY NOT NULL, AppraiserCode VARCHAR( 08 ) BINARY NOT NULL, SplitNumber FLOAT DEFAULT 0.00, SplitDesignator CHAR( 01 ) DEFAULT '$', CalculatedSplitAmt FLOAT DEFAULT 0.00, DateFeePaid DATE, DateTimeRecordAdded DATETIME, PersonDesignatorCHAR( 01 ), SplitComments TEXT, LastModifiedTIMESTAMP, LastModifiedBy VARCHAR( 08 ), PRIMARY KEY ( FileNumber, AppraiserCode, SplitNumber, SplitDesignator, CalculatedSplitAmt, DateFeePaid, DateTimeRecordAdded ), INDEX FileNumberDesignatorIndex ( FileNumber, PersonDesignator ), INDEX AppraiserDesignatorIndex ( FileNumber, AppraiserCode, PersonDesignator ), INDEX AppraiserCodeIndex ( AppraiserCode ), INDEX FileNumberIndex ( FileNumber ); To load my data, I execute the following commands mysql --user=xxx --password=xxx -e USE Live_Tables; DELETE QUICK FROM FeeSplit; FLUSH TABLES; mysql --user=xxx --password=xxx -e USE Live_Tables; LOAD DATA INFILE 'Converted_FeeSplit.txt' INTO TABLE FeeSplit FIELDS TERMINATED BY ',' ENCLOSED BY '\' ESCAPED BY '~'; When these commands have completed, the value of field 5 is *always* 0.00. This in and of itself has had me confused - especially since I can execute the same SQL commands in MySQLCC and the contents of field 5 will be whatever is in my input file. I had this problem with 4.0.13 as well as now with 4.0.16. Has anyone seen this before? And possibly has a cure or fix? Thanks! Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lots of connections and RAM
for a server that is configured for 500 to 1000 max_connections, how much RAM is recommended to support this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile confusion
Thanks for the reply Kim! On Tuesday 18 November 2003 12:47 pm, you wrote: Hello Robert ,, I have no explanation , just a dummy question , 1) Are all values u try to put into column 5 a float with . ? Always floats. 2) what if u try with integer ?? without . ? haven't yet tried. 3) can u input float values in column 3 ? From MySQLCC - yes. 4) are u running mysqlcc from same pc ? Yes - against a local copy of mysqld 5 could it be some settings with decimal point is set to something diff than . depending of ur enviroment ? Not that I can tell. I have 3 seperate MySQL servers (Production, Test and Development) The problem appears to be happening on this one table on all 3 machines. And to make matters worse, the value in field 3 is a float too. The *only* thing I can think of is that the value of field 6 being simply 2 quotes is causing some confusion for field 5. But that I can import from MySQLCC, I would say that I've messed up the data somehow. Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Execute shell script
There is a command or function to call a shell script through mysql? _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
The MYSQL account on Linux
Hello, I started my current job with MySQL already installed on a Linux box, and no one seemed to know what the mysql account's password was I want to know/change the Linux password so I can login as mysql. So far it has been fine to become root and su to mysql, as I don't do it THAT often, but I need to stop using that route. Does anything besides user-created scripts, etc., care what the Linux mysql user has for a password? I don't want some process to break because I changed the account's password. Many thanks in advance, Julie Myhre This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
MaxProxy with MYSQL Control Center.
Has anyone tried MaxProxy, it claims to allow you to use any MySQL pogram to access the their new MAX SAP solution. it seems to work, but it causes MySQL Control Center to crash, anyone else had this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data definition problem?
I have a table that has a field type DOUBLE. I need to put into it data like '0.123' however any number less than .0001 (like .999) show up in the field like '1e-05' Also when I retieve the field in a PHP page it is return like '1e-05'. Do I have the wrong column definition or am I doing something else wrong here. How can I get it to display correctly when returned by a query? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: MySQL NAT
Someone can make nat with mysql? I can do it with apache without problems, but mysql client freeze trying to connect. Thanx, Alejandro. _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Execute shell script
There is a command or function to call a shell script through mysql? _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Re: OT: MySQL NAT
If you are going out throught NAT to the mysql server, there should be no problem. If you are trying to come into the server through NAT you will need port forwarding. adburne wrote: Someone can make nat with mysql? I can do it with apache without problems, but mysql client freeze trying to connect. Thanx, Alejandro. _ http://www.incredimail.com/redir.asp?ad_id=310lang=10 IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí http://www.incredimail.com/redir.asp?ad_id=310lang=10
Re: The MYSQL account on Linux
Why do you want to login as mysql? This really is not a mysql question, but while logged in as root, enter the command: passwd mysql Myhre, Julie wrote: Hello, I started my current job with MySQL already installed on a Linux box, and no one seemed to know what the mysql account's password was I want to know/change the Linux password so I can login as mysql. So far it has been fine to become root and su to mysql, as I don't do it THAT often, but I need to stop using that route. Does anything besides user-created scripts, etc., care what the Linux mysql user has for a password? I don't want some process to break because I changed the account's password. Many thanks in advance, Julie Myhre This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Execute shell script
At 4:47 PM -0300 11/18/03, adburne wrote: There is a command or function to call a shell script through mysql? Invoke mysql, then issue a \h command and look in the output for the line that begins with system. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday calendar
Hi Olaf, Sorry for this misunderstanding. Try something like this: AND dayofyear(gebdatum) BETWEEN dayofyear(now()) AND dayofyear(date_add(now(),interval 7 day)) OR (dayofyear(now()) dayofyear(date_add(now(),interval 7 day)) AND (dayofyear(gebdatum) dayofyear(date_add(now(),interval 7 day)) OR dayofyear(gebdatum) dayofyear(now( The first part deals with normal situations that both now() and gebdatum are within the same year. The second part (after the first OR) deals with the situation that you are in the end of december but also need the birthdays of the first days of january. Dayofyear(gebdatum) will then be smaller than dayofyear(now()). I tested it with a couple of days and it seems to work. However, there is still one problem: when your working with leap-years dayofyear will be different for for instance March 1st. This can be overcome by adding an extra condition to the where clause. Please check the use of and , I didn't pay much attention to that. Hope this helps -- Peter Sap - Original Message - From: Olaf van Zandwijk [EMAIL PROTECTED] To: Peter Sap [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 8:17 AM Subject: Re: Birthday calendar Hi, This can be done using 3.23.49a, but it won't work, because the birthday year is somthing like 1981 and now() returns 2003. That is exactly the problem which I try to work out. \Olaf Peter Sap wrote: Hi Olaf, try something like this: AND gebdatum between now() and date_add(now(),interval 7 day) However, I have no idea if this can be done with 3.23.49a. -- Peter Sap. - Original Message - From: Olaf van Zandwijk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 17, 2003 5:39 PM Subject: Birthday calendar Hello, I want to have a query that returns the names from people that are having their birthday one of the coming 7 days. I currently have a buggy query that deals with this, but that query fails when the birthday is the next month, and we're now in the last week of the month before. Can anyone give me a hint in the right direction to do this? I'm using MySQL 3.23.49a, and it's not (yet) possible to use MySQL 4 Grtz, \Olaf SELECT month(gebdatum) AS maand, dayofmonth(gebdatum) AS dag, achternaam, CONCAT(roepnaam, ' ', COALESCE(tussenvoegsel, ' '), ' ', achternaam) AS naam FROM persoon, WHERE YEAR(gebdatum) IS NOT NULL AND This part of the query needs to be redone AND CONCAT(roepnaam, ' ', COALESCE(tussenvoegsel, ' '), ' ', achternaam) IS NOT NULL ORDER BY maand, dag, achternaam -- __XXX__ (0-0) +--ooO--(_)--Ooo---+ | | | Olaf van Zandwijk| | | | ICQ# 30231605| | PGP Public Key: http://www.vanzandwijk.net/pgp.txt | +--+ -- __XXX__ (0-0) +--ooO--(_)--Ooo---+ | | | Olaf van Zandwijk| | | | ICQ# 30231605| | PGP Public Key: http://www.vanzandwijk.net/pgp.txt | +--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
Hello, I'm new to this list, and relatively new to SQL (well more rusty then new). Anyhow, I really need some assistance with the following query. When I made it up it worked fine, but then during my testing I deleted all of the info from the gallery_info table and that blew this query outta the water. I tried subsequently using joins (that I never was good at) and sub queries and neither seemed to work. With the sub query I kept getting an error as the MySQL version is 4.0.15 (prior to the 4.1 release where I believe the sub queries were brought in). Any help at all would be so greatly appreciated. This really needs to run as one query as so much of the subsequent code relies upon it. thanks Ed Peterson SELECT product_info.PRODINFO_ID, product_info.ARTIST_ID, product_info.GALLERY_ID, product_info.SUPPLIER_ID, product_info.PRODINFO_NAME, product_info.PRODINFO_COST, product_info.PRODINFO_SH_DESC FROM product_info, artist_info, gallery_info, supplier_info WHERE PRODINFO_DEL = 0 AND PRODINFO_SUSPEND_DATE = '-00-00 00:00:00' AND ((artist_info.ARTIST_ID = product_info.ARTIST_ID AND artist_info.ARTIST_SUSPEND_DATE = '-00-00 00:00:00' AND artist_info.ARTIST_DEL = 0 ) OR ((gallery_info.GALLERY_ID = product_info.GALLERY_ID AND gallery_info.GALLERY_SUSPEND_DATE = '-00-00 00:00:00' AND gallery_info.GALLERY_DEL = 0) OR (supplier_info.SUPPLIER_ID = product_info.SUPPLIER_ID AND supplier_info.SUPPLIER_SUSPEND_DATE = '-00-00 00:00:00' AND supplier_info.SUPPLIER_DEL = 0))) GROUP BY product_info.PRODINFO_ID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: The MYSQL account on Linux
Hi, Thanks - I'm familiar with the passwd command...this was actually a mysql question, as I wasn't asking how to change the password, but rather: would changing the password affect anything that was part of the processes supporting the database? I've been bitten by account passwords hiding, and changing them breaks a process that assumed the original password. As to why I wish to login as mysql: The mysql user owns the daemon, as well as any output files that are created by users to whom I have granted FILE [only two very trusted users]. I also have mysql dumps that are cronned and owned by mysql (to keep consistent with our oracle backups owned by the oracle user). I keep the mysql account directories uncluttered,not abused, and making use of the mysql user helps organize the administrative tasks. Regards, Julie -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 12:01 PM To: Myhre, Julie Cc: [EMAIL PROTECTED] Subject: Re: The MYSQL account on Linux Why do you want to login as mysql? This really is not a mysql question, but while logged in as root, enter the command: passwd mysql Myhre, Julie wrote: Hello, I started my current job with MySQL already installed on a Linux box, and no one seemed to know what the mysql account's password was I want to know/change the Linux password so I can login as mysql. So far it has been fine to become root and su to mysql, as I don't do it THAT often, but I need to stop using that route. Does anything besides user-created scripts, etc., care what the Linux mysql user has for a password? I don't want some process to break because I changed the account's password. Many thanks in advance, Julie Myhre This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: MySQL amp; NAT
gerald_clark said: If you are going out throught NAT to the mysql server, there should be no problem. If you are trying to come into the server through NAT you will need port forwarding. adburne wrote: Someone can make nat with mysql? I can do it with apache without problems, but mysql client freeze trying to connect. Thanx, Alejandro. You might also remember that unless you limit the outgoing connection to a specific NIC, there is no way that I know of to limit which stations inside the NAT can use the external mysql server, i.e., the 'host' address will be the public IP of the NAT firewall. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing Field Type
What command do I use to change the field type in my client_info table from: referral_percent | tinyint(4) | YES | NULL to referral_percent | double(3,2) unsigned zerofill | YES | NULL alter table tablename modify columnname double(3,2) unsigned zerofill, etc. The MySQL docs are not always that easy to wade through but they do have information and examples for such things. There are a couple of good sites you can visit on the web for basic SQL instruction: http://sqlzoo.net http://sqlcourse.com http://www.w3schools.com/sql/ http://www.firstsql.com/tutor.htm http://www.geocities.com/paikiran/articles/index.html -- John A. Sullivan III Chief Technology Officer Nexus Management +1 207-985-7880 [EMAIL PROTECTED] --- If you are interested in helping to develop a GPL enterprise class VPN/Firewall/Security device management console, please visit http://iscs.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Detecting data collisions (was Re: Preventing data collisions)
How do I prevent data collision in the following scenario: User A and User B are examining the same data (say a customer's name and address). User A modifies something (say the customer's phone number) and saves the transaction. User B modifies something different at the same time. How can I prevent User B's modification from being saved? Under MySQL, you can lock the a table during sensitive transactions. If you're using InnoDB tables, you can use row locking. May I ask for some input on taking this a step further. We are developing a complex network security management application - something akin to Solsoft, SmartPipes, or Checkpoint's Provider1 only with a more highly abstracted policy server (http://iscs.sourceforge.net). We need to ensure that multiple users distributed anywhere around the world can make extensive changes to the database and evaluate the security impact of those changes before committing the changes. These edit sessions can last from several minutes to hours and all the changes must be evaluated together. Thus it is not practical to lock the database for hours at a time. I'm not sure that the solution I have implemented is the best approach to this problem as I am truly a newbie and would like any idea on how to do this better. In our current design, when a user starts the application, we make a copy of the production database (called the edit database) and the user makes all changes to their own personal edit database. All of the SQL statements that alter the database are recorded in a StringList. We make sure that every field that has been changed is part of the where clause. When the user is satisfied with their changes and requests a commit, we begin a transaction on the production database and execute all the saved SQL statements. If there is either an error (e.g., a duplicate key) or zero items changed (implying that we did not match on a where statement that describes a record we thought existed) on any statement, we assume that another user made a conflicting change while this user was making their edits, rollback the transaction and throw away the edit session. Is there a better way to reconcile such large, time intensive, multi-user database edits? Thanks - John -- John A. Sullivan III Chief Technology Officer Nexus Management +1 207-985-7880 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL - MS SQL Server - relationale Datenbank
Ich hätte da einige Fragen an Sie ! A - Sie sprechen, wenn sie über MYSQL reden einmal von einer MySQL, die populärste Open Source SQL-Datenbank, wird von MySQL AB dann wiederum ist es ein MySQL ist ein Datenbank-Managementsystem oder ein MySQL ist ein relationales Datenbank-Managementsystem Was ist es denn nun eigentlich ? Das verwirrt den Laien, insbesondere mich ! Klärung des Ausdruckes SQL-Datenbank - MYSQL B - SQL = Structured Query Language Eine Sprache um Datenbankabfragen / Filteroprerationen auf Datenbanktabellen auszuüben. SQL ist keine spezifische Eigenschaft von MYSQL sondern auch andere Datenbanken unterstützen diese Operationsarten. C - MYSQL als Datenbank Hat man die Möglichkeit mit dem Programm MYSQL relationale Datenbanken neu anzulegen. Tabellen und Felder ( Feldtypen etc. ) zu definieren. Ist es möglich Verknüpfungen zwischen Tabellen mit MYSQL bei dieser Neuerstellung zu definieren ? Wenn nicht - So ist es für mich keine Datenbank. D - MYSQL als Datenbank - Managementsystem Ist es möglich über MYSQL Verbindungen zu schaffen zwischen verschiedenen Tabellen, Feldern verschiedener Datenbanken. Beispielfall - ( Extremfall ) Im Hintergrund können verschiedene Datenbanken stehen wie dBase, Access, DB2, delphi etc. MYSQL hat über die meisten vorhandene Schnittstelle SQL die Möglichkeit eine dBase Tabelle / Feld mit einer DB2 - Tabelle / Feld zu verbinden und gleichzeitig zur Anzeige zu bringen. Wo finde ich eine Internet Seite - Datenbanken im Vergleich - Ausdrücklich keine Managementsysteme. Habe ich das so einigermaßen verstanden ? Können Sie mir helfen bezüglich Vergleich von Datenbanken ? Mit freundlichen Grüßen W. von der Linden
piping blob into shell command (tar)
here's what im trying to do, i have a tar file in a blob field and i'm trying to retrieve it and pipe it directly into tar to decompress it, without first writing it to the hard drive, here's what i've tried so far, I create a text file called test1: use my_db; select * into dumpfile /usr/local/test1 from my_table; so when i try shell mysql --pager test1 | tar x the tar file does get written to /usr/local/test1 which is the step i'm trying to avoid, and nothing seems to get piped to tar? i also tried: use my_db; select * from my_table; so when i try shell mysql --pager test1 | tar x the tar file does not get written to /usr/local/test1 but i still dont get my untared files? write permissions are ok? what am i missing? any ideas would be appreciated thanks Denis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Execute shell script
At 14:05 -0600 11/18/03, Paul DuBois wrote: At 4:47 PM -0300 11/18/03, adburne wrote: There is a command or function to call a shell script through mysql? Invoke mysql, then issue a \h command and look in the output for the line that begins with system. I forgot to mention: The system command is Unix-only. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: piping blob into shell command (tar)
At 16:21 -0500 11/18/03, Denis Mercier wrote: here's what im trying to do, i have a tar file in a blob field and i'm trying to retrieve it and pipe it directly into tar to decompress it, without first writing it to the hard drive, here's what i've tried so far, I create a text file called test1: use my_db; select * into dumpfile /usr/local/test1 from my_table; so when i try shell mysql --pager test1 | tar x the tar file does get written to /usr/local/test1 which is the step i'm trying to avoid, and nothing seems to get piped to tar? Right, because you've told the SELECT to write its output to /usr/local/test1, not to its standard output. Hence, tar receives nothing. i also tried: use my_db; select * from my_table; so when i try shell mysql --pager test1 | tar x the tar file does not get written to /usr/local/test1 but i still dont get my untared files? write permissions are ok? what am i missing? Have you verified that the mysql command actually writes any output, for example, with this command: shell mysql --pager test1 | wc Do you get any output from tar at all, such as an error message? Maybe the output from mysql isn't suitable. Couple of things to try: 1) --pager should be omitted. It serves no function here. 2) You might want to add --raw to the mysql command -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL A B, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
KEVIN ZEMBOWER wrote: The organization hired an outside consultant to evaluate which SQL engine to go with. This is what he sent to us: ... SQL Server 2000 is a complete Relational Database Management System (RDBMS) that also includes integrated analysis functionality for OLAP and data mining. SQL Server 2000 meets the data and analysis storage requirements of the largest data processing systems and commercial Web sites, yet at the same time can provide easy-to-use data storage services to an individual or small business. I don't know whether the consultant wrote this himself, or if it came from somewhere. Answer: It came from somewhere. The above paragraph is a word-for-word quote from this Microsoft document: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/deploy/upgrdmigrate/mysql.asp Regards, PeterG -- Peter Gulutzan, Software Architect MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 13
Hi there, I'm running MySQL 4.0.16-nt (previously 3.23.5x, same system, same problem) on Windows 2000 Pro SP3 and almost constantly run against the following error (or similar) when I try to alter something about the structure of a table (MyISAM database): Error on rename of '.\BlackBoard\uni_Forums.MYI' to '.\BlackBoard\#sql2-350-c.MYI' (Errcode: 13) The problem can quite easily be resolved by restarting the MySQL server. Sometimes it works for a while then but right now I had to restart it once for any index change (create/drop index). Is this normal? It seems this only happens when the MySQL server is installed (not necessarily running) for some time. I don't know if a Windows restart would help, other applications and servers work perfectly. I just did a filesystem check on that NTFS partition, but no errors were found. Did anyone experience the same problem or is this just me having those weird problems all the time? -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL - MS SQL Server - relationale Datenbank
Hallo, MySQL ist selbst ein Datenbank-Server, d.h. man kann sich da hinverbinden, Datenbanken anlegen, die wieder Tabellen enthalten. Diese Tabellen können mehrere Spalten unterschiedlichen Typs haben, in die sich mit anderen Befehlen (SQL-Sprache) Daten einfügen lassen (fast genau wie in Access, nur dass es sich bei MySQL um einen Server handelt). Genauso kann man mit SQL-Anweisungen die Daten auch wieder auslesen, auch von mehreren Tabellen oder sortiert oder zusammengefasst etc. Alle Tabellen müssen sich in einer Datenbank befinden. Andere DBMS oder Tabellentypen wie Access oder DB/2 können meines Wissens nach nicht mit einbezogen werden, dafür wäre dann vielleicht ODBC gut (wovon ich nicht viel verstehe)? Auf der Seite www.mysql.com unter Documentation befindet sich der Link zum Vergleich der Datenbanksysteme (Crash-me), wo man verschiedene DBMS (MySQL in mehreren Versionen, Access, DB/2, Oracle uva.) auswählen kann und eine Feature-Übersicht bekommt. HTH Ach ja, die meisten auf dieser Liste werden wohl eher englisch sprechen, denk ich... -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) On Tuesday, November 18, 2003 12:58 PM CET, [EMAIL PROTECTED] wrote: Ich hätte da einige Fragen an Sie ! A - Sie sprechen, wenn sie über MYSQL reden einmal von einer MySQL, die populärste Open Source SQL-Datenbank, wird von MySQL AB dann wiederum ist es ein MySQL ist ein Datenbank-Managementsystem oder ein MySQL ist ein relationales Datenbank-Managementsystem Was ist es denn nun eigentlich ? Das verwirrt den Laien, insbesondere mich ! Klärung des Ausdruckes SQL-Datenbank - MYSQL B - SQL = Structured Query Language Eine Sprache um Datenbankabfragen / Filteroprerationen auf Datenbanktabellen auszuüben. SQL ist keine spezifische Eigenschaft von MYSQL sondern auch andere Datenbanken unterstützen diese Operationsarten. C - MYSQL als Datenbank Hat man die Möglichkeit mit dem Programm MYSQL relationale Datenbanken neu anzulegen. Tabellen und Felder ( Feldtypen etc. ) zu definieren. Ist es möglich Verknüpfungen zwischen Tabellen mit MYSQL bei dieser Neuerstellung zu definieren ? Wenn nicht - So ist es für mich keine Datenbank. D - MYSQL als Datenbank - Managementsystem Ist es möglich über MYSQL Verbindungen zu schaffen zwischen verschiedenen Tabellen, Feldern verschiedener Datenbanken. Beispielfall - ( Extremfall ) Im Hintergrund können verschiedene Datenbanken stehen wie dBase, Access, DB2, delphi etc. MYSQL hat über die meisten vorhandene Schnittstelle SQL die Möglichkeit eine dBase Tabelle / Feld mit einer DB2 - Tabelle / Feld zu verbinden und gleichzeitig zur Anzeige zu bringen. Wo finde ich eine Internet Seite - Datenbanken im Vergleich - Ausdrücklich keine Managementsysteme. Habe ich das so einigermaßen verstanden ? Können Sie mir helfen bezüglich Vergleich von Datenbanken ? Mit freundlichen Grüßen W. von der Linden -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: piping blob into shell command (tar)
On Tue, 2003-11-18 at 16:40, Paul DuBois wrote: At 16:21 -0500 11/18/03, Denis Mercier wrote: here's what im trying to do, i have a tar file in a blob field and i'm trying to retrieve it and pipe it directly into tar to decompress it, without first writing it to the hard drive, here's what i've tried so far, I create a text file called test1: use my_db; select * into dumpfile /usr/local/test1 from my_table; so when i try shell mysql --pager test1 | tar x the tar file does get written to /usr/local/test1 which is the step i'm trying to avoid, and nothing seems to get piped to tar? Right, because you've told the SELECT to write its output to /usr/local/test1, not to its standard output. Hence, tar receives nothing. i also tried: use my_db; select * from my_table; so when i try shell mysql --pager test1 | tar x the tar file does not get written to /usr/local/test1 but i still dont get my untared files? write permissions are ok? what am i missing? Have you verified that the mysql command actually writes any output, for example, with this command: shell mysql --pager test1 | wc if i dont use into dumpfile /usr/local/test1 mysql --pager test1 | wc wc: :2: Invalid or incomplete multibyte or wide character 21093 87836 I get this: with raw mysql --raw test1 | wc 2 2 11 Invoke mysql, then issue a \h command and look in the output for the line that begins with system. system (\!)Execute a system shell command. Im running redhat 9 Do you get any output from tar at all, such as an error message? Maybe the output from mysql isn't suitable. Couple of things to try: 1) --pager should be omitted. It serves no function here. 2) You might want to add --raw to the mysql command -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL A B, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizer Troubles
I've got a table with two indices -- one is a datetime with massive cardinality, and another is a varchar with only 9 distinct values. The optimizer seems to choose the varchar over the datetime in all cases unless I specifically say USE INDEX(). Is there some way to make MySQL smarter in this case? I tried analyzing the table, that didn't work. Maybe this is a case where I have to say that's why USE INDEX() exists. Strange thing is I didn't have this problem in 3.23 (running 4.0.16 now). Thanks for your help. -Rob- P.S. Here are some details: SELECT count(*) FROM table1 WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; 1 row in set (1 min 14.18 sec) SELECT count(*) FROM table1 USE INDEX(date1) WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; 1 row in set (0.00 sec) EXPLAIN SELECT count(*) FROM table1 WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; Table: table1 Type: ref Possible_keys: date1,status Key: status Key_len: 17 Ref: const Rows: 4548428 Extra: Using where EXPLAIN SELECT count(*) FROM table1 USE INDEX(date1) WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; Table: table1 Type: range Possible_keys: date1 Key: date1 Key_len: 9 Ref: NULL Rows: 16105 Extra: Using where show keys from table1; Table: table1 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 7911940 Sub_part: NULL Packed: NULL Null: NO Index_type: BTREE Comment: Table: table1 Non_unique: 1 Key_name: date1 Seq_in_index: 1 Column_name: date1 Collation: A Cardinality: 3955970 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Table: table1 Non_unique: 1 Key_name: status Seq_in_index: 1 Column_name: status Collation: A Cardinality: 9 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizer Troubles
Hi Rob, Since you're using 4.0.16, sounds like you are experiencing its optimizer bug. From the ChangeLog for 4.0.17 (not released yet): * Fixed optimizer bug, introduced in 4.0.16, when REF access plan was preferred to more efficient RANGE on another column. So hopefully the problem only exists in 4.0.16. Until you can use another version, I guess you'll have to use USE INDEX. :-/ Hope that helps. Matt - Original Message - From: Rob Brackett Sent: Tuesday, November 18, 2003 4:33 PM Subject: Optimizer Troubles I've got a table with two indices -- one is a datetime with massive cardinality, and another is a varchar with only 9 distinct values. The optimizer seems to choose the varchar over the datetime in all cases unless I specifically say USE INDEX(). Is there some way to make MySQL smarter in this case? I tried analyzing the table, that didn't work. Maybe this is a case where I have to say that's why USE INDEX() exists. Strange thing is I didn't have this problem in 3.23 (running 4.0.16 now). Thanks for your help. -Rob- P.S. Here are some details: SELECT count(*) FROM table1 WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; 1 row in set (1 min 14.18 sec) SELECT count(*) FROM table1 USE INDEX(date1) WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; 1 row in set (0.00 sec) EXPLAIN SELECT count(*) FROM table1 WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; Table: table1 Type: ref Possible_keys: date1,status Key: status Key_len: 17 Ref: const Rows: 4548428 Extra: Using where EXPLAIN SELECT count(*) FROM table1 USE INDEX(date1) WHERE date1 NOW() - INTERVAL 1 HOUR AND status='status1'; Table: table1 Type: range Possible_keys: date1 Key: date1 Key_len: 9 Ref: NULL Rows: 16105 Extra: Using where show keys from table1; Table: table1 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 7911940 Sub_part: NULL Packed: NULL Null: NO Index_type: BTREE Comment: Table: table1 Non_unique: 1 Key_name: date1 Seq_in_index: 1 Column_name: date1 Collation: A Cardinality: 3955970 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Table: table1 Non_unique: 1 Key_name: status Seq_in_index: 1 Column_name: status Collation: A Cardinality: 9 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help DB Error: unknown error
Hi Thai, I think you're just joining wy too many tables! LOL Matt - Original Message - From: Thai Thanh Ha Sent: Sunday, November 16, 2003 8:10 PM Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131
Re: Feature wish
Hi Gunnar, I agree, this should be optimized. It says in the Internals manual, I believe, that the code already exists (in what version(s) I don't know) to optimize such searches on multiple indexes but it just isn't implemented yet. Hopefully it will be soon... Matt - Original Message - From: Boehn, Gunnar von Sent: Tuesday, November 18, 2003 4:23 AM Subject: Feature wish Hi, I wonder if a small enhancement to the mysql planner/optimizer could be made. The current (mysql4.0) optimizer only uses one index per SELECT. Because of this some queries result in a slow fulltable scan even if all used columns are indexed. Example: SELECT * FROM table1 WHERE columnA='x' OR columnB='x' (Slow table scan even if columnA and columnB are indexd! Other Databases like PostgreSQL don't have this problem.) Since Mysql 4.0 a UNION can be used to speed this up. Example: SELECT * FROM table1 WHERE columnA='x' UNION SELECT * FROM table1 WHERE columnB='x' (Now we have two SELECT with one index each.) Couldn't the planner/optimizer be easely changed to do this UNION automaticly ? I think this would be a big improvement to mysql. If I port applications from other databases to mysql there are often queries like this. Either those queries run terrible slow an mysql or I have to manually convert them into UNIONs. Thanks in advance Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: piping blob into shell command (tar)
Please reply to the list, not to me personally, so that others can follow this discussion. At 17:05 -0500 11/18/03, Denis Mercier wrote: On Tue, 2003-11-18 at 16:40, Paul DuBois wrote: At 16:21 -0500 11/18/03, Denis Mercier wrote: here's what im trying to do, i have a tar file in a blob field and i'm trying to retrieve it and pipe it directly into tar to decompress it, without first writing it to the hard drive, here's what i've tried so far, I create a text file called test1: use my_db; select * into dumpfile /usr/local/test1 from my_table; so when i try shell mysql --pager test1 | tar x the tar file does get written to /usr/local/test1 which is the step i'm trying to avoid, and nothing seems to get piped to tar? Right, because you've told the SELECT to write its output to /usr/local/test1, not to its standard output. Hence, tar receives nothing. i also tried: use my_db; select * from my_table; so when i try shell mysql --pager test1 | tar x the tar file does not get written to /usr/local/test1 but i still dont get my untared files? write permissions are ok? what am i missing? Have you verified that the mysql command actually writes any output, for example, with this command: shell mysql --pager test1 | wc Do you get any output from tar at all, such as an error message? Maybe the output from mysql isn't suitable. Couple of things to try: The output from mysql seems fine because when it does write the value of my blob I get a well formatted tar file, I'm thinking that maybe tar cannot handle a stream as it's input? That may be. In that case, you should invoke tar as tar xf - rather than as tar x -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help DB Error: unknown error
Typically, this is the kind of query I see at MS-SQL houses. :) -- R. Deuce - Original Message - From: Matt W [EMAIL PROTECTED] To: Thai Thanh Ha [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:28 PM Subject: Re: Please help DB Error: unknown error Hi Thai, I think you're just joining wy too many tables! LOL Matt - Original Message - From: Thai Thanh Ha Sent: Sunday, November 16, 2003 8:10 PM Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile ... snip ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php and passing implicit connection identifiers
Hi Stephen, You can always omit the connection id in PHP (unless you have multiple connections open for some reason). Matt - Original Message - From: Stephen Fromm Sent: Monday, November 17, 2003 8:26 AM Subject: php and passing implicit connection identifiers The connection identifier returned by mysql_connect() need not be passed to calls to MySQL-related functions. What is the scope of this claim? In particular, if I open a connection and then call a function I wrote myself, which then calls a MySQL related function, can I still omit the connection identifier? TIA, sjfromm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help DB Error: unknown error
At 9:10 +0700 11/17/03, Thai Thanh Ha wrote: Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Joins can join up to 31 tables in MySQL. Looks like you're exceeding that limit. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131 AND t18.FieldValue = 1 AND t1.UserID=t19.UserID AND t19.FieldID = 131 AND t19.FieldValue = 3 AND t1.UserID=t20.UserID AND t20.FieldID = 137 AND
RE: Detecting data collisions (was Re: Preventing data collisions)
Thank you. That sounds great but I have a few concerns. Within the overall user edit session (I suppose this would be one big transaction held for potentially several hours if I use only a single database and row versioning), some series of changes must be handled as their own transaction -- a nested transaction. How would I do this if the entire edit session is wrapped in a transaction? I suppose I could use savepoints. However, although I see that savepoints are supported in InnoDB 4.0.14 and 4.1.1, I did not see anything about savepoints being available in the MySQL implementation of InnoDB. Are they? I'm a little concerned about the index locking. I suppose I could live with users not being able to change fields in a record another user has changed even if the other user has not changed those specific fields; that could be dangerous anyway. But I am concerned about having insertions blocked around a record that has been changed. The literature recommends committing transactions often but, in my application, using row level locking to control concurrent multi-user access over the entire potentially multi-hour edit session seems to contradict this advice. Am I misapplying this caution from the InnoDB folks? Please don't take these questions negatively. Being able to use this rather than what we have already done would be great. I just don't want to create more problems than I solve by misapplying the technology. We had originally considered using it instead of creating separate edit databases but dismissed it because of the above concerns. I would be very happy to be wrong. Thanks - John On Tue, 2003-11-18 at 16:10, Jon Frisby wrote: Have you considered looking at InnoDB's row versioning? The primary drawback is that once a row has been modified by a user, that user has an exclusive write-lock on the row, so another user cannot modify the same row -- however the user can READ the row, and will see the old version of the row until the new one is committed. This exclusion prevents the synchronization problems you're talking about, allows users to see their own consistent view of the entire policy set, and makes rolling back changes easy (issue a ROLLBACK). Just be sure to set the connection timeout value very high to avoid unexpected dropped connections (I think it defaults to 8 hours). -JF -Original Message- From: John A. Sullivan III [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 12:54 PM To: Brian Reichert; [EMAIL PROTECTED] Subject: Detecting data collisions (was Re: Preventing data collisions) How do I prevent data collision in the following scenario: User A and User B are examining the same data (say a customer's name and address). User A modifies something (say the customer's phone number) and saves the transaction. User B modifies something different at the same time. How can I prevent User B's modification from being saved? Under MySQL, you can lock the a table during sensitive transactions. If you're using InnoDB tables, you can use row locking. May I ask for some input on taking this a step further. We are developing a complex network security management application - something akin to Solsoft, SmartPipes, or Checkpoint's Provider1 only with a more highly abstracted policy server (http://iscs.sourceforge.net). We need to ensure that multiple users distributed anywhere around the world can make extensive changes to the database and evaluate the security impact of those changes before committing the changes. These edit sessions can last from several minutes to hours and all the changes must be evaluated together. Thus it is not practical to lock the database for hours at a time. I'm not sure that the solution I have implemented is the best approach to this problem as I am truly a newbie and would like any idea on how to do this better. In our current design, when a user starts the application, we make a copy of the production database (called the edit database) and the user makes all changes to their own personal edit database. All of the SQL statements that alter the database are recorded in a StringList. We make sure that every field that has been changed is part of the where clause. When the user is satisfied with their changes and requests a commit, we begin a transaction on the production database and execute all the saved SQL statements. If there is either an error (e.g., a duplicate key) or zero items changed (implying that we did not match on a where statement that describes a record we thought existed) on any statement, we assume that another user made a conflicting change while this user was making their edits, rollback the transaction and throw away the edit session. Is there a better way to reconcile such large, time
Re: [PHP-DB] TABLE_PRIV
At 9:02 +0200 11/18/03, Nikos Gatsis wrote: And the columns: Grantor, Timestamp Table_priv, Column_priv what is up to? Ignore the Grantor and Timestamp columns. They're not used. Thanx again - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Nikos Gatsis' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, November 17, 2003 3:37 PM Subject: RE: [PHP-DB] TABLE_PRIV table_privs is going to assign privileges at the individual table level grant select on database.tablename to user@'192.168.0.10' IDENTIFIED BY 'password' -Original Message- From: Nikos Gatsis [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 7:20 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] TABLE_PRIV Hello List. Does anybody knows how to configure table_priv table of Mysql? I didn't find something in manual I have v. 3.23.41 in Linux Thanx Nikos -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sendmail+mysql
Hi all, Is it possible for sendmail to work with mysql under linux environment? I am finding a way to handle sendmail alias by using mysql. Does anyone can tell me how to do it? Any reference site? unplug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql API for Delphi
Hello All, can someone tell me where to get the latest mysql API for Delphi (mysql.pas) that works with mysql library version 4 ? i already check the author's site.. and no update yet. i also found some problem connecting to mysql version 4.1 alpha with mysqlfront.. it says that the client did not support the authentication (or something like that.. i forgot to write it down :) ) and replacing the libmysql.dll wont work any idea? tia -leo-
mysqld restarting
Hello all, Please forgive what seems like a basic question, but I'm noticing that my mysql server (4.0.16) is restarting every couple of days. I recently upgraded from 3.23.53, and it was doing something similar. Problem is that I'm not sure how to go about diagnosing this problem, and I'm concerned that it might cause some corruption in one of the databases. This is on Solaris 8. Actually I've got two nearly identical servers running the same setup, each replication slaves to the other (but only one is actively taking connections and answering queries). The problem only occurs on the server with activity on it (it's also running apache, php, etc) and I suspect that it's a memory problem, although that doesn't completely make sense either. Here's what I'm seeing in my logs with 4.0.16: Number of processes running now: 0 031118 07:33:27 mysqld restarted 031118 7:33:30 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... What it comes down to is that I'm not sure how to begin diagnosing this. The log file isn't telling me much, I'm not seeing anything abnormal in the admin server logs, and I'm not sure where else to look. Could someone point me in the right direction for solving this problem? And I'm also looking to confirm that this is mysqld actually crashing and restarting. Thanks in advance, Shane Iseminger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sendmail+mysql
Thanks. Have you tried it before? Does it work or not. I found that the installation process is very complicated. There are 3 sendmail rpm files. Do I need to install them before applying the patches? Greg Donald wrote: On Wed, 19 Nov 2003, unplug wrote: Is it possible for sendmail to work with mysql under linux environment? I am finding a way to handle sendmail alias by using mysql. Does anyone can tell me how to do it? Any reference site? http://sourceforge.net/projects/sendmail-sql -- Greg Donald http://destiney.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Standard vs. Log
I have mysql on linux on two machines. Both version 4.0.16. On one machine, it is reported as 4.0.16-standard and on the other machine it is reported as 4.0.16-log (according to PHPMyAdmin). What is the difference (obviously something with logging) and how can I change from standard to log and vice versa? Thanks * Jon Rosenberg www.DeanForAmerica.com www.OhioForDean.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] TABLE_PRIV
ok thanx - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Nikos Gatsis [EMAIL PROTECTED]; Victor Pendleton [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 2:58 AM Subject: Re: [PHP-DB] TABLE_PRIV At 9:02 +0200 11/18/03, Nikos Gatsis wrote: And the columns: Grantor, Timestamp Table_priv, Column_priv what is up to? Ignore the Grantor and Timestamp columns. They're not used. Thanx again - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Nikos Gatsis' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, November 17, 2003 3:37 PM Subject: RE: [PHP-DB] TABLE_PRIV table_privs is going to assign privileges at the individual table level grant select on database.tablename to user@'192.168.0.10' IDENTIFIED BY 'password' -Original Message- From: Nikos Gatsis [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 7:20 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] TABLE_PRIV Hello List. Does anybody knows how to configure table_priv table of Mysql? I didn't find something in manual I have v. 3.23.41 in Linux Thanx Nikos -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
