Re: How to Backup the Database using Script
Here is how I have been doing it, for years, and I have used this to restore from, which has always worked for me. http://dl.getdropbox.com/u/340087/Drops/10.29.09/mysql_backup-0196a0c2-013914.txt A quick explanation, and some caveats: First, set the u and p variables to a mysql user and password that has read access to all databases. You only need read access, no more, no less. I then set the permissions on this script itself to read/write/execute for root only. There is a small window in which the data is being dumped in which the permissions of the dump files are a little more open than I wish them to be. I need to look into how to get MySql to create the dump with a fixed set of owner, group, and permissions. There are also ways to do with without exposing a password in a file, but for my system, this was sufficient. Here is how it works: 1) Variable for your MySql data dump storage location is set. 2) show databases is passed to MySql, in my case, the binary is called mysql5, you may need to adjust the name and add a path 3) With a list of all your databases, the script can now iterate through them all, sending out the database.sql files 4) Right after the .sql files are dumped, permissions are reduced 5) The entire batch of databases are then compressed, with datestamp as name 6) Archive file is then reduced to lesser permissions 7) Original .sql files are removed, now that there is one archive file You can hook this to cron, or on OS X to launchd, and have it run on a schedule. There are a lot of improvements that could be made. For example, it may be possible to have the dump pipe to a compression on the fly, which would then only require a quick tar of the data when done. Permissions certainly could be dealt with better. I was thinking to `touch database.sql` with correct permissions, and then have MySql overwrite that, but still not sure the most elegant and sane way to do this. The data could probably be appended to an archive set, saving the step of removing all the .sql files. You could also locate files older than x days, and remove them, only storing a fixed number of backups. I needed backups, and this works for me. My main criteria was that I wanted all databases backed up, and did not want to have to think about modifying a script every time I added a new database. This script is limited by the speed of your database, and the drives you are dumping to, and will of course affect the performance of the database as the dumps are happening. I generally try to run this on a replication server, so I am not hitting the live database. This way, you can have multiple MySql machines all replicated to one machine, and then the dumps happen on a non public facing machine. Add in some RAID and other backup strategies, and you should be fine. Feel free to modify the script or comment on improvements, I would love to make it a better script. The most important thing to me is that I have been able to use it to recover from. Script is below: #!/bin/sh # Backup all MySql databases # [10/29/2009 01:37:35 AM] sc...@hostwizard.com # I set permissions on this file like so: # -rwx-- 1 rootwheel 864 Oct 20 23:33 mysql_backup # set date and time time=`date +%m-%d-%y_%I-%M%p` u=username p=password # set path to final destination, needs trailing slash location=/backups/mysql/ # set db_list to the list of databases db_list=`echo show databases | /opt/local/bin/mysql5 -N -u$u -p$p` for db in $db_list; do echo dumping $db to $location$db.sql /opt/local/bin/mysqldump5 -u$u -p$p --opt $db $location$db.sql chown root:wheel $location$db.sql chmod 0 $location$db.sql done echo changing to directory $location cd $location echo Now in: `pwd` echo begin tarballing tar cvfz $time.tgz *.sql # set permissions on the final file chown root:wheel $time.tgz chmod 0 $time.tgz echo removing: ls -la $location*.sql rm $location*.sql echo All your MySql Database are Belong to Us; echo $location$time.tgz -- Scott * If you contact me off list replace talklists@ with scott@ * On Oct 28, 2009, at 10:33 PM, Ganeswar Mishra wrote: Hi Everyone, I am trying to backup a database regularly, without using Administrator tool in mysql, Can anyone help to write a scipt regarding backup database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Performance tuning a group by with percentage
Running MySql 5.0.85, I need to be as efficient as possible about a few queries. If I could get a little review, I would appreciate it. I collect data in the millions, and need the top 50 grouped by one field, with a percentage of how much those top 50 occupy. Here is what I have come up with... 1) I have a feeling I can be more efficient, perhaps with a join 2) How can I get the percentage to be of precision in the hundredths, so * 100.00 ie: .07 becomes 7.00, getting SQL errors if I (percentage * 100) SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount, COUNT( * ) / ( SELECT COUNT( * ) FROM agents ) AS percentage FROM agents GROUP BY user_agent_parsed ORDER BY thecount DESC LIMIT 50; Second issue, once a day I need to archive the result of the above. Any suggestions on how to best to do that? I can schedule with cron, or in my case, launchd, unless someone has a better suggestion. Would you think that a simple 'SELECT (the above) INTO foo' would suffice? ( I will add a date stamp as well ) Thanks all. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: insert random number into table
In addition to what Gavin said. You seem to want some form of key perhaps to be able to identify the authenticity of your contest winner. An auto increment ID won't be very secure for that, but you still need it. Take a known combination of perhaps the key, name, email address, etc, and run a hashing function on it. Md5 will often suffice. This will give you a unique hash that only you know how it was made. You can inject some 'salt' into a known location to add more difficulty in guessing your hashing input. This hash will be asking too much of the user to hand type in, so I'm assuming you will accept it via a link in a URL. Or ask for whatever you used to make the hash and rehash that and compare your internal stored result. If not, for your needs you may be able to safely remove ambiguous characters, and take a substring of it to make it shorter. It's also quite easy to create a dictionary of words and create a function that can make word1-xx-word2 and end up with red-56-car. -- Scott Iphone says hello. On Oct 16, 2009, at 11:25 AM, Gavin Towey gto...@ffn.com wrote: Don't try to give them a random number, instead use a table with a primary key that is AUTO_INCREMENT. Then you just insert the record, and afterwards SELECT LAST_INSERT_ID(); to retrieve the id for the record created. With random numbers, you're going to have more collisions when you add more records. Regards, Gavin Towey -Original Message- From: Ray [mailto:r...@stilltech.net] Sent: Friday, October 16, 2009 8:43 AM To: mysql@lists.mysql.com Subject: insert random number into table Hello All, I am trying to insert a unique random number* with each row when inserting into a table. I can do it in the application layer (php) but just wondering if there is an easier way in the database layer. Thanks Ray * for example, when registering a person for a contest, I want to give them a unique, random identifier.
Re: insert random number into table
I always maintain a timestamp in my random numbers. As long as my precision is higher than my requests per second, wouldn't I be safe from collisions? Assuming a time machine is not invented. -- Scott Iphone says hello. On Oct 16, 2009, at 11:29 AM, Jerry Schwartz jschwa...@the-infoshop.com wrote: JS] Just remember that the pseudo-random numbers that come out of a computer, regardless of where or how they are generated, are not guaranteed to be unique. Although the odds of a collision are slim, my philosophy has always been if it's possible, it will happen - in the middle of your vacation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. On Aug 27, 2009, at 2:57 PM, Adrian Aitken wrote: Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
Oops, sorry about that. My understanding is this is OS dependent: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html You may be able to set lowercase tables names, but would always have to use lowercase. I would just stick to entering in the correct case, as the other methods seem prone to trouble. On Aug 27, 2009, at 3:04 PM, Scott Haneda wrote: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. On Aug 27, 2009, at 2:57 PM, Adrian Aitken wrote: Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Logins, php, legacy, old password
Hello. It is time for some long overdue service consolidation on my end. I have a production machine ready to go, running the latest MySql 5 series. I have a legacy machine running MySql 4. As you know, version 4 used an older user account password system. I also will be up against some data import/export changes. Specifically I think the timestamp column changed. I want to move the data from 4 to 5. Here is my problem. The http server that talks to MySql is ancient, unable to be updated. Http will remain on this legacy machine talking to MySql, using an equally ancient scripting language that does not, and can not support the new password format. This http server does not support MySql, so a local copy is not an option. I believe I will need to set old style passwords. I'm trying to avoid multiple machines or multiple instances of MySql on the same database server. What are my options? I'll do the work to convert the data. This means scripting code that displays and formats timestamp data is going to break. I can dig deep into my long term memory and change the scripting code to solve the timestamp issues. Are there other major issues besides timestamps? All inserts used NOW (), so I'm ok there. But display of timestamp to the user needs editing. It's controlled by a function so I should have a pretty easy go. But what about the passwords? I hear php 5 will not do old style passwords at all. So I can not just set old style passwords to accomodate my legacy systems. Don't really want to either. What I would like is to set old style passwords on a per account basis, but have the default be new methods. I suspect by nature of how this works that is not possible. I'm really trying to consolidate. Yes, I can run a second server, but I would rather manage and maintain one, giving me the ability to retire a machine. -- Scott Iphone says hello. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Client deleted database, semi high priority master slave question
A client deleted their database and did not have a slave in place for backup. No scheduled dumps either. However, I have set up a slave to the master for one table. These tables hold DNS data for database driven DNS server features. The master table is empty, the slave rightly saw the deletes and carried them out. But I believe the replication logs on the slave, and also perhaps on the master should still be there. I'm not clear on how large these get, if they roll/truncate, etc. Is there any way to restore the master up to the point the data was deleted? I can then set the slave to resync and they should be back where the left off. There are a lot if records, not a lot of data since DNS data is so small. If I could restore the master It would prove quite helpful. Currently, all servers are down since DNS is not working, as a result of DNS being backed by MySql via DLZ/named. Thanks for any suggestions. -- Scott Iphone says hello. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Client deleted database, semi high priority master slave question
On Jul 8, 2009, at 12:49 PM, Daevid Vincent wrote: Ouch. I know it comes a little too late, but as a preventive measure going forward, I offer up my daily crontab backup script: http://www.daevid.com/content/examples/daily_backup.php They move a lot of data, so scheduled dumps were not really an option. I wrote a proposal to use two databases, one is the primary acting as a DNS 1, and the second is the slave, acting as DNS 2. I then wanted to add a 3rd slave server, that was a hot standby, in case the primary went down. It would hold copies of all databases. This would be on a raid, and on a schedule, it would also do dumps, which could take as long as they want, since this machine was not being hit at all. This was not implemented for some reason. I will look at your scripts, thanks again. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Client deleted database, semi high priority master slave question
On Jul 8, 2009, at 12:49 PM, Daevid Vincent wrote: I know it comes a little too late, but as a preventive measure going forward, I offer up my daily crontab backup script: http://www.daevid.com/content/examples/daily_backup.php FYI, this link does not work in Safari. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
The times I have heard, this is just a test, hack it together, or this will never see significant load are more than I care to count. Worse, the times that those statements ended up being false, and a rigged and hacked demo code base become production has taught me to treat all work as though it will become production. That small rant aside... Your question is hard to answer without knowing how you plan on displaying the results of the data that comes out of the database. If this is a case where you pull the data out and show it in a web browser, then without question, less access time would be to store the images in the file system. There is no access to if there is no request for the image out of the database. Even if you are not using a browser, I am having a hard time thinking of many cases in which storing an image in the database is a good idea. Then again, I am thinking jpg, png, gif etc. Could be by image you mean a highly structured bitmap pattern, or pure vector coordinates that render an image out to SVG. I do have one question. If you can make the statement that scaling will never be an issue, then you why does it matter? In a non performance driven environment, you should pick the most convenient method to manage, and not worry about performance or scaling, if you have that luxury. On May 27, 2009, at 9:10 PM, mugisha moses wrote: what if you had no issues of scaling, which would have less access time, file system or database? On Thu, May 28, 2009 at 1:54 AM, Arthur Fuller fuller.art...@gmail.com wrote: I second that emotion (don't store the images in the data file: just store the paths to said images). Why? Because in the event of an updated image it is more hassle than it is worth, to drop the old image and replace it with the new one; if what you store is a simple pointer to an image file, lots of this work goes away. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
A bit of a confusing set of replies was in the previous thread... Doing my best to answer the issue at hand, please reference past posts if my reply is not clear... On May 28, 2009, at 6:04 AM, PJ wrote: Could you clarify/expand on this a bit - I am setting up a site where I expect to have a lot of images, both still and flvs, and a lot of recipes (including ingredients, procedures ad text : ;images). I would put some thought into how you store the recipes. You mention XML below. This will all depend on the structure of the recipes data. In a simple case, you have quantity and item for each recipe. A simple relationship of something like the recipe parent, where that holds the title, and maybe description and instructions, linked to a second table with quantity and item columns would work fine. That is also very rigid, and you never know where your data needs are going to deviate from that form. Put some thought into this. Get a good sampling of your recipe data to make sure you build this out in a way that is forward flexible. I am storing the images in /images directory. If the amount of images gets rather large, you are suggesting to store them on another server, right? I would first start be researching how your OS deals with large quantities of images in one single directory. Write a script to copy 1 image 100,000 times over into the same directory. Test how fast you can grab a random image, test how fast you can delete a random image. Different OS's will behave different under different amounts Even from a pure CLI management perspective, a simple `ls -la` will take some time to finish on 100,000 images. I generally do something along the lines of: images/$user-id/$year/$month/$day/$image-name-$random.ext Back to your question. Just because the amount of images gets large, does not mean you need to use multiple servers. It is only if the load of requests for those images gets more than the server can handle that you may want to look into distributing that load. Distributing image load over http, if that is what you are doing, is almost trivial. Starting with round robin DNS and rsync to more advanced load balancing, or looking into a CDN like what Amazon offers. There are solutions abound in this area. Now, with a lot of recipes, I understand that I should be storing them also on another server; and perhaps using XML to store the recipes. Does that sound like I have understood your advice? I am not so sure. Recipes are text only, take little space. A simple phpBB forum may have GB's of data in them, on a shared database server. You may want to have a second database for replication, as a hot backup, and then have other things in place for cold backups. This all depends on a lot of factors about your end plans which have not yet been shared. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
Unless you have very good reason to store binary data like an image in your database, do not. It may work well for a time, but always be prepared that your system will grow. If it grows a good deal relative to your hardware, and users, and connections etc, you will always be in a race to throw more hardware at the problem. In a simplistic terms, an image is a file, files should be stored on a file system. You can do some very nice and simple things later on to distribute image loads. If you store the path to the image, so example, images/ date/time.jpg, and then need to grow your serving needs, you could adjust your code in the http request to call images.example.com as the hostname. You could set images.example.com to return round robin DNS records. Now all you have to do is mirror or sync your images directory to any number of high performance http servers. That is just one example of how I have avoided dealing with binary file load in MySql, or any database for that matter. Even a reltively small image stored in a database can become a lot of data very fast. You will also find it can be inefficient to fetch that data and show it to the end user. At the very least, you have to request the data, read it, rebuild it into a form that can be displayed to a user, which is a lot of work, compared to no work at all if just calling it from the file system. Your question about which is faster, always will be on disk binary storage, with the exception of deletes. Deletes will happen in near the same time, though you do have to locate the record to delete. This probably still has no impact, since you will locate on an index. Hope that was helpful. On May 27, 2009, at 9:50 AM, Pete Wilson wrote: I am new to MySQL and just laying out what I hope will be just one db that holds all the info for a list of forged or machined metal parts. Let's say there are 10,000 such parts, each with a unique part number. That part number is the primary index. Each part has at least one, and maybe several, accompanying photos. And each part has at least one, and perhaps as many as five, human- language descriptions. These descriptions might be as long as 5,000 characters each. I believe I have the choice of: 1. Storing everything -- photos and descriptions included -- in the database; or 2. Holding some info in the database; and storing photos and descriptions in normal-type disk files apart from the database, with names based on part numbers. So my questions: 1. Which scheme is faster and less resource-hogging in reading, updating, adding, and deleting? 2. I understand there are problems in storing photos in the db. Can one overcome these problems somehow? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrade Mysql
On May 20, 2009, at 1:27 AM, Webmaster Studio Informatica wrote: I need to upgrade Mysql 4 to Mysql 5 on Linux. Sometimes I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. In most cases, but you should have a database dump of all of them just in case. You do not specify what version of 4 you are at. You need to at least read this: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html Specifically look at Incompatible change, there are quote a few. Most will not bother you. For me, the biggest issue was my use of timestamp, and how that changed a bit. Luckily I had a function that I used in my code to format that timestamp value, so it was just a matter of going through all my code and updating one function to all sites. This was also only a display issue for me and did not change my data. You do have to know your code. If you do not, I would use a staging server, and do them one database at a time, test, make sure it works, and go from there. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On May 16, 2009, at 12:28 AM, Simon J Mudd wrote: I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = xx master-port = 3306 # End slave config No. not necessary as the information is stored in the master info file. Thanks Simon, I will test. Looks like if this is the case, literally, 99% of every tutorial out there is wrong, as they all do this in duplicate, along with the CHANGE MASTER SQL command. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: 1. Ensure binlogging is enabled on the master. 2. Ensure you setup grant permissions so the slave can connect to the master. 3. Configure on the slave the replication (which databases need to be replicated) 4. Get the master and slave in sync (via rsync, load/dump or whatever) 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. Also, how do I set the slave to be read only? I set read-only in my.cnf and it made all databases read only. I want to limit just the replicated database to be read only. The rest of them are production databases in normal use by clients. I suppose just not having a login and pass to the replicated database is more than enough? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 1. Ensure binlogging is enabled on the master. 2. Ensure you setup grant permissions so the slave can connect to the master. 3. Configure on the slave the replication (which databases need to be replicated) 4. Get the master and slave in sync (via rsync, load/dump or whatever) 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. I have it set up and working in test. I will redo it again once I get a better handle on it. I am still a little confused on one aspect. In the mysql sample cfg file, the section that has: #Replication Slave there is a very clear OR to use either #1 OR #2. I did the suggestions of #2, issuing #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; on the slave. I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = passw3rd master-port = 3306 # End slave config Am I correct in that this is not needed. I know I for certain need server_id, but is that all I need, and I have redundant data? I figure also better to not have raw user and pass in a cnf file if it is not needed. I would say 99% of the examples on the internets are using both methods, the MySql docs are not entirely clear to me on this specific issue. Conflicting data on the comments for sure. I also have seen a good deal more options specified than I have. To be clear, I am only looking for one way replication, the most basic, a master that is read/write by clients, and a slave that is neither read/ write by clients, but only reading in data from the master, syncing it. Here is a example of other options I am seeing, comments interspersed... server-id = 1 log-bin = /usr/local/mysql/var/bin.log I take it I do not need this if I am ok with default data dir? log-slave-updates I am pretty sure I do not not need this, since I am only doing master to slave, and no cascading replication, or circular rep. log-bin-index = /usr/local/mysql/var/log-bin.index Same as log-bin, of I am ok with default data dir? log-error = /usr/local/mysql/var/error.log Again, if I am ok with default data-dir? relay-log = /usr/local/mysql/var/relay.log I do not seem to have this file anywhere. relay-log-info-file = /usr/local/mysql/var/relay-log.info relay-log-index = /usr/local/mysql/var/relay-log.index I do not see that I have these on the master, I have it on the slave. Maybe all these logs are best to be defined. Perhaps if they are not, the hostname may be used in the naming of the logs, and if a hostname ever changes, I would have issues on getting replication to fine the logs? auto_increment_increment = 10 auto_increment_offset = 1 I am definitely not clear on these. I want a 100% replica. Why do I need to worry of key id collisions at all? Or is This n/a to a simple master/slave setup master-host = other master hostname master-user = replication username master-password = replication password These I wonder if are needed at all, if I use the sql CHANGE replicate-do-db = somedbname1 replicate-do-db = somedbname2 Good to know, wondered how to add more, if you comma seperated
Re: Replication config
Thanks, a few comments inline below... On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 1. Ensure binlogging is enabled on the master. Done, I can see the binlogs as well. 2. Ensure you setup grant permissions so the slave can connect to the master. From the remote MySql I have mysql -u user -h example.com -p It allows me in so I think I am good there. 3. Configure on the slave the replication (which databases need to be replicated) This is where I need a little clarification, is the only thing I need to do is adjust my.cnf to have in the [mysqld] section server-id = 2 4. Get the master and slave in sync (via rsync, load/dump or whatever) Is this mandatory? There is not a lot of data, hundred rows or so, can I use LOAD DATA FROM MASTER; ? Seems most instructions say to use a dump. This does not make a lot of sense to me, I am setting up replication, is it not the point to be able to pull the data down? Why does it need priming like this? 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master I can do this now, gives back a position. It seems to change over time. Since it is a moving target, if I am using LOAD DATA FROM MASTER; I take it I need to lock the tables while the first load is happening? 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE Will do, thanks. 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. Thanks. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. While not part of my plan, if the master goes down and I want to start using the slave as the master while I am fixing the master server What is the best way to do this? Can the slave be treated like a master by just pointing any client to the slave assuming I set a user to allow it? With the slave temporarily becoming the master, the data will of course change. When I bring the master back online, what is the best way to reverse sync and get back to where I was? Probably take the entire thing thing down, copy the database from the current temp live slave that has been used as a master, and go from there? Thank you. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication config
Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) -- Scott * If you contact me off list replace talklists@ with scott@ * -- 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 mysql query, multiple list
What about sub selects. As I see it you only care about the highest and lowest order of results in each list. Sorry, in am on a mobile so I can nit make a test case, and this will be pseudo SQL. Select * from table where start = (select foo) and ( select foo) ... Also look at the between and in keywords. Perhaps your list of timestamps is not in a database, can you put then in? Either way unless I am reading you wrong you only need the greatest and lowest time in your comparison, not a huge list. -- Scott Iphone says hello. On May 8, 2009, at 9:26 AM, Abhishek Pratap abhishek@gmail.com wrote: Hi All I am kind of stuck with this query , cant expand my thinking. May this is a limitation. Here it is I have a database with many cols two of which are start and end position for an event. Now I have a list of event time stamps, I want to find all the info once the current event time stamp is = start time of event and =end time of event. something like this select * from table_name where start = ( LIST of time stamps) AND end =( list of time stamps). Clearly above query accepts only one item in the list. Is there a way to do this for multiple items in the list ??? I can't think of anything at this moment. Thanks, -Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query log for just one database
I am trying to monitor a specific issue, and I know it is related to only one database. There is a lot of other noise in the logs if I enable query logging. Is there any way to limit query logging to just one database? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
Always echo out your SQL string, it will make it a lot more obvious. You want to see the result. I php concatenated string can be confusing at times. Also, you are not escaping your data, so if you had a word of 'stops, here' that would break it as well. So in your case, you very well may break it by changing the data you put in. You could also do something like stuffing drop database foo; into your data, and be in for real fun. Pass every string to http://us2.php.net/mysql_real_escape_string On Apr 30, 2009, at 9:27 PM, Antonio PHP wrote: I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: This is MySQL data structure. - I underlined where it causes the error message. (datetime) `id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL `Revenue` mediumint(6) NOT NULL, `Company_Size` mediumint(6) NOT NULL, `Ownership` tinyint(1) NOT NULL, `Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *`Created` datetime NOT NULL, *PRIMARY KEY (`id_Company`), KEY `Ownership` (`Ownership`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE CASCADE ON UPDATE CASCADE; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. Here is php script - $sql = INSERT INTO company SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source' *Created=NOW() // if I remove this line it works fine. *; mysql_query ($sql) or die (mysql_error()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
We need to see your entire query and the table structure. timestamp fields can have options set to auto update them, where order matters, and only one field can support that feature. Please supply more data. On Apr 28, 2009, at 2:18 PM, Antonio PHP wrote: 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 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Information Schema
I have been meaning to find out about this since I moved to mysql 5. In version 4, I never saw the table information schema. With it being in version 5, I assume it was something only the root users, or a higher level user could see. I now know that it shows up under any account. I will certainly go read more in the docs about what this table is for. However, since it seems to be important, I would assume you do not want database users to be able to update, insert, or delete against it. Is the default set up in a way that database/tables are protected? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto increment?
On Apr 2, 2009, at 12:51 AM, Steve Edberg wrote: At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote: Hi, I have a table 'test' +-+--+--+-+--- ++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- ++ | id | int(11) | NO | PRI | NULL | auto_increment | | foo | varchar(255) | NO | | NULL || | bar | varchar(255) | NO | | NULL || | payload | longblob | YES | | NULL || +-+--+--+-+--- ++ where 'payload' size usually is quite big! Now I want to manipulate a number of rows in a non-trivial way and at the same time set 'id' for those rows to a new number in the sequence. I.e. UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ... My motivation is that this table has several consumers in other applications that keep track of the biggest 'id' they have seen between polls and thus can keep track of new and changed rows. Right now, I solve this problem by copying the rows to a temporary table, then delete them and insert them from the temporary table but this moves my huge payload around which I really want to avoid. How can I solve this in a better way? /Andreas Pardeike Add a column of type timestamp which, by default, will be updated every time a record is inserted or updated. Then the other applications can simply select records with timestamp last_poll_time. My same suggestion as well. I go a far as to have at least, three standard fields to any table I make: CREATE TABLE IF NOT EXISTS `foobar` ( `id` int(11) NOT NULL auto_increment, `udpated` timestamp NOT NULL default '-00-00 00:00:00' on update CURRENT_TIMESTAMP, `created` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; You never know when you want to know time and date, and this makes it brainless. So any new record you add you will set created = NOW();, outside of that, never worry about the updated, field, it gets set automatically any time there is any change to the record. I am near certain, there is one small thing to note, which is for example, if you update foobar set something = 'test' where id = 1 and something was already at test, since no real update/change happened the time-stamp is not going to get updated. * There is a version of mysql 4, that going forward, had a change to how `timestamp` was defined. If your old data is in version four, and you plan to move to a new version, look out for that issue. Thanks for welcome :) -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto increment?
I read your other replies about the timestamp not working. I still think adding the updated and created fields is a good idea in general, to any table. I have some questions about the below since the original suggestion would not work for you. On Apr 2, 2009, at 12:35 AM, Andreas Pardeike wrote: +-+--+--+-+--- ++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- ++ | id | int(11) | NO | PRI | NULL | auto_increment | | foo | varchar(255) | NO | | NULL || | bar | varchar(255) | NO | | NULL || | payload | longblob | YES | | NULL || +-+--+--+-+--- ++ where 'payload' size usually is quite big! Now I want to manipulate a number of rows in a non-trivial way and at the same time set 'id' for those rows to a new number in the sequence. I.e. UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ... My motivation is that this table has several consumers in other applications that keep track of the biggest 'id' they have seen between polls and thus can keep track of new and changed rows. I am a little stumped on this, since id is auto_increment, do you start to see gaps in your id's? This is not undesirable to you? Right now, I solve this problem by copying the rows to a temporary table, then delete them and insert them from the temporary table but this moves my huge payload around which I really want to avoid. How can I solve this in a better way? How about changing the initial insert logic. The above table would keep the id but be used as your key. Add a second id of payload_id. Create a new table with id, payload_id, and payload. Store just the payload in a separate table, connected with the id = payload_id. Now you are never touching your payload data, which is too large. Also, I have been in cases with batch uploads where performance is an issue, and used the INSERT DELAYED features of mysql, where the the insert is sent in one command, but the server will do it when there is idle time to deal with it. You have to be a little careful to anticipate a server crash or connection failure, but rollback can solve that easily. There does not seem to be an UPDATE DELAYED syntax, but I did just learn of The UPDATE statement supports the following modifiers: • If you use the LOW_PRIORITY keyword, execution of the UPDATE is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). Maybe you can use the LOW_PRIORITY keyword in your update commands to your advantage? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Auto increment without uniquness
I think ideally I would like to create an auto increment column that has no requirement for uniqueness. So if 6 was the last entry, and there are 10 of them, 7 would still be the next, is this possible? I am assuming it is not? I am working in a case where data is needing to be de-normalized, and I have never had to do this before. In the past, this would be a join away. Now I have one table, it will have a single parent record, with x children records. I start with an `id` primary key auto inc field, standard stuff. Keeping it simple, lets say I have: id, group_id, foo, bar, baz I will do a batch of inserts, where the first record is the parent, and the rest are children. The first parent record is what is getting me stuck, I need to give it a group_id, but all the children will need to have the same group id. I could do one insert on the parent, get the returned id, and then update the group_id on the parent to the same id, but that is an insert and an update, I want to avoid the update. Can I insert into table set foo = 'test', `group_id` = `id` Or is that too soon in the insert trasaction to be noticed? I can allow the parent to have an empty group_id, and use the id as what I search on, but it seems messy. Suggestions? Thank you all -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Hypothetically looking for Kevin Bacon
Hello mysql list, I am looking for a little guidance on a a potential query. I am sure most people have heard of the limited degrees of separation one has to become connected to another person. This is much like the myspace in your extended network idea, though I would like my implementation to not be broken. If person A exists in mysql, as well as persons B through Z, and some are related or known to each other, they have a relationship. Very much a family tree. While I can see how one wold start at person A, and look for the relationship to another person, thereby drilling down to other known links... I would like to take it one step further. I think celebrities are the best example, so I would be able to enter in myself, and ask the question: show me a tree of how I am closest to x celebrity. Since this is not a real project, and just a hypothetical question, assume that the links are known to be valid, and there is some proven way to key one person to another that is accurate. For example, in this case, we would use an honor system where Person A logs in, and selects all the other persons that they know. What would be the query to find out all the relationships of person A to x celebrity? How expensive is this query? I am looking to build something as a real project that will use this idea as a feature, albeit in a different way, but the end result will be the same. I get the feeling this is one, that if it is a used and popular service, the database design before it is made will be highly important. I smell a bit more recursion in this idea that I am used to, and also feel that doing most of the work in the DB layer, and not in the application layer, is going to give me the best performance. Does anyone have suggestions, or perhaps examples of this in working use? Maybe there is a known algo of function that gets close to this already. Thank you -- Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Anyone know what to do about OS X 10.5 PPC
I can not find a pkg installer for OS X 10.5 on PPC, any suggestions? Would I want the 64 or 32 bit version of this as well? I also need to know a simple php 5 installer that works on 10.5 for PPC, I am growing very tired of building that one out all the time, and Entropy seems to have fallen off the planet. Thanks -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone know what to do about OS X 10.5 PPC
I think I got this worked out, you can use the 10.4 installer for OS X on PPC. Use the 32 bit one for G4, and the 64 bit one for G5, or you can still use the 32 bit one on the G5 if you want to. Just install the app, do not install the startup item or the prefpane, they do not work, and need modifying. Since this is a server, you want it to run all the time anyway, so I see no need for on demand. The repaired prefpane I have seen, still uses a StartUp Item, which is deprecated as of 10.4 as far as I know. Solution: in /Library/LaunchDaemons create com.mysql.mysqld.plist Contents should be: (END) ?xml version=1.0 encoding=UTF-8? !DOCTYPE plist PUBLIC -//Apple//DTD PLIST 1.0//EN http://www.apple.com/DTDs/Pr opertyList-1.0.dtd plist version=1.0 dict keyDisabled/key false/ keyGroupName/key string_mysql/string keyKeepAlive/key true/ keyLabel/key stringcom.mysql.mysqld/string keyProgram/key string/usr/local/mysql/bin/mysqld/string keyProgramArguments/key array string--user=_mysql/string string--lower_case_table_names=2/string /array keyRunAtLoad/key true/ keyUmask/key integer7/integer keyUserName/key string_mysql/string keyWorkingDirectory/key string/usr/local/mysql/string /dict /plist I am not sure about the array item for lower_case_table_names, but I know OS X is case insensitive, so I know forcing a 0 would be bad, and not sure about a 1, but the 2 is set with a warning anyway, so I just wanted the warning out of my logs. now in terminal just type sudo launchctl load com.mysql.mysqld.plist mysql will start, you can use unload to stop it. It will start back up again after a reboot, or even a crash, it will start back up again. The default php install on 10.5 is junk, and needs a lot of messing with to get to work, so I am still looking for a way to solve that right now. I can not find a pkg installer for OS X 10.5 on PPC, any suggestions? Would I want the 64 or 32 bit version of this as well? I also need to know a simple php 5 installer that works on 10.5 for PPC, I am growing very tired of building that one out all the time, and Entropy seems to have fallen off the planet. Thanks -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improving random record selection
I posted this a month or so ago, and was helped a little, but I am now back. Currently I use select x, y, z from images where (condition) order by rand() limit 1; As most know, it is slow, depending on the record set, and what I compare it to, it can be from one order of magnitude slower, to several. I have cobbled together a solution, but it on occasion, returns an empty result set, which is causing me grief. I should mention, there are holes in my id column, and I am needing to select a set based on a condition. The below sql I do not fully understand either, if someone could step me through it, I would certainly appreciate it, though my main goal is to figure out why I get an empty set on occasion. $sql = SELECT storage_path, image_md5, t.id FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images) AS x ON (t.id = x.id) AND (t.approved = 1) AND (t.ip_address != '$exclude_ip') LIMIT 1; * I could almost live with the slow speed of an order by rand() but I find it has a less than even distribution. While it indeed may be very random, I am looking for a pretty flat response in distribution of returned records over time. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Age old order by rand() issue
I have seen nicer fast random row implement, but that will work. Do you happen to have a snip of it, the one I have seems to lean pretty heavy as far as I can tell, and on occasion, though rare, also sends me an empty result set. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Age old order by rand() issue
List search seems to return 0 results, and I am a bit stumped. Getting a more optimized order by random with 1 record... I found a snip online that works, but seems to return an empty on occasion, and I am not sure why: SELECT storage_path, image_md5, id FROM images JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = 1)) AS id) AS r2 USING (id) WHERE approved = 1; I really do not get this, SELECT CEIL(RAND() will always return 1 will it not? Any idea why I get an empty result set at times? I then managed to rig this together: SELECT * FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id = x.id LIMIT 1; This works, but I get an odd result, in that the id column is listed twice, once at the beginning, where it is in the table, and once at the end. Duplicate values of course. If I narrow the * to a real called select, such as SELECT id, storage_path, image_md5 FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id = x.id LIMIT 1; - FROM images WHERE approved = 1) AS x ON t.id = x.id LIMIT 1; ERROR 1052 (23000): Column 'id' in field list is ambiguous I can not seem to get past that error, and would like to call just the columns I want. Or, if someone has a better random record return select, I am all ears. Thanks so much. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Design decision
I have an a table of objects, attached to those objects are keywords. Users submit the keywords to the objects. Currently, I chose to keep a hit count on the keywords, so if a duplicate keyword is supplied, a counter is incremented. I thought this was a good idea, as it keeps the number of rows in the keywords table to a minimum. However, this is a user login based system, and with the above, I lose the ability to track which users sent in which keywords. So I can move to the keywords table storing duplicate keywords, and each keyword will get a user_id attached to it, but, that table will grow. Fast. 1000 objects, each with 10 keywords only = 10,000, I could hit many millions very fast. I could toss in a third table, and relate that to the user_id and keyword. However, I am inserting in one go, and that could be upwards of 30 or more inserts, taking too long. Anyone got any suggestions? Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design decision
Scott Haneda wrote: I have an a table of objects, attached to those objects are keywords. Users submit the keywords to the objects. Currently, I chose to keep a hit count on the keywords, so if a duplicate keyword is supplied, a counter is incremented. I thought this was a good idea, as it keeps the number of rows in the keywords table to a minimum. However, this is a user login based system, and with the above, I lose the ability to track which users sent in which keywords. Why do you need this info? Is there a reporting need or something else you need to keep this information for? Ie when are you going to need to know who inserted a particular keyword? Because it is a semi-public system, every user is allowed to supply keywords to other users objects. If some not so nice person decides to paste in a list of words to 'game' that object up the ranks, I want to know who, and be able to take action. Looking over the php now, thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
single line inserts with on duplicate key
Is it possible, in single-line inserts, with on duplicate key clauses, to
get back a list of last insert id's for what was inserted?
I get strange results, just one single insert id, which makes sense from the
perspective of what was just inserted, however, I need to know what the
returned insert id is for each of an arbitrary amount of single-line
inserts.
Defining what I am calling single line insets, I mean:
INSERT INTO foo (a, b, c) VALUES ('x', 'y', 'x'), ('x', 'y', 'x'), ('x',
'y', 'x'), etc etc
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X binary installer
Looking for the OS X 4.1 binary package installer, I can not seem to locate this on the site, I just find a source style, and a tar style. I am pretty sure in the past, I was able to have a double clickable installer, and it had a preference pane for starting and stopping the service. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hypothetical design question regarding keyword searching
I have been looking at stock photo sites lately, started wondering how they are doing their keyword searched. Given a potential for millions of images, each with x keywords, I have come up with two approaches... Approach one Images table, with a parent id Keywords table, each keyword would be its own row, and also link back to the parent id. You could then search for the keyword, get the parent id's, and do a IN (...) search against the images, thereby pulling up the images that have those keywords. Potentially problematic as assuming million of images, and average 10 keywords per image, you end up with a keyword table that has 10's of millions of rows, along with lots of duplicate keywords. Approach two Use a link table, this resolves the duplicate keyword issue, and I am sure there will be many dupes. However, it adds a third table. This would complicate the JOIN query. Are there other approaches? Which approach would yield the best performance for growth issue? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables in update statement
Is it possible to use user variables in an update statement, I can find only scant docs on it, but they do not pertain to what I am trying to do. Given this select: SELECT user_id, order_status, quantity_chosen, month_price, each_price, sales_tax, sales_tax_rate, @NEW_each_price:=(each_price + .06) as NEW_each_price, @NEW_month_price:=(quantity_chosen * @NEW_each_price) as NEW_month_price, @postage_rate:=(0.30) as postage_rate, @cost_of_postage:=(quantity_chosen * @postage_rate) as postage, @taxable_price:=(@NEW_month_price - @cost_of_postage) as taxable, (@taxable_price * sales_tax_rate) as NEW_sales_tax As you can see, I am using variables to make some calcs, sure, I can do it long hand, but it gets long and confusing. Given the above, I end up with some vars like NEW_month_price, NEW_each_price etc, I would like to update ... SET price = NEW_each_price, but of course, that does not work. Suggestions? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do you reference custom column names
SELECT 1+1 as foo, 2 as bar, foo+bar This will not work, but I think you can see what I am trying to do. I need to run a pretty hefty update on a database, and there are some pretty heavy calculations I will be doing. The result of many of those, needs to be further used to make updates on other columns. Can someone suggest a method to do this so my SQL is not so non-manageable? Something like this: SELECT price*tax_rate+something-other Then I need to use the result of the above calc right away in some other field, which would be: SELECT price*tax_rate+something-other, ((price*tax_rate+something-other)+.6) Where what I really want to do is: SELECT price*tax_rate+something-other as foo, (foo+.6) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
STEP 1: Create the root of the image name
update staff set imgName = Lower(CONCAT(Left(last,6),Left(first,2)));
STEP 2:
How do I delete spaces in the imgName?
STEP 3:
update staff set webLine = CONCAT(tdimg src='images/,imgName,.jpg'
width='100' height='125'br clear='all' /,first, ,last,/td);
Thanks in advance for your time spent in reading or responding.
Personally, I would move your html and string parse logic into php, rather
than in mysql, but that is up to you. If you want to do this in mysql..
SELECT REPLACE('De Long', ' ', '');
+-+
| REPLACE('De Long', ' ', '') |
+-+
| DeLong |
+-+
1 row in set (0.00 sec)
You could also do..
SELECT REPLACE('De Long', ' ', '%20');
++
| REPLACE('De Long', ' ', '%20') |
++
| De%20Long |
++
1 row in set (0.00 sec)
Which will url encode the space, which will allow perfectly for spaces in
filenames on a web server.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
(1) I thought it might be quicker than Perl or PHP. Correct me if I'm wrong. (2) I have read a short description of Triggers, and I thought these three lines of code might be an excellent AFTER INSERT trigger. (I don't know enough about Triggers yet to know if they'll even take multiple lines of code however.) (A) Could this be a Trigger? and (B) Would it be worth doing? (3) I've written plenty of Perl and PHP code that concatenates fields and builds XHTML cells and rows. I thought it might be interesting to build the rows inside the database table and have my PHP do nothing but count MySQL records in order to know when to open and close the XHTML table rows. I think things just start to get messy over time. One day will come when you want to search something in the database, and you will then have to accommodate that search with more hacks in order to ignore the html. Further, as time goes on, say you wanted to make some design changes to your html, you are now faced with constructing a rather complicated method for updating all your old records to the new html. If the html logic is pulled out into the php/web front end, you change in one spot, the site sees those changes on all pages. Databases are used to store data, I do not really see html as data so to speak, at least not important data. Even storing a path to an image is something I would stay away from, just store the image name in a field, and call the path out in your html. This in large part is my opinion, but I do tend to find most will agree with it. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace, Substitute, Delete
Instead of individual replacements, as in ...
SELECT REPLACE('De Long', ' ', '');
would this global approach work?
SELECT REPLACE(imgName,' ','') FROM staff WHERE imgName REGEXP ' ';
I just used that as an example. What you are doing is fine, you put the
field name in the first argument of the REPLACE() function. You no not need
to add the where imgname REGEXP part at all.
SELECT REPLACE(imgName,' ','') FROM staff
That should suffice, unless you have some other limiting factor you want to
toss in like :
SELECT REPLACE(imgName,' ','') FROM staff WHERE imgName LIKE '%d'
You get the idea, that would select all image names that end in 'd'
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bin logs and mysql 4
Running mysql 4, just poked into data and see I have gigs and gigs of hostname-bin.xxx log files. How does one maintain these, can someone point me to relevant data on what to do about drive space being lost to these? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged safely with a single keystroke :-) I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Yes -- sorry for being so general. You can use the binlogs for a)
replication b) replaying changes since your last backup so you get
point-in-time recovery. If you have no replication slaves, just delete
everything older than your latest backup. You can just use 'rm'. If
you use PURGE MASTER LOGS BEFORE, it's a bit easier than cron because
you can do it across all platforms easily. On UNIX of course, you'd use
something like
find /var/lib/mysql/data -name *.bin -mtime +7 -exec rm {} \
(My find syntax is guaranteed to be wrong there... don't run that as I
typed it).
But if you do it via SQL, you don't have to mess with this.
Thanks!
So, I take it since I do not have a slave at all, I could safely just
disable this feature altogether?
If I do not need point in time recovery, and the once every 12 hour dump I
do across all databases is ok with me, I suppose I can just disable said
feature? Heck, some of these boogers are a GB each :-)
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL e-zine
Hey everyone, I have been considering putting together a e-zine for MySQL as my way to give back to the community. I was curious as to what type of interest there would be in this. I am thinking something quarterly to start with and probably 15 - 20 pages. Nothing huge. Topics would vary from everything like standard DBA stuff like backups, replication, system architecture and such to stuff for the developers out there..php, perl, python whatever. My background is more of a DBA so I would definitely need some input/articles for the developer side of things. It is just funny that in this day and age there is no magazines specifically for MySQL. I even saw a php magazine at the bookstore the other day! I can do the layout in QuarkXpress and generate pdfs that I can host on my website. Any thoughts? Any desires to contribute? Anything on the topic of actual queries I would skip, those are narrow and finite questions, usually specific to a certain application, and quickly answered on the list. I would cover what is more mysterious to most, which is the underbelly of mysql. Today there was good dialogue on the bin logs, that I think is stuff that helps people. Demystifying all the .cnf options, how to optimize for load, test the load etc. Steer clear of high end features like replication and the like, things that generally, if you are worried about them, your app is doing well enough you should be around knowledgeable people already. For me at least, it would be more about internals, that the step by step of how to do a join. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible in sql, or just move it to a language
This is a one off I need to do Table1 has email addresses in it, table two also has email addresses in it. table1 represents bounced emails that need to be purged from the database. What I would like to do, is mark a field in table2, called needs_purging to true, when there is a match from table 1 Should I just run a loop in my language of choice, or is there a join I am not seeing here? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choose a random record from a list of duplicates
zv Green wrote: Hello all, What I want to do is select all the records from the table but where there are duplicate entries (based on say, the surname and postcode fields) pick a random record and then ignore the rest. If you want to pick a random record, you can do ORDER BY RANDOM LIMIT 1 or so. (warning, untested) And I think you will want to group by surname and postcode in order to filter out the duplicates. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql, PHP and Persistent mode
Hello, I have been using mysql_connect in a script that display a lot of thumbnails for an album. Each thumbnail is displayed using the code: IMG SRC=thm.php?id=some_id ALT=some title thm.php use a mysql_connect to the database to access the info about the picture based on the id. This worked fine. However, the SQL server is located on a different network than the web-server with a firewall between. When I looked into the firewalls log I saw that there was large amount of new connections when someone accessed the page where all the thumbnails was displayed. I then changed mysql_connect to mysql_pconnect on the scripts and viola... the amount of new connections to the SQL server dropped to only two. Good, I thought. But later I discovered that the SQL server had a large amount of childs running. I had 50-60 mysqld running on the system. This number was constant to below 10 before I changed to persistent mode. Any settings in the config file for the sql-server I need to be aware of? I'm using MyISAM tables . I would have to say, this is suboptimal, and perhaps a little OT, but here goes. If you have 50 images, you are going to make 50 selects to your database, depending on how you connect, you may make 50 connections, or not. What you should do, is grab the 50 images ahead of time, in one select, and use some php to iterate them and display them. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql, PHP and Persistent mode
What you should do, is grab the 50 images ahead of time, in one select, and use some php to iterate them and display them. Second, your suggestion is not an option in my case, since the image and the thumbnails are not stored in the database. The database contain image id, image attributes (exposure++) and name of the image and thumbnail file. That's all the data you need, I am not saying to pull the image out of the database, I am saying to pull the file path out of the database and put it in your html img src tag. Its the difference of making one call to the database, versus x, where x is as many thumbnails as you have, not to mention, x also represents that many http calls to your server, and also, that many times php must process that file. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cloning or duplicating a record
Can someone tell me how to take one record and duplicate it exactly, with the exception of the pk, which I want to increment as usual. I think I need insert select, however, I don't want to name all the fields, as it would break over time, if I add or subtract fields. I seem to recall using a wildcard tossed me up an error about a duplicate key. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cloning or duplicating a record
how about: insert into table_name select * from table_name where select criteria is the primary key an auto sequence? Yes, pk is on auto increment -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cloning or duplicating a record
how about: insert into table_name select * from table_name where select criteria is the primary key an auto sequence? This is what happens when I try: insert into logbook select * from logbook where id = 1; ERROR 1062: Duplicate entry '1' for key 1 -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cloning or duplicating a record
YOu'll have to list the fields explicitly, except for the primary key. For example, if your table has columns: id (PK) data_1 data_2 data_3 you should be able to do insert into table_name (data_1, data_2, data_3) select data_1,data_2,data_3 from table_name where id=1 The insert failed because you were - as the error message said - trying to insert a record with an existing primary key, which is unique. Check mysql manual for more info on syntax of insert command. I figured as much, its just two sets of fields I will have to manage, which is why I was hoping there was a sneaky SQL way to deal with it. Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
Again, I know this is not necessarily a MySQL specific question, and sorry for getting off target, but this is a pretty smart and experienced bunch, and I'm sure that someone out there will be able to help me. We have a web application in which we have a mass e-mail function. It allows the user to compose a simple text-only e-mail and send to everyone in our database (MySQL). Problem is that our e-mail server seems to be getting overloaded, and it just shuts down, causing an error. We're using ArgoSoft Mail server, which works very well for our normal needs. We do not want to change to Microsoft's Exchange Server. I hear it's expensive, and difficult to set up and get working properly. I was wondering if anyone knows of any alternative mass e-mail options. I don't want to use servers that spammers use, because first, and foremost, this is NOT spam, and second, some recipients may have these servers black listed. What other alternatives are there? How many messages are we talking here? Perhaps create a server side script, that sends the emails in chunks, then sleeps a little, and sends another chunk. I would also try to unorder the email addresses, so that large groups of yahoo and aol etc emails do not hit the outbound queue at the same time. They tend to look down on that activity, and throttle you back, making it take even longer to get your messages out. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
You might put special-case conditons on specific domains, but actually, you're much better off ordering by domain because your server will end up sending _ONE_ message addressed to whatever number of recipients at that domain. If your list is truely massive, your MTA may be incapable of sorting the recipients itself: a good MTA will batch by recipient domain. An equal probability is that you're actually generating INDIVIDUAL messages (one per recipient), rather than messages with a BCC: recipient list, in which case, my advise would be to switch to BCC: addressing, but if that isn't an option, look at a cascaded MTA queue (messages which don't deliver on the first try get sent to a secondary queue which won't retry right away - and those which fail to deliver from that queue get moved to one that takes even LONGER) - a fairly typical (read: default Sendmail setup) queue retry is every 15 minutes, 24/7 --- if you've got a few hoser domains in there, they can stuff you up quick. Check your server logs to see if the domains you're emailing to are trying to perform callbacks (GTE and it's affiliated telco domains are/were doing this for some time - this is the same bunch of idiots who've blocked many european IP ranges from sending mail to them, and such sites have to relay through other hosts in order to deliver to GTE customers). Any domain that does this might be a candidate for being added to a special case handler to be shuttled to a low priority queue right off the bat. Might I suggest you set up a database for the special conditions and the queues you'd place them in? g Good comments, also, there is the option of simply interfacing mysql and your scripts with mailman, which is really one of the better ways to send mass emails, list serves pretty much have it down these days. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I don¹t have sub queries, can someone suggest alternatives
DELETE from cart WHERE product_id NOT IN( SELECT id FROM products where status = 'enabled') AND user_id = '90' The above is what I would like it to do, though I can not test it as I do not have access to a mysql that supports it. Aside from two queries, can someone perhaps show me how to run the same as above but without the sub query? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don¹t have sub queries, can someone suggest alternatives
DELETE from cart WHERE product_id NOT IN( SELECT id FROM products where status = 'enabled') AND user_id = '90' The above is what I would like it to do, though I can not test it as I do not have access to a mysql that supports it. Aside from two queries, can someone perhaps show me how to run the same as above but without the sub query? I am pretty sure I worked this out with the following: DELETE cart2.* FROM cart2 c LEFT JOIN products p ON c.product_id = p.id WHERE c.user_id = '90' AND p.id IS NULL -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join with additional table, stumped
Forgive the mess I present you, this is a strangely done site with a even
stranger structure and methodology that I am trying to work with.
The basic idea is one website has multiple websites in it. So if you place
an order with website A, orders_A is where the data is stored, if you place
an order with website B, orders_B is where the data is stored. The site
changes from A to B based on the url. Anyway...
SELECT o.id, u.industry,
u.email, u.b_first_name, u.b_last_name,
t.created
FROM orders_npfd AS o
INNER JOIN users AS u
ON (o.user_id = u.id)
INNER JOIN transactions as t
ON (t.order_id = o.id)
WHERE t.type in ('first_charge', 'recurring')
ORDER BY t.created DESC;
This basically gives me a list of all orders that I want to see from the
orders_npfd table, works as I need it to. Now, I need to add in a second
table, for the orders from the other table. Table structure is more or less
the same, at least, the data I am selecting. So the second table is
orders_npfs - note the 's'
How can I add that in so I will get results out of that table as well?
Second problem, each order table of course gets an id, or orderId, in the
above example, it is o.id and linked to t.order_id. The problem is, o.id is
autoinc PK and will/can overlap with the id's from the either of the orders
table.
However, users u.id will never overlap, nor share table data, so I think I
can use that in my condition to make sure there is no pollution of orders?
If I use a join in the above to get the second table data in there, I would
have to add in more x.foo items to my select, which really will not help me.
I am sure this is pretty confusing, if anyone has any suggestions, aside
from rethinking the design entirely, I would most appreciate it.
Maybe select the contents of both tables into a tmp table, and use that as
my join table above?
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this privilege case even possible
One database, multiple tables. I need to limit access to one table per user, but also allow all other tables to be accessed by all users. For example: Say I have 5 users: usera, userb, userc, userd, usere Table 1 - all 5 users, select, inset, update, delete Table 2 - all 5 users, select, inset, update, delete Table 3 - only userc, select, inset, update, delete When I add in a new table, such as table 4, I would like it to be all 5 users. So basically, I just want to lock out one table, and allow only one user to that table. I have tried several ways, and read over the docs, and I am just not seeing how to make this work. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie needs help
I'm going throught some tutorial about uploading and displaying images files. But the display script isn't working. Here's what I have: I think you may want to bring this to a php based list, not a mysql one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table specific privileges
MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table specific privileges
I keep getting the error: ERROR 1147: There is no such grant defined for user 'username' on host 'host.example.com' on table 'orders_npfs' Sean, As http://dev.mysql.com/doc/refman/5.0/en/revoke.html states, REVOKE ALL ON `database`.`table` FROM 'user'@'hostname'; MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table specific privileges (BUMP)
Sorry to push this back out to the list, I am stumped, and the docs are not leading me to an answer. One users reply was close, and I had tried it, but it generates an error, which is also posted in this thread. Thanks everyone, original message follows: MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table specific privileges (BUMP)
Hi Scott, One would think that you should be able to accomplish what you are looking for by changing rows in table - tables_priv in mysql database. And using flush privileges when you done. So does this mean my database privs of select, insert, update, and delete supercede any table specific ones? I am trying to not have to add new table privs every time I add a new table. That would be kind of a hassle. My goal is to have a set of tables, that are accessible to 10 mysql users, there will then be 10 additional tables in that database, each user is allowed to access only a certain one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
Brian Dunning wrote: My understanding is that SCSI has a faster transfer rate, for transferring large files. SCSI is better for EVERYTHING except your budget. Faster for large transfers, small transfers, seek times, and most especially it handles requests from multiple threads much better. Almost everything, they have not hit that capacity issue yet, they are all generally much smaller that non SCSI. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automatic timestamp
People, I have the following table: DROP TABLE IF EXISTS bid; CREATE TABLE bid ( bid_id int(11) NOT NULL auto_increment, bid_proj_name varchar(100) NOT NULL default '', bid_prop_name varchar(100) NOT NULL default '', bid_amount varchar(20) NULL default '', bid_sub_name varchar(100) NOT NULL default '', bid_sub_desc varchar(100) default '', bid_sub_amount varchar(20) NULL default '', bid_winner tinyint(1) NOT NULL default '0', bid_date TIMESTAMP, PRIMARY KEY (bid_id), UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name) ) TYPE=MyISAM; and I am trying toload a file with the following data: ,Construction Management,RCG Consulting,,Orly-Belle ,Construction Management,RCG Consulting,,American Engineers I am using phpmyadmin and the date always gets enter as 00 What do I need to do to force the current time stamp? I am using mysql version 4.0.21-standard IIRC, you need two timestamp fields for auto to work, default timestamps for the first timestamp column will be ignored, but the first column will get auto timestamp in a table that has more than one timestamp column. I usually set up two, updated and added, and I always set the added field to NOW() It explains it pretty well in the docs here http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leading zero where strlen 5
If this is so, IMNSHO it was a wrong decision: Zip codes are character strings, even though they may (in some / many countries) consist of digits only. Use a char (n) column for them, with n varying by country. So did I, as in the char(n) however, the import script I wrote, in the language I was using, being pretty lose at times, has a habit of turning strings to ints, so 01 is 1, and that's what gets sent off to the database. It was after I imported 40,000 of these, that I noticed. I did not want to re-parse all those again, as it takes some time, so I just ran the update and all was well. Thanks everyone. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Leading zero where strlen 5
I need to update a column, if the string length is less than 5, I want to add leading zeros to it until it has 5. These are zip codes, I think there are no 00 leading zips, so most should all be four chars long. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leading zero where strlen 5
J.R. Bullington wrote:
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
How about
UPDATE tbl_Name SET ZipCodes = right(concat('0',ZipCodes), 5) WHERE
length(ZipCodes) 5
Works, perfect, thanks to both of you.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Records in front of and behind another record
Mysql 4 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or so, however, there are gaps in the numbers, so not purely sequential. I am faced with selecting one record from the dataset, that's simple, however, before that select, I need to make sure the record is there, and if it is not, find the one either just before it, or just after it. Whichever is closest. If they are the same, lean on either one, I don't really care. Any suggestion on this one would be appreciated, I can do this with 3 separate queries, but that is taking too long, since I have to do this with two separate datasets and shove this all back out a browser to the user. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records in front of and behind another record
Mysql 4 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or so, however, there are gaps in the numbers, so not purely sequential. I am faced with selecting one record from the dataset, that's simple, however, before that select, I need to make sure the record is there, and if it is not, find the one either just before it, or just after it. Whichever is closest. If they are the same, lean on either one, I don't really care. Any suggestion on this one would be appreciated, I can do this with 3 separate queries, but that is taking too long, since I have to do this with two separate datasets and shove this all back out a browser to the user. Clearing this up a little, I have data like this: 3, 4, 5, 8, 9, 10 If I am looking for 6, it is not there, I want back 5 and 8, in this case, 5 is closest, so I would like 5 back, but both are ok, as I can use server side code to determine the closest. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records in front of and behind another record
Mysql 4 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or so, however, there are gaps in the numbers, so not purely sequential. I am faced with selecting one record from the dataset, that's simple, however, before that select, I need to make sure the record is there, and if it is not, find the one either just before it, or just after it. Whichever is closest. If they are the same, lean on either one, I don't really care. Any suggestion on this one would be appreciated, I can do this with 3 separate queries, but that is taking too long, since I have to do this with two separate datasets and shove this all back out a browser to the user. Clearing this up a little, I have data like this: 3, 4, 5, 8, 9, 10 If I am looking for 6, it is not there, I want back 5 and 8, in this case, 5 is closest, so I would like 5 back, but both are ok, as I can use server side code to determine the closest. I am thinking UNION and two SQL queries would do this, how is UNION optimized, is it more or less the same as running two selects? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records in front of and behind another record
OK, that's clear. Sorry about the bervity, ill clear this up below. Scott Haneda wrote: I am thinking UNION and two SQL queries would do this, how is UNION optimized, is it more or less the same as running two selects? Usually, but a UNION of what two queries? I won't comment on the efficiency of a query I haven't seen. Here is what I was thinking: (select zipcode FROM zipcodes_head_of_house WHERE zipcode = '94949' ORDER BY zipcode ASC LIMIT 1) UNION (select zipcode FROM zipcodes_head_of_house WHERE zipcode = '94949' ORDER BY zipcode DESC LIMIT 1) This seems to give me either one of two records, in which case, its pretty simple to find the closest one. Here is my table structure, there is a lot more auxiliary data to it, but these are the main bits that matter. describe zipcodes_head_of_house; ++---+--+-+--++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--++ | id | int(11) | | PRI | NULL | auto_increment | | zipcode| char(5) | | MUL | || | latitude | double(12,6) | | | 0.00 || | longitude | double(12,6) | | | 0.00 || | created| timestamp(14) | YES | | NULL || ++---+--+-+--++ This can be done in one query. You didn't give any details, so I'll make them up. The table is named scotts_table, the numbers are in the column named val, and the target value is 413. I'll use user variables for clarity, but they aren't necessary. For each row in the table, the distance from that row's val to the target value is the absolute value of the difference between val and the target value. The row with the smallest distance is the one you want. Hence, SET @target = 413; SELECT * FROM scotts_table ORDER BY ABS([EMAIL PROTECTED]) LIMIT 1; select zipcode from zipcodes_head_of_house order by abs(zipcode-94999) limit 1; +-+ | zipcode | +-+ | 95001 | +-+ I know 94999 is not in the database, and I get back 95001, which should be the closest match, using my UNION to test it: mysql (select zipcode FROM - zipcodes_head_of_house - WHERE zipcode = '94999' ORDER BY zipcode -ASC LIMIT 1) - UNION - (select zipcode FROM - zipcodes_head_of_house - WHERE zipcode = '94999' ORDER BY zipcode - DESC LIMIT 1) - ; +-+ | zipcode | +-+ | 95001 | | 94979 | +-+ 2 rows in set (0.00 sec) And there you are, the 95001 is of course the closest one. I think this is it, this works well, and fast for me. Of course, that's a full-table scan with a filesort, so it's not very efficient. We can improve on this, however, if we know the size of the largest gap. For example, if we know that the largest gap is 26, we can do the following: SET @target = 413; SET @range=26; SELECT * FROM scotts_table WHERE val BETWEEN (@target - @range) AND (@target + @range) ORDER BY ABS([EMAIL PROTECTED]) LIMIT 1; I could probably figure it out, at some point, but right now, I have no idea what the largest gap is. In this case, mysql can use the index on val (You do have an index on val, right?) to choose the few rows near the target value, before performing the filesort on just those few matching rows. I am pretty sure I do, I will check though. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: summing of my distance query
Scott, I think you want something like this, then, assuming you still want to limit by radius from a given ZIP. SELECT b.zipcode, sum( b.inc_level1 ), sum( b.inc_level2 ), FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 AND (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) = 10 I have been bashing my head on that a bit, right now I get ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
summing of my distance query
Mysql 4.0.18 ++---+--+-+--++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--++ | id | int(11) | | PRI | NULL | auto_increment | | zipcode| char(5) | | MUL | || | inc_level1 | int(11) | | | 0|| | inc_level2 | int(11) | | | 0|| | inc_level3 | int(11) | | | 0|| | inc_level4 | int(11) | | | 0|| | inc_level5 | int(11) | | | 0|| | inc_level6 | int(11) | | | 0|| | inc_level7 | int(11) | | | 0|| | latitude | double(12,6) | | | 0.00 || | longitude | double(12,6) | | | 0.00 || | created| timestamp(14) | YES | | NULL || ++---+--+-+--++ My query is this: SELECT b.zipcode, b.inc_level1, b.inc_level2, b.inc_level3, b.inc_level4, b.inc_level5, b.inc_level6, b.inc_level7, (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) AS distance FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 GROUP BY distance having distance = 10 Describe yields: +---+--+---+--+-+---+---+--- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+--- + | a | ref | position | position | 5 | const | 1 | Using where; Using index; Using temporary; Using filesort | | b | ALL | NULL | NULL |NULL | NULL | 38623 | | +---+--+---+--+-+---+---+--- + Its pretty quick, even 500 miles is under half a second, if there is anything I can do to add indexes and such, let me know, the main issue is, in a 500 mile search, I don't need back 1839 rows as in this case, I need just one, where each of the b.inc_level1-7 are added up, just adding in SUM(b.inc_level7) still yields me 1839 rows in this case. To do this at the application level is painfully slow, I can shove the data into a temp table at get it pretty easy, but I thought there would be a simple way. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: summing of my distance query
Scott, can you expound on what 1 row would be returned, ideally? The one with the shortest distance? Or a row with the sums of inc_level1 ... inc_level7 ? Looks to me like you're trying to locate all the ZIP codes within a given radius of (in this case) ZIP 94949 with the query below. Yes/no? Dan This is some older code, that did just that, find all zip codes in a certain radius. I need to modify it somewhat. You are correct, I want the sum of Let me show you... SELECT b.zipcode, b.inc_level1, b.inc_level2, (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) AS distance FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 GROUP BY distance having distance = 10 +-+++--+ | zipcode | inc_level1 | inc_level2 | distance | +-+++--+ | 94949 |164 |156 | 0.00 | | 94903 |227 |231 | 3.241369 | | 94947 |268 |323 | 3.393376 | | 94945 |132 |152 | 4.120687 | | 94960 | 60 | 77 | 5.588795 | | 94930 | 55 | 62 | 5.847434 | | 94973 | 88 | 70 | 6.533081 | | 94901 |339 |317 | 6.603527 | | 94904 | 51 | 68 | 7.685091 | | 94963 | 22 | 19 | 8.085156 | | 94946 | 21 | 20 | 8.495255 | | 94939 | 38 | 49 | 8.640175 | | 94933 | 48 | 28 | 8.865090 | | 94938 | 30 | 16 | 9.367796 | | 94925 | 46 | 65 | 9.750440 | +-+++--+ That gives me 15 rows, but I only need one, which would be the total of each of the income level columns, distance is not important to me, that's the one row I want back -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing large data sets
I have two chunks of data to import, one is in this format: 01001 - AGAWAM, MA,01001,0,0,291,249,0,42.070206,-72.622739 Where it is comma sep and partially quoted The other is in this format 99502 ANCHORAGE, AK,256,265,1424,1962,1131,528,643,6209,99502,61.096163, -150.093943 Where everything is in quotes and comma sep Can someone tell me, how to rapidly import all this data into a table. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding records that do not exist
Mysql 4 still version: 4.0.18 I have users and transaactions, the key is user_id Somehow, a bunch of users were made, and there is a total lack of a transaction record at all. I need to fix this, which means finding the user_id's of those who are not paired with a transaction record. Thanks for the pointers. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aborting a greedy querry from the command line
Sometimes I will issue something, slip of the fingers, and mysql will chew on it for hours, the best I can do is control-z to get out of it and log back in. But, I just learned these processes are still running, and I have to go in and kill them by hand. Is there any easy way to abort from the command line and have it really stop that query? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One to many meetting specific conditions
4.0.18-standard-log I have a very basic one to many relationship, accounts and transactions. There is only one account per users, but of course, there can be x transactions. These are once a month charges for billing. I need to be able to select all accounts where next_charge_date = NOW() That's the easy part, I get all the records I want. However, some of those get charges through one gateway, and some get charged through another. The transaction table has a field called merchant, lets say it can be bankA or bankB. So, I need a list of accounts, where none of its many transaction records has the merchant bankA. I also need to do the same for bankB This allows me to then run each chunk of records through the correct gateway processor. Of course, this join will result in x records where x = the number of transaction records, so I just group by the account id to get only one record back. I can script this, it is going to be slow, I would like to see how I can do it in SQL. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning records in a circle
Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. I don't know any way to do this directly with SQL, but I could be wrong, stored procedures may help you here, I do not use mysql 5, so I have not played with them yet. I would probably do this with a second table, and some SQL in the middle of it all to make it happen. Your second table would simply store the last id that you selected. So, you select from your second table, to get the last id you retrieved. Them you select from the primary table, where id retreived_id limit 2, once you have that, take the ID from the last record in the 2 you just got back, and insert/update that data into your second table. Make sure you add in app logic to deal with cases where the second table has never been inserted into, also when you wrap your record set, it will need to be reset, but I think you get the idea. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One to many join with only one result
Mysql 4.x I have a one to many case, key is `id`, in this case, I have tables users and transactions. A user can have 1 to x transactions, each transaction has a status to it as well. I want to be able to get users where there is a transaction status of 'wells', however, there can be more than one of those transactions, all I care is that it exists and I just want one unique user record back. Ie: if I have 100 users, and each user has 2 transactions of type 'wells', I want to select those, but a regular join will send me back 200 records, where I want only 100 Can someone point me in the direction of the correct join to make this happen. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks What veriosn of mysql, timestamp handling has changed from one version to the next? Perhaps you have others in your table, I think only the first is updated, at least, in pre 4.1 days, after that, check the docs for the correct behavior. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Host denied errors
Little confused, can not seem to locate the docs on this. Trying to set up mysql to allow a remote webserver to talk to it, using a user/pass/host setting with host set to allow anything, of course works. If the IP I am connecting from has a valid PTR, I can use the hostname as well, and that works. In this one case, I do not have the ability to get the client to get a PTR set up on the IP I want to connect from. I tried putting in the IP address, and it still blocks it, can someone tell me how mysql authenticates this data? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to design one to many queries
Been wondering this for a while now, I can explain how I do it, but I am sure there are better ways. I have products and attributes, for example: Product A nice sweet fast funny Attributes are arbitrary, attributes are in a separate table. Yes, I could add fields for the attributes to the products table, but that does not allow the client the flexibility to add new attributes on the fly. Generally, in the products table I have a text filed where I store the id of the attribute, space separated, for example: attributes: 1 5 23 56 3 6 The trouble is, when I want to find all products where the attribute id is 5 and 23 and 3, I have to build it up like so: Select prod_name from products where attribute_id like ' 5 ' AND etc etc etc which also leads to issues with leading and trailing spaces on the first and last attribute id's Suggestions? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleted sock file, how can I recover
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' I accidentally deleted the above file, I can not connect any new connections in the CLI, old stuff seems to still be ok. I can not seem to shut down the server, as that file is needed. Any way I can recreate it, and what do I put in it? I can force kill the server and I am guessing it would all come back up ok, but there must be a gentler way? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing? What's
incorrect? Please help us to help you.
Orders does not have a product_id column.
Let me see if I can explain this again, more better :-)
We have orders and order items, so for every orders, there are 1 or more
order items, pretty basic. This SQL gets me almost what I want:
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
However, there are
mysql select count(*) from products;
+--+
| count(*) |
+--+
| 109 |
+--+
1 row in set (0.00 sec)
So, 109 products in the products database, the first SQL above, will give me
back a row for every order item that meets those criteria, however, it does
not list products that were not ordered.
If I changed the first SQL to a date 10 years ago, I would get 0 rows, I
want 109 where the sum() is all 0.
Basically, my client is wanting to see what products are selling, and which
ones are not, in a certain date range, and I need to add in the status to
limit it to only certain orders.
Running these three SQL's does what I want, with a temp table, but I find
the solution kinda strange, and know it can be done in one go:
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
Is this what you mean?
SELECT
p.prod_name,
count(oi.product_id) AS mycount
FROM ORDERS AS o
INNER JOIN products ON o.id=p.id
LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
GROUP BY oi.product_id
ORDER BY mycount;
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products with
a zero count. This tells me those products have not been ordered ever, but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2005-01-01 00:00:00 AND 2006-02-22 23:59:59)
GROUP BY oi.product_id
ORDER by qty ASC
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
You just need to invert a couple of things...
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
AND o.created BETWEEN 2005-01-01 00:00:00 AND 2006-02-22 23:59:59
AND o.status not IN ('cancelled', 'pending', 'ghost')
LEFT JOIN order_items as oi
ON (o.id = oi.order_id)
GROUP BY p.id
ORDER by qty ASC
That should give you a list of all products and a count of how many have been
ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status of the
order is neither 'cancelled', 'pending', or 'ghost'.
The think to remember is that an ON clause can be as complex as a WHERE
clause. The ON clause also determines which rows of which table participate in
a JOIN. In this case the only table to be affected will be the one on the
right side of a LEFT join (in an INNER join both tables are filtered). So you
keep all of your products visible (as declared in the FROM clause) and
optionally associate with each product an order and optionally past that to an
order_item.
HTH!
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
Not sure if this is related to my version of mysql, or something else?
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
Maybe this is where your problem is - you're joining to orders but
referencing order_items in your join condition. Shurely shome mishtake?*
I am not sure, but I think that is what I want. If it is of any help, I was
able to do this with what I would call a hack, and some temp tables, the
result is what I am after, however, I am not 100% happy with the method I
used.
// first make a selection of the data I want
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-22 00:00:00 AND 2006-02-22 23:59:59)
GROUP BY oi.product_id
// select all products, set qty to '0', this fills in the gaps where there
// are zero item products
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
// re-seslect the real data, using group by to merge the duplicates
// out of the select
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inner join with left join
Got myself a little stumped here, 4.0.18-standard
Three tables in this mess, orders, order_items and products.
orders.prod_id = order_items.prod_id = products.prod_id is how I relate them
all to each other.
order_items has a quantity field.
I need a report that shows me all the products with a sum() for each, but
only if the status of the order is NOT IN ('cancelled', 'pending', 'ghost')
This gets me pretty close, but does not mask out the orders that have the
wrong status, as I do not know how to add in the join on orders
SELECT p.prod_name, count(oi.product_id) as mycount
FROM products as p
LEFT JOIN order_items as oi
on (p.id = oi.product_id)
group by oi.product_id order by mycount;
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Semi-complicated delete
4.0.18-standard-log I have a table cart and a table products Key is as follows: products.id = cart.prod_id The problem I have is we have decided to store the users cart, so when they come back it is still in the same state they left it. Pretty usual stuff so far. Two things can possible happen that would make this bad: 1) product has been deleted 2) product has been disabled. Just before I am checking the user out, I want to fix this scenario with a delete statement. What I need to do is: DELETE FROM cart where cart.prod_id does not exist in the products table, or where 'online' = 0. There is a user_id that I match on as well, but that does not entirely matter to this question. Deleting where online = 0 is simple, but deleting where there is a lack of a matching product has me stumped. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joined delete where records may or may not exist
Hello, mysql 4.0.18-standard-log (yeah, I know, I need to update, the date format changes are a nightmare :-)) I have two tables, cart, and products, I need to do this style delete: First, I need to join the two tables on the cart.product_id = products.id and delete those records where products.online = 0 and here is the bit that I get stuck on, and where there is no matching products record. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting # of days until expiration
I have a DATE field that includes a date sometime within the past 30 days. I'm trying to show the number of days until the expiration date, which is 30 days in the future from the date in the field. I've tried a bunch of permutations of something like this: select (30 - SUBDATE(CURDATE() - datefield)) as expiration_days All my permutations give me errors. Can someone set me straight? I'm hoping to get an integer between 1 and 30 (30 day old records are purged separately). SELECT DATE_ADD(datefield, INTERVAL 30 DAY) from table; -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grand summary
I have two tables, order and order items. An order can have 1 or more order items. Important data in order is the `id` which is the key to link that order to the order_items table. Quantity of items is stored in the order_items table. I need to select the below data, and would like to do it in one select, and not use server side code to add it all up: Total Number of Orders (count order records) Total Products $ (sum of qty and price in order_items) Total Shipping $ (sum of shipping field in order record) Total Gift Card $ (sum of gift card price in order record) Total Tax $ (sum of sales tax in order record) Total Orders: $ (grand summary of total in order record) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grand summary
on 12/27/05 6:25 PM, Scott Haneda at [EMAIL PROTECTED] wrote: I have two tables, order and order items. An order can have 1 or more order items. Important data in order is the `id` which is the key to link that order to the order_items table. Quantity of items is stored in the order_items table. I need to select the below data, and would like to do it in one select, and not use server side code to add it all up: Total Number of Orders (count order records) Total Products $ (sum of qty and price in order_items) Total Shipping $ (sum of shipping field in order record) Total Gift Card $ (sum of gift card price in order record) Total Tax $ (sum of sales tax in order record) Total Orders: $ (grand summary of total in order record) Sorry for the noise, turns out I did not need this at all, and can just use sum() and no need for the join at all. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merging two tables
I need to do this just once... I have table zip_codes and table hardiness_zones In this case, the key will be the actual zip codes. hardiness_zones has two fields, zone_start and zone_end, these are all empty in the zip_codes table. I just need to move the data over, where the zip codes are ==. 4.0.18-standard-log UPDATE zip_codes, hardiness_zones SET zip_codes.zone_start=hardiness_zones.zone_start, zip_codes.zone_end=hardiness_zones.zone_end; Is that correct, and how can I test this before I do the real thing, aside from working on a copy of the table, is there something where I can run it and have it not really do anything, kinda like EXPLAIN, but not exactly. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
