mysql

How are nested set models traversed in MySQL?

I'm reading this article and on the part with the query: SELECT node.name FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = 'ELECTRONICS' ORDER BY node.lft; I'm wondering how is it travered? What happens step by step? I'm confused, please help. ...

MySQL problems after upgrading to Rails 2.3

Yesterday I updated our project from Rails 2.2.2 to Rails 2.3.4 and there seem to be major problems with MySQL: a lot of slow queries (up to 100-200 seconds), very high DB latency, lock timeout errors. I had to rollback to the previous version and now trying to investigate this strange behavior. Maybe someone experienced similar issues? ...

MySQL Connector/Net Trace

Does anyone know a way to enabled Tracing using a TraceListener for the MySQL Connector/net I'd like it to log the SQL Queries that are actually run against the DB, i.e. see the SQL with the parameter values substituted in. ...

MySQL increment value of attribute - misses sometimes(?)

I have a column called views in my table A. I want to increment the views column like so: UPDATE A set views = views + 1 WHERE ID = blabla LIMIT 1; This seems like the way to do it, at least to me. Or so I thought. Seems like when I (from PHP) do: $views = get_viewcount($id); $views++: save_viewcount($id, $views); //here we just u...

mysql: How to INNER JOIN a table but limit join to 1 result with the highest vote or count?

I have 2 tables. One is items and another is votes for those items. Items table has: |item_id|name|post_date Votes table has: |votes_id|item_id|answer|total_yes|total_no What I want to do is show all items based on post_date and show the answer in the votes table with the HIGHEST total_yes. So I want to show only a SINGLE answer from ...

Native Stored Proc v/s Hibernate

I am a newbie in Hibernate. I am working on a cloud service data access layer. Currently we are using Hibernate for OR mapping and as data access layer using Hibernate annotations. But lately i have been asked to implement Hibernate/Data Access layer in such a way that my stored procedures be in HQL and we can change our DB at a short ...

mysql error: Lock wait timeout exceeded

I'm using mysql together with asp.net on my webserver. I also use a sqlite database to be able to use the database on another device. I need to send data between the two databases. This is something that needs to be done many times a day. This is an example of how I do it: using (MySqlTransaction mysqltransaction = mysqlconn.BeginTr...

Adding certain minutes to time in MySQL

This question is related to this question I have a field which is a time-field (it does not need to be a datetime field, cause the date part makes no sense here). The value i want to add is in another field, in minutes. So basicly, I want to add minutes to a time value. I have tried the DATE_ADD function, but it expects the date to be ...

mysql server configuration on windows

am installed mysqlserver on windows(xp) another system as server....i want to access mysql from (i.e create table) client machine.client machine does't have mysql..and installed all tools..how can i connect from client.. ...

Output buffering and large MySQL result sets in PHP 5

Hey guys I'm trying to build an XML feed from a database with a ginormous table, almost 4k records. I want to use output buffering to get it to spit out the XML but the script still keeps on timing out. ob_start(); $what = 'j.*, (select description from tb_job_type as jt WHERE jt.jobtype_id = j.job_type_id) as job_type,'; $what .= '(s...

MySQL field values, can a float be defined beyond digit count?

Three questions, all basically the same: So my table has a field, and it's data type is float. There are only 64 possible values, however, ranging from 0.25 to 16.0 (increments of 0.25), and I'd like to only allow those values as an extra layer of validation. Can I set the field to be greater than zero? Can I set a minimum and maximum...

Grouping with operands question

I have a table: mysql> desc kursy_bid; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | datetime | datetime | NO | PRI | NULL | | | currency | varchar(6) | NO | PRI | NULL | | | va...

PHP/MYSQL only allowing one vote per member??

Hi, ive been giving the task at work of setting up an awards voting system, I dont know too much about php and mysql. But i know more about this than anyone else here, and my boss in on holiday. But I've been reusing the code, that had previously been left on our system and adapting it for this year. Basically the voting system works fi...

How to use the controller variable in Views + cakephp

hi, i am having the QUery in my controller actions as $report_attrid=$this->Report->find('all',array('conditions'=>array('Report.report_id'=>$report_id,'Report.user_id'=>$userId))); $submitters['Result']['submitters']=$this->Result->find('all',array('conditions'=>array('Result.form_id'=>$report_form_id) ,'group'=>array('Result.su...

How to select the MySQL's engine name of some table by sql query?

How to select the MySQL's engine name of some table. MyISAM or InnoDB. Can we do it by a simple sql query? ...

Starting Mysql as root

I have started mysql on my test server as a root. I have added user=root line in my.cnf Since I want the test server to upgrade to production server, I will like to comment this line and restart. Are there any side effects? data loss expected? ...

how do i get mysql results of todays date?

I'm trying to pull results from a table that have been inserted today. Each insert is marked with a timestamp. If I do a query like: SELECT * FROM table WHERE added > DATE_SUB(NOW(), INTERVAL 1 DAY) It seems I get stuff that did fall on today's date, but also stuff from 24 hours ago. maybe I'm seeing things. I don't want to s...

Wamp phpMyAdmin password issue

Hi, I'm new to working with web servers and need some assistance. I have downloaded the most recent Wamp server and did a bit of database design and querying, but there ws always a warning at the bottom of the admin page about setting up a password. I tried doing this for the root user, but ended up doing something wrong and I no longe...

If fetched data from MySql is smaller than *number*, show this, else show something else?

Hi Masters of Web Programing. I've got this code: <?PHP $db_user = 'user'; $db_pass = 'password'; $db_name = 'dbname'; $db_host = 'localhost'; if(mysql_connect($db_host,$db_user,$db_pass)) { mysql_select_db($db_name); mysql_query("CREATE TABLE IF NOT EXISTS smsads(id bigint unsigned primary key auto_increment, link varchar(255)...

Problems using MySQL FULLTEXT indexing for programming-related data (SO Data Dump)

I'm trying to implement a search feature for an offline-accessible StackOverflow, and I'm noticing some problems with using MySQLs FULLTEXT indexing. Specifically, by default FULLTEXT indexing is restricted to words between 4 and 84 characters long. Terms such as "PHP" or "SQL" would not meet the minimum length and searching for those t...