Re: why can not pass constant to stored procedure?
On 10 Mar, 2012, at 7:06 pm, Cifer Lee wrote: when we call procedure normally we declare the parameter out of the procedure and pass the variable to procedure like this set @x=1; call *a_procedure*(@x); why can not directly pass the digit 1 to the* a_procedure* ? You can pass the argument directly as long as it is not an INOUT parameter. Antony T Curtis atcur...@gmail.com 0523 C487 9187 6972 6894 AEC7 3087 F819 B477 B687
Re: ::1 root entry in mysql.user
::1 is the IPv6 address for localhost. On 18 Dec 2011, at 09:17, lourenstcc wrote: Hi, I installed mysql for mac os x. Now I am inspecting mysql.user and I see entries for root which I am not to confident with. On a debian installation there is no host=::1 entry Can you explain this entry? (Could I have inadvertantly made it myself?) Here: mysql select user, host, password from mysql.user; +---+ +---+ | user | host | password | +---+ +---+ | root | localhost | | | root | mbprebel.local | | | root | 127.0.0.1 || | root | ::1 | | Thanks, Lourens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
In case you all missed it.
mysql select date_format(now(),'%m-%d%-%y %h:%i:%s') AS time; +---+ | time | +---+ | 11-11-11 11:11:11 | +---+ 1 row in set (0.00 sec)
Re: large temp files created by mysql
Something you're doing is creating a very large temporary table as part of handling it's query. Usual culprit would be something doing a full table join combined with an order by or group by which would typically cause MySQL to need to create a temp table. You should do EXPLAINs on your slow-running queries to find out which ones are likely... If it is generating a 30gig file, I'd expect it must be a very slow query. Regards Antony, On 24 Oct 2011, at 08:03, Joey L wrote: On Mon, Oct 24, 2011 at 10:52 AM, Joey L mjh2...@gmail.com wrote: I have a very large table - approx 3 or 4 gig in size. When i initiate a process on my webpage - mysql starts to create a temporary table in /tmp directory. Is there a way i can create this file/table ahead of time so mysql does not have to create it ? thanks mjh Sorry - it is 30 gig file . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=antony.cur...@ieee.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slower performance with LOCK TABLES
Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Binary builds for AIX
Alas, I mothballed my old RS/6000 AIX machine a few years ago. It was getting quite old and only ran AIX 4.3 I had toyed with the idea of getting a more modern machine from ebay but to be honest, I haven't had much time recently. On 9 Sep 2011, at 08:22, Peter Gershkovich wrote: I noticed that there is no binary builds for AIX any more. What would be the best way to install a current version of MySQL (5.5) on AIX? Specifically I am looking for instructions for AIX 6.1 and/or 7.1 Thanks, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=antony.cur...@ieee.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: selecting the 'best' match
in your code, you can define ranges of say if the model year being looked for is 2002, then present model years 2000 thru 2004. --Curtis blackwater dev wrote: Thanks but doing it in code would require me to pull in the entire car table and process it. With potentially tons of rows, seems like I should be able to use the db to get those. On Tue, May 12, 2009 at 12:23 PM, Johan De Meersman vegiv...@tuxera.bewrote: you *could* go with if-statements, returning a numerical weight for each criterion if match and 0 if not; summing those and sorting by the sum column. I would do it in code, though - it may or may not be less efficient, but it'll be easier to maintain and read. On Tue, May 12, 2009 at 5:50 PM, blackwater dev blackwater...@gmail.comwrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Thanks! -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: Question on replication terminology
I think what's really being sought after, here is clustering. --C Eric Bergen wrote: Dual master replication can be either dual master dual write or dual master single writer. The latter is preferred. In this configuration replication is connected in both directions but clients only ever connect to one master at a time. It's just as safe as master - slave replication if you handle the failover correctly. -Eric On Tue, Apr 28, 2009 at 3:43 PM, Claudio Nanni claudio.na...@gmail.com wrote: Hi there, I would only like to stress that the only supported (and recommended) replication solution in MySQL is Master---Slave replication. In this scenario you can have ONLY one master and (virtually) any number of slaves. There is NO other safe replication solution. The terms you mention seems to refer to the same solution, where you have two servers each acting as a master: this is a non standard dangerous scenario in MySQL and requires application logic awareness. Hope to have brought a little light in your mind Cheers Claudio Vikram Vaswani wrote: Hi I'm new to replication and looking through some docs on how to use it. Could someone please tell me if the following terms mean the same thing or, if not, what is the difference: master-master replication dual-master replication bidirectional replication TIA -BT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com
Re: Sun bought by Oracle
I figure that they'll either kill mysql or they'll limit the commnunity version in ways that will make you purchase a commercial version if you want to continue to use it. I figure there will be heavy migrations to open source alternatives. --C Andy Shellam wrote: I've just been made aware by a client that Oracle have purchased Sun Microsystems. The article below on Sun's website mentions that Oracle are committed to Linux and other open platforms and mentions the fact that Java touches practically every business system around. http://www.sun.com/third-party/global/oracle/index.jsp I wonder what Oracle's plans are when it comes to MySQL? There is no mention of MySQL in the above article. Will it eventually come under the Oracle umbrella, much like BerkeleyDB did? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cant get TRIM to work?
http://www.mydigitallife.info/2007/04/23/remove-or-trim-first-or-last-few-characters-in-mysql-database-with-sql/ Richard Reina wrote: Hello All, I can't get trim to trim the blank space from a TEXT field in the query below and was wondering if someone could tell what I am doing wrong? SELECT TRIM(notes) FROM work_notes; Thanks for any help as I am at a complete loss. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: From MS Access to MySQL
I have taken a Microsoft Access database and have basically copied the structure of it and rebuilt it in MySQL, however, I have encountered some problems with formatting of data. I need to ask what is the most suitable field type to use to retain the content from Access field types The MS Access fields are: Currency (formatted to £ with two decimal places e.g. £10.00) DECIMAL(nnn,2) You'll have to add your own signage ($,£, etc.) upon extraction. Currency (formatted to £#,##0.00;(£#,##0.00) Date/Time (with a Now() default) From the manual: The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in '-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '-12-31 23:59:59'. Yes/No enum() or set() Number (as a percentage) decimal() Number (with field size of Double and Decimal Places of 2) decimal() What would be the equivalent of the above for MySQL 5.1.35? If there isn't an equivalent for some, how can I work around it considering that the Access version of the database is already used in an ASP website? see: http://dev.mysql.com/doc/refman/5.1/en/data-types.html --Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] multiple choice dropdown box puzzle
You're looking for something like: This gets called 10 times from another function, but this is sort of what you're looking for. This gives me a combo-box. function qselect($mysql_link, $i) { $driverquery = select car_no, drv_name from cars order by car_no + 0; $driverresult = mysql_query($driverquery, $mysql_link); print(select name='pick$i'\n); while ($driverrows = mysql_fetch_array($driverresult)) { print( option value = '$driverrows[0]'$driverrows[1]/option\n); } print( /select\n); } HTH Curtis Afan Pasalic wrote: PJ wrote: I think this is a tough one... and way above my head: PLEASE READ ALL OF THE ABOVE TO UNDERSTAND WHAT I AM TRYING TO DO. Having a bit of a rough time figuring out how to formulate php-mysql to insert data into fields using a multiple dropdown box in a form. to post I am using the following: snip... $categoriesIN= $_POST[categoriesIN]; ...snip... select name=$categoriesIN[] multiple=multiple OPTIONChoose Categories.../option OPTION VALUE=? echo $categoriesIN; ?1 OPTION VALUE=? echo $categoriesIN; ?2 OPTION VALUE=? echo $categoriesIN; ?3 OPTION VALUE=? echo $categoriesIN; ?4 OPTION VALUE=? echo $categoriesIN; ?5 /SELECT ...snip... $sql4 = FOR ( $ii = 0 ; $ii count($categoriesIN) ; $ii++ ) INSERT INTO temp (example) $categoriesIN[$ii] ; $result4 = mysql_query($sql4, $db); ...snip this does not work! The other posts work like a charm... but this... I cannot figure out what I should be entering where... I have tried several different configurations, but nothing seems to work... I found this as a model for entering the selections but can't figure out how to modify it for my needs: select name=branch_no[] multiple=multiple size=5 option Choose your location(s) /option option value=31003100/option option value=31053105/option option value=3503 3503/option option value=3504 3504/option /select What I would like to do is something like the following: select name=$categoriesIN[] multiple=multiple OPTIONChoose Categories.../option OPTION VALUE=1History OPTION VALUE=2Temples OPTION VALUE=2Pharaohs and Queens OPTION VALUE=4Cleopatra OPTION VALUE=4Mummies /SELECT and going further, I would like to be able to use a table that actually holds these values to feed them to the code above. I am sure this is possible but it must take some huge knowledge and experience to do it. BUT ... as I look at things, I am wondering if the FOR statement in the above should be used to do several INSERTs, that is, one $sql(number) per selected category... now, would that require many $sqls or many INSERTs within the $sql ? first, I think, $categoriesIN is string, but in the form you made it as an array $categoriesIN[]. I think you have to modify it a little bit, something like {$categoriesIN}.'[]' second, I think the php part FOR ( $ii = 0 ; $ii count($categoriesIN) ; $ii++ ) can't be part of the mysql statement, it should be outside the statement FOR ( $ii = 0 ; $ii count($categoriesIN) ; $ii++ ) { $sql4 = INSERT INTO temp (example) $categoriesIN[$ii] ; $result4 = mysql_query($sql4, $db); } afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie First Use Connection Question - Mac OSX 10.5.6
or as the docs read: shell mysqladmin password your password John Daisley wrote: The root Password will be blank after initial install. You can set it at a shell prompt with commands something like this... shell mysql -u root mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd'); mysql FLUSH PRIVILEGES; mysql EXIT; Where newpwd is your desired password. Best to secure or delete all accounts with blank passwords :) You can then start MySQL administrator and log in using the username root, the password you specified in the SET PASSWORD command and the host of localhost. Regards John I am brand new to MySQL and JAVA/Netbeans 6.5 so please excuse the stupid questions ... 1. I have just downloaded and successfully installed MySQL v5.1 on my MacBook Pro running OS X 10.5.6 2. I have also downloaded and installed MySQL Tools: Administrator Query Browser (I come from a Visual Basic MS SQL Server 2000/2005 environment) When you install MS SQL server the default login is sa with a blank password. My question is; How do I login (connection settings) to MySQL (for Administrator and Query Browser tools)? (I start my server by going to settings and then MySQL icon, Start Server, so my server is running) Unfortunately, I have never seen MySQL in action nor do I know anyone to ask/show me how to get started. __ This email has been scanned by Netintelligence http://www.netintelligence.com/email
Re: non-relational engine for mySQL?
I believe such things already exist, for example the Nitro storage engine. There is a presentation about it at the coming MySQL conference... http://en.oreilly.com/mysql2009/public/schedule/detail/6984 Regards, Antony On 6 Feb 2009, at 14:50, Daevid Vincent wrote: When our database reaches the 10-100TB range, we may need to consider non-relational databases. Relational databases like MySQL tend to heavily rely on random access, which is governed by the slow disk seek rates. On the other hand, non-relational database pioneered by Google's Map/Reduce framework operate at much faster disk transfer rates. Open source solutions incorporating these ideas exist, such as Yahoo's Hadoop and CouchDB. Are there currently (or plans for) any mySQL engines with this type of database? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to convert Acess 2007 ACCDB file to MySQL?
I'm not plugging the product, but I just ran into this: http://www.dbconvert.com/product.php It's $79.00. --Curtis Dan Nelson wrote: In the last episode (Dec 29), mos said: Someone has given me an Access 2007 file *.ACCDB and I don't have Access 2007. Is there a (preferably free) way to convert it to CSV or MySQL? Try the ODBC driver downloadable at 2007 Office System Driver: Data Connectivity Components http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891 You should then be able to connect to the database via any ODBC-capable client (e.g. OpenOffice Base, or even an older version of Access or Excel) and export the tables in whetever format you want.
Re: Is it a bug or my mistake in server configuration?
I've been having the same trouble in a Xen virtual machine. After about an hour and a half, mysql will be consuming 100% of cpu. There is nothing wrong with the tables. I'm assuming its a dynamic vs. fix amount of memory available to mysql. I'm guaranteed x amount of ram, but that might get reduced due to server load. I'm assuming mysql doesn't like having ram taken away from it and get into a tizzy about it. I've been forced to restart mysql hourly in order to get smooth operation. --curtis Alexey Vlasov wrote: Hi. One client from my shared hosting periodically informs me about an error: DBI connect('database,...) failed: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug at ... There's nothing suspicious in the MySQL error-log. # free -m total usedfree sharedbuffers cached mem:16039 15794 245 0 2109 6935 -/+buffs/cache: 6748 9290 Swap:28615 502123594 my.cnf: flush_time = 1800 set-variable = long_query_time=10 set-variable = back_log=1024 set-variable = max_connect_errors=1000 set-variable = max_connections=64 set-variable = connect_timeout=20 set-variable = wait_timeout=600 set-variable = interactive_timeout=600 set-variable = table_cache=1000 set-variable = thread_cache_size=16 set-variable = max_tmp_tables=8192 set-variable = max_heap_table_size=64M set-variable = tmp_table_size=256M set-variable = max_join_size=5000 set-variable = key_buffer_size=512M set-variable = read_buffer_size=128K set-variable = read_rnd_buffer_size=64K set-variable = sort_buffer=128M set-variable = join_buffer_size=64M set-variable = net_buffer_length=64K set-variable = query_cache_type=1 set-variable = query_cache_size=256M set-variable = max_allowed_packet=16M set-variable = ft_min_word_len=3 # ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) 2097152 scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 143360 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 143360 virtual memory (kbytes, -v) 4194304 file locks (-x) unlimited # ps PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4728 mysql 20 0 2045m 1.0g 5620 S0 6.3 2602:15 mysqld # pstree | grep mysql |-mysqld---29*[{mysqld}] # mysql --version mysql Ver 14.12 Distrib 5.0.54, for pc-linux-gnu (x86_64) using readline 5.2 # uname -a Linux 2.6.24 #4 SMP Fri Feb 29 20:10:01 MSK 2008 x86_64 Intel(R) Xeon(R) CPU E5345 @ 2.33GHz GenuineIntel GNU/Linux I would like to know against what limit rests MySQL and whose mistake it really is, of Perl mysql-client, mysqld or someone else? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install Microsoft.Jet
You need the mdac components. free download from MS. Sivasakthi wrote: Hi all, I have tried to import the excel to db , but i get the following error, The OLE DB provider Microsoft.Jet.OLEDB.4.0 has not been registered. how can i install the Microsoft.Jet? System Info: OS Name Microsoft(R) Windows(R) Server 2003, Enterprise Edition for 64-Bit Itanium-based Systems System Type Itanium (TM) -based System Processor ia64 Family 31 Model 1 Stepping 5 GenuineIntel ~1300 Thanks, Siva -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why people don't use engine named BDB?
Its mainly because it was purchased by Oracle. BDB provided transaction support. Innodb has been the defacto choice for a ACID transactions, but Innodb was also purchased by Oracle in its attempt to kill MySQL after its failed attempt to purchase MySQL. That's why MySQL has been working on their own storage engine as well as the pluggable storage system. Curtis David Giragosian wrote: On 7/21/08, Moon's Father [EMAIL PROTECTED] wrote: Any reply is appreciated . -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn Maybe something to do with this: *BDB support will be removed. * Note that, as of MySQL 5.1, BDB isn't supported any longer. http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html But you're right that as a storgage engine, there have been very few questions related to it, on this mailing list anyway.
Re: Accessing remote machine (Ubuntu) from Window
I cannot. Are you trying to connect via the localhost. It may still be trying to connect to localhost. You might try changing the bind-address statement to bind-address = 0.0.0.0 so that it also listens to localhost as well. Also if you're trying to connect to an ip address instead of a named host, I've found that you have to code your grant statement to have an ip address instead of a hostname. Curtis Jesse wrote: Obvious question: Did you restart MySQL? netstat -l should show you what's listening for connections. you'll want to see if its listening on port 3306. Yes. When that didn't work, I re-started the whole server. netstat -l tells me that 192.168.1.128:mysql is listening. It lists a foreign address of *.* When I re-issue the command with -n, I can seee that it is, indeed, listening on port 3306. Can you think of any other reasons why the connection would fail? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Accessing remote machine (Ubuntu) from Window
I just checked my ubuntu config and I have: Port = 3306 Bind-address = 0.0.0.0 Pid-file = /var/run/mysqld/mysqld.pid Socket = /var/run/mysqld/mysqld.sock Nothig is in upper case. My phone is doing that for me. -Original Message- From: Jesse [EMAIL PROTECTED] To: Curtis Maurand [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: 7/17/2008 4:46 PM Subject: Re: Accessing remote machine (Ubuntu) from Window I was wondering how I get it to listed on all ports. When I try 0.0.0.0, and try to restart, the restart fails, and when it tries to start again, I get the error, /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password:YES)' This has been happening for a day or so, and I don't know if it's related to my problem or not. After changing the grant to use an IP address, I still cannot log in. Jesse [truncated by sender] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ***SPAM*** RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
grant all on *.* to root@'%.def.com'; /* The percent sign is your wildcard character. */ flush privileges; I don't think you need to flush privileges as of 5.0. I still do just to be sure. Curtis [EMAIL PROTECTED] wrote: Hi Parikh, Yes.. It worked with IP i.e when I granted privileges to 'root'@'ip of my machine' and flushed it, it worked fine. But I have a qestion below: GRANT ALL PRIVILEGES ON *.* to 'root'@'IP'; FLUSH PRIVILEGES; However, Would '*.def.com' work in the place of IP?, as I feel that giving privileges for each IP for each machine that is going to access the mysql server would be tedious and not recommended choice. Please let me know. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:34 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Ok It means that DNS not configured so better give the IP address instead of hsostname and check using in mysql as Show grants for [EMAIL PROTECTED]; This should show u the grants Thanks Regards, Dilipkumar MphasiS an EDS Company | No 25,Steeple Reach,Cathedral Road | Chennai | India | 91 44 28113801 |Extn 2216 Mobile: 9884430998 | 9962029004 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:30 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED
Re: Running 2 versions of MySQL on same server
Which *n?x distribution are you running? [EMAIL PROTECTED] wrote: No, because I fear that would break the existing customer php apps, which is the whole point of running two versions of MySQL. What I guess I need to know is if php's mysql and mysqli extensions can be made to recognize two different sets of client libs. Unless you're telling me that having them use the 5.0 client libraries won't break working 3.23 apps... On Wed, 2 Jul 2008, Curtis Maurand wrote: Did you rebuild php against the 5.0 libraries as I suggested yesterday? If you didn't it will only recognize the 3.23 version. It will not be able to talk to the 5.0 version. Curtis [EMAIL PROTECTED] wrote: It would appear that the problem isn't getting MySQL 3.23 and 5.0 to run on the same server with different ports and sockets, but rather getting mod_php to recognize both clients. phpinfo() shows only 3.23, since it's in the standard path. If anyone has experience getting two different versions recognized by php, I'd greatly appreciate input. Thanks! James Smallacombe PlantageNet, Inc. CEO and Janitor [EMAIL PROTECTED]http://3.am = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] James Smallacombe PlantageNet, Inc. CEO and Janitor [EMAIL PROTECTED]http://3.am = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 2 versions of MySQL on same server
Did you rebuild php against the 5.0 libraries as I suggested yesterday? If you didn't it will only recognize the 3.23 version. It will not be able to talk to the 5.0 version. Curtis [EMAIL PROTECTED] wrote: It would appear that the problem isn't getting MySQL 3.23 and 5.0 to run on the same server with different ports and sockets, but rather getting mod_php to recognize both clients. phpinfo() shows only 3.23, since it's in the standard path. If anyone has experience getting two different versions recognized by php, I'd greatly appreciate input. Thanks! James Smallacombe PlantageNet, Inc. CEO and Janitor [EMAIL PROTECTED]http://3.am = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger that calls a webservice??
On 20 Jun 2008, at 06:43, James wrote: On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for mysql external command trigger. You can declare a stored proc as an XMLRPC request and use that as a trigger. Such functionality already works in our experimental tree. http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Regards Antony. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JAVA UDF HOW
Hi! MySQL is an open-source product. That allows anyone to contribute patches and new features from the community. In time, when the feature is mature, it may be included in some future version. Documentation on this feature is very limited because the (2) people behind the project have only pursued it during their personal free time. The code is of alpha quality and the target audience is mostly aimed at people of expert knowledge who can largely figure out how to use it without the aid of verbose documentation. Of course, if anyone is willing to contribute documentation, it would be greatly appreciated. The forge site is a wiki: Anyone may add stuff to it. Regards, Antony On 5 Jun 2008, at 05:05, Abhayjeet Singh Grewal wrote: Thanks Everybody, I went through the project and was a little dissapointed with MySQL not supporting Java natively as yet. Let's hope we include this support in coming future. Also, there is not enough documentation for the project mentioned: http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Thanks again !! Abhay Grewal On Thu, Jun 5, 2008 at 11:04 AM, Antony T Curtis [EMAIL PROTECTED] wrote: Hi, Check out this link for Java stored procedures with MySQL http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures It is probably what you are looking for. I have some more info on my blog at http://antbits.blogspot.com/ For more information, you can email Eric Herman [EMAIL PROTECTED] or you can email me. Currently, you will not see any Java Stored Procedure functionality in any official MySQL release but I plan to maintain a fork with this feature. Soon, I plan to be in a position to build 'preview' binaries for Linux, FreeBSD, Windows and MacOS. Regards, Antony Curtis, (Not speaking on behalf of my current or any prior employer) On 4 Jun 2008, at 01:39, Abhayjeet Singh Grewal wrote: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal
Re: JAVA UDF HOW
Hi, Check out this link for Java stored procedures with MySQL http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures It is probably what you are looking for. I have some more info on my blog at http://antbits.blogspot.com/ For more information, you can email Eric Herman [EMAIL PROTECTED] or you can email me. Currently, you will not see any Java Stored Procedure functionality in any official MySQL release but I plan to maintain a fork with this feature. Soon, I plan to be in a position to build 'preview' binaries for Linux, FreeBSD, Windows and MacOS. Regards, Antony Curtis, (Not speaking on behalf of my current or any prior employer) On 4 Jun 2008, at 01:39, Abhayjeet Singh Grewal wrote: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD MySQL Performance Tunning suggestions???
Hi, FreeBSD 7 should offer much better performance for MySQL. The FreeBSD kernel developers have found ways to relieve some of the kernel bottlenecks which permit multithreaded applications to operate much better. Regards, Antony. On 3 Jun 2008, at 03:43, VeeJay wrote: Hi Guys I need some performance tuning suggestions/help from you. At my job, I am going to build a Web Server with 1. FreeBSD 7.0-RELEASE amd64 2. Apache 2.2.8 3. PHP 4.4.8 (or may be PHP5, what do you suggest?) Server's hardware configuration is as follow: 2 x Quad Core Xeon E5450 3.0GHz,2x6MB,1333FSB 16GB (8x2GB Dual Rank DIMMs) 667MHz FBD 6 x 450GB SAS 15k 3.5 HD Hot Plug PERC 6/i, Integrated Controller Card x6 backplane PE2950 III C5 MSS R10 Add-in PERC 5/i / 6/i 1 S TCP/IP Offload Engine 2P Broadcom TCP/IP Offload Engine functionality (TOE) Not Enabled For FreeBSD 7.0-RELEASE amd64 Which MySQL 5.0 would be used ? 1. FreeBSD 7.x (x86_64) or 2. FreeBSD 6.x (x86) I have done some googling and made these configuration files for Apache and MySQL? Apache: httpd.conf- start # = # Basic settings # = ServerType standalone ServerRoot /usr/local/apache PidFile /usr/local/apache/logs/httpd.pid ScoreBoardFile /usr/local/apache/logs/httpd.scoreboard ResourceConfig /dev/null AccessConfig /dev/null # = # Performance settings # = Timeout 300 KeepAlive On MaxKeepAliveRequests 100 KeepAliveTimeout 15 MinSpareServers 5 MaxSpareServers 10 StartServers 5 MaxClients 256 MaxRequestsPerChild 0 # = # Apache modules # = ClearModuleList AddModule mod_log_config.c AddModule mod_mime.c AddModule mod_dir.c AddModule mod_access.c AddModule mod_auth.c AddModule mod_php4.c AddModule mod_rewrite.c AddModule mod_security.c AddModule mod_setenvif.c # = # General settings # = Port 80 User apache Group apache ServerAdmin [EMAIL PROTECTED] UseCanonicalName Off ServerSignature Off HostnameLookups Off ServerTokens Prod IfModule mod_dir.c DirectoryIndex index.html /IfModule DocumentRoot /home/apache/www # = # Access control # = Directory / Options None AllowOverride None Order deny,allow Deny from all /Directory Directory /home/apache/www Order allow,deny Allow from all /Directory Directory /home/apache/www/vhosts/mydomain.com/public_html Order allow,deny Allow from all /Directory # = # MIME encoding # = IfModule mod_mime.c TypesConfig /usr/local/apache/conf/mime.types /IfModule DefaultType text/plain IfModule mod_mime.c AddEncoding x-compress Z AddEncoding x-gzip gz tgz AddType application/x-tar .tgz AddType application/x-httpd-php .html /IfModule # = # Logs # = LogLevel warn LogFormat %h %l %u %t \%r\ %s %b \%{Referer}i\ \%{User-Agent}i \ combined LogFormat %h %l %u %t \%r\ %s %b common LogFormat %{Referer}i - %U referer LogFormat %{User-agent}i agent ErrorLog /var/apache/logs/error_log CustomLog /var/apache/logs/access_log combined # = # Virtual hosts # = NameVirtualHost * VirtualHost * DocumentRoot /home/apache/www/vhosts/mydomain.com/public_html ServerName www.mydomain.com ServerAlias mydomain.com ErrorLog /var/apache/logs/vhosts/mydomain.com/error_log CustomLog /var/apache/logs/vhosts/mydomain.com/access_log combined IfModule mod_rewrite.c RewriteEngine on RewriteRule ^/([a-z]{2})/index.html$ /index.html?topicid=$1 /IfModule ErrorDocument 400 /page_error.html ErrorDocument 401 /page_error.html ErrorDocument 403 /page_error.html ErrorDocument 404 /page_error.html ErrorDocument 500 /page_error.html /VirtualHost # # Logging GET/POST requests, defending against # Cross-Site-Scripting (XSS) and SQL Injection attacks # IfModule mod_security.c AddHandler application/x-httpd-php .html #Turn the filtering engine On or Off SecAuditEngine On # Only log suspicious requests SecAuditEngine RelevantOnly SecAuditLog /var/apache/logs/audit_log SecFilterScanPOST On SecFilterEngine On SecFilterDefaultAction deny,log,status:500
Re: Debug Stored Proc
Hi, Currently there is no way but there is a WorkLog for implementing such a feature, It may be possible to encourage someone to implement such a feature request. Regards, Antony. On 6 May 2008, at 14:58, Bryan Cantwell wrote: Is there no way to step thru a stored proc in order to debug it and see what it is doing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 6.0.4 Alpha has been released ! (part 1 of 2)
Missing feature not mentioned... Falcon works on PowerPC and UltraSparc. Regards, Antony On 10 Mar, 2008, at 11:53, Joerg Bruehe wrote: Dear MySQL users, MySQL 6.0.4-alpha, a new version of the MySQL database system including the Falcon transactional storage engine (now at beta stage), has been released. The main page for MySQL 6.0 is at: http://www.mysql.com/mysql60/ If you are new to the Falcon storage engine and need more information, please read the Falcon Evaluation Guide at: http://www.mysql.com/why-mysql/white-papers/falcon-getting-started.php and the Falcon White Paper at: http://www.mysql.com/why-mysql/white-papers/storage-engines-falcon.php MySQL 6.0.4-alpha is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/6.0.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing Despite all trimming, describing all changes since the last released version of MySQL 6.0 exceeds the mailing list configuration. We had to split this message into two parts, this one (part 1) lists all changes which are labeled functionality, security, incompatible, or important. You can view the full list online at http://dev.mysql.com/doc/refman/6.0/en/news-6-0-4.html Functionality, security, incompatible, or important changes since the last release: Functionality added or changed: * Important Change: Partitioning: Security Fix: It was possible, by creating a partitioned table using the DATA DIRECTORY and INDEX DIRECTORY options to gain privileges on other tables having the same name as the partitioned table. As a result of this fix, any table-level DATA DIRECTORY or INDEX DIRECTORY options are now ignored for partitioned tables. (Bug#32091: http://bugs.mysql.com/32091, CVE-2007-5970 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-5970)) See also Bug#29325: http://bugs.mysql.com/29325, Bug#32111: http://bugs.mysql.com/32111 * Incompatible Change: The Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features: + utf16 and utf32 character sets have been added. These correspond to the UTF-16 and UTF-32 encodings of the Unicode character set, and they both support supplementary characters. + The utf8 character set from previous versions of MySQL has been renamed to utf8mb3, to reflect that its encoding uses a maximum of three bytes for multi-byte characters. (Old tables that previously used utf8 will be reported as using utf8mb3 after an in-place upgrade to MySQL 6.0, but otherwise work as before.) + The new utf8 character set in MySQL 6.0 is similar to utf8mb3, but its encoding allows up to four bytes per character to enable support for supplementary characters. + The ucs2 character set is essentially unchanged except for the inclusion of some newer BMP characters. In most respects, upgrading from MySQL 5.1 to 6.0 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. Some examples: + For the variable-length character data types (VARCHAR and the TEXT types), the maximum length in characters for utf8 columns is less in MySQL 6.0 than previously. + For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters for utf8 columns that can be indexed is less in MySQL 6.0 than previously. Consequently, if you want to upgrade tables from the old utf8 (now utf8mb3) to the current utf8, it may be necessary to change some column or index definitions. For additional details about the new Unicode character sets and potential incompatibilities, see Section 9.1.8, Unicode Support, and Section 9.1.9, Upgrading from Previous to Current Unicode Support. If you use events, a known issue is that if you upgrade from MySQL 5.1 to 6.0.4, the event scheduler will not work, even after you run mysql_upgrade. (This is an issue only for an upgrade, not for a new installation of MySQL 6.0.4.) To work around this upgrading problem, use these instructions: 1. In MySQL 5.1, before upgrading, create a dump file containing your mysql.event table: shell mysqldump -uroot -p mysql event event.sql 2. Stop the server, upgrade to MySQL 6.0, and start the server. 3. Recreate the mysql.event table using the dump file: shell mysql -uroot -p mysql event.sql 4. Run mysql_upgrade to upgrade the other system tables in the mysql database: shell mysql_upgrade -uroot -p 5. Restart the server. The event scheduler should run normally. * Incompatible Change: Because of a change in the format of the Falcon pages stored within Falcon database files, Falcon
Re: User Preferences?
I think that I'd set up a varchar column and store a tab separated list in it. Then parse it upon retrieval. Curtis Dan Buettner wrote: Waynn, I've used both schemes 1 and 2 as you describe, and in my experience 2 is the best way to go. It's easy to scale up as you add users and settings, and it's easy to make changes if the meaning of settings should change (i.e. you need to do a backend change to people's settings). #1 is harder to make those kind of back end updates on, and harder for someone troubleshooting to make sense of the data. #3 may not scale well - you would end up having to track too many tables, I think. What I'm doing in my current project is using a data model that has a method for each preference setting, and returns a sensible value by default if the user has no pref set for a given lookup key; otherwise, I return what the user has set. This means adding a method every time I add a preference setting, which on the one hand means adding code - on the other hand, chances are very high that if I am adding the ability for a user to set a preference, I'm already adding code somewhere to ensure that preference has an effect. HTH, Dan On Thu, Feb 28, 2008 at 9:50 AM, Waynn Lue [EMAIL PROTECTED] wrote: I'm looking for a good way to store user preferences. The most straightforward way is just to add a column to the Users table for each preference we're looking to store. Downside is that it requires an ALTER TABLE which gets prohibitively expensive as it gets larger, as it's fairly inflexible. I've come up with a few alternatives, and I'm wondering if people have ideas or suggestions, as this has to be a common problem. A quick Google search didn't turn up anything. 1. Store the preferences as a binary blob on the Users table. This blob could be either a blob, or an integer that I use application logic to read/write from, or I could use the SET datatype. 2. Store the preferences in normalized form, with a new table called UserPreferences that has UserId, Setting, Preference and we add a row for each setting of that user. 3. Create a separate table each time we want to add a new setting, UserId, WhateverTheNameOfThePreferenceIs. Anyone have any experience with this, or better suggestions? Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Curtis Maurand Head Honcho Xyonet Hosting Services Biddeford, ME 04005 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Migrating form 3.23.49 to 5.0.32
I'm doing the above migration as mentioned in the subject line. I figured I would use the MySQL Migration Toolkit to help it along but it won't let me connect to my 3.23.49 server. Is there any other way to migrate all my data easily. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Query
I need to get some duplicate record information from a table and I haven't found any way to do it yet. I figured there might be some type of query I could do using a for each type command. What I have is a table with names and companies. Some people have multiple entries for different companies. What I need to get is the name that has multiple entries along with the company names. Name| Company Joe BlowCompany 1 Joe BlowCompany 2 Joe G. Blow Company 1 Running the query should only return Joe Blow with Company 1 and Company 2. I can find out how many records Joe Blow has or list out each Company record grouped by Name but I only want Names with multiple entries shown. Can anyone help? I'm sure this also makes a difference but I'm stuck using MySQL 3.23. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqloptimize
I think this question has been asked, but I have not yet found an answer to the problem. I'm running MySQL 5.0.22 on Gentoo Linux AMD 64. Its blazingly fast, mostly. I'm running a package called dbmail on it. http://www.dbmail.org. All mail is stored in the database. After running a dbmail-util which deleted somewhere around 9,000 messages, I ran mysqloptimize against the database. After running mysqloptimize the innodb file (ibdata1) was larger than it was before i started. is this normal? If not, how do I change the behavior? I'm happy to forward any relevant data that you need. Thanks, Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.innodb.com
http://www.oracle.com/innodb/index.html Riemer Palstra wrote: On Thu, Nov 09, 2006 at 09:26:52AM -0800, Bill MacAllister wrote: What happened to the Innodb web pages? What comes up for be is a search page with a bunch of related links on it. I wanted to pull down a copy of ibbackup documentation and it isn't there anymore. Strange indeed, I get the search pages that Tucows/OpenSRS put up when they park a domain as soon as a customer lets their domain name expire... -- Riemer Palstra Amsterdam, The Netherlands [EMAIL PROTECTED] http://www.palstra.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.innodb.com
My guess is that its intentional. Oracle is who they are and MySQL is eating their lunch. I look for them to kill the product to try to drive MySQL out of business or make life difficult for them; hence the reason they're working on a new storage engine of their own. They tried to buy MySQL, but when they couldn't they bought up the two pieces of software that gave MySQL ACID transactions (innodb and Berkely (sp?)). They will renegotiate the contract, but make it very expensive for MySQL to license. You don't honestly think Oracle is going to be honest about this do you? They have absolutely no interest in helping MySQL survive. Sounds pretty fishy to me no matter what they're saying publicly. Curtis Bill MacAllister wrote: --On Friday, November 10, 2006 08:46:50 AM -0500 Curtis Maurand [EMAIL PROTECTED] wrote: http://www.oracle.com/innodb/index.html Please quit telling us that Oracle purchased Innodb. That is ancient news. The innodb.com web site had innodb content on it in the past, Heikki signature makes me think that it still should, and it doesn't now. That is the issue that I raised. I am guessing, given Reimer's insight, that the domain name registration has expired and someone needs to renew it. I hope they plan to do that because I found the site useful. Bill Riemer Palstra wrote: On Thu, Nov 09, 2006 at 09:26:52AM -0800, Bill MacAllister wrote: What happened to the Innodb web pages? What comes up for be is a search page with a bunch of related links on it. I wanted to pull down a copy of ibbackup documentation and it isn't there anymore. Strange indeed, I get the search pages that Tucows/OpenSRS put up when they park a domain as soon as a customer lets their domain name expire... -- Riemer PalstraAmsterdam, The Netherlands [EMAIL PROTECTED]http://www.palstra.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] +--- | Bill MacAllister, Senior Programmer | 10030 Foothills Blvd | Roseville, CA 95747 -- 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: www.innodb.com
I just checked it again and its working. Francesco Riosa wrote: Heikki Tuuri ha scritto: Bill, we are moving the DNS of innodb.com from Capnova to Oracle Corp. I can now view http://www.innodb.com through my ISP, Elisa. Does anyone still have problems accessing http://www.innodb.com? If you cannot see some web page, you can resort to Google's cache to view it. I hope that we will not get more disruption of service this weekend. Best regards, This is the answer from ibm nameservers i.e. none: ##- dig www.innodb.com @ns.almaden.ibm.com. ; DiG 9.3.2 www.innodb.com @ns.almaden.ibm.com. ; (1 server found) ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 33840 ;; flags: qr rd; QUERY: 1, ANSWER: 0, AUTHORITY: 13, ADDITIONAL: 0 ;; QUESTION SECTION: ;www.innodb.com.IN A ;; AUTHORITY SECTION: com.172552 IN NS i.gtld-servers.net. com.172552 IN NS j.gtld-servers.net. com.172552 IN NS k.gtld-servers.net. com.172552 IN NS l.gtld-servers.net. com.172552 IN NS m.gtld-servers.net. com.172552 IN NS a.gtld-servers.net. com.172552 IN NS b.gtld-servers.net. com.172552 IN NS c.gtld-servers.net. com.172552 IN NS d.gtld-servers.net. com.172552 IN NS e.gtld-servers.net. com.172552 IN NS f.gtld-servers.net. com.172552 IN NS g.gtld-servers.net. com.172552 IN NS h.gtld-servers.net. ;; Query time: 188 msec ;; SERVER: 198.4.83.35#53(198.4.83.35) ;; WHEN: Fri Nov 10 13:04:51 2006 ;; MSG SIZE rcvd: 256 ##- This is a query to the internet: ##- [EMAIL PROTECTED] dbdesigner 1 $ dig www.innodb.com ; DiG 9.3.2 www.innodb.com ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 3995 ;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0 ;; QUESTION SECTION: ;www.innodb.com.IN A ;; ANSWER SECTION: www.innodb.com. 80775 IN A 216.40.33.31 ;; Query time: 24 msec ;; SERVER: 192.168.4.1#53(192.168.4.1) ;; WHEN: Fri Nov 10 13:03:11 2006 ;; MSG SIZE rcvd: 48 [EMAIL PROTECTED] dbdesigner 0 $ dig -x 216.40.33.31 ; DiG 9.3.2 -x 216.40.33.31 ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 33913 ;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 3, ADDITIONAL: 1 ;; QUESTION SECTION: ;31.33.40.216.in-addr.arpa. IN PTR ;; ANSWER SECTION: 31.33.40.216.in-addr.arpa. 1200 IN PTR www.renewyourname.net. ;; AUTHORITY SECTION: 33.40.216.in-addr.arpa. 1200IN NS dns1.tucows.com. 33.40.216.in-addr.arpa. 1200IN NS dns2.tucows.com. 33.40.216.in-addr.arpa. 1200IN NS dns3.tucows.com. ;; ADDITIONAL SECTION: dns3.tucows.com.172051 IN A 204.50.180.59 ;; Query time: 263 msec ;; SERVER: 192.168.4.1#53(192.168.4.1) ;; WHEN: Fri Nov 10 13:03:19 2006 ;; MSG SIZE rcvd: 161 [EMAIL PROTECTED] dbdesigner 0 $ -- 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]
date_add function
I'm having some trouble setting a future date within a table. I have one column 'this_date' which is a DATE field and I'm trying to add 90 days to it and set a column named 'future_date', also a DATE field. I don't know if the problem is that I'm trying to write the value into the 'this_date' and 'future_date' fields in the same query. UPDATE this_table SET this_date = $this_date, future_date = (DATE_ADD(this_date) INTERVAL 90 DAY) Would this work? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
On Wed, 11 Oct 2006, Mark Leith wrote: Ed Curtis wrote: I'm having some trouble setting a future date within a table. I have one column 'this_date' which is a DATE field and I'm trying to add 90 days to it and set a column named 'future_date', also a DATE field. I don't know if the problem is that I'm trying to write the value into the 'this_date' and 'future_date' fields in the same query. UPDATE this_table SET this_date = $this_date, future_date = (DATE_ADD(this_date) INTERVAL 90 DAY) Would this work? Thanks, Ed UPDATE this_table SET this_date = NOW(), future_date = NOW() + INTERVAL 90 DAY; This is probably along the lines of what you want.. Actually I'm setting the DATE via drop down menus using PHP and creating the date by hand via variables. NOW() won't work in this instance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
On Wed, 11 Oct 2006, Rolando Edwards wrote: Please check your syntax. It should look like this: UPDATE this_table SET this_date = $this_date, future_date = DATE_ADD($this_date,INTERVAL 90 DAY); Don't forget your WHERE clause or else you populate every row. Tried it, this is what I get back. You have an error in your SQL syntax near 'future_date = date_add(2008-10-20, INTERVAL 90 DAY) WHERE id =' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
On Wed, 11 Oct 2006, Rolando Edwards wrote: Oops, also the $this_date UPDATE this_table SET this_date = '$this_date', future_date = DATE_ADD('$this_date',INTERVAL 90 DAY); Got it going guys, thanks again Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
I'm trying to do a keyword search within a phrase saved in a table. Here's the query: SELECT * from closedtickets WHERE keyphrase LIKE '%$keyword1%' OR keyphrase LIKE '%$keyword2%' OR keyphrase LIKE '%$keyword3%' The problem I'm having is that the query is returning every record in the table. I only want it to return the records where the keywords (any combination) are contained within 'keyphrase' Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
You were right. Its a global privilege not a table one. I granted it at a global level. it can't be granted at the databae level Dan Buettner wrote: Curtis, you might need to make sure 'admin'@'localhost' has 'FILE' privileges in the proper database, for load data infile. Note that 'admin'@'%' is not the same as 'admin'@'localhost' See http://dev.mysql.com/doc/refman/5.0/en/grant.html Also here is a comment from the same page: QUOTE Posted by Cristian Gafton on July 17 2006 6:28pm [Delete] [Edit] Please note that the current MySQL priviledge model does not support creating a read-only user for an application that needs to work with temporary tables. In other words, the 'SELECT,CREATE TEMPORARY TABLES' privileges are not sufficient to let an application work with temporary tables against a read-only set of tables. MySQL also requires INSERT, DELETE and UPDATE grants to allow temporary tables to be used. Since the temporary tables are not 'defined' at the time of the grant, one would have no choice but to grant INSERT,DELETE,UPDATE on db.*, which negates the possibility of a read-only user. It would be nice if MySQL would have a more generic 'TEMPORARY TABLES' permission that would allow one to create, insert, delete from and drop temporary tables without having to give up insert/update/delete privileges on the real tables /QUOTE Dan On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote: here is what I get. I'm logged in as the user admin (really a regular user.) mysql create temporary table customer_tmp as select * from customer limit 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table customer_tmp; ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) Dan Buettner wrote: Or possibly that the mysql user on the box does not have access to the data file in question. Can you post the error messages you get? Dan On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote: This may sound like a stupid question, but I have to ask. I've been running a script that goes like the following. use ecommerce; create temporary table customer_tmp as select * from customer limit 0; load data infile '/home/bluetarp/ezauth/customers.txt' into table \customer_tmp; at which point the script fails for permssion reasons. It seems that this user can create a temporary table, but not load data into it? What did I miss on permissions to allow this to work? Thanks Curtis -- 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]
temporary tables
This may sound like a stupid question, but I have to ask. I've been running a script that goes like the following. use ecommerce; create temporary table customer_tmp as select * from customer limit 0; load data infile '/home/bluetarp/ezauth/customers.txt' into table \customer_tmp; at which point the script fails for permssion reasons. It seems that this user can create a temporary table, but not load data into it? What did I miss on permissions to allow this to work? Thanks Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
here is what I get. I'm logged in as the user admin (really a regular user.) mysql create temporary table customer_tmp as select * from customer limit 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table customer_tmp; ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) Dan Buettner wrote: Or possibly that the mysql user on the box does not have access to the data file in question. Can you post the error messages you get? Dan On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote: This may sound like a stupid question, but I have to ask. I've been running a script that goes like the following. use ecommerce; create temporary table customer_tmp as select * from customer limit 0; load data infile '/home/bluetarp/ezauth/customers.txt' into table \customer_tmp; at which point the script fails for permssion reasons. It seems that this user can create a temporary table, but not load data into it? What did I miss on permissions to allow this to work? Thanks Curtis -- 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: temporary tables
When I created the user, I specified: grant all on ecommerce.* to admin@'localhost' identified by 'password'; Doesn't that cover the file privilege? I noticed the navicat commercial product doesn't list that privilege specifically. the mysql-administrator just locks up when I go to manage users. i've been working it from the command line. Curtis Dan Buettner wrote: Curtis, you might need to make sure 'admin'@'localhost' has 'FILE' privileges in the proper database, for load data infile. Note that 'admin'@'%' is not the same as 'admin'@'localhost' See http://dev.mysql.com/doc/refman/5.0/en/grant.html Also here is a comment from the same page: QUOTE Posted by Cristian Gafton on July 17 2006 6:28pm [Delete] [Edit] Please note that the current MySQL priviledge model does not support creating a read-only user for an application that needs to work with temporary tables. In other words, the 'SELECT,CREATE TEMPORARY TABLES' privileges are not sufficient to let an application work with temporary tables against a read-only set of tables. MySQL also requires INSERT, DELETE and UPDATE grants to allow temporary tables to be used. Since the temporary tables are not 'defined' at the time of the grant, one would have no choice but to grant INSERT,DELETE,UPDATE on db.*, which negates the possibility of a read-only user. It would be nice if MySQL would have a more generic 'TEMPORARY TABLES' permission that would allow one to create, insert, delete from and drop temporary tables without having to give up insert/update/delete privileges on the real tables /QUOTE Dan On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote: here is what I get. I'm logged in as the user admin (really a regular user.) mysql create temporary table customer_tmp as select * from customer limit 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table customer_tmp; ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) Dan Buettner wrote: Or possibly that the mysql user on the box does not have access to the data file in question. Can you post the error messages you get? Dan On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote: This may sound like a stupid question, but I have to ask. I've been running a script that goes like the following. use ecommerce; create temporary table customer_tmp as select * from customer limit 0; load data infile '/home/bluetarp/ezauth/customers.txt' into table \customer_tmp; at which point the script fails for permssion reasons. It seems that this user can create a temporary table, but not load data into it? What did I miss on permissions to allow this to work? Thanks Curtis -- 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]
Review O'Reilly MySQL backup chapter?
I'm the author of the O'Reilly book Backup Recovery, due to be released in Q3 of this year. Among other things, it has a chapter on backing up MySQL. I'm looking for a few MySQL-knowledgeable folks to provide a technical review of this chapter. Obviously I'd want you to be experienced in backing up and recovering MySQL databases. Please reply via email. Thanks. (I've got a tight timeline, so please only reply if you think you can review a 10-page or so chapter very quickly.)
Replacing A Value
I have a column in a table I need to replace a value of certain records in. The current value is /realtors/Value/. I need to change them to /realtors/This_Value/. Is there an easy way to do this. There are way too many records to do it one record at a time. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to take dump of a query instead of table / database
select criteria into outfile name of output file The path for the output file must be writeable by the user underwhich mysql is running. -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com On Fri, 24 Mar 2006, Pure Web Solution wrote: you can manipulate mysqldump using the where clause (check the man) You might find the following usefull, but would not be so good for restoring. from the command line mysql --database=db_name --execute=select * from users -u username -ppassword output.file Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services abhishek jain [EMAIL PROTECTED] wrote: Dear Friends, I need to take the backup of a query, is it possible. If yes how. -- Regards Abhishek jain www.smsengine.co.uk Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REITF?
Does anyone on the list know what REITF stands for? I'm guessing it's some type of data format for real estate information but I can't find any information on it anywhere. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP and mysql
mysqladmin -u root password new password Curtis sheeri kritzer wrote: Hi Alaister, Your root password is not actually set. If you do mysql -u root -ppassword and it fails, it means that the password is not password if you do mysql -u root password the mysql client will parse password as the database you're trying to use -- that's just the syntax of the mysql client. Which means that your password is not very secure, because it's the same as your database name. You should get an error like this: ERROR 1049 (42000): Unknown database 'password' What you should do is this: # mysql -u root set password=password(newpassword); where newpassword is your new password. hope this helps. -Sheeri On 10/27/05, Alastair Roy [EMAIL PROTECTED] wrote: Thanks for your reply I think this is the issue I don't think root is allowed to login from local host I created another user called web user and changed the script, that works fine, next question is how do I set the permissions for root in mysql to allow root to login, if I use #mysql -u root password I go straight in If I use #mysql -u root -p password I get access denied for [EMAIL PROTECTED] -Original Message- Visit our websites: http://www.dailysnack.com +IBw-bite size news and gossip+IB0 http://www.express.co.uk The Worlds Greatest Newspaper http://www.dailystar.co.uk Simply The Best 7 Days A Week http://www.happymagazine.co.uk The One Stop Shopping Magazine http://www.ok.co.uk First For Celebrity News http://www.northernandshell.co.uk The Mark Of Excellence http://www.expresspictures.com Express Newspapers and OK Magazine online picture archive Also visit: The NMA: Opening Up Newspapers http://www.nmauk.co.uk ###2004### Any views or opinions are solely those of the author and do not necessarily represent those of Express Newspapers The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material.If you are not the intended recipient of this message please do not read ,copy, use or disclose this communication and notify the sender immediately. It should be noted that any review, retransmission, dissemination or other use of, or taking action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. E-mail communications may be monitored. ##EXN2000## From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: 25 October 2005 18:06 To: Alastair Roy Cc: mysql@lists.mysql.com Subject: Re: PHP and mysql Yes. Whenever you have a problem, go back to basics. Can you login as root on the commandline? What password do you use there? Once you're there, check root's permissions, but I'd be willing to bet your password is incorrect, or root is only allowed to logon from localhost and not the machine the webserver is on. -Sheeri On 10/25/05, Alastair Roy [EMAIL PROTECTED] wrote: Greetings everyone, I am having a problem with PHP and mysql I have copied a script exactly off a website to open the database and insert a new user I try to access the mysql database using the root user, and enter information into the user table, this is the PHP I am using ? include 'library/config.php'; include 'library/opendb.php'; $query = INSERT INTO user (host, user, password, select_priv, insert_priv, update_ priv) . VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y', 'Y'); mysql_query($query) or die('Error, insert query failed'); $query = FLUSH PRIVILEGES; mysql_query($query) or die('Error, insert query failed'); include 'library/closedb.php'; ? When I try this I get the access for user [EMAIL PROTECTED] host denied, it is driving me nuts no matter what I try I get the same thing other scripts I have tried do the same thing, have tried messing around with the config.php and opendb.php included scripts but nothing works if I run php -f opendb.php I get no errors which I think means it is working, then again I don't know. Anyone have any ideas ?? Thanks in advance Visit our websites: http://www.dailysnack.com +IBw-bite size news and gossip+IB0 http://www.express.co.uk The Worlds Greatest Newspaper http://www.dailystar.co.uk Simply The Best 7 Days A Week http://www.happymagazine.co.uk The One Stop Shopping Magazine http://www.ok.co.uk First For Celebrity News http://www.northernandshell.co.uk The Mark Of Excellence http://www.expresspictures.com Express Newspapers and OK Magazine online picture archive Also visit: The NMA: Opening Up Newspapers http://www.nmauk.co.uk ###2004### Any views or opinions
Re: problem with mysql.sock
I could be wrong but this may have something to do with ownership and permissions of the socket file. I recently upgraded my MySQL version and had basically the same problem. I can't remember though if I had to change the ownership to root.root or mysql.mysql. HTH, Ed On Mon, 26 Sep 2005, Sandhya Reddy wrote: Hello, I have recently installed FC3 and now I have PHP(4.4.9) and MYSQL(3.23) which got installed along with FC3. I'm able to connect to MYSQL from command prompt. But the problem fires when I do the same from a PHP script. The error I get is Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13). And I'm sure that MYSQL server is running and able tp connect from command prompt. I have been searching on the net for the same from the last 3 days but ended with nothing. I have changed php.ini to include the mysql_default_socket=/var/lib/mysql/mysql.sock. even then it doen't work. Earlier I had FC1 and everthing was working fine! Could please suggest me a solution to tackle this problem. Thanks in Advance Sandhya __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux vs. Windows?
Please note that this answer is not meant to support one OS or another, but the information on that page is not useful. The information is seriously out of date. The comparison is on Windows NT not Server 2K3 or XP. The hardware is Pentium Pro 400 or AMD K6II-350 with old versions of software. Server 2K3 has been much more stable than Windows NT and its security is better, but still not great. -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com go to http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/information/ benchmark-results/result-mysql-platform-relative.html anyway the difference isn't only in performance what did you think about crashless :-) Good job Paolo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding row by value of a certain length
I've been cruising the docs for a while now and can't find what I'm looking for. I know it has soemthing to do with value or LEN or something easy like that but I just can't find the right command structure. I need to list the rows in a table where the length of a field, lets say field1 is a minimum of 60 characters or larger. The field type is varchar. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding row by value of a certain length
On Wed, 22 Jun 2005 [EMAIL PROTECTED] wrote: You were SO close!!! SELECT field list FROM table references WHERE CHAR_LENGTH(varcharfield) = 60; Thanks so much. I knew I was close but couldn't remember the exact command. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
While you're at it, take a look at Gentoo Linux (http://www.gentoo.org). I've been having very good luck with it on everything from a Duron 1GHz to Opterons. Very responsive. It compiled a kernel on an opteron in about 5 minutes. Curtis Atle Veka wrote: Excellent, I'll be waiting to see performance numbers, specifically for FreeBSD vs. Linux. Save for a few odd machines, we're pretty much pure FreeBSD and the last releases in the 4 branch are really impressive as far as speed and stability. That being said, the Opteron would have to offer a pretty decent performance gain for us to consider switching. At the moment, our software layout is such that we have not had a need to take advantage of more than 2G memory. Feel free to woo me with your performance results. ;) In the not so distant future I will have to get an Opteron box so I can see for myself.. Atle - Flying Crocodile Inc, Unix Systems Administrator On Mon, 9 May 2005, Kevin Burton wrote: Great! I created a wiki node for this issue. http://hashmysql.org/index.php?title=Opteron_HOWTO
Re: missing file ( msyql.sock)
the socket file is created in the spot specified in /etc/mysql/my.cnf. In my case its: socket = /var/run/mysqld/mysqld.sock as always ymmv. Curtis ganesan malairaja wrote: is it possible a firewall is denying mysql to create the mysql.sock file if not where i can get this file i cannot find it in my entire harddrive i am stuck at for days now a clear guideline will help i tried reading at the mysql.com but no help .. i am new to linux.. anyone who had this experience and solve it please reply i am running on suse 9.3 and using mysql-4.1.11 ( source file ) htmlDIV DIVFONT color=#cc face=Lucida Handwriting, CursiveEMSTRONGIMG height=16 src=http://graphics.hotmail.com/emarrow_right.gif; width=16Ganesan_MalairajaIMG height=16 src=http://graphics.hotmail.com/emarrow_left.gif; width=16/STRONG/EM/FONT/DIV/DIV/html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use MySQL with Microsoft Office
Using ODBC, however, you can link Access tables to MySQL tables and use Access as the front end to MySQL. It works very nicely. Curtis Martijn Tonies said: Alternatively you could use OpenOffice.org (http://www.openoffice.org/) which has built in MySQL support. Alternatively, you could switch to MS Access, which is very well supported by Microsoft Office. :-) mmhhh MySQL Vs. Access MS... just a little be different :-))) Yes, very :-) So is MS Office and OpenOffice :-) -- Martijn -- 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]
SELECT ERROR
I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ERROR
Thanks, as soon as I seen it I slapped myself really hard :) On Wed, 23 Feb 2005, mel list_php wrote: If you want to compare the 2 tables you have to join them: select * from listings, fake where listings.id=fake.id; If you do your query SELECT * from listings where listings.id = fake.id; it simply doesn't know where to get fake.id From: Ed Curtis [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: SELECT ERROR Date: Wed, 23 Feb 2005 08:02:21 -0500 (EST) I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- 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]
SELECT UPDATE question
I know this is possible but I'm not real sure of the command to use. I have 2 tables that are pretty much identical except for one column. What I want to do is moved data from one table column to the other table column based on a matching id number that is also a column in both tables called id. UPDATE table2 SET active = '1' WHERE table2.id = table1.id; is this the correct syntax? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ignoring username parameter when connecting via ssh tunnel
I am trying to connect to my mysql server through an SSH tunnel. On the server, I have a local instance of mysql running, but one of the hosted domains needs to access another remote mysql server. For security, I want to connect to the remote server via an ssh tunnel. I am creating the tunnel using the following command: ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f This creates a tunnel so I can connect to port 3307 on the local server, and end up talking to the remote server on 3306. Telneting to 127.0.0.1:3307 gives me the mysql handshake. Now the fun begins when I try to use the connection. If I do: mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the remote server using the username leg_shop. This works fine with no problems except the fact the traffic is not encrypted as it isnt using the ssh tunnel. If I do: mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the remote server through the ssh tunnel, but for some insane reason, it ignores the -u leg_shop. I can enter any username of my choice (e.g. a user which is DEFINATELY not valid on the remote server), and yet it still connects. Am I missing something here ? On the server where I am trying to connect FROM, it has mysql client mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686), and on the remote server I am trying to connect to via the tunnel, it is running mysql Ver 12.22 Distrib 4.0.16, for pc-linux (i686) Any ideas or suggestions welcome. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignoring username parameter when connecting via ssh tunnel
I am trying to connect to my mysql server through an SSH tunnel. On the server, I have a local instance of mysql running, but one of the hosted domains needs to access another remote mysql server. For security, I want to connect to the remote server via an ssh tunnel. I am creating the tunnel using the following command: ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f This creates a tunnel so I can connect to port 3307 on the local server, and end up talking to the remote server on 3306. Telneting to 127.0.0.1:3307 gives me the mysql handshake. Now the fun begins when I try to use the connection. If I do: mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the remote server using the username leg_shop. This works fine with no problems except the fact the traffic is not encrypted as it isnt using the ssh tunnel. If I do: mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the remote server through the ssh tunnel, but for some insane reason, it ignores the -u leg_shop. I can enter any username of my choice (e.g. a user which is DEFINATELY not valid on the remote server), and yet it still connects. Am I missing something here ? On the server where I am trying to connect FROM, it has mysql client mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686), and on the remote server I am trying to connect to via the tunnel, it is running mysql Ver 12.22 Distrib 4.0.16, for pc-linux (i686) Any ideas or suggestions welcome. Richard I believe that your mysql server gets the local ip address (127.0.0.1) as connection source and not the one from your remote host which you are connecting from. You're granted access from the anonymous user which is default entered in the mysql.user table. I would suggest to 1.) delete this users with use mysql; delete from user where user=''; flush privileges; 2.) Try to reconnect via ssh tunnel. This should be denied now. 3.) alter the host-entry from the leg_shop user to 'localhost' and try connecting again. Regards, bh Absolutely right. By removing the anonymous entry, it now works as I'd expected :) Thanks all Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Generic graphing tool?
There are several PHP scripts that develop graphs. You'll have do some work to feed the data to them, but I found them at: http://www.hotscripts.com/PHP/Scripts_and Programs/Graphs_and_Charts/ Curtis Jim wrote: Jason Martin wrote: Does anyone know of a web-based tool that will let you graph arbitrary data out of of MySQL? I'm thinking of something that lets you define a select statement, some graph options and produce a graph. I'm afraid I'm not aware of anything as simple to use as you describe, but you could build one without TOO MUCH pain using CharDirector (a free version is available from http://www.advsofteng.com/) and your scripting language of choice. I prefer PHP or Perl, myself. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NOT LIKE
I've been searching the docs and can't find examples how to do what I need to do. I need to exclude some records from my SELECT statement results but it appears I can't use (=, != or LIKE) for it. What I have is a result set having a column name 'path'. I need to exclude any record reulting in '/realtors/Kokomo/%' You can't use wildcards when using = or != as far as I can see. How do I exclude any records where the path column is '/realtors/Kokomo/%'? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need advice on windows front end application
Use the ODBC connector and write it in VB. Curtis Chris Mason wrote: I have a mysql database runing on an internal linux server and I need to connect to it with an appliication running on a windows workstation. The application must start another application with command line informaiton from the database so I cant use a web based application, as web browsers cannot start an application on the local machine. I'm looking for recommendations on the easiest way to implement this project. I would prefer a php like scripting language, I certainly wont be able to do it in C or similar. Chris Mason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and PHP
If you follow the instructions properly, you can get the ISAPI version of PHP to run and it it runs pretty well once installed. Curtis Don Stefani wrote: GH wrote: I am using IIS :( On Windows... Which do i install first? PHP or MySQL? Apache. :) If your just working on a local dev box, it may not be that big of a deal. PHP using IIS is a bit easier to install than Apache since PHP5 does not have an installer that auto-configs Apache, you will have to use the zipped package and configure your http.conf yourself. Windows doesn't always like to play nice. Although if I remember correctly it installs it as CGI, and there are big security warnings about it. Again, a local dev box may not be a huge risk. Anyway, Have Fun! - dstefani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
OK, now I really feel stupid. It helps to change the ownership of the files to mysql:mysqlduh. Curtis Curtis Maurand wrote: I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data
Hi, I have done an incredibly stupid thing. I have a server that suffered a bit of a failure. It wasn't a hard failure, but it was enough of one to have to rebuild the server from scratch. I was and am running mysql 4.0.22 on Gentoo with a 2.6 kernel. It rocks. However, The dumb thing that I did was fail to perform a mysqldump -a on the machine before I finished killing it. I rebuilt mysql from scratch. I then copied over the my.cnf files and I copied over the old /var/lib/mysql directory in tact. mysql wouldn't start at that point. I then deleted all the files in /var/lib/mysql and ran mysql_install_db. mysql starts automatically. If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which PHP for MySQL 4.1
Jay Blanchard wrote: [snip] I would like to migrate my MySQL servers from 4.0 to 4.1. As I use PHP as well as Java with these servers I wonder what PHP 4 version would be compatible with MySQL 4.1. Has anyone used MySQL 4.1 with PHP yet? [/snip] PHP 4 is compatible with MySQL 4.1. My caution to you would be using Apache 2 as it has some quirks that haven't been worked out yet. I've been using Apache 2 with PHP 4 for quite some time. Its been working fine for me and my customers. Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slashes in update statement
I'm trying to get a slash in a variable into my database and am having some trouble. If the variable = 1 1/2 it echoes to the screen correctly but it seems to strip the 1/2 off the variable when updating the value to the database. I'm using php and a form select list to get this value from a previous page. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and PHP
You have to rebuild PHP against the new MySQL libraries. Curtis Greg Donald said: On Wed, 15 Sep 2004 08:04:08 -0700, nestor(earth) [EMAIL PROTECTED] wrote: This more of a php mysql question. I have installed PHP ( 5.01) with Apache(1.31) and it runs. I have install Mysql (the latest as of last night) and it runs. My problem is that PHP does not see Mysql. Now I have done this installation 4 or 5 times but th elast time was over a year ago. Any ideas? Perhaps this will help: http://www.wampserver.com/en/faq.php#q5 -- Greg Donald http://gdconsultants.com/ http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting data from 2 tables.
God, I feel real stupid this morning and know I should know this. I have 2 tables in the same database and I'm trying to select distinct data from a row with the same name in each table. SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC I'm missing something I'm sure because it doesn't work. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
Feel stupid again ;-) Where's your JOIN? With regards, Martijn Tonies Thanks, that makes me feel better :) Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
On Mon, 9 Aug 2004 [EMAIL PROTECTED] wrote: He does have a join. He has an *implied* INNER JOIN (http://dev.mysql.com/doc/mysql/en/JOIN.html): FROM pages, pdflog What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Like I said I knew I was missing something. I just couldn't for the life of me remember what it was. All I needed was a nudge. Thanks all, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Database Administrator Unimin Corporation - Spruce Pine OK now I really really feel stupid. Now that I've been given the correct way this particular person wants this done. What I need to produce is a distinct list from pages.magazine and pdflog.magazine without a condition. Just a list of all data in these table columns without duplicates. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
I think a quick way to write this query would be (http://dev.mysql.com/doc/mysql/en/UNION.html): ( SELECT magazine FROM pages ) UNION DISTINCT ( SELECT magazine FROM pdflog ) ORDER BY magazine; Thanks for all the help on this one. I just also realized that the server I'm working with has version 3.23.x of MySQL. My only other option now is to create a temp table with distinct data from both tables, is it not? Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect mysql.sock
I just installed mysql using Xampp, an installer for Apache, Mysql, PHP installation. Everything else works fine, but mysql will not start with the Apache startup. When I try to do a manual startup of my sql I get the following error message ERROR 2002: Can't connect to local MySQL server through socket '/opt/lampp/var/mysql/mysql.sock' (2) Any suggestions ? Curtis Seyfried [EMAIL PROTECTED] Bronx, NYC, NY. USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect mysql.sock
Yes, Linux RH9 2.4.08 When I run the /opt/lampp/lampp start command, Apache starts, SSL, starts and another service, mysql seems to start. But when I try to connect to mysql with admin or cc or other tools, so I can setup databases and tables, it tells me mysql is not running. Then I issue /opt/lampp/bin/mysql start to get mysql to start and this is when I get the can not connect to mysql.sock Michael, OK, what you said makes sense, but I thought I WAS starting the msql SERVER. I did not realize I was inadvertently starting the client. I have never had to manually start mysql before. In win2k pro it is installed as a Service, which I can set to manual and start when I want. So I was starting Apache, then mysql as services. So now I ran /opt/lampp/bin/mysqld Got NO file or directory. Went into bin direwctory and found there is NO mysqld, but there is a mysql.server, so I ran /opt/lampp/bin/mysql.server and then I get this error message. .Starting mysqld daemon with databases from /opt/lampp/var/mysql 040614 14:57:40 mysqld ended That was after I tried it and got the following because I had created a directory called /mysql.sock thinking before it needed this directory. then I go this error, so I REMOVED the directory. .Starting mysqld daemon with databases from /opt/lampp/var/mysql rm: cannot remove `/opt/lampp/var/mysql/mysql.sock': Is a directory 040614 14:56:50 mysqld ended So, HOW does Xampp Normally start mysql, when I issue the /opt/lampp/lampp start command ? Since it isn't HOW do I Start mysql so I can finally get it to work. My O/S is Linux RH9 2.4.08 complete custom install with ALL packages and features. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User ID Password
I think that I'd look at postfix and dbmail. Postfix and dbmail both allow userdata to be stored in MySQL databases. dbmail will also put the message store in a mysql database. Very nice, very fast. RH9 has hit eol. Gentoo rocks. Curtis -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com On Sat, 5 Jun 2004, Kirti S. Bajwa wrote: Hello: I am posting this message on freeRADIUS, vpopmail mysql lists. This may get few people upset but please read I am trying to install (on RH9), qmail, vpopmail, mysql, Courier-IMAP, squirrelmail, etc., with backend data on MySQL. On another computer I have installed RH9 freeRADIUS server. vpopmail is used to add UID PW and the data is stored in vpopmail DB in MySQL. Now freeRADIUS also uses UID PW to authenticate and has its own data structure. I like to know if there is a way so that user data is stored in one table in MySQL so vpopmail and freeRADIUS can access the same information?? Thanks in advance. Kirti -- 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: fastest filesystem for MySQL
Reiser is good for lots of small files. ext3 would is better for large ones. At least that's what I get from the benchmark data that I've seen posted in various places. Curtis -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com On Wed, 12 May 2004, Roy Butler wrote: Jacob, I'd go with Reiser on SuSE. Like Sasha mentioned though, the filesystem component may have little overall effect, depending on your set-up. I'd stay away from XFS when working with databases, as its performance gains are achieved via extended write delays while the queue sits in main memory: not the sort of thing you want after a crash... If you have the time/interest, why not try some benchmarks of your own? Roy -- Date: Wed, 12 May 2004 00:22:21 +0200 To: [EMAIL PROTECTED] From: JFL [EMAIL PROTECTED] Subject: fastest filesystem for MySQL Message-ID: [EMAIL PROTECTED] I've heard and read that the Reiser filesystem should be better for MySQL than Ext3. Is this still true? We will be running MySQL on either Red Hat ES 3, Suse or Debian. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying a database
How would you go about copying a database? I need to make a copy with all the tables and names the same. I just need to name the database something different. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security
Usernames, passwords, and then perform the queries select ... where customerid = the variable name you feed Its all handled by your app. Curtis On Wed, 10 Mar 2004, Mulugeta Maru wrote: Hi Mike, I am sorry for the confusion I might have caused. May be it would help to give a clear example. Table - Customers (CustomerID, CustomerName, Address, etc) Table - Transaction(TransactionID,CustomerID,Date,Amount) Note: CustomerID in Customer Table is a Primary Key. TransactionID is a Primary Key and CustomerID is a Foreign Key in Transaction Table). Question: How would I be able to give my customers access to the database so that they can update the customer table (for example address change) and add transactions to the transaction table. What I do not want to happen is that customer A is able to modify customer B's record. In short how would you restrict customer a to see transactions that pertain to him/her. Many thanks. - Original Message - From: Mike Johnson [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 4:55 PM Subject: RE: Security From: Maru, Mulugeta [mailto:[EMAIL PROTECTED] When I go online to access my bank account I only see transactions pertain to my account only. I think when ever I make a transaction the database records my account number in the transaction table. When I log-in using my account number and password the system checks whether it is correct or not and run another query to get all transaction that match my account number. Do I make sense? (sent offlist by mistake, please excuse the dupe) The point being made is that you're looking at your bank account information in a client that is set to read records only pertaining to your account. The native mysql client is not such a program and was never intended to be. While you can customize access for users to certain databases or certain tables within those databases, it's simply not built as a multi-user transactional client for limiting access to data in commonly-used tables. It begs the question why you're giving your clients access to the native mysql client itself rather than developing an application to do this, in which you could quite easily limit such access. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security
I guess the easiest way to do this would be to index all transactions with a user id number or something identifying the user. When they log in to the site have the scripts only access the records for that person using a WHERE clause in the queries. You would have to be able to keep track of their user id for them via cookies or sessions or something though. Ed Curtis On Tue, 9 Mar 2004, Mulugeta Maru wrote: Thank you for the kind response. May be I did not clearly ask the question. The user table in mysql database is used to set-up a user and password. Once I set-up my tables (customer, customer orders, customer order details, etc) in say abc database what will I have to do to make sure when customer A logs in to the database can only see his/her account, orders, order details without getting access to other customer accounts. I hope my question is clear. Maru - Original Message - From: Paul Rigor [EMAIL PROTECTED] To: Mulugeta Maru [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:46 PM Subject: Re: Security Heya, Those are the default databases that comes with the setup. the mysql database holds info on mysql accounts. the test is an empty database. You should create a new database CREATE DATABASE customers then use customers... after that... you can setup the tables you mentioned. Goodluck! Paul At 06:34 PM 3/9/2004, Mulugeta Maru wrote: I have used access in the past and now I have started using MySQL. I have customer table, customer order table, customer order detail table. How would I make sure that when a particular customer log-in he/she sees only the account that is set-up for them. What confused me is that MySQL has a database called mysql and a table in this database called users that is used to set a user name and password for each user. I could not figure out how a user in my case a customer that has access to a customer table could be restricted to see his/her transaction only. Any insight is very much appreciated. _ Paul Rigor [EMAIL PROTECTED] Go Bruins! -- 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: Doing a LIKE search on a ENCODE type
WHERE password = password('1234'); Curtis On Thu, 4 Mar 2004, Victoria Reznichenko wrote: Scott Haneda [EMAIL PROTECTED] wrote: Here is what I am doing now... SELECT id, first_name, last_name, password FROM account WHERE password = ENCODE(1234, 'foobar') ORDER BY last_name LIMIT 0,10 I need to do a where password LIKE '%1234%' instead, I can not seem to get this to work, is there some trick? There is DECODE() function in MySQL. You can perform pattern-matching search for the decrypted password: SELECT .. FROM account WHERE DECODE(password, 'foobar') LIKE '%1234%' ORDER BY last_name LIMIT 0,10; -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scripting
There's a whole list of all the functions at http://www.php.net Curtis On Thu, 4 Mar 2004, James Marcinek wrote: Rhino, I know this is an off topic; however I see that you have DB2 experience. Most of the information I have a question you might be able to answer. Are you using PHP with MySQL? If so, have you tried to use PHP with DB2? I'd like to know what functions you call to connect or anything that could help? I'm new to the MySQL arena as well. Thanks, James Todd, I'm also pretty new to MySQL and Linux but I have years of DB2 experience on various platforms. I've bash scripts very useful and cron them when I have something that I want to automate, such as a daily backup of my databases. Here are some examples of bash scripts that I use with MySQL. This example, which is in file RI.sql, creates two related tables in the MySQL 'tmp' database, populates them, and displays the results. NOTE: The 'Another Mistake' row will not be successfully inserted into the 'emp' table because it has an invalid foreign key; there is no row for dept 'X99' in the 'dept' table. --- use tmp; drop table if exists dept; create table dept( deptno char(3) not null, deptname varchar(36) not null, mgrno char(6), primary key(deptno) ) Type=InnoDB; drop table if exists emp; create table emp( empno char(6) not null, firstnme char(12) not null, midinit char(1), lastname char(15) not null, workdept char(3) not null, salary dec(9,2) not null, primary key(empno), index(workdept), foreign key(workdept) references dept(deptno) on delete restrict ) Type=InnoDB; insert into dept values ('A00', 'Administration', '10'), ('D11', 'Manufacturing', '20'), ('E21', 'Education', '30'); insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00); insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00); insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99', 15000.00); insert into emp values ('40', 'John', ' ', 'Kerry', 'E21', 35000.00); select * from dept; select * from emp; --- You can run the preceding script from the mysql prompt by preceding its name with '\.'. For example: mysql \. RI.sql This script, called backup2.bash, is what I use to back up my databases each night. It includes a comment showing the crontab entry I use to run it. NOTE: We are using a Perl package called 'sendEmai'l instead of the traditional 'sendmail' program. --- #!/bin/bash #This script makes a separate database-level backup of each of the current MySQL databases and #deletes backups older than a certain number of days. #This script is normally invoked via a cron job so that it runs once per day in the middle of the night. #The crontab entry looks like this: #0 3 * * * sh /home/rhino/MySQL/backup2.bash /home/rhino/MySQL/backup2.out 21; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t [EMAIL PROTECTED] -u Daily Backup Report USERID=foo; #The userid to use for creating the backup PASSWORD=foopass; #The password to use for creating the backup BACKUP_TIMESTAMP=`/bin/date +%Y%m%d-%H%M%S`; #The timestamp (MMDD-HHMMSS) of the backup BACKUP_PATH=/home/rhino/MySQL/backup; #The directory into which the backup will be written NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to keep echo ** REPORT BEGINS **; echo echo Program Name: $0 report_date=`/bin/date` echo Report Date: $report_date; echo #Display the non-secret values used in this run. echo Backup Values:; echo Backup timestamp is $BACKUP_TIMESTAMP; echo Backup path is $BACKUP_PATH; echo Number of daily backups to keep = $NUMBER_OF_DAILY_BACKUPS_TO_KEEP; #For each database currently in MySQL, take a database-level backup, then list any backups older than a certain number of day for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD` do echo echo Backing up database $ONE_DBNAME; /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} ${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TI echo Deleting these old backups for this database... /usr/bin/find ${BACKUP_PATH} -mtime +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old backups (i /usr/bin/find ${BACKUP_PATH} -mtime +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';'; #delete old done echo echo ** REPORT ENDS **; --- If you want to take a backup manually with this script (and omit the email being sent to you), you can do this from the mysql prompt via: mysql \. /home/rhino/MySQL/backup2.bash /home/rhino/MySQL/backup2.out 21 I think the biggest obstacle you're going to face in writing bash scripts is the lack of good tutorials on it. There are umpteen bash
Re: PgSQL vs MySQL
:-) someflag enum('TRUE','FALSE'); Not quite boolean, but it works. Curtis On Wed, 3 Mar 2004, Mark Warner wrote: The thing which bothers me most about MySQL is the lack of a proper boolean. I don't like having to abstract a tinyint(1) into true or false. As much of my work involves building applications with Yes or No questions, I think I am switching to PostgreSQL. [EMAIL PROTECTED] wrote: What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
I know, I get it, I was trying for humor. Curtis On Wed, 3 Mar 2004, Michael Stassen wrote: Curtis Maurand wrote: :-) someflag enum('TRUE','FALSE'); Not quite boolean, but it works. Curtis On Wed, 3 Mar 2004, Mark Warner wrote: The thing which bothers me most about MySQL is the lack of a proper boolean. I don't like having to abstract a tinyint(1) into true or false. As much of my work involves building applications with Yes or No questions, I think I am switching to PostgreSQL. SNIP To my mind, a boolean is something that can be used in a boolean context. That is, if flag is a boolean, the following should work as expected: SELECT * FROM mytable WHERE flag; # rows with flag = TRUE SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE If you have to compare the column's value to something, it isn't really a boolean value. MySQL, like most programming languages, treats 0 as FALSE and any other number as TRUE. That means that boolean expressions are evaluated numerically and compared to 0. In a numeric context, ENUM columns return the value's position in the list of allowed values, starting with 1. This means that with the definition someflag enum('TRUE','FALSE') someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both of which are TRUE in boolean context. Hence, you cannot use someflag in boolean context and get the expected results. In other words, someflag looks like a boolean if you view the data, but doesn't behave as a boolean in queries. I don't quite know what Mark Warner means by abstract a tinyint(1) into true or false, but I personally find using tinyint for boolean to be a simple solution. I define someflag TINYINT, then set it to 0 for FALSE and 1 (or any other number) for true. Of course, if you're not a programmer it won't look like a boolean when viewing the data (is that what you don't like, Mark?), but it will behave as one. With tinyint you get some added flexibility, which may be an advantage, depending on your application. For example, if I store the number of children a person has in the tinyint kids, I can use kids in boolean context: SELECT * FROM persontable WHERE kids; #people who have children SELECT * FROM persontable WHERE NOT kids; #people who don't One more advantage of tinyints is that, with common programming languages, they continue to function as booleans in your application. Another option is to take advantage of the special error value in every ENUM. If you insert a wrong value, mysql replaces it with '', which is always 0 in numeric context. So, you could define someflag ENUM ('TRUE', 'T'); If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will get the error value, 0 or ''. When you view the data, you will see 'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will behave as boolean in queries. If your values are 'Y' and 'N', then, strictly speaking, you don't have a boolean unless you've defined which is TRUE and which is FALSE. Assuming you want to treat 'Y' as TRUE, you could define yesflag ENUM ('YES', 'Y'); Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding entries, which are TRUE in boolean context. Inserting 'NO' or 'N' (or 'no' or 'n') will get you blanks, which are FALSE in boolean context. Michael -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remove a RPM Installation
rpm -e package Curtis On Tue, 2 Mar 2004, Rafael Diaz Valdes wrote: Please how can I remove a RPM installation. I used MySQL-server-4.1.1-1.i386.rpm to install MySQL, but how can I delete it. -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping MySQL result set to a spreadsheet
SELECT rest of query into OUTFILE some place the mysql user can write to Should get you a tab delimited file there are more options for using different delimiters and field encapsulations, etc. Its in the manual. curtis On Sun, 29 Feb 2004, Joshua Beall wrote: Hi All, I am wondering if anyone can point me to a utility that will let me dump the result of a MySQL query to a file I can open in MS Excel or (preferably) OpenOffice.org Calc. phpMyAdmin lets you do this if you want to dump a SELECT * FROM table, but if I want to fine tune it I cannot (unless I am missing something) MySQL CC lets me save the result of a query to a test file right click-save results, but this format does not quite conform to either Excel or Calc's text format. I could fix it by hand, but before I would do that, I think I would write a PHP script that would generate the right output. But before I spend any time working on doing that, I am wondering if anyone knows of a way to do this already? I am running MySQL 4.0.12-standard on Mandrake 9.2 on the server, and my workstation is an XP Pro box. I use MySQL CC 0.9.1-beta and phpMyAdmin 2.5.0, but am completely willing to upgrade more recent versions of either of these tools, or try a new one. Thanks for any feedback! Sincerely, -Josh p.s. How do I use my newsreader to post to these mailing lists? I see that news.gmane.org seems to carry the mysql lists, but it would not let me post, telling me (even after I was subscribed to the list with the same email address I use in my newsreader): Outlook Express could not post your message. Subject 'Dumping MySQL result set to a spreadsheet', Account: 'news.gmane.org', Server: 'news.gmane.org', Protocol: NNTP, Server Response: '441 You are not allowed to approve postings', Port: 119, Secure(SSL): No, Server Error: 441, Error Number: 0x800CCCA9 -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another loss of mysql
There are many companies that develop on MySQL then market the product with Oracle for the same reasons you statethe name. curtis On Mon, 1 Mar 2004, Leo wrote: After two years of developing a new system based on MySQL for the company i work at... it turned out to face a failure.. not because the performance.. nor the price... finally the company choose Oracle Application Suite because the Oracle brand it self is a guarantee to bussiness competition i mourn for the dead of my mysql project good bye.. good luck -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com http://gtw.binasanprima.com/~leo -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple concurrent transactions per connection
checkout http://www.dbmail.org On Sun, 15 Feb 2004, Chris Nolan wrote: Hi all, I'm currently designing an open-source messaging server that will use MySQL as the data store (in embedded form). High performance is one of the goals of this project, so I have been examining possible I/O models and seem to have settled on a model where each thread services many requests using non-blocking I/O and keeping track of how much of the request has been satisfied. Given this model, each thread is obviously going to want to have multiple transactions outstanding. Is this something that might be added to MySQL in future or am I totally overestimating the expense of using one thread per connection? Regards, Chris -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect to MySQL via PHP
Did you install PHP-mysql*.rpm? Curtis On Tue, 10 Feb 2004, Eric W. Holzapfel wrote: Hello Listers, I have a problem with my PHP/redhat setup, and possible problem with my Mysql setup. I have Apache (2.0) and PHP (4.3.2) installed on a Red Hat 3.0 ES system. I have MySql installed on a Slackware linux box. I want to be able to use Apache/PHP to connect to the mysql database on the slackware box. I think that PHP is set up ok, which may be a lie, because PHP says it does not recognize the commands like - mysql_pconnect and mysql_connect. Also if I try to connect to the database using something like this: mysql://user,[EMAIL PROTECTED] demodb this fails and the or die getMessage() returns DB: no such database. (I am trying to use the Pear DB here) Do I need to have mysql installed on the red hat machine? I can connect to the slackware linux box from a Windows machine using ODBC. Any ideas on what I have not done, or what I have done wrong? eric -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL search engine
Have you tried explain? Have you indexed the table? Curtis On Sun, 8 Feb 2004, ___bug wrote: Hello, What is the best way to search a table with about 500.000 rows of varchar type. --- / id / time / name / --- Like '%blah%' and x Like '%blub%' is too slow (takes about 20 sec) I tried using a fulltext index and search by using MATCH() AGAINST() The problem is each varchar (name) contains not space seperated words but they are combined with . or - or _ For example I have the following row: | 567456 | 20040102 | Owg-08299-abzu_via.lap-2003 | Now i want to be able to search for (sorted by date): Owg abzu lap or 082 abzu_via 2003 or Owg-08299-abzu_via.lap-2003 or Owg 08299 abzu via lap 2003 or 99-abzu Anyone can help me? Thanks Chris -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query matching
I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query matching
Thanks, that seemed the sensible way to me as well. I just didn't know for sure if you could do that in a MySQL query for sure. Thanks, Ed On Fri, 6 Feb 2004, John McCaskey wrote: Yes, I think the most straight forward way is to simply put in a series of grouped OR statements. See below. SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND ( changelog.orig_id = pages.mls_1 OR changelog.orig_id = pages.mls_2 OR changelog.orig_id = pages.mls_3 OR changelog.orig_id = pages.mls_4 OR changelog.orig_id = pages.mls_5 OR changelog.orig_id = pages.mls_6 OR changelog.orig_id = pages.mls_7 OR changelog.orig_id = pages.mls_8 OR changelog.orig_id = pages.mls_9 OR changelog.orig_id = pages.mls_10 OR changelog.orig_id = pages.mls_11 OR changelog.orig_id = pages.mls_12 ) John A. McCaskey -Original Message- From: Ed Curtis [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: Query matching I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- 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]
Sorting by more than 1 column
I didn't know if this was possible and haven't tried yet. My boss wants me to sort results by 3 columns (city, county, price.) He would like city and county in alphabetical order a-z and have price from highest to lowest. I told him I didn't think it was possible to sort two different fields one acending and one descending. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
select last_insert_id(); or in php use the mysql_insert_id() eg: $somevalue = mysql_insert_id(); print (The last auto incremented number was: $somevaluebr\n); Cheers Curtis Paul Fine said: Greetinsg. If I have a table like with a column being the PK for the table and being an Auto Increment value, what is the best way to return this value to my script? It is possible that additional rows may have been added during the small wait. Ie. Col 1 Col 2 Col 3 AA# SmallText SmallText 123 Foo Bar 124 GoodGuy So my script (PHP) for adding records to the database inserts NULL,text,text into the table but I need to echo back the # that was created! My thought was to maybe grab the last entry in the database before the insert, perform the insert and then query where the file # is greater than that last entry and where the text matches the columns appropriately. I imagine there has to be a better way! Thanks for any help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Matthew Stanfield said: Hi, Usually, i'll use enum('0','1') in place of a boolean type. Curtis [snip] well. The only annoying thing I can think of, from a programming perspective, is MySQL's lack of a Boolean type - the manual says use TINYINT(1) which works fine but is slightly annoying because of the extra type conversion needed every time you use it. Apparently MySQL will be implementing the Boolean type soon in accordance with whatever SQL standard requires it. Quite why it still has not been implemented, even though MySQL is into version 4, I have no idea - as a programmer I find this a staggering omission but presumably they have their reasons and perhaps most people are happy with TINYINT(1), but for clarity of code TINYINT(1) is inferior to a Boolean type. I hope this helps, ..matthew Jerry Apfelbaum wrote: Hello. I have been tasked with evaluating open source databases for a large upcoming project: e-commerce, B2B, high availability. The O/S is most likely to be Linux, although FreeBSD could possibly be used (lower probability). So far, it seems that MySQL, MaxDB, PostgreSQL, and Interbase/Firebird are possible candidates. Does anyone know why we should or should not use any of these? Does anyone know of other possibilities? Id very much appreciate hearing your comments and recommendations. I have only recently started these evaluations. BTW, my own background is from the Oracle DBA world. MySQL is certainly popular and seems to have very good performance, but I am concerned that the lack of Triggers, Stored Procedures, User-Defined Functions, and Views (to a lesser degree ) will be a disadvantage. MaxDB appears to be more feature-rich and possibly more industrial-strength. How does its performance and stability compare to the others? Many Thanks. Jerry Apfelbaum Toronto -- 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]