Re: Faster version of Movie Search
Mark, You're killing me because I can't visualize the source tables. A query like this is bread and butter, it shouldn't take to long to execute. Give me a better idea of the table structure and relationships between them. I try to help. Regards, Adam On Feb 4, 2004, at 11:04 PM, Mark wrote: I have a site where members rate movies they've seen. I run a routine to recommend 5 titles based on movies they've given a max 5 rating. It's very slow, and I think a better MySQL query can speed it up. I'm running MySQL 3.23.54. Tables: movies (unique id plus movie info) subcats (movie id, subcategory id) ratings (movie id, user id) Currently: Step 1: // User's top five movie subcategories SELECT COUNT(s.subcategory) AS cnt, s.subcategory FROM ratings r, movies m, movie_subcat s WHERE r.rating = 5 AND r.user_id = $user_id AND r.type = 'movie' AND m.id = r.item AND s.movie = m.id GROUP BY s.subcategory ORDER BY cnt DESC LIMIT 5; // Create a list of subcategory IDs to match Step 2: // Which titles already rated? SELECT item FROM ratings WHERE user_id = $user_id // This is where it slows things down by creating a huge list of ids NOT to match Step 3: SELECT m.id, m.title FROM movies m, movie_subcat s WHERE s.movie = m.id AND m.release < NOW() [AND m.id NOT IN (huge list of ids NOT to match)] [AND s.subcategory IN (list of 5 subcats)] GROUP BY m.id ORDER BY RAND() LIMIT 5 Thanks to any takers! -- 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: syntax question..
Kalin, Kalin> thanks to all... yes, Rhino - i'm new. my first Kalin> post. the version is the problem indeed. it's Kalin> 4.0.13-standard. how would i achieve the same Kalin> query in this version?... You'll need to provide the following: (1) What is the result you want to achieve? (2) What are the details of the tables in the join (column names & data types)? Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem connecting to MySQL 4.1.7 running on another machine
Kevin, Could be a few things causing problems. Lets look at the most obvious. First, make sure you can login locally as root. Second, MySQL authenticates based on user, password, and host. If you have a know user with the right password coming from an unknown host MySQL will bounce you. http://dev.mysql.com/doc/mysql/en/Connection_access.html In the MySQL database, there is a table named `user`. In that table is a list of authorized users to the database server. Check the table and make sure you're accounts are in their and you have the host set correctly. -- Execute this with a user that has read access to MySQL database. SELECT u.user, u.host FROM mysql.user u; If you see something like this, then you know user `Bigkevin` can only connect from the db-server's box. ++---+ | user | host | ++---+ | BigKevin | localhost | ++---+ If you see something like this, then you know user `BigKevin` can connect from _ANY_ machine. ++---+ | user | host | ++---+ | BigKevin | % | ++---+ Hope this helps some. Cheers, Adam Kevin Barry> > I've installed ver 4.1.7 on a brand new Fedora Core 3 server but cannot > connect remotely from a Mac OSX machine running Navicat or a Win XP machine > running MySQL Admin. I can ping the machine with no problem and have opened > the firewall for the 3306 port. > > The error I receive is: > > Could not connect to the specified instance. > > MySQL Error Number 2003 > Can't connect to MySQL server on 192.168.0.11(10060) > > Thanks. > > Kevin > Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database/Table Structure
Michael, There are a bunch. I'll assume you have a Win32 client to use these tools on. Take a look at: - Erwin (http://www3.ca.com/Solutions/Product.asp?ID=260) - MicroLap Database Designer (http://www.microolap.com/dba/mysql/designer/) - ER/Studio (http://www.embarcadero.com/products/erstudio/index.html) There are many other some are probably free. Look around! I hope you find something you like. Cheers, Adam Ferguson, Michael> > G'Day All, > > Can anyone recommend an application that will read/probe a MySQL > database on a Linux server and generate a graphical output of > thedatabase/table structure? FlowChart type? > > Thanks > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to dump from GUI client
Eve, >From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley> > > I'm not sure how best to proceed in dumping data from 1 database and getting > a copy of the export, in order to transfer it to another server. I usually > use phpmyadmin to do an export, which nicely creates a .zip file of > everything. I managed to get it connected with Mysql Control Center, but am > not sure how to dump from this. So I figure I can: > > A. use a command line (in which case, what commnands should I use to dump > and export to a file), > Or > B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config > file for this), > Or > C. learn how to dump from MySql CC (how? I saw no way of handling this from > MySQL CC), > Or > D. use another GUI client (which one?). > > The server in question is mysql.loosefoot.com. Oddly, it was connecting fine > until my company decided to move to a new server, and suddenly, it throws an > error that "Connection to database failed: Unknown MySQL Server Host > 'mysql.loosefoot.com' (0)". I've changed *nothing* in my connect script, and > as mentioned, I can connect to the database via other means. > > What would you suggest as the least painful solution? > > Thanks, > Eve > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
Ziggy, I'm not sure what you need help with. If you want to get data into MySQL from Excel, consider saving the data (I assume it is a table) as a CSV then using the LOAD DATA command to bring the data into a temporary table in MySQL. Take a look at the command in the help pages. Regards, Adam David Ziggy Lubowa> > > Hey guys , > > i know this has been discussed but looking through the archives i have more > less hit a stand still , i have one big excel file which has data i would > like to extract., Now i am no expert in perl neither am i in php , i have > looked at some tools like navicat but i dont see where you actually start the > app , if that is what happens. Any help is highly appreciated. > > > cheers > > -Z Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
frecvent table corruption
Hello. My company is using mysql as a backed to Bugzilla. However, recently, as the number of users that are logging bugs increased we are experiencing frequent table corruption and loss of data. The actual error message we get is: " SELECT query FROM namedqueries WHERE userid = 4 AND name = '(Default query)': Got error 127 from table handler at globals.pl line 276. " After repairing the table by using the mysql Control Center we lose lots of data, even 60% of it. The mysql version is 4.0.18 installed on Red Hat. Is this a bug? How exactly do we fix this, with a patch? Regards,Alex
RE: frecvent table corruption
-Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 12:40 PM To: [EMAIL PROTECTED] Subject: Re: frecvent table corruption "adam" <[EMAIL PROTECTED]> wrote: > The actual error message we get is: > > " SELECT query FROM namedqueries WHERE userid = 4 AND name = '(Default > query)': Got error 127 from table handler at globals.pl line 276. " [EMAIL PROTECTED] egor]$ perror 127 Error code 127: Unknown error 127 127 = Record-file is crashed > After repairing the table by using the mysql Control Center we lose lots of > data, even 60% of it. > > > > The mysql version is 4.0.18 installed on Red Hat. Abnormal situation. What MySQL version is installed on Red Hat? The Red Hat build or MySQL build or your own build? It is the binary mysql build , from the following archive: mysql-standard-4.0.18-pc-linux-i686.tar.gz I have actually looked at some of the possible causes and eliminated the following: -the disk space is sufficient -the mysql server never crased, it has been running for oover 100 days. Also, I am using MyIsam table with the skip-locking option, because we only have one mysld instance. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to turn on auditing for mysql
Is there any way to log all the activity in a mysql database? I would like to know every single delete insert or select on all tables for my database. Regards, Alex
Problem with my bin-logs in replication
Occasionally I will notice that one of my slaves is not in sync. It will be reading fine from the master's bin-log, but it gets hung up in the relay log and stops. It seems there is corrupt data in the relay log. When I use the mysqlbinlog utility, I get tons of "mysqlbinlog: Error writing file 'UNOPENED' (Errcode: 29)", and when it does show the queries, the queries are all messed up. Missing the first half of a big select or insert - so when it gets to one of those in the relay-log it also quits. This causes a great problem because I cannot "fix" this issue by pointing it ahead in the relay log. Once this happens the first time, the rest of the log is riddled with them. I may not notice this for a day or more. The master gets TONS of writes, it is very highly used. I'm wondering if this could be something where the disks themselves are too slow for the application? Is that possible? Or should it just queue everything it needs to write? Or is there a buffer somewhere for this? So I have two questions: a) What is going on here, can I fix this easily (or not so easily even!) b) Is there a way to have mysql easily notify me via email, or hitting a url or some sort of automagical device to tell me things are stopped, or is the best way to write a quick script to pull show slave status and check the vars? Thank you, Adam ### This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.F-Secure.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SuperSmack on x86_64 / gcc 3.2.2
Anyone ever get super-smack to compile on this system? I get tons of warnings/errors that look like syntax errors almost. Not sure what I am doing wrong. I get a list of errors/warnings like this: c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/mysql/include -g -O2 -c client.cc In file included from client.h:15, from client.cc:1: dictionary.h:15: `string' was not declared in this scope dictionary.h:15: 'vector' is used as a type, but is not defined as a type. dictionary.h:17: 'string' is used as a type, but is not defined as a type. dictionary.h:59: 'string' is used as a type, but is not defined as a type. dictionary.h: In constructor `Unique_dictionary::Unique_dictionary()': dictionary.h:65: class `Unique_dictionary' does not have any field named `templ ' Thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SuperSmack on x86_64 / gcc 3.2.2
Wow! Great timing. Thanks Jeremy -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 11:13 PM To: Adam Cc: [EMAIL PROTECTED] Subject: Re: SuperSmack on x86_64 / gcc 3.2.2 On Tue, Sep 02, 2003 at 10:23:42PM -0400, Adam wrote: > Anyone ever get super-smack to compile on this system? > > I get tons of warnings/errors that look like syntax errors almost. Not > sure what I am doing wrong. Funny you mention that. I'm about to post version 1.2 that may work for you. Stay tuned. I need another 30 minutes to package it and get the site updated, etc. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,385,301,075 queries (495/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
supersmack question regarding dictionary
If I wanted to make a dictionary entry that would just provide a sequential # starting at X and every time its used, it adds one... Is this possible? How? I do not quite understand the docs, but it seems like unique and template has this ability. Thanks, Adam
Re: Using Access as client all fields are marked "#Deleted"
Osvaldo's question is a good one. How are you connecting to the MySQL instance? I use MySQL and Access as you are, but I use a MySQL instance running on OS X. However, I don't have that problem. I use linked tables to bring the MySQL tables into MS Access. I have had quirky behavior when executing DDL queries to MySQL. Regards, Adam On Apr 1, 2004, at 4:14 AM, Patrick Kirk wrote: Hi all, My setup is windoes 2000, Access 2000 and Mysql built from source on Debian. Some tables can be seen but all new entries are marked "#Deleted". If I close the table in Access and re-open it, its looks fine. Others have every field marked "#Deleted" and nothing I do makes those tables readbale. HOWEVER, queries that rely on the data work accurately but show the results as "#Deleted". I've installed myodbc, the latest MDAC and Jet Service packs. Is there some test or diagnostic procedure to establish what isn't working? I think I've followed all the documented steps but the lack of diagnostics makes this very difficult. Thanks in advance, Patrick -- 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: Why can't I use an "AS" value in the WHERE clause.
Daevid, SELECT * FROM wifi_table WHERE active = 1 HAVING unix_timestamp()-unix_timestamp(last_seen) < 600; Regards, Adam On Apr 5, 2004, at 8:29 PM, Daevid Vincent wrote: I'm curious when will I be able to do something like this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as active FROM wifi_table WHERE active = 1; It's so obnoxious, especially since I can do this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as active FROM wifi_table WHERE unix_timestamp()-unix_timestamp(last_seen) < 600; Why do I have to do the math TWICE?! *sigh* -- 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: Why can't I use an "AS" value in the WHERE clause.
Mike, I see what you're saying `active` was the alias name not an actual column. Ironically I was using a HAVING clause because I agree with that last post. Mike, why keep the `IF` statement? You're really saying give me all the records where this expression is true. Why not just move the expression in the `IF` to the HAVING clause? So take my old statement and ditch the where clause. You'll get: SELECT * FROM wifi_table HAVING unix_timestamp()-unix_timestamp(last_seen) < 600; A little easier on the eyes no? Cheers, Adam On Apr 6, 2004, at 9:42 PM, Michael Stassen wrote: Adam, That won't work. Daevid doesn't have a column named active. Nor does he have to do the math twice. As was pointed out earlier, he can do what he wants using HAVING instead of WHERE, like this: SELECT *, IF(((unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) active FROM wifi_table HAVING active = 1; Michael Adam wrote: Daevid, SELECT * FROM wifi_table WHERE active = 1 HAVING unix_timestamp()-unix_timestamp(last_seen) < 600; Regards, Adam On Apr 5, 2004, at 8:29 PM, Daevid Vincent wrote: I'm curious when will I be able to do something like this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as active FROM wifi_table WHERE active = 1; It's so obnoxious, especially since I can do this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as active FROM wifi_table WHERE unix_timestamp()-unix_timestamp(last_seen) < 600; Why do I have to do the math TWICE?! *sigh* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an "AS" value in the WHERE clause.
MIchael, Point well taken. Cheers, Adam On Apr 8, 2004, at 2:47 PM, Michael Stassen wrote: Good point. I was focused on the question of using the alias to restrict results, so I left the function in the SELECT part. As you say, in this query, that would just give a useless column of '1's, so you might as well leave it out. In that case, though, the alias question is moot. That is, it doesn't really make any difference whether you put the condition in the WHERE or HAVING clause. On the other hand, we can imagine a query where we want to see a calculated result and use it to screen which rows are returned. Then using the alias in the HAVING clause is the way to go. For example, something like SELECT *, unix_timestamp()-unix_timestamp(last_seen) AS Last_Active FROM wifi_table HAVING Last_Active < 600; Michael Adam wrote: Mike, I see what you're saying `active` was the alias name not an actual column. Ironically I was using a HAVING clause because I agree with that last post. Mike, why keep the `IF` statement? You're really saying give me all the records where this expression is true. Why not just move the expression in the `IF` to the HAVING clause? So take my old statement and ditch the where clause. You'll get: SELECT * FROM wifi_table HAVING unix_timestamp()-unix_timestamp(last_seen) < 600; A little easier on the eyes no? Cheers, Adam On Apr 6, 2004, at 9:42 PM, Michael Stassen wrote: Adam, That won't work. Daevid doesn't have a column named active. Nor does he have to do the math twice. As was pointed out earlier, he can do what he wants using HAVING instead of WHERE, like this: SELECT *, IF(((unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) active FROM wifi_table HAVING active = 1; Michael Adam wrote: Daevid, SELECT * FROM wifi_table WHERE active = 1 HAVING unix_timestamp()-unix_timestamp(last_seen) < 600; Regards, Adam On Apr 5, 2004, at 8:29 PM, Daevid Vincent wrote: I'm curious when will I be able to do something like this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as active FROM wifi_table WHERE active = 1; It's so obnoxious, especially since I can do this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as active FROM wifi_table WHERE unix_timestamp()-unix_timestamp(last_seen) < 600; Why do I have to do the math TWICE?! *sigh* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Trying to run multiple statements through MySQL monitor
All, I've got data listing people's identification details including first and last name, social, and status in the application. Given this data, I want to know how many duplicate socials I have. Normally I'd write a query with a sub select to get that information - something like this: SELECT a.* FROM table a WHERE a.ssn IN (SELECT b.ssn FROM table b GROUP BY b.ssn HAVING count(b.ssn) > 1); However, I don't have MySQL 4.1.x so the sub select is out of the question. I've decided to identify the duplicate socials and dump them into a table. Join the table with the dups to the table storing the identification details and then drop the dups table. However, its not working. From the first statement (see below) I am getting an error reading ERROR 1060 at line 3: Duplicate column name 'ssn' WTF? # SQL Statements CREATE TABLE dups (ssn VARCHAR(25) NULL) SELECT s.ssn FROM org s GROUP BY s.ssn HAVING COUNT(s.ssn) > 1; SELECT s.* FROM org s, tbl_tmp_ssn t WHERE s.ssn = t.ssn; DROP TABLE dups; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Datetime Default Value - I want to know too!
This is a great question! I also work on SQL Server (2k) databases as well as Access and MySQL. I run into the same problem. So far, I've just changed the SQL in my applications, but I also would like to know if a default can be set. Regards, Adam On Apr 18, 2004, at 12:01 AM, Stormblade wrote: Hey all, I'm currently converting a SQLServer 2000 database over to MySQL. I have a web application that currently uses SQLServer but will be using MySQL soon as I can get this done. I was able to match data types but so far I have not found a way to let the database handle setting the default value of a datetime field to the current date/time. Is this possible? In my research I found 2 main suggestions: 1. Use timestamp. While this suggestion is good for my modified fields it is not really suitable for the others which basically will get set once and that's it. 2. Use datetime but specify the date/time in your SQL. This is also not very suitable for two reasons. One is that it would require me to go through the web application and change all the SQL which I can do but rather not. The second reason is that this approach is dangerous. There is no guarantee that the database is on the same system as the web application. May not even be in the same timezone. So I prefer a more loosely coupled design. If I can't find any other way I will have to go with the second option but I'd really like to find out a better way if one exists. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- 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: Create table results in (errno: 121)
Tom, More specifically, find out what the error messages means by using the `SHOW INNODB STATUS` command. This will give a description of the last error message generated by InnoDb. In your case, its definitely the foreign key that is causing the problem. As far as what it is specifically, I'd reference the message, and if that doesn't help bring the message to the group. Regards, Adam On Apr 22, 2004, at 9:29 AM, Tom Brown wrote: Hi, I'm trying to create a table with the following syntax CREATE TABLE TNSession ( SessionID int(11) NOT NULL auto_increment, IPAddress varchar(50) default NULL, Created datetime default NULL, Expired tinyint(4) default '0', AccountID int(11) default '0', PRIMARY KEY (SessionID), KEY FK_AccountID (AccountID), CONSTRAINT `0_20` FOREIGN KEY (`AccountID`) REFERENCES `TNAccount` (`AccountID`) ) TYPE=InnoDB ROW_FORMAT=DYNAMIC; it results in a ERROR 1005: Can't create table './TNMailServer/TNSession.frm' (errno: 121) This is on version mysql-standard-4.0.18-pc-linux-i686 Can anyone shed any light on this as it has me stumped - There is a TNAccount table with a AccountID column? thanks for any assistance Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This electronic mail transmission contains confidential information intended only for the person(s) named. Any use, distribution, copying or disclosure by another person is strictly prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to uninstall mysql
Hello. I have installed MySql on RH Linux, however since I have installed in the wrong location I would like to uninstall. Basically I have installed the binary version from the "mysql-standard-4.0.18-pc-linux-i686.tar.gz" and I have followed the instructions in the INSTALL-BINARY, in particular I have run the scripts/mysql_install_db script. My question is: how can I uninstall it? (Is it just stop server instances and delete the directory ?) Regards, Alex
mysqldump under cron
Hello. I am using mysql 4.0.18 on rh3, and I would like to daily save db state. I have this small script under the cron directory that when executed from the prompt works fine. Basically the dump is done by: mysqldump --user=root --password= --opt bugs > $BACKUPDIR$BACKUPSQLFILE My problem is that it does not seem to work when the crond calls the script. The result of the dump is a zero size sql file. I know this might be a Linux problem,but nevertheless what might be the cause? Regards, Alex
MySQL 3.23 Lost Connection
Hello. I have compiled/rebuilt the MySQL 3.23.49 Source RPM under kernel 2.4.17, and glibc 2.1. Using gcc version 2.95.4 20010319 (prerelease). I had 2.96 on there and headed the warnings and downgraded to 2.95. Randomly I get the Lost Connection using PHP 4.1.2... Any idea why? Thanks Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL 3.23 Lost Connection
Happens randomly among all my scripts. Mysql is being started with: /usr/sbin/mysqld --basedir=/ --datadir=/usr/local/lib/mysql --user=mysql --pid-file=/usr/local/lib/mysql/mysqld.pid --skip-locking -O max_connect_errors=1 -O back_log=30 -O max_connections=580 -O table_cache=512 -O Machine has a gig of ram. Very little traffic. adam -Original Message- From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 3:52 PM To: Adam Helfgott Subject: Re: MySQL 3.23 Lost Connection Hi, Perhaps a mysql thread which runs out of memory ? Regards, Jocelyn Fournier - Original Message - From: "Adam Helfgott" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 05, 2002 9:32 PM Subject: MySQL 3.23 Lost Connection > > Hello. > I have compiled/rebuilt the MySQL 3.23.49 Source RPM under kernel 2.4.17, > and glibc 2.1. > > Using gcc version 2.95.4 20010319 (prerelease). I had 2.96 on there and > headed the warnings and downgraded to 2.95. > > Randomly I get the Lost Connection using PHP 4.1.2... Any idea why? > > Thanks > Adam > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird problem again.
I was the one having issues getting Lost Connection and whatnot for no reason. I have recompiled under glibc2.2.5 with 2.95 and 3.04. I just tried 4.0.2 from the source tree. Sometimes from the command line of doing mysql -uroot -p I get this: ERROR: And that's it. I run it again, and it goes in fine. Any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL 3.23 Lost Connection
Yes thank you for these links. I have already read the documentation as the first thing I did. The second thing I did was recompile. The third thing I did was subscribe to the list. I can write a php script like this: I will run this. A lot of the time I wont get anything to the screen. Other times I will get a bunch of "Failed". Completely Random. Oh, users has 17 rows in it. Machine is a dual p3-500 with 1gb RAM. I've tried recompiling with GCC 2.95, 2.96 and 3.04. I've tried this under GLIBC 2.1.3 and 2.2.5. Running linux kernel 2.4.17. Tried default startup, and my own options that works well on another production server of a similar configuration. Apache/PHP and Qmail work flawlessly on this machine. Thanks for everyones help on this matter! -adam -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 9:48 AM To: [EMAIL PROTECTED] Subject: MySQL 3.23 Lost Connection Adam, Tuesday, March 05, 2002, 10:14:28 PM, you wrote: A> I have compiled/rebuilt the MySQL 3.23.49 Source RPM under kernel A> 2.4.17, and glibc 2.1. A> Using gcc version 2.95.4 20010319 (prerelease). I had 2.96 on there and A> headed the warnings and downgraded to 2.95. A> Randomly I get the Lost Connection using PHP 4.1.2... Any idea why? "Connection lost" error occurs when communication bug or bug in the client take place. You can find description of error, if you check the following links. Probably they will help you: http://www.mysql.com/doc/G/o/Gone_away.html http://www.mysql.com/doc/P/a/Packet_too_large.html A> Adam -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL coding: Finding singular when search with plural.
I'm assuming your users arnt using a MySQL client directly to perform the queries... And your using middleware like PHP or something. So your PHP code can parse the users responses and then design your SQL queries around your parsed data. -Original Message- From: BadgerBay [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 10:10 AM To: [EMAIL PROTECTED] Subject: SQL coding: Finding singular when search with plural. Hello, This is a good question for when you are sipping coffee or tea and are inspired to write some code. I require additional functionality for my SQL search: MY CURRENT SEARCH SELECT ID, Authors, Year, Title FROM libraryTable WHERE (Authors LIKE '%" + varAuthor + "%') OR (Keywords LIKE '%" + varKeyword + "%') OR (Title LIKE '%" + varTitle + "%')"; ORDER BY Year; ADDITIONAL FUNCTIONALITY REQUIRED 1) The search should be able to locate singular words when the plural (s or es) is entered. Example: If "singers" is entered in the search box, than "singer" would be found. 2)When two or more words are entered in search box (for example, into the variable varTitle), each word entered should be searched for separately. So if "Iyengar Yoga" is entered, than the records found might contain: yoga iyengar India yoga style iyengar yoga tai chi iyengar 3)The search should NOT locate words in which the search word is merely a component. So if "low" is entered in the search box: SHOULD FIND: low lows SHOULD NOT FIND slow below - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Structure as a field
Perhaps you can create a function to serialize the array into text and dump it into a blob in the DB, and have another field(s) with the data you want to search on.. Then unserialize the array when you take it out. -Original Message- From: Hresko, Christian A. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 2:43 PM To: '[EMAIL PROTECTED]' Subject: Structure as a field this is an obvious 'newbie' question, so please bare with me. i'm parsing a lot of data from file, and a good deal of this data is in the form of nested C structs. within any given struct, you might have an array of 256 floats, or an array of 256 structs, with 256 floats, etc, etc. i'd like to have one entry in the field column which can be accessed by name and index. i.e. fieldnameX(1), fieldnameX(2), etc. where the index is the floating point value, a character string, or whatever in an array within a struct. how do i go about doing this, if it's at all possible? (it would seem odd if i had to create n number of fields for each value in an array. especially arrays of large sizes) i'm not looking for the C/C++ or Perl to SQL mechanism, i just haven't found the syntax to create a field of that type in SQL, which is my first, and obvious starting point. thanks, christian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stepping through a MySQL_fetch_array in PHP?
$sql="select * from table where blah=$blah";
$x=mysql_query($sql);
while ($data=mysql_fetch_array($x)) {
echo "$data[columnName]";
}
I think is what your trying to do...
On Thu, 7 Mar 2002, Gavin Philips-Page wrote:
|Could anybody please help:
|
|In the query I have included a limit string which allows me to display
|individual records page by page. The limit string variable is
|incremented/decremented using hyperlinks. I feel it would be better if I
|could somehow manually step through the returned
|$query_data = mysql_fetch_array($result). Is it possible to create a loop
|with a counter to step through the records contained in the array?
|
|// PHP Code
|$link_id = db_connect($default_dbname) ;
| if(!$link_id) error_message(sql_error()) ;
|$query = "select count(*) from $user_tablename" ;
|$result = mysql_query($query) ;
|if(!$result) error_message(sql_error()) ;
|$query_data = mysql_fetch_row($result) ;
|$total_num_user = $query_data[0] ;
|if(!$total_num_user) error_message('No records found!') ;
|
|$query = "select usernumber, userid, userpassword, username, sex,
|nationality, usermail, userprofile, registerdate, date_format(registerdate,
|'%M, %e, %Y') as formatted_registerdate, lastaccesstime,
|date_format(lastaccesstime, '%M, %e, %Y') as formatted_lastaccesstime from
|$user_tablename $order_by_str $sort_order_str $limit_str" ;
|
|$result = mysql_query($query) ;
|if(!$result) error_message(sql_error()) ;
|
|$query_data = mysql_fetch_array($result) ;
|// Table displaying the found records from MySQL database
|
|Thanx
|
|Gavin
|
|
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Web hosting scalability?
Yep.. this is very feasible.. And there are some packages to do this already. http://www.firstworks.com/sqlrelay.html Is a good package and library/API to implement what you want to do. We have done this in the past for a large community based website. -Original Message- From: John Masterson [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 1:22 PM To: [EMAIL PROTECTED] Subject: Web hosting scalability? Situation: mass virtual website hosting, with php/perl/python. One master mysql server, one or more replicated slaves. Question: would it be possible/feasible to write a daemon that accepts connections on behalf of mysqld, and depending on what type of query it is (updating or selecting) farm the query out to the appropriate database server? Perhaps it could do some connection pooling as well. The reason: we'd like to keep telling our users to connect to "the database server", instead of hoping that they will always write correct code and connect to the appropriate servers, which of course they won't. Any feedback (such as, "that's the stupidest idea I've ever heard, why don't you just do _") would be appreciated :) John Masterson Modwest Powerful, Affordable Web Hosting http://www.modwest.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [ANN] Blue World Announces Lasso vs PHP White Paper
There is a point that if you buy APC-Cache and the encoder you are spending a lot of cache. However there is APC, PHPCache and others that do the job.. but not as well. PHP is mildly flawed in the sense that it is somewhat intentionally crippled so Zend can make a profit from sites that adopt it and get big. However with cacheing of the html and whatnot, you can get around all this and have your costs be at 0 of software.. just time to implement.. -Original Message- From: Todd Williamsen [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 2:11 PM To: 'Cathy Cunningham (Blue World Lasso Evangelist)'; 'Chuck "PUP" Payne'; [EMAIL PROTECTED] Subject: RE: [ANN] Blue World Announces Lasso vs PHP White Paper Can you provide a NON-BIAS so-called "TRUE comparison"? I would be curious on what Blueworld has to say. Also, why do you think the TCO is lower with lasso than PHP? I don't get it, there is no cost to own it, just to develop it. And if you develop with PHP correctly, then the TCO is low. Here is my basic TCO comparison: Lasso Software Pro $1199 PHP $0.00 Learning Lasso$2500 PHP $0.00 Development (hr avg) $75 PHP $75/hr. So to me, Lasso isn't worth switching. I would already be $3700 in the hole without even touching it! -Original Message- From: Cathy Cunningham (Blue World Lasso Evangelist) [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 12:37 PM To: Chuck "PUP" Payne; [EMAIL PROTECTED] Subject: Re: [ANN] Blue World Announces Lasso vs PHP White Paper At 1:19 PM -0500 3/7/02, Chuck \"PUP\" Payne wrote: >But who wants to pay. Get a clue that's why PHP, PERL, and other Open Source >Lang., are widely more used. If you do a true comparison in features, you'll see that a PHP-based solution (with commercial add-ons) costs over three times the cost of Lasso. Then there's also the (TCO) total cost of ownership where due to productivity gains in using Lasso, developers end up saving lots of time (e.g. money) compared with using other tools. Then there's also the fact that when you buy Lasso Professional 5, you're also supporting MySQL AB as each copy purchased puts money in their pockets. And, this is a good thing to ensure the future vitality (and growth) of the MySQL market. It also gives you peace of mind in knowing that you have a commercial license of MySQL, which can help ease liability and other concerns that may otherwise be present in certain organizations who may have concerns using just GPL products. Then there's the issue of quality. With Lasso Professional 5, no stone remains unturned with respect to ensuring Web developers have the highest quality offering both in terms of features, documentation, support materials and more. CC -- - Cathy Cunningham [EMAIL PROTECTED] Lasso Evangelist Blue World Communications, Inc. http://www.blueworld.com/ - Lasso Studio is "the easiest way to create a database-driven Web site" - Macworld Magazine - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [ANN] Blue World Announces Lasso vs PHP White Paper
What they need is a PHP->Lasso converter, or offer a dev person to convert your application. Similar to ASP2PHP MYSQL CONTENT: And does Lasso offer MySQL connection pooling? -Original Message- From: John Dean [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 2:59 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [ANN] Blue World Announces Lasso vs PHP White Paper At 12:30 07/03/2002 -0700, [EMAIL PROTECTED] wrote: >I think this is should be > > Learning Lasso$2500 PHP $2500 This may be true if you have no knowledge of either system, but most people on this list have a great deal of experience using PHP and for them that would be money down the drain. >Dosent matter if the product is free it still costs you time. > > > Can you provide a NON-BIAS so-called "TRUE comparison"? I would be > > curious on what Blueworld has to say. Also, why do you think the TCO > > is lower with lasso than PHP? I don't get it, there is no cost to own > > it, just to develop it. And if you develop with PHP correctly, then > > the TCO is low. > > > > Here is my basic TCO comparison: > > > > Lasso Software Pro $1199 PHP $0.00 > > Learning Lasso$2500 PHP $0.00 > > Development (hr avg) $75 PHP $75/hr. > > > > > > So to me, Lasso isn't worth switching. I would already be $3700 in the > > hole without even touching it! > > > > -Original Message- > > From: Cathy Cunningham (Blue World Lasso Evangelist) > > [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, March 07, 2002 12:37 PM > > To: Chuck "PUP" Payne; [EMAIL PROTECTED] > > Subject: Re: [ANN] Blue World Announces Lasso vs PHP White Paper > > > > > > At 1:19 PM -0500 3/7/02, Chuck \"PUP\" Payne wrote: > >>But who wants to pay. Get a clue that's why PHP, PERL, and other Open > > Source > >>Lang., are widely more used. > > > > If you do a true comparison in features, you'll see that a PHP-based > > solution (with commercial add-ons) costs over three times the cost of > > Lasso. > > > > Then there's also the (TCO) total cost of ownership where due to > > productivity gains in using Lasso, developers end up saving lots of > > time (e.g. money) compared with using other tools. > > > > Then there's also the fact that when you buy Lasso Professional 5, > > you're also supporting MySQL AB as each copy purchased puts money in > > their pockets. And, this is a good thing to ensure the future > > vitality (and growth) of the MySQL market. It also gives you peace of > > mind in knowing that you have a commercial license of MySQL, which can > > help ease liability and other concerns that may otherwise be > > present in certain organizations who may have concerns using just GPL > > products. > > > > Then there's the issue of quality. With Lasso Professional 5, no > > stone remains unturned with respect to ensuring Web developers have > > the highest quality offering both in terms of features, > > documentation, support materials and more. > > > > CC > > -- > > > > - > > Cathy Cunningham [EMAIL PROTECTED] > > Lasso Evangelist > > Blue World Communications, Inc. http://www.blueworld.com/ > > - > > > > Lasso Studio is "the easiest way to create a database-driven Web site" > > - Macworld Magazine > > > > - > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> To > > unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > - > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> To > > unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Regards John, Former MySQL Developer - [EMAIL PROTECTED] http://www.rygannon.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread,
RE: I want to RFTM...
That's probably your problem. You don't want to read "fucking the manual", you want to read "the fucking manual"! Sorry. I couldn't resist! :) Your best bet isn't gonna come from a mysql doc. But from a SQL design theory book/resource. -Original Message- From: Matthew Walker [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 8:35 PM To: [EMAIL PROTECTED] Subject: I want to RFTM... I want to find a good site that will help me set up foreign keys /properly/ for InnoDB so that I can have it do referential integrity checks, and all that jazz. Does anyone have any good references on how foreign keys should be set up in general, and how they can be used with InnoDB in particular? Filter: mysql Matthew Walker Ecommerce Project Manager Mountain Top Herbs --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.332 / Virus Database: 186 - Release Date: 3/6/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
/tmp/mysql.sock problem
Apologies for posting such a basic query, but I couldn't find an archived version of the mailing list, or a fix to my problem. Using Linux Mandrake 8.2. It's the problem with mysql.sock, which I've tried to make the /tmp directory sticky, following the command on the mysql site. However, it still hasn't provided me with a mysql.sock file. How do I reinstate this file? advTHANKSance Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: /tmp/mysql.sock problem
Dicky Wahyu Purnomo wrote: > On Tue, 04 Jun 2002 08:04:01 +0100 > adam <[EMAIL PROTECTED]> wrote: > > >>Apologies for posting such a basic query, but I couldn't find an >>archived version of the mailing list, or a fix to my problem. Using >>Linux Mandrake 8.2. >> >>It's the problem with mysql.sock, which I've tried to make the /tmp >>directory sticky, following the command on the mysql site. However, it >>still hasn't provided me with a mysql.sock file. How do I reinstate this >>file? >> > > > the mysql.sock file is automatically created when mysqld is started. > please check your configuration (/etc/my.cnf) and your mysql daemon ... :D > Thanks. However, something's still wrong. Restarted the daemon - /usr/sbin/safe_mysqld & and the file wasn't created. How can I check where this file is going? I've searched for it, but not found it. Adam -- -BEGIN PGP PUBLIC KEY BLOCK- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org Comment: A revocation certificate should follow iEkEIBECAAkFAjxu+xACHQIACgkQEN8jbmPrR/B3LwCcDZs25AlyaMhHxZ0PoFAx 3iFknAEAmwcOYuqBoG2AGYJ5U8z3YiFgWvok =TLkp -END PGP PUBLIC KEY BLOCK- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: /tmp/mysql.sock problem
Dicky Wahyu Purnomo wrote: > On Tue, 04 Jun 2002 08:04:01 +0100 > adam <[EMAIL PROTECTED]> wrote: > > >>Apologies for posting such a basic query, but I couldn't find an >>archived version of the mailing list, or a fix to my problem. Using >>Linux Mandrake 8.2. >> >>It's the problem with mysql.sock, which I've tried to make the /tmp >>directory sticky, following the command on the mysql site. However, it >>still hasn't provided me with a mysql.sock file. How do I reinstate this >>file? >> > > > the mysql.sock file is automatically created when mysqld is started. > please check your configuration (/etc/my.cnf) and your mysql daemon ... :D > On Tue, 04 Jun 2002 10:04:28 +0100 adam <[EMAIL PROTECTED]> wrote: > >>It's the problem with mysql.sock, which I've tried to make the /tmp > >>directory sticky, following the command on the mysql site. However, it > >>still hasn't provided me with a mysql.sock file. How do I reinstate this > >>file? > >> > > > > > > the mysql.sock file is automatically created when mysqld is started. > > please check your configuration (/etc/my.cnf) and your mysql daemon ... :D >did u install mysql from rpm or tgz ? >if rpm ... >0. make sure that all mysqld stopped ... killall -9 safe_mysqld; killall -9 mysqld >1. /etc/init.d/mysql start or /etc/init.d/mysql restart >2. ps ax | grep mysqld >3. you should see minimal line with safe_mysqld and only mysqld >4. do --> shell $ updatedb >5. do --> shell $ locate mysql.sock > >6. for sure ... run mysql client --> shell $ mysql > I've followed these instructions and strangely, mysql.sock appears in /var/lib/mysql/ Should I copy it over? I has a funny equals sign next to it in Midnight Commander. Thanks again for advice. Adam -- -BEGIN PGP PUBLIC KEY BLOCK- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org Comment: A revocation certificate should follow iEkEIBECAAkFAjxu+xACHQIACgkQEN8jbmPrR/B3LwCcDZs25AlyaMhHxZ0PoFAx 3iFknAEAmwcOYuqBoG2AGYJ5U8z3YiFgWvok =TLkp -END PGP PUBLIC KEY BLOCK- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: /tmp/mysql.sock problem
Egor Egorov wrote: > adam, > > Tuesday, June 04, 2002, 12:05:30 PM, you wrote: > >>>the mysql.sock file is automatically created when mysqld is started. >>>please check your configuration (/etc/my.cnf) and your mysql daemon ... :D >>> >> > > a> Thanks. However, something's still wrong. > > a> Restarted the daemon - /usr/sbin/safe_mysqld & and the file wasn't created. > > a> How can I check where this file is going? I've searched for it, but not > a> found it. > > Are you sure that your MySQL server was started? Check with the > following command if there is mysqld in the process list >ps ax | grep mysqld Egor, This was the output I received: [root@localhost mysql]# ps ax | grep mysqld 2398 pts/0S 0:00 grep mysqld [root@localhost mysql]# Thanks Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: /tmp/mysql.sock problem
OK got this sorted in the end, by putting a soft link in to the /tmp folder pointing to the /var/lib - thanks one and all. Adam Dicky Wahyu Purnomo wrote: > On Tue, 04 Jun 2002 08:04:01 +0100 > adam <[EMAIL PROTECTED]> wrote: > > >>Apologies for posting such a basic query, but I couldn't find an >>archived version of the mailing list, or a fix to my problem. Using >>Linux Mandrake 8.2. >> >>It's the problem with mysql.sock, which I've tried to make the /tmp >>directory sticky, following the command on the mysql site. However, it >>still hasn't provided me with a mysql.sock file. How do I reinstate this >>file? >> > > > the mysql.sock file is automatically created when mysqld is started. > please check your configuration (/etc/my.cnf) and your mysql daemon ... :D > -- -BEGIN PGP PUBLIC KEY BLOCK- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org Comment: A revocation certificate should follow iEkEIBECAAkFAjxu+xACHQIACgkQEN8jbmPrR/B3LwCcDZs25AlyaMhHxZ0PoFAx 3iFknAEAmwcOYuqBoG2AGYJ5U8z3YiFgWvok =TLkp -END PGP PUBLIC KEY BLOCK- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.sock
Oscar Mena wrote: > Im trying to configure MySQL on a RaQ Cobalt > everything seems fine > but then I type ./mysql -u root -p > and type my pass > and I get ERROR 2002: Can't connect to local MySQL server through socket > '/tmp/mysql.sock' > (111) > > I took a look at mysql.sock and that file is empty > > any ideas how to fix that? *nix? Is mysql.sock in /etc? Mine wasn't, so I put in a soft link from the location and it worked fine after that. Adam -- -BEGIN PGP PUBLIC KEY BLOCK- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org Comment: A revocation certificate should follow iEkEIBECAAkFAjxu+xACHQIACgkQEN8jbmPrR/B3LwCcDZs25AlyaMhHxZ0PoFAx 3iFknAEAmwcOYuqBoG2AGYJ5U8z3YiFgWvok =TLkp -END PGP PUBLIC KEY BLOCK- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.sock
Oscar Mena wrote: > is not in /etc > how do I put that soft link? > > - Original Message ----- > From: "adam" <[EMAIL PROTECTED]> > To: "Oscar Mena" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Saturday, June 08, 2002 1:47 PM > Subject: Re: mysql.sock > > > >>Oscar Mena wrote: >> >>>Im trying to configure MySQL on a RaQ Cobalt >>>everything seems fine >>>but then I type ./mysql -u root -p >>>and type my pass >>>and I get ERROR 2002: Can't connect to local MySQL server through socket >>>'/tmp/mysql.sock' >>> (111) >>> >>>I took a look at mysql.sock and that file is empty >>> >>>any ideas how to fix that? >> >>*nix? >> >>Is mysql.sock in /etc? Mine wasn't, so I put in a soft link from the >>location and it worked fine after that. >>Adam Ahah, so we're definately talking *nux then? Go to the directory where the mysql.sock lives and type ln -s mysql.sock /etc - should do the trick. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: scripts needed
JR wrote: > To setup your table see: http://www.mysql.com/doc/C/R/CREATE_TABLE.html > > I do not believe that mysql will read the foxpro db format. Dump your > foxpro table data into a text file and use mysqlimport to pull the data > into your mysql table. See: > http://www.mysql.com/doc/m/y/mysqlimport.html > > As far as add, edit, search, etc., you may want to take a look at > phpMyEdit at: > http://phpmyedit.sourceforge.net/ > > JR > > - > SallyJo, Inc., is your source for Web Hosting & Programming Services. > Visit: http://SallyJoInc.com > > > >>-Original Message- >>From: Edwin Davidson [mailto:[EMAIL PROTECTED]] >>Sent: Saturday, June 08, 2002 9:07 AM >>To: [EMAIL PROTECTED] >>Subject: scripts needed >> >> >> >>Our internet provider is starting to host database services. >>I am the webmaster of our local church and we have a library >>of 1000 books, tapes, etc. in a small Foxpro database. >> >>I need a script to create a MySQL table with the following character >>fields: >> >>field length >> >>section 28 >>author1 42 >>author2 42 >>title156 >>publisher80 >>number 10 >>authorsort 70 >> >>I also need a script to import my Foxpro data into a MySQL >>table. I'm going to just update my Foxpro table and refresh >>the MySQL table manually >>- easier that way in this case. >> >>Also, I'll need a HTML form and script to allow people to >>search by title, author, publisher, etc and to let them see >>the results. Pretty standard stuff really. >> >>I don't see the point of reinventing the wheel so would one >>of you have a couple of scripts that I can use or adjust ? >>It will be running on a UNIX platform. >> >>Thank you very much. >>Edwin Davidson You could also try http://php.resourceindex.com to see if any of the scripts there fit the bill. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Obscure replication error message
>Description: Replication stopped on our MySQL 4.0.1-alpha distribution with the error: Error executing query '%': got error 127 from table handler. (This may not be the exact text). Note the '%', which is what this bug report is referring to. Presumably the text of the query should be there. >How-To-Repeat: Unclear. Perhaps cause table corruption on master? >Fix: >Submitter-Id: >Originator:[EMAIL PROTECTED] >Organization: NewsNow Publishing Ltd >MySQL support: none >Synopsis: Obscure error message during replication >Severity: non-critical >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-4.0.1-alpha (Source distribution) >Environment: Gigabyte GS-SR101 server, 2xPIII 1GHz processors, 2G RAM, IDE hardware RAID (Promise RAID controller). Debian Linux 'testing' distribution with custom-compiled 2.4.18 kernel. System: Linux pilger 2.4.18 #1 SMP Mon Mar 25 11:16:14 GMT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 18 11:08 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x1 root root 1153816 Mar 24 17:00 /lib/libc-2.2.5.so -rw-r--r--1 root root 2391274 Mar 24 17:00 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 24 17:01 /usr/lib/libc.so -rw-r--r--1 root root 716080 Jan 13 20:06 /usr/lib/libc-client.so.2001 Configure command: ./configure --prefix=/usr/local/mysql-3.23.49 --enable-assembler --with-raid --with-unix-socket-path=/tmp --with-innodb --with-libwrap --with-tcp-port=3306 --with-unix-socket-path=/var/run/mysql/mysqld.sock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help with mysql.sock error
I am having trouble with mysql... i keep getting "ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" I uninstalled and reinstalled MySQL rpm (RH 7.1). I originally I was able to connect to MySQL server via command line client, but could not connect via web page with the error "mysql.sock (111)". - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Sock error
Thank you everyone who helped :o) I got MySQL up and running... as it turned out it was a permissions problem... go figure.. i hoped i left that all behind with NT :o) -Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Error
i had the same problem chown -R mysql.mysql /var/lib/mysql and i created a symlink from /tmp to the /var/lib/mysql/mysql.sock and restarted mysql and wammo it worked.. I used this on one computer on the advice for Error 2002: ... ... mysql.sock(2) error. On Tue, 2001-09-11 at 08:53, Mike wrote: > Hi All > > After upgrading to 42 I get the Error 2002 cannot connect though socket > /tmp/mysql.sock (111) > But using my Win client I can connect and run commands and my Web site > connects and is running fine. > > Using RH 7.0 latest MySQL build .42 > > > M;) > > MySQL Database Enlightenment > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database Design
i got a really good book on MySQL, it does not have much in the way of troubleshooting, but a lot on use. Title: MySQL Author: Paul DuBois ISBN: 0-7357-0921-1 Publisher: New Riders - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and Perl
In web development PHP is very easy to use and best of all very well documented. I have to aggress with Lezz Giles on using PHP over perl for MySQL backed webpages.. The learning curve on PHP is far easier than perl.. I use both for various reasons..I tend to use perl of CGI apps that php cant do or commandline linux administration stuff.. check out the PHP documentation at http://www.php.net/ PHP has built in functions that will dump an entire row into an array (this works with CSV and SQL databases).well there is my 2 cents worth on PHP+MySQL On Tue, 2001-09-11 at 12:27, Lezz Giles wrote: > PHP, Perl, Python, etc are all good ways to generate web pages, > and they all get on with MySQL perfectly well (OK, I've only used > Perl, but I've heard a lot about PHP and a little about Python). My > personal input re Perl v. PHP is that PHP is great for people starting > CGI stuff if they know HTML first - it lets you create web pages > and embed bits of programs. Perl, on the other hand, is better if > you know programming first, or if you want complete flexibility. > Also note that the Perl DBI interface means that it is relatively simple > to move from one database engine to another, but PHP only supports > a limited number of database engines. > > Lezz Giles > > - Original Message - > From: "Nilesh Parmar" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, September 10, 2001 8:13 PM > Subject: MySQL and Perl > > > > Hi > > I just wanted to know how well MySQL gels with Perl, when compared to > MySQL > > and PHP. > > any ideas ?? > > regards > > Nilesh > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Designing a recovery plan for MySQL
I got an idea and thought i would ask around about a recovery plan about databases? Currently all i do is dump the databases on to a share on an NT box thru a SMB share from the RH 7.1 Linux DB server. The NT box is a Compaq CL1850 which is 2 Compaq 1850 server with a shared SCSI array. The NT box is backed up on a 6 hour interval. I was just wondering any other ideas. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.sock error
the sock file is in the /usr/lib/mysql dir i created a symlink of the file from there to /tmp On Thu, 2001-09-13 at 14:32, Nick Torenvliet wrote: > > I'm trying to start my first ever installation of mysql, so I've installed > the tar.gz (3.23.42 on Linux) and I've run ./configure from /mysqlhome when > I try to star the server I get the following message > > Can't connect ot local MySQL server through socket '/tmp/mysql.sock' > > When I do a find I can't find the mysql.sock file anywhere on my server. > > Any suggestions? > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie optimization question
Hi. I'm new to database optimization and I have a couple of questions. I have a table like this: +++-+-+ | id | fullname | email | user_id | +++-+-+ Where fullname and email are varchar(100) and user_id is a non nullable foreign key. I have indices on every column. InnoDB engine. * Question 1: How can I optimize the case where I filter on one key but sort on another? This is fast: SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY fullname LIMIT 10; But this is slow: SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY email LIMIT 10; EXPLAIN tells me that the optimizer is using filesort for the second but not the first (which makes sense.) * Question 2: Why does introducing an extra WHERE clause make things slower? If I do this: SELECT * FROM contacts WHERE fullname LIKE "j%" AND user_id=1 ORDER BY fullname LIMIT 10; The results come back several orders of magnitude slower. This is despite the facts that: A) The results are the same for this query as the one without the test for user_id and B) About 95% of the records of in the table have user_id=1 Any insight will be greatly appreciated. Thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie optimization question
Thanks for the replies. The database is basically read-only at the moment, so OPTIMIZE TABLE didn't do anything. When I force the key to be fullname for the second problem, it runs even worse. It's not practical to create an additional email,fullname index because in my app I actually have 3! combinations of filters and sort orders. Adam On Apr 20, Alexey Polyakov wrote: > On 4/20/06, Adam Wolff <[EMAIL PROTECTED]> wrote: > > > How can I optimize the case where I filter on one key but sort on another? > > This is fast: > >SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY fullname LIMIT > > 10; > > > > But this is slow: > >SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY email LIMIT 10; > > > > EXPLAIN tells me that the optimizer is using filesort for the second > > but not the first (which makes sense.) > > Such things are pretty hard to optimize. If you have large number of > rows, forcing usage of (email) index for this query might help, as > engine will scan as many rows as required for satisfying limit. Also > having (email, fullname(1)) index might save a few cycles. > > > * Question 2: > > Why does introducing an extra WHERE clause make things slower? > > If I do this: > >SELECT * FROM contacts WHERE fullname LIKE "j%" AND user_id=1 > >ORDER BY fullname LIMIT 10; > > Probably because the engine is not using (fullname) index for this > query - optimizer sees 'const' ref for user_id, retrieves all rows > that have user_id=1 and then filters/sorts them all. > > -- > Alexey Polyakov > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
finding a record within a sort order
I have a table with a large number of rows. I have the primary key for a record within the table. The record I'm looking for looks like this: +++---+ | id | fullname | email | +++---+ | 123456 | Eldridge Price | [EMAIL PROTECTED] | +++---+ The only way I've found to find the ordinal number of a record within a sort is to use the count(*) subquery trick, like this: SELECT contacts.*, (SELECT COUNT(*) FROM contacts AS c2 WHERE contacts.fullname > c2.fullname OR (contacts.fullname = c2.fullname AND contacts.id > c2.id) ORDER BY fullname) AS ord FROM contacts WHERE contacts.id=123456; (I added the second condition to the WHERE subquery clause because there isn't a guarantee that fullname is unique.) This takes a substantial amount of time to run, even though I have indices on every column. EXPLAIN says: +++--+---+--+--+-+---++--+ | id | select_type| table| type | possible_keys| key | key_len | ref | rows | Extra| +++--+---+--+--+-+---++--+ | 1 | PRIMARY| contacts | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 2 | DEPENDENT SUBQUERY | c2 | index | PRIMARY,fullname | fullname | 101 | NULL | 195664 | Using where; Using index | +++--+---+--+--+-+---++--+ Although I must admit that I really don't know what this means. It seems like both const and index type queries should be fine. I also have a follow up: assuming that the enlightened folks on this list can help me optimize the above, I'm looking for a way to pass this information into an OFFSET value, as my end goal is retrieve a page from the table in the given sort order starting with the record whose id I have -- with a single SQL statement. Obviously, this isn't legal syntax: SELECT * FROM CONTACTS ORDER BY fullname LIMIT 10 OFFSET `SELECT contacts.*, (SELECT COUNT(*) FROM contacts AS c2 ... but that's conceptually what I'm looking for. Thanks! Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie optimization question
Hey! I figured out this one myself: On Apr 19, Adam Wolff wrote: > * Question 2: > Why does introducing an extra WHERE clause make things slower? > If I do this: > SELECT * FROM contacts WHERE fullname LIKE "j%" AND user_id=1 > ORDER BY fullname LIMIT 10; > It's because the index needs to be on user_id, fullname -- not just user_id -- in order to use the index. This second problem is pretty much solved now. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does this query takes a lot of time
On Apr 22, Philippe Poelvoorde wrote: > alter table s add index(login_name); > alter table c add index(recordID); To make this much faster, I think you may want: alter table s add index(recordID, login_name); alter table c add index(recordID); Because after the join, the engine can use the two-key index to filter the results. I may be wrong about this though -- I haven't tried it. A > 2006/4/22, abhishek jain <[EMAIL PROTECTED]>: > > Dear Friends, > > I have two table joined by the followng query, the problem is this simple > > query takes a lot of time greater than 10 mins depending on the number of > > records, Pl. help me find out the reason: > > Table 1: > > id_key primary and auto increment > > recordID varchar(100) > > login_name varchar(255) > > blah > > blah > > > > > > Table 2: > > id_key primary and auto increment > > recordID varchar(100) > > blah > > blah > > > > > > Query: > > SELECT count( * ) FROM table1 s, table2 c WHERE s.login_name = 'abhishek' > > and s.recordID=c.recordID; > > > > Do you it recordID to be a varchar ? > > -- > 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: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
I didn't look through your code very carefully. Have you confirmed (using EXPLAIN) that your select query is using the index? I don't much about the mysql optimizer, but it's possible that this query: > $query1="SELECT lat,lon from integer_test WHERE lat>$lat1 and lat<$lat2 > and lon>$lon1 and lon<$lon2"; Actually runs through the table four times instead of twice, and maybe can't even use the index for the whole query. Is the performance different if you use a subqery? Some thing like: SELECT id, lat, lon FROM integer_test WHERE lat>$lat1 and lat<$lat2 LEFT JOIN (SELECT id, lat, lon FROM integer_test AS i2 WHERE lon>$lon and lon<$lon ) ON integer_test.id = i2.id assuming that you have independent indicies on lat and lon. I didn't try this so the syntax may be wrong. You could also dispense with the id idea, but if so you should probably declare (lat,lon) as your primary key. A > and lon>$lon1 and lon<$lon2"; On Apr 23, Nick Hill wrote: > Hello > > I have been looking at planning the database strategy for openstreetmap > (http://www.openstreetmap.org). > > There are several data types stored in tables with longitude and latitude > columns. Select statements work by selecting > > where lat>$lat1 and lat<$lat2 and lon>$lon1 and lon<$lon2 > > I have made many empirical tests and have concluded: > > 1) I can improve performance by a factor of 2-2.5 by changing the double > lat/lon to an integer then selecting on an integer. > > 2) I have concluded that for each 10 fold increase in the number of records, > select queries take twice as long. For each doubling of the number of returned > records, there is a sqrt(2) increase in select query time. > > All this is assuming all relevant database information is in memory. > > > As the database grows, it would likely improve database performance by > splitting an individual table into several thousand tables using the file > system directory btree algorithm to effectively pre-select the data before the > query is handled to the MySQL engine. This is not a neat solution. A much > better way would be to improve the mysql index performance on very large > numbers of records. > > Given that there is such a strong relationship between the number of records > returned, and query time, I conclude that the whole index tree is matched for > every given number of root x records returned. If all records we are matching > are under a single node or under a small number of nodes in the index tree, > perhaps there is some way of telling the database engine to ignore the rest of > the index tree. > > Could this work, or am I misunderstanding how the index tree works? Are there > existing optimisations which can de-couple the relationship between number of > records and query time where the records I am selecting are within a small > range? > > > > Background information: > > We can boil all this down to a mathematical relationship where > query1 selects s number of records from r records dataset > and > query2 selects b number of records from c records dataset > > Tquery1 is time to execue query 1 and Tquery2 is time to execute query2. > > Tquery2=Tquery1 * sqrt(b/s) * (2^log(r/c)) + (b-s*CONST/15000)+CONST > Where for my processor, CONST is 0.03 > > > This can be simplified (loosing some accuracy) to: > > Tquery2=Tquery1 * sqrt(b/s) * (2^log(r/c) > > > > > Raw data for selects: > Creating a plan with 10 points and averaging over 25 queries > Points_per_tile Query_Time > 25600 0.118 > 25600 0.119 > 25600 0.119 > 25600 0.119 > 12800 0.069 > 64000.042 > 32000.026 > 16000.017 > 800 0.011 > 400 0.008 > 200 0.005 > 100 0.004 > 50 0.003 > Creating a plan with 100 points and averaging over 25 queries > Points_per_tile Query_Time > 25600 0.224 > 25600 0.223 > 25600 0.222 > 25600 0.223 > 12800 0.145 > 64000.093 > 32000.062 > 16000.043 > 800 0.029 > 400 0.020 > 200 0.015 > 100 0.011 > 50 0.008 > Creating a plan with 1000 points and averaging over 25 queries > Points_per_tile Query_Time > 25600 0.558 > 25600 0.548 > 25600 0.551 > 25600 0.551 > 12800 0.376 > 64000.257 > 32000.181 > 16000.125 > 800 0.087 > 400 0.062 > 200 0.044 > 100 0.031 > Creating a plan with 1 points and averaging over 25 queries > Points_per_tile Query_Time > 25600 2.422 > 25600 2.332 > 25600 2.493 > 25600 2.446 > 12800 1.769 > 64001.295 > 32000.866 > 16000.657 > 800 0.456 > 400 0.328 > 200 0.233 > 100
Re: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
Actually I think this should be an INNER JOIN -- not a LEFT JOIN. A On Apr 23, Adam Wolff wrote: > I didn't look through your code very carefully. Have you confirmed (using > EXPLAIN) that your select query is using the index? > > I don't much about the mysql optimizer, but it's possible that this query: > > $query1="SELECT lat,lon from integer_test WHERE lat>$lat1 and lat<$lat2 > > and lon>$lon1 and lon<$lon2"; > Actually runs through the table four times instead of twice, and maybe > can't even use the index for the whole query. > > Is the performance different if you use a subqery? Some thing like: > SELECT id, lat, lon FROM integer_test WHERE lat>$lat1 and lat<$lat2 > LEFT JOIN (SELECT id, lat, lon FROM integer_test AS i2 >WHERE lon>$lon and lon<$lon ) > ON integer_test.id = i2.id > > assuming that you have independent indicies on lat and lon. I didn't try > this so the syntax may be wrong. You could also dispense with the id idea, > but if so you should probably declare (lat,lon) as your primary key. > > A > > > and lon>$lon1 and lon<$lon2"; > > On Apr 23, Nick Hill wrote: > > > Hello > > > > I have been looking at planning the database strategy for openstreetmap > > (http://www.openstreetmap.org). > > > > There are several data types stored in tables with longitude and latitude > > columns. Select statements work by selecting > > > > where lat>$lat1 and lat<$lat2 and lon>$lon1 and lon<$lon2 > > > > I have made many empirical tests and have concluded: > > > > 1) I can improve performance by a factor of 2-2.5 by changing the double > > lat/lon to an integer then selecting on an integer. > > > > 2) I have concluded that for each 10 fold increase in the number of records, > > select queries take twice as long. For each doubling of the number of > > returned > > records, there is a sqrt(2) increase in select query time. > > > > All this is assuming all relevant database information is in memory. > > > > > > As the database grows, it would likely improve database performance by > > splitting an individual table into several thousand tables using the file > > system directory btree algorithm to effectively pre-select the data before > > the > > query is handled to the MySQL engine. This is not a neat solution. A much > > better way would be to improve the mysql index performance on very large > > numbers of records. > > > > Given that there is such a strong relationship between the number of records > > returned, and query time, I conclude that the whole index tree is matched > > for > > every given number of root x records returned. If all records we are > > matching > > are under a single node or under a small number of nodes in the index tree, > > perhaps there is some way of telling the database engine to ignore the rest > > of > > the index tree. > > > > Could this work, or am I misunderstanding how the index tree works? Are > > there > > existing optimisations which can de-couple the relationship between number > > of > > records and query time where the records I am selecting are within a small > > range? > > > > > > > > Background information: > > > > We can boil all this down to a mathematical relationship where > > query1 selects s number of records from r records dataset > > and > > query2 selects b number of records from c records dataset > > > > Tquery1 is time to execue query 1 and Tquery2 is time to execute query2. > > > > Tquery2=Tquery1 * sqrt(b/s) * (2^log(r/c)) + (b-s*CONST/15000)+CONST > > Where for my processor, CONST is 0.03 > > > > > > This can be simplified (loosing some accuracy) to: > > > > Tquery2=Tquery1 * sqrt(b/s) * (2^log(r/c) > > > > > > > > > > Raw data for selects: > > Creating a plan with 10 points and averaging over 25 queries > > Points_per_tile Query_Time > > 25600 0.118 > > 25600 0.119 > > 25600 0.119 > > 25600 0.119 > > 12800 0.069 > > 64000.042 > > 32000.026 > > 16000.017 > > 800 0.011 > > 400 0.008 > > 200 0.005 > > 100 0.004 > > 50 0.003 > > Creating a plan with 100 points and averaging over 25 queries > > Points_per_tile Query_Time > > 25600 0.224
Re: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
Well, I hadn't known about the spatial features of MySQL. If you're ok using vendor extensions then that definitely looks like the way to go: http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html A On Apr 24, Nick Hill wrote: > Hello Adam > > Adam Wolff wrote: > > Actually runs through the table four times instead of twice, and maybe > > can't even use the index for the whole query. > > Assuming my results are not typical of MySQL query times, this would explain > the sqrt() relationship of returned rows to query time. > > I have tried your suggestions of using a sub-query and have had trouble > getting the syntax valid. But on using explain, it seems that 4 bytes of the > index (either lat or lon) are being used and a brute force search on the index > for the other constraint. > > If the query is returning 25600 points from a 100m dataset, it is brute > seaching through 1.6m records in the second part of the index. > > If it were an option of creating 2 1.6M lists then looking for commonalities, > it may be faster to instead use 1 1.6m item list then brute force constraint > search. > > I have received suggestions to use spatial indexes, which I am looking into. > Alternatively, I could optimise queries by creating multiple slices of the > data set accross one axis then use a key on the other axis. MySQL 5.1 > partitioning scheme may help. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow query
I have a very simple table that looks like this: CREATE TABLE `contacts` ( `id` int(11) NOT NULL auto_increment, `fullname` varchar(100) default NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `user_id_2` (`user_id`,`fullname`), CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 It's a bit of a lopsided table in that of the 1,000,100 records in the db, 1,000,000 of them belong to user_id 1. But I wouldn't expect this to skew my results. I am writing a little paging server that retrieves pages of data using LIMIT and OFFSET. I'm really surprised by how slowly my queries are running on a relatively fast desktop machine. Records near the top of the list are fine: mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 0; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-+--+ | 371543 | Aaron Abbott | [EMAIL PROTECTED] | 1 | aaronab | ++--+-+-+--+ 1 row in set (0.03 sec) But as I move down the list, queries run slower and slower: mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 10; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-+--+ | 726543 | Benny Abbott | [EMAIL PROTECTED] | 1 | bennyab | ++--+-+-+--+ 1 row in set (2.94 sec) mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 50; ++---+--+-+--+ | id | fullname | email| user_id | nickname | ++---+--+-+--+ | 309543 | Jimmie Abbott | [EMAIL PROTECTED] | 1 | jimmieab | ++---+--+-+--+ 1 row in set (12.75 sec) EXPLAIN says: ++-+--+--+---+---+-+---++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+---+-+---++-+ | 1 | SIMPLE | contacts | ref | user_id,user_id_2 | user_id_2 | 4 | const | 506222 | Using where | ++-+--+--+---+---+-+---++-+ In other words, it *is* using an index for this query. Anyone have any advice for me? Thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query
Thanks for the response, Dan. I did try ORDER BY on the table. Didn't help -- I presume because the query is using an index. Unfortunately, the point of my current development is to show searches against millions of contacts, so the suggestion about working with other user_ids isn't too practical. I will look into increasing the size of my data cache. I guess what surprises me is that I thought that the index was stored as a BTree in sort order. I'm pretty bad with big-O, but I thought this would suggest O log N performance to find a given offset within the index. A On May 10, Dan Buettner wrote: > I would expect the problem to be that the further down in the data you go by > using OFFSET, the more records the mysql server has to scan in order to get to > what you want. This will produce a fairly linear slowdown the further in you > go - it just takes time to check through 1,000,000 matching records. > Especially on desktop grade hardware where you probably haven't got the > fastest disk subsystem. > > I think in this case your slow searches may be a result of the heavy bias in > your data toward user_id 1. Try your search on some of your other user_ids > and see. With so many records for the same user_id, your search for that > user_id is necessitating something pretty close to a table scan, even though > it's hitting an index. > > Some suggestions would be to increase the size of your data cache, so that > after your first queries, the data (or more of it) is in memory. Assuming > you'll be deploying on server hardware, a faster disk system should help quite > a bit too. Memory caches on hardware RAID systems can help with this kind of > thing too. > > You might also try > ALTER TABLE contacts ORDER BY user_id, fullname > to get your data sorted into the same order you're looking through it, though > it may well affect other queries you need to run against the same data. I'm > not certain whether you can ORDER BY more than one column: > http://dev.mysql.com/doc/refman/5.0/en/alter-table.html > Also note that as you add or delete rows the table does not stay in order. > > Hope this helps! > > Dan > > > Adam Wolff wrote: > > I have a very simple table that looks like this: > > CREATE TABLE `contacts` ( > > `id` int(11) NOT NULL auto_increment, > > `fullname` varchar(100) default NULL, > > `user_id` int(11) NOT NULL, > > PRIMARY KEY (`id`), > > KEY `user_id` (`user_id`), > > KEY `user_id_2` (`user_id`,`fullname`), > > CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` > > (`id`) > > ENGINE=InnoDB DEFAULT CHARSET=utf8 > > > > > > It's a bit of a lopsided table in that of the 1,000,100 records in the db, > > 1,000,000 of them belong to user_id 1. But I wouldn't expect this to > > skew my results. > > > > I am writing a little paging server that retrieves pages of data using > > LIMIT and OFFSET. > > > > I'm really surprised by how slowly my queries are running on a > > relatively fast desktop machine. Records near the top of the list are > > fine: > > mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname > > LIMIT 1 OFFSET 0; > > ++--+-+-+--+ > > > > | id | fullname | email | user_id | nickname | > > ++--+-+-+--+ > > > > | 371543 | Aaron Abbott | [EMAIL PROTECTED] | 1 | aaronab | > > ++--+-+-+--+ > > > > 1 row in set (0.03 sec) > > > > But as I move down the list, queries run slower and slower: > > mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname > > LIMIT 1 OFFSET 10; > > ++--+-+-+--+ > > > > | id | fullname | email | user_id | nickname | > > ++--+-+-+--+ > > > > | 726543 | Benny Abbott | [EMAIL PROTECTED] | 1 | bennyab | > > ++--+-+-+--+ > > > > 1 row in set (2.94 sec) > > > > mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname > > LIMIT 1 OFFSET 50; > > ++---+--+-+--+ > > > > | id | fullname | email| user_id | nickname > > | > > ++-
Re: Sum of counts
You could you use UNION to make this all execute in a single query. On 5/10/06, Rhino <[EMAIL PROTECTED]> wrote: Hi Chris, Joerg, and everyone else following this discussion, Joerg, you are correct; the best way to sum the tables is individually and then add the sums together with program logic of some kind, such as might be found in a script or application program or stored procedure. I'm afraid I jumped in and gave correct but irrelevant information. I saw that Chris's query lacked joining conditions so I explained why they were needed and how to write them. Unfortunately, this was premature: I should have thought about the basic problem more carefully first. Joerg, you are absolutely right: if one table contains students and another contains teachers, you don't count the number of people in the school by JOINING the tables together. You count the people in each table separately and add the two sums together. I don't know why that didn't come to me when I read Chris's question but it didn't. My apologies to all for wasting your time with an inappropriate solution. I'll try not to do that again! -- Rhino - Original Message - From: "Joerg Bruehe" <[EMAIL PROTECTED]> To: "Chris Sansom" <[EMAIL PROTECTED]> Cc: "Rhino" <[EMAIL PROTECTED]>; "MySQL List" Sent: Wednesday, May 10, 2006 12:04 PM Subject: Re: Sum of counts > Hi Chris, all, > > > Re-inserting Chris' original question: > | I want to get a total of entries from four tables which all match a > | particular id. The result for the id I'm testing (21) should be 233. > | In my naivety, I thought something like this would work: > | > | select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id) > | from table_a as a, table_b as b, table_c as c, table_d as d > | where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 > > > IMO, this is simply no task for a join, > probably not a task for any single SQL statement. > > The easiest way is to have four separate "SELECT count(*) FROM table_?" > with the '?' replaced by 'a' .. 'd'. > > > More explanations below: > > > Chris Sansom wrote: >> At 13:28 -0400 9/5/06, Rhino wrote: >>> The reason you are getting so many rows has nothing to do with the way >>> you are using the count(*) function and adding the different count() >>> results together. The problem is that you are doing your joins >>> incorrectly... In your case, I think you need to change the original >>> query to this: >>> >>>select count(a.id) + count(b.id) + count(c.id) + count(d.id) >>>from table_a as a, table_b as b, table_c as c, table_d as d >>>where a.id = b.id >>>and b.id = c.id >>>and c.id = d.id >>>and a.id = 21 >>>and b.id = 21 >>>and c.id = 21 >>>and d.id = 21 > > First, the transitive equality on the 4 "id" columns (first 3 conditions) > together with one restriction to 21 (say, on "a.id") > has no different effect than the 4 conditions "= 21", > in mathematical view 3 of these 7 conditions can be dropped. > (Not "any 3", but several different combinations.) > > But that is not the cause of the problem - this is the join approach: > Remember that a join does a cartesian product, this is in no way helpful > to the solution of your task! > > > Let us construct a minimized example: Just two tables, each with three > rows, all having that magic value 21: > > Table aTable b > id cntid cnt > 21 1 21 4 > 21 2 21 5 > 21 3 21 6 > > Doing a natural join on the "id" column will yield 9 rows: > > a.id a.cnt b.id b.cnt > 211 214 > 211 215 > 211 216 > 212 214 > 212 215 > 212 216 > 213 214 > 213 215 > 213 216 > > Summing "a.id" and "b.id" results in 18, where the correct value is 6. > > >> >> Hi Rhino >> >> Many thanks for the very full and frank response, but sadly it didn't >> work. I do understand exactly what you said, and I even took it further, >> adding in: >> >> and a.id = c.id >> and a.id = d.id >> and b.id = d.id >> >> ...so that every table is thus related to every other one, but I'm >> /still/ getting that damned eight and a half million instead of the 233 I >> expect! > > See above - > demanding all 4 columns to be equal to 21 > is equivalent to demanding one is 21, and all are equal, > and also equivalent to some other combinations of conditions. > > If you have a mathematical education, apply your knowledge of > "transitivity" to the problem. > >> >> I'm baffled by this, though the version I did with subqueries works very >> nicely (and it's simple enough to do four separate queries and add them >> together in the script for the older MySQL). > > Frankly spoken: This is the way to go! > > > From your problem description, there seems to be no connection between the > tables that would warrant joining them. > > For a very coarse analogy: > If you have separate tables for the teachers and the pupils of a school, > and wa
MySQL commercial licence
Folks, We have a commercial product that uses mysql 4.0.24. We bought a commercial licence for this version. We want to upgrade to 5.0.21. I spoke with the MySQL saleswoman this morning and she says they don't do upgrades and want another entire licence fee for v5.x. Does anyone else have experience of this? Upgrading commercial licences? What's the story? Thanks - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL commercial licence
Checking. I don't think the customer bought the "network" version. Apparently its MySQL Pro Licence V4. It's a bit rich not to offer upgrades at a discount IMO. Adam -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: 12 May 2006 11:52 To: Adam Lipscombe Cc: 'MySQL List' Subject: Re: MySQL commercial licence Adam Lipscombe wrote: >Folks, > > >We have a commercial product that uses mysql 4.0.24. We bought a >commercial licence for this version. We want to upgrade to 5.0.21. > >I spoke with the MySQL saleswoman this morning and she says they don't >do upgrades and want another entire licence fee for v5.x. > > >Does anyone else have experience of this? Upgrading commercial >licences? What's the story? > > >Thanks - Adam > > > > Hi Adam, That is a very interesting development. Is your license under the MySQL Network? I am considering using this for a number of new servers and would have to rethink my strategy if this is the case. I notice their Network FAQ has Q: Does MySQL Network include MySQL 5.0? A: Yes, MySQL Network includes all updates and upgrades including MySQL 5.0 It is also very interesting that the Network product automatically includes a GPL covered product rather than a commercial one by default. How many people actually check on that before purchase? Are they aware they have purchased a GPL product and are now obligated under that license to GPL their distributed products? Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Drop Index if Exists
Folks, Does this work in MYSQL 5? I tried "DROP INDEX [NAME] IF EXISTS;" and got an error "check your syntax". Thanks - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop Index if Exists
Thanks What I am getting at is: does the "IF EXISTS" qualifier work in this context? I have an index that is present in some DB's but not in others. I want to run a generic script to upgrade them all. I don't want the script to stop if the index is not present. Thanks - Adam Visolve DB TEAM wrote: Hello Adam,This is the exact syntax to drop the index.DROP INDEX index_name ON tbl_nameThanksVisolve DB Team . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
java mysql alias is being displayed blank?
Does anyone know why a mysql alias would not display in the return a result? When I try to access say the first name with it's alias f_name, it is blank? , but it works for its column name first_name? This becomes more of a problem with subselects because how does one alias it? Thank you, Adam JSP: User ID First Name Last Name User Name Phone Number phone_name login Status Number of Contacts Servlet sql: sql = "SELECT " + "u.id as user_id, " + "u.first_name as fname, " + "u.last_name, " + "u.user_name, " + "SUBSTRING(d.contact_url, 5,10) as phone_number, /* phone number */ " + "pt.phone_name, " + "a.login_status, " + "COUNT(c.user_id) as contacts " + "FROM " + "device as d, " + "client_relations as cr, " + "phone_types as pt, " + "availability as a," + "usr as u " + "LEFT JOIN " + "contact AS c " + "ON " + "( u.id = c.user_id) " + "WHERE " + "u.id = d.user_id " + "AND " + "d.client_relation_id = cr.id " + "AND " + "cr.phone_type_id = pt.id " + "AND " + "u.id = a.user_id " + "AND " + "a.device_id = d.id " + "AND " + "a.login_status > 3 " + "GROUP BY " + "u.id " + "ORDER BY " + "a.id LIMIT 2;"; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sarge problems with MySQL and DBI / DBD::mysql
Oliver Elphick wrote: >which returns 0 rows, works correctly in mysql. But when it is passed >through this statement in Perl: > >my @row = $dbh->selectrow_array($sql) or >&failed(__FILE__, __LINE__, > "Failed to run the sql statement with error > $DBI::errstr"); > >How can I debug or fix this problem, please? > > Instead of doing this, you might want to try setting RaiseError (see man DBI) which would cause DBI to detect errors and throw exceptions. Alternatively, you might want to use $h->err to check for errors. - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locks acquired with get_lock() persisting, even after MySQL thread has exited
Description:
We have distributed applications which make extensive use of the
get_lock() function
to acquire a system-wide lock. Occasionally, all copies of a given
application
block attempting to acquire a lock on the same lock string. Using
is_used_lock() reports
that the lock is held by a particular thread ID.
In one instance, this thread ID did not exist.
In another instance, it did exist, but after killing that thread,
is_used_lock()
still reported the same (now non-existent) thread as holding the lock.
Here is an example:
output from 'mysql newsnow -unewsnow -e "select is_used_lock('1 1
11')"':
is_used_lock('1 1 11')
166
mysqladmin processlist output at same time - note that the above thread
ID is not listed:
+-+--+--+-++---++--+
| Id | User| Host| db | Command
| Time| State | Info|
+-+--+--+-++---++--+
| 29 | DELAYED | | newsnow | Delayed insert
| 0 | Waiting for INSERT | |
| 36 | DELAYED | | newsnow | Delayed insert
| 0 | Waiting for INSERT | |
| 66 | DELAYED | | newsnow | Delayed insert
| 0 | Waiting for INSERT | |
| 152 | DELAYED | | newsnow | Delayed insert
| 0 | Waiting for INSERT | |
| 1270| repl| gilligan.webcluster.newsnow.co.uk:55619
| | Binlog Dump| 49399 | Has sentall
binlog to slave; waiting for binlog to be updated |
|
| 1931| newsnow | fw-2.webcluster.newsnow.co.uk:34872
| newsnow | Sleep | 21 | | |
| 303482 | newsnow | king.webcluster.newsnow.co.uk:40739
| newsnow | Query | 0 | Locked | /*
SouAutFre */ select distinct(substring(FDate, 1, 10))
as _Date, dayofweek(FDate)as _DayfromRef |
| 764780 | champ | bush.newsnow.net:55504 | newsnow
| Sleep | 2 | | |
| 764781 | champ | bush.newsnow.net:55505 | newsnow
| Sleep | 3 | | |
| 764782 | champ | bush.newsnow.net:55506 | newsnow
| Sleep | 2 | | |
| 770544 | newsnow | fw-1.webcluster.newsnow.co.uk:46897
| newsnow | Sleep | 2 | | |
| 993861 | newsnow | ford.webcluster.newsnow.co.uk:42566
| newsnow | Sleep | 3 | | |
| 993865 | newsnow | ford.webcluster.newsnow.co.uk:42567
| newsnow | Sleep | 4 | | |
| 994002 | newsnow | simpson.webcluster.newsnow.co.uk:36279
| newsnow | Sleep | 5 | | |
| 994021 | newsnow | db-1.webcluster.newsnow.co.uk:46345
| newsnow | Sleep | 0 | | |
| 994023 | newsnow | db-1.webcluster.newsnow.co.uk:46346
| newsnow | Sleep | 3 | | |
| 994026 | newsnow | db-2.webcluster.newsnow.co.uk:45053
| newsnow | Sleep | 3 | | |
| 994092 | newsnow | fw-2.webcluster.newsnow.co.uk:42534
| newsnow | Sleep | 5 | | |
| 994116 | newsnow | db-1.webcluster.newsnow.co.uk:46347
| newsnow | Sleep | 4 | | |
| 994475 | newsnow | king.webcluster.newsnow.co.uk:40726
| newsnow | Sleep | 1 | | |
| 999805 | newsnow | www-2.webcluster.newsnow.co.uk:37533
| newsnow | Sleep | 4 | | |
| 1000512 | newsnow | king.webcluster.newsnow.co.uk:41318
| newsnow | Sleep | 3 | | |
| 1055442 | newsnow | db-2.webcluster.newsnow.co.uk:48367
Re: MySQL Administrator on 10.3 Server -HELP
Did you drag it from a network drive, or copy in some other way? If so, that may be the problem. I haven't used the MySQL Administrator, but am pretty well versed in application troubleshooting. Send me an email privately if you need some direct help. Adam. -- Adam Randall <[EMAIL PROTECTED]> Senior Web Architect Stork Avenue, Inc. http://www.storkavenue.com/ (800) 861-5437 AIM/iChat: adamstorkave On Oct 5, 2005, at 6:04 PM, Andrew stolarz wrote: Hello List, I have installed the mysql database on the osx 10.3 server machine. along with the myodbc connector. I also installed the administrator module, by simply draging it over to the application folder like it says. When I go to launce the application, it does nothing. I tried restarting the machine, and am logging with the administrator account. Any Ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backwards compatibility when exporting M
Folks I am trialling MySQL 5 before we all upgrade, however others need to import my dump files into their existing MySQL4.x databases MySQL 5 mysqldump seems exports BIT fields as true = '', false = '\0'. When that dump file is imported into MySQL 4 these value are not interpreted correctly. MySQL4.x does not have a real BIT type , rather it uses TINYINT(1). Both '' and '\0' seem to be interpreted as "false", and the relevant TINYINT(1) field is set to 0. So far that has meant that I have to hand-edit my dump file to convert '' to 1, '\0' to 0. Does anyone know a way around this? TIA - Adam Lipscombe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump line endings
Folks By default mysqldump uses linefeed as the line ending. This results in the data looking odd in a Windows text editor, as they expect \r\n. I have experimented with the --tab and --line-terminated-by options, but this results in a separate txt file for each table. Is there any way to persuade mysqldump to produce one file (with "insert into" statements) and also use Windows style line endings? TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump INSERT statements (Was Mysqldump line endings)
>> (--result-file) option to save your output but use the > output redirector >> to map the output of mysqldump to a file you specify, you will get the >>CRLF line endings you seek. Many thanks. One more thing: by default mysqldump uses --extended-insert=TRUE. This results in all the VALUES data being on one line. IF the table has much data this produces very long lines when viewed in a text editor. (WordPad crashes!) If --extended-insert=FALSE then multiple INSERT statements are used. IS there any way to have 1 INSERT statement with each subsequent data line on a separate line? E.g. : INSERT INTO t1 VALUES (1,2,3), (1,4,5), (1,6,7); TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting the data directory.
Hi, There's a mysqld startup option --datadir=/path/to/data which you can use to alter your data directory. Simply adjust your startup scripts to suite. Regards, -Adam On 1/31/06, Scott Johnson <[EMAIL PROTECTED]> wrote: > Hi All, > > I am trying to find out how to change the location of the data files. I have > not yet found a generic entry for the my.conf file to set a data and log > location. > > Can I move the exiting data directory form the mySQL tree and put a soft > link to a new location on another partition? > > thanks, > > Scott Johnson > [EMAIL PROTECTED] > Tel.: (514) 917-4922 > Fax: (514) 673-0011 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Adam Alkins http://www.rasadam.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with importing the british pound (£) an d euro (€) sign
Folks, I have a mysql 5.0 db with the following char sets: mysql> show variables like '%char%'; +--+--+ | Variable_name| Value| +--+--+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\MySQL\share\charsets\ | +--+--+ I have a column that stores currency symbols - two of which are the British pound (£) and euro (€) sign. I can I export via mysqldump no with problem - the "£" sign appears in the export file OK, although the euro is converted into an odd looking set of chars But when I import using the command line client like this: "mysql -u -p < datadump.sql", the £ sign and euro sign get changed into "£" and "€" respectively. I have tried forcing the encoding by using "mysql --default-character-set=latin1 -u -p < datadump.sql" but this produces the same results. Any ideas? TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Solaris 10 bug with mysql_install_db
>Description: running mysql_install_db does not correctly fill grant tables >How-To-Repeat: run mysql_install_db; attempt to connect, fail. >Fix: Edit #!/bin/sh to use **ANY OTHER SOLARIS SHELL** >Submitter-Id: [EMAIL PROTECTED] >Originator:Adam Thompson >Organization: athompso.net >MySQL support: none >Synopsis: Solaris 10 /bin/sh inadequate to run mysql_install_db >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-5.1.30 (MySQL Community Server (GPL)) >Server: /bin/mysqladmin Ver 8.42 Distrib 5.1.30, for sun-solaris2.10 on sparc Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.1.30 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 49 sec Threads: 1 Questions: 3 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.61 >C compiler: >C++ compiler: CC: Sun C++ 5.9 SunOS_sparc Patch 124863-08 2008/10/16 >Environment: System: SunOS e450 5.10 Generic_137137-09 sun4u sparc SUNW,Ultra-4 Architecture: sun4 Some paths: /bin/perl /usr/xpg4/bin/make /usr/sfw/bin/gmake /usr/sfw/bin/gcc GCC: Reading specs from /usr/sfw/lib/gcc/sparc-sun-solaris2.10/3.4.3/specs Configured with: /sfw10/builds/build/sfw10-patch/usr/src/cmd/gcc/gcc-3.4.3/configure --prefix=/usr/sfw --with-as=/usr/ccs/bin/as --without-gnu-as --with-ld=/usr/ccs/bin/ld --without-gnu-ld --enable-languages=c,c++ --enable-shared Thread model: posix gcc version 3.4.3 (csl-sol210-3_4-branch+sol_rpath) Compilation info (call): CC='/opt/studio12/SUNWspro/bin/cc' CFLAGS='-g -xO2 -Xa -xstrconst -mt -D_FORTEC_ -m64' CXX='/opt/studio12/SUNWspro/bin/CC' CXXFLAGS='-g -xO2 -noex -mt -D_FORTEC_ -m64' LDFLAGS='-m64' ASFLAGS='-m64' Compilation info (used): CC='/opt/studio12/SUNWspro/bin/cc' CFLAGS=' -g -xO2 -Xa -xstrconst -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T -DUNIV_SOLARIS' CXX='/opt/studio12/SUNWspro/bin/CC' CXXFLAGS=' -g -xO2 -noex -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T' LDFLAGS='-m64 ' ASFLAGS='-m64' LIBC: lrwxrwxrwx 1 root root 9 Dec 1 01:40 /lib/libc.so -> libc.so.1 -rwxr-xr-x 1 root bin 1629616 Oct 3 16:37 /lib/libc.so.1 lrwxrwxrwx 1 root root 19 Dec 1 01:39 /usr/lib/libc.so -> ../../lib/libc.so.1 lrwxrwxrwx 1 root root 19 Dec 1 01:39 /usr/lib/libc.so.1 -> ../../lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Server (GPL)' '--with-server-suffix=' '--enable-thread-safe-client' '--enable-local-infile' '--with-mysqld-libs=-lmtmalloc' '--with-named-z-libs=no' '--with-pic' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static' '--with-zlib-dir=bundled' '--with-big-tables' '--with-ssl' '--with-readline' '--with-embedded-server' '--with-partition' '--with-innodb' '--without-ndbcluster' '--with-archive-storage-engine' '--with-blackhole-storage-engine' '--with-csv-storage-engine' '--without-example-storage-engine' '--with-federated-storage-engine' '--with-extra-charsets=complex' 'CC=/opt/studio12/SUNWspro/bin/cc' 'CFLAGS=-g -xO2 -Xa -xstrconst -mt -D_FORTEC_ -m64' 'LDFLAGS=-m64' 'CXX=/opt/studio12/SUNWspro/bin/CC' 'CXXFLAGS=-g -xO2 -noex -mt -D_FORTEC_ -m64' -- -Adam Thompson <[EMAIL PROTECTED]>
restoring mysql db doesn't restore user passwords
I'm running Fedora 11 i386 with Mysql 5.1.32. I dumped my mysql databases with: mysqldump -u root -pxxx --lock-all-tables --all-databases > /root/mysql-backup/all-db.sql and then wiped the operating system and reinstalled. Then I ran mysqladmin -u root password to set my root password. Then I needed to restore all of the databases so I ran: mysql --verbose -u root -p < /root/mysql-backup/all-db.sql and that seems to have restored everything, there is the correct data in my databases, however, users can not connect. For example, this user was working prior to the reinstall: mysql -u adam -px ERROR 1045 (28000): Access denied for user 'adam'@'localhost' (using password: YES) but in the mysql.user table, adam exists and has his password set from the dump file: mysql> select User,Password from mysql.user where user = 'adam'; +--+--+ | User | Password | +--+--+ | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | +--+--+ 3 rows in set (0.06 sec) It is the same with all of the other users also. They have their password set from the dump file, but they can't log in with what their password was either. Any ideas on how to get the passwords restored? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: restoring mysql db doesn't restore user passwords
you're a genius! I had old_passwords=1 in my.cnf, changed it to 0, restarted mysql, and then the users worked like a charm. thanks! Little, Timothy wrote: My theory would be that it's an OLD-PASSWORDS issue. It would seem that you might have used the old_passwords=1 in your original configuration my.cnf but it's not in your new configuration file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
help with group by
I've written a helpdesk ticket problem and am working on the statistics module. I'm having problems with group by. For instance, I want to get the count of the number of different problem types, by how many were solved by each person. This is my statement: mysql> select distinct accepted_by, problem_type, count(*) from form where ((problem_type is not NULL) && (problem_type != 'Test') && (accepted_by is not null)) group by problem_type; +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | awilliam| Computer Hardware | 13 | | awilliam| Computer Peripheral | 16 | | awilliam| Computer Software | 138 | | awilliam| Delete User |4 | | smccoy | Networking | 17 | | awilliam| New User|6 | | jomiles | Printer | 21 | | awilliam| Server | 47 | | sokolsky| Telephone |6 | +-+-+--+ 9 rows in set (0.00 sec) But it is leaving out two of the support staff, and smccoy and jomiles have also solved Computer Software problems, but it's only showing awilliam as solving Computer Software problems. I think its just showing accepted_by's values by first occurrence of accepted_by on problem_type. Here's the two users its not even showing: mysql> select accepted_by, problem_type, count(*) from form where (accepted_by = 'ehynum') group by problem_type; +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | ehynum | Computer Peripheral |1 | | ehynum | Computer Software |5 | | ehynum | Telephone |1 | +-+-+--+ 3 rows in set (0.00 sec) mysql> select accepted_by, problem_type, count(*) from form where (accepted_by = 'dbrooks') group by problem_type; +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | dbrooks | Computer Peripheral |2 | | dbrooks | Computer Software |9 | | dbrooks | Networking |2 | | dbrooks | Printer |3 | | dbrooks | Server |3 | +-+-+--+ 5 rows in set (0.01 sec) but what I really need is an SQL statement that would return this, but I'm at a loss as to what that would be: +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | awilliam| Computer Hardware |6 | | awilliam| Computer Peripheral |7 | | awilliam| Computer Software | 64 | | awilliam| Delete User |4 | | awilliam| Networking | 10 | | awilliam| New User|5 | | awilliam| Printer |4 | | awilliam| Server | 33 | | awilliam| Telephone |1 | | awilliam| Test|1 | | dbrooks | Computer Peripheral |2 | | dbrooks | Computer Software |9 | | dbrooks | Networking |2 | | dbrooks | Printer |3 | | dbrooks | Server |3 | | ehynum | Computer Peripheral |1 | | ehynum | Computer Software |5 | | ehynum | Telephone |1 | | jomiles | Computer Hardware |5 | | jomiles | Computer Peripheral |6 | | jomiles | Computer Software | 44 | | jomiles | Networking |1 | | jomiles | Printer | 12 | | jomiles | Server |7 | | smccoy | Computer Hardware |2 | | smccoy | Computer Software | 15 | | smccoy | Networking |4 | | smccoy | New User|1 | | smccoy | Printer |2 | | smccoy | Server |4 | | sokolsky| Computer Software |1 | | sokolsky| Telephone |4 | +-+-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help with group by
works perfectly, i didn't know you could use multiple columns in the group by. thanks a bunch! Michael Dykman wrote: try this: select accepted_by, problem_type, count(*) from form where problem_type is not NULL AND problem_type != 'Test' AND accepted_by is not null group by accepted_by, problem_type -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: opening a server to generalized queries but not "too" far
Sounds like you just want to GRANT access to specific tables (and with limited commands), which is exactly what MySQL's privilege system does. Refer to http://dev.mysql.com/doc/refman/5.1/en/grant.html <http://dev.mysql.com/doc/refman/5.1/en/grant.html>For example, you can grant only SELECT privileges to a specific table for a specific user. -- Adam Alkins || http://www.rasadam.com On 16 June 2010 14:17, Don Cohen wrote: > > This seems like a topic that must have been studied, but I'm having > trouble figuring out what to search for in Google, since the usual > discussion of sql injection is not what I'm looking for here. > If anyone knows of references that discuss the issue, I'd like to > see them. I'm also interested in answers for other RDBMS's, > and I imagine that details of implementation may matter, but my > immediate primary interest is mysql used from php. > > I want to allow web users to make a very wide variety of queries, but > limited to queries (no updates, redefinitions, etc), and limited to a > fixed set of tables - let's suppose one table with no joins, and > perhaps a few other restrictions. > > I propose to send queries of the following form from php to the DB: > select from fixedtable > where group by order by > The user gets to supply all of the 's. > So, as an example, I want the user to be able to do > select max(col1) from fixedtable group by col2 > > The question is what I have to prohibit in order to prevent either > updates or access to other tables, or perhaps other things that > I should be worried about but haven't yet thought of. > > So far I have identified at least one problem, which is subqueries > such as > select 1 from ... where exists (select 1 from othertable ...) > These can tell the attacker about other data he should not be able to > read. At the moment I plan to simply disallow inputs containing the > string "select" (case insensitive). Is there any way to get a select > statement to execute other statements, such as insert, delete, drop? > > I believe that ";" is not a problem because a single request from php > to mysql containing multiple statements will result in a syntax error. > If I subject the inputs to mysql_real_escape_string then the user will > not be able to use quotes, which will prevent use of string constants. > What more could an attacker do if I don't escape the inputs? > > Finally, suppose I want to limit access to the table to the rows > where col1=value1. If I just add that to what can an > attacker do to read other rows? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@adamalkins.com > >
Re: list rows with no recent updates
One option would be to add a column to the table with a last_updated timestamp. Everytime you update the row, update the last_updated field with the current timestamp. Therefore you could just query the timestamp column to get recently updated rows (or not so recently updated) as you please. -- Adam Alkins || http://www.rasadam.com On 14 June 2010 16:02, MadTh wrote: > Hi, > > > I ran a update command on around 2700 rows inside a mysql database table > which has around 3000 table rows to change the ( say) price of each item ( > with unique ID. unique product code). > > like: > > mysql> UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and > prod_code='a0071'; > Query OK, 1 row affected (0.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > > > > How can I list rows with no recent updates ( or the once where the above > updates were not done) or say with no updates in last 2 hours? > > > > > > Thank you. >
Re: opening a server to generalized queries but not "too" far
MySQL doesn't have row level permissions, but this is what VIEWS are for. If you only want access to specific rows, create a view with that subset of data. You can create a function (privilege bound) to create the view to make this more dynamic. If you want direct access to the database, then you will need multiple MySQL users. I don't quite get the purpose of what you're trying to do through a PHP script; I see little reason why you can't use some client level security to facilitate data access, especially if you're going to implement dynamic creation of users in the PHP script itself (which would be rather weak security imho). -- Adam Alkins || http://www.rasadam.com On 16 June 2010 14:48, Don Cohen wrote: > Adam Alkins writes: > > Sounds like you just want to GRANT access to specific tables (and with > > limited commands), which is exactly what MySQL's privilege system does. > > How about this part? > > > Finally, suppose I want to limit access to the table to the rows > > > where col1=value1. If I just add that to what can an > > > attacker do to read other rows? > > The http request I have in mind will be something like > https://server.foo.com?user=john&password=wxyz&;... > and the resulting query something like > select ... from table where user=john and ... > (I will first have verified the password.) > > It seems I'd have to create a separate mysql user for each user in > my table, and perhaps also a separate separate table (or view?) for > that user to be allowed to read. > I suppose the php process could create the table/view, create the > user, then connect as the user to execute the query, then remove the > user. > > Even if this turns out to be the best solution, I'm interested in > the answer to the original question. > >
Split string by regex
Hello, I'm working on a library OPAC system with books classified using the Library of Congress classification system. This takes the format of either one or two letters followed by some numbers, i.e. R272 or RA440 etc. What I want to do is split the field that holds this classification into two, one containing the letter portion and the other containing the number bit. So +---+ | Class | +---+ | R100 | +---+ | RA65 | +---+ | RP2 | +---+ Would become +++ | Class | Class2 | +++ | R| 100 | +++ | RA | 65 | +++ | RP |2 | +++ etc Could this be done in MySQL? I want to do something along the lines of set class2 = SUBSTRING_INDEX(class,'[A-Z]',-1) but I understand this is not possible. Any ideas? Regards Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Share To My Love x--)
Dear: I would like to share with you my recently shopping experience: I found a very excellent e-site, the above is the latest and most popular products. By their staff, understanding that they have stable supply channel, so the price of products is shock your eyeballs burst. Certainly I will not miss this opportunity, I received my ordered products 5 days later. If you are free, I suggest you can go to see:difsiz.com j--) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Messed up mysql-server, cant reinstall?!
I accidentally drop the database mysql. I now cannot restore tables to the database or do much of anything some details can be found here http://serverfault.com/questions/234321/i-accidently-dropped-the-mysql-db/234340#234340 after a few unsuccessful uninstalled/reinstalls i wrote whereis mysql and rm -rf every directory in that list. I ran `dpkg -P mysql-server`. `apt-get install mysql-server` saw "Stopping MySQL database server: mysqld.", ran `/etc/init.d/mysql start` and got the error "Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!". I wrote whereis mysql again and got NO DIRECTORIES. How do i properly reinstall this? I cant even restore my backups because of this. Yes i did all of this as root
Re: Seconds Behind the master 0 but in reality it's over 10 minutes or hours..
Dear List, I have a Master -> [ Slave 1, Slave 2 ] setup for years. Last week we've reinstalled the whole setup to: 5.1.58 from: 5.1.54. The situation is: the master says: (show master status) mysql-bin.87 | 974376716 slave says: (show slave status) Master_Log_File: mysql-bin.87 Read_Master_Log_Pos: 627246309 Exec_Master_Log_Pos: 627246309 Relay_Log_Space: 596009233 Seconds_Behind_Master: 0 In reality, it's NOT 0 seconds! it's delayet more than 10-20 minutes, some times more than 1 hours. Why? Is it a network problem? If i restart the slave, nothing happens. If I restart the master and the slave, it recognises the "delay" and start to read the logs again, but a few minutes or hours (or sometimes days later) it stops again. Is it a slave or a master problem? Network problems? What should I do? full outputs below: mysql> show master status; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.88 | 8078024 | | | +--+--+--+--+ 1 row in set (0.30 sec) mysql> mysql> show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.3.3.12 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.87 Read_Master_Log_Pos: 627246309 Relay_Log_File: tungsten-relay-bin.000213 Relay_Log_Pos: 541523464 Relay_Master_Log_File: mysql-bin.87 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 627246309 Relay_Log_Space: 596009233 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) Any ideas? I have the db under /var/db/mysql it shows: -rw-rw 1 mysql mysql 149765008 Sep 4 18:37 tungsten-relay-bin.000214 -rw-rw 1 mysql mysql 67 Sep 4 18:38 relay-log.info -rw-rw 1 mysql mysql 73 Sep 4 18:38 master.info but the date is: [root@tungsten /var/db/mysql]# date Sun Sep 4 19:11:13 CEST 2011 And it's delayed 1 hour. No data is transferred to the slave. It says: Secords behind master 0. What the hell is going on here? -- Adam PAPAI Grapes Communication Kft. http://www.grapes.hu E-mail: papai.a...@grapes.hu Phone: +36 30 33-55-735 (Hungary) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Seconds Behind the master 0 but in reality it's over 10 minutes or hours..
Alexis Guajardo wrote: Are the date time set the same on both servers? What upgrade process did you use? Alexis Guajardo Zaynka.com On Sep 4, 2011, at 12:09 PM, Andrej Pintar wrote: any warnings in the logs. timeouts. connects. had same thing... just can't remember what was it. -> never upgrade if the system works with no errors. did you test and simulate in the development environment? Hm. It seems we have sort of ethernet segment saturation problem. We have new network environment and the slave does not recognise that he has no connection to the master. I set the slave_net_timeout to 5 sec. It's working as an ASAP workaround but we're investigating the problems behind the network. Thanks for all. And you're right: Never upgrade it the system works... :( But we have to move our whole server infrastructure to another place (had to left the old ones there). Thanks again. -- Adam PAPAI Grapes Communication Kft. http://www.grapes.hu E-mail: papai.a...@grapes.hu Phone: +36 30 33-55-735 (Hungary) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Variables in stored procedure
I am getting the error that "TABLENAME" does not exist. How do I get it
to substitute the value stored in TABLENAME, and not the literal string?
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter <= v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0', 'statpress_mincap',
'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
Adam Gerson
Co-Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org
http://www.cgps.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: Variables in stored procedure
Thanks Luiz,
That got me closer. I was able to save the stored proc. It should be
execute stm; not execute @sql; right?
I get this when I try to execute it:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ''309',
'0', 'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_co' at line 1
begin
declare v_max int unsigned default 21;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter <= v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
set @sql = concat( "INSERT INTO ", TABLENAME, "VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_collectloggeduser', 'checked', 'yes'), ('307', '0',
'statpress_autodelete', '1 year', 'yes'), ('308', '0',
'statpress_daysinoverviewgraph', '31', 'yes'), ('310', '0',
'statpress_donotcollectspider', 'checked', 'yes'), ('311', '0',
'statpress_autodelete_spider', '1 day', 'yes'), ('312', '0',
'statpress_number_display_post_and_page', '20', 'yes'), ('313', '0',
'statpress_number_display_ip_spy_visitor', '20', 'yes'), ('314', '0',
'statpress_number-display_visit_spy_visitor', '20', 'yes');");
prepare stm from @sql;
execute stm;
set v_counter=v_counter+1;
end while;
commit;
END
--
Adam Gerson
Assistant Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org
http://www.cgps.org
On 10/4/11 5:29 PM, luiz rodrigo mottin wrote:
you can use:
set @sql = concat( "INSERT INTO ", TABLENAME, "VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes')");
prepare stm from @sql;
execute @sql;
2011/10/4 Adam Gerson mailto:agers...@cgps.org>>
I am getting the error that "TABLENAME" does not exist. How do I get
it to substitute the value stored in TABLENAME, and not the literal
string?
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter <= v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
Adam Gerson
Co-Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org <mailto:ager...@cgps.org>
http://www.cgps.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?__unsub=luizrodrigomottin@gmail.__com
<http://lists.mysql.com/mysql?unsub=luizrodrigomot...@gmail.com>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldiff resurrected and 0.43 released
Hi all, After a very long hiatus from maintainership (several years), I have finally released a new version of MySQL-Diff, the CPAN module suite which also contains mysqldiff, a CLI-based frontend tool for comparing the table schema of a pair of MySQL databases. Its output is a sequence of MySQL statements (CREATE/ALTER/DROP TABLE/COLUMN etc.), which if applied to the first database of the pair, will make its schema match that of the second. The web page is here: http://adamspiers.org/computing/mysqldiff/ and the manual page is here: http://search.cpan.org/dist/MySQL-Diff/bin/mysqldiff There are still a few open bugs in the tracker which need ironing out, mostly due to the evolution of MySQL itself since I first wrote this utility in 2000. Nevertheless I hope that some of you will already be able to benefit from the new life I'm trying to breathe into this little project. github forks are of course welcome too ;-) Cheers, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql/sql server migration
I'm involved with a project that is migrating data from sql server to mysql. The project will take up to a year to complete. During that period of time, we will need to synchronize data from sql server to mysql as we migrate windows based functionality to linux based servers. What is the recommended course of action to keep data synchronized between the two platforms? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Blob implementation question
Engine: MyISAM? InnoDB? other? InnoDB Let's see the SELECT. select bytes from table_name where id = %d If InnoDB, let's see the transaction, if it is part of such. It's a single query If InnoDB, which (COMPACT, etc) are you using. Not sure You are asking about a single row with the 500MB, correct? Yes Thanks for the additional details On Wed, Mar 13, 2013 at 1:00 PM, Rick James wrote: > A lot of details are missing... > Engine: MyISAM? InnoDB? other? > Let's see the SELECT. > If InnoDB, let's see the transaction, if it is part of such. > If InnoDB, which (COMPACT, etc) are you using. > You are asking about a single row with the 500MB, correct? > > In general, each request will ask for the same row, and will be blocked at > some level. The data will be fetched from disk and cached (radically > differently, depending on the Engine). Each request will be satisfied -- > perhaps sequentially, perhaps simultaneously. > > The "resultset" will need to be built at some point. This will probably > take up 500MB+ of extra RAM. This might lead to swapping or running out of > RAM. > > If the SELECT needs to build a temp table, it will be MyISAM, and it will > be on disk. But not all SELECTs need to build a temp table. This, for > example, won't: > SELECT myblob FROM mytable WHERE id=123; > This probably will (if foo is not indexed): > SELECT myblob FROM mytable ORDER BY foo; > > > -Original Message- > > From: Adam Ilardi [mailto:mastaskill...@gmail.com] > > Sent: Wednesday, March 13, 2013 9:16 AM > > To: mysql > > Subject: Blob implementation question > > > > Hello All, > > > > I'm trying to grok the blob implementation. This scenario is contrived > > to understand blobs please don't suggest I shouldn't do this. If you > > have a theoretical machine. Ubuntu with 4 cores/4gb of ram and we'll > > say once mysql is running 500mb of free disk space. > > > > I have a 500mb blob stored in a table and 30 concurrent requests come > > in to select the blob's bytes. How does mysql handle this situation @ > > an implementation level? > > > > Would mysql buffer the blob data to the disk? > > Would mysql keep 30 large in memory buffers for the data? > > > > I'd like to know when I would be @ risk of either filling up the disk > > or running out of ram in this situation. I'm also curious as to the > > code level details about how blobs are read and transmitted to a > > client. > > > > > > Thanks, > > Adam >
Re: Need help with a "natural sort order" for version numbers and release code names
Here's my full test solution:
use test;
drop table if exists releases;
create temporary table releases
(
releaseid int(10) unsigned not null auto_increment primary key,
name varchar(255)
)engine=myisam;
insert into releases ( name ) values
( 'Unspecified' ),
( 'Next Patch' ),
( 'LOCset' ),
( 'Abashiri' ),
( '4.6.0 (Folsom)' ),
( '4.5.9' ),
( '4.5.6' ),
( '4.5.5 (Purdy)' ),
( '4.5.5' ),
( '4.5.4' ),
( '4.5.3' ),
( '4.5.2' ),
( '4.5.10' ),
( '4.5.1 Deferred' ),
( '4.5.1 (Leavenworth)' ),
( '4.2.7.4' ),
( '4.2.7.3' ),
( '4.2.7.2' ),
( '4.2.7.1' ),
( '4.2.7.0' );
select
releaseid,
name
from
releases
order by
case name
when 'Unspecified' then 0
when 'Next Patch' then 1
when 'LOCset' then 2
else 10
end,
if(
locate( ' ', name ) > 0 and locate( '.', name ) > 0,
inet_aton( substr( name, 1, locate( ' ', name ) - 1 ) ),
if(
locate( '.', name ) > 0,
inet_aton( name ),
4294967295 ) ) desc,
if(
locate( ' ', name ) > 0 and locate( '.', name ) > 0,
replace( replace( substr( name, locate( ' ', name ) + 1 ), '(',
'' ), ')', '' ),
if(
locate( '.', name ) = 0,
name,
null ) );
This is the end result after talking to Daevid and finding out what
the ultimate goal was. I think it's kind of clever myself :)
Yes, there's no possibility of indexing here, but it gets the job done.
Adam.
On Oct 12, 2007, at 12:39 PM, Daevid Vincent wrote:
I'm trying to get some 'release/version numbers' to sort properly.
mysql> SELECT ReleaseID, Name FROM releases ORDER BY Name DESC;
+---+-+
| ReleaseID | Name|
+---+-+
|18 | Unspecified |
|20 | Next Patch |
|58 | LOCset |
|74 | Abashiri|
|54 | 4.6.0 (Folsom) |
<-- 4.5.10 should be here
|99 | 4.5.9 |
|98 | 4.5.6 |
|93 | 4.5.5 (Purdy) |
|97 | 4.5.4 |
|96 | 4.5.3 |
|94 | 4.5.2 |
| 100 | 4.5.10 | <-- should be ^ there
|91 | 4.5.1 Deferred |
|78 | 4.5.1 (Leavenworth) |
|95 | 4.2.7.4 |
|92 | 4.2.7.3 |
|90 | 4.2.7.2 |
|87 | 4.2.7.1 |
|88 | 4.2.7.0 |
I like this order, especially with the top four,
except for that 4.5.10 should be higher up,
just under 4.6.0, not under 4.5.2 as it is now.
So I tried the " + 0 " trick which makes things even worse
(notice the 4.2.6.1 and 4.2.6.0 -- yipes!):
mysql> SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC;
+---+-+
| ReleaseID | Name|
+---+-+
(18,20,58,74) are moved
:(
|54 | 4.6.0 (Folsom) |
|78 | 4.5.1 (Leavenworth) |
| 100 | 4.5.10 |
|91 | 4.5.1 Deferred |
|93 | 4.5.5 (Purdy) |
|94 | 4.5.2 |
|96 | 4.5.3 |
|97 | 4.5.4 |
|98 | 4.5.6 |
|99 | 4.5.9 |
|82 | 4.2.6.1 | << ?
|76 | 4.2.2 |
|75 | 4.2.4 |
|72 | 4.2.1 |
|73 | 4.2.3 |
| 67 | 4.2.6.0 | << ?
I'm pretty sure this is going to involve some sort of splitting the
version
from the release codeword via some string functions, and then
operating on
that part.
D.Vin
http://daevid.com
---
eval() is my favorite templating engine.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Adam Randall
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a "natural sort order" for version numbers and release code names
Oh, and here's the output (Sorry):
+---+-+
| releaseid | name|
+---+-+
| 1 | Unspecified |
| 2 | Next Patch |
| 3 | LOCset |
| 4 | Abashiri|
| 5 | 4.6.0 (Folsom) |
|13 | 4.5.10 |
| 6 | 4.5.9 |
| 7 | 4.5.6 |
| 9 | 4.5.5 |
| 8 | 4.5.5 (Purdy) |
|10 | 4.5.4 |
|11 | 4.5.3 |
|12 | 4.5.2 |
|14 | 4.5.1 Deferred |
|15 | 4.5.1 (Leavenworth) |
|16 | 4.2.7.4 |
|17 | 4.2.7.3 |
|18 | 4.2.7.2 |
|19 | 4.2.7.1 |
|20 | 4.2.7.0 |
+---+-+
20 rows in set (0.00 sec)
Adam.
On Oct 12, 2007, at 3:56 PM, Adam Randall wrote:
Here's my full test solution:
use test;
drop table if exists releases;
create temporary table releases
(
releaseid int(10) unsigned not null auto_increment primary key,
name varchar(255)
)engine=myisam;
insert into releases ( name ) values
( 'Unspecified' ),
( 'Next Patch' ),
( 'LOCset' ),
( 'Abashiri' ),
( '4.6.0 (Folsom)' ),
( '4.5.9' ),
( '4.5.6' ),
( '4.5.5 (Purdy)' ),
( '4.5.5' ),
( '4.5.4' ),
( '4.5.3' ),
( '4.5.2' ),
( '4.5.10' ),
( '4.5.1 Deferred' ),
( '4.5.1 (Leavenworth)' ),
( '4.2.7.4' ),
( '4.2.7.3' ),
( '4.2.7.2' ),
( '4.2.7.1' ),
( '4.2.7.0' );
select
releaseid,
name
from
releases
order by
case name
when 'Unspecified' then 0
when 'Next Patch' then 1
when 'LOCset' then 2
else 10
end,
if(
locate( ' ', name ) > 0 and locate( '.', name ) > 0,
inet_aton( substr( name, 1, locate( ' ', name ) - 1 ) ),
if(
locate( '.', name ) > 0,
inet_aton( name ),
4294967295 ) ) desc,
if(
locate( ' ', name ) > 0 and locate( '.', name ) > 0,
replace( replace( substr( name, locate( ' ', name ) + 1 ), '(',
'' ), ')', '' ),
if(
locate( '.', name ) = 0,
name,
null ) );
This is the end result after talking to Daevid and finding out what
the ultimate goal was. I think it's kind of clever myself :)
Yes, there's no possibility of indexing here, but it gets the job
done.
Adam.
On Oct 12, 2007, at 12:39 PM, Daevid Vincent wrote:
I'm trying to get some 'release/version numbers' to sort properly.
mysql> SELECT ReleaseID, Name FROM releases ORDER BY Name DESC;
+---+-+
| ReleaseID | Name|
+---+-+
|18 | Unspecified |
|20 | Next Patch |
|58 | LOCset |
|74 | Abashiri|
|54 | 4.6.0 (Folsom) |
<-- 4.5.10 should be here
|99 | 4.5.9 |
|98 | 4.5.6 |
|93 | 4.5.5 (Purdy) |
|97 | 4.5.4 |
|96 | 4.5.3 |
|94 | 4.5.2 |
| 100 | 4.5.10 | <-- should be ^ there
|91 | 4.5.1 Deferred |
|78 | 4.5.1 (Leavenworth) |
|95 | 4.2.7.4 |
|92 | 4.2.7.3 |
|90 | 4.2.7.2 |
|87 | 4.2.7.1 |
|88 | 4.2.7.0 |
I like this order, especially with the top four,
except for that 4.5.10 should be higher up,
just under 4.6.0, not under 4.5.2 as it is now.
So I tried the " + 0 " trick which makes things even worse
(notice the 4.2.6.1 and 4.2.6.0 -- yipes!):
mysql> SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC;
+---+-+
| ReleaseID | Name|
+---+-+
(18,20,58,74) are moved
:(
|54 | 4.6.0 (Folsom) |
|78 | 4.5.1 (Leavenworth) |
| 100 | 4.5.10 |
|91 | 4.5.1 Deferred |
|93 | 4.5.5 (Purdy) |
|94 | 4.5.2 |
|96 | 4.5.3 |
|97 | 4.5.4 |
|98 | 4.5.6 |
|99 | 4.5.9
how to "drop index if exists"
Folks How can one conditionally drop an index in MySQL? Googling shows that the "drop index" does not support an "if exists" qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to "drop index if exists"
Fantastic, thanks very much! Adam Rob Wultsch wrote: On Nov 29, 2007 4:34 AM, Adam Lipscombe <[EMAIL PROTECTED]> wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the "drop index" does not support an "if exists" qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to "drop index if exists"
Sorry I got carried away in my former response. When I tried this, ALTER IGNORE TABLE tbl_name DROP INDEX seems to fail if the index does not exist. The DROP INDEX statement is part of a larger script, what I want is for the script to conue to execute if this index does not exist. e.g. ALTER IGNORE TABLE table_name DROP INDEX index_name; ALTER IGNORE TABLE table_name ADD INDEX UNIQUE index_name(column_1,column_2); Thanks - Ada Rob Wultsch wrote: On Nov 29, 2007 4:34 AM, Adam Lipscombe <[EMAIL PROTECTED]> wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the "drop index" does not support an "if exists" qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- Adam Lipscombe T: 01872 575083 M: 07957 548686 E: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
% wildcard host permission not working
I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
Yes, I did "FLUSH PRIVILEGES" and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you "FLUSH PRIVILEGES"? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Retaining last primary key value
Hello, When I run a DELETE FROM table_name, my primary key field is reset back to 1. Is there any way for it to not do this? Preferably, if there were 1000 records in table_name, and I ran DELETE FROM table_name, that the primary key field would start at 1001. This is an InnoDB table if that helps. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retaining last primary key value
Thanks for the reply but that didn't do it. I believe I've got this to work by performing the following steps: DELETE FROM table_name (1000 rows) INSERT INTO table_name(column) VALUES(value) DELETE FROM table_name Now when I perform another INSERT, the primary key starts at 1001. Adam Zerlin On Dec 8, 2006, at 11:38 AM, Saqib Ali wrote: try the DELETE with the WHERE clause e.g. DELETE FROM table_name WHERE 1=1 saqib http://www.full-disk-encryption.net On 12/8/06, Adam Zerlin <[EMAIL PROTECTED]> wrote: Hello, When I run a DELETE FROM table_name, my primary key field is reset back to 1. Is there any way for it to not do this? Preferably, if there were 1000 records in table_name, and I ran DELETE FROM table_name, that the primary key field would start at 1001. This is an InnoDB table if that helps. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Saqib Ali, CISSP, ISSAP http://www.full-disk-encryption.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
