http://blog.gmane.org/gmane.comp.db.mysql.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.mysql.general/110079 <pre>I am hoping someone can bail me out on why this query in not working. In the queries below: query 1 returns multiple rows - one per client. in the rows where: client_id is 254240, dr_all_ther_qty = 1 client_id is 253821, dr_all_ther_qty = 1 client_id is 254023, dr_all_ther_qty = 6 But when I break out the queries into their own queries (queries 2,3,4), I get different result (WHICH are ACCURATE - the ones I need): query 2 - dr_all_ther_qty = 0 query 3 - dr_all_ther_qty = 0 query 4 - dr_all_ther_qty = 5 Clueless on why these DO NOT reconcile. I really need query 1 results to be the same results as queries 2,3, and 4. Little help! --- start query 1 --- SELECT q1.* FROM (SELECT apt.user_id, apt.client_id, c.last_name, c.first_name, MIN(apt.time_start) AS stime, FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m-%d') AS ftime, (SELECT count(*) FROM tl_appt LEFT JOIN tl_rooms r on r.room_id = tl_appt.room_id WHERE client_id = apt.client_id AND r.location_id = '1' AND appt_status_id = '3' AND time_</pre> Don Wieland 2012-05-25T19:58:42 http://comments.gmane.org/gmane.comp.db.mysql.general/110074 <pre>Dear ladies and gentlemen, Upscene Productions is happy to announce the launch of a new database-developer tool: "Hopper" (public beta 1) Hopper is a Stored Routine Debugger, the first beta for MySQL is now available. For more information, see http://www.upscene.com/displaynews.php?item=20120525 With regards, Martijn Tonies Upscene Productions http://www.upscene.com </pre> Martijn Tonies 2012-05-25T10:44:54 http://comments.gmane.org/gmane.comp.db.mysql.general/110073 <pre>I have a database for the category id | name | parentid ---------------------- 1 | cat A | 0 2 | cat B | 0 3 | subcat A | 1 4 | subcat A | 1 5 | subncat B | 2 ------------------------- I want to display the result like this: 1. Cat A - Subcat A - Subcat A 2. CatB - Subcat B ask for help for this. Thanks for the help __________ Information from ESET NOD32 Antivirus, version of virus signature database 7165 (20120524) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com </pre> HaidarPesebe 2012-05-25T07:57:46 http://comments.gmane.org/gmane.comp.db.mysql.general/110065 <pre>Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server.  Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done)  --Anupam </pre> Anupam Karmarkar 2012-05-24T09:17:12 http://comments.gmane.org/gmane.comp.db.mysql.general/110048 <pre>Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU : Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read "Hiperformance Mysql vol.2" , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3</pre> Yu Watanabe 2012-05-23T02:07:08 http://comments.gmane.org/gmane.comp.db.mysql.general/110011 <pre>I have got this query that returns the correct rows I want to display, BUT the columns dr_ther_qty, dr_not_ther_qty, and dr_all_ther_qty are not calculating correctly: --- START QUERY ONE --- SELECT q1.* FROM (SELECT apt.user_id, apt.client_id, c.last_name, c.first_name, MIN(apt.time_start) AS stime, FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m- %d') AS ftime, (SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND appt_status_id = '3' AND time_start &lt; apt.time_start) AS previous, (SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND user_id = apt.user_id AND appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20') AS dr_ther_qty, (SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND user_id != apt.user_id AND appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20') AS dr_not_ther_qty, (SELECT DISTINCT count(*) FROM tl_appt WHERE client_id = apt.client_id </pre> Don Wieland 2012-05-21T16:17:58 http://comments.gmane.org/gmane.comp.db.mysql.general/110008 <pre>Hi , I am trying to reduce the ibdata1 data file in MySQL. In MySQL data directory the ibdata1 data file is always increasing whenever I am creating a new database and inserting some data into database. If I drop the existing database, the table structures only dropped from the server but data still exist in the ibdata1 data file. How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. Do you have any idea how to solve this problem. Thanks for any feedback. Thanks Manivannan S DISCLAIMER: This email message and all attachments are confidential and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error, please notify us immediately by return email or to mailadmin&lt; at &gt;spanservices.com and destroy the original message. Opinions, conclusions and ot</pre> Manivannan S. 2012-05-21T13:03:46 http://comments.gmane.org/gmane.comp.db.mysql.general/110003 <pre>Ladies and Gentlemen: I am getting below errors and therefore the user sessions terminate causing business impact...Can some one who is expertise already in this advice at the earliest? 120513 8:19:45 [Warning] Aborted connection 1167257 to db: 'iib' user: 'iibuser' host: '210.18.3.94' (Got timeout reading communication packets) OS version: RHEL 5.3 DB version: MYSQL 5.1 Table involved in the DB is of type : inndoDB Background : This is an online exam registration site DB and the concurrent connex invariably reaches to 200 for 500 users which should not be the case. Ideally the concurrent connex must be &lt;10. Normally , we run truncate table before the exam starts up. A similar setup(in terms of DB/OS/config etc ) works fine which is actually DR at different site. PS: Network segment between web and DB tier has been thoroughly checked and seems to be fine. Thanks a ton! Best Rgs, Shafi AHMED Sify - Chennai Get your world in your inbox! Mail, widgets, documents, spreadsheet</pre> Shafi AHMED 2012-05-21T07:45:09 http://comments.gmane.org/gmane.comp.db.mysql.general/109997 <pre>I have a table of DVDs, another of scenes and a last one of encoding formats/files... I want to find in one query all the dvd_id that have &gt; 0 scene_id that's encoded in format_id = 13. In other words all DVDs that are format_id = 13 despite not having a direct link. CREATE TABLE `dvds` ( `dvd_id` smallint(6) unsigned NOT NULL auto_increment, `dvd_title` varchar(64) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`dvd_id`), ) CREATE TABLE `scenes_list` ( `scene_id` int(11) NOT NULL auto_increment, `dvd_id` int(11) NOT NULL default '0', `description` text NOT NULL, PRIMARY KEY (`scene_id`), ) CREATE TABLE `moviefiles` ( `scene_id` int(11) NOT NULL default '0', `format_id` int(3) NOT NULL default '0', `filename` varchar(255), `volume` smallint(6) NOT NULL default '0', PRIMARY KEY (`scene_id`,`format_id`), ) </pre> Daevid Vincent 2012-05-19T00:33:58 http://comments.gmane.org/gmane.comp.db.mysql.general/109994 <pre>All, I'd like to be able to create column aliases which are bound to the database itself. I have an assortment of columns without naming standards which i'd like to migrate to a better naming scheme. Is there a feature in MySQL that would allow me to give a database column multiple names? I'm thinking that for SELECT * statements, you would use the default column name, but for insert, delete, update, etc, it would be fine to use the aliased name or default. Doing this would *really* help to allow me to migrate the database to the new naming convention without breaking existing code. I would then be able to refactor at a more leisurely pace. Does the feature exist, or can it be created? </pre> D. Dante Lorenso 2012-05-18T21:21:59 http://comments.gmane.org/gmane.comp.db.mysql.general/109991 <pre>List is broken, in many ways. Amongst many of its failures, the two current biggest is: 1/ This lists bounce messages are clueless they do not include the reject reason 2/ mysql-owner address STILL points to a sun address which of course is rejected as they no longer host this list. *sigh* </pre> Noel Butler 2012-05-18T00:51:04 http://comments.gmane.org/gmane.comp.db.mysql.general/109988 <pre>Hi folks, I am trying to compile a query that does statistics on appointments based on specific criteria. Here is my starting query: SELECT u.user_id, c.client_id, c.first_name, c.last_name, a.time_start AS stime, FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted FROM tl_appt a LEFT JOIN tl_users u ON a.user_id = u.user_id LEFT JOIN tl_clients c ON a.client_id = c.client_id LEFT JOIN tl_rooms r on a.room_id = r.room_id WHERE a.appt_id IS NOT NULL AND FROM_UNIXTIME(a.time_start,'%Y-%m- %d') between '2011-05-01' and '2011-12-31' and r.location_id = '2' and a.user_id IN (14) ORDER BY u.last_name, u.first_name, c.last_name, c.first_name This will return a set of rows where a client may have MORE THEN ONE appointment. From this set I need to narrow more: 1) Only display the first appointment PER Client. (there will be no duplicate client_id) Then once I have that set of rows established, I need to query for two more result: 1) Show New Customers =</pre> Don Wieland 2012-05-17T14:37:01 http://comments.gmane.org/gmane.comp.db.mysql.general/109984 <pre>Good morning, I have an application where the user ids were stored lowercase. Some batch import, in the user table some users stored a uppercase id, and for some applicative logic, in other tables that have a foreign key to the user table, their user ids are stored lowercase. MySQL didn't throw any error probalby because the collation used is "case insensitive". My problem is that the application is Java and java strings are case sensitive, so now I want to set user ids to lowercase EVERYWHERE. I supposed that I could execute with ease these commands: - update mytable1 set USER_ID = LOWER(USER_ID); - update mytable2 set USER_ID = LOWER(USER_ID); - update mytable3 set USER_ID = LOWER(USER_ID); But for some tables I got some Foreign key constraint to throw an error. (butwhy they didn't throw an error on the insert but just on the update???) And if I try to disable foreign key checks during these updates, I get some "duplicate key" errors where USER_ID is a part of composite key with other columns. (but I do</pre> GF 2012-05-16T07:54:09 http://comments.gmane.org/gmane.comp.db.mysql.general/109981 <pre>Seu cliente de e-mail não pode ler este e-mail. Para visualizá-lo on-line, por favor, clique aqui: http://www.engsena.com.br/market/display.php?M=88578&amp;C=a422ea16f0fc983e666748f74186ae45&amp;S=9&amp;L=7&amp;N=1 Para parar de receber nossos Emails:http://www.engsena.com.br/market/unsubscribe.php?M=88578&amp;C=a422ea16f0fc983e666748f74186ae45&amp;L=7&amp;N=9 E-Marketer </pre> Sena Engenharia 2012-05-15T20:32:36 http://comments.gmane.org/gmane.comp.db.mysql.general/109978 <pre>hi every one Is there any performance difference, maintaining separate ibdata files for each and every table insted of having one singl tabale for all databases, for InnoDB Storage Engine. please let me know the difference. </pre> Pothanaboyina Trimurthy 2012-05-15T11:57:43 http://comments.gmane.org/gmane.comp.db.mysql.general/109974 <pre>Hi all, We are currently designing a database for our application (python/mysql) and we have some performance concern: We would have "users" and "images". "users" can view some "images". "images" can be viewed by several "users". (n to m mapping) Which would be most efficient practice (regarding speed)? We want to figure the most efficient way to get the images that a user see? We want to handle 10 000 users and 100 000 images. *1* to create 3 tables:* user - info about a user images - info about an image user_image_mapping -&gt; one row per association: user 1 -&gt; image 22 user 1 -&gt; image 8888 user 2 -&gt; image 567 user 3 -&gt; image 888 user 3 -&gt; image 44453 *2* to create 2 tables* user - info about a user -&gt; a field would contain a list which represents the ids of the images the user can look at? images - info about an image Any suggestion would be greatly appreciated, Best, Nicolas </pre> Nicolas Rannou 2012-05-14T21:05:26 http://comments.gmane.org/gmane.comp.db.mysql.general/109952 <pre>Upscene Productions is celebrating it's 10 year anniversary with a massive discount on all our products: 70% discount until the end of May. Don't forgot to blog and twitter about this! We produce database development, management and testing tools for: * Oracle * Microsoft SQL Server * MySQL * InterBase * Firebird * SQL Anywhere * NexusDB * Advantage Database * Generic connectivity tools for ADO and ODBC These include test data generator tools, database design and development tools, auditing tools, a dbExpress driver for Firebird, debugging tools, performance analysis tools. Coupon code TENYEARS will get you this discount, check http://www.upscene.com for more information. </pre> Martijn Tonies 2012-05-14T13:16:15 http://comments.gmane.org/gmane.comp.db.mysql.general/109946 <pre>Hi all. I write a script to delete rows from slow_log older than 2 weeks. #!/bin/bash if [ $# -ne 1 ]; then echo "Usage: $0 mysql_config_file" exit 1 fi SELECTQUERY="select * from slow_log where start_time &lt; DATE_ADD(NOW(),INTERVAL - 2 WEEK)" DELETEQUERY="delete from slow_log where start_time &lt; DATE_ADD(NOW(),INTERVAL - 2 WEEK)" CONFIG_FILE="$1" HOSTNAME="$(hostname | awk -F'.' '{print $1}')" INSTANCENAME="$(grep datadir $CONFIG_FILE | awk -F'/' '{print $4}'|uniq)" LOG_FILE="/var/log/${INSTANCENAME}-${HOSTNAME}-slowlog-clean.log" echo "***" &gt;&gt; $LOG_FILE echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Started cleaning..." &gt;&gt; $LOG_FILE echo "Before there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e \"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" &gt;&gt; $LOG_FILE mysql --defaults-file=${CONFIG_FILE} mysql -e \"$DELETEQUERY\" &gt; /dev/null 2&gt;&amp;1 echo "After there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e \"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" &gt;&gt; $LOG_FILE echo "$(date +'%Y-%m-%d %H:</pre> Rafał Radecki 2012-05-14T09:48:52 http://comments.gmane.org/gmane.comp.db.mysql.general/109942 <pre>While doning a batch process... show full processlist show: | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query | 6 | end | update `account` set `balance`= 0.00 + '-4000' where accountid='2583092' No other process, lo locking no nothing... so you take this same query... run it isolated, and the mu....fu... is just...f.... fast!!!!! update `account` set `balance`= 0.00 + '-4000' where accountid='2583092' Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 ARRRG! I have seen this type of query take as long as 100+ seconds.. and I don't have a F*** clue... </pre> Andrés Tello 2012-05-12T14:08:28 http://comments.gmane.org/gmane.comp.db.mysql.general/109927 <pre>Hi all. Is there a possibility to see the info from slowlog somewhere in database? I would like to see slow queries using mysql and not by watching the log file. I've searched on google and mysql website but hasn't found the solution. Best regards, Rafal Radecki. </pre> Rafał Radecki 2012-05-11T09:10:11 http://comments.gmane.org/gmane.comp.db.mysql.general/109926 <pre>Hi all last night we droped some partitions and we found that the first drop costs about 30mins and then it's about 3 seconds. when droping the partition all processes are shown waiting for opening table like: +---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | 4656803 | bkdloguser | 10.3.0.116:48642 | backend_log | Query | 1420 | Opening tables </pre> louis liu 2012-05-11T08:34:41 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.mysql.general