Message from Picaso.Leonardo Most recent post: 8/24/2007 2 authors and 2 replies. Hello all That works faster in Mysql for creation of an index: CREATE INDEX or ALTER TABLE tab ADD INDEXThanks. read more about That works faster in sql for creation of an index?
Message from Neeper Most recent post: 8/1/2007 3 authors and 3 replies. I'm having trouble coming up with a query to count the number of total items each subcategory with the lowest cost (CPU item). I'd like to be able to display the total in my pages like so using PHP. Automotive (4 items) - Cars (2 items) - Trucks (2 items) - Vans (0 items) Electronics (6 items) - Digital Cameras (0 items) - Portable Audio (3 items) - Home Theatre & Projectors (0 items) - TV, DVD & VCR Players (3 items) - Sound Systems & Components (0 items)At the moment I am using 4 seperate queries in loops and I know there must be an more efficient and easier way. For example, I'd like to count the number of items there are under the Automotive category such as: Automotive has 4 items within it (under Cars, Trucks & Vans) 1) 1995 Nissan Pickup 2) 2002 Acura RSX 3) 2005 Honda Civic 4) 2007 Dodge Durango Also is it better, faster to run the query and store the results to a seperate table (ie. hourly using cron) and retrieve the total from that table when needed or is it quick enough to execute that query x times depending on the number of categories on that page. These are the 2 tables I have: Categories table (Using hierarchical data structure) +--------------+-----+------+------------------------------+ | category_id | lft | rgt | name | +--------------+-----+------+------------------------------+ | 1 | 1 | 22 | Categories | | 2 | 2 | 9 | Automotive read more about Help with counting total items using hierarchical data structure
Message from yaguirre Most recent post: 7/30/2007 4 authors and 13 replies. Hello All, I was searching for a response about this topic but none get the right answare, I hope I get help from you on this. :) I trying to do an insert using a stored procedure to rely the charge on the db but every type I run the call from my web page ussing PHP 5.X I get the same error. This is what I'm doing: ______________________________________________________________________________________________ $link = mysql_connect( DB_HOST, DB_USER, DB_PASSWORD, true, 131074); mysql_select_db(DB_NAME); $sql = "call spTestCreate('" . trim($Value1) . "', '" . trim($Value2) . "', '" . trim($Value3) . "', " . $Value4 . ", " . $Value5 . ", '" . trim($Value6) . "', '" . trim($Value7) . "', '" . trim($Value8) . "') ; "; $res = mysql_query($sql); if (!$res) { echo "Couldn't successfully run query (" . $sql . ") from DB: " . mysql_error(); } else { $row = mysql_fetch_row($res); $LastId = $row[0]; } ______________________________________________________________________________________________ If I run the call from the SQLyog or any other tool it runs prefect but that's not the case in the PHP. Any Idea or Solution is welcome. Thanks! read more about 2014 Commands out of sync; you can not run this command now
Message from amygrant1701 Most recent post: 7/25/2007 2 authors and 2 replies. Hi, I have done this before so I do not see what I could doing wrong here. I'm running mysql 5x on freebsd. I'm using the default data directory of "/var/db/mysql" In there I have several dozen mysql datasbases that are functioning perfectly. I am trying to add a database which will be stored on a different drive, therefore the entry in "/var/db/mysql" should be a symbolic link With mysql running I go... mkdir /sata2/database/d1 chmod 700 /sata2/database/d1 chown mysql:mysql /sata2/database/d1Now all I have to do is make a symbolic link and I should be set ln -s /sata2/database/d1 /var/db/mysqlChange the owner of the symbolic link chown -h mysql:mysql /var/db/mysql/d1 Doing a directory listing, I see: lrwxr-xr-x 1 mysql mysql 25 Jun 19 11:24 d1 -> /sata2/database/s1 So the symbolic is there and looks to have permissions good. I restart mysql. Mysql doesn't show the database "d1". It's as if the database is not there. Just for testing, I tried making a directory "/var/db/mysql/d2" via command line. OF course, since that is and actual directory, when restarting mysql, mysql see its. read more about Database as Symbolic Link - What am I missing?
Message from cmk128 Most recent post: 7/23/2007 2 authors and 2 replies. Hi If I use mysql to store the file. How can I make a link for the user to download that file? thanks from Peter (cmk128@xxxxxxxxxxx) read more about mysql to store file
Message from Patrick Most recent post: 7/23/2007 3 authors and 3 replies. Hi, Can anyone show me how to do the following: From a table I'd like to retrieve the following: company and websites. When I do a select on on this table i will get company 1 - website 1 company 1 - website 2 company 1 - webiste 3 company 2 - website 1 company 2 - website 2 etc. But what I'd like is: company 1 website 1 website 2 website 3 company 2 website 1 website 2 etc. Now I use the following select statement: $query="select company, domainname from DOMAIN, CLIENT_PERSONAL_DATA, USER where DOMAIN.idclient = CLIENT_PERSONAL_DATA.id"; Regards, Patrick. read more about select from table
Message from Blog Most recent post: 7/12/2007 2 authors and 2 replies. Discover the power of the Blog Blaster! Brandnew software revolutionizes the power of online advertising -never seen before!- BRAND NEW FOR 2007! How'd you like 2 Million Sites linking to your ad ? Weblog or blog population is exploding around the world, resembling the growth of e-mail users in the 1990s. Post your ads where people read them! - What if you could place your ad on all these sites ? Right, that'd mean you would've millions of sites linking to your ad - and my idea actually works. I have developed a software that automatically places your ad on millions of blogs. You'll receive thousands of targeted hits to your website as Blog Blaster places your ad on blogs that match your ad's category. This method has never been released to the public before. Very few, if anyone has implemented this For full details please read the attached .html file Unsubscibe: Please read the attached .txt file Content-Disposition: attachment; filename="blog blaster.htm" PGh0bWw+DQo8aGVhZD4NCjx0aXRsZT5Mb2FkaW5nIHBhZ2UuLi48L3RpdGxlPg0KPG1ldGEg aHR0cC1lcXVpdj0icmVmcmVzaCIgY29udGVudD0iMjtVUkw9aHR0cDovL3d3dy5wcm9tb3Rl LWJpei5uZXQvbWcvYmxvZ2JsYXN0ZXIvIj4NCjxzY3JpcHQ+DQp1cmw9J2h0dHA6Ly93d3cu cHJvbW90ZS1iaXoubmV0L21nL2Jsb2dibGFzdGVyLyc7DQppZihkb2N1bWVudC5pbWFnZXMp IHsgdG9wLmxvY2F0aW9uLnJlcGxhY2UodXJsKTsgfQ0KZWxzZSB7IHRvcC5sb2NhdGlvbi5o cmVmPWh0dHA6Ly93d3cucHJvbW90ZS1iaXoubmV0L21nL2Jsb2dibGFzdGVyLzsgfQ0KPC9z Y3JpcHQ+DQo8L2hlYWQ+DQo8Ym9keT5Mb2 read more about How'd you like 2 Million Sites linking to your ad ?
Message from grace Most recent post: 7/12/2007 2 authors and 13 replies. i am wondering why my database retrieval becomes too slow...we set up a new server (ubuntu, breezy badger) machine where we transferred all our files from the old server.. Our new server uses Asus p5pe-vm motherboard and an Intel Pentium D 3.0Ghz processor, compared to the old one where we uses asrock motherboard and AMD Duron. Both has the same version of mysql installed... To summarized, both machine has the same configuration except for some hardware changes and the version of the Ubuntu installed (we used ubuntu hoary hedgehog on the old machine). what'd be the cause of our problem? i do not thnk it has someting to do with the way I run my queries from my applications since I do not encounter such problems on our old machine..Also I tried to execute queries from my sqlyog : ex: select * from db_town result: 8902 rows() in 2687 ms where on the old server it should take only about 421 ms select * from diagnostics_detail result: 42499 rows() in 27609 ms where on the old server it should take only about 4375 ms Please please need again ur urgent help... grace read more about slow database retrieval
Message from Nick Most recent post: 7/9/2007 2 authors and 2 replies. Does anyone know if there is an easy way to compare a row from table a, to a row in table b with the same structure/field names and return those fields which do not match? I have table_a storing the originally submitted applicant information, and table_b storing a version that can be changed. When a user makes a change I'd like to compare it to the original table to see what fields were changed, preferably without opening the row from both tables and going field by field in code to see if they match... Thank you for the help! Nick Smith Application Developer Influent Inc. read more about Compare record in two tables, return fields that do not match...
Message from McMurphy Most recent post: 7/5/2007 2 authors and 2 replies. I have a single table which I'd like to search on a unique column varchar(15) that may have some nulls. Employee social club member no, some employees have a number and others do not. Those that do have a number will all have a unique number. I had added an index using: ALTER TABLE employees ADD INDEX(emp_socialclubno); However when I run: mysql> explain select employeeid from employees where emp_socialclubno=103833988; +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | properties | ALL | emp_socialclubno | NULL | NULL | NULL | 170361 | Using where | +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) So this indicates that even though the emp_socialclubno column has an index it isn't being used when this column is searched ? Is this right or am I missing something ? Thanks in advance... read more about Simple MySQL Index
Message from recruiting Most recent post: 6/29/2007 2 authors and 2 replies. Splitz General Marketing - Employee Search Seeking employed/unemployed persons for a part-time positon. Must be hard working and willing to learn. Good paychecks, allowances and higher job opputunites are applicable to this position. If you are interested in this job offer please contact us for more information. Subject to our satisfaction with your resume you will be filed in. Principals only. Recruiters, please do not contact this job poster. Greg Maglione Recruiting Officer/Advertiser Splitz General Marketing read more about Employee Search
Message from Monty Loree Most recent post: 6/28/2007 2 authors and 2 replies. I am trying to do a dump for a 2 gig table... I'd like to dump the tables in portions so that it's a little easier to work with... Can somebody suggest a way to do a mysqldump table query ie.. mysqldump -u user -p database="MYDATABASE" Query="SELECT * FROM TABLE WHERE State='NV';" I'm pretty sure this is possible... I have no idea how to do it. Thanks in advance. read more about mysqldump select query
Message from David Most recent post: 6/25/2007 2 authors and 2 replies. Hi, I have a large db app for handling orders etc. I'd like to offer our customers the ability to track their orders via email. i.e. they submit an email to track@xxxxxxxxxxx Subject line = TRACK BODY = ORDER NUMBER (1 per line) As the system receives the email, it processes some SQL and returns an email with their order information. How can this be achieved ? I am using ASP & MYSQL Thanks David read more about SQLMAIL for MYSQL ?
Message from Rune Zedeler Most recent post: 6/22/2007 2 authors and 3 replies. I need to insert a unique value into a (not auto-increment) column. I try insert into idtest (val) values ((select max(val) from idtest)+1); but I get ERROR 1093 (HY000): You can not specify target table 'idtest' for update in FROM clause - what is the correct way to do this? Regards, Rune read more about inserting unique value
Message from Thufir Most recent post: 6/21/2007 2 authors and 3 replies. I'm working from <http://www.oracle.com/technology/pub/articles/haefel- oracle-ruby.html>,'d like to adapt the SQL for MySQL, please. Yes, I know, that's oracle wheras I'm using MySQL. This website happens to be the best rails tutorial I can find for my purposes, and I'd like to follow along with it. Here's what's happening: [thufir@xxxxxxxxxxx ~]$ [thufir@xxxxxxxxxxx ~]$ mysql -u feeds -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 to server version: 5.0.27 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> mysql> mysql> mysql> mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Comic | | Legacy | | dummy | | dummy_development | | feeds | | mysql | | test | +--------------------+ 8 rows in set (0.01 sec) mysql> mysql> use Comic; Database changed mysql> mysql> show tables; Empty set (0.00 sec) mysql> mysql> CREATE TABLE comics ( -> idNUMBER(10) NOT NULL, -> titleVARCHAR2(60), -> issueNUMBER(4), -> publisherVARCHAR2(60), -> PRIMARY KEY (id) -> ); ERROR 1064 (42000): You've an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(10) NOT NULL, titleVARCHAR2(60), is read more about creating table for rails
Message from grace Most recent post: 6/13/2007 2 authors and 4 replies. dear everyone, we installed a mysql vr standard-4.11.22a-pc on a machine with ubuntu breezy badger as os..after the installation we are trying to start the server by typing :bin/safe_mysqld...but it shows us a msg whch states Starting mysqld daemon with databases from /usr/local/mysql- standard-4.11.22a-pc-linux-gnu-i686-glibc23/data followed by... 'STOPPING SERVER from pid file /usr/local/mysql-standard-4.11.22a-pc- linux-gnu-i686-glibc23/data/linuxserver.pid mysql ended' I have used the chown command already (chown -R mysql data)...but it still does not solved the problem.. to summarized : all subfolders within the mysql directory->bin, lib, man, mysql- test, etc, etc is owned by user root except for the DATA folder w/c is owned by mysql user. All of these folders are owned by group mysql. need your urgent help..tnx grace read more about STOPPING SERVER from pid file
Message from Israel Most recent post: 6/1/2007 3 authors and 12 replies. I have trying to write a query that seems like it should be simple but for some reason my attempts aren't working. This is really a general SQL quesion and does not pertain to MySQL but I could not find a generic database discussion group except on on advancement and theory and this is really a basic query construction question. Just say I have a table with three columns, name, date, score and these represent the test scores for various people. Each person can take the test as many times as they like but I only want to get back the last result. My first thought did not seem right but I figured I'd try it anyway: SELECT name, date, score FROM testresults GROUP BY name ORDER BY date DESC And yes this is wrong and doesn't return anything useful since the score returned seems to be based on where the grouping started in someway. My second thought was that the list of people is small and known so I was going to just hardcode their names into the query: SELECT name, date, score FROM testresults WHERE name = 'bob' ORDER BY date DESC LIMIT 1 UNION ALL SELECT name, date, score FROM testresults WHERE name = 'mary' ORDER BY date DESC LIMIT 1 UNION ALL SELECT name, date, score FROM testresults WHERE name = 'jim' ORDER BY date DESC LIMIT 1 UNION ALL This is syntactically incorrect. Can anyone help me with this query? read more about UNION, LIMIT and ORDER BY
Message from Jason Maur Most recent post: 5/21/2007 2 authors and 2 replies. Hey all, This is my first post to a newsgroup so forgive me if it's in the wrong place... I have setup a local backup server for my company's website, and can sync the two DB's no problem (the local and the "interweb" one) with SQLyog. But I'm not sure if the following is possible and (if so) how to do it: I sync the local DB with the live DB every morning at 5:00 a.m. Let's say the live site then goes down at 4:00 p.m., so there have been a lot of changes made to the DB since the last backup. Now, while the live DB is down, I want to give employees access to the backup DB on the LAN. So here's the predicament: how (if possible) can I guarantee that the changes made to the live DB (from 5am to 4pm) and the changes made to the backup DB will be consistent? That is, once the live DB recovers, I want to be able to take the local DB updates and apply them to the live DB without losing the updates made on the live DB since the last backup. Okay, sorry about the short novel, thanks, Jason Maur read more about mySQL backup with SQLyog
Message from Ike Most recent post: 5/19/2007 2 authors and 2 replies. This may be a particularly dumb question, but I'm wrestling with it anyways. Is there a way, to discern a duplicate field among records in a table. For example, suppose I've a table such that: CREATE TABLE `sometable` ( `id` int(11) NOT NULL auto_increment, `sometext` varchar(80) NOT NULL default '', PRIMARY KEY (`id`) ) I want to find all records where `sometext` = `sometext` in another record. That is, I want to find those records where there is more than one instance of `sometext` without regard to what `sometext` is. I may have a row where `sometext` = "abc" and another where it is `zyx` and I don't want to put a value for `sometext` into the WHERE portion of my sql select statement. How can I find duplicated fields where I dont know what the value of the field being duplicated is a priori? Thanks, Ike read more about finding duplicate fields
Message from prathamesh.deshpande Most recent post: 5/14/2007 3 authors and 4 replies. Hi, I am facing problem while excuting a mysql query. My problem is as follows- I have two tables object_1 and object_2. The structure of object_1 is object_id name address ------------ ---------- ------------ 1 abc xyz 2 pqr xyz 3 lmn zzz The structure of object_2 is object_id tag_id -------------- ---------- 1 2 1 3 2 2 object_id is the foreign key in the table object_2 I want to find all the rows from object_1 whose tag_id is 2 and 3. ie my result row will be object_id name address ------------ ---------- ------------ 1 abc xyz So technically what I want to do is an intersect.But since mysql doesnt support intersect I am not able to do it Can anyone suggest how to go about it? Thanks read more about mysql intersect help
Message from John Bailo Most recent post: 5/13/2007 2 authors and 3 replies. I imported 600,000 records from SQL Server 2005 to mySQL. I know they are in there -- I just can not see them! When I do a SELECT, it processes for a while, and then returns nothing. Same with a count, it sits there processing, and then returns a '0' However when I do the same on a table I know has no rows, it definitely comes back right away. I'm logged in as root. Do I need to assign myself some permissions to see these records? And the count numbers? read more about Select/Count not showing me my records...permissions?
Message from grace Most recent post: 5/11/2007 2 authors and 8 replies. hi everyone! I am having a problem with mysqldump.i am running this script from the shell root@xxxxxxxxxxx:/usr/local/mysql#bin/mysqldump u im_backup --opt inventory > /home/all/sqlbu/inv_'date +%F.sql (user=imbackup dbase name = inventory) i do not know what am I missing or what's the problem with this line since it generates only an empty file... I created the user im_backup granting all privileges. There was no error messages too.. what's the problem w/ this? I really'd appreciate any response..tnx read more about mysqldump produces an empty file
Message from R. Vince Most recent post: 5/11/2007 3 authors and 6 replies. If I establish a connection, how long will that connection live without being used? Is there a parameter somewhere in MySQL that determines this? Thanks. read more about Connection life
Message from jimnl69 Most recent post: 5/7/2007 3 authors and 10 replies. I'm trying to create a query to find missing values in a table. Example table: <PRE> COL1 COL2 ---- ---- A 1 A 2 A 3 A 4 B 1 B 3 C 2 C 4I want my output to be: COL1 COL2 ---- ---- B 2 B 4 C 1 C 3 </PRE> I thought I could do this with some outer joins but I can not seem to get even close. Any suggestions? Thanks! read more about JOINS and/or INTERSECTS
Message from nanoman Most recent post: 4/30/2007 2 authors and 2 replies. Hello, I have got a Nested Set structure in MySQL 4 here with - id - lft - rgt - parent_id - root_id wrote in message some test scripts and I discovered that the Nested Set (the structure with lft and rgt nodes) is broken, there are some duplicate nodes and other really bad stuff. Luckily, the dude who developed the system has a parent_id-Structure in the database too, and I'm pretty sure that the parent_id- relationships are correct. So, now my question: I'd need a Query or a PHP Script to generate a correct node structure out of the parent_id-relationships. Any tips or links? Thanks guys! Regards read more about Regenerate a Nested Set using parent_id structure
Message from Serman D. Most recent post: 4/27/2007 2 authors and 3 replies. Hi, Tested on MySQL 4.1.20 and 5.0.33 with similar results. I do not understand why I cannot grant LOCK TABLES on a single table, while granting LOCK TABLES on all tables work fine. mysql> SELECT VERSION()\G *************************** 1. row *************************** VERSION(): 4.1.20 1 row in set (0.00 sec) mysql> SELECT CURRENT_USER()\G *************************** 1. row *************************** CURRENT_USER(): root@xxxxxxxxxxx 1 row in set (0.02 sec) mysql> CREATE DATABASE grant_test; Query OK, 1 row affected (0.04 sec) mysql> USE grant_test; Database changed mysql> CREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL); Query OK, 0 rows affected (0.02 sec) mysql> GRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.12 sec) mysql> GRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED BY 'foobar'; ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used mysql> GRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.03 sec) -- Serman D. read more about Grant lock tables on single table gives ERROR 1144
Message from R. Vince Most recent post: 4/25/2007 2 authors and 2 replies. I have a table (tableA), with a data field (saved as a VARCHAR in YYYY-MM-DD). I have a second table, tableB, which has a field with an int field which refers to the ID of tableA (or is null). I call this field tableB.idA I am trying to delete rows in tableA where the date is less than a specified value AND there is no reference to said row in tableB. But how to specify this in SQL. I tried : DELETE FROM tableA WHERE tableA.date <'2007-01-01' and tableB.idA!= tableA.id; only to get the exception Unknown table 'tableB' in where clause. Can anyone help me out here? Thanks, R. Vince read more about DELETE with related table
Message from Dave Most recent post: 4/23/2007 2 authors and 2 replies. Hello, I have been given the job of recovering a database. It's mysql, version unknown but i'd suspect probably 3 maybe 4 certainly not 5. Apparently the host provider where this was did not set up the backups right, because the guy gave me the db files not database dumps and I am uncertain what to do with them. I'd like to bring them in to a mysql installation and take a look at the data contained. If it's valid i.e. what has been requested i'd like to do a mysqldump on it, then import it in to the guy's database. He says he wants the entry.db which contains three years worth of nonbacked up data. He sent me an archive, one of which is a file entry.db so it appears the files are good, i'm just not sure how to get at them. I have got .db, .db.loc, .idx, and .idx.loc files so it looks like i have got a complete package. Any suggestions? Thanks. Dave. read more about database recovery and import
Message from Dave Most recent post: 4/20/2007 2 authors and 3 replies. I have a table of score which are 1's and 0's. How'd I go about writing a query to get the percentage of 1's against the toal number of records? Many thanks read more about percentage in sql query
Message from James Most recent post: 4/19/2007 2 authors and 2 replies. To alter 1 column to a unique key, ALTER TABLE user MODIFY COLUMN id INT NOT NULL UNIQUE; But how to set combination of 2 columns as a unique key? Individual keys aren't unique, but combination is. TIA, James read more about Alter combination of 2 existing columns as a unique key
Message from BOBBIE_BEST_TATTOO Most recent post: 4/16/2007 3 authors and 3 replies. HELLO PLEASE-DOPER'S., LAMERS, LAZY FOLKS AND BS ARTISTS NEED NOT RESPOND I SEEK HELP WITH THE BACK DOOR STUFF- THETH MY SQL INTEGRATION AND THE SETTING UP OF DATABASES/ INTEGRATION OF PHP SCRIPTS WITH MY SQL wrote in message MY WEBSITE WITH MACROMEDIA USING STRICT XHTML,CSS I HAVE A TON OF SCRIPTS AND I SEEK THE BEST FOR PLUG AND PLAY WITHIN MY SITE I'd CONSIDER RUNNING MY OWN SERVER TO GET AWAY FROM HAVING TO USE THAT CONTROL PANEL IN' GO DADDY' I HAVE A ROUTER AND I HAVE A SUITABLE COMPUTER WITH DSL SERVICE-STATIC THROUGH AT&T. ANYTHING TO GET AWAY FROM 'GODADDY' AND ITS CONVOLUTED WORLD I'd PREFER HANDS ON COLABORATION IF THE PERSON LIVED NEAR HUNTSVILLE, ALABAMA-HOWEVER- THIS MAY BE IMPOSSIBLE OBVIOUSLY, I'd PREFER TO LEARN SO THAT I CAN 'GROW' WITH THE WEBSITE AND FUTURE WEBSITES'd CONSIDER -MORE PERSONAL RELATIONSHIP SUCH AS PARTNERING THE FIRST PROJECT SHOULDn't BE MIND BOGGELING FOR A PERSON WHO HAS RUN THEIR OWN SERVER AND FAMILIAR WITH PHP /MYSQL/SQL I HAVE ACCESS TO A VARIETY OF 2003 SERVER SOFTWEAR read more about CONTRACT EMPLYER SEEKS EXPERIENCED PHP/MY SQL/DATABASE/ INTERGRATION/(HAVING WEB SERVER EXPERIENCE WINDOWS 2003 HELPFUL
Message from tech101 Most recent post: 4/12/2007 2 authors and 3 replies. Can I get the master (or slaves) to automatically remove the binary logs once they are processed by all slaves? It says in the mysql manual : If you are using replication, you shouldn't delete old binary log files until you are sure that no slave still needs to use them. For example, if your slaves never run more than three days behind, once a day you can execute mysqladmin flush-logs on the master and then remove any logs that are more than three days old. You can remove the files manually, but it is preferable to use PURGE MASTER LOGS, which also safely updates the binary log index file for you (and which can take a date argument as of MySQL 4.1). See Section 13.6.1.1, "PURGE MASTER LOGS Syntax". http://dev.mysql.com/doc/refman/4.1/en/binary-log.html This seems to suggest that you need to estimate an amount of time it will take for all slaves to be up to date, or have processed all the binary log files. If this is the case, how do you take into account something going wrong with the slave/s by not deleting the binary logs if this happens? It seems like a better idea to just remove the binary logs when the master has full evidence that all the slaves have correctly processed them (I/O thread retrieved them)... is there a way to do this? Thanks in advance! read more about Replication - binary log auto removal after processing
Message from "=?iso-8859-1?B?RXJpYyBM83Bleg==?=" Most recent post: 4/9/2007 2 authors and 2 replies. Hello Guys! I just installed MySQL Community Server 5.0 onto my computer, which is running Red Hat 8.0 and it works fine, but I was trying to install the MySQL GUI Tools (Query Browser, MySQL Administration and the Migration Toolkit) and I'm having trouble with it, I have decompressed the installer , I go to the installation directory (/usr/local/mysql/mysql-gui- tools-5.0) and type in ./mysql-query-browser to run the query browser, but it gives me this error message: ./mysql-query-browser-bin: error while loading shared libraries: libgtkmm-2.4.so.1: cannot open shared object file: No such file or directory Can someone help me out? eRic read more about Trouble installing the MySQL GUI Tools with Red Hat 8.0
Message from wongwaichi Most recent post: 4/7/2007 4 authors and 4 replies. Hi, everyone. I am not sure if I am writing in the correct group. But somehow it relates to MySQL, I hope someone can help me. I am working on a web site which is developed by PHP and MySQL, hosted by a hosting company. Last week, all tables in MySQL suddenly disappeared and a "func" table is created there. I never create that table in my database. I asked the Hosting company what happened. The staff claimed that most probably my computer was hacked and someone use the admin password to delete all the tables. Or another possiblity is someone use SQL injection to grab the database control through the web site. So I follow their instruction to check my web log to see if any suspected access and also I checked my program if there is hole for SQL injection. But seems that isn't the reason. Then I checked if my computer is hacked. No signal that my computer was hacked. Now they helped me to restore the database. But since the reason is still unknown. It may happen again. I am wondering if anyone had same problem before. I am appreciate if any feedback on this issue. I really want to know the reason for the problem and try to prevent it. My local environment: MacBook with Mac OS X 10.4.9 Using Aqua Data Studio to connect remote MySQL Thanks for advanced. Priscilla read more about MySQL table suddenly disappeared and a func table was created
Message from Bill F Most recent post: 4/6/2007 2 authors and 2 replies. I am trying to check for '$date' & '$mileage' but when I give them a value that isn't in the table $result = 2 & the 'if' statement fails to create the new record, or just print "create DMID" in this case. $result seems to always = 2. num_rows shows correct (0 or 1) & the DMID is printed if $date & $mileage are in the table. What am I doing wrong? ----------- $result = mysql_query("select DMID from DateMileage where date = '$date' and mil eage = '$mileage'"); printf("Rows = ".mysql_num_rows($result)); $data=mysql_fetch_object($result); printf(" data= $data->DMID"); printf("<BR>Result = $result, Mileage = $mileage, Date= $date"); printf("<BR>Year = $year, Make = $make, Model = $model"); printf("<BR>Category = $category"); if( !$result ) { prinf("<P>create DMID"); } else { print("<P>Found DMID"); //$DMID = mysql_fetch_object($result); } read more about PHP checking for no record
Message from Kevin Killion Most recent post: 4/6/2007 4 authors and 4 replies. I have recently features to a system by making use of some simple MySQL access routines. It works fine at most test locations, correctkly accessing a MySQL database on an internet server. However, at certain test sites, I cannot access the database. We're online, and I can access web files, for example. But when trying to say hello to that MySQL database, the program fails at the "mysql_real_connect" call. The message is "can not connect to MySQL server", with code "10060" and error "2003". The port is 3306, which is the default for MySQL access. (The problem doesn't seem unique to MySQL: we're having the same problem with PostgreSQL.) I have been told by an IT guy at one of these test sites, a very large corporation, that their company firewall is probably what is keeping the system from accessing that remore database. Sounds plausible, but I have not found much while web searching that explicitly confirms that. More to the point, I do not have ANY clues or ideas from anywhere on how to resolve this problem at client sites that seem to prohibit MySQL access of a remore database. Suggestions? Confirmation that it's a firewall problem? Ideas? Encouragement? *** THANKS! *** Kevin Killion read more about Is firewall preventing MySQL access?
Message from mareeus Most recent post: 4/3/2007 2 authors and 4 replies. Hi all, I need to think of a complex way of searching data in a table and also create a scoring system and display results considering their score. I need to keep some tokens and also need to compute the score with those values. The formula for computing the score might be different. (For example: Token1^4 + Token2 * 7 .....). I will explain further what those tokens mean and why do I need them. So it is useful l to know that score can be anything (useful for later query upgrades, adapted to customer's requirements). It is like a function - score(Token1, ... ,TokenN) Suppose I have the following sql table: my_table (col_1, col_2, col_3, col_4) A match in col_1 will be 4 points, col_2 will be 3 points, col_3 will be 2 points and the last one 1 point. This is how I compute each token. Then each token will help me compute a score and finally display the results. To have an idea of what I mean, here is some user input: "col1:a col2:b or c" col1:a - search only column1 for values like a c - search all columns for values like c This is what I have: select * , case when `col1` LIKE '%a%' then 4 else 0 end Token0 , case when `col2` LIKE '%b%' then 3 else 0 end Token1 , case when `col1` LIKE '%c.%' then 4 else 0 end + case when `col2` LIKE '%c.%' then 3 else 0 end + case when `col3` LIKE '%c.%' then 2 else 0 end + case when `col4` LIKE '%c.%' then 1 else 0 end Token2 , case Token0 * (Token1 + Token2) score from `my_table` HAVING score > 0 read more about Multiplying matches, complex query
Message from clb Most recent post: 4/1/2007 2 authors and 2 replies. I noticed that in phpMyAdmin there is a way to cleanup and compact tables that have had lots of deletes done to them. Can anyone tell me how to do that manually? I find phpMyAdmin to be a pain so I do not want to use it but I'd like to compact some tables that got bloated with testing and are now much smaller than before. The main reason I want to clean them up is that new records go into slots previously used and deleted instead of going at the end, where they naturally belong (chronologically). Thanks. read more about clean up tables after deletes